NPOI - 从模板创建新 Excel 时条件格式不起作用

NPOI是一个强大的.NET开源库，用于处理Microsoft Office格式的文件。其中，NPOI可以用于创建和修改Excel文件。然而，最近在使用NPOI的过程中，我遇到了一个问题：在使用模板创建新Excel文件时，条件格式似乎不起作用。本文将详细介绍这个问题，并提供一个案例代码来解决它。

csharp
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System;
using System.IO;
public class ExcelCreator
{
    public void CreateNewFileFromTemplate(string templatePath, string outputPath)
    {
        using (FileStream fs = new FileStream(templatePath, FileMode.Open, FileAccess.Read))
        {
            IWorkbook workbook = new HSSFWorkbook(fs);
            ISheet sheet = workbook.GetSheetAt(0);
            IConditionalFormattingHelper helper = sheet.SheetConditionalFormatting;
            ISheetConditionalFormatting formatting = helper.CreateConditionalFormatting();
            IConditionalFormattingRule rule = helper.CreateConditionalFormattingRule(ComparisonOperator.GreaterThan, "TODAY()");
            IPatternFormatting patternFormatting = rule.CreatePatternFormatting();
            patternFormatting.FillBackgroundColor = IndexedColors.Red.Index;
            formatting.AddConditionalFormatting(CellRangeAddress.ValueOf("A2:A100"), rule);
            sheet.SheetConditionalFormatting.AddConditionalFormatting(formatting);
            using (FileStream outputFile = new FileStream(outputPath, FileMode.Create, FileAccess.Write))
            {
                workbook.Write(outputFile);
            }
        }
    }
}

csharp
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System;
using System.IO;
public class ExcelCreator
{
    public void CreateNewFileFromTemplate(string templatePath, string outputPath)
    {
        using (FileStream fs = new FileStream(templatePath, FileMode.Open, FileAccess.Read))
        {
            IWorkbook templateWorkbook = new HSSFWorkbook(fs);
            IWorkbook newWorkbook = new HSSFWorkbook();
            ISheet newSheet = newWorkbook.CreateSheet("Sheet1");
            CopyConditionalFormatting(templateWorkbook, newWorkbook, newSheet);
            using (FileStream outputFile = new FileStream(outputPath, FileMode.Create, FileAccess.Write))
            {
                newWorkbook.Write(outputFile);
            }
        }
    }
    private void CopyConditionalFormatting(IWorkbook templateWorkbook, IWorkbook newWorkbook, ISheet newSheet)
    {
        for (int i = 0; i < templateWorkbook.NumSheets; i++)
        {
            ISheet templateSheet = templateWorkbook.GetSheetAt(i);
            ISheet newSheet = newWorkbook.GetSheetAt(i);
            for (int j = 0; j < templateSheet.NumConditionalFormattings; j++)
            {
                IConditionalFormatting templateFormatting = templateSheet.GetConditionalFormattingAt(j);
                IConditionalFormatting newFormatting = newSheet.SheetConditionalFormatting.CreateConditionalFormatting();
                for (int k = 0; k < templateFormatting.NumberOfRules; k++)
                {
                    IConditionalFormattingRule templateRule = templateFormatting.GetRule(k);
                    IConditionalFormattingRule newRule = newFormatting.CreateConditionalFormattingRule(templateRule);
                    newFormatting.AddConditionalFormatting(templateFormatting.GetFormattingRanges(k), newRule);
                }
                newSheet.SheetConditionalFormatting.AddConditionalFormatting(newFormatting);
            }
        }
    }
}