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("导出成功"); }
导出效果图如下：
赞 (0)