CODEWITHSUNDEEP
c#xmlexcelgraphepplus
jnel899
jnel899

Reputation: 583

Is there a way to set gridline options for Excel graphs using C# / EPPlus

I am trying to create an excel Graph using EPPlus and C#. I can successfully create the following Graph: Graph Created However, I would like the program to create both major and minor gridlines in both directions at runtime; so that the graph looks like this: Graph Desired

I know I can adjust these settings in Excel, but I would like to be able to set the default gridlines with my code. I cannot find a setting in EPPlus that will handle this (If there is one, please make me aware of it). I am trying to figure out how to customize the XML directly but I'm having trouble with that as well.

Is there a way I can accomplish this? Any help is appreciated.

Upvotes: 1

Views: 2971

Answers (1)

Ernie S
Ernie S

Reputation: 14250

It is a bit strange that was not added since it is fairly easy to turn on. Here is how to do it with XML (if you want to set color and size you have to get more into the XML manipulation but this will get you started):

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

    var chart = worksheet.Drawings.AddChart("chart test", eChartType.XYScatter);
    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("c", nsuri);

    //XY Scatter plots have 2 value axis and no category
    var valAxisNodes = chartXml.SelectNodes("c:chartSpace/c:chart/c:plotArea/c:valAx", nsm);
    if (valAxisNodes != null && valAxisNodes.Count > 0)
        foreach (XmlNode valAxisNode in valAxisNodes)
        {
            if (valAxisNode.SelectSingleNode("c:majorGridlines", nsm) == null)
                valAxisNode.AppendChild(chartXml.CreateNode(XmlNodeType.Element, "c:majorGridlines", nsuri));
            if (valAxisNode.SelectSingleNode("c:minorGridlines", nsm) == null)
                valAxisNode.AppendChild(chartXml.CreateNode(XmlNodeType.Element, "c:minorGridlines", nsuri));
        }

    //Other charts can have a category axis
    var catAxisNodes = chartXml.SelectNodes("c:chartSpace/c:chart/c:plotArea/c:catAx", nsm);
    if (catAxisNodes != null && catAxisNodes.Count > 0)
        foreach (XmlNode catAxisNode in catAxisNodes)
        {
            if (catAxisNode.SelectSingleNode("c:majorGridlines", nsm) == null)
                catAxisNode.AppendChild(chartXml.CreateNode(XmlNodeType.Element, "c:majorGridlines", nsuri));
            if (catAxisNode.SelectSingleNode("c:minorGridlines", nsm) == null)
                catAxisNode.AppendChild(chartXml.CreateNode(XmlNodeType.Element, "c:minorGridlines", nsuri));
        }

    pck.Save();
}

Upvotes: 1

Related Questions