如何在Document.OpenXML中实现Excel公式拖拽填充功能

实现原理 #

Excel向下拖拽公式的核心逻辑是根据源单元格和目标单元格的位置偏移量，自动调整公式内的相对引用，绝对/混合引用按规则保留固定部分，将调整后的公式写入目标单元格后，标记文件打开时强制重算即可，不需要在代码里手动计算公式结果。 #

核心步骤 #

• 定位源单元格，读取原始公式
• 计算源单元格到每个目标单元格的行、列偏移值
• 按偏移量调整公式内的单元格引用：带$的绝对引用部分不偏移，无$的相对引用部分按偏移量更新
• 将调整后的公式写入对应目标单元格，清除原有单元格缓存值
• 配置工作簿计算属性，让Excel/WPS打开文件时自动完成全量公式计算

可直接复用的代码片段 #

需要先引入DocumentFormat.OpenXml Nuget包，以下是完整实现代码：

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.Text.RegularExpressions;

/// <summary>
/// 模拟Excel拖拽操作填充公式
/// </summary>
/// <param name="filePath">目标Excel文件路径</param>
/// <param name="sheetName">公式所在工作表名称</param>
/// <param name="sourceCellRef">源公式单元格地址，例："B2"</param>
/// <param name="targetRange">填充目标范围，例："B3:B10"</param>
public static void FillFormulaLikeExcelDrag(string filePath, string sheetName, string sourceCellRef, string targetRange)
{
    using (SpreadsheetDocument doc = SpreadsheetDocument.Open(filePath, true))
    {
        WorkbookPart wbPart = doc.WorkbookPart;
        WorksheetPart wsPart = wbPart.Worksheet.Descendants<Sheet>()
            .First(s => s.Name == sheetName)
            .WorksheetPart;
        Worksheet ws = wsPart.Worksheet;
        SheetData sheetData = ws.GetFirstChild<SheetData>();

        // 读取源单元格公式
        Cell sourceCell = ws.Descendants<Cell>().First(c => c.CellReference == sourceCellRef);
        string sourceFormula = sourceCell.CellFormula.Text;
        (int sourceRow, int sourceCol) = ParseCellRef(sourceCellRef);

        // 遍历所有目标单元格写入调整后的公式
        foreach (var targetCellRef in ParseRangeToCellList(targetRange))
        {
            (int targetRow, int targetCol) = ParseCellRef(targetCellRef);
            int rowOffset = targetRow - sourceRow;
            int colOffset = targetCol - sourceCol;
            string adjustedFormula = AdjustFormulaWithOffset(sourceFormula, rowOffset, colOffset);

            Cell targetCell = ws.Descendants<Cell>().FirstOrDefault(c => c.CellReference == targetCellRef);
            if (targetCell == null)
            {
                targetCell = new Cell() { CellReference = targetCellRef };
                sheetData.AppendChild(targetCell);
            }
            // 清除旧缓存值，写入新公式
            targetCell.CellValue = null;
            targetCell.CellFormula = new CellFormula(adjustedFormula);
            targetCell.DataType = null;
        }

        // 标记打开文件时强制全量计算，避免显示旧缓存值
        wbPart.Workbook.CalculationProperties = new CalculationProperties()
        {
            FullCalculationOnLoad = true,
            ForceFullCalculation = true
        };

        ws.Save();
    }
}

// 解析单元格地址为行号、列索引（例：A1 -> (1,1)，B3 -> (3,2)）
private static (int row, int col) ParseCellRef(string cellRef)
{
    Match match = Regex.Match(cellRef, @"^([A-Z]+)(\d+)$");
    string colStr = match.Groups[1].Value;
    int row = int.Parse(match.Groups[2].Value);
    int col = 0;
    foreach (char c in colStr)
    {
        col = col * 26 + (c - 'A' + 1);
    }
    return (row, col);
}

// 列索引转字母标识（例：1->A，27->AA）
private static string ColIndexToLetter(int colIndex)
{
    string result = "";
    while (colIndex > 0)
    {
        int mod = (colIndex - 1) % 26;
        result = (char)('A' + mod) + result;
        colIndex = (colIndex - mod) / 26;
    }
    return result;
}

// 按偏移量调整公式内的所有单元格引用
private static string AdjustFormulaWithOffset(string formula, int rowOffset, int colOffset)
{
    // 匹配所有单元格引用，支持带$的绝对/混合引用
    return Regex.Replace(formula, @"(\$?[A-Z]+)(\$?\d+)", match =>
    {
        string colPart = match.Groups[1].Value;
        string rowPart = match.Groups[2].Value;

        // 列是绝对引用（带$）则不偏移，否则更新列值
        int newCol = ParseCellRef(colPart.Replace("$", "") + "1").col;
        if (!colPart.StartsWith("$")) newCol += colOffset;
        string newColStr = colPart.StartsWith("$") ? "$" + ColIndexToLetter(newCol) : ColIndexToLetter(newCol);

        // 行是绝对引用（带$）则不偏移，否则更新行值
        int newRow = int.Parse(rowPart.Replace("$", ""));
        if (!rowPart.StartsWith("$")) newRow += rowOffset;
        string newRowStr = rowPart.StartsWith("$") ? "$" + newRow : newRow.ToString();

        return newColStr + newRowStr;
    });
}

// 将范围字符串拆分为所有单元格地址列表（例：B3:B5 -> ["B3","B4","B5"]）
private static List<string> ParseRangeToCellList(string range)
{
    List<string> result = new List<string>();
    string[] rangeParts = range.Split(':');
    (int startRow, int startCol) = ParseCellRef(rangeParts[0]);
    (int endRow, int endCol) = ParseCellRef(rangeParts[1]);

    for (int r = startRow; r <= endRow; r++)
    {
        for (int c = startCol; c <= endCol; c++)
        {
            result.Add(ColIndexToLetter(c) + r);
        }
    }
    return result;
}

适配说明 #

• 代码默认支持A1引用样式、跨sheet引用、绝对/相对/混合引用场景
• 若公式包含结构化引用（表引用）、命名区域，正则逻辑不会误修改这类内容
• 如果需要填充数组公式，需要额外给目标单元格的CellFormula设置Array属性，绑定数组公式的生效范围
• 不要手动给公式单元格赋值CellValue，否则会覆盖Excel自动计算的结果

内容的提问来源于stack exchange，提问作者Shreyan Narayan Chowdhury