C# ,数据导出到带有级联下拉框的模板（二，根据模板导出数据）
二，根据模板进行导出显示，带有级联下拉框功能
开始因为时间原因，要求抓紧处理，就直接使用了之前的读入模板导出功能，但是1.9M的文件导出用了，2分钟多
1 /// <summary> 2 /// 导出EDI价格 3 /// </summary> 4 /// <returns></returns> 5 public FileResult ExportEDI() 6 { 7 string file = HttpContext.Server.MapPath($"~/DownLoad/EDI_{DateTime.Now.ToString("yyyyMMddHHmmss")}.xls"); 8 ExcelWriter excel = new ExcelWriter(HttpContext.Server.MapPath("~/Upload/Excel/EDI.xls"), file); 9 excel.app.DisplayAlerts = false; 10 excel.app.Visible = false; 11 12 var list = GetData(); 13 var reportList = GetData().ToList(); 14 int row = 15; 15 foreach (Models.UploadViewModels.UpPirce price in reportList) 16 { 17 18 //NumberFormatLocal = "@"; 19 excel.SetCells(row, 2, "'0000363693"); 20 21 excel.SetCells(row, 3, "'" + price.CustomerQYCode); 22 23 excel.SetCells(row, 4, ""); 24 25 excel.SetCells(row, 5, "'" + price.CustomerSYBCode); 26 27 excel.SetCells(row, 6, ""); 28 29 excel.SetCells(row, 7, "'" + price.CustomerCode); 30 31 excel.SetCells(row, 8, price.LifeTypeText); 32 33 34 //excel.SetCells(row, 8, price.ProductName); 35 36 //excel.SetCells(row, 9, "商流一本化"); 37 38 39 excel.SetCells(row, 9, price.SaleType); 40 41 excel.SetCells(row, 10, ""); 42 43 excel.SetCells(row, 11, price.StartTime.HasValue ? price.StartTime.Value.ToString("yyyy/MM/dd") : ""); 44 45 excel.SetCells(row, 12, price.EndTime.HasValue ? price.EndTime.Value.ToString("yyyy/MM/dd") : ""); 46 47 excel.SetCells(row, 13, price.Price); 48 49 50 51 row++; 52 } 53 excel.OutputExcelFile(); 54 return File(file, "application/vnd.ms-excel", $"EDI_{DateTime.Now.ToString("yyyyMMdd")}.xls"); 55 }
ExcelWriter 是一个封装的类文件，下载链接：https://pan.baidu.com/s/1T5H22YhwgbJ60DK8Sy9lXg 提取码：0l4k
因为是封装的类，不知道如何优化，只能自己重新写了一个
1 /// <summary> 2 /// 导出数据 3 /// </summary> 4 /// <returns></returns> 5 public void ExportProductDataToExcel() 6 { 7 var reportList = GetData().ToList(); 8 string TempletFileName = HttpContext.Server.MapPath("~/Upload/Excel/产品信息.xls"); 9 HSSFWorkbook wk = null; 10 using (FileStream fs = new FileStream(TempletFileName, FileMode.Open)) 11 { 12 //把xls文件读入workbook变量里，之后就可以关闭了 13 wk = new HSSFWorkbook(fs); 14 fs.Close(); 15 } 16 HSSFSheet sheet1 = (HSSFSheet)wk.GetSheetAt(0); 17 18 if (reportList != null) 19 { 20 int nRow = 1; 21 22 foreach (var product in reportList) 23 { 24 IRow row = sheet1.CreateRow(nRow); 25 26 row.CreateCell(0).SetCellValue(product.CustomerQYCode); 27 28 row.CreateCell(1).SetCellValue(product.CustomerQYName); 29 30 row.CreateCell(2).SetCellValue(product.CustomerSYBCode); 31 row.CreateCell(3).SetCellValue(product.CustomerSYBName); 32 33 row.CreateCell(4).SetCellValue(product.CustomerCode); 34 35 row.CreateCell(5).SetCellValue(product.InsideCode); 36 37 row.CreateCell(6).SetCellValue(product.LCode); 38 39 row.CreateCell(7).SetCellValue(product.SaleTypeText); 40 41 row.CreateCell(8).SetCellValue(product.SLType); 42 43 row.CreateCell(9).SetCellValue(product.BUTypeText); 44 45 row.CreateCell(10).SetCellValue(product.ProductTypeText); 46 row.CreateCell(11).SetCellValue(product.StartCode); 47 48 row.CreateCell(12).SetCellValue(product.LPPT); 49 50 row.CreateCell(13).SetCellValue(product.ManageLabel); 51 row.CreateCell(18).SetCellValue(product.ReNewTime.HasValue ? product.ReNewTime.Value.ToString("yyyy/MM/dd") : ""); 52 nRow++; 53 } 54 55 } 56 Response.ContentType = "application/vnd.ms-excel"; 57 // 添加头信息，指定文件名格式 58 Response.AddHeader("Content-Disposition", "attachment;filename=产品信息_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"); 59 Response.AddHeader("Content-Transfer-Encoding", "binary"); 60 Response.ContentType = "application/octet-stream"; 61 Response.ContentEncoding = System.Text.Encoding.UTF8; 62 MemoryStream file = new MemoryStream(); 63 wk.Write(file); 64 Response.BinaryWrite(file.GetBuffer()); 65 }
使用导出2m，只要2秒左右，速度提升啊
