Work with Worksheets

While working with worksheets, you can perform the following operations to accomplish several important tasks in a workbook.

Access the Default Worksheet

Whenever a new workbook is created, an empty worksheet with the name Sheet1 is automatically added to the workbook. This worksheet is known as the default worksheet. For every workbook, only one default worksheet is added to it.

Refer to the following example code in order to access the default worksheet in your workbook.

// Fetch the default WorkSheet
IWorksheet worksheet = workbook.Worksheets[0];

Add Multiple Worksheets

A workbook may contain any number of worksheets. You can add one or more worksheets before or after a specific sheet in your workbook.

Refer to the following example code to insert multiple worksheets in a workbook.

//Initialize the WorkBook and add multiple WorkSheets
IWorksheet worksheet = workbook.Worksheets.Add();
IWorksheet worksheet2 = workbook.Worksheets.AddAfter(worksheet);
IWorksheet worksheet3 = workbook.Worksheets.AddBefore(worksheet2);

Note: The Add method in IWorksheets interface has an overload with SheetType, which lets you add two types of sheets, Worksheet or Chart sheet. By default, this method adds a Worksheet in the Workbook.

Activate a Worksheet

While working with multiple worksheets in a workbook, you may require to make the current sheet to workbook's active sheet so as to execute certain operations on that particular worksheet. This can be done using the Activate method of the IWorksheet interface.

Refer to the following example code to activate a worksheet.

IWorksheet worksheet3 = workbook.Worksheets.Add();
            
//Activate new created worksheet.
worksheet3.Activate();

Access a Worksheet

All the worksheets within a workbook are stored in Worksheets collection. In order to access a specific worksheet within a workbook, you can choose either of the two ways : using the Index property or using the Name property of the IWorksheet interface.

Refer to the following example code to access a worksheet within the workbook.

//Use sheet index to access the worksheet.
IWorksheet worksheet4 = workbook.Worksheets[0];

////Use sheet name to access the worksheet.
IWorksheet worksheet5 = workbook.Worksheets["SampleSheet5"];
//worksheet5.Name = "SampleSheet5";

Protect a Worksheet

A worksheet can be protected by transforming it into a read-only sheet so that the data lying in the cells cannot be modified. The worksheet can be prevented from modification either by using a password or without it.

Protect worksheet from modification without password

A worksheet can be protected by setting the Protection property of the IWorksheet interface to true. Further, you can use the properties of the IProtectionSettings interface to explicitly setup your protected worksheet the way you want. Later, if you want to remove protection, you can unprotect your worksheet by setting the Protection property to false.

Refer to the following example code to protect or unprotect a worksheet from modification without password.

//protect worksheet, allow insert column.
worksheet3.Protection = true;
worksheet3.ProtectionSettings.AllowInsertingColumns = true;

//Unprotect worksheet.
worksheet3.Protection = false;

Protect Worksheet from Modification using Password

A worksheet can be made password protected to restrict modification by using the Protect method of IWorksheet interface. The password is a case sensitive string which can be passed as a parameter to the Protect method.

Refer to the following example code to protect a worksheet from modification using password.

// Initialize workbook
Workbook workbook = new Workbook();
// Fetch default worksheet 
IWorksheet workSheet = workbook.Worksheets[0];

//Protects the worksheet with password
workSheet.Protect("Ygs_87@ytr");
// Save workbook to xlsx
workbook.Save(@"ProtectWorksheetWithPassword.xlsx", SaveFileFormat.Xlsx);

A password protected worksheet can be unprotected by using the Unprotect method of IWorksheet interface. The correct password (password set in Protect method) needs to be passed as a parameter to the Unprotect method. In case, the password is omitted or an incorrect password is passed, an exception message "Invalid Password" is thrown.

Refer to the following example code to unprotect a worksheet from modification using password.

// Initialize workbook
Workbook workbook = new Workbook();
// Fetch default worksheet 
IWorksheet workSheet = workbook.Worksheets[0];
        
workSheet.Protect("Ygs_87@ytr");
//Removes the above protection from the worksheet
workSheet.Unprotect("Ygs_87@ytr");
// Save workbook to xlsx
workbook.Save(@"UnprotectWorksheetWithPassword.xlsx", SaveFileFormat.Xlsx);

Delete Worksheet

You can remove one or more worksheets from a workbook. When you delete a worksheet, it automatically gets deleted from the Worksheets collection.

Refer to the following example code to delete a specific sheet from the workbook.

