一、创建一个类文件添加
public class ExportToExcelColumn { public ExportToExcelColumn(string _Columnnames, string _EnglishColumnnames) { Columnnames = _Columnnames; EnglishColumnnames = _EnglishColumnnames; } public ExportToExcelColumn(string _Columnnames, string _EnglishColumnnames, List<EnumName> _EnumValueList) { Columnnames = _Columnnames; EnglishColumnnames = _EnglishColumnnames; EnumValueList = _EnumValueList; if(_EnumValueList!=null&& _EnumValueList.Count()>0) { IsEnum = true; } } public string Columnnames { get; set; } public string EnglishColumnnames { get; set; } public bool IsEnum { get; set; } = false; public List<EnumName> EnumValueList { get; set; } } public class EnumName { public string EnumKey { get; set; } public string EnumValue { get; set; } }
二、添加调用方法
#region /// <summary> /// /// </summary> /// <param name="list">数据</param> /// <param name="saveFileName">保存名</param> /// <param name="Columnnames">列名</param> /// <param name="IsSortCol">是否添加排序列</param> /// <returns></returns> public ActionResult ExportToExcel(List<dynamic> list, string saveFileName, List<ExportToExcelColumn> Columnnames, bool IsSortCol) { //创建Excel文件的对象 NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); //添加一个sheet NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1"); //给sheet1添加第一行的头部标题 NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0); //存储列名 if (Columnnames != null) { int iStart = 0; if (IsSortCol) { row1.CreateCell(iStart).SetCellValue("序号"); iStart = 1; } for (int i = 0; i < Columnnames.Count; i++) { row1.CreateCell(i + iStart).SetCellValue(Columnnames[i].Columnnames); } //存储值 if (list != null) { for (int i = 0; i < list.Count; i++) { NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1); if (IsSortCol) { rowtemp.CreateCell(0).SetCellValue(i + 1); } for (int m = 0; m < Columnnames.Count; m++) { string value = ""; bool IsEnum = Columnnames[m].IsEnum; List<EnumName> EnumValueList= Columnnames[m].EnumValueList; value = list[i][Columnnames[m].EnglishColumnnames].ToString(); if(IsEnum) { EnumName enumName = EnumValueList.FirstOrDefault(t=>t.EnumKey== value); if (enumName != null) { value = enumName.EnumValue; } else { enumName = EnumValueList.FirstOrDefault(t => t.EnumKey == ""); if (enumName != null) { value = enumName.EnumValue; } } } rowtemp.CreateCell(m + iStart).SetCellValue(value); } } } } // 写入到客户端 System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); ms.Seek(0, SeekOrigin.Begin); return File(ms, "application/vnd.ms-excel", saveFileName + ".xls"); } #endregion
三、自定义导出列
List<ExportToExcelColumn> Columnnames = new List<ExportToExcelColumn>(); Columnnames.Add(new ExportToExcelColumn("编号", "code")); Columnnames.Add(new ExportToExcelColumn("名称", "name"));
List<EnumName> EnumValueList = new List<EnumName>();
EnumValueList.Add(new EnumName() { EnumKey = "0", EnumValue = "成功" });
//默认值
EnumValueList.Add(new EnumName() { EnumKey = "", EnumValue = "失败" });
Columnnames.Add(new ExportToExcelColumn("状态", "status", EnumValueList));
四、调用导出方法
/// <summary> /// /// </summary> /// <param name="list">数据</param> /// <param name="fileName">保存名</param> /// <param name="Columnnames">列名</param> /// <param name="IsSortCol">是否添加排序列</param> /// <returns></returns>
return ExportToExcel(list, filename, Columnnames, true);