I have a template XLS file that I load with Apache POI and write loads of data in it, then save it as another file. I have formulas in my XLS file like this:
these evaluate correctly if I press Enter on the cell in Excel. However, simply calling
does not seem to evaluate them, neither does pressing on the "Calculate now" button in Excel - so I guess this is a special formula or function.
The other, more conventional
SUMIFs work fine, however these do not allow multiple conditions to be specified.
POI does not support array formulas.
Is there any way to make these work. I'm using POI version 3.7.
One can press CTRL-ALT-F9 to manually re-evaluate all formulas forcefully in Excel.
And here is the trick to make it work automatically on workbook open.
Add the following to your formula:
so for example, my
SUMPRODUCT above becomes
And this works! Excel now recalculates my special formula cells on open.
The reason for this is that
NOW() is a volatile function. Here is where I learned about this: http://msdn.microsoft.com/en-us/library/bb687891.aspx
Application.CalculateFull also works, but only in Excel 2007 and later (and of course, one must enable macros to run). Unfortunately, in my case even though I use Excel 2007 my workbook will be opened by Excel 2003 users as well, so this was not an option.
The content is written by members of the stackoverflow.com community.
It is licensed under cc-wiki