Bug 48256 - HSSF-created formulas won't work in Microsoft Excel Viewer
Summary: HSSF-created formulas won't work in Microsoft Excel Viewer
Status: RESOLVED INVALID
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.5-FINAL
Hardware: PC Windows Vista
: P2 normal (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2009-11-20 20:28 UTC by Joe
Modified: 2011-06-25 12:35 UTC (History)
0 users



Attachments
Screenshot of Excel Viewer with the incorrect formula results (13.09 KB, image/png)
2009-11-20 20:28 UTC, Joe
Details
Example of an .xls file that displays incorrectly in Excel Viewer (6.00 KB, application/vnd.ms-excel)
2009-11-20 20:29 UTC, Joe
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Joe 2009-11-20 20:28:16 UTC
Created attachment 24577 [details]
Screenshot of Excel Viewer with the incorrect formula results

I have noticed what I think might be a bug in HSSF: Microsoft Excel Viewer won't do the calculations specified in the formulas in the POI-created document: all of them show zeroes. 

Interestingly, if I open the document in OpenOffice and re-save it, the formulas *do* display properly. 

Steps to reproduce:

0. Download and install Microsoft Excel Viewer 2003.
http://www.microsoft.com/downloads/details.aspx?FamilyID=c8378bf4-996c-4569-b547-75edbd03aaf0&displaylang=EN

1. Download and compile TimesheetDemo
http://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/ss/examples/TimesheetDemo.java

2. Run TimesheetDemo -xls

3. Launch Microsoft Excel Viewer and open the generated timesheet.xls. Note that all of the sums are "0.00", even though they should be non-zero. See the attached screenshot, xlview.png.

4. To fix the problem, open timesheet.xls in OpenOffice. Note that the calculations are correct -- the column sums are no longer all zero. Make a trivial change, save the document, and close it. Reopen it in Excel Viewer, and note that the results are correct.

I haven't confirmed this, but I have a hunch that there is some sort of caching of formula results that Excel Viewer relies upon, that OpenOffice does, but that HSSF doesn't do. (The Python xls generation library, xlwt, has the exact same issue.) This may be beyond the scope of POI, but I thought you would want to know about it.
Comment 1 Joe 2009-11-20 20:29:24 UTC
Created attachment 24578 [details]
Example of an .xls file that displays incorrectly in Excel Viewer
Comment 2 Yegor Kozlov 2011-06-25 12:35:57 UTC
Excel Viewer displays cached formula results, i.e. the results previously calculated by Excel or OpenOffice. 

POI doesn't pre-calculate formulas for you, you need to manually pass your workbook to the formula evaluator.

Add the following line in the end of your code and you will be good:
   workbook.getCreationHelper().createFormulaEvaluator().evaluateAll();

it should work for both .xls and .xlsx formats.

More about evaluation of formulas:
http://poi.apache.org/spreadsheet/eval.html

Yegor