How can we create charts in excel using closedxml

Question

How to create charts in excel,While exporting data from database to excel sheet..!

I'm exporting data to excel from database.I need to create chart in the excel sheet based on data from database. Below code creates simple excel sheet I need to make changes in the same code to create charts. Please Guide

    public ActionResult ExportToExcel()
    {

            var workbook = new ClosedXML.Excel.XLWorkbook();
            var worksheet = workbook.Worksheets.Add("SheetOne");
            DocumentFormat.OpenXml.Drawing.PictureLocks piclocks = new DocumentFormat.OpenXml.Drawing.PictureLocks();
            worksheet.Cell("A1").Value = "Test data!";
            Response.ClearContent();
            Response.Buffer = true;
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            Response.AddHeader("content-disposition", "attachment;filename=\"SheetOne.xlsx\"");
            System.IO.MemoryStream memoryStream = new System.IO.MemoryStream();
           workbook.SaveAs(memoryStream);
            memoryStream.WriteTo(Response.OutputStream);
            memoryStream.Close();
            Response.End();
            return File(memoryStream, "application/ms-excel", "PPs_Workflow.xlsx");

    } *

ClosedXML doesn't support charts. – Raidri Jul 06 '16 at 08:48 — Raidri, Jul 06 '16 at 08:48
https://github.com/ClosedXML/ClosedXML/issues/50 – Mauricio Scheffer Jul 21 '20 at 08:36 — Mauricio Scheffer, Jul 21 '20 at 08:36

score 6 · Answer 1 · answered Apr 06 '17 at 15:50

Unfortunately, to my knowledge, ClosedXML doesn't support adding charts.

So in order to accomplish what you need, you'll need to create an Excel file (to use as a template) and add the chart(s) you need manually. After that, you can use ClosedXML to open and edit the existing file (as opposed to creating a new file as you're currently doing). When the data updates, the chart will automatically update along with it.

score 3 · Answer 2 · answered Mar 29 '18 at 07:30

You can use OfficeOpenXml, Here you can get a wide variety of charts with some flexible options

*ExcelChart chart = chartSheet.Drawings.AddChart("MyChart",
OfficeOpenXml.Drawing.Chart.eChartType.ColumnClustered);
chart.Title.Text = "Category Chart";
chart.SetPosition(1, 0, 3, 0);
chart.SetSize(800, 300);
var ser1 = (ExcelChartSerie)(chart.Series.Add(workSheet.Cells["B2:B6"],
workSheet.Cells["A2:A6"]));*

score 3 · Answer 3 · answered Sep 16 '21 at 17:00

Although ClosedXML may not support charting, OpenXML does. Since ClosedXML is a simplified interface to OpenXML, there is no reason that I can see that you cannot mix them. (You automatically get an OpenXML reference when you pull ClosedXML from nuget). Like everything in OpenXML, it is kind of ugly. (That is why ClosedXML is so popular)

I found some examples of using OpenXML at https://csharp.hotexamples.com/examples/DocumentFormat.OpenXml.Drawing.Charts/PieChart/-/php-piechart-class-examples.html

How can we create charts in excel using closedxml

3 Answers3