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

问题描述

我在微软的Office OpenXML的SDK 2.0更新的一个Excel US preadsheet一些细胞。改变值使得含有式依赖于改变的细胞无效所有细胞。然而，由于缓存值Excel不重新计算公式推，即使用户点击计算现在

什么是通过SDK的整个工作簿中所有依赖电池失效的最好方法是什么？到目前为止，我已经找到了以下code段的<一个href="http://cdonner.com/introduction-to-microsofts-open-xml-format-sdk-20-with-a-focus-on-excel-documents.htm">http://cdonner.com/introduction-to-microsofts-open-xml-format-sdk-20-with-a-focus-on-excel-documents.htm:

 公共静态无效ClearAllValues​​InSheet
      （S preadsheetDocument US preadSheet，串sheetName）
{
    WorksheetPart worksheetPart =
        GetWorksheetPartByName（S preadSheet，sheetName）;

    的foreach（排排
       worksheetPart.Worksheet。
          GetFirstChild（）元（））
    {
        的foreach（在row.Elements细胞的细胞（））
        {
            如果（cell.CellFormula = NULL和放大器;！＆安培;
                  cell.CellValue！= NULL）
            {
                cell.CellValue.Remove（）;
            }
        }

    }

    worksheetPart.Worksheet.Save（）;
}
 

另外一个事实，即这个片段不编译对我来说，它有两个限制：

• 这只是一张纸，虽然其他工作表可能包含依赖公式无效，
• 在它没有考虑任何依赖关系。

我要寻找一种方式，是有效的（特别是中，只有无效依赖于某个单元格的值细胞），并采取一切床单进去。

更新：

在此期间我已成功地使code编译和放大器;运行，并取消对工作簿的所有工作表的缓存值。 （见的答案。）不过我很感兴趣，更好的/替代的解决方案，特别是如何只删除，实际上取决于更新的细胞对细胞的缓存值。

解决方案

 取值preadSheet.WorkbookPart.Workbook.CalculationProperties.ForceFullCalculation = TRUE;
US preadSheet.WorkbookPart.Workbook.CalculationProperties.FullCalculationOnLoad = TRUE;
 

对我的作品！

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".

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:

• It only invalidates a single sheet, although other sheets might contain dependent formula
• It does not take into account any dependencies.

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.

Update:

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;

Works for me!

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