如何用OpenXml/ClosedXml实现Excel公式自动填充及引用更新

场景说明 #

我们组织员工使用包含表格数据和公式的Excel表格，需要通过C#对接数据提供程序。使用OpenXml或ClosedXml创建/更新Excel时，无法实现Excel手动拖动填充时的自动更新公式相对引用功能：

• 示例公式（第5行单元格）：=IFS(P5=$B$24,$E$24,P5=$B$25,$E$25,P5=$B$26,$E$26)，该公式用P5匹配固定查找表，手动拖动时会自动将P5改为P6，绝对引用$B$24等保持不变。
• Excel宏录制的自动填充代码（ExcelScript）可以实现，但ClosedXml无此功能；OpenXml虽有相关API，但无可用示例。

现有问题 #

1. 主要问题：C#代码中实现类似Excel的autoFill功能，自动更新公式中的相对引用，适配用户修改的任意首行公式。
2. 次要问题：简化现有OpenXml代码，实现单元格值/公式的正确写入（原代码写入失败）。

解决方案 #

一、次要问题：简化并修复OpenXml写入代码 #

原代码失败原因包括未处理目标单元格不存在的情况、未正确保存WorksheetPart。以下是修复后的简化代码：

using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

public void UpdateExcelCell(string filePath)
{
    using (var document = SpreadsheetDocument.Open(filePath, true))
    {
        var workbookPart = document.WorkbookPart;
        var sheet = workbookPart.Workbook.Descendants<Sheet>().First();
        var worksheetPart = (WorksheetPart)workbookPart.GetPartById(sheet.Id);
        var worksheet = worksheetPart.Worksheet;

        // 获取源单元格L5
        var sourceCell = GetOrCreateCell(worksheet, "L5");
        if (sourceCell.CellFormula == null) return;

        // 写入目标单元格L6，先手动更新引用（后续扩展为自动处理）
        var targetCell = GetOrCreateCell(worksheet, "L6");
        targetCell.CellFormula = new CellFormula(UpdateFormulaRowReference(sourceCell.CellFormula.Text, 1));
        targetCell.DataType = CellValues.Number; // 根据实际数据类型调整

        // 必须保存WorksheetPart
        worksheetPart.Worksheet.Save();
    }
}

// 辅助方法：获取或创建指定单元格
private Cell GetOrCreateCell(Worksheet worksheet, string cellReference)
{
    var rowIndex = int.Parse(cellReference.Substring(1));
    var columnName = cellReference.Substring(0, cellReference.Length - rowIndex.ToString().Length);
    
    // 获取或创建行
    var row = worksheet.Descendants<Row>().FirstOrDefault(r => r.RowIndex == rowIndex) 
              ?? new Row { RowIndex = rowIndex };
    if (!worksheet.Elements<Row>().Contains(row))
    {
        worksheet.Append(row);
    }

    // 获取或创建单元格
    var cell = row.Descendants<Cell>().FirstOrDefault(c => c.CellReference == cellReference) 
               ?? new Cell { CellReference = cellReference };
    if (!row.Elements<Cell>().Contains(cell))
    {
        row.Append(cell);
    }

    return cell;
}

二、主要问题：实现公式自动填充（更新相对引用） #

方案1：使用EPPlus库（推荐）

EPPlus提供了原生的AutoFill方法，完全模拟Excel的自动填充行为，自动处理相对/绝对引用：

using OfficeOpenXml;
using System.IO;

public void AutoFillFormulaWithEPPlus(string filePath)
{
    // 根据实际许可证设置
    ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
    
    using (var package = new ExcelPackage(new FileInfo(filePath)))
    {
        var worksheet = package.Workbook.Worksheets.First();
        
        // 源范围：第5行L列；填充范围：第5-10行L列
        var sourceRange = worksheet.Cells["L5"];
        var fillRange = worksheet.Cells["L5:L10"];
        
        // 执行自动填充，自动更新引用
        sourceRange.AutoFill(fillRange, eAutoFillType.FillDefault);
        
        package.Save();
    }
}

方案2：手动解析公式并更新引用（OpenXml原生实现）

如果无法使用第三方库，可编写正则表达式解析公式中的单元格引用，根据行/列偏移量更新相对引用：

// 根据行偏移量更新公式中的相对行引用
private string UpdateFormulaRowReference(string originalFormula, int rowOffset)
{
    // 正则匹配所有单元格引用（支持A-Z、AA-AZ等列名，兼容相对/绝对/混合引用）
    var cellRefRegex = new Regex(@"([$]?[A-Za-z]+)[$]?(\d+)");
    
    return cellRefRegex.Replace(originalFormula, match =>
    {
        var columnPart = match.Groups[1].Value;
        var rowPart = match.Groups[2].Value;
        var isRowAbsolute = match.Value.Contains("$", StringComparison.Ordinal) && 
                            match.Value.IndexOf("$", columnPart.Length) != -1;

        // 仅更新非绝对行引用
        int newRow = isRowAbsolute ? int.Parse(rowPart) : int.Parse(rowPart) + rowOffset;
        
        return $"{columnPart}{(isRowAbsolute ? "$" : "")}{newRow}";
    });
}

// 批量填充示例：从L5填充到L6-L10
public void BatchFillFormulas(string filePath)
{
    using (var document = SpreadsheetDocument.Open(filePath, true))
    {
        var workbookPart = document.WorkbookPart;
        var sheet = workbookPart.Workbook.Descendants<Sheet>().First();
        var worksheetPart = (WorksheetPart)workbookPart.GetPartById(sheet.Id);
        var worksheet = worksheetPart.Worksheet;

        var sourceCell = GetOrCreateCell(worksheet, "L5");
        if (sourceCell.CellFormula == null) return;

        // 批量填充到第6-10行
        for (int targetRow = 6; targetRow <= 10; targetRow++)
        {
            var targetCellRef = $"L{targetRow}";
            var targetCell = GetOrCreateCell(worksheet, targetCellRef);
            int offset = targetRow - 5; // 相对于源行的偏移量
            targetCell.CellFormula = new CellFormula(UpdateFormulaRowReference(sourceCell.CellFormula.Text, offset));
            targetCell.DataType = CellValues.Number;
        }

        worksheetPart.Worksheet.Save();
    }
}

内容的提问来源于stack exchange，提问作者Roland Kwee