How to re-calculate a cell's formula?

Question

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.

This is the formula on that cell

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

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

So what I decided to do is to:

1. Wrap the call to execute a formula in a try-catch block.
2. If executing a cell throws an exception, I save coordinates of that cell.
3. Then I execute the cells that threw exceptions

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

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.

Thanks for helping.

Update

I'm using npoi and c#.

Answer 1

In NPOI

if(wb is XSSFWorkbook) {
    XSSFFormulaEvaluator.EvaluateAllFormulaCells(wb);
} else {
    HSSFFormulaEvaluator.EvaluateAllFormulaCells(wb);
}