webapi-npio-excel导出

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;
        }

    }


}