.NET生成的Excel文件中添加柱状图

一:NPOI引用

1 using NPOI.SS.UserModel;
2 using NPOI.SS.UserModel.Charts;
3 using NPOI.SS.Util;
4 using NPOI.XSSF.UserModel;
View Code

二:创建方法类

 1 public static void CreateExcelWithChart()
 2 {
 3     // 创建工作簿
 4     IWorkbook workbook = new XSSFWorkbook();
 5     ISheet sheet = workbook.CreateSheet("销售数据");
 6 
 7     // 创建标题行
 8     IRow headerRow = sheet.CreateRow(0);
 9     headerRow.CreateCell(0).SetCellValue("月份");
10     headerRow.CreateCell(1).SetCellValue("销售额");
11     headerRow.CreateCell(2).SetCellValue("利润");
12 
13     // 创建数据行
14     var data = new List<Tuple<string, double, double>>
15     {
16         Tuple.Create("1月", 120000.0, 30000.0),
17         Tuple.Create("2月", 150000.0, 40000.0),
18         Tuple.Create("3月", 180000.0, 50000.0),
19         Tuple.Create("4月", 160000.0, 45000.0),
20         Tuple.Create("5月", 200000.0, 60000.0),
21         Tuple.Create("6月", 220000.0, 65000.0)
22     };
23 
24     for (int i = 0; i < data.Count; i++)
25     {
26         IRow row = sheet.CreateRow(i + 1);
27         row.CreateCell(0).SetCellValue(data[i].Item1);
28         row.CreateCell(1).SetCellValue(data[i].Item2);
29         row.CreateCell(2).SetCellValue(data[i].Item3);
30     }
31 
32     // 自动调整列宽
33     sheet.AutoSizeColumn(0);
34     sheet.AutoSizeColumn(1);
35     sheet.AutoSizeColumn(2);
36 
37     // 创建柱状图
38     CreateBarChart(workbook, sheet, data.Count);
39 
40     // 保存文件
41     using (FileStream fs = new FileStream("SalesReportWithChart.xlsx", FileMode.Create))
42     {
43         workbook.Write(fs);
44     }
45 }
46 
47 public static void CreateBarChart(IWorkbook workbook, ISheet sheet, int dataCount)
48 {
49     // 创建绘图对象 - 使用具体的XSSFDrawing类型
50     var drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;
51     if (drawing == null)
52     {
53         drawing = (XSSFDrawing)sheet.CreateDrawingPatriarch();
54     }
55 
56     // 创建锚点
57     var anchor = drawing.CreateAnchor(0, 0, 0, 0, 4, 0, 10, 20);
58 
59     // 创建图表
60     var chart = drawing.CreateChart(anchor);
61     var legend = chart.GetOrCreateLegend();
62     legend.Position = LegendPosition.Bottom;
63     // 创建坐标轴
64     var bottomAxis = chart.ChartAxisFactory.CreateCategoryAxis(AxisPosition.Bottom);
65     var leftAxis = chart.ChartAxisFactory.CreateValueAxis(AxisPosition.Left);
66 
67     // 定义数据范围
68     var categoryAxisData = DataSources.FromStringCellRange(
69         sheet, new CellRangeAddress(1, dataCount, 0, 0));
70 
71     // 销售额数据系列
72     var salesData = DataSources.FromNumericCellRange(
73         sheet, new CellRangeAddress(1, dataCount, 1, 1));
74 
75     // 利润数据系列
76     var profitData = DataSources.FromNumericCellRange(
77         sheet, new CellRangeAddress(1, dataCount, 2, 2));
78 
79     // 添加柱状图系列
80     var barChartData = chart.ChartDataFactory.CreateBarChartData<string, double>();
81 
82     var salesSeries = barChartData.AddSeries(categoryAxisData, salesData);
83     salesSeries.SetTitle("销售额");
84 
85     var profitSeries = barChartData.AddSeries(categoryAxisData, profitData);
86     profitSeries.SetTitle("利润");
87 
88     // 将图表数据应用到图表
89     chart.Plot(barChartData, bottomAxis, leftAxis);
90 
91     // 设置图表标题
92     chart.SetTitle("销售数据柱状图");
93 }
View Code

 

posted @ 2025-10-16 16:05  黑夜的ghost  阅读(7)  评论(0)    收藏  举报