Summary: | setSheetOrder(...) of HSSFWorkBook class doesn't set tab name correctly in Excel file | ||
---|---|---|---|
Product: | POI | Reporter: | Jim Shen <jim.shen> |
Component: | HSSF | Assignee: | POI Developers List <dev> |
Status: | RESOLVED FIXED | ||
Severity: | major | CC: | adrianrm, lingjiao.chen, rich.renomeron |
Priority: | P3 | ||
Version: | 2.0-pre3 | ||
Target Milestone: | --- | ||
Hardware: | Other | ||
OS: | All | ||
Attachments: | patch that fixes the tab order problem |
Description
Jim Shen
2003-10-20 18:16:24 UTC
I can confirm that this happens in the 2.5.1 release. Here's a workaround that I use for the case where you want to put the last sheet in the first position: for(int i = workbook.getNumberOfSheets() - 2; i >= 0 ; --i) { String sheetName = workbook.getSheetName(i); workbook.cloneSheet(i); workbook.removeSheetAt(i); int lastSheet = workbook.getNumberOfSheets() - 1; workbook.setSheetName(lastSheet, sheetName); } It's probably not too hard to generalize this for an abitrary position. I think the problem goes deeper. Here is an example error case: I have a workbook with 6 sheets 1) Open the workbook 2) Call setSheetOrder(...) to move sheet 3 before sheet 1; 3) Save the file 4) Open the file The result is that the name of the tabs hasn't changed, but the contents of the tabs follow the order: 2,3,1,4,5,6. I've tried to fix it, but don't know much about the project, so it didn't make (apparently)anything. I changed the setSheetOrder method in class HSSFWorkbook: public void setSheetOrder(String sheetname, int pos ) { workbook.setSheetOrder(sheetname, pos); int sheetNumber = getSheetIndex(sheetname); //remove the sheet that needs to be reordered and place it in the spot we want sheets.add(pos, sheets.remove(sheetNumber)); } Here's a better workaround for using setSheetOrder() without using cloneSheet() method. The current open-source version will not support sheet cloning if the sheet has any embedded images or objects. As you've probably found out, setSheetOrder() only swaps the actual sheet content -- not the sheet's name. So you'll need to manually set that on your own. As regards to the contents not swapping properly, try not to think about it as 'sheet 3 BEFORE sheet 1', but rather the opposite direction. So if you write the code as: workbook.setSheetName(sheetNameWhereIndexIsZero, 3); //this will work. Anyways, here's my arbitrary positioned workaround: //sheetName buffer. int intTotalSheet = workbbook.getNumberOfSheets(); String[] straSheetName = new String[intTotalSheet]; for (int b=0; b<intTotalSheet; b++) straSheetName[b] = workbook.getSheetName(b); workbook.setSheetName(intTotalSheet-1, "newSheet"); workbook.setSheetName(0, SHEET_NAME); for(int a = 1; a < intTotalSheet; a++) { workbook.setSheetName(a, straSheetName[a-1]); } workbook.setSheetOrder(wb.getSheetName(0), intTotalSheet-1); Created attachment 20308 [details]
patch that fixes the tab order problem
the problem was in sheet serialization, hssf did not actually write the bound
sheets from the bound sheets collection and thus the records were written in
the original order. This probably affected new sheets and other things as
well.
fixed in HEAD Mon Jun 4 13:52:54 UTC 2007 |