[C#][NPOI][Excel] 自動產生圖表

最近專案遇到需要自動產生圖表在Excel內

延用之前自動產生檔案所使用的NPOI

發現它是可以自動產生圖表

FileStream RfileStream = new FileStream("D:\\test.xlsx", FileMode.Open, FileAccess.Read);
//建立讀取資料的FileStream
XSSFWorkbook wb = new XSSFWorkbook(RfileStream);
//讀取檔案內的Workbook物件
ISheet Wsheet = wb.GetSheetAt(1);
//選擇圖表存放的sheet
ISheet Rsheet = wb.GetSheetAt(0);
//選擇資料來源的sheet
IDrawing drawing = Wsheet.CreateDrawingPatriarch();
//sheet產生drawing物件
IClientAnchor clientAnchor = drawing.CreateAnchor(0, 0, 0, 0, 0, 0, 5, 10);
//設定圖表位置
IChart chart = drawing.CreateChart(clientAnchor);
//產生chart物件
IChartLegend legend = chart.GetOrCreateLegend();
//還沒研究出這行在做甚麼
legend.Position = LegendPosition.TopRight;
ILineChartData<double, double> data = chart.ChartDataFactory.CreateLineChartData<double, double>();
//產生存放資料的物件(資料型態為double)
IChartAxis bottomAxis = chart.ChartAxisFactory.CreateCategoryAxis(AxisPosition.Bottom);
//設定X軸
IValueAxis leftAxis = chart.ChartAxisFactory.CreateValueAxis(AxisPosition.Left);
//設定Y軸
bottomAxis.Crosses = AxisCrosses.AutoZero;
//設定X軸數值開始為0
leftAxis.Crosses = AxisCrosses.AutoZero;
//設定Y軸數值開始為0
IChartDataSource<double> xs = DataSources.FromNumericCellRange(Rsheet, new CellRangeAddress(0, 4, 0, 0));
//取得要讀取sheet的資料位置(CellRangeAddress(first_row,end_row, first_column, end_column))
//x軸資料
IChartDataSource<double> ys1 = DataSources.FromNumericCellRange(Rsheet, new CellRangeAddress(0, 4, 1, 1));
//第一條y軸資料
IChartDataSource<double> ys2 = DataSources.FromNumericCellRange(Rsheet, new CellRangeAddress(0, 4, 2, 2));
//第二條y軸資料
data.AddSeries(xs, ys1);
data.AddSeries(xs, ys2);
//加入到data
chart.Plot(data, bottomAxis, leftAxis);
//加入到chart
FileStream WfileStream = new FileStream("D:\\test.xlsx", FileMode.Create, FileAccess.Write);
//建立寫入資料的FileStream
wb.Write(WfileStream);
//將workbook寫入資料
RfileStream.Close();
//關閉FileStream
WfileStream.Close();
//關閉FileStream

但是對於圖表的進階設定卻還沒嘗試出來

如果有找到方法或是有其他Dll可以使用的

會再更新文章