• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Strange error with Apache POI

 
Ranch Hand
Posts: 10198
3
Mac PPC Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Guys,

I explored a bit on Apache POI to work with the Excel sheets in my Java application. What I'm trying to do is just a simple thing. Read a master excel file which has around 6 worksheets inside, delete all sheets except the last one and save the workbook as a new excel file.


But strangely, I get the following error for which I have no clue...


>
 
Author
Posts: 12617
IntelliJ IDE Ruby
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Are you sure that once a sheet is removed the indices aren't "reset"? In other words, if there are 6 sheets (0-5), and sheet 0 is removed, are the indices now 0-4?
 
Joe San
Ranch Hand
Posts: 10198
3
Mac PPC Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I just checked that and yes the indices are getting replaced. After removing the first two sheets, I checked for the count of sheets and it gave me 4. Have to do a workaround for this.
 
Joe San
Ranch Hand
Posts: 10198
3
Mac PPC Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Any suggestions on how I could do a workaround for this? All I have to acheive is to create seperate Excels out of the master excel.
 
Joe San
Ranch Hand
Posts: 10198
3
Mac PPC Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thinking to try reverse iterate and remove the sheets that I do not want....Any ideas other than this?
 
Joe San
Ranch Hand
Posts: 10198
3
Mac PPC Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Or perhaps forward iterate till the sheet that I want to retain, break out of that loop, call another loop and reverse iterate till the the zeroth record which would now be the one that I have to retain.
 
David Newton
Author
Posts: 12617
IntelliJ IDE Ruby
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Just use index 0 until there are no more left?
 
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
You're thinking way too complicated:


Edit: ... which is just what David suggests.
 
Joe San
Ranch Hand
Posts: 10198
3
Mac PPC Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I did not quite get you.
 
Joe San
Ranch Hand
Posts: 10198
3
Mac PPC Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Ulf Dittmer wrote:You're thinking way too complicated:


Edit: ... which is just what David suggests.



Yes, but what does that bring me? I have 6 sheets and I have to create 6 workbooks out of those 6 sheets in the master excel. But when I simply say wb.removeSheetAt(0); I do not have control on getting my individual sheets? Am I right?
 
Ulf Dittmer
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Yes, but what does that bring me? I have 6 sheets and I have to create 6 workbooks out of those 6 sheets in the master excel.


That's different from what you said in your first post:

...delete all sheets except the last one...


So what are you actually trying to do?
 
Joe San
Ranch Hand
Posts: 10198
3
Mac PPC Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Well. let me post the entire code here....

The goal is to first get the individual excel sheets out of the main workbook. Send them to the OpenOffice to get them converted as PDF's.


 
Ulf Dittmer
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
In that case, you'll need to open the file N times if it contains N sheets, and each time remove those N-1 sheets you don't want to save.
 
Joe San
Ranch Hand
Posts: 10198
3
Mac PPC Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
But can we try something like revers sort, forward sort and so on?? and then may be remove the unwanted sheets.
 
Ulf Dittmer
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I don't see what that would accomplish, besides complicating the issue.
 
Joe San
Ranch Hand
Posts: 10198
3
Mac PPC Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Like it is shown in my code sample above, one loop will iterate through the sheets and for each sheet, pass the workbook to another method which will create a new workbook with the workbook object passed and here forward iterate till the page that we need to retain is reached (once this is done the page that we want to retain will be the first page i. e., with index 0), break from the loop and call another method which will revers iterate till the first page.
 
Ulf Dittmer
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Have you had tried what I suggested two posts back? I suspect that you'd be done by now if you had.
 
Joe San
Ranch Hand
Posts: 10198
3
Mac PPC Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I hope you are aware of the fact that the indices get renumbered once you delete a sheet. Your sample code simply deletes the sheet at 0 which I do not want. I hope you understood my problem. Let me try and will post the solution here.
 
Ulf Dittmer
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Is that a reply to my first post? That has since been partially invalidated by your subsequent posts, in which you changed the problem that you're trying to address. And even that code does not "simply deletes the sheet at 0 which I do not want." - it does that repeatedly in order to "delete all sheets except the last one " which is what you said initially you were trying to accomplish.

I have addressed this changed problem in my third post. Please take the time and read the full conversation, and then think about what you're read, before doubting it.
 
Joe San
Ranch Hand
Posts: 10198
3
Mac PPC Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Ulf Dittmer wrote:In that case, you'll need to open the file N times if it contains N sheets, and each time remove those N-1 sheets you don't want to save.



Are you referring to your post below in quotes?? If yes, then how do you think that if I have N sheets and I want to retain the N-3 sheet by deleting the rest? You understand what I mean?
 
Joe San
Ranch Hand
Posts: 10198
3
Mac PPC Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
The indices get renumbered each and every time I delete a sheet. I have an idea to do this and it is like this:

Forward iterate till the sheet number that I want to delete becomes the 0th sheet. Then use the while loop to delete the rest while retaining the 0th sheet which is now I want. The pseudo code might look like this,



So does that make sense?
 
Ulf Dittmer
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
That looks like you're getting closer. But "workbook.removeSheetAt(i)" should be "workbook.removeSheetAt(0)", or you'll again run into the problem you described in the first post.
 