IWorksheet worksheet7 = workbook.Worksheets.Add();

//workbook must contain one visible worksheet at least, if delete the one visible worksheet, it will throw exception.
worksheet7.Delete();

Copy and Move Worksheet

You can copy the current spreadsheet on which you're working as well as copy a worksheet between workbooks and then move them to a specific location as per your custom requirements and preferences. This can be done by using the Copy() method, the CopyAfter() method, the CopyBefore() method, the Move() method, the MoveBefore() method and the MoveAfter() method of the IWorksheet interface. Using these methods, the worksheet can easily be copied and relocated by placing it within the same workbook or another workbook as and when you want.

Refer to the following example code in order to copy a worksheet.

// Initialize workbook
Workbook workbook = new Workbook();
        
// Fetch the active worksheet 
IWorksheet worksheet = workbook.ActiveSheet;

object[,] data = new object[,]{
{"Name", "City", "Birthday", "Sex", "Weight", "Height", "Age"},
{"Bob", "newyork", new DateTime(1968, 6, 8), "male", 80, 180, 56},
{"Betty", "newyork", new DateTime(1972, 7, 3), "female", 72, 168, 45},
{"Gary", "NewYork", new DateTime(1964, 3, 2), "male", 71, 179, 50},
{"Hunk", "Washington", new DateTime(1972, 8, 8), "male", 80, 171, 59},
{"Cherry", "Washington", new DateTime(1986, 2, 2), "female", 58, 161, 34},
{"Coco", "Virginia", new DateTime(1982, 12, 12), "female", 58, 181, 45},
{"Lance", "Chicago", new DateTime(1962, 3, 12), "female", 49, 160, 57},
{ "Eva", "Washington", new DateTime(1993, 2, 5), "female", 71, 180, 81}};
        
// Set data
worksheet.Range["A1:G9"].Value = data;

// Copy the active sheet to the end of current workbook
var copy_worksheet = worksheet.Copy();
copy_worksheet.Name = "Copy of " + worksheet.Name;

// Saving workbook to xlsx
workbook.Save(@"CopyWorkSheet.xlsx", SaveFileFormat.Xlsx);

Refer to the following example code in order to copy a worksheet between the workbooks.

// Create a new workbook
var workbook = new GrapeCity.Documents.Excel.Workbook();
        
// Create another source_workbook
Workbook source_workbook = new Workbook();
        
// Fetch the active worksheet 
IWorksheet worksheet = source_workbook.ActiveSheet;
object[,] data = new object[,]{
{"Name", "City", "Birthday", "Sex", "Weight", "Height", "Age"},
{"Bob", "newyork", new DateTime(1968, 6, 8), "male", 80, 180, 56},
{"Betty", "newyork", new DateTime(1972, 7, 3), "female", 72, 168, 45},
{"Gary", "NewYork", new DateTime(1964, 3, 2), "male", 71, 179, 50},
{"Hunk", "Washington", new DateTime(1972, 8, 8), "male", 80, 171, 59},
{"Cherry", "Washington", new DateTime(1986, 2, 2), "female", 58, 161, 34},
{"Coco", "Virginia", new DateTime(1982, 12, 12), "female", 58, 181, 45},
{"Lance", "Chicago", new DateTime(1962, 3, 12), "female", 49, 160, 57},
{ "Eva", "Washington", new DateTime(1993, 2, 5), "female", 71, 180, 81}};
        
// Set data
worksheet.Range["A1:G9"].Value = data;

// Copy data of active sheet from source workbook to current workbook before Sheet1
var copy_worksheet = worksheet.CopyBefore(workbook.Worksheets[0]);
copy_worksheet.Name = "Copy of Sheet1";
copy_worksheet.Activate();
       
// Saving workbook to xlsx
workbook.Save(@"CopyWorkSheetBetweenWorkBooks.xlsx", SaveFileFormat.Xlsx);

Select Multiple Worksheets

GcExcel allows you to select multiple worksheets at once by using Select method of IWorksheets interface. The method takes an optional parameter replace, which:

Replaces the current selection with the specified object when set to True (default value).
Extends the current selection to include any previously selected objects and the specified object when set to False.

The selected worksheets can also be retrieved by using SelectedSheets property of IWorkbook interface. In addition, Excel files with multiple selected worksheets can be loaded, modified and saved back to Excel. GcExcel displays following behavior when multiple worksheets are selected:

