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.
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
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.