导出数据到Excel 2007 多个Sheet页(NPOI)(web版)

泛型数据源：

public static string Export2007<T>(IList<T> sourceData)
        {
            if (sourceData == null) throw new ArgumentNullException("sourceData");

            IWorkbook workbook = new XSSFWorkbook();
            var properties = TypeDescriptor.GetProperties(typeof(T));

            //一个Sheet导出的记录数
            const int pageSize = 20000;
            //记录总数
            var rowCount = sourceData.Count;
            //总Sheet数
            var pageCount = (rowCount + pageSize - 1) / pageSize;
            var count = 1;
            while (count <= pageCount)
            {
                var list = count == 1
                    ? sourceData.Skip(0).Take(pageSize).ToList()
                    : sourceData.Skip((count - 1)*pageSize).Take(pageSize).ToList();

                if (list.Any())
                {
                    var sheet = workbook.CreateSheet(String.Format("Sheet{0}", count));
                    var row = sheet.CreateRow(0);

                    for (var i = 0; i < properties.Count; i++)
                    {
                        var cell = row.CreateCell(i);
                        cell.SetCellValue(String.IsNullOrEmpty(properties[i].Description)
                            ? properties[i].Name
                            : properties[i].Description);
                    }

                    for (var i = 0; i < list.Count; i++)
                    {
                        row = sheet.CreateRow(i + 1);

                        for (var j = 0; j < properties.Count; j++)
                        {
                            var cell = row.CreateCell(j);

                            var value = properties[j].GetValue(list[i]);

                            cell.SetCellValue(value == null ? String.Empty
                                : (value is DateTime ? ((DateTime)value).ToString("yyyy-MM-dd HH:mm") : value.ToString()));
                        }
                    }
                }

                count++;
            }
            var dir =
                HttpContext.Current.Server.MapPath(String.Format("~/Resources/{0}/", DateTime.Now.ToString("yyyyMMdd")));

            if (!Directory.Exists(dir))
            {
                Directory.CreateDirectory(dir);
            }
            var fileName = dir + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";

            var fileStream = new FileStream(fileName, FileMode.Create, FileAccess.Write);
            workbook.Write(fileStream);
            return fileName;
        }

Datatable 数据源：

public static string RenderDataTableToExcel(DataTable SourceTable)
        {
            XSSFWorkbook workbook = null;
            ISheet sheet = null;
            XSSFRow headerRow = null;
            try
            {
                workbook = new XSSFWorkbook();
                sheet = workbook.CreateSheet();
                headerRow = (XSSFRow)sheet.CreateRow(0);
                foreach (DataColumn column in SourceTable.Columns)
                    headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                int rowIndex = 1;
                foreach (DataRow row in SourceTable.Rows)
                {
                    XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex);
                    foreach (DataColumn column in SourceTable.Columns)
                        dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
                    ++rowIndex;
                }
                //列宽自适应，只对英文和数字有效
                for (int i = 0; i <= SourceTable.Columns.Count; ++i)
                {
                    sheet.AutoSizeColumn(i);
                }

                var dir =
               HttpContext.Current.Server.MapPath(String.Format("~/Resources/{0}/", DateTime.Now.ToString("yyyyMMdd")));

                if (!Directory.Exists(dir))
                {
                    Directory.CreateDirectory(dir);
                }
                var fileName = dir + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";

                var fileStream = new FileStream(fileName, FileMode.Create, FileAccess.Write);
                workbook.Write(fileStream);
                return fileName;
            }
            catch (Exception ex)
            {
                return null;
            }
            finally
            {
                sheet = null;
                headerRow = null;
                workbook = null;
            }
        }

前台代码：

            

public ActionResult ExportExecl(int? pid)
        {
            if (!pid.HasValue) return null;

            var list = _userinfoBo.GetAnswerByUser(pid.Value);

            if (null == list) return null;

            var newList = list.Select(question => new ResultExecl()
            {
              //加载数据
            }).ToList();

            var filePath = ExcelUtil.Export2007(newList);

            var fileName = Path.GetFileName(filePath);

            return File(filePath, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", fileName);
        }