When modifying an Excel 2000 Spreadsheet to add extra rows and columns the code completes as expected but causes either an 'Unable to open file' message from Excel or for the spreadsheet to briefly flash on screen and then disappear. The code that causes this problem is: String[][] accountCodes = dataHandler.getAcctCodeList(data.getCostCenter()); HSSFCell cell = null; HSSFCellStyle cellStyle = null; HSSFRow row = sheet.getRow(rowPos); String[] accountCode = null; int a = 0; row = sheet.createRow((short)(rowPos)); cell = row.createCell((short)cellPos); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellStyle(headerStyle); cell.setCellValue("Account Code (and description)"); a++; for(;(accountCodes != null) && (a<=accountCodes.length-1);a++){ accountCode = accountCodes[a]; //Fails when adding this row row = sheet.createRow((short)(rowPos+a)); cell = row.createCell((short)cellPos); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellStyle(bodyStyle); cell.setCellValue(accountCode[0] + " - " + accountCode[1]); } return a; This is not a problem in 1.5.1
Created attachment 4168 [details] The file being modified that fails. The code is attempting to modify the first sheet of this workbook.
Give another code sample which is just a simple "main" class and is the least amount of code required to replicate the problem. I'm pretty suspicious of this bug.
Created attachment 4173 [details] Here is the barebones version of the failing code. With further testing i've found this code does work with POI 1.6 Dev but fails with all subsequent versions.
This is failing for me as well using the 1.8.0 distribution. I'm surprised, because my test case is *very* basic (using Excell 2000) and taken right from the docs. HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); HSSFRow row = sheet.getRow(5); HSSFCell cell = row.getCell((short)3); if (cell == null) cell = row.createCell((short)3); // Write test cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue("a test");
Created attachment 4429 [details] original xls file
Created attachment 4430 [details] file corrupted by poi
Here is the code that corrupts the xls file (1/15/03 16:53 attachment to this bug report) when using the 1.9.0 nightly build from 20030115: public void poiXlsTest() { try { POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream ("/usr/local/data/dashView.xls")); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); HSSFRow row = sheet.getRow(5); HSSFCell cell = row.getCell((short)3); if (cell == null) cell = row.createCell((short)3); // Write test cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue("a test"); // change existing numeric cell value log.debug("change existing numeric cell"); HSSFRow oRow = sheet.getRow(14); HSSFCell oCell = oRow.getCell((short)4); oCell.setCellValue(75); oCell = oRow.getCell((short)5); oCell.setCellValue("0.3"); // Write the output to a file FileOutputStream fileOut = new FileOutputStream("/usr/local/data/dashView- 1.xls"); wb.write(fileOut); fileOut.close(); } catch (java.io.FileNotFoundException ex) { log.error(ex.getMessage()); } catch (java.io.IOException ex) { log.error(ex.getMessage()); } }
Resubmit the ExcelTest in a simpler form. As it stands I'd have to spend too much time tracing through your sample. It appears this may indeed be a bug, but I can't rule out a bug in the sample. When we say "the simplest possible example" we envision a monolitic class with main() which does things clearly in a linear fashion... There is a little too much for me to grok in a limited amount of time.
new sample submitted by haddad should be adequate. I'll take a look.
Created attachment 4435 [details] sample code with main() demonstrating the excel error when loading the file. Excel 2000 9.0.4402 sr1 on w2k used.
I have tried: jakarta-poi-1.8.0-dev-20020919.jar jakarta-poi-1.10.0-dev-20030222.jar poi-2.0-pre1-20030517.jar In each case using POI to modify (per example provided by Chris Haddad) a blank (open Excel press save) spreadsheet created using each of: Excel 2000 (9.0.2720) Excel 2002 (10.2614.2625) resulted in a blank spreadsheet Using other input spreadsheets of varying complexity would result in a corrupt output spreadsheet But generally the result spreadsheet appeared as if it had not been modified This problem does not occur using jakarta-poi-1.5.1-final-20020615.jar
Created attachment 7367 [details] A small class that can simulate this error
I have a similar problem and have managed to simulate it with a small class. I think it has to do with string references?(See class)
Have you tried this with 2.0-pre2? 1.5.x will not get new fixes this close to a release.
This fault only happens with 2.0-pre2. It does not happen with jakarta-poi-1.5.1-final
I have noticed that (In my case) as soon as the SST table becomes very big this happens. I can put thousands of strings into a spreadsheet as long as there are not too many unique ones. As soon as there are plenty unique strings the sheet can't open. Using the BIFF Viewer, I see that CONTINUE RECORD's are written for the spreadsheet that can't open and none are written for a spreadsheet that can open. From looking at the source (I am not sure though) it seems that these CONTINUE RECORDs are a major change between POI 1.5.1 and POI2. Am I on the wrong track ???
*** Bug 23445 has been marked as a duplicate of this bug. ***
*** Bug 22568 has been marked as a duplicate of this bug. ***
OK, This bug is now fixed thanks to Jason's SST/Continue record fixes. I would appreciate if some of the original reporters would try out a latest nightly or CVS build, and report if they can confirm the fix. We should do a release once we get some more confirmation.
*** Bug 23628 has been marked as a duplicate of this bug. ***
*** Bug 21646 has been marked as a duplicate of this bug. ***
I tried a build from CVS and can confirm that it fixes the problem I was having. Fantastic! A speedy release would be *very* much appreciated!
Cool. Big round of applause for Jason! Closing this bug. Please reopen with testcase if you still have a problem. Before you do, please check test15375, test15375_2, and test22568 in TestBugs.java, which are the proof that this bug is fixed.
Big round of applause for Jeff who actually found the problem with the continue records! Nice going boet.
*** Bug 21305 has been marked as a duplicate of this bug. ***
*** Bug 24355 has been marked as a duplicate of this bug. ***