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
    {
        /// <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即可。方法大同小异，这里就不写了