| Summary: | Problem with Formula cells. | ||
|---|---|---|---|
| Product: | POI | Reporter: | Sandeep <sandeep30469> |
| Component: | HSSF | Assignee: | POI Developers List <dev> |
| Status: | RESOLVED FIXED | ||
| Severity: | critical | CC: | vkt3142 |
| Priority: | P1 | ||
| Version: | 3.0-dev | ||
| Target Milestone: | --- | ||
| Hardware: | PC | ||
| OS: | Windows XP | ||
| Attachments: |
Sample excel file to test
Sample excel file to test Output file - Note the #value for the for the formula cells |
||
Created attachment 20285 [details]
Sample excel file to test
Can't reproduce with latest version of POI and Excel 2000. I used your attachment and created a new one with your disposition. Retested OK in 3.1-beta1. This may have had something to do with shared formulas. 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";
try{
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);
wb.write(fileOut);
fileOut.close();
istream.close();
System.out.println("After copyFilesUsingPOI");
}catch(Exception e){
e.printStackTrace();
}
%>
+++++++++++++++++++++++++++++ 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.
Created attachment 22062 [details]
Sample excel file to test
Note:
-----
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)
Created attachment 22063 [details]
Output file - Note the #value for the for the formula cells
|
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"); wb.write(fileOut); fileOut.close(); istream.close(); 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.