EPPlus Custom XYScatterLines Chart Axes Formats VB.Net

Question

Ernie's post in this question C# Change Excel Chart Axis Format using EPPLUS was very informative and nearly got me to where I want to be, which is to customise the X and Y chart axes formats for a XYScatterLines graph independently from format of the data source ranges.

I worked out that there are two value axes (valAx) nodes each uniquely identified with ID childnodes as follows axId val="1" and axId val="2"

Based on an earlier question also answered by Ernie (Is there a way to set gridline options for Excel graphs using C# / EPPlus) I thought it would be easy to get each valAx node, find the respective numFmt node and add the 'sourceLinked=0' attribute to it by doing something like this (VB.net):

        Dim att = xdoc.CreateAttribute("sourceLinked")
        att.Value = "0"
        Dim valAxisNodes = xdoc.SelectNodes("/c:chartSpace/c:chart/c:plotArea/c:valAx", nsm)
        If valAxisNodes IsNot Nothing AndAlso valAxisNodes.Count > 0 Then
            For Each valAxisNode As System.Xml.XmlNode In valAxisNodes
                If valAxisNode.SelectSingleNode("c:numFmt", nsm) IsNot Nothing Then
                    valAxisNode.SelectSingleNode("c:numFmt", nsm).Attributes.Append(att)
                End If
            Next
        End If

But it doesn't work. Two nodes are returned as expected, and the attribute is added to the child numFmt node for each one. But in the saved XML, only one of the valAx nodes is changed. The other stubbornly remains the same. I've tried a zillion different ways but nothing works. Can anyone enlighten me as to why?

Answer 1

Two things to do. 1 - make sure you EXPLICITLY set the format of both axis otherwise you will be missing the actual numFmt nodes so you test for not null will fail. 2 - You cannot use the same att instance twice so you need to make a copy for each axis inside your for loop.

I am way rusty at VB so here it is in C#:

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

    worksheet.Cells["A2:A11"].Style.Numberformat.Format = "m/d/yyyy";
    var chart = worksheet.Drawings.AddChart("chart ", eChartType.XYScatterLines);
    var series = chart.Series.Add(worksheet.Cells["B2:B11"], worksheet.Cells["A2:A11"]);

    //MUST BE EXPICIT otherwise the numFmt will not be generated by EPPLUS
    chart.XAxis.Format = "yyyy-mm-dd";
    chart.YAxis.Format = "General";

    var xdoc = chart.ChartXml;
    var nsm = new XmlNamespaceManager(xdoc.NameTable);
    nsm.AddNamespace("c", xdoc.DocumentElement.NamespaceURI);

    var valAxisNodes = xdoc.SelectNodes("/c:chartSpace/c:chart/c:plotArea/c:valAx", nsm);
    if (valAxisNodes != null && valAxisNodes.Count > 0)
        foreach (XmlNode valAxisNode in valAxisNodes)
        {
            //MUST create an attribute copy for each axis
            var att = xdoc.CreateAttribute("sourceLinked");
            att.Value = "0";
            if (valAxisNode.SelectSingleNode("c:numFmt", nsm) != null)
                valAxisNode.SelectSingleNode("c:numFmt", nsm).Attributes.Append(att);
        }

    pck.Save();
}