如何重新计算单元格的公式? [英] How to re-calculate a cell's formula?

问题描述

我的代码设置了很多单元格值.最后，在生成excel文件之前，需要评估每个单元格中的公式.对于大多数工作表来说，情况都很好.

My code is setting up a lot cell values. At the end, formulas in every cell needs to be evaluate before the excel file is generated. For most of the sheets, things are going well.

但是，有一个单元格引发异常.该单元格正在计算之后的一系列单元格的平均值，并计算其他工作表中的参考单元格.我猜想当第一个单元格试图计算平均值时，后面的单元格尚未评估.

However, there is a cell that is throwing an exception. That cell is calculating the average of a series of cells that come after it and reference cells in other sheets. I guess that when the first cell is trying to calculate the average, the cells after are not evaluated yet.

这是该单元格上的公式

=IFERROR(AVERAGEIF(D2:AU2,"<>-"),"-")

我怀疑这是因为int范围内的单元格引用了其他工作表中的单元格.例如， D2 具有以下公式 = Common！E2 .

I suspect that it's because cells int that range reference cells in other sheet. D2, for instance, has the following formula =Common!E2.

所以我决定要做的是:

1. 将调用包装为在 try-catch 块中执行公式.
2. 如果执行一个单元格会引发异常，我将保存该单元格的坐标.
3. 然后我执行引发异常的单元格

我注意到同一单元格不断抛出错误.为了能够退出循环，我计算了3次迭代.

I've noticed that the same cell keeps throwing error. Just to be able to exit the loop, I'm counting to 3 iterations.

生成excel生成时，该单元格的值为-.但是，当我选择单元格然后按 enter 时，它将执行并显示正确的值.

When the excel generate is generated, that cells has the value -. However, when I select the cell then press enter, it executes and displays the right value.

我推迟执行该单元格的原因是，在第一次迭代之后，已经评估了引用其他工作表的单元格.但是，它仍然不起作用，因为它不断抛出异常.

The reason I'm postponing the execution of that cell is that, after the first iteration, cells referencing other sheets are already evaluated. But, it still not working as it keeps throwing exception.

感谢您的帮助.

我正在使用npoi和c#.

I'm using npoi and c#.

推荐答案

您是否已检查

Have you checked MSDN: Run Excel Calculations Programmatically?

这篇关于如何重新计算单元格的公式?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！