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