Modified values not shown after recalculation by NPOI Engine

Dec 23, 2009 at 3:41 PM

This is a very unique and inexplicable problem. I have 2 instance variables in my code pointing to 2 worksheets in a workbook viz. Investment and Monthly CF sheets. However when I break after assigning the values to these variables, it does not give me values in the worksheet cells as expected.

Investment has a chunk of data in it spanning 1 - 400 rows. Monthly CF too has a lot of data, but through the Calculate UI of the demo C# app, we only modify Rows# 13 and 17 containing one revenue item and one expense item, and that too only for the period from Jan - Dec 2012 out of the period spanning 2006-2015. When this data is modified, there is a corresponding change in a number of rows on the Investment sheet out of which about 8 rows are displayed back to the user in the Display UI. However inspite of any changes in the values on the Calculate UI, the Display UI continues to show the old, initial values at all times. We noticed this problem in almost all the tools (but one) which subsequently ended up failing the evaluation. Apparently it seems even NPOI has the same problem. I tried locating some kind of a property to force a recalculation of the whole workbook and of the worksheets individually, however there seems to be no such property which could help me here.

Could this potentially be because the NPOI API doesnt have a calculation engine to force a recalculate on the spreadsheet values when any cell(s) is modified on the spreadsheet externally? Ofcourse I am just speculating here.

Any suggestions/ideas are welcome.

Coordinator
Dec 24, 2009 at 3:00 PM

Have you tried set HSSFSheet.ForceFormulaRecalculation to true?

Dec 24, 2009 at 3:16 PM

No, I will try it right now.

 

Dec 28, 2009 at 2:31 PM

I tried both these lines of code. However the first line did not result in any change and the second threw a RTE.

HSSFSheet.ForceFormulaRecalculation = true;
HSSFFormulaEvaluator.EvaluateAllFormulaCells(workbook);

The exception goes as:

System.InvalidCastException: Unable to cast object of type 'NPOI.HSSF.Record.Formula.Eval.ErrorEval' to type 'NPOI.HSSF.Record.Formula.Eval.BoolEval'.

Any suggestions?

 

Coordinator
Dec 30, 2009 at 1:14 AM
Edited Dec 30, 2009 at 1:16 AM

Can you send me your code? It looks there are something wrong in your formula. my mailbox is tonyqus (at) gmail.com

Jun 2, 2011 at 6:55 PM

    Hello. I've been having a problem that could be related to this topic. In fact, in my case, HSSFFormulaEvaluator.EvaluateAllFormulaCells works fine as Excel actually recalculates all formulas. However if afterwards I open the file in Excel and I try to modify the values that are related to any formula those formulas don't recalculate anymore.

    Am I forgetting anything to make this work?

    Thanks!

Jun 2, 2011 at 7:02 PM

I am not sure reely as I haven't looked at NPOI again after December 2009. However question for you here. Is the excel file closed at the time of this NPOI API call? After the API call, do you proceed to open it? And if you do, do you see the revised values as expected (due to the forced re-evaluation of all formula cells)?

Apologies to tony for not responding to your email. We decided not to proceed with evaluation of NPOI and ended up using another server side tool instead.

Coordinator
Jun 3, 2011 at 1:38 AM

It's calculated in real-time. After NPOI opens the excel file, all the data are in memory. There may be some issue of formula cache. I'm not sure.