NetCore 2.2 中使用范本导出 Excel 和Word(可转换PDF)
导出excel 使用的 NPOI
导出word 或PDF 使用的 FreeSpire.Doc
以上两个都在nuget中可搜索。
可完善的部分: 范本文件在使用时最好先复制一份,打开和读取为复制的范本文件(多用户同时下载文件,目前的写法范本会有只读锁。)
public IActionResult Test() { var exportTemplatePath = "wwwroot/template/FNF400.xlsx"; MemoryStream ms = new MemoryStream(); using (FileStream fs = new FileStream(exportTemplatePath, FileMode.Open, FileAccess.Read)) //打开myxls.xls文件 { NPOI.SS.UserModel.IWorkbook wk; wk = new NPOI.XSSF.UserModel.XSSFWorkbook(fs); #region 导出资料到Excel int n = 5;//数据从第四行开始,循环加载的 #region 资料填充 NPOI.SS.UserModel.ISheet sheet = wk.GetSheetAt(0); #region 先赋值抬头 sheet.GetRow(0).Cells[1].SetCellValue(System.DateTime.Now.ToString("yyyy/MM/dd")); sheet.GetRow(1).Cells[1].SetCellValue( "共有支付成功订单:5笔" + " 共收款金额(元):333333" ); sheet.GetRow(2).Cells[1].SetCellValue(DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss")); List<string> list = new List<string>(); for (int i = 0; i < 500; i++) { list.Add("aaaa"); list.Add("bbbbb"); list.Add("cccccc"); list.Add("ddddd"); } //sheet.ShiftRows(5, 9, 5); //增加行数 原有样式一行,故增加列表数据-1的行数 sheet.ShiftRows(n, sheet.LastRowNum, list.Count - 1, true, false); #region 新增单元格的样式复制 var rowSource = sheet.GetRow(sheet.LastRowNum);//之前的样式行被移动到最后一行 var rowStyle = rowSource.RowStyle;//获取当前行样式 for (int i = n; i < n + list.Count - 1; i++) { var rowInsert = sheet.CreateRow(i); if (rowStyle != null) rowInsert.RowStyle = rowStyle; rowInsert.Height = rowSource.Height; for (int col = 0; col < rowSource.LastCellNum; col++) { var cellsource = rowSource.GetCell(col); var cellInsert = rowInsert.CreateCell(col); var cellStyle = cellsource.CellStyle; //设置单元格样式 if (cellStyle != null) cellInsert.CellStyle = cellsource.CellStyle; } } #endregion Console.WriteLine(sheet.LastRowNum); #endregion foreach (var item in list) { #region 数据加载 sheet.GetRow(n).Cells[0].SetCellValue(item); sheet.GetRow(n).Cells[1].SetCellValue(item); sheet.GetRow(n).Cells[2].SetCellValue(item); sheet.GetRow(n).Cells[3].SetCellValue(item); sheet.GetRow(n).Cells[4].SetCellValue(item); sheet.GetRow(n).Cells[5].SetCellValue(item); sheet.GetRow(n).Cells[6].SetCellValue(item); sheet.GetRow(n).Cells[7].SetCellValue(item); sheet.GetRow(n).Cells[8].SetCellValue(item); n++; #endregion } sheet.ForceFormulaRecalculation = true;//刷新所有计算 #endregion #endregion wk.Write(ms); ms.Flush(); ms.Position = 0; } return File(ms, "application/vnd.ms-excel", "fnf400.xlsx"); } public IActionResult Test2() { Dictionary<string, string> di = new Dictionary<string, string>(); di.Add("{$custmor}", "11111111111"); di.Add("{$custmor$}","aaaaaaaaaaaaaaaaaaa"); di.Add("{$FileNo$}", "bbbbbbbbbbbbbbbbb"); di.Add("{$CarNo$}", "ccccccccccccccccc"); di.Add("{$型号及配置$}", "ddddddddddddddd"); di.Add("{$车辆供应商名称$}", "eeeeeeeeeeeeeeeee"); di.Add("{$打包金额$}", "ffffffffffffffffffffff"); var exportTemplatePath = "wwwroot/template/REF100.docx"; MemoryStream ms = new MemoryStream(); using (FileStream fs = System.IO.File.OpenRead(exportTemplatePath)) { var doc = new Spire.Doc.Document(); doc.LoadFromFile(exportTemplatePath); foreach (var item in di) { doc.Replace(item.Key, item.Value, false, false); } //doc.SaveToStream(ms,Spire.Doc.FileFormat.Docx); doc.SaveToStream(ms, Spire.Doc.FileFormat.PDF); doc.Close(); ms.Flush(); ms.Position = 0; } return File(ms, "application/vnd.ms-word", "REF100.pdf"); }