Joe San
Ranch Hand
Posts: 10198
3
Mac PPC Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Ulf Dittmer wrote:That looks like you're getting closer. But "workbook.removeSheetAt(i)" should be "workbook.removeSheetAt(0)", or you'll again run into the problem you described in the first post.



No, it is not i but it is the index number 1....
 
Joe San
Ranch Hand
Posts: 10198
3
Mac PPC Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
The idea is to iterate till I bring the worksheet that I want to retain to index 0 and use the while loop to delete everything else after 0.
 
Ulf Dittmer
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Yes, it's clear what you're trying to do. But, as I said, the pseudo-code you posted uses "removeSheetAt(i)" in line 4, and that will trigger the very error mentioned in your first post; the index needs to be "0".
 
Joe San
Ranch Hand
Posts: 10198
3
Mac PPC Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
So here is my entire program, but still there seems to be one problem. I tried with an Excel workbook that has 6 sheets alltogether but when I ran it, after creating the seperate Excel and PDF for the first two sheets, the remaining sheets get the same name and the content as the second sheet. What did I do wrong here?

 
Joe San
Ranch Hand
Posts: 10198
3
Mac PPC Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
@Ulf: your expertise needed here...and thanks for all the support that you have given here!
 
Ulf Dittmer
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
The code is still doing exactly what I said in my previous two posts was causing the problem. If that didn't make sense to you, I advise to print out the number of sheets in the workbook -along with the value of "i"- before each "removeSheetAt" method call.
 
Joe San
Ranch Hand
Posts: 10198
3
Mac PPC Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I really do not understand this,



What I'm simply trying to do is to delete the sheets till the counter, so that the sheet that I want to retain becomes the sheet with 0th index. And once this happens, call the saveWorkBook(newWB) method which will delete the rest from 1 to N so that I have the single sheet at index 0 and just save that.>
 
Joe San
Ranch Hand
Posts: 10198
3
Mac PPC Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I even tried replacing the i with 0 as mentioned in your post, but it ran till the third sheet and the remaining sheets (4, 5 and 6) were created with the content of the 3rd sheet.
 
Joe San
Ranch Hand
Posts: 10198
3
Mac PPC Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Simply could not figure out what the heck is happening!
 
Ulf Dittmer
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
We had already established that each call to removeSheetAt leads to all pages being renumbered, hadn't we?

If you really don't want to contemplate taking my advice of repeatedly using removeSheetAt(0) (why not, by the way?), then you can start the loop at "newWB.getNumberOfSheets()-1" and count down to 0 from there.

But you should still try to understand why it doesn't work the way you're trying to do it. Maybe you can write down on paper the numbers of the sheets, before and after each iteration and renumbering, and what the value of "i" would be at each step (and -cruciallly!- what the call to getNumberOfSheets returns).
 
Joe San
Ranch Hand
Posts: 10198
3
Mac PPC Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Ulf Dittmer wrote:We had already established that each call to removeSheetAt leads to all pages being renumbered, hadn't we?

If you really don't want to contemplate taking my advice of repeatedly using removeSheetAt(0) (why not, by the way?), then you can start the loop at "newWB.getNumberOfSheets()-1" and count down to 0 from there.

But you should still try to understand why it doesn't work the way you're trying to do it. Maybe you can write down on paper the numbers of the sheets, before and after each iteration and renumbering, and what the value of "i" would be at each step (and -cruciallly!- what the call to getNumberOfSheets returns).



I did use removeSheetAt(0) instead of i and I found out that it simply printed the first 3 pdf documents and the remaining documents 4, 5 and 6 had the same content of the 3rd pdf document.
 
Ulf Dittmer
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

I even tried replacing the i with 0 as mentioned in your post, but it ran till the third sheet and the remaining sheets (4, 5 and 6) were created with the content of the 3rd sheet.


That's a good start - note how you didn't get an exception any longer? That's an improvement, isn't it? The other missing piece is the call to "getNumberOfSheets" in the loop - that number changes each time a sheet is removed. So the loop must not stop at the current number of sheets, it must stop at the initial number of sheets before you removed any. Since that number is lost once you start removing sheets, you need to store it before the loop.
 
Joe San
Ranch Hand
Posts: 10198
3
Mac PPC Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Yes. I just missed the fact that the getNumberOfSheets will keep changing. It simply worked now.

Ulf: Thanks for the hint!
 
Joe San
Ranch Hand
Posts: 10198
3
Mac PPC Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Ulf Dittmer wrote:

I even tried replacing the i with 0 as mentioned in your post, but it ran till the third sheet and the remaining sheets (4, 5 and 6) were created with the content of the 3rd sheet.


That's a good start - note how you didn't get an exception any longer? That's an improvement, isn't it? The other missing piece is the call to "getNumberOfSheets" in the loop - that number changes each time a sheet is removed. So the loop must not stop at the current number of sheets, it must stop at the initial number of sheets before you removed any. Since that number is lost once you start removing sheets, you need to store it before the loop.



Man, you are a true Sheriff!
 
Joe San
Ranch Hand
Posts: 10198
3
Mac PPC Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Here is the complete working code for converting Excel To PDF!

Setup OpenOffice, make it listening on port 8100 and run the program below. I used Apache POI, JDOConverter and had an OpenOffice 3.0 installation. See the class level Javadoc in the code below on how to run OpenOffice 3 as a service on Windows installation.

 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic