EPPlus: ExcelChartSerie.Header loads once but doesn't update when cell is changed

Question

I am trying to create an EPPlus chart. I am having problems with series headers. Even when ExcelChartSerie.HeaderAddress is used, it seems to have no effect.

Each serie is initialized with the following piece of code

ExcelBarChartSerie serie = (OfficeOpenXml.Drawing.Chart.ExcelBarChartSerie)
                           chartClustered.Series.Add(ExcelRange.GetAddress(fromRow, fromCol, toRow, toCol),
                                                     ExcelRange.GetAddress(fromRow, fromColH, toRow, fromColH));

ExcelAddressBase headerAddr = new ExcelAddressBase(headRow, headCol, headRow, headCol);
serie.HeaderAddress = headerAddr;
serie.Header = (string)ws.Cells[headRow, headCol].Value;

Everything works great but I am having problems with the Header updating correctly with the rest of the graph. The serie.Header variable controls the legend, which is where I'm having the problem. I understand this is an awkwardly specific question, but perhaps someone out there can offer some insight. Here is some pictures to show you exactly where I am having my problem.

Initial plot before a change (correct):

Plot after label data changed (incorrect):

Answer 1

The problem is the you are creating a new Excel Address that is NOT attached to a worksheet when setting HeaderAddress. Its a very subtle but important difference because Excel would not know which sheet the address is actually associated with when looking for the header value (it would not assume the one the chart is on). Take a look at this:

[TestMethod]
public void ExcelChartSerie_Header()
{
    //http://stackoverflow.com/questions/27866521/epplus-excelchartserie-header-loads-once-but-doesnt-update-when-cell-is-change
    var existingFile = new FileInfo(@"c:\temp\temp.xlsx");
    if (existingFile.Exists)
        existingFile.Delete();

    using (var package = new ExcelPackage(existingFile))
    {
        var workbook = package.Workbook;
        var ws = workbook.Worksheets.Add("newsheet");

        //Some data
        ws.Cells["A1"].Value = "Stuff";
        ws.Cells["A2"].Value = "bar1";ws.Cells["A3"].Value = "bar2";ws.Cells["A4"].Value = "bar3";ws.Cells["A5"].Value = "bar4";ws.Cells["A6"].Value = "bar5";

        ws.Cells["C1"].Value = "Canadian";
        ws.Cells["C2"].Value = 53;ws.Cells["C3"].Value = 36;ws.Cells["C4"].Value = 43;ws.Cells["C5"].Value = 86;ws.Cells["C6"].Value = 86;

        ws.Cells["D1"].Value = "Saudi Arabia";
        ws.Cells["D2"].Value = 53;ws.Cells["D3"].Value = 36;ws.Cells["D4"].Value = 43;ws.Cells["D5"].Value = 86;ws.Cells["D6"].Value = 86;

        ws.Cells["E1"].Value = "Alaskan";
        ws.Cells["E2"].Value = 53; ws.Cells["E3"].Value = 36; ws.Cells["E4"].Value = 43; ws.Cells["E5"].Value = 86; ws.Cells["E6"].Value = 86;

        ws.Cells["F1"].Value = "Indian";
        ws.Cells["F2"].Value = 53; ws.Cells["F3"].Value = 36; ws.Cells["F4"].Value = 43; ws.Cells["F5"].Value = 86; ws.Cells["F6"].Value = 86;


        //Create the chart
        var chart = (ExcelBarChart)ws.Drawings.AddChart("Chart1", eChartType.ColumnClustered);
        chart.SetSize(400, 300);
        chart.SetPosition(10, 400);

        //Apply header
        for (var i = 0; i < 4; i++)
        {
            var serie = (ExcelBarChartSerie) chart.Series.Add(
                ExcelCellBase.GetAddress(2, i + 3, 6, i + 3),
                ExcelCellBase.GetAddress(2, 1, 6, 1)
            );

            //var headerAddr = new ExcelAddressBase("C1");  //THIS IS NOT ASSOCIATED WITH WORKSHET 'ws'
            var headerAddr = ws.Cells[1, i + 3]; //THIS IS
            serie.HeaderAddress = headerAddr;
            //serie.Header = (string) ws.Cells[1, i + 3].Value;
        }
        package.Save();

    }
}