使用NuGet安装NPOI，添加以下命名空间
|
1 2 3 4 |
using NPOI.HSSF.UserModel; using NPOI.SS.UserModel; using NPOI.SS.Util; using NPOI.XSSF.UserModel; |
添加类 ExcelExporter
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 |
public static class ExcelExporter { /// <summary> /// DataTable导出Excel /// </summary> /// <param name="dataTable">数据源</param> /// <param name="fileName">保存的文件名</param> /// <param name="sheetName">表名</param> 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<T>，使用反射转成DataTable即可。方法大同小异，这里就不写了
from:https://www.qyyshop.com/info/813140.html