OpenXML SDK：使Excel重新计算公式

Question

我通过 Microsoft Office OpenXML SDK 2.0 更新了 Excel 电子表格中的一些单元格。更改值会导致依赖于更改单元格的公式的所有单元格无效。但是，由于缓存的值，即使用户点击“立即计算”，Excel 也不会重新计算公式。

通过 SDK 废除整个工作簿的所有依赖单元格的最佳方法是什么？到目前为止，我在 http://cdonner.com/introduction-to-microsofts-open-xml-format-sdk-20-with-a-focus-on-excel-documents.htm 上找到了以下代码片段：

public static void ClearAllValuesInSheet
      (SpreadsheetDocument spreadSheet, string sheetName)
{
    WorksheetPart worksheetPart =
        GetWorksheetPartByName(spreadSheet, sheetName);

    foreach (Row row in
       worksheetPart.Worksheet.
          GetFirstChild().Elements())
    {
        foreach (Cell cell in row.Elements())
        {
            if (cell.CellFormula != null &&
                  cell.CellValue != null)
            {
                cell.CellValue.Remove();
            }
        }

    }

    worksheetPart.Worksheet.Save();
}

除了这段代码片段在我的环境中无法编译之外，它还有两个限制：

• 它只能使单个工作表失效，尽管其他工作表可能包含依赖于该单元格的公式
• 它没有考虑到任何依赖关系。

我正在寻找一种有效的方式（特别是仅使依赖于某个单元格值的单元格失效），并考虑所有工作表。

更新:

与此同时，我已经成功使代码编译并运行，并删除了工作簿的所有工作表上的缓存值。（请参见答案。）但我仍然对更好/替代的解决方案感兴趣，特别是如何仅删除实际依赖于更新单元格的缓存值。

- chiccodoro

---

删除缓存值后，如何读取单元格的值？是否需要关闭对象？在我的情况下，我获取了该值并返回了空。 - mggSoft

Answer 1

spreadSheet.WorkbookPart.Workbook.CalculationProperties.ForceFullCalculation = true;
spreadSheet.WorkbookPart.Workbook.CalculationProperties.FullCalculationOnLoad = true;

对我有效！

- Nafi

---

很酷，看起来简单多了！为了让它更易读，你能否编辑你的答案并删除源代码前面的空格？（只保留4个空格以便将文本格式化为源代码） - chiccodoro

这种形式和之前的消息对我不起作用。我该尝试什么？ - mggSoft

一个小提示，当你更新了单元格的值之后，需要将上述两行代码添加到你的程序中。 - Vipul bhojwani

一点信息。在上述操作后，document.save() 不会为您计算值。您必须在 Excel 应用程序或 Excel 互操作中打开并保存，以便更新字段。 - Jins Peter

这个救了我两个月的头疼...我写了自己的帮助类覆盖了OpenSDK v2.5，它表现得非常好，甚至在某些情况下比EPPlus更快，但我遇到了这个问题。非常感谢。 - OneGhana

Answer 2

由于它部分解决了我的问题，并且目前似乎没有更好的解决方案，因此将该代码块从问题中移出到答案中...这是新代码的样子：

foreach (WorksheetPart worksheetPart in spreadSheet.WorkbookPart.WorksheetParts)
{
    foreach (Row row in
            worksheetPart.Worksheet.GetFirstChild<SheetData>().Elements())
    {
        foreach (Cell cell in row.Elements())
        {
            if (cell.CellFormula != null && cell.CellValue != null)
                cell.CellValue.Remove();
        }
    }
}

- chiccodoro

Answer 3

我使用这个

    static void FlushCachedValues(SpreadsheetDocument doc)
    {
        doc.WorkbookPart.WorksheetParts
            .SelectMany(part => part.Worksheet.Elements<SheetData>())
            .SelectMany(data => data.Elements<Row>())
            .SelectMany(row => row.Elements<Cell>())
            .Where(cell => cell.CellFormula != null)
            .Where(cell => cell.CellValue != null)
            .ToList()
            .ForEach(cell => cell.CellValue.Remove())
            ;
    }

这将清除缓存的值。

问候

- Elmer

---

我已经尝试过这个，清除缓存值后，我得到了值但没有返回任何东西。 - mggSoft

同样的问题，这种方法对我也不起作用。我在 Stack Overflow 上发布了类似的问题。 - dotnet-practitioner

Answer 4

你需要在最后保存工作表，这对我非常有效。

foreach (WorksheetPart worksheetPart in spreadSheet.WorkbookPart.WorksheetParts) {
    foreach (Row row in
            worksheetPart.Worksheet.GetFirstChild<SheetData>().Elements()) {
        foreach (Cell cell in row.Elements()) {
            if (cell.CellFormula != null && cell.CellValue != null)
                cell.CellValue.Remove();
        }
    }
    worksheetPart.Worksheet.Save();
}

- robin

Answer 5

或者，您可以更改公式以使用INDIRECT运算符。如果您正在使用SAX +模板文件方法，则特别有用。由于此解决方案不需要更改您的代码，只需模板Excel文件。请参考我的解决方案-Set xlsx to recalculate formulae on open

- Andrii Kolesnikov

Answer 6

想要注意到我遇到的另一个问题，似乎是重新计算的问题。我盲目地跟随了一些代码来填充单元格，并显示了共享字符串。经过很长一段时间，我发现我需要使用CellValues.Number值作为DataType。一旦我这样做了，单元格在打开时重新计算。

- Thomas