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" file
All 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
> > > >
> > >
> >
>
