File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Other Open Source Projects and the fly likes Strange error with Apache POI Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCA/OCP Java SE 7 Programmer I & II Study Guide this week in the OCPJP forum!
JavaRanch » Java Forums » Products » Other Open Source Projects
Bookmark "Strange error with Apache POI" Watch "Strange error with Apache POI" New topic
Author

Strange error with Apache POI

Joe Harry
Ranch Hand

Joined: Sep 26, 2006
Posts: 9466
    
    2

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


>


SCJP 1.4, SCWCD 1.4 - Hints for you, Certified Scrum Master
Did a rm -R / to find out that I lost my entire Linux installation!
David Newton
Author
Rancher

Joined: Sep 29, 2008
Posts: 12617

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 Harry
Ranch Hand

Joined: Sep 26, 2006
Posts: 9466
    
    2

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 Harry
Ranch Hand

Joined: Sep 26, 2006
Posts: 9466
    
    2

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 Harry
Ranch Hand

Joined: Sep 26, 2006
Posts: 9466
    
    2

Thinking to try reverse iterate and remove the sheets that I do not want....Any ideas other than this?
Joe Harry
Ranch Hand

Joined: Sep 26, 2006
Posts: 9466
    
    2

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
Rancher

Joined: Sep 29, 2008
Posts: 12617

Just use index 0 until there are no more left?
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42360
    
  64
You're thinking way too complicated:


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


Ping & DNS - my free Android networking tools app
Joe Harry
Ranch Hand

Joined: Sep 26, 2006
Posts: 9466
    
    2

I did not quite get you.
Joe Harry
Ranch Hand

Joined: Sep 26, 2006
Posts: 9466
    
    2

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
Marshal

Joined: Mar 22, 2005
Posts: 42360
    
  64
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 Harry
Ranch Hand

Joined: Sep 26, 2006
Posts: 9466
    
    2

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
Marshal

Joined: Mar 22, 2005
Posts: 42360
    
  64
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 Harry
Ranch Hand

Joined: Sep 26, 2006
Posts: 9466
    
    2

But can we try something like revers sort, forward sort and so on?? and then may be remove the unwanted sheets.
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42360
    
  64
I don't see what that would accomplish, besides complicating the issue.
Joe Harry
Ranch Hand

Joined: Sep 26, 2006
Posts: 9466
    
    2

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
Marshal

Joined: Mar 22, 2005
Posts: 42360
    
  64
Have you had tried what I suggested two posts back? I suspect that you'd be done by now if you had.
Joe Harry
Ranch Hand

Joined: Sep 26, 2006
Posts: 9466
    
    2

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
Marshal

Joined: Mar 22, 2005
Posts: 42360
    
  64
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 Harry
Ranch Hand

Joined: Sep 26, 2006
Posts: 9466
    
    2

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 Harry
Ranch Hand

Joined: Sep 26, 2006
Posts: 9466
    
    2

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
Marshal

Joined: Mar 22, 2005
Posts: 42360
    
  64
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 Harry
Ranch Hand

Joined: Sep 26, 2006
Posts: 9466
    
    2

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 Harry
Ranch Hand

Joined: Sep 26, 2006
Posts: 9466
    
    2

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
Marshal

Joined: Mar 22, 2005
Posts: 42360
    
  64
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 Harry
Ranch Hand

Joined: Sep 26, 2006
Posts: 9466
    
    2

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 Harry
Ranch Hand

Joined: Sep 26, 2006
Posts: 9466
    
    2

@Ulf: your expertise needed here...and thanks for all the support that you have given here!
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42360
    
  64
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 Harry
Ranch Hand

Joined: Sep 26, 2006
Posts: 9466
    
    2

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 Harry
Ranch Hand

Joined: Sep 26, 2006
Posts: 9466
    
    2

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 Harry
Ranch Hand

Joined: Sep 26, 2006
Posts: 9466
    
    2

Simply could not figure out what the heck is happening!
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42360
    
  64
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 Harry
Ranch Hand

Joined: Sep 26, 2006
Posts: 9466
    
    2

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
Marshal

Joined: Mar 22, 2005
Posts: 42360
    
  64
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 Harry
Ranch Hand

Joined: Sep 26, 2006
Posts: 9466
    
    2

Yes. I just missed the fact that the getNumberOfSheets will keep changing. It simply worked now.

Ulf: Thanks for the hint!
Joe Harry
Ranch Hand

Joined: Sep 26, 2006
Posts: 9466
    
    2

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 Harry
Ranch Hand

Joined: Sep 26, 2006
Posts: 9466
    
    2

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.

 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Strange error with Apache POI