| Summary: | Problems adding a new XSSFSheet before a sheet containing a table | ||
|---|---|---|---|
| Product: | POI | Reporter: | Donato <d_tagliabue2> |
| Component: | XSSF | Assignee: | POI Developers List <dev> |
| Status: | RESOLVED WORKSFORME | ||
| Severity: | major | CC: | jiangguangtao |
| Priority: | P2 | ||
| Version: | 3.7-FINAL | ||
| Target Milestone: | --- | ||
| Hardware: | PC | ||
| OS: | Windows XP | ||
| Attachments: | original Excel 2007 file and POI output file | ||
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 (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! 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. |
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).