using NPOI.HSSF.UserModel; using NPOI.SS.UserModel; using System; using System.Collections; using System.Collections.Generic; using System.IO; using System.Linq; using System.Net; using System.Net.Http; using System.Net.Http.Headers; using System.Reflection; using System.Web; using System.Web.Http; namespace WebApi.Controller { public class BusRouteModel { public string Title { get;set; } public int? DepartDate { get;set; } public string BeginPlace { get;set; } public string ContactsName { get;set; } public string ContactsPhone { get;set; } } [RoutePrefix("Home")] public class HomeController : ApiController { /// <summary> /// 班车数据导出成EXCEL /// </summary> /// <param name="conferenceID"></param> /// <returns></returns> [HttpGet] [Route("Export")] public HttpResponseMessage ExportExcelDataForBusRoute() { HttpResponseMessage result = new HttpResponseMessage(); var busColl = new List<BusRouteModel>() { new BusRouteModel(){ Title = "1",DepartDate=null,BeginPlace="3",ContactsName="4"} }; //如果想要某个单元格内容显示多列,在内容中加入: "\n" 换行字符 Dictionary<string, string> dicColl = new Dictionary<string, string>() { {"路线标题","Title" }, {"发车时间","DepartDate" }, {"出发地","BeginPlace" }, {"对接人","ContactsName" }, {"对接人电话","ContactsPhone" } }; result = ExcelHelp<BusRouteModel, List<BusRouteModel>>.ExportExcelDataForWebAPI(dicColl, busColl, "sdsf", "sheet1"); return result; } } /// <summary> /// EXCEL帮助类 /// </summary> /// <typeparam name="T">泛型类</typeparam> /// <typeparam name="TCollection">泛型类集合</typeparam> public class ExcelHelp<T, TCollection> where T : new() where TCollection : List<T>, new() { /// <summary> /// 得到excel文件流 /// </summary> /// <returns></returns> public static MemoryStream ExcelStream(Dictionary<string, string> fieldNameAndShowNameDic, TCollection tList, string sheetName = "sheet1") { IWorkbook workbook = new HSSFWorkbook(); ISheet worksheet = workbook.CreateSheet(sheetName); List<string> columnNameList = fieldNameAndShowNameDic.Keys.ToList(); //设置首列显示 IRow row1 = worksheet.CreateRow(0); ICell cell = null; ICellStyle cellHeadStyle = workbook.CreateCellStyle(); //设置首行字体加粗 IFont font = workbook.CreateFont(); font.Boldweight = short.MaxValue; cellHeadStyle.SetFont(font); for (var i = 0; i < columnNameList.Count; i++) { cell = row1.CreateCell(i); cell.SetCellValue(columnNameList[i]); cell.CellStyle = cellHeadStyle; } //根据反射创建其他行数据 var raws = tList.Count; Dictionary<int, PropertyInfo> indexPropertyDic = GetIndexPropertyDic(fieldNameAndShowNameDic.Values.ToList()); for (int i = 0; i < raws; i++) { row1 = worksheet.CreateRow(i + 1); for (int j = 0; j < fieldNameAndShowNameDic.Count; j++) { cell = row1.CreateCell(j); if (indexPropertyDic[j].PropertyType == typeof(int) || indexPropertyDic[j].PropertyType == typeof(decimal) || indexPropertyDic[j].PropertyType == typeof(double)) { cell.SetCellValue(Convert.ToDouble(indexPropertyDic[j].GetValue(tList[i]))); } else if (indexPropertyDic[j].PropertyType == typeof(DateTime)) { cell.SetCellValue(Convert.ToDateTime(indexPropertyDic[j].GetValue(tList[i])?.ToString())); } else if (indexPropertyDic[j].PropertyType == typeof(bool)) { cell.SetCellValue(Convert.ToBoolean(indexPropertyDic[j].GetValue(tList[i])?.ToString())); } else { cell.SetCellValue(indexPropertyDic[j].GetValue(tList[i])?.ToString()); } } //设置行宽度自适应 worksheet.AutoSizeColumn(i, true); } //3-自动列宽-根据内容长度自动展开 for (int i = 0; i < fieldNameAndShowNameDic.Count; i++) { worksheet.AutoSizeColumn(i); } MemoryStream file = new MemoryStream(); workbook.Write(file); //这句代码非常重要,如果不加,会报:打开的EXCEL格式与扩展名指定的格式不一致 file.Seek(0, SeekOrigin.Begin); return file; } /// <summary> /// 导出excel功能控制器 /// </summary> /// <returns></returns> public static HttpResponseMessage ExportExcelDataForWebAPI(Dictionary<string, string> displayAndColumnName, TCollection tlist, string exportFileName, string sheetName = "sheet1") { var file = ExcelStream(displayAndColumnName, tlist, sheetName); HttpResponseMessage result = new HttpResponseMessage(HttpStatusCode.OK); result.Content = new StreamContent(file); result.Content.Headers.ContentType = new MediaTypeHeaderValue("application/vnd.ms-excel"); result.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment"); result.Content.Headers.ContentDisposition.FileName = exportFileName + ".xls"; return result; } /// <summary> /// 根据属性名顺序获取对应的属性对象 /// </summary> /// <param name="fieldNameList"></param> /// <returns></returns> private static Dictionary<int, PropertyInfo> GetIndexPropertyDic(List<string> fieldNameList) { Dictionary<int, PropertyInfo> indexPropertyDic = new Dictionary<int, PropertyInfo>(fieldNameList.Count); List<PropertyInfo> tPropertyInfoList = typeof(T).GetProperties().ToList(); PropertyInfo propertyInfo = null; for (int i = 0; i < fieldNameList.Count; i++) { propertyInfo = tPropertyInfoList.Find(m => m.Name.Equals(fieldNameList[i], StringComparison.OrdinalIgnoreCase)); indexPropertyDic.Add(i, propertyInfo); } return indexPropertyDic; } } }