Bug 23951 - setSheetOrder(...) of HSSFWorkBook class doesn't set tab name correctly in Excel file
Summary: setSheetOrder(...) of HSSFWorkBook class doesn't set tab name correctly in Ex...
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 2.0-pre3
Hardware: Other All
: P3 major with 9 votes (vote)
Target Milestone: ---
Assignee: POI Developers List
Depends on:
Reported: 2003-10-20 18:16 UTC by Jim Shen
Modified: 2007-06-04 06:54 UTC (History)
3 users (show)

patch that fixes the tab order problem (2.29 KB, patch)
2007-06-04 06:51 UTC, Andy Oliver
Details | Diff

Note You need to log in before you can comment on or make changes to this bug.
Description Jim Shen 2003-10-20 18:16:24 UTC
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 

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.
Comment 1 Rich Renomeron 2006-06-14 21:03:43 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);
            int lastSheet = workbook.getNumberOfSheets() - 1;
            workbook.setSheetName(lastSheet, sheetName);

It's probably not too hard to generalize this for an abitrary position.
Comment 2 Jadriman 2006-08-24 16:51:26 UTC
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));	        
Comment 3 Ling Chen 2007-03-27 12:44:41 UTC
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);
Comment 4 Andy Oliver 2007-06-04 06:51:24 UTC
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
Comment 5 Andy Oliver 2007-06-04 06:54:00 UTC
fixed in HEAD Mon Jun  4 13:52:54 UTC 2007