Hi all, I saw that Bug 48294 is corrected in revision 1142219 ( https://issues.apache.org/bugzilla/show_bug.cgi?id=48294) What about 50083? is there any solution yet?
How can I get the latest jar which corrected the first bug? Thanks, Hamza On Wed, Jun 29, 2011 at 7:43 PM, Jon Svede <[email protected]> wrote: > There are some bugs related to the setSheetOrder() method (48294 and 50083) > and > I think they are related to this. > > > Basically all the code you've mentioned works but in my example it blows up > when > I try to re-order the sheets. Here is my test case: > > public static void main( String[] args ) { > > > try { > File wbFile = new File( dirName + fileName ) ; > FileInputStream fis = new FileInputStream( wbFile ) ; > Workbook wb = new HSSFWorkbook( fis ) ; > > System.out.println( "file: " + fileName + > " contains " + wb.getNumberOfSheets() + > " sheets now." ) ; > > Sheet newSheet1 = wb.cloneSheet( 3 ) ; > Sheet newSheet2 = wb.cloneSheet( 4 ) ; > > System.out.println( "file: " + fileName + > " contains " + wb.getNumberOfSheets() + > " sheets now." ) ; > > if (args != null && args.length > 0 ) { > wb.setSheetOrder(newSheet1.getSheetName(), 0); > wb.setSheetOrder(newSheet2.getSheetName(), 1); > } > wb.setSheetName( wb.getNumberOfSheets() - 2, "newSheet1" ) ; > wb.setSheetName(wb.getNumberOfSheets() - 1, "newSheet2" ) ; > > > > removeExtraSheets( wb ) ; > > System.out.println( "file: " + fileName + > " contains " + wb.getNumberOfSheets() + > " sheets now." ) ; > > File outputFile = new File( dirName + outFileName ) ; > > if( outputFile.exists() ) { outputFile.delete() ; } > > FileOutputStream fos = new FileOutputStream( outputFile ) ; > wb.write( fos ) ; > > System.out.println( "saved file " + outFileName + "." ) ; > > } catch (Exception e) { > e.printStackTrace(); > } > } > > private static void removeExtraSheets(Workbook destwb) { > int i = 0; > while (i < 3) { > destwb.removeSheetAt(destwb.getNumberOfSheets() - 1); > i++; > } > } > > > If I run the program with no args, it works as expected: > > file: test_wb.xls contains 9 sheets now. > file: test_wb.xls contains 11 sheets now. > file: test_wb.xls contains 8 sheets now. > saved file test_wb_out.xls. > > However, if I pass some argument, I get this: > > file: test_wb.xls contains 9 sheets now. > file: test_wb.xls contains 11 sheets now. > file: test_wb.xls contains 8 sheets now. > java.lang.IllegalArgumentException: calculated end index (55507) is out of > allowable range (55497..55506) > at > > org.apache.poi.util.LittleEndianByteArrayOutputStream.<init>(LittleEndianByteArrayOutputStream.java:41) > > at > org.apache.poi.hssf.record.StandardRecord.serialize(StandardRecord.java:38) > > So your issue is actually caused at write time, if your test case doesn't > write > the out workbook it works fine (though it's illogical) I thought you were > saying the exception happened when you called the remove method itself. > > > HTH, > > Jon > > > > > > > > > ________________________________ > From: Hamza Abdelkebir <[email protected]> > To: Jon Svede <[email protected]>; POI Users List <[email protected]> > Sent: Wed, June 29, 2011 8:07:26 AM > Subject: Re: Removing sheets from workbook (poi 3.8) > > > I don't have a test case per say but here's how I do to generate my file : > * Read a template XLS file which contains the following sheets : > 1. Cover > 2. General Information > 3. Table of contents > 4. List of Products > 5. > > 6. Cabinet Layout > 7. Chassis (F) > 8. Chassis (R) > 9. Hardware View > 10. 1 > 11. 2 > 12. 3 > 13. 4 > 14. 5 > 15. 6 > 16. 7 > * Write data to sheets : cover, general information, list of > products, cabinet > layout, chassis > * Each time that I need to write a new page (according to a list of > products, > items, etc) I duplicate a template according to the type of sheet to add > (eg: > sheet "1" is for products, "2" for items, ..etc) > * Example : i need to write a new sheet called CILIMapping.... > * I duplicate the corresponding template (in this case 3) to a > certain > position > * rename the duplicate sheet with "CILIMapping +some reference > * Generate TOC > * Once all sheets are written I need to remove the template sheets > from 1 to 7 > * save the workbook to a "somename.xls" fileAll work is done within > the same > workbook object. > I hope this helps > > Thanks, > Hamza > > > On Wed, Jun 29, 2011 at 3:45 PM, Jon Svede <[email protected]> wrote: > > You have a file you use as a template with 7 sheets. You clone this > template to > >a new file, duplicate the 7 sheets and remove the last 4 from the > workbook, is > >that your scenario? > > > >If the loop always fails at the same place I'd wonder why it is the same > all > the > >time? That sounds more like a loop issue than a POI issue. > > > >Do you have a test case? If not can you distill this down to a simple test > case? > > > >Jon > > > > > > > > > >________________________________ > >From: Hamza Abdelkebir <[email protected]> > >To: Jon Svede <[email protected]>; POI Users List <[email protected]> > >Sent: Wed, June 29, 2011 1:21:13 AM > >Subject: Re: Removing sheets from workbook (poi 3.8) > > > >In fact I'm using 7 sheets as templates at the end of my xls file. I clone > >to another position in the file & rename eache sheet with a unique name > like > >this : > > > >sheet=destwb.cloneSheet(destwb.getNumberOfSheets()-6); > >destwb.setSheetOrder(sheet.getSheetName(), shIndex); > >destwb.setSheetName(shIndex++,"Rack "+r.getRackname()); > > > > > >I tried to rename the templates simply "1","2",.., "7". > >Sheets from 7 down to 2 are removed correctly. But when the loop arrives > to > >sheet "1" it fails with the same exception. > > > > > >Hamza > > > > > > > >On Tue, Jun 28, 2011 at 6:48 PM, Jon Svede <[email protected]> wrote: > > > >> Are you saying that you have sheets of the same name or that they start > >> with the > >> same literal value - "Rack"? So would a use case be 6 sheets with the > >> names > >> Rack1, Rack2,...,Rack6? > >> > >> When I ran my tests I created sheets with names like 'Sheet1', 'Sheet2', > >> etc., > >> and that test was successful, which suggests to me that having similarly > >> named > >> sheets shouldn't be a problem. > >> > >> Jon > >> > >> > >> > >> > >> > >> ________________________________ > >> From: Hamza Abdelkebir <[email protected]> > >> To: Jon Svede <[email protected]> > >> Cc: POI Users List <[email protected]> > >> Sent: Tue, June 28, 2011 8:43:15 AM > >> Subject: Re: Removing sheets from workbook (poi 3.8) > >> > >> Hi, > >> I think my problem is that the sheets that I'm trying to remove all > start > >> with "Rack" and as other sheets that don't need to be removed start the > >> same > >> way the XLS file gets messed up. > >> > >> I don't know how to fix this. > >> > >> Hamza > >> > >> > >> On Fri, Jun 24, 2011 at 8:16 PM, Jon Svede <[email protected]> wrote: > >> > >> > If I create a workbook in Excel and open it in POI 3.8 BETA 3 and use > >> your > >> > method, it works. > >> > > >> > Is there any more info you can provide? > >> > > >> > Jon > >> > > >> > > >> > ------------------------------ > >> > *From:* Hamza Abdelkebir <[email protected]> > >> > *To:* Jon Svede <[email protected]> > >> > *Cc:* POI Users List <[email protected]> > >> > *Sent:* Fri, June 24, 2011 8:52:37 AM > >> > *Subject:* Re: Removing sheets from workbook (poi 3.8) > >> > > >> > Yes, I'm using a workbook which represents an existing xls file. > >> > > >> > Hamza > >> > > >> > > >> > On Fri, Jun 24, 2011 at 4:50 PM, Jon Svede <[email protected]> wrote: > >> > > >> > > How is the workbook being created? Is the workbook representing an > >> > > existing file or are you creating? > >> > > > >> > > Jon > >> > > > >> > > > >> > > > >> > > ------------------------------ > >> > > *From:* Hamza Abdelkebir <[email protected]> > >> > > *To:* POI Users List <[email protected]> > >> > > *Sent:* Fri, June 24, 2011 1:54:45 AM > >> > > *Subject:* Removing sheets from workbook (poi 3.8) > >> > > > >> > > Hi all, > >> > > > >> > > I'm having this exception when trying to remove the last 4 sheets > from > >> a > >> > > workbook : > >> > > > >> > > Exception in thread "main" java.lang.IllegalArgumentException: > >> calculated > >> > > end index (484283) is out of allowable range (484279..484282) > >> > > at > >> > > > >> > > > >> > > >>>org.apache.poi.util.LittleEndianByteArrayOutputStream.<init>(LittleEndianByteArrayOutputStream.java:41) > >>> > >>) > >> >) > >> > > at > >> > > > >> > > >> > org.apache.poi.hssf.record.StandardRecord.serialize(StandardRecord.java:38) > >> > > at > >> > > > >> > > > >> > > >>>org.apache.poi.hssf.usermodel.HSSFWorkbook$SheetRecordCollector.serialize(HSSFWorkbook.java:1246) > >>> > >>) > >> >) > >> > > at > >> > > > >> > > >> > org.apache.poi.hssf.usermodel.HSSFWorkbook.getBytes(HSSFWorkbook.java:1292) > >> > > at > >> > > org.apache.poi.hssf.usermodel.HSSFWorkbook.write(HSSFWorkbook.java:1191) > >> > > at pb.odm.ODM.saveData(ODM.java:1212) > >> > > at pb.odm.ODM.buildODM(ODM.java:195) > >> > > at pb.odm.ODM.run(ODM.java:1222) > >> > > at pb.odm.Main.main(Main.java:11) > >> > > > >> > > > >> > > Using this method : > >> > > > >> > > private void removeExtraSheets(){ > >> > > int i=0; > >> > > while(i<3){ > >> > > destwb.removeSheetAt(destwb.getNumberOfSheets()-1); > >> > > i++; > >> > > } > >> > > } > >> > > > >> > > Any ideas ? > >> > > > >> > > Thanks, > >> > > Hamza > >> > > > >> > > >> > > >
