Support Center

Change worksheets order in workbook

With a release of GemBox.Spreadsheet 3.9 this article is now obsolete.
GemBox.Spreadsheet now provides a direct API on ExcelFile's Worksheets property for reordering worksheets (ExcelWorksheetCollection.Move method).

 

 

 

 

Adding a new ExcelWorksheet into an ExcelFile instance is achieved with an Add method from the ExcelWorksheetCollection, for example:

C# code

ExcelFile file = new ExcelFile();
ExcelWorksheet sheet2 = file.Worksheets.Add("Sheet 2");
ExcelWorksheet sheet4 = file.Worksheets.Add("Sheet 4");

VB.NET code

Dim file As New ExcelFile()
Dim sheet2 As ExcelWorksheet = file.Worksheets.Add("Sheet 2")
Dim sheet4 As ExcelWorksheet = file.Worksheets.Add("Sheet 4")

Inserting a new ExcelWorksheet into the ExcelWorksheetCollection is achieved with an InsertEmpty method. This method will insert an ExcelWorksheet instance before a worksheet that called it, for example:

C# code

ExcelWorksheet sheet1 = sheet2.InsertEmpty("Sheet 1");
ExcelWorksheet sheet3 = sheet4.InsertEmpty("Sheet 3");

VB.NET code

Dim sheet1 As ExcelWorksheet = sheet2.InsertEmpty("Sheet 1")
Dim sheet3 As ExcelWorksheet = sheet4.InsertEmpty("Sheet 3")

Now to re-order our worksheets we can use an InsertCopy method followed by the Delete method on the targeted ExcelWorksheet to change its position inside a worksheets collection. Or if it needs to be added to the very end of the worksheets collection then we can use an AddCopy method on the worksheets collection followed by the Delete method on the targeted ExcelWorksheet, for example:

C# code

// "Sheet 4" to first position.
string sheet4Name = sheet4.Name;
ExcelWorksheet copiedSheet4 = sheet1.InsertCopy("Temperary name", sheet4);
sheet4.Delete();
copiedSheet4.Name = sheet4Name;

// "Sheet 1" to last position.
string sheet1Name = sheet1.Name;
ExcelWorksheet copiedSheet1 = file.Worksheets.AddCopy("Temperary name", sheet1);
sheet1.Delete();
copiedSheet1.Name = sheet1Name;

VB.NET code

' "Sheet 4" to first position.
Dim sheet4Name As String = sheet4.Name
Dim copiedSheet4 As ExcelWorksheet = sheet1.InsertCopy("Temperary name", sheet4)
sheet4.Delete()
copiedSheet4.Name = sheet4Name

' "Sheet 1" to last position.
Dim sheet1Name As String = sheet1.Name
Dim copiedSheet1 As ExcelWorksheet = file.Worksheets.AddCopy("Temperary name", sheet1)
sheet1.Delete()
copiedSheet1.Name = sheet1Name

1 of 2 people found this page helpful
Subscribe to this article to get an email notification when it is updated.

0 Comments

  • There are no comments.