Manage Worksheets

Managing worksheets using Aspose.Cells is as easy as ABC. In this section, we will describe how can we:

  1. Create a new Excel file from scratch and add a worksheet to it
  2. Add worksheets to designer spreadsheets
  3. Accessing Worksheets using Sheet Name
  4. Remove a worksheet from an Excel file using its sheet name
  5. Remove a worksheet from an Excel file using its sheet index

Aspose.Cells provides a class, Workbook that represents an Excel file. Workbook class contains a WorksheetCollection that allows access to each worksheet in the Excel file.

A worksheet is represented by the Worksheet class. Worksheet class provides a wide range of properties and methods to manage a worksheet. Let’s see how can we make use of these basic set of APIs.

Adding Worksheets to a New Excel File

To create a new Excel file programmatically, developers would need to create an object of Workbook class that represents an Excel file. Then developers can call add method of the WorksheetCollection. When we call add method, an empty worksheet is added to the Excel file automatically, which can be referenced by passing the sheet index of the newly added worksheet to the WorksheetCollection. After the worksheet reference is obtained, developers can work on their worksheets according to their requirements. After the work is done on the worksheets, developers can save their newly created Excel file with new worksheets by calling the save method of the Workbook class.

Adding Worksheets to a Designer Spreadsheet

The process of adding worksheets to a designer spreadsheet is entirely same as that of the above approach except that the Excel file is already created and we need to open that Excel file first before adding a worksheet to it. A designer spreadsheet can be opened by passing the file path or stream while initializing the Workbook class.

Accessing Worksheets using Sheet Name

Developers may access or get any worksheet by specifying its name or index.

Removing Worksheets using Sheet Name

Sometimes, developers may need to remove worksheets from existing Excel files and that task can be performed by calling the removeAt method of the WorksheetCollection collection. We can pass the sheet name to the removeAt method to remove a specific worksheet.

Removing Worksheets using Sheet Index

The above approach of removing worksheets works well if developers already know the sheet names of the worksheets to be deleted. But, what if you don’t know the sheet name of the worksheet that you want to remove from your Excel file?

Well, in such circumstances, developers can use an overloaded version of removeAt method that takes the sheet index of the worksheet instead of its sheet name.

Advance topics