Documents for Excel, Java Edition Documentation
Features / Worksheet / Work with Worksheets
In This Topic
    Work with Worksheets
    In This Topic

    While managing worksheets, you can execute the following operations to accomplish essential spreadsheet tasks.

    Access the default worksheet

    By default, an empty worksheet with the name Sheet1 is automatically added in the workbook when a new workbook is created. For every workbook, only one default worksheet is added.

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

    Java
    Copy Code
    // Fetch the default worksheet
    IWorksheet worksheet = workbook.getWorksheets().get(0);

    Add multiple worksheets

    You can add one more worksheets before or after a specific sheet in the workbook.

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

    Java
    Copy Code
    // Add a worksheet to the workbook. 
    IWorksheet worksheet1 = workbook.getWorksheets().add();
            
    // Add a new worksheet before worksheet1 and reset its name
    IWorksheet worksheet2 = workbook.getWorksheets().addBefore(worksheet1);
    worksheet2.setName("MySheet2");
            
    // Add a sheet after worksheet2
    workbook.getWorksheets().addAfter(workbook.getWorksheets().get(1));

    Activate a worksheet

    In a workbook with multiple worksheets, you may want to set the current sheet or any particular worksheet as workbook's active sheet. This can be done using the activate method of the IWorksheet interface.

    Refer to the following example code in order to activate a worksheet in a workbook.

    Java
    Copy Code
    IWorksheet worksheet4 = workbook.getWorksheets().add();
            
    // Activate the newly created sheet
    worksheet4.activate();

    Access a worksheet

    A workbook stores all the worksheets in the Worksheets collection.

    In order to access a particular worksheet within a workbook, refer to the following example code.

    Java
    Copy Code
    // Accessing a worksheet using sheet index.
    IWorksheet worksheet = workbook.getWorksheets().get(0);
            
    // Accessing a worksheet using sheet name as "Sheet1".
    IWorksheet worksheet1 = workbook.getWorksheets().get("Sheet1");

    Protect a worksheet

    In order to ensure security and integrity of the data in the workbook, GcExcel Java enables users to protect worksheets via converting it into a read-only sheet. A worksheet can be prevented from modification either by using a password or without it.

    Protect worksheet from modification without password

    The IProtectionSettings interface provides the methods to explicitly configure the protection settings in a worksheet. In case you want to remove protection, you can unprotect your worksheet by setting the protection field to false.

    To protect or unprotect a worksheet in GcExcel Java, refer to the following example code.

    Java
    Copy Code
    // Protect Worksheet
    worksheet.setProtection(true);
    worksheet.getProtectionSettings().setAllowInsertingColumns(true);
            
    // Unprotect worksheet
    IWorksheet worksheet1 = workbook.getWorksheets().add();
    worksheet1.setProtection(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.

    Java
    Copy Code
    // Initialize workbook
     Workbook workbook = new Workbook();
    // Fetch default worksheet 
     IWorksheet worksheet = workbook.getWorksheets().get(0);
    // Data
     Object data = new Object[][] { { "Name", "City", "Sex", "Weight", "Height", "Age" },
            { "Bob", "NewYork", "male", 80, 180, 56 }, { "Betty", "NewYork", "female", 72, 168, 45 },
            { "Gary", "NewYork", "male", 71, 179, 50 }, { "Hunk", "Washington", "male", 80, 171, 59 },
            { "Cherry", "Washington", "female", 58, 161, 34 }, { "Coco", "Virginia", "female", 58, 181, 45 },
            { "Lance", "Chicago", "female", 49, 160, 57 }, { "Eva", "Washington", "female", 71, 180, 81 } };
            
      // Set data
      worksheet.getRange("A1:G9").setValue(data);
    //Protects the workbook with password so that other users cannot view hidden worksheets, add, move, delete, hide, or rename worksheets.
      worksheet.protect("Ygs_87@ytr");
    // Save workbook to xlsx
      workbook.save("ProtectWorksheet.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.

    Java
    Copy Code
    // Initialize workbook
    Workbook workbook = new Workbook();
    // Fetch default worksheet 
     IWorksheet worksheet = workbook.getWorksheets().get(0);
    // Data
     Object data = new Object[][] { { "Name", "City", "Sex", "Weight", "Height", "Age" },
            { "Bob", "NewYork", "male", 80, 180, 56 }, { "Betty", "NewYork", "female", 72, 168, 45 },
            { "Gary", "NewYork", "male", 71, 179, 50 }, { "Hunk", "Washington", "male", 80, 171, 59 },
            { "Cherry", "Washington", "female", 58, 161, 34 }, { "Coco", "Virginia", "female", 58, 181, 45 },
            { "Lance", "Chicago", "female", 49, 160, 57 }, { "Eva", "Washington", "female", 71, 180, 81 } };
      // Set data        
      worksheet.getRange("A1:G9").setValue(data);
      worksheet.protect("Ygs_87@ytr");
      //Removes the above protection from the workbook.
      worksheet.unprotect("Ygs_87@ytr");
      // Save workbook to xlsx
      workbook.save("UnProtectWorksheet.xlsx", SaveFileFormat.Xlsx);

    Delete Worksheet

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

    To delete a specific sheet from the workbook, refer to the following example code.

    Java
    Copy Code
    IWorksheet worksheet5 = workbook.getWorksheets().add();
            
    // Workbook must contain at least one visible worksheet, if delete the one visible worksheet, it will throw exception.
    worksheet5.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.

    Java
    Copy Code
    // Initialize workbook
    Workbook workbook = new Workbook();
            
    // Fetch default worksheet
    IWorksheet worksheet = workbook.getWorksheets().get(0);
    Object data = new Object[][] { 
    { "Name", "City", "Birthday", "Sex", "Weight", "Height", "Age" },
    { "Bob", "newyork", new GregorianCalendar(1968, 6, 8), "male", 80, 180, 56 },
    { "Betty", "newyork", new GregorianCalendar(1972, 7, 3), "female", 72, 168, 45 },
    { "Gary", "NewYork", new GregorianCalendar(1964, 3, 2), "male", 71, 179, 50 },
    { "Hunk", "Washington", new GregorianCalendar(1972, 8, 8), "male", 80, 171, 59 },
    { "Cherry", "Washington", new GregorianCalendar(1986, 2, 2), "female", 58, 161, 34 },
    { "Coco", "Virginia", new GregorianCalendar(1982, 12, 12), "female", 58, 181, 45 },
    { "Lance", "Chicago", new GregorianCalendar(1962, 3, 12), "female", 49, 160, 57 },
    { "Eva", "Washington", new GregorianCalendar(1993, 2, 5), "female", 71, 180, 81 } };
            
    // Set data
    worksheet.getRange("A1:G9").setValue(data);
    
    // Copy the active sheet to the end of current workbook
    IWorksheet copy_worksheet = worksheet.copy();
    copy_worksheet.setName("Copy of " + worksheet.getName());
    
    // 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.

    Java
    Copy Code
    // Create a new workbook
    Workbook workbook = new Workbook();
            
    // Create another source_workbook
    Workbook source_workbook = new Workbook();
            
    // Fetch the active worksheet
    IWorksheet worksheet = source_workbook.getActiveSheet();
    Object data = new Object[][] { 
    { "Name", "City", "Birthday", "Sex", "Weight", "Height", "Age" },
    { "Bob", "newyork", new GregorianCalendar(1968, 6, 8), "male", 80, 180, 56 },
    { "Betty", "newyork", new GregorianCalendar(1972, 7, 3), "female", 72, 168, 45 },
    { "Gary", "NewYork", new GregorianCalendar(1964, 3, 2), "male", 71, 179, 50 },
    { "Hunk", "Washington", new GregorianCalendar(1972, 8, 8), "male", 80, 171, 59 },
    { "Cherry", "Washington", new GregorianCalendar(1986, 2, 2), "female", 58, 161, 34 },
    { "Coco", "Virginia", new GregorianCalendar(1982, 12, 12), "female", 58, 181, 45 },
    { "Lance", "Chicago", new GregorianCalendar(1962, 3, 12), "female", 49, 160, 57 },
    { "Eva", "Washington", new GregorianCalendar(1993, 2, 5), "female", 71, 180, 81 } };
            
    // Set data
    worksheet.getRange("A1:G9").setValue(data);
    
    /* Copy content of active sheet from source_workbook to the current workbook
       before the first sheet */
    IWorksheet copy_worksheet = worksheet.copyBefore(workbook.getWorksheets().get(0));
    copy_worksheet.setName("Copy of Sheet1");
    copy_worksheet.activate();
    
    // Saving workbook to xlsx
    workbook.save("CopyWorkSheetBetweenWorkBooks.xlsx", SaveFileFormat.Xlsx);
    See Also