使用NPOI导出Excel

HSSFWorkbook导出的Excel是2003版本，代码如下

function testExport() {
            $.ajax({
                type: "GET",
                url: "/test/Generate",
                dataType: "json",
                success: function (data) {
                    console.log(data);
                    window.location.target = "_blank";
                    window.location.href = data.ExeclPath;
                }
            });
        }

 

 public JsonResult Generate()
        {

            //获取要生成文件的数据源
            var result =  FillFileStream();

            var fileName = DateTime.Now.ToString("yyyyMMddHHmmss");
            var exportFileName = "/UploadFiles/ExcelFiles/" + fileName + ".xls";

            var excelFileName = Server.MapPath("/UploadFiles/ExcelFiles/" + fileName + ".xls");
            string directoryName = Path.GetDirectoryName(excelFileName);
            if (!Directory.Exists(directoryName))
            {
                Directory.CreateDirectory(directoryName);
            }
            var buffer = result.GetBuffer();
            using (FileStream fsForWrite = new FileStream(excelFileName, FileMode.Create))
            {
                fsForWrite.Write(buffer, 0, buffer.GetLength(0));
                //清除流
                fsForWrite.Flush();
                //关闭文件
                fsForWrite.Close();
                //释放
                fsForWrite.Dispose();
            }

            // DownloadFileHelper.DownloadFile(System.Web.HttpContext.Current, excelFileName);

            var dataJson = new {
                ExcelPath= exportFileName
            };
            return Json(dataJson, JsonRequestBehavior.AllowGet);
        }

private MemoryStream FillFileStream()
        {

            #region 生成excel工作簿

            var dataTable = ListToDataTable(bizDecodeLog.GetAll(null, null, "", 1, "", null).Select(m => new { m.testID, m.testName}).ToList());

            //列原字段名称 
            string[] oldColumn = new string[] {
                "testID",
                "testName"
            };

            //列名称
            string[] newColumn = new string[]
            {
                "test编号",
                "test名称"
            };
            #endregion

            HSSFWorkbook workbook = new HSSFWorkbook();
            CreateSheet(workbook, "test", dataTable, "test", oldColumn, newColumn);


            MemoryStream ms = new MemoryStream();
            workbook.Write(ms);
            ms.Flush();
            ms.Position = 0;

            workbook = null;
            return ms;
        }

