Sort worksheet tabs in alphabetical order

Aspose.Cells Product Family
1

Hello,


I apologize if this question has already been answered somewhere else, but do you have a suggestion for how I could sort all of the worksheet tabs in a workbook alphabetically by name?

For example, if I open up a workbook that has the following tabs:

Sheet1
Sheet3
Sheet4
Sheet10
Sheet12
Sheet5
Sheet2
Sheet11

How could I get that re-sorted so it looks like this:

Sheet1
Sheet2
Sheet3
Sheet4
Sheet5
Sheet10
Sheet11
Sheet12

Thank you!

–Eric

2

Hi,


Thanks for your posting and using Aspose.Cells.

First access the names of your sheets and sort them and then re-arrange the worksheets as per your sorted array.

Please see the following code, its sample Excel file, its output Excel file and screenshot. As you can see in the output excel file, all sheets have been re-arranged as per the sorted array of sheet names.

Java
//Suppose, you have already sorted the sheet names.
String[] sortedArrayOfSheetNames = new String[]{“Sheet1”, “Sheet2”, “Sheet3”, “Sheet4”, “Sheet5”, “Sheet6”, “Sheet7”, “Sheet8”};

//Open your workbook
Workbook wb = new Workbook(dirPath + “sortSheet.xlsx”);

//Count of sheets
int count = wb.getWorksheets().getCount();

//Sort them according to your sorted array
for(int i=0; i<count; i++)
{
//Sheet name
String sheetName = sortedArrayOfSheetNames[i];
//Access the sheet by its name
Worksheet ws = wb.getWorksheets().get(sheetName);
//Move the sheet
ws.moveTo(i);
}

//Save the workbook
wb.save(dirPath + “outSortSheet.xlsx”);

3

This is perfect, thank you very much!


What do you recommend using as the most efficient way to get the NameCollection of worksheet names into a string[] array?

4

Hi,


See the following sample code for your reference. It print each named range in the workbook and you may easily save to your string arrays:
e.g
Sample code:

String filePath = “Book1.xlsx”;
Workbook workbook = new Workbook(filePath);
Worksheet worksheet = workbook.getWorksheets().get(0);
for (int i = 0; i < workbook.getWorksheets().getNames().getCount(); i++)
{
Name name = workbook.getWorksheets().getNames().get(i);
System.out.println("Name: " + name.getText());
System.out.println("Refers To: " + name.getRefersTo());
//…
//Your code goes here.
//…
}

Hope, this helps a bit.

Thank you.
5

Thank you. Here is my final working code:


//Count of sheets

int sheetCount = workbook.Worksheets.Count;

string[] sortedSheetNames = new string[sheetCount];


//Add sheets to an array

for (int i = 0; i < sheetCount; i++)

{

sortedSheetNames[i] = workbook.Worksheets[i].Name;

}


//Sort the sheets alphabetically

Array.Sort(sortedSheetNames, StringComparer.InvariantCulture);


//Sort them according to your sorted array

for (int i = 0; i < sheetCount; i++)

{

//Sheet name

String sheetName = sortedSheetNames[i];


<span style="color:#57a64a;">//Access the sheet by its name</span>
<span style="color:#4ec9b0;">Worksheet</span> <span style="color:gainsboro;">worksheet</span> <span style="color:#b4b4b4;">=</span> <span style="color:gainsboro;">workbook</span><span style="color:#b4b4b4;">.</span><span style="color:gainsboro;">Worksheets</span><span style="color:gainsboro;">[</span><span style="color:gainsboro;">sheetName</span><span style="color:gainsboro;">];</span>

<span style="color:#57a64a;">//Move the sheet</span>
<span style="color:gainsboro;">worksheet</span><span style="color:#b4b4b4;">.</span><span style="color:gainsboro;">MoveTo</span><span style="color:gainsboro;">(</span><span style="color:gainsboro;">i</span><span style="color:gainsboro;">);</span>



}
6

Hi,


Thanks for sharing the final code segment.

I think it may help other users who require to perform similar task. In the event of any other query, please feel free to write us back.

Thanks,