EPPlus ColumnStacked chart data point colors

Question

I am able to generate Column Stacked chart using EPPlus. There is is requirement to change the color of datapoint.

I found the solution of at enter link description here but it only changes the color of first datapoint of the series. Can I get help to change the color of other datapoints as well. Here is the concept that I am looking for enter image description here

Here is the function that helps to change datapoint first color

    public void SetDataPointStyle(OfficeOpenXml.Drawing.Chart.ExcelChart chart, ExcelChartSerie series, int totalDataPoint, Color color)
{
    var i = 0;
    var found = false;
    foreach (var s in chart.Series)
    {
        if (s == series)
        {
            found = true;
            break;
        }
        ++i;
    }
    if (!found) throw new InvalidOperationException("series not found.");

    var nsm = chart.WorkSheet.Drawings.NameSpaceManager;
    var nschart = nsm.LookupNamespace("c");
    var nsa = nsm.LookupNamespace("a");
    var node = chart.ChartXml.SelectSingleNode(@"c:chartSpace/c:chart/c:plotArea/c:barChart/c:ser[c:idx[@val='" + i.ToString(System.Globalization.CultureInfo.InvariantCulture) + "']]", nsm);
    var doc = chart.ChartXml;

    var spPr = doc.CreateElement("c:spPr", nschart);
    var solidFill = spPr.AppendChild(doc.CreateElement("a:solidFill", nsa));
    var srgbClr = solidFill.AppendChild(doc.CreateElement("a:srgbClr", nsa));
    var valattrib = srgbClr.Attributes.Append(doc.CreateAttribute("val"));
    valattrib.Value = ToHex(color).Substring(1);

    //var ln = spPr.AppendChild(doc.CreateElement("a:ln", nsa));
    //var lnSolidFill = ln.AppendChild(doc.CreateElement("a:solidFill", nsa));
    //var lnSrgbClr = lnSolidFill.AppendChild(doc.CreateElement("a:srgbClr", nsa));
    //var lnValattrib = lnSrgbClr.Attributes.Append(doc.CreateAttribute("val"));
    //lnValattrib.Value = ToHex(Color.Gray).Substring(1);

    node.AppendChild(spPr);
}
public String ToHex(Color c)
{
    return "#" + c.R.ToString("X2") + c.G.ToString("X2") + c.B.ToString("X2");
}

SetDataPointStyle(chart, chart.Series[0], 1, Color.Tan);

Multicolor

Probably need to loop through the nodes. Post your code that you have so far which will make it much easier for others to add to it. – Mug

Edited question with code – Multicolor

#25623824 there is a sample on how to do it you have to iterate chart.Series – Abed

Answer 1

You have to populate a series of data point colors per series. Here is an extension method that will set the series data points to random colors. Just have to specify the serie number. If pick your own colors just override the logic or send in an array to use:

public static void SetChartPointRandomColors(this ExcelChart chart, int serieNumber)
{
    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:barChart/c:ser[c:idx[@val='" + serieNumber + "']]", nsm);
    var serie = chart.Series[serieNumber];
    var points = serie.Series.Length;
    var rand = new Random(serieNumber);

    for (var i = 1; i <= points; i++)
    {
        var dPt = chartXml.CreateNode(XmlNodeType.Element, "dPt", nsuri);
        var idx = chartXml.CreateNode(XmlNodeType.Element, "idx", nsuri);
        var att = chartXml.CreateAttribute("val", nsuri);
        att.Value = i.ToString();
        idx.Attributes.Append(att);
        dPt.AppendChild(idx);

        var srgbClr = chartXml.CreateNode(XmlNodeType.Element, "srgbClr", nsa);
        att = chartXml.CreateAttribute("val");

        //Generate a random color - override with own logic to specify
        var color = Color.FromArgb(rand.Next(256), rand.Next(256), rand.Next(256));
        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 spPr = chartXml.CreateNode(XmlNodeType.Element, "spPr", nsuri);
        spPr.AppendChild(solidFill);

        dPt.AppendChild(spPr);
        serieNode.AppendChild(dPt);
    }
}

Here is an example of usage:

[TestMethod]
public void Chart_BarChart_Colors_Test()
{
    //Throw in some data
    var datatable = new DataTable("tblData");
    datatable.Columns.AddRange(new[]{new DataColumn("Col1", typeof(int)),new DataColumn("Col2", typeof(int)),new DataColumn("Col3", typeof(int))});
    for (var i = 0; i < 10; i++){var row = datatable.NewRow();row[0] = i;row[1] = i * 10;row[2] = i * 15;datatable.Rows.Add(row);}

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

    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.ColumnStacked);
        chart.Series.Add(worksheet.Cells["B2:B11"], worksheet.Cells["A2:A11"]);
        chart.Series.Add(worksheet.Cells["C2:C11"], worksheet.Cells["A2:A11"]);

        chart.SetChartPointRandomColors(0);
        chart.SetChartPointRandomColors(1);

        pck.Save();
    }
}

Will give you this:

Mug

For an improved version of this that accounts for the legend colors see this post: #36520927 – Mug

Very helpful. But if Y-Axis has negative values, chart bar color turns to blank or white. – Watercourse

Answer 2

I had a similar use case, I needed to set the color of a slice (datapoint) of a doughnut chart. This question/answer helped immensely and I figured I would share the result in case anyone else hits this issue.

Note 1: I am using C# 9 with nullability enabled; you can remove the !'s if you aren't using nullability.

Note 2: I have no use case for multiple series in a doughnut chart, so this is hardcoded to series 0. You can parameterize the SelectSingleNode index if this doesn't work for you.

    public void SetDoughnutChartDataPointFill(ExcelChart chart, int dataPointIdx, Color color)
    {
        var nsm = chart.WorkSheet.Drawings.NameSpaceManager;
        var nschart = nsm.LookupNamespace("c");
        var nsa = nsm.LookupNamespace("a");
        var node = chart.ChartXml.SelectSingleNode(@"c:chartSpace/c:chart/c:plotArea/c:doughnutChart/c:ser[c:idx[@val='0']]", nsm)!;
        var doc = chart.ChartXml;
        var dPt = doc.CreateElement("c:dPt", nschart);
        var cdpIdx = doc.CreateElement("c:idx", nschart);
        var valattr = cdpIdx.Attributes!.Append(doc.CreateAttribute("val"));
        valattr.Value = dataPointIdx.ToString();
        dPt.AppendChild(cdpIdx);

        var spPr = doc.CreateElement("c:spPr", nschart);
        var solidFill = spPr.AppendChild(doc.CreateElement("a:solidFill", nsa))!;
        var srgbClr = solidFill.AppendChild(doc.CreateElement("a:srgbClr", nsa))!;
        var valattrib = srgbClr.Attributes!.Append(doc.CreateAttribute("val"));
        valattrib.Value = string.Format("{0:X2}{1:X2}{2:X2}", color.R, color.G, color.B);
        dPt.AppendChild(spPr);

        node.AppendChild(dPt);
    }

Mccune