Bug 42541 - Problem with Formula cells.
Summary: Problem with Formula cells.
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 3.0-dev
Hardware: PC Windows XP
: P1 critical with 3 votes (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2007-05-29 09:28 UTC by Sandeep
Modified: 2008-06-03 10:04 UTC (History)
1 user (show)

Sample excel file to test (14.00 KB, application/octet-stream)
2007-05-29 09:29 UTC, Sandeep
Sample excel file to test (22.50 KB, application/vnd.ms-excel)
2008-06-03 09:18 UTC, vijay
Output file - Note the #value for the for the formula cells (23.50 KB, application/vnd.ms-excel)
2008-06-03 09:20 UTC, vijay

Note You need to log in before you can comment on or make changes to this bug.
Description Sandeep 2007-05-29 09:28:22 UTC
POI Version . poi-3.0-rc4-20070503.jar
Create a simple excel file . input a formula in C1 as =A1+B1
Copy the same formula into the corresponding cells for the C column by dragging 
the C1 Cell. Excel will generate the formulas automatically for the cells.
Save the workbook and try opening it. 
InputStream istream = new FileInputStream(filename);
POIFSFileSystem fs = new POIFSFileSystem(new BufferedInputStream(istream));
HSSFWorkbook wb= new HSSFWorkbook(fs,true);
FileOutputStream fileOut = new FileOutputStream("c:\\test.xls");

All the cells where the formula is copied will show #value instead of the value.
This happens only in the current release(poi-3.0-rc4-20070503).  
Could somebody please fix this or suggest some solution. This happens only for 
those formula cells that have been copied.
Comment 1 Sandeep 2007-05-29 09:29:46 UTC
Created attachment 20285 [details]
Sample excel file to test
Comment 2 Olivier Leprince 2008-01-16 16:13:42 UTC
Can't reproduce with latest version of POI and Excel 2000.
I used your attachment and created a new one with your disposition.
Comment 3 Josh Micich 2008-05-08 23:22:13 UTC
Retested OK in 3.1-beta1.

This may have had something to do with shared formulas.
Comment 4 vijay 2008-06-03 09:17:24 UTC
POI Version: poi-3.1-beta1-20080428.jar
Sample Excel File: template_183.xls (Attached)
Variable names within the excel file (template_183.xls):
End_1st: =OFFSET(testsheet!$A$2,0,0,COUNTA(testsheet!$A:$A)-1,1)
End_Acct: =OFFSET(End_1st,,6)
End_Bal: =OFFSET(End_1st,,19)

JSP To Test: This jsp reads the excel file using POI and generates a copy of the excel file.

+++++++++++++++++++++++++++++ JSP FILE +++++++++++++++++++++++++++++++++++++++++
<%@ page import= "java.util.*,java.io.*"%>
	String fromPath = "C:\\template_183.xls";
	String toPath2 = "C:\\template_183_POICOPY.xls";
		System.out.println("Before copyFilesUsingPOI");
		InputStream istream = new FileInputStream(fromPath);
		org.apache.poi.poifs.filesystem.POIFSFileSystem fs = new org.apache.poi.poifs.filesystem.POIFSFileSystem(new BufferedInputStream(istream));
		org.apache.poi.hssf.usermodel.HSSFWorkbook wb= new org.apache.poi.hssf.usermodel.HSSFWorkbook(fs,true);
		FileOutputStream fileOut = new FileOutputStream(toPath2);
		System.out.println("After copyFilesUsingPOI");
		}catch(Exception e){
+++++++++++++++++++++++++++++ ENDJSP FILE ++++++++++++++++++++++++++++++++++++++
Output: (template_183_POICOPY.xls)All the cells where the formula is copied will show #value instead of the value in the original excel file. I am attaching the output file as well.

Could someone please look into this issue. The #value error in this scenario is not resolved in the latest POI release.

Thank you.
Comment 5 vijay 2008-06-03 09:18:32 UTC
Created attachment 22062 [details]
Sample excel file to test

Variable names within the excel file (template_183.xls)

End_1st: =OFFSET(testsheet!$A$2,0,0,COUNTA(testsheet!$A:$A)-1,1)
End_Acct: =OFFSET(End_1st,,6)
End_Bal: =OFFSET(End_1st,,19)
Comment 6 vijay 2008-06-03 09:20:08 UTC
Created attachment 22063 [details]
Output file - Note the #value for the for the formula cells
Comment 7 Josh Micich 2008-06-03 10:04:02 UTC
Thanks for the new attachment.  The problem is easy to reproduce. 

It is a new bug though, so I have opened up bug 45123 to track the work.