If a non-selected sheet is activated, the selected sheets are deselected.
If a selected sheet is deleted, it is removed from selected sheets.
If all worksheets are selected and a worksheet is activated, it is set as the active sheet and all selected sheets are deselected.
If a worksheet is added, copied or moved, the selected sheets are deselected.

Refer to the following example code to select multiple worksheets in a workbook.

//create a new workbook
var workbook = new GrapeCity.Documents.Excel.Workbook();

var sheet1 = workbook.ActiveSheet;
var sheet2 = workbook.Worksheets.Add();
var sheet3 = workbook.Worksheets.Add();

// Select sheet2 and sheet3
workbook.Worksheets[new[] { sheet2.Name, sheet3.Name }].Select();        

// Write names of selected sheets to console
foreach (var sheet in workbook.SelectedSheets)
{
    Console.WriteLine(sheet.Name);
}

// Add sheet1 to selected sheets
sheet1.Select(replace: false);

// Write count of selected sheets to console
Console.WriteLine(workbook.SelectedSheets.Count);

//save to an excel file
workbook.Save("selectworksheets.xlsx");

Copy and Move Multiple Worksheets

GcExcel provides Copy, CopyBefore, CopyAfter, Move, MoveBefore, and MoveAfter methods with the IWorksheets interface that allow you to copy or move multiple worksheets at once. You can copy or move worksheets to the end or a specific location within the same workbook or a different workbook, as described below:

Method	Description
Copy	This method copies the sheet collection to the end of the target workbook. If the target workbook is null, the sheet collection will be copied to the current workbook.
CopyBefore	This method copies the sheet collection before the specified sheet. The target worksheet can belong to any workbook.
CopyAfter	This method copies the sheet collection after the specified sheet. The target worksheet can belong to any workbook.
Move	This method moves the sheet collection to the end of the target workbook. If the target workbook is null, the sheet collection will be moved to the current workbook.
MoveBefore	This method moves the sheet collection before the specified sheet. The target worksheet can belong to any workbook.
MoveAfter	This method moves the sheet collection to the specified location after the specified sheet. The target worksheet can belong to any workbook.

Refer to the following example code to copy multiple sheets in the same workbook:

// Initialize Workbook.
Workbook workbook = new Workbook();

// Open the Excel file.
workbook.Open("FlowChartsFile.xlsx");

// Copy the selected sheets to the end of the current workbook.
workbook.Worksheets[new string[] { "FlowChart1", "FlowChart2" }].Copy();

// Save the Excel file.
workbook.Save("CopyMultipleWorksheets.xlsx");

Refer to the following example code to copy multiple sheets to another workbook:

// Initialize Workbook.
Workbook workbook = new Workbook();

// Open the Excel file.
workbook.Open("FlowChartsFile.xlsx");

// Create another Excel file.
Workbook copyWorkbook = new Workbook();

// Copy the selected sheets to the end of the target workbook.
workbook.Worksheets[new string[] { "FlowChart1", "FlowChart2" }].Copy(copyWorkbook);

// Save the Excel file.
copyWorkbook.Save("CopyMultipleWorksheets.xlsx");

Refer to the following example code to move multiple sheets in the same workbook:

// Initialize Workbook.
Workbook workbook = new Workbook();

// Open the Excel file.
workbook.Open("FlowChartsFile.xlsx");

// Copy the selected sheets to the end of the current workbook.
workbook.Worksheets[new string[] { "FlowChart1", "FlowChart2" }].Move();

// Save the Excel file.
workbook.Save("MoveMultipleWorksheets.xlsx");

Refer to the following example code to move multiple sheets to another workbook:

// Initialize Workbook.
Workbook workbook = new Workbook();

// Open the Excel file.
workbook.Open("FlowChartsFile.xlsx");

// Create another Excel file.
Workbook moveWorkbook = new Workbook();

// Copy the selected sheets to the end of the target workbook.
workbook.Worksheets[new string[] { "FlowChart1", "FlowChart2" }].Move(moveWorkbook);

// Save the Excel file.
moveWorkbook.Save("MoveMultipleWorksheets.xlsx");

Note:
All the worksheets in the current workbook cannot be moved to another workbook; this will raise an exception because the workbook must have at least one sheet.
When all the worksheets are moved in the current workbook, nothing will happen as the sheets are added in the same manner.
When copying a worksheet with the same name that exists in the target workbook, the worksheet will be renamed by adding a suffix (x). x represents the index with the same name.

Limitation

A valid license is required to select multiple worksheets in a workbook. Otherwise, the evaluation warning sheet will overwrite the sheet selection and active sheet.