如何使用EPPlus修改图表系列？

Question

我想询问如何使用EPPLUS库更新Excel中现有图表的系列值。

目前我还没有找到相关方法。

我已经成功创建了图表，但仍无法更新系列值。

请问有人能提供一些示例代码或参考链接吗？

- Anti Mafia

---

你究竟想要更新什么？EPPlus在图表系列方面的功能受到限制，因此这取决于你想要做什么。 - Chris

Answer 1

非常抱歉这么晚回复（我是在搜索其他内容时偶然看到了您的文章）。如果图表有一系列指向工作表中单元格的数据，为什么不直接更新单元格的值呢？首先创建饼图如下：

public void PieChartCreate()
{
    var file = new FileInfo(@"c:\temp\temp.xlsx");
    if (file.Exists)
        file.Delete();

    var pck = new ExcelPackage(file);
    var workbook = pck.Workbook;
    var worksheet = workbook.Worksheets.Add("newsheet");

    var data = new List<KeyValuePair<string, int>>
    {
        new KeyValuePair<string, int>("Group A", 44613),
        new KeyValuePair<string, int>("Group B", 36432),
        new KeyValuePair<string, int>("Group C", 6324),
        new KeyValuePair<string, int>("Group A", 6745),
        new KeyValuePair<string, int>("Group B", 23434),
        new KeyValuePair<string, int>("Group C", 5123),
        new KeyValuePair<string, int>("Group A", 34545),
        new KeyValuePair<string, int>("Group B", 5472),
        new KeyValuePair<string, int>("Group C", 45637),
        new KeyValuePair<string, int>("Group A", 37840),
        new KeyValuePair<string, int>("Group B", 20827),
        new KeyValuePair<string, int>("Group C", 4548),
    };

    //Fill the table
    var startCell = worksheet.Cells[1,1];
    startCell.Offset(0, 0).Value = "Group Name";
    startCell.Offset(0, 1).Value = "Value";

    for (var i = 0; i < data.Count(); i++)
    {
        startCell.Offset(i + 1, 0).Value = data[i].Key;
        startCell.Offset(i + 1, 1).Value = data[i].Value;
    }

    //Add the chart to the sheet
    var pieChart = worksheet.Drawings.AddChart("Chart1", eChartType.Pie);
    pieChart.SetPosition(data.Count + 1, 0, 0, 0);
    pieChart.Title.Text = "Test Chart";
    pieChart.Title.Font.Bold = true;
    pieChart.Title.Font.Size = 12;

    //Set the data range
    var series = pieChart.Series.Add(worksheet.Cells[2, 2, data.Count, 2], worksheet.Cells[2, 1, data.Count, 1]);
    var pieSeries = (ExcelPieChartSerie)series;
    pieSeries.Explosion = 5;

    //Format the labels
    pieSeries.DataLabel.Font.Bold = true;
    pieSeries.DataLabel.ShowValue = true;
    pieSeries.DataLabel.ShowPercent = true;
    pieSeries.DataLabel.ShowLeaderLines = true;
    pieSeries.DataLabel.Separator = ";";
    pieSeries.DataLabel.Position = eLabelPosition.BestFit;

    //Format the legend
    pieChart.Legend.Add();
    pieChart.Legend.Border.Width = 0;
    pieChart.Legend.Font.Size = 12;
    pieChart.Legend.Font.Bold = true;
    pieChart.Legend.Position = eLegendPosition.Right;

    pck.Save();

}

然后更新单元格：

public void PieChartUpdate()
{
    var file = new FileInfo(@"c:\temp\temp.xlsx");
    if (!file.Exists)
        return;

    var pck = new ExcelPackage(file);
    var workbook = pck.Workbook;
    var worksheet = workbook.Worksheets["newsheet"];

    var startCell = worksheet.Cells[2, 2];
    for (var i = 0; i < 12; i++)
    {
        startCell.Offset(i, 0).Value = ((double)startCell.Offset(i, 0).Value) * 100;
    }
    file.Delete();
    pck.Save();
}

如果您需要更改系列本身的定义，则按名称获取它：

public void PieChartUpdateSeries()
{
    var file = new FileInfo(@"c:\temp\temp.xlsx");
    if (!file.Exists)
        return;

    var pck = new ExcelPackage(file);
    var workbook = pck.Workbook;
    var worksheet = workbook.Worksheets["newsheet"];

    var piechart = worksheet.Drawings["Chart1"] as ExcelPieChart;
    var series = piechart.Series[0];
    series.XSeries = worksheet.Cells[2, 1, 7, 1].FullAddress;
    series.Series = worksheet.Cells[2, 2, 7, 2].FullAddress;

    file.Delete();
    pck.Save();
}

希望这能帮到您。

- Ernie S