Documents for Excel .NET Edition | GCDocuments
Features / Worksheet / Work with Worksheets
In This Topic
    Work with Worksheets
    In This Topic

    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.

    C#
    Copy Code
    // 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.

    C#
    Copy Code
    //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.

    C#
    Copy Code
    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.

    C#
    Copy Code
    //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.

    C#
    Copy Code
    //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.

    C#
    Copy Code
    // 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.

    C#
    Copy Code
    // 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.

    C#
    Copy Code
    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.

    C#
    Copy Code
    // 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.

    C#
    Copy Code
    // 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);
    See Also