c# Merged cells as header address epplus

Question

How do I set HeaderAddress to two cells of a clustered bar chart in EPPlus? I have the below data from my database output, where first column is as you can see merged cells.

I am looking for the following layout of the data

Please take note that the images are from a replica of the data, but generated inside Excel.

What I've tried so far is basically

ExcelChartSerie s = chart.Series.Add(axis.Address, xAxis.Address);
s.HeaderAddress = new ExcelAddress(startRow + r, GetColumnNumberByName(startColumn), startRow + 1, GetColumnNumberByName(startColumn) + 1);

where I more or less select the current row and two columns. This gives me "Address must be a row, column or single cell", but in order to get this to work, I must select multiple cells, no?

Ernie S Ernie S · Accepted Answer · 2016-02-24T02:05:40

Epplus does not have the ability to set it. it is not so difficult but it requires XML manipulation. Kind of ugly but it gets the job done. Not knowing your code I made up a quick unit test that demos. It has to match to the right chart type so if it is not BarClustered let me know:

[TestMethod]
public void Chart_Meged_Header_Test()
{
    //Throw in some data
    var datatable = new DataTable("tblData");
    datatable.Columns.AddRange(new[]
    {
        new DataColumn("Col1", typeof (string)),
        new DataColumn("Col2", typeof (int))
    });

    for (var i = 0; i < 10; i++)
    {
        var row = datatable.NewRow();
        row[0] = $"item {(i%2 == 0 ? "A" : "B")}";
        row[1] = i * 10;
        datatable.Rows.Add(row);
    }

    //Create a test file    
    var fileInfo = new FileInfo(@"c:\temp\Chart_Meged_Header_Test.xlsx");
    if (fileInfo.Exists)
        fileInfo.Delete();

    using (var pck = new ExcelPackage(fileInfo))
    {
        var workbook = pck.Workbook;
        var worksheet = workbook.Worksheets.Add("Sheet1");
        worksheet.Cells["B1"].LoadFromDataTable(datatable, true);
        worksheet.Column(4).Style.Numberformat.Format = "m/d/yyyy";

        var chart = worksheet.Drawings.AddChart("chart test", eChartType.BarClustered);
        var serie = chart.Series.Add(worksheet.Cells["C2:C11"], worksheet.Cells["B2:B11"]);
        chart.SetPosition(0, 0, 3, 0);
        chart.SetSize(120);

        //Add merged headers
        worksheet.Cells["A2"].Value = "Group 1";
        worksheet.Cells["A2:A6"].Merge = true;

        worksheet.Cells["A7"].Value = "Group 2";
        worksheet.Cells["A7:A11"].Merge = true;

        //Get reference to the worksheet xml for proper namespace
        var chartXml = chart.ChartXml;
        var nsm = new XmlNamespaceManager(chartXml.NameTable);

        var nsuri = chartXml.DocumentElement.NamespaceURI;
        nsm.AddNamespace("c", nsuri);

        //Get the Series ref and its cat
        var serNode = chartXml.SelectSingleNode("c:chartSpace/c:chart/c:plotArea/c:barChart/c:ser", nsm);
        var catNode = serNode.SelectSingleNode("c:cat", nsm);

        //Get Y axis reference to replace with multi level node
        var numRefNode = catNode.SelectSingleNode("c:numRef", nsm);
        var multiLvlStrRefNode = chartXml.CreateNode(XmlNodeType.Element, "c:multiLvlStrRef", nsuri);

        //Set the proper cell reference and replace the node
        var fNode = chartXml.CreateElement("c:f", nsuri);
        fNode.InnerXml = numRefNode.SelectSingleNode("c:f", nsm).InnerXml;
        fNode.InnerXml = fNode.InnerXml.Replace("$B$2", "$A$2");
        multiLvlStrRefNode.AppendChild(fNode);
        catNode.ReplaceChild(multiLvlStrRefNode, numRefNode);

        //Set the multi level flag
        var noMultiLvlLblNode = chartXml.CreateElement("c:noMultiLvlLbl", nsuri);
        var att = chartXml.CreateAttribute("val");
        att.Value = "0";
        noMultiLvlLblNode.Attributes.Append(att);

        var catAxNode = chartXml.SelectSingleNode("c:chartSpace/c:chart/c:plotArea/c:catAx", nsm);
        catAxNode.AppendChild(noMultiLvlLblNode);

        pck.Save();
    }
}

Gives this as the output:

c# Merged cells as header address epplus

1 Answers