使用NPOI库操作Chart图表使用心得

最近使用NPOI处理Chart图表时遇到了一些问题。

以下做一些总结,先上完整代码

static void Main(string[] args)
{
    IWorkbook workbook = new XSSFWorkbook();
    ISheet sheet = workbook.CreateSheet("Sheet1");

    // 创建数据
    IRow row = sheet.CreateRow(0);
    row.CreateCell(0).SetCellValue("X");
    row.CreateCell(1).SetCellValue("Y");

    for (int i = 1; i <= 10; i++)
    {
        row = sheet.CreateRow(i);
        row.CreateCell(0).SetCellValue(i);
        row.CreateCell(1).SetCellValue(i * i); // Y = X^2
    }

    // 创建图表
    XSSFDrawing drawing = (XSSFDrawing)sheet.CreateDrawingPatriarch();
    XSSFClientAnchor anchor = (XSSFClientAnchor)drawing.CreateAnchor(0, 0, 0, 0, 0, 12, 10, 30);

    XSSFChart chart = (XSSFChart)drawing.CreateChart(anchor);
    chart.SetTitle("Scatter Plot");

    IChartDataFactory dataFactory = chart.ChartDataFactory;
    IChartAxisFactory axisFactory = chart.ChartAxisFactory;

    IChartAxis categoryAxis = axisFactory.CreateCategoryAxis(AxisPosition.Bottom);
    IChartAxis valueAxis = axisFactory.CreateValueAxis(AxisPosition.Left);
    valueAxis.Crosses = AxisCrosses.AutoZero;

    IScatterChartData<double, double> data = dataFactory.CreateScatterChartData<double, double>();

    IChartDataSource<double> xs = DataSources.FromNumericCellRange(sheet, new CellRangeAddress(1, 10, 0, 0));
    IChartDataSource<double> ys = DataSources.FromNumericCellRange(sheet, new CellRangeAddress(1, 10, 1, 1));

    IScatterChartSeries<double, double> series = data.AddSeries(xs, ys);
    series.SetTitle("Sample Series");

    chart.Plot(data, categoryAxis, valueAxis);

    // 设置系列线条颜色和宽度
    var ctScatterChart = chart.GetCTChart().plotArea.scatterChart[0];
    var ctSer = ctScatterChart.ser[0];
    if(ctSer.spPr == null)
    {
        ctSer.spPr=new NPOI.OpenXmlFormats.Dml.Chart.CT_ShapeProperties();
    }
    var lineProperties = ctSer.spPr.AddNewLn();
    lineProperties.solidFill = new NPOI.OpenXmlFormats.Dml.CT_SolidColorFillProperties();
    lineProperties.solidFill.srgbClr = new NPOI.OpenXmlFormats.Dml.CT_SRgbColor() { val = new byte[] { 255, 0, 0 } }; // 红色
    lineProperties.w = (int)(12700*1.5); // 1.5磅

    // 设置标记边框和填充颜色
    var marker = ctSer.marker;
    if(marker == null)
    {
        marker = new CT_Marker();
        ctSer.marker=marker;
    }
    marker.symbol = new NPOI.OpenXmlFormats.Dml.Chart.CT_MarkerStyle() { val = NPOI.OpenXmlFormats.Dml.Chart.ST_MarkerStyle.circle };
    marker.size = new CT_MarkerSize { val = 5 };
    marker.spPr = new NPOI.OpenXmlFormats.Dml.Chart.CT_ShapeProperties();
    marker.spPr.ln = new NPOI.OpenXmlFormats.Dml.CT_LineProperties();
    marker.spPr.ln.solidFill = new NPOI.OpenXmlFormats.Dml.CT_SolidColorFillProperties();
    marker.spPr.ln.solidFill.srgbClr = new NPOI.OpenXmlFormats.Dml.CT_SRgbColor() { val = new byte[] { 0, 255, 0 } }; // 绿色
    marker.spPr.solidFill = new NPOI.OpenXmlFormats.Dml.CT_SolidColorFillProperties();
    marker.spPr.solidFill.srgbClr = new NPOI.OpenXmlFormats.Dml.CT_SRgbColor() { val = new byte[] { 0, 0, 255 } }; // 蓝色

    using (var fs = new FileStream("chart.xlsx", FileMode.Create, FileAccess.Write))
    {
        workbook.Write(fs);
    }

}

易错点：

1. 如果按照Apache POI的用法，修改图表中的线条样式的逻辑大致应该按照下面的逻辑，但是NPOI（目前最新版本2.7.4）尚未实现Apache POI中的XDDFChart,因此不能直接使用下面的代码：

XDDFChart xddfChart = chart.GetCTChart();
XDDFLineChartData lineChartData = (XDDFLineChartData)xddfChart.GetChartSeries()[0];
XDDFLineChartData.Series lineSeries = (XDDFLineChartData.Series)lineChartData.GetSeries()[0];

XDDFSolidFillProperties fill = new XDDFSolidFillProperties(XDDFColor.From(PresetColor.ORANGE));
XDDFLineProperties lineProperties = new XDDFLineProperties();
lineProperties.SetFillProperties(fill);

lineSeries.SetShapeProperties(new XDDFShapeProperties(lineProperties));

2. 在NPOI中设置系列线条宽度时，单位为点，12700点和Excel中的1磅宽度大致相同。因此需要使用下面的代码设置

lineProperties.w = (int)(12700 * 1.5); // 1.5磅

3. 颜色目前支持rgb分量输入使用（srgbClr或scrgbClr ），暂不支持直接输入System.Drawing.Color

marker.spPr.solidFill.srgbClr = new NPOI.OpenXmlFormats.Dml.CT_SRgbColor() { val = new byte[] { 0, 0, 255 } }; // 蓝色