As far as I can remember, Excel does cache away the result of calculating
formulae so your hypothesis could well be correct. As a test, try to
eveluate the formulae in your workbook before you save it away. The code is
quite simple, I have just copied this from the Formula Evaluation page at
http://poi.apache.org/spreadsheet/eval.html
Re-calculating all formulas in a Workbook
FileInputStream fis = new FileInputStream("/somepath/test.xls");
Workbook wb = new HSSFWorkbook(fis); //or new
XSSFWorkbook("/somepath/test.xls")
FormulaEvaluator evaluator =
wb.getCreationHelper().createFormulaEvaluator();
for(int sheetNum = 0; sheetNum < wb.getNumberOfSheets(); sheetNum++) {
Sheet sheet = wb.getSheetAt(sheetNum);
for(Row r : sheet) {
for(Cell c : r) {
if(c.getCellType() == Cell.CELL_TYPE_FORMULA) {
evaluator.evaluateFormulaCell(c);
}
}
}
}
Obviously, you will not need to open the workbook as you already have a
reference to the newly created workbook in hand and you will need - inless
you are using the classes in the SS stream - to change the way you get the
FormulaEvaluator object. If you are using the HSSF stream then there is an
HSSFFormulaEvaluator with a constructor that accepts a reference to the
workbook. I cannot promise that this will work but it may be worth trying.
Yours
Mark B
Bugzilla from [email protected] wrote:
>
> https://issues.apache.org/bugzilla/show_bug.cgi?id=48256
>
> Summary: HSSF-created formulas won't work in Microsoft Excel
> Viewer
> Product: POI
> Version: 3.5-FINAL
> Platform: PC
> OS/Version: Windows Vista
> Status: NEW
> Severity: normal
> Priority: P2
> Component: HSSF
> AssignedTo: [email protected]
> ReportedBy: [email protected]
>
>
> Created an attachment (id=24577)
> --> (https://issues.apache.org/bugzilla/attachment.cgi?id=24577)
> 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.
>
> --
> Configure bugmail:
> https://issues.apache.org/bugzilla/userprefs.cgi?tab=email
> ------- You are receiving this mail because: -------
> You are the assignee for the bug.
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [email protected]
> For additional commands, e-mail: [email protected]
>
>
>
--
View this message in context:
http://old.nabble.com/DO-NOT-REPLY--Bug-48256--New%3A-HSSF-created-formulas-won%27t-work-in-Microsoft-Excel-Viewer-tp26453698p26454135.html
Sent from the POI - Dev mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
