Add chart element Data table with legend

Question

We are able to generate a chart in the excel sheet using EPPlus. There is requirement to add chart element >> Data table with legend. Is there any way to add this using EPPlus? You can review the chart that I am looking for here

Answer 1

EPPlus does not support adding a data table to a chart. You can turn it on fairly easily with some XML manipulation however if you want to style it you will need to get a bit more into it. Best to create the chart the way you like it, save it and open the XLSX as a ZIP file and look at the chart XML.

This is how you show the default Data Table with Legend to get you started:

using (var pck = new ExcelPackage(fileInfo))
{
    var workbook = pck.Workbook;
    var worksheet = workbook.Worksheets.Add("Sheet1");
    worksheet.Cells.LoadFromDataTable(datatable, true);

    //Assume some data range
    var chart = worksheet.Drawings.AddChart("chart test", eChartType.Area);
    var series = chart.Series.Add(worksheet.Cells["B2:B11"], worksheet.Cells["A2:A11"]);

    //Get reference to the worksheet xml for proper namespace
    var chartXml = chart.ChartXml;
    var nsuri = chartXml.DocumentElement.NamespaceURI;
    var nsm = new XmlNamespaceManager(chartXml.NameTable);
    nsm.AddNamespace("default", nsuri);

    //Add the table node
    var plotAreaNode = chartXml.SelectSingleNode("/default:chartSpace/default:chart/default:plotArea", nsm);
    var dTableNode = chartXml.CreateNode(XmlNodeType.Element, "dTable", nsuri);
    plotAreaNode.AppendChild(dTableNode);

    //With Legend Flag
    var att = chartXml.CreateAttribute("val");
    att.Value = "1";

    var showKeysNode = chartXml.CreateNode(XmlNodeType.Element, "showKeys", nsuri);
    showKeysNode.Attributes.Append(att);
    dTableNode.AppendChild(showKeysNode);

    pck.Save();
}