当前位置：首页 > news >正文

c#集成npoi根据excel模板导出excel

news 2025/6/28 22:06:17

NuGet中安装npoi

创建excel模板，替换其中的内容生成新的excel文件。

例子中主要写了这四种情况：

1、替换单个单元格内容；
2、替换横向多个单元格；
3、替换表格；
4、单元格中插入图片；

using System.IO;
using NPOI.XSSF.UserModel;
using NPOI.SS.UserModel;
using System.Diagnostics;public static void exportExcel(){string currentDirectory = System.AppDomain.CurrentDomain.BaseDirectory;string templatePath = currentDirectory + "/template.xlsx";string outputPath = currentDirectory + "/output/" + DateTime.Now.ToString("yyyyMMdd"); //存储路径string outputPathExcel = outputPath + "/" + DateTime.Now.ToString("yyyyMMddHHmmss") + "_test.xlsx";// 检查路径是否存在if (!Directory.Exists(outputPath)){Directory.CreateDirectory(outputPath);}//1、单元格直接替换部分Dictionary<string, string> replacements = new Dictionary<string, string>{{ "test1", "测试1" },{ "test2", "测试2" }};//2、横向数组,曲线表格Dictionary<string, double[]> replacementsArrayH = = new Dictionary<string, double[]>();replacementsArrayH["item1"] = new double[] { 1.1, 2.2, 3.3 };replacementsArrayH["item2"] = new double[] { 4.4, 5.5, 6.6 };//3、表格数据//自己定义类//public TaskItem{//  private string name;//  private string age;//  public TaskItem(){//  }//  public TaskItem(string name, string age){//	  this.name=name;//	  this.age=age;//  }//  ……//}List<TaskItem> taskItemList = new List<TaskItem>();taskItemList.Add(new TaskItem("Alice", "30"));taskItemList.Add(new TaskItem("Bob", "25"));try{// excel导出using (FileStream file = new FileStream(templatePath, FileMode.Open, FileAccess.Read)){IWorkbook workbook = new XSSFWorkbook(file);ISheet sheet = workbook.GetSheetAt(0);// 替换模板中的字符串for (int rowIndex = 0; rowIndex <= sheet.LastRowNum; rowIndex++){IRow row = sheet.GetRow(rowIndex);if (row == null){continue;}for (int cellIndex = 0; cellIndex < row.LastCellNum; cellIndex++){ICell cell = row.GetCell(cellIndex);if (cell != null && cell.CellType == CellType.String){string cellValue = cell.StringCellValue;//填充单个文本foreach (var replacement in replacements){if (cellValue.Equals(replacement.Key)){cell.SetCellValue(cellValue.Replace(replacement.Key, replacement.Value));}}//填充横向数组foreach (var replacement in replacementsArrayH){string placeholder = replacement.Key;double[] values = replacement.Value;int index = values.Length;if (cellValue.Equals(replacement.Key)){for (int colNum = 0; colNum < index; colNum++){cell = row.GetCell(cellIndex + colNum);cell.SetCellValue(cellValue.Replace(placeholder, values[colNum].ToString()));}}}//填充详情表格if (cellValue.Equals("table")){if (list == null) // list 为业务需要写入数据{continue;}// 添加数据行for (var i = 0; i < list.Count; i++){TaskItem item = list[i];// 因为模版内已经存在一格空行，所以直接赋值，后续从此行进行 CopyRow 即可if (i == 0){SetRowCellValue(row, item, cellIndex);continue;}//其他行var newRow = sheet.CopyRow(rowIndex, rowIndex + i);SetRowCellValue(newRow, item, cellIndex);}}}}}//4、导出图片string picPath = currentDirectory + "/test.png";//图片byte[] bytes = File.ReadAllBytes(picPath);int pictureIndex = workbook.AddPicture(bytes, PictureType.PNG);// 定义图片的位置var drawingPatriarch = sheet.CreateDrawingPatriarch();var anchor = drawingPatriarch.CreateAnchor(0, 0, 0, 0, 1, 6, 12, 13); // 参数依次是：dx1, dy1, dx2, dy2, col1, row1, col2, row2// 在单元格中添加图片var picture = drawingPatriarch.CreatePicture(anchor, pictureIndex);// 保存新文件using (FileStream outputFile = new FileStream(outputPathExcel, FileMode.Create, FileAccess.Write)){workbook.Write(outputFile);}}}catch (Exception ex){}}/// <summary>/// 填充表格数据/// </summary>/// <param name="row">填充行</param>/// <param name="item">填充数据</param>/// <param name="cellIndex">列开始行</param>private static void SetRowCellValue(IRow row, TaskItem item, int cellIndex){ICell cell = row.GetCell(cellIndex++);cell.SetCellValue(item.name);cellIndex++;cell = row.GetCell(cellIndex++);cell.SetCellValue(item.age);}

excel模板内容：