Work with Worksheets

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.

// 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.

// 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.

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.

// 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, DsExcel 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 DsExcel Java, refer to the following example 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.

// 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.

// 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.

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.

// 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.

// 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);

Select Multiple Worksheets

DsExcel 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 getSelectedSheets method of IWorkbook interface. In addition, Excel files with multiple selected worksheets can be loaded, modified and saved back to Excel. DsExcel 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
Workbook workbook = new Workbook();
IWorksheet sheet1 = workbook.getActiveSheet();
IWorksheet sheet2 = workbook.getWorksheets().add();
IWorksheet sheet3 = workbook.getWorksheets().add();

// Select sheet2 and sheet3.
workbook.getWorksheets().get(new String[] { sheet2.getName(), sheet3.getName() }).select();

// Write names of selected sheets to console
for (IWorksheet sheet : workbook.getSelectedSheets()) {
System.out.println(sheet.getName());
}

// Add sheet1 to selected sheets
sheet1.select(false);

// Write count of selected sheets to console
System.out.println(workbook.getSelectedSheets().getCount());

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

Copy and Move Multiple Worksheets

DsExcel 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.getWorksheets().get(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.getWorksheets().get(new String[] {"FlowChart1", "FlowChart2"}).copy(copyWorkbook);

// Save the Excel file.
workbook.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.getWorksheets().get(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.getWorksheets().get(new String[] {"FlowChart1", "FlowChart2"}).move(moveWorkbook);

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

!type=note
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.