EPPlus how to change the colors of a PIE chart in EXCEL

Question

How to change the standard colors of an Excel chart using EPPlus programmatically.

below is my code

var pieChart = worksheet.Drawings.AddChart("piechart", eChartType.Pie3D) as ExcelPieChart; //Set top left corner to row 1 column 2 pieChart.SetPosition(18, 0, 0, 0); pieChart.SetSize(350, 300); pieChart.Series.Add(ExcelRange.GetAddress(12, 2, 15, 2),ExcelRange.GetAddress(12, 1, 15, 1) ); pieChart.Legend.Position = eLegendPosition.Bottom; pieChart.Legend.Border.Fill.Color = Color.Green; pieChart.Legend.Border.LineStyle = eLineStyle.Solid; pieChart.Legend.Border.Fill.Style = eFillStyle.SolidFill; pieChart.Title.Text = "Current Status"; pieChart.DataLabel.ShowCategory = false; pieChart.DataLabel.ShowPercent = true; 

I want to change the default colors to bright colors.

Offer and shed light on this.

Answer 1

Inspired by Ernie, answer, here is an extension method that works to set the color and thickness of a linear graphical series, and a non-tested version to set the color of a data point in a pie chart:

 public static void SetSeriesStyle(this ExcelLineChart chart, ExcelChartSerie series, Color color, decimal? thickness = null) { if (thickness < 0) throw new ArgumentOutOfRangeException("thickness"); 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."); //Get the nodes 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:lineChart/c:ser[c:idx[@val='" + i.ToString(CultureInfo.InvariantCulture) + "']]", nsm); var doc = chart.ChartXml; //Add the solid fill node var spPr = doc.CreateElement("c:spPr", nschart); var ln = spPr.AppendChild(doc.CreateElement("a:ln", nsa)); if (thickness.HasValue) { var w = ln.Attributes.Append(doc.CreateAttribute("w")); w.Value = Math.Round(thickness.Value * 12700).ToString(CultureInfo.InvariantCulture); var cap = ln.Attributes.Append(doc.CreateAttribute("cap")); cap.Value = "rnd"; } var solidFill = ln.AppendChild(doc.CreateElement("a:solidFill", nsa)); var srgbClr = solidFill.AppendChild(doc.CreateElement("a:srgbClr", nsa)); var valattrib = srgbClr.Attributes.Append(doc.CreateAttribute("val")); //Set the color valattrib.Value = color.ToHex().Substring(1); node.AppendChild(spPr); } public static void SetDataPointStyle(this ExcelPieChart chart, int dataPointIndex, Color color) { //Get the nodes 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:pieChart/c:ser", nsm); var doc = chart.ChartXml; //Add the node //Create the data point node var dPt = doc.CreateElement("c:dPt", nschart); var idx = dPt.AppendChild(doc.CreateElement("c:idx", nschart)); var valattrib = idx.Attributes.Append(doc.CreateAttribute("val")); valattrib.Value = dataPointIndex.ToString(CultureInfo.InvariantCulture); node.AppendChild(dPt); //Add the solid fill node 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)); valattrib = srgbClr.Attributes.Append(doc.CreateAttribute("val")); //Set the color valattrib.Value = color.ToHex().Substring(1); dPt.AppendChild(spPr); } public static String ToHex(this Color c) { return "#" + cRToString("X2") + cGToString("X2") + cBToString("X2"); } 

Using:

 lineChart.SetSeriesStyle(s, color: Color.FromArgb(0, 0, 0), thickness: 6m); pieChart.SetDataPointStyle(dataPointIndex: 0, color: Color.FromArgb(0, 0, 0)); 

Answer 2

Just thought that I would return a little, because I ran into a similar problem. EEPlus' short answer does not support the ability to change the colors of individual slices, so I had to rely on xml manipulation. Not really and requires a good knowledge of the data that you output - you need to know the number of expected fragments. But it works, and this should be applicable to other types of pie charts except 3D.

Here is a test method that demonstrates. This was against EPP 4.0.1, but should work just as well with previous versions:

 [TestMethod] public void Change_3DPieChart_Color() { const string PIE_PATH = "c:chartSpace/c:chart/c:plotArea/c:pie3DChart/c:ser"; var existingFile = new FileInfo(@"c:\temp\temp.xlsx"); if (existingFile.Exists) existingFile.Delete(); using (var package = new ExcelPackage(existingFile)) { var workbook = package.Workbook; var worksheet = workbook.Worksheets.Add("newsheet"); //Some data worksheet.Cells["A12"].Value = "wer"; worksheet.Cells["A13"].Value = "sdf"; worksheet.Cells["A14"].Value = "wer"; worksheet.Cells["A15"].Value = "ghgh"; worksheet.Cells["B12"].Value = 53; worksheet.Cells["B13"].Value = 36; worksheet.Cells["B14"].Value = 43; worksheet.Cells["B15"].Value = 86; //Create the pie var pieChart = (ExcelPieChart) worksheet.Drawings.AddChart("piechart", eChartType.Pie3D); //Set top left corner to row 1 column 2 pieChart.SetPosition(18, 0, 0, 0); pieChart.SetSize(350, 300); pieChart.Series.Add(ExcelCellBase.GetAddress(12, 2, 15, 2), ExcelCellBase.GetAddress(12, 1, 15, 1)); pieChart.Legend.Position = eLegendPosition.Bottom; pieChart.Legend.Border.Fill.Color = Color.Green; pieChart.Legend.Border.LineStyle = eLineStyle.Solid; pieChart.Legend.Border.Fill.Style = eFillStyle.SolidFill; pieChart.Title.Text = "Current Status"; pieChart.DataLabel.ShowCategory = false; pieChart.DataLabel.ShowPercent = true; //Get the nodes var ws = pieChart.WorkSheet; var nsm = ws.Drawings.NameSpaceManager; var nschart = nsm.LookupNamespace("c"); var nsa = nsm.LookupNamespace("a"); var node = pieChart.ChartXml.SelectSingleNode(PIE_PATH, nsm); var doc = pieChart.ChartXml; //Add the node var rand = new Random(); for (var i = 0; i < 4; i++) { //Create the data point node var dPt = doc.CreateElement("dPt", nschart); var idx = dPt.AppendChild(doc.CreateElement("idx", nschart)); var valattrib = idx.Attributes.Append(doc.CreateAttribute("val")); valattrib.Value = i.ToString(CultureInfo.InvariantCulture); node.AppendChild(dPt); //Add the solid fill node var spPr = doc.CreateElement("spPr", nschart); var solidFill = spPr.AppendChild(doc.CreateElement("solidFill", nsa)); var srgbClr = solidFill.AppendChild(doc.CreateElement("srgbClr", nsa)); valattrib = srgbClr.Attributes.Append(doc.CreateAttribute("val")); //Set the color var color = Color.FromArgb(rand.Next(256), rand.Next(256), rand.Next(256)); valattrib.Value = ColorTranslator.ToHtml(color).Replace("#", String.Empty); dPt.AppendChild(spPr); } package.Save(); } }