使用模板导入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());

}

建议结合我上一篇文章一起看