NPOI技术导出Excel表格示例代码

记录ASP.NET系统，使用NPOI技术导出Excel表格的代码

记得项目导入NPOI的依赖喔，具体可问ChatGPT，这里我就不赘述了。

IWorkbook workbook = new HSSFWorkbook();   xls
IWorkbook workbook = new XSSFWorkbook();   xlsx

这里记录一个使用NPOI技术，导出并渲染Excel格式的方法，

public static void ExportByNPOI(GridView gridView, string path)
{
               
    HSSFWorkbook workbook = new HSSFWorkbook();
    ISheet sheet = workbook.CreateSheet("CostReport");

    try
    {
        #region 给Excel填充内容和样式

        #region 设置样式

        // 设置标头的样式
        ICellStyle styleHead = workbook.CreateCellStyle();
        IFont fontHead = workbook.CreateFont();
        fontHead.Boldweight = (short)FontBoldWeight.BOLD;
        fontHead.FontHeightInPoints = 12;
        fontHead.Color = IndexedColors.WHITE.Index;
        styleHead.SetFont(fontHead);
        styleHead.Alignment = HorizontalAlignment.CENTER;
        styleHead.VerticalAlignment = VerticalAlignment.CENTER;
        // 自定义颜色
        HSSFPalette palette = workbook.GetCustomPalette();
        // HSSFWorkbook 中，可以自定义的颜色索引范围通常是 8 到 63
        palette.SetColorAtIndex((short)10, 179, 202, 231);
        HSSFColor myColor = palette.FindColor(179, 202, 231);
        styleHead.FillForegroundColor = myColor.GetIndex();
        styleHead.FillPattern = FillPatternType.SOLID_FOREGROUND;               

        //设置背景间隔颜色，淡蓝色，奇数行
        ICellStyle styleBlue = workbook.CreateCellStyle();
        styleBlue.Alignment = HorizontalAlignment.CENTER;
        styleBlue.VerticalAlignment = VerticalAlignment.CENTER;
        styleBlue.DataFormat = workbook.CreateDataFormat().GetFormat("#,##0.00");
        HSSFPalette paletteBlue = workbook.GetCustomPalette();
        // RGB颜色，可以使用Excel的填充颜色--自定义--去设置不同的RGB参数，然后看看得到什么视觉效果的颜色
        paletteBlue.SetColorAtIndex((short)11, 239, 243, 251);
        HSSFColor myColorBlue = paletteBlue.FindColor(239, 243, 251);
        styleBlue.FillForegroundColor = myColorBlue.GetIndex();
        styleBlue.FillPattern = FillPatternType.SOLID_FOREGROUND;

        //设置背景间隔颜色，白色，偶数行
        ICellStyle styleWhite = workbook.CreateCellStyle();
        styleWhite.Alignment = HorizontalAlignment.CENTER;
        styleWhite.VerticalAlignment = VerticalAlignment.CENTER;
        styleWhite.DataFormat = workbook.CreateDataFormat().GetFormat("#,##0.00");
        styleWhite.FillForegroundColor = IndexedColors.WHITE.Index;
        styleWhite.FillPattern = FillPatternType.SOLID_FOREGROUND;

        #endregion

        // 给标头填充数据，并设置样式
        IRow row = sheet.CreateRow(0);

        int headLength = gridView.HeaderRow.Cells.Count;
        for (int i = 0; i < headLength; i++)
        {
            ICell cell = row.CreateCell(i);
            cell.SetCellValue(HttpUtility.HtmlDecode(gridView.HeaderRow.Cells[i].Text).Trim());
            cell.CellStyle = styleHead;
        }

        int rowLength = gridView.Rows.Count;
        // 给内容填充数据
        for (int i = 0; i < rowLength; i++)
        {
            IRow excelRow = sheet.CreateRow(i + 1);
            for (int j = 0; j < gridView.Rows[i].Cells.Count; j++)
            {
                ICell excelCell = excelRow.CreateCell(j);
                excelCell.SetCellValue(HttpUtility.HtmlDecode(gridView.Rows[i].Cells[j].Text).Trim());
                if ((i + 1) % 2 == 1)
                {
                    excelCell.CellStyle = styleBlue;
                }else
                {
                    excelCell.CellStyle = styleWhite;
                }
            }
        }                

        // 定义一个集合来存储，每个Category对应的范围，记录下标，到时候合并单元格
        List<Tuple<int, int>> list1 = new List<Tuple<int, int>>();

        int headCount = 0;
        int tailCount = 0;

        string categoryName = gridView.Rows[0].Cells[0].Text;
        for (int i = 1; i < rowLength; i++)
        {                  
            string name = gridView.Rows[i].Cells[0].Text;
            if (categoryName.Equals(name))
            {
                tailCount++;
            }else
            {
                list1.Add(Tuple.Create(headCount, tailCount));
                tailCount++;
                headCount = tailCount;
                categoryName = name;
            }                   
        }
        // 合并单元格
        foreach (Tuple<int, int> tuple in list1)
        {
            // 起止行号，终止行号，起止列号，终止列号
            sheet.AddMergedRegion(new CellRangeAddress(tuple.Item1 + 1, tuple.Item2 + 1, 0, 0)); 
        }
       
        // 自动调整列宽
        for (int i = 0; i <= sheet.GetRow(0).LastCellNum; i++)
        {
            // 自动调整列宽
            //sheet.AutoSizeColumn(i);
            if(i == 0 || i == 1)
            {
            	// 手动调整列宽，列宽的单位是字符宽度（1/256 个字符的宽度）
            	// 最窄的列宽：大约 1,000 - 2,000 单位。适用于非常紧凑的列。
				// 普通列宽：大约 6,000 - 10,000 单位。适合大多数常规数据展示。
				// 较宽的列宽：大约 12,000 - 20,000 单位。适合需要显示较长数据的列。
                sheet.SetColumnWidth(i, 6000);
            }else
            {
                sheet.SetColumnWidth(i, 4000);
            }
            
        }               

        // 固定单元格，从1开始，列数，行数
        sheet.CreateFreezePane(5, 1);

        using (FileStream fileStream = new FileStream(path, FileMode.Create, FileAccess.Write))
        {
            workbook.Write(fileStream);
        }

        #endregion
    }
    catch (Exception ex)
    {
        throw ex;
    }            
}

