I called setSheetOrder(...) of HSSFWorkBook re-set the order of one worksheet. The worksheet was re-located successfully. However, the sheet tab name for that location where the new sheet was moved to still points to the name of original sheet. To re-produce: 1) Open a workbook with two worksheets: sheet 1 followed by sheet 2; 2) Call setSheetOrder(...) to move sheet 2 before sheet 1; 3) Save the file; 4) Open the modified file: see the sheet name in worksheet tab still has the order of sheet 1 followed by sheet 2 even though the actual work wheets have been switched.
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