OpenXML SDK：使Excel重新计算公式 [英] OpenXML SDK: Make Excel recalculate formula

问题描述

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

I update some cells of an Excel spreadsheet through the Microsoft Office OpenXML SDK 2.0. Changing the values makes all cells containing formula that depend on the changed cells invalid. However, due to the cached values Excel does not recalculate the formular, even if the user clicks on "Calculate now".

什么是使所有依赖单元无效的最佳方法的整个工作簿通过SDK？到目前为止，我已经在 http://cdonner.com/introduction-to-microsofts-open-xml-format-sdk-20-with-a-focus-on-excel-documents.htm ：

What is the best way to invalidate all dependent cells of the whole workbook through the SDK? So far, I've found the following code snippet at 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();
}

除了这个代码段不为我编译的事实，它有两个限制：

Besides the fact that this snippet does not compile for me, it has two limitations:

• 尽管其他工作表可能包含依赖公式，但它只会使单张无效。


• 不考虑任何依赖关系。

我正在寻找一种有效的方法（特别是仅使依赖于

I am looking for a way that is efficient (in particular, only invalidates cells that depend on a certain cell's value), and takes all sheets into account.

更新：

在此期间，我已经设法使代码编译和运行并删除工作簿的所有工作表上的缓存值。 （见答案。）仍然我对更好的/替代的解决方案感兴趣，特别是如何只删除实际上依赖于更新的单元格的单元格的缓存值。

In the meantime I have managed to make the code compile & run, and to remove the cached values on all sheets of the workbook. (See answers.) Still I am interested in better/alternative solutions, in particular how to only delete cached values of the cells that actually depend on the updated cell.

推荐答案

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

适用于我！

这篇关于OpenXML SDK：使Excel重新计算公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！