Changing color of a line serie in EPPlus

Question

Is there an easier way to change the color of a line serie?

I tried using this. But the serieNode is producing a NullReferenceException at serieNode.AppendChild(spPr);.

Here's the code that generates the graph:

private void GenerateLicenseUsageStatsChart(FileInfo excelFileInfo, FileInfo 
                                           csvFileInfo, DateTime lastCheckedDate)
    {
        string worksheetsName = "Sheet1";
        const bool firstRowIsHeader = false;

        var excelTextFormat = new ExcelTextFormat { Delimiter = ',' };
        // excelTextFormat.EOL = "\r";

        using (ExcelPackage package = new ExcelPackage(excelFileInfo))
        {
            ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(worksheetsName);

            worksheet.Cells["A1"].LoadFromText(csvFileInfo, excelTextFormat, 
                      OfficeOpenXml.Table.TableStyles.None, firstRowIsHeader);

            var workbook = package.Workbook;
            ExcelWorksheet workSheet = workbook.Worksheets[1];

            var chart = workSheet.Drawings.AddChart("chart", 
                                              eChartType.ColumnClustered);
            chart.SetPosition(10, 250);
            chart.SetSize(700, 500);
            chart.Title.Text = $"LicenseUsageStats {lastCheckedDate:MMM} 
                         {lastCheckedDate.Year}";

            chart.YAxis.MajorUnit = 1;
            chart.YAxis.MinorUnit = 1;

            chart.XAxis.MajorTickMark = eAxisTickMark.None;
            chart.XAxis.MinorTickMark = eAxisTickMark.None;
            chart.XAxis.Title.Text = "Hour";
            chart.XAxis.Title.Font.Size = 10;

            var maxLicensesSerie = chart.Series.Add("B2:B25", "A2:A25");
            maxLicensesSerie.Header = "Max Licenses Used";

            var avgLicensesSerie = chart.Series.Add("C2:C25", "A2: A25");
            avgLicensesSerie.Header = "Avg Licenses Used";

            var maxLineChart = (ExcelLineChart) 
                            chart.PlotArea.ChartTypes.Add(eChartType.Line);
            var maxThreshLineSerie = maxLineChart.Series.Add("D2:D25", 
                                                             "A2:A25");
            maxThreshLineSerie.Header = "Max";

            SetLineChartColor(maxLineChart, 0, Color.Red);

            var warningLineChart = 
             (ExcelLineChart)chart.PlotArea.ChartTypes.Add(eChartType.Line);
            var warningThreshLineSerie = 
                 warningLineChart.Series.Add("E2:E25", "A2:A25");
            warningThreshLineSerie.Header = "Warning";

            SetLineChartColor(warningLineChart, 1, Color.Yellow);

            worksheet.Cells[worksheet.Dimension.Address].AutoFitColumns();

            // workSheet.Column(4).Hidden = true;
            // workSheet.Column(5).Hidden = true;

            package.Save();
        }
    }

My end goal:

Since excel doesn't have a neat way of creating horizontal lines, I created the max and warning columns. I would like to hide those columns as well, without them affecting the graph. Maybe I could "hide" them by moving them to another sheet?

Answer 1

I see the problem. The function assumes that the index of the serie matches the number of series in the chart object collection. But since it is a mixed chart that is not the case after casting the result of the Add. This is a bit of a hack but it will work (I really should think a little harder about how to match up the numbers):

public static void SetLineChartColor(this ExcelChart chart, int serieIdx, int chartSeriesIndex, Color color)
{
    var chartXml = chart.ChartXml;

    var nsa = chart.WorkSheet.Drawings.NameSpaceManager.LookupNamespace("a");
    var nsuri = chartXml.DocumentElement.NamespaceURI;

    var nsm = new XmlNamespaceManager(chartXml.NameTable);
    nsm.AddNamespace("a", nsa);
    nsm.AddNamespace("c", nsuri);

    var serieNode = chart.ChartXml.SelectSingleNode($@"c:chartSpace/c:chart/c:plotArea/c:lineChart/c:ser[c:idx[@val='{serieIdx}']]", nsm);
    var serie = chart.Series[chartSeriesIndex];
    var points = serie.Series.Length;

    //Add reference to the color for the legend
    var srgbClr = chartXml.CreateNode(XmlNodeType.Element, "srgbClr", nsa);
    var att = chartXml.CreateAttribute("val");
    att.Value = $"{color.R:X2}{color.G:X2}{color.B:X2}";
    srgbClr.Attributes.Append(att);

    var solidFill = chartXml.CreateNode(XmlNodeType.Element, "solidFill", nsa);
    solidFill.AppendChild(srgbClr);

    var ln = chartXml.CreateNode(XmlNodeType.Element, "ln", nsa);
    ln.AppendChild(solidFill);

    var spPr = chartXml.CreateNode(XmlNodeType.Element, "spPr", nsuri);
    spPr.AppendChild(ln);

    serieNode.AppendChild(spPr);
}

And you can call it like this:

maxLineChart.SetLineChartColor(2, 0, Color.Red);
warningLineChart.SetLineChartColor(3, 0, Color.Yellow);