You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@poi.apache.org by bu...@apache.org on 2009/11/21 05:28:20 UTC
DO NOT REPLY [Bug 48256] New: HSSF-created formulas won't work in
Microsoft Excel Viewer
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: dev@poi.apache.org
ReportedBy: asf-bugzilla@barillari.org
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: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
DO NOT REPLY [Bug 48256] HSSF-created formulas won't work in
Microsoft Excel Viewer
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=48256
--- Comment #1 from Joe <as...@barillari.org> 2009-11-20 20:29:24 UTC ---
Created an attachment (id=24578)
--> (https://issues.apache.org/bugzilla/attachment.cgi?id=24578)
Example of an .xls file that displays incorrectly in Excel Viewer
--
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: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
DO NOT REPLY [Bug 48256] HSSF-created formulas won't work in
Microsoft Excel Viewer
Posted by bu...@apache.org.
https://issues.apache.org/bugzilla/show_bug.cgi?id=48256
Yegor Kozlov <ye...@dinom.ru> changed:
What |Removed |Added
----------------------------------------------------------------------------
Status|NEW |RESOLVED
Resolution| |INVALID
--- Comment #2 from Yegor Kozlov <ye...@dinom.ru> 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
--
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: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org
Re: DO NOT REPLY [Bug 48256] New: HSSF-created formulas won't work
in Microsoft Excel Viewer
Posted by MSB <ma...@tiscali.co.uk>.
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 bugzilla@apache.org 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: dev@poi.apache.org
> ReportedBy: asf-bugzilla@barillari.org
>
>
> 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: dev-unsubscribe@poi.apache.org
> For additional commands, e-mail: dev-help@poi.apache.org
>
>
>
--
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: dev-unsubscribe@poi.apache.org
For additional commands, e-mail: dev-help@poi.apache.org