Question:
I need to read and write data from Excel spreadsheets, and export some charts, using C#. But I can't use the interop library ( Microsoft.Office.Interop
) for that.
How can I do?
Does anyone have any NuGet package indication for this?
I know EPPlus, I've done some work with it, but with it you can't export graphics.
I know that with EPPlus it is possible to record and read data from the spreadsheet, and even create graphics, but the problem is when exporting the graphics, I need to export this existing graphic in an existing excel to JPG, PNG or even PDF . But preferably for JPG.
Answer:
Spire.XLS
I made a solution using the Spire.XLS component this is the developer's site and there is good documentation about the component .
You can install Spire.XLS from Nuget :
Install-Package Spire.XLS -Version 8.11.6
Here is an example of how to convert an excel bar chart to image:
public void ConvertChartXlsToImg()
{
Workbook workbook = new Workbook();
workbook.LoadFromFile(@"D:\MinhaPasta\column-chart.xlsx");
Worksheet sheet = workbook.Worksheets[0];
Image[] imgs = workbook.SaveChartAsImage(sheet);
for (int i = 0; i < imgs.Length; i++)
{
imgs[i].Save(string.Format("img-{0}.jpeg", i), ImageFormat.Jpeg);
}
}
Project with examples on GitHub
I made a repository on GitHub with a web project that reads an excel file with a column chart and converts it to an image and prints it to the screen.
In the example, if you access the Home/Index controller you will find how to generate the image and save it to file or memory ( byte[]
) and print it in the View .
In the excel file there is only one graphic, but if others were created, they will all be printed.
Note: The problem is that the free version of this component limits you on some things. If you use it only for this purpose, I believe your only problem will be text that is printed on the graphic image stating that it was generated by this component, but I recommend that you read about the limitations on their website and do tests by reading larger spreadsheets with more graphic.
Other components:
EPPlus
With EPPlus I could n't find a way to render the image. The following code that was our first attempt doesn't work even though it compiles, but when trying to read the graphic it doesn't render the image and var img
comes out null.
I think it's really not possible, but I'll leave it as a query if anyone tries to use it.
FileInfo arquivoExcel = new FileInfo("CaminhoArquivo");
using (ExcelPackage package = new ExcelPackage(arquivoExcel))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
var img = (ExcelPicture)worksheet.Drawings["Chart 1"];
}
Aspose Cells
Another good option is Aspose Cells for .NET , but it's also not free and there are some limitations.
In the same GitHub project I made a method that also prints the image using the Aspose Cells component.
Example with Aspose Cells returning Model with list of printable graphics:
public ActionResult Index()
{
HomeModel model = new HomeModel();
model.ListaExcelChartImg = new List<byte[]>();
//Pegar o caminho do projeto
string path = Server.MapPath("~");
//Abrir arquivo excel com Aspose Cells
Workbook workbook = new Workbook(path + "\\Content\\column-chart.xlsx");
Worksheet worksheet = workbook.Worksheets[0];
foreach (var grafico in worksheet.Charts)
{
MemoryStream ms = new MemoryStream();
grafico.ToImage().Save(ms, ImageFormat.Jpeg);
byte[] bmpBytes = ms.ToArray();
model.ListaExcelChartImg.Add(bmpBytes);
}
return View(model);
}
Unfortunately I haven't found a completely free component that doesn't limit you in some aspect that renders the chart to excel. But here is a list of Nuget Packages that work with excel file .