Bug 50867 - Problems adding a new XSSFSheet before a sheet containing a table
Summary: Problems adding a new XSSFSheet before a sheet containing a table
Status: RESOLVED WORKSFORME
Alias: None
Product: POI
Classification: Unclassified
Component: XSSF (show other bugs)
Version: 3.7-FINAL
Hardware: PC Windows XP
: P2 major (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2011-03-04 03:50 UTC by Donato
Modified: 2015-09-23 07:00 UTC (History)
1 user (show)



Attachments
original Excel 2007 file and POI output file (12.40 KB, application/x-zip-compressed)
2011-03-04 03:50 UTC, Donato
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Donato 2011-03-04 03:50:41 UTC
Created attachment 26728 [details]
original Excel 2007 file and POI output file

I created a .xlsx file contaning an Excel 2007 table in the first sheet.
Then, using POI, I opened it a created a new sheet with:
wb.createSheet("ARC")
wb.setSheetOrder("ARC", 0)
The resulting Excel file seems to be corrupted. Not only the table (now in the second sheet) is "read-only", but deleting the first sheet ("test") also the sheet with the table is deleted.
This bug is related only to XSSF (with .xls files and HSSF no problems).
Comment 1 Nick Burch 2011-03-04 12:35:12 UTC
Bad news - I can't see anything wrong with what POI is doing

I've looked at the POI output xlsx file, and all the relationships and links look just fine to me. I've added a unit test in r1078065 which loads the original file, checks it, adds the new sheet and re-orders, checks, saves + reloads, and checks. All passes just fine.

Incidentally, OpenOffice has no problems opening the POI saved file, and the table is writable. Unfortunately, that either means you've hit an Excel bug, or you've found an area where Excel is much stricter than it needs to be (and also stricter than POI + OpenOffice are)

What I'd suggest you do is firstly try a different version of excel, in case it is an excel bug. Failing that, perform the same action in excel as you did in poi, and see if you can identify (by unzipping and comparing the xml) what excel does differently
Comment 2 Donato 2011-03-08 02:27:09 UTC
(In reply to comment #1)
> Bad news - I can't see anything wrong with what POI is doing
> I've looked at the POI output xlsx file, and all the relationships and links
> look just fine to me. I've added a unit test in r1078065 which loads the
> original file, checks it, adds the new sheet and re-orders, checks, saves +
> reloads, and checks. All passes just fine.
> Incidentally, OpenOffice has no problems opening the POI saved file, and the
> table is writable. Unfortunately, that either means you've hit an Excel bug, or
> you've found an area where Excel is much stricter than it needs to be (and also
> stricter than POI + OpenOffice are)
> What I'd suggest you do is firstly try a different version of excel, in case it
> is an excel bug. Failing that, perform the same action in excel as you did in
> poi, and see if you can identify (by unzipping and comparing the xml) what
> excel does differently

Hi Nick, I tested the workbook created with POI on different Excel 2007 installations and as a matter of fact sometimes the bug happens and sometimes does not. I found that the problem is that more than one sheet are selected by default when the workbook is opened (and this prevents updating the table).
The simple workaround I found is to use:

workBook.getSheetAt(j).setSelected(false);

on ALL sheets in the workBook. This solves the problem (so I think the bug can be closed). Thanks!
Comment 3 Dominik Stadler 2015-08-23 19:26:23 UTC
I could not reproduce this any more in a quick test with the latest version 3.13-dev, I think we had some fixes related to the default selected sheet during moving/re-ordering so I assume this is fixed since some time, please re-test this with a recent nightly build and reopen this if you still see this problem.