更新单元格并保存excel后，基于此单元格的公式不会重新计算

Hi,

嗨，

Im using XML SDK to update some cells on an excel file.

我使用XML SDK更新excel文件上的一些单元格。

This is the code that im using to update a cell for a given text (and its working fine).

这是我用于更新给定文本的单元格的代码（并且其工作正常）。

WorksheetPart worksheetPart = GetWorksheetPartByName(spreadSheet, sheetname);

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

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

    if (worksheetPart != null)
    {
       Cell cell = GetCell(worksheetPart.Worksheet, columnName, rowIndex);
       cell.CellValue = new CellValue(text);
       cell.DataType = new EnumValue<CellValues>(CellValues.Number);
       // Save the worksheet.
       worksheetPart.Worksheet.Save();
    }

For other hand now i want to open again the excel file and get the updated values from other cells whose formulas are based on the previous cell but even with the following lines im not able to do it:

另一方面，我想再次打开excel文件并从其他单元格中获取更新的值，这些单元格的公式基于前一个单元格，但即使使用以下行也无法执行此操作：

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

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

Do you know why im not getting the updated values?

你知道为什么我没有得到更新的值吗？

Thanks

谢谢

Regards.

问候。

Jose

何塞

2 个解决方案

#1

Execute the below function for every cell that contains a formula.

对包含公式的每个单元格执行以下函数。

public void ReCalcuateFormula(Cell cell)
{
    if (cell.CellFormula != null)
        cell.CellFormula.CalculateCell = new BooleanValue(true);
}

#2

See Vincent's answer here: OpenXML - refresh Excel sheet after cell update

请参阅Vincent的答案：OpenXML - 在单元格更新后刷新Excel表格

Open XML SDK doesn't refresh the calculation results of formulas. Only Excel does that (or other spreadsheet software). So even if you set ForceFullCalculation and FullCalculationOnLoad to true, you only tell Excel to force a full calculation and do it when loading the spreadsheet file.

Open XML SDK不会刷新公式的计算结果。只有Excel（或其他电子表格软件）。因此，即使将ForceFullCalculation和FullCalculationOnLoad设置为true，也只能告诉Excel强制进行完整计算，并在加载电子表格文件时执行此操作。

This is why you always get the "old" value. Because there's no "new" value to speak of.

这就是为什么你总是得到“旧的”价值。因为没有“新”价值可言。

To test this, you can set the CellValue (of the Cell class) to say "3.14159" with the CellFormula intact. Excel will calculate and display the correct value of your formula, even though you specifically set the cell value as "3.14159". [Unless of course, your formula evaluates to PI...]

要测试这一点，您可以将CellValue（Cell类）设置为“3.14159”，CellFormula保持不变。即使您专门将单元格值设置为“3.14159”，Excel也会计算并显示公式的正确值。 [除非当然，你的公式评估为PI ...]

To solve this, you either have a calculation engine to read in the formula and calculate the result for you. Or save the spreadsheet and run Excel (in Interop mode) to calculate all the formulas, but that kind of defeat the purpose of using Open XML SDK in the first place.

要解决此问题，您可以使用计算引擎读取公式并为您计算结果。或者保存电子表格并运行Excel（在Interop模式下）来计算所有公式，但这样做首先打败了使用Open XML SDK的目的。

#1