NPOI封裝通用的導出模板

　　在後臺管理系統中導出Excel功能是必不可少的，該模板可以幫助我們簡單優雅的實現導出功能，支持導出大數據。封裝如下：

　　public class NPOIExcelExporterBase
    {
        protected ICellStyle HeadStyle { get; set; }
        protected ICellStyle TextStyle { get; set; }

        /// <summary>
        /// 創建Excel 文件
        /// </summary>
        /// <param name="fileName">Excel文件名</param>
        /// <param name="creator">委托</param>
        /// <returns></returns>
        protected string CreateExcel(string fileName, Action<IWorkbook> creator)
        {
            var wb = new XSSFWorkbook();
            var sWorkbook = new SXSSFWorkbook(wb, 1000);
            var outputFilePath = "";
            try
            {
                HeadStyle = DefaultHeaderCellStyle(sWorkbook);
                TextStyle = DefaulTextCellStyle(sWorkbook);
                creator(sWorkbook);

                outputFilePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, fileName);
                using (var fs = new FileStream(outputFilePath, FileMode.Create, FileAccess.Write))
                {
                    sWorkbook.Write(fs);
                }
            }
            finally
            {
                sWorkbook.Dispose();
            }
            return outputFilePath;
        }


        /// <summary>
        /// 添加表頭
        /// </summary>
        protected void AddHeader(ISheet sheet, params string[] headerTexts)
        {
            if (headerTexts == null || headerTexts.Length == 0)
            {
                return;
            }

            ICell cell;
            var headerRow = sheet.CreateRow(0);
            for (var i = 0; i < headerTexts.Length; i++)
            {
                cell = headerRow.CreateCell(i);
                cell.SetCellValue(headerTexts[i]);
                cell.CellStyle = HeadStyle;

                //根據位元組長度計算列寬
                sheet.SetColumnWidth(i, (Encoding.GetEncoding("gb2312").GetBytes(headerTexts[i]).Length + 10) * 256);
            }
        }

        /// <summary>
        /// 添加表格內容
        /// </summary>
        protected void AddBody<T>(ISheet sheet, IList<T> list, params Func<T, object>[] propertySelectors)
        {
            if (list == null || !list.Any())
            {
                return;
            }

            if (propertySelectors == null || !propertySelectors.Any())
            {
                return;
            }

            IRow row;
            ICell cell;
            var startRowIndex = 1;
            foreach (var item in list)
            {
                row = sheet.CreateRow(startRowIndex++);
                for (var i = 0; i < propertySelectors.Length; i++)
                {
                    cell = row.CreateCell(i);
                    cell.SetCellValue(propertySelectors[i](item)?.ToString());
                    cell.CellStyle = TextStyle;
                }
            }
        }

        /// <summary>
        /// 預設表頭樣式
        /// </summary>
        /// <param name="workbook"></param>
        /// <returns></returns>
        private ICellStyle DefaultHeaderCellStyle(IWorkbook workbook)
        {
            var format = workbook.CreateDataFormat();
            var cellStyle = workbook.CreateCellStyle();
            cellStyle.Alignment = HorizontalAlignment.Center;
            cellStyle.VerticalAlignment = VerticalAlignment.Center;
            cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Yellow.Index;
            cellStyle.FillPattern = FillPattern.SolidForeground;
            cellStyle.DataFormat = format.GetFormat("@");
            cellStyle.BorderBottom = BorderStyle.Thin;
            cellStyle.BorderLeft = BorderStyle.Thin;
            cellStyle.BorderRight = BorderStyle.Thin;
            cellStyle.BorderTop = BorderStyle.Thin;
            cellStyle.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
            cellStyle.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
            cellStyle.RightBorderColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
            cellStyle.TopBorderColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
            var headFont = workbook.CreateFont();
            headFont.FontHeightInPoints = 11;
            headFont.IsBold = true;
            cellStyle.SetFont(headFont);
            return cellStyle;
        }

        /// <summary>
        /// 預設單元格文本樣式
        /// </summary>
        /// <param name="workbook"></param>
        /// <returns></returns>
        private ICellStyle DefaulTextCellStyle(IWorkbook workbook)
        {
            var format = workbook.CreateDataFormat();
            var cellStyle = workbook.CreateCellStyle();
            cellStyle.Alignment = HorizontalAlignment.Center;
            cellStyle.VerticalAlignment = VerticalAlignment.Center;
            cellStyle.WrapText = false;
            cellStyle.DataFormat = format.GetFormat("@");
            cellStyle.BorderBottom = BorderStyle.Thin;
            cellStyle.BorderLeft = BorderStyle.Thin;
            cellStyle.BorderRight = BorderStyle.Thin;
            cellStyle.BorderTop = BorderStyle.Thin;
            cellStyle.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
            cellStyle.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
            cellStyle.RightBorderColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
            cellStyle.TopBorderColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
            var curFont = workbook.CreateFont();
            curFont.FontHeightInPoints = 10;
            cellStyle.SetFont(curFont);
            return cellStyle;
        }
    }

下麵是使用的示例：

    public class Person
    {
        public string Name { get; set; }
        public int Age { get; set; }
        public DateTime Birthday { get; set; }
    }

    public sealed class ExportPersonExcel : NPOIExcelExporterBase
    {
        public string Export(List<Person> list)
        {
            var excelPath =
                CreateExcel("PersonList.xlsx", workbook =>
                {
                    var sheet = workbook.CreateSheet();
                    //HeadStyle = HeaderCellStyle(workbook); //替換預設的表頭樣式

                    //表頭
                    AddHeader(sheet,
                        "姓名", "年齡", "出生日期");

                    //表格內容
                    AddBody(sheet, list,
                        ex => ex.Name,
                        ex => ex.Age,
                        ex => ex.Birthday.ToString("yyyy-MM-dd"));

                    //sheet.SetColumnWidth(0, 20 * 256); //修改列的寬度
                });
            return excelPath;
        }

        /// <summary>
        /// 表頭樣式
        /// </summary>
        /// <param name="workbook"></param>
        /// <returns></returns>
        private ICellStyle HeaderCellStyle(IWorkbook workbook)
        {
            var cellStyle = workbook.CreateCellStyle();
            cellStyle.Alignment = HorizontalAlignment.Center;
            cellStyle.VerticalAlignment = VerticalAlignment.Center;
            cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Red.Index;
            cellStyle.FillPattern = FillPattern.SolidForeground;
            cellStyle.BorderBottom = BorderStyle.Thin;
            cellStyle.BorderLeft = BorderStyle.Thin;
            cellStyle.BorderRight = BorderStyle.Thin;
            cellStyle.BorderTop = BorderStyle.Thin;
            cellStyle.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
            cellStyle.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
            cellStyle.RightBorderColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
            cellStyle.TopBorderColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
            var headFont = workbook.CreateFont();
            headFont.FontHeightInPoints = 11;
            headFont.IsBold = true;
            cellStyle.SetFont(headFont);
            return cellStyle;
        }
    }

        static void Main(string[] args)
        {
            var list = new List<Person>()
            {
                new Person{Name = "Tony",Age = 20,Birthday = DateTime.Now},
                new Person{Name = "Jack",Age = 23,Birthday = DateTime.Now},
                new Person{Name = "張三",Age = 56,Birthday = DateTime.Now},
                new Person{Name = "李四",Age = 36,Birthday = DateTime.Now}
            };

            var exporter = new ExportPersonExcel();
            var path = exporter.Export(list);
            Console.WriteLine("導出成功");
        }    

導出效果圖如下：