Bug 15223 - Formula column value reading error
Summary: Formula column value reading error
Status: RESOLVED FIXED
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: unspecified
Hardware: PC All
: P3 blocker with 4 votes (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2002-12-10 09:51 UTC by Kristian Larsson
Modified: 2005-04-22 05:58 UTC (History)
0 users



Attachments
This is the excel I tried to read and subsequently write to a new file, the B column caused the problem! (13.50 KB, application/octet-stream)
2002-12-10 09:56 UTC, Kristian Larsson
Details
excel sheet please refer the bug lines in the message text (73.50 KB, application/octet-stream)
2005-01-11 18:20 UTC, Balasubramaniam
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Kristian Larsson 2002-12-10 09:51:51 UTC
I'm using Excel XP on Windows 2000 with JDK 1.3.1 and POI 1.9.0 (I downloaded 
the dev-build to see if this would be resolved, I had the same problem with a 
build from this summer).

When writing a series of formula cells in drag-down style e.g
(Cell A2) =A1+1
(Cell A3) =A2+1
(Cell A4) =A3+1
and then reading the workbook in a java application and writing to a new output 
everything first generates fine, but...
If I close Excel, open it again with the original document, edit a cell 
somewhere else in the sheet, save it and try to run my java again - all the 
formula cells in the generated book are represented by REF#.

When using the drag-down (I don't know if that's the correct term for the mouse-
dragging extrapolation of formulas) the problem appears immediately without 
involving restarts or other cells.

Am I missing something?
Grateful for all help I can get,
Kristian Larsson
Comment 1 Kristian Larsson 2002-12-10 09:56:01 UTC
Created attachment 4102 [details]
This is the excel I tried to read and subsequently write to a new file, the B column caused the problem!
Comment 2 Kristian Larsson 2002-12-18 01:28:07 UTC
I moved my test file to an AIX server with JDK 1.2.2 (same POI) and ran the 
test. Everything worked perfectly. Luckily for me, that's the target 
environment for my project so this must be one of those rare occasions when a 
project which is not working in testing/developing works perfectly in 
production.

(The formulas still get screwed on the W2k machine.)
Comment 3 Danny Mui 2003-05-08 00:14:13 UTC
Committed fix for SharedFormulas and tested your excel file.  Works now on my
windows xp machine.  Thanks for the bug report!
Comment 4 Balasubramaniam 2005-01-11 17:43:21 UTC
Bug #15223 reopened....

I'm using Excel 2000 on Windows 2000 with j2sdk1.4.1_02 and POI 2.5.1 (I 
downloaded poi-src-2.5.1-final-20040804.zip.asc.)

when i try to read from the excel sheet cell formula as
=IF(H8="","Incomplete",IF(H8>0,"Potential Gap Identified","Meets CTQ"))
and actual value is displaying as Potential Gap Identified, it cannt read all 
the cell value like this. 

please refer attached excel sheet.

sheet1: 
error row number 6, column cell C
error row number 8, column cell C
error row number 9, column cell C
error row number 10, column cell C
error row number 11, column cell C
error row number 12, column cell C
error row number 13, column cell C
error row number 14, column cell C
error row number 15, column cell C
error row number 16, column cell C
error row number 17, column cell C
error row number 18, column cell C

Sheet 2:
error row number 40, column cell D



can you please help me.

Advance Great thanks

Balasubramaniam P
Mailto: bala.p@tcs.com
Comment 5 Balasubramaniam 2005-01-11 18:20:13 UTC
Created attachment 13973 [details]
excel sheet please refer the bug lines in the message text
Comment 6 Avik Sengupta 2005-04-22 13:58:37 UTC
Cant see your problem. the following code works

 HSSFSheet sh = wb.getSheetAt(0);
 HSSFRow r = sh.getRow(9);
 assertEquals("formula read ","IF(H11=\"\",\"Incomplete\",IF(H11>0,\"Potential
Gap Identified\",\"Meets CTQ\"))", r.getCell((short)2).getCellFormula());