poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jon Svede <jsv...@yahoo.com>
Subject RE: Removing sheets from workbook (poi 3.8)
Date Wed, 29 Jun 2011 17:43:31 GMT
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 <hamza.abdelkebir@gmail.com>
To: Jon Svede <jsvede@yahoo.com>; POI Users List <user@poi.apache.org>
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 <jsvede@yahoo.com> 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 <hamza.abdelkebir@gmail.com>
>To: Jon Svede <jsvede@yahoo.com>; POI Users List <user@poi.apache.org>
>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 <jsvede@yahoo.com> 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 <hamza.abdelkebir@gmail.com>
>> To: Jon Svede <jsvede@yahoo.com>
>> Cc: POI Users List <user@poi.apache.org>
>> 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 <jsvede@yahoo.com> 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 <hamza.abdelkebir@gmail.com>
>> > *To:* Jon Svede <jsvede@yahoo.com>
>> > *Cc:* POI Users List <user@poi.apache.org>
>> > *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 <jsvede@yahoo.com> wrote:
>> >
>> > > How is the workbook being created?  Is the workbook representing an
>> > > existing file or are you creating?
>> > >
>> > > Jon
>> > >
>> > >
>> > >
>> > > ------------------------------
>> > > *From:* Hamza Abdelkebir <hamza.abdelkebir@gmail.com>
>> > > *To:* POI Users List <user@poi.apache.org>
>> > > *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
>> > >
>> >
>>
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message