EPPlus如何在Excel中更改饼图的颜色

Question

如何使用EPPlus通过编程方式更改Excel饼图的默认颜色。

以下是我的代码

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;

我希望将默认颜色更改为一些明亮的颜色。

请提出建议并对此进行说明。

- PRAVEEN SARAF

Answer 1

受Ernie答案的启发，这是一个扩展方法，可用于设置线图系列的颜色和粗细，以及用于设置饼图数据点颜色的未测试版本：

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 "#" + c.R.ToString("X2") + c.G.ToString("X2") + c.B.ToString("X2");
}

使用方法：

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

- Erwin Mayer

---

虽然有点晚了，但是对于 PieChart 的 xml 文件存在一个拼写错误。应该是 c:pieChart 而不是 c:lineChart，但是这个扩展非常棒。 - Steven

谢谢@Steven，我已经修复了这个问题。 - Erwin Mayer

Answer 2

我想作为回馈，介绍一下我的经验。简短的回答是，EEPlus不支持更改单个饼图片段的颜色，因此我必须依赖xml操作。虽然不太优美并且需要好的数据输出知识，但它确实有效，并且适用于除3D之外的其他饼图类型。

以下是演示的测试方法。我在EPP 4.0.1上进行了测试，但在之前的版本上同样适用：

[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();

    }
}

- Ernie S