使用模板導入Excel數據，模板設定下拉框以及級聯下拉框

因客戶需求，要求導出的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());

}

建議結合我上一篇文章一起看