/// <summary>
        /// 
        /// </summary>
        /// <param name="workBook">工作薄</param>
        /// <param name="sheetName">工作表</param>
        /// <param name="dtSource">数据源</param>
        /// <param name="strHeaderText">表头</param>
        /// <param name="oldColumnNames">原有列表名称</param>
        /// <param name="newColumnNames">新列表名称</param> 
        /// <param name="strStatisticsText"></param>
        /// <returns></returns>
        private ISheet CreateSheet(HSSFWorkbook workBook, string sheetName, DataTable dtSource, string strHeaderText,
            string[] oldColumnNames, string[] newColumnNames)
        {
            ISheet sheet = workBook.CreateSheet(sheetName);

            ICellStyle dateStyle = workBook.CreateCellStyle();
            IDataFormat format = workBook.CreateDataFormat();
            dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss");

            #region 取得列宽
            int[] arrColWidth = new int[oldColumnNames.Length];
            for (int i = 0; i < oldColumnNames.Length; i++)
            {
                arrColWidth[i] = Encoding.GetEncoding(936).GetBytes(newColumnNames[i]).Length;
            }

            for (int i = 0; i < dtSource.Rows.Count; i++)
            {
                for (int j = 0; j < oldColumnNames.Length; j++)
                {
                    int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][oldColumnNames[j]].ToString()).Length;
                    if (intTemp > arrColWidth[j])
                    {
                        arrColWidth[j] = intTemp;
                    }
                }
            }
            #endregion

            int rowIndex = 0;
            foreach (DataRow row in dtSource.Rows)
            {
                #region 新建表，填充表头，填充列头，样式
                if (rowIndex == 0)
                {
                    if (rowIndex != 0)
                    {
                        //   sheet = workBook.CreateSheet(sheetName + ((int)rowIndex / 65535).ToString());
                        sheet = workBook.CreateSheet(sheetName);
                    }

                    #region 表头及样式
                    {
                        IRow headerRow = sheet.CreateRow(0);

                        ICellStyle headStyle = workBook.CreateCellStyle();
                        headStyle.Alignment = HorizontalAlignment.Center;
                        IFont font = workBook.CreateFont();
                        font.FontHeightInPoints = 20;
                        //font.Boldweight = 700;
                        headStyle.SetFont(font);

                        for (int i = 0; i < oldColumnNames.Length; i++)
                        {
                            headerRow.CreateCell(i).SetCellValue(newColumnNames[i]);
                            //headerRow.GetCell(i).CellStyle = headStyle;
                        }
                    }
                    #endregion

                }
                #endregion

                #region 填充内容
                IRow dataRow = sheet.CreateRow(rowIndex + 1);
                for (int i = 0; i < oldColumnNames.Length; i++)
                {
                    ICell newCell = dataRow.CreateCell(i);

                    string drValue = row[oldColumnNames[i]].ToString();

                    switch (dtSource.Columns[oldColumnNames[i]].DataType.ToString())
                    {
                        case "System.String"://字符串类型      
                            newCell.SetCellValue(drValue);
                            break;
                        case "System.DateTime"://日期类型      
                            DateTime dateV;
                            DateTime.TryParse(drValue, out dateV);
                            newCell.SetCellValue(dateV);

                            newCell.CellStyle = dateStyle;//格式化显示      
                            break;
                        case "System.Boolean"://布尔型      
                            bool boolV = false;
                            bool.TryParse(drValue, out boolV);
                            newCell.SetCellValue(boolV);
                            break;
                        case "System.Int16"://整型      
                        case "System.Int32":
                        case "System.Int64":
                        case "System.Byte":
                            int intV = 0;
                            int.TryParse(drValue, out intV);
                            newCell.SetCellValue(intV);
                            break;
                        case "System.Decimal"://浮点型      
                        case "System.Double":
                            double doubV = 0;
                            double.TryParse(drValue, out doubV);
                            newCell.SetCellValue(doubV);
                            break;
                        case "System.DBNull"://空值处理      
                            newCell.SetCellValue("");
                            break;
                        default:
                            newCell.SetCellValue("");
                            break;
                    }

                }
                #endregion

                rowIndex++;
            }

            return sheet;
        }

/// <summary>
        /// List转DataTable
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="data"></param>
        /// <returns></returns>
        private DataTable ListToDataTable<T>(List<T> data)
        {
            PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));
            DataTable dataTable = new DataTable();
            for (int i = 0; i < properties.Count; i++)
            {
                PropertyDescriptor property = properties[i];
                dataTable.Columns.Add(property.Name, Nullable.GetUnderlyingType(property.PropertyType) ?? property.PropertyType);
            }
            object[] values = new object[properties.Count];
            foreach (T item in data)
            {
                for (int i = 0; i < values.Length; i++)
                {
                    values[i] = properties[i].GetValue(item);
                }

                dataTable.Rows.Add(values);
            }
            return dataTable;
        }

/// <summary>
        /// 说明：获得一个对象的所有属性
        /// </summary>
        /// <returns></returns>
        private string[] GetPropertyNameArray()
        {
            PropertyInfo[] props = null;
            try
            {
                Type type = typeof(DecodeLogExportModel);
                object obj = Activator.CreateInstance(type);
                props = type.GetProperties(BindingFlags.Public | BindingFlags.Instance);

                string[] array = props.Select(t => t.Name).ToArray();
                return array;
            }
            catch (Exception ex)
            {
                return null;
            }

        }