MVC NPOI Linq匯出Excel通用類

標籤：

之前寫了一個模型匯出Excel通用類，但是在實際應用中，可能不是直接匯出模型，而是通過Linq查詢後擷取到最終結果再匯出

通用類：

public enum DataTypeEnum    {        Int = 0,        Float = 1,        Double = 2,        String = 3,        DateTime = 4,        Date = 5    }    public class ExportFieldInfo    {        /// <summary>        /// 欄位名，用於反射擷取值        /// </summary>        public string FieldName { get; set; }        /// <summary>        /// 中文名，用於匯出標題        /// </summary>        public string DisplayName { get; set; }        /// <summary>        /// 資料類型，用於強制轉換，並進行格式化,其實利用反射也可以擷取到資料類型，此處因為要處理Date和Date的顯示格式        /// </summary>        public DataTypeEnum DataType { get; set; }    }    public class ExcelHelper    {        /// <summary>        /// 匯出到記憶體流        /// </summary>        /// <param name="data">需要匯出的模型列表</param>        /// <param name="fieldInfo">匯出的欄位列表資訊</param>        /// <param name="sheetRows">每個工作表的行數</param>        /// <returns></returns>        public static MemoryStream ToExcel(List<object> data, List<ExportFieldInfo> fieldInfo, int sheetRows = 65536)        {            //建立Excel檔案的對象            NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();            //需要產生工作溥總簿            int sheetCount = data.Count / sheetRows + 1;            int rowCount = data.Count;            for (int i = 0; i < sheetCount; i++)            {                //添加一個sheet                NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("Sheet" + Convert.ToString(i));                //給sheet添加第一行的頭部標題                NPOI.SS.UserModel.IRow rowTitle = sheet.CreateRow(0);                for (int k = 0; k < fieldInfo.Count; k++)                {                    rowTitle.CreateCell(k).SetCellValue(fieldInfo.ElementAt(k).DisplayName);                }                //處理Excel一張活頁簿只能放65536行記錄的問題                //因為頭部佔一行，所以要減1                for (int j = 0; j < sheetRows - 1; j++)                {                    //將資料逐步寫入sheet各個行                    NPOI.SS.UserModel.IRow rowtemp = sheet.CreateRow(j + 1);                    int dataIndex = i * (sheetRows - 1) + j;                    for (int k = 0; k < fieldInfo.Count; k++)                    {                        //擷取類型                        Type type = data[dataIndex].GetType();                        //擷取指定名稱的屬性                        System.Reflection.PropertyInfo propertyInfo = type.GetProperty(fieldInfo.ElementAt(k).FieldName);                        //擷取屬性值                        var value = propertyInfo.GetValue(data[dataIndex], null);                        switch (fieldInfo.ElementAt(k).DataType)                        {                            case DataTypeEnum.Int:                                rowtemp.CreateCell(k).SetCellValue(Convert.ToInt32(value));                                break;                            case DataTypeEnum.Float:                                rowtemp.CreateCell(k).SetCellValue(Convert.ToDouble(value));                                break;                            case DataTypeEnum.Double:                                rowtemp.CreateCell(k).SetCellValue(Convert.ToDouble(value));                                break;                            case DataTypeEnum.String:                                rowtemp.CreateCell(k).SetCellValue(Convert.ToString(value));                                break;                            case DataTypeEnum.DateTime:                                rowtemp.CreateCell(k).SetCellValue(Convert.ToDateTime(value).ToString("yyyy-MM-dd HH:mm:ss.SSS"));                                break;                            case DataTypeEnum.Date:                                rowtemp.CreateCell(k).SetCellValue(Convert.ToDateTime(value).ToString("yyyy-MM-dd"));                                break;                            default:                                break;                        }                    }                    //所有記錄迴圈完成                    if (i * (sheetRows - 1) + (j + 1) == rowCount)                    {                        // 寫入到用戶端                         System.IO.MemoryStream ms = new System.IO.MemoryStream();                        book.Write(ms);                        ms.Seek(0, SeekOrigin.Begin);                        return ms;                    }                }            }            return null;        }    }

調用：

/// <summary>        /// 匯出Excel        /// </summary>        /// <param name="request">請求參數集合</param>        /// <returns></returns>        public FileResult ToExcel(ActiveRecordRequest request)        {            //擷取記錄資訊            Response<object> listInfo = this.CrmService.GetActiveRecordList(request);            //初始化需要匯出欄位            List<ExportFieldInfo> fieldInfo = new List<ExportFieldInfo>();            fieldInfo.Add(new ExportFieldInfo()            {                FieldName = "ActiveDate",                DisplayName = "活動日期",                DataType = DataTypeEnum.Date            });            fieldInfo.Add(new ExportFieldInfo()            {                FieldName = "ActiveSubject",                DisplayName = "活動主題",                DataType = DataTypeEnum.String            });            fieldInfo.Add(new ExportFieldInfo()            {                FieldName = "Remark",                DisplayName = "備忘",                DataType = DataTypeEnum.String            });            return File(ExcelHelper.ToExcel(listInfo.Data, fieldInfo), "application/vnd.ms-excel", "活動資訊表.xls");        }

js調用：

//匯出到Excel    ToExcel: function () {        location.href = "/" + PageParam.Area + "/" + PageParam.AjaxController + "/ToExcel?IsPaging=false";    }

 

MVC NPOI Linq匯出Excel通用類