WebApi导出Excel使用NPOI

Api调用

public virtual HttpResponseMessage Generate()
        {
            TestExport export = new TestExport();
            try
            {
                var testList = bizTest.GetAll();
                var result = export.FillFileStream(testList );
                var response = new HttpResponseMessage(HttpStatusCode.OK)
                {
                    Content = new ByteArrayContent(result)
                };
                response.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment");
                response.Content.Headers.ContentType = new MediaTypeHeaderValue("application/octet-stream");
                response.Content.Headers.ContentLength = result.Length;
                response.Content.Headers.ContentDisposition.FileName =
                    string.Format("test-{0}.xlsx", DateTime.Now.ToString("yyyyMMddHHmmss"));
                return response;

            }
            catch (Exception ex) {

               
                var result = export.ExceptionFileStream(ex.Message);

                //buffer = result.ToArray();


                var response = new HttpResponseMessage(HttpStatusCode.OK)
                {
                    Content = new ByteArrayContent(result)
                };
                response.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment");
                response.Content.Headers.ContentType = new MediaTypeHeaderValue("application/octet-stream");
                response.Content.Headers.ContentLength = result.Length;
                response.Content.Headers.ContentDisposition.FileName = string.Format("test-{0}.xlsx", DateTime.Now.ToString("yyyyMMddHHmmss"));
                return response;
            }
        }

public class TestExport
    {

        public byte[] FillFileStream(List<TestModel> list)
        {

            var dataTable = ListToDataTable(list.Select(m => new
            {
                m.testId,
                m.testName
            }).ToList());

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

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


            XSSFWorkbook workbook = new XSSFWorkbook();
            try
            {

                CreateSheet(workbook, "test", dataTable, "test", oldColumn, newColumn);


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

                //workbook = null;

                byte[] tempBuffer;
                using (var tempStream = new NPOIMemoryStream())
                {
                    tempStream.AllowClose = false;
                    workbook.Write(tempStream);
                    tempStream.Flush();
                    tempStream.Seek(0, SeekOrigin.Begin);
                    tempStream.AllowClose = true;
                    tempBuffer = tempStream.ToArray();
                }
                return tempBuffer;
            }
            finally
            {
                workbook.Close();
            }
        }


        public byte[] ExceptionFileStream(string exceptionStr)
        {
            XSSFWorkbook workbook = new XSSFWorkbook();
            try
            {
                var tempSheet = workbook.CreateSheet("test");
                var tempHeader = tempSheet.CreateRow(0);
                tempHeader.CreateCell(0, CellType.String).SetCellValue(exceptionStr);

                byte[] tempBuffer;
                using (var tempStream = new NPOIMemoryStream())
                {
                    tempStream.AllowClose = false;
                    workbook.Write(tempStream);
                    tempStream.Flush();
                    tempStream.Seek(0, SeekOrigin.Begin);
                    tempStream.AllowClose = true;
                    tempBuffer = tempStream.ToArray();
                }
                return tempBuffer;
            }
            finally
            {
                workbook.Close();
                workbook = null;
            }
        }


        /// <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(XSSFWorkbook 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;
        }
    }

public class NPOIMemoryStream : MemoryStream
    {
        public NPOIMemoryStream()
        {
            AllowClose = true;
        }

        public bool AllowClose { get; set; }

        public override void Close()
        {
            if (AllowClose)
                base.Close();
        }
    }

js调用

exportTest(){
                
                var url=`Api/Test/Generate?executeBeginTime=${this.searchCondition.startTime}&executeEndTime=${this.searchCondition.endTime}&executeStatus=${this.searchCondition.taskState}&taskCatalog=${this.searchCondition.taskType}&etlName=${this.searchCondition.etlname}&taskId=${this.searchCondition.taskId}`;
                location.href=url;
            }