也可以直接使用IndexedColors中的颜色：

ICellStyle style3 = workbook.CreateCellStyle();
style3.Alignment = HorizontalAlignment.LEFT;
style3.DataFormat = workbook.CreateDataFormat().GetFormat("#,##0.00");
// IndexedColors中的颜色，可以去网上搜IndexedColors的颜色对比图
style3.FillForegroundColor = IndexedColors.PALE_BLUE.Index;
style3.FillPattern = FillPatternType.SOLID_FOREGROUND;

关于xlsx格式的Excel，怎么自定义单元格的背景颜色

有两种方式，第一种方式：

XSSFCellStyle rowsStyleColor = (XSSFCellStyle)workbook.CreateCellStyle();
    XSSFColor xssfColor = new XSSFColor();
    //根据自己需要设置RGB
    byte[] colorRgb = { (byte)252, (byte)139, (byte)139 };
    xssfColor.SetRgb(colorRgb);
    rowsStyleColor.FillForegroundColorColor = xssfColor;
    rowsStyleColor.FillPattern = FillPatternType.SOLID_FOREGROUND;

第二种方式：

	//设置背景间隔颜色，淡蓝色，奇数行
    ICellStyle styleBlue = workbook.CreateCellStyle();
    styleBlue.Alignment = HorizontalAlignment.CENTER;
    styleBlue.VerticalAlignment = VerticalAlignment.CENTER;
    styleBlue.DataFormat = workbook.CreateDataFormat().GetFormat("#,##0.00");                            
    styleBlue.FillForegroundColor = 0;
    ((XSSFColor)styleBlue.FillForegroundColorColor).SetRgb(new byte[] { 239, 243, 251 });
    styleBlue.FillPattern = FillPatternType.SOLID_FOREGROUND;

把一个Excel文件读取到XSSFWorkbook 的代码如下：

XSSFWorkbook workbook;
//打开模板文件到文件流中
using (FileStream file = new FileStream(Utility.Enums.GetApplicationPath() + "Template\\AccountSummary.xlsx", FileMode.Open, FileAccess.Read))
{
    //将文件流中模板加载到工作簿对象中
    workbook = new XSSFWorkbook(file);
}
ISheet sheetSummary = workbook.GetSheetAt(0);