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

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模板内容：

导出后：