前端Vue：
var tempType = "application/msexcel"; this.SearchForm.Type="2"; this.tableHeader.forEach((element)=>{ this.SearchForm.selectHeader.push(element) }); downLoadTemplate(this.SearchForm).then((response) => { this.importLoading = false; if (response.data.request_status == undefined && response.status!=200) { this.$message.error("没有权限"); return; } let blob = new Blob([response.data], { type: tempType, // word文档为msword,pdf文档为pdf，msexcel 为excel }); let objectUrl = URL.createObjectURL(blob); let link = document.createElement("a"); let fname = "导出物料信息.xlsx"; // 下载文件的名字 link.href = objectUrl; link.setAttribute("download", fname); document.body.appendChild(link); link.click(); });Controller：
try
{
List<Ba_Material> allBa_material = new List<Ba_Material>();//所有物料信息
DataTable datSource = _bll.getMaterialDatatable(model);//所有物料信息
List<tableHeader> tableHeader = model.selectHeader;
List<string> selectEnNameHeader = new List<string>(tableHeader.Select(t => t.EnName).ToList());
selectEnNameHeader.Insert(0,"MaterialId");
List<string> selectCnNameHeader = new List<string>(tableHeader.Select(t => t.CnName).ToList());
selectCnNameHeader.Insert(0,"物料编号");
DataTable datNew = datSource.DefaultView.ToTable(false ,selectEnNameHeader.ToArray());//选中列的数据
string fullPath = FileHelper.MapPath($"/Template/导出物料信息.xlsx");
string sheetName = "物料信息";
// string[] columnNames = { "产品线id", "物料id", "货号", "物料名称", "品牌", "规格", "组合物料", "目录价", "目录价执行日期", "目录价更新日期" };
string[] columnNames = selectCnNameHeader.ToArray();//要显示在excel里的表头
ExcelHelper.ExportExcelTemplate(datNew, fullPath, sheetName, columnNames);//table里的数据写入excel文件
var stream = new FileStream(fullPath, FileMode.Open);
HttpResponseMessage response = new HttpResponseMessage(HttpStatusCode.OK);
response.Content = new StreamContent(stream);
response.Content.Headers.ContentType = new System.Net.Http.Headers.MediaTypeHeaderValue("application/octet-stream");
response.Content.Headers.ContentDisposition = new System.Net.Http.Headers.ContentDispositionHeaderValue("attachment") { FileName = "" };
return response;
}
catch (Exception ex)
{
StaticHelper.ErrorLog("模板下载错误信息:" + ex.Message);
throw;
}
ExportExcelTemplate：
/// <summary>
/// 导出Template1Excel
/// </summary>
/// <param name="dt">写入sheet中的数据</param>
/// <param name="FilePath">文件路径</param>
/// <param name="sheetName">sheet名称</param>
/// <param name="columnNames">sheet中列名</param>
public static void ExportExcelTemplate(DataTable dt, string filePath, string sheetName = "", string[] columnNames = null)
{
if (File.Exists(filePath))
{
FileStream file = null;
FileStream sw = null;
IWorkbook workbook = null;
try
{
file = File.OpenRead(filePath);
int row_index = 0;
file.Position = 0;
workbook = new XSSFWorkbook(file);
ISheet sheet = null;
int index = workbook.GetSheetIndex(sheetName);
//存在sheet名称，则删除
if (index > -1)
{
workbook.RemoveSheetAt(index);
}
//新建sheet
sheet = workbook.CreateSheet(sheetName);
IRow row;
row = sheet.CreateRow(row_index);
if (columnNames == null)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
row.CreateCell(j).SetCellValue(dt.Columns[j].Caption.ToString());
}
}
else
{
for (int j = 0; j < columnNames.Length; j++)
{
row.CreateCell(j).SetCellValue(columnNames[j].ToString());
}
}
row = sheet.CreateRow(++row_index);
//写入数据
if (columnNames == null)
{
foreach (DataRow r in dt.Select())
{
for (int j = 0; j < dt.Columns.Count; j++)
{
row.CreateCell(j).SetCellValue(r[j].ToString());
}
row = sheet.CreateRow(++row_index);
}
}
else
{
foreach (DataRow r in dt.Select())
{
for (int j = 0; j < columnNames.Length; j++)
{
row.CreateCell(j).SetCellValue(r[j].ToString());
}
row = sheet.CreateRow(++row_index);
}
}
//保存文档
sw = File.Create(filePath);
workbook.Write(sw);
}
catch (Exception ex)
{}
finally
{
if (workbook != null)
workbook.Close();
if (sw != null)
sw.Dispose();
if (file != null)
file.Dispose();
}
}
}