Bug 18311 - Writing Formula to Excel workbook - mutliple paste formula
Summary: Writing Formula to Excel workbook - mutliple paste formula
Status: RESOLVED FIXED
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 2.0-pre3
Hardware: PC Windows XP
: P3 major with 1 vote (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2003-03-25 10:42 UTC by Austin Gaughan
Modified: 2006-07-25 07:29 UTC (History)
0 users



Attachments
Original Excel workbook (13.50 KB, application/octet-stream)
2003-03-25 10:49 UTC, Austin Gaughan
Details
Output Excel written by POI (14.00 KB, application/octet-stream)
2003-03-25 10:49 UTC, Austin Gaughan
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Austin Gaughan 2003-03-25 10:42:41 UTC
I think this bug is related to 15223 but I am logging it again because I think 
I have a bit more detail.
This appears to be one of those obscure bugs that only appears under certain 
circumstances.

I am reading an existing Excel workbook (excel.xls), amending some values 
(omitted from the test case) and writing out the resultant workbook (poi.xls).
In Excel creating the original workbook, I have entered two rows of numbers.
I have also defined a summation row. I declare a formula for a cell, copy it 
then :-
1)  paste it individually to any number of subsequent cells across a row
        the workbook is written out correctly
2)  paste it simultaneously to up to 5 subsequent cells in the row
        the workbook is written out correctly
3)  paste it simultaneously to more than 5 subsequent cells in the row
        the original formula cell is OK but the others have #REF! error
        and when the workbook is opened in Excel you get the error
            "File error: data may have been lost."

Test Case
---------
	String filename = "Excel.xls" ;
	FileInputStream  fis = new FileInputStream( filename );
	POIFSFileSystem  fs  = new POIFSFileSystem( fis );
	HSSFWorkbook     wb  = new HSSFWorkbook( fs );

	FileOutputStream fos = new FileOutputStream( "Poi.xls" );
	wb.write( fos );
	fos.close();

Environment
-----------
POI   : jakarta-poi-1.11.0-dev-20030324.jar
Java  : 1.4.1 b21
Excel : 2002 SP-2
OS    : XP 5.1.2600
Comment 1 Austin Gaughan 2003-03-25 10:49:15 UTC
Created attachment 5490 [details]
Original Excel workbook
Comment 2 Austin Gaughan 2003-03-25 10:49:58 UTC
Created attachment 5492 [details]
Output Excel written by POI
Comment 3 Andy Oliver 2003-07-24 16:00:46 UTC
I think I know why...This looks like something Avik or Glen (which one?) worked
on awhile bakc...
Comment 4 Austin Gaughan 2003-07-25 09:21:00 UTC
Is there a fix for it?
Comment 5 Danny Mui 2003-07-25 15:20:45 UTC
this looks like a shared formula which we added to release_2_branch..can u
verify with 2.0pre2
Comment 6 Tushar Kapila 2005-09-19 13:05:27 UTC
A workaround I used is: split the formula across multiple cells; use something 
like =IF((BH4>BX4),BH4,0)*IF(BK4>BX4,BK4,0) instead of =IF((BH4<>""),IF
(BK4<>"",BH4*BK4,0),0)
Comment 7 Jason Height 2006-07-25 14:29:01 UTC
Shared Formula support now in SVN. Marking as fixed.