网站屏蔽ip地址,移动网站开发技术,福州网站建设求职简历,开发商排名NuGet中安装npoi 创建excel模板#xff0c;替换其中的内容生成新的excel文件。
例子中主要写了这四种情况#xff1a;
1、替换单个单元格内容#xff1b; 2、替换横向多个单元格#xff1b; 3、替换表格#xff1b; 4、单元格中插入图片#xff1b;
using System.IO;
…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、单元格直接替换部分Dictionarystring, string replacements new Dictionarystring, string{{ test1, 测试1 },{ test2, 测试2 }};//2、横向数组,曲线表格Dictionarystring, double[] replacementsArrayH new Dictionarystring, 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.namename;// this.ageage;// }// ……//}ListTaskItem taskItemList new ListTaskItem();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 namerow填充行/param/// param nameitem填充数据/param/// param namecellIndex列开始行/paramprivate 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模板内容 导出后
