因客戶需求,要求導出的Excel數據文件,可以修改後,在上傳數據。而且,需要指定的字段要有下拉框和級聯下拉框功能。感覺處理起來挺麻煩的,使用原有的代碼處理導出,導出1.9M數據,超過2分鐘,速度不理想
源代碼功能如下
///
/// 導出EDI價格
///
///
public FileResult ExportEDI()
{
string file = HttpContext.Server.MapPath($"~/DownLoad/EDI_{DateTime.Now.ToString("yyyyMMddHHmmss")}.xls"); //文件路徑以及文件名
ExcelWriter excel = new ExcelWriter(HttpContext.Server.MapPath("~/Upload/Excel/EDI.xls"), file); //讀取指定模板
excel.app.DisplayAlerts = false;
excel.app.Visible = false;
var reportList = GetData().ToList();//獲取導出數據
int row = 15; //設定數據寫入行號
foreach (Models.UploadViewModels.UpPirce price in reportList)
{
//No.
//公司
//NumberFormatLocal = "@";
excel.SetCells(row, 2, "'0000363693");
//企業
excel.SetCells(row, 3, "'" + price.CustomerQYCode);
//名稱
excel.SetCells(row, 4, "");
//事業部
excel.SetCells(row, 5, "'" + price.CustomerSYBCode);
//名稱
excel.SetCells(row, 6, "");
//顧客品番
excel.SetCells(row, 7, "'" + price.CustomerCode);
//生擔
excel.SetCells(row, 8, price.LifeTypeText);
////產品名稱
//excel.SetCells(row, 8, price.ProductName);
////商流一本化 / 直取
//excel.SetCells(row, 9, "商流一本化");
//代碼
excel.SetCells(row, 9, price.SaleType);
//名稱
excel.SetCells(row, 10, "");
//FROM
excel.SetCells(row, 11, price.StartTime.HasValue ? price.StartTime.Value.ToString("yyyy/MM/dd") : "");
//TO
excel.SetCells(row, 12, price.EndTime.HasValue ? price.EndTime.Value.ToString("yyyy/MM/dd") : "");
//顧客単価
excel.SetCells(row, 13, price.Price);
//代碼
excel.SetCells(row, 14, "");
//名稱
excel.SetCells(row,15, "");
////通貨幣種
//excel.SetCells(row, 15, price.MoneyType);
////單價區分
//excel.SetCells(row, 17, price.PriceTypes);
////價格改定原因
//excel.SetCells(row, 18, price.RegisterReasonText);
////有效無效
//excel.SetCells(row, 19, price.RegisterStateText);
//日立型式
excel.SetCells(row, 16, "");
//仕入単価
excel.SetCells(row, 17, "");
//料率
excel.SetCells(row, 18, "");
//登錄人
excel.SetCells(row, 19, "");
//登錄日
excel.SetCells(row, 20, "");
//更新人
excel.SetCells(row, 21, "");
//更新日
excel.SetCells(row, 22,"");
row++;
}
excel.OutputExcelFile(); //生成Excel文件
return File(file, "application/vnd.ms-excel", $"EDI_{DateTime.Now.ToString("yyyyMMdd")}.xls"); //導出文件
}
方法改進
///
/// 導出數據
///
///
public void ExportProductDataToExcel()
{
var reportList = GetData().ToList(); //獲取數據
string TempletFileName = HttpContext.Server.MapPath("~/Upload/Excel/產品信息.xls");//模板路徑以及名稱
HSSFWorkbook wk = null;
using (FileStream fs = new FileStream(TempletFileName, FileMode.Open))
{
//把xls文件讀入workbook變量裡,之後就可以關閉了
wk = new HSSFWorkbook(fs);
fs.Close();
}
HSSFSheet sheet1 = (HSSFSheet)wk.GetSheetAt(0);
if (reportList != null)
{
int nRow = 1; //從第二行開始寫入
foreach (var product in reportList)
{
IRow row = sheet1.CreateRow(nRow); //新增sheet行
row.CreateCell(0).SetCellValue(product.CustomerQYCode);
row.CreateCell(1).SetCellValue(product.CustomerQYName);
row.CreateCell(2).SetCellValue(product.CustomerSYBCode);
row.CreateCell(3).SetCellValue(product.CustomerSYBName);
row.CreateCell(4).SetCellValue(product.CustomerCode);
row.CreateCell(5).SetCellValue(product.InsideCode);
row.CreateCell(6).SetCellValue(product.LCode);
row.CreateCell(7).SetCellValue(product.SaleTypeText);
row.CreateCell(8).SetCellValue(product.SLType);
row.CreateCell(9).SetCellValue(product.BUTypeText);
row.CreateCell(10).SetCellValue(product.ProductTypeText);
row.CreateCell(11).SetCellValue(product.StartCode);
}
}
Response.ContentType = "application/vnd.ms-excel";
// 添加頭信息,指定文件名格式
Response.AddHeader("Content-Disposition", "attachment;filename=產品信息_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls");
Response.AddHeader("Content-Transfer-Encoding", "binary");
Response.ContentType = "application/octet-stream";
Response.ContentEncoding = System.Text.Encoding.UTF8;
MemoryStream file = new MemoryStream();
wk.Write(file);
Response.BinaryWrite(file.GetBuffer());
}
建議結合我上一篇文章一起看