C# 使用NPOI导出DataTable到Excel,首行冻结,添加筛选,填充颜色,列宽自适应
使用NuGet安装NPOI,添加以下命名空间
using NPOI.HSSF.UserModel; using NPOI.SS.UserModel; using NPOI.SS.Util; using NPOI.XSSF.UserModel;
添加类 ExcelExporter
public static class ExcelExporter { ////// DataTable导出Excel /// /// 数据源 /// 保存的文件名 /// 表名 public static void ExportToExcel(this DataTable dataTable, string fileName, string sheetName = "Sheet1") { if (dataTable == null || dataTable.Rows.Count == 0) throw new Exception("No data to export"); ISheet sheet = null; IWorkbook workbook = null; try { using (FileStream fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite)) { if (fileName.IndexOf(".xlsx") > 0) // 2007版本 workbook = new XSSFWorkbook(); else if (fileName.IndexOf(".xls") > 0) // 2003版本 workbook = new HSSFWorkbook(); else throw new NotSupportedException("Not supported file extension"); if (string.IsNullOrEmpty(sheetName)) sheetName = "Sheet1"; sheet = workbook.CreateSheet(sheetName); ICellStyle headerStyle = workbook.CreateCellStyle(); //首行填充黄色 headerStyle.FillForegroundColor = IndexedColors.Yellow.Index; headerStyle.FillPattern = FillPattern.SolidForeground; IRow row = sheet.CreateRow(0); for (int columnIndex = 0; columnIndex < dataTable.Columns.Count; columnIndex++) { var cell = row.CreateCell(columnIndex); cell.CellStyle = headerStyle; cell.SetCellValue(dataTable.Columns[columnIndex].ColumnName); //设置列名 } for (int rowIndex = 0; rowIndex < dataTable.Rows.Count; rowIndex++) { row = sheet.CreateRow(rowIndex + 1); for (int columnIndex = 0; columnIndex < dataTable.Columns.Count; columnIndex++) { row.CreateCell(columnIndex).SetCellValue(Convert.ToString(dataTable.Rows[rowIndex][columnIndex])); } } for (int columnIndex = 0; columnIndex < dataTable.Columns.Count; columnIndex++) { sheet.AutoSizeColumn(columnIndex); //自适应宽度 } sheet.SetAutoFilter(new CellRangeAddress(0, 0, 0, dataTable.Columns.Count - 1)); //首行筛选 sheet.CreateFreezePane(dataTable.Columns.Count, 1); //首行冻结 workbook.Write(fs); //写入到excel } } finally { if (workbook != null) workbook.Close(); } } }
使用时传入文件路径,即可将DataTable导出到指定位置。
如要导出集合类数据IEnumerable