Export charts from an Excel worksheet without Interop

6

I need to read and write data from Excel spreadsheets, and export some graphics, using C #. But I can not use the interop library ( Microsoft.Office.Interop ) for this.

How can I do it? Does anyone have any nuGet package indication for this?

I know EPPlus, I've done some work with it, but it can not export graphics.

I know that with EPPlus it is possible to write and read data from the worksheet, and even create graphs, but the problem is when I export the graphs, I need to export the existing graph to an existing excel for JPG, PNG or even in PDF. But preferably for JPG.

    
asked by anonymous 03.12.2018 / 15:14

2 answers

6

Spire.XLS

I made a solution using the component Spire.XLS or this developer site is and there is a good documentation about the component .

You can install Spire.XLS by Nuget :

  

Install-Package Spire.XLS -Version 8.11.6

Here is an example of how I converted a bar chart from excel 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);
    }
}

Design with examples in GitHub

I made a repository in GitHub with a web project that reads an excel file with a graph of columns and converts it to image and prints on screen.

In the example, if you access the Home / Index controler you will find how to generate the image and save to file or memory ( byte[] ) and print it in View >.

In the excel file there is only one graphic, but if others have been created they will all be printed.

Note: The problem is that the free version of this component limits you to some things. If I 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 you read about the limitations on their site and make tests with reading larger and more spreadsheets graphic.

Other components:

EPPlus

With EPPlus I could not find a way to render the image. The following code that was ours first attempt does not work despite compiling, but when trying to read the graph it does not render the image and var img comes null.

I do not think it's really possible, but I will 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 the Aspose Cells for .NET , but it's not free either 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 graphics for printing:

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 have not found a totally free component that does not limit you to some aspect that renders the graph to excel. But here's a list of Nuget Packages that work with excel file .

    
07.12.2018 / 19:22
3

You can generate graphics using EPPlus, see an example:

        //Criar o relatório
        var pieChart = (ExcelPieChart)ws.Drawings.AddChart("crtExtensionsSize", eChartType.PieExploded3D);
        pieChart.SetPosition(1, 0, 2, 0);
        pieChart.SetSize(400, 400);
        //Adicionar as séries
        pieChart.Series.Add(ExcelRange.GetAddress(3, 2, row-1, 2), ExcelRange.GetAddress(3, 1, row-1, 1));
        //Adicionar titulo e configurar o relatório
        pieChart.Title.Text = "Extension Size";
        pieChart.DataLabel.ShowCategory = true;
        pieChart.DataLabel.ShowPercent = true;
        pieChart.DataLabel.ShowLeaderLines = true;
        pieChart.Legend.Remove();

You can see more information at the EPPlus Wiki

    
04.12.2018 / 14:54