Manage Worksheets

Developers can easily create and manage worksheets in their Excel files programmatically using the flexible API of Aspose.Cells. In this topic, we will discuss some approaches to add and remove worksheets in Excel files.

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.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(AddingWorksheetstoNewExcelFile.class) + "worksheets/";

// Instantiating a Workbook object
Workbook workbook = new Workbook();

// Adding a new worksheet to the Workbook object
WorksheetCollection worksheets = workbook.getWorksheets();

int sheetIndex = worksheets.add();
Worksheet worksheet = worksheets.get(sheetIndex);

// Setting the name of the newly added worksheet
worksheet.setName("My Worksheet");

// Saving the Excel file
workbook.save(dataDir + "AWToNewExcelFile_out.xls");

// Print Message
System.out.println("Sheet added successfully.");

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.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(AddingWorksheetstoDesignerSpreadsheet.class) + "worksheets/";

// Creating a file stream containing the Excel file to be opened
FileInputStream fstream = new FileInputStream(dataDir + "book.xls");

// Instantiating a Workbook object with the stream
Workbook workbook = new Workbook(fstream);

// Adding a new worksheet to the Workbook object
WorksheetCollection worksheets = workbook.getWorksheets();
int sheetIndex = worksheets.add();
Worksheet worksheet = worksheets.get(sheetIndex);

// Setting the name of the newly added worksheet
worksheet.setName("My Worksheet");

// Saving the Excel file
workbook.save(dataDir + "AWToDesignerSpreadsheet_out.xls");

// Closing the file stream to free all resources
fstream.close();

// Print Message
System.out.println("Sheet added successfully.");

Accessing Worksheets using Sheet Name

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

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(AccessingWorksheetsusingSheetName.class) + "worksheets/";
String filePath = dataDir + "book1.xlsx";

// Creating a file stream containing the Excel file to be opened
FileInputStream fstream = new FileInputStream(filePath);

// Instantiating a Workbook object with the stream
Workbook workbook = new Workbook(fstream);

// Accessing a worksheet using its sheet name
Worksheet worksheet = workbook.getWorksheets().get("Sheet1");
Cell cell = worksheet.getCells().get(0, 0);

// Print Message
System.out.println(cell.getValue());

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.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(RemovingWorksheetsusingSheetName.class) + "worksheets/";

// Creating a file stream containing the Excel file to be opened
FileInputStream fstream = new FileInputStream(dataDir + "book.xls");

// Instantiating a Workbook object with the stream
Workbook workbook = new Workbook(fstream);

// Removing a worksheet using its sheet name
workbook.getWorksheets().removeAt("Sheet1");

// Saving the Excel file
workbook.save(dataDir + "RemovingWorksheetsusingSheetName_out.xls");

// Closing the file stream to free all resources
fstream.close();

// Print Message
System.out.println("Sheet removed successfully.");

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.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(RemovingWorksheetsusingSheetIndex.class) + "worksheets/";

// Creating a file stream containing the Excel file to be opened
FileInputStream fstream = new FileInputStream(dataDir + "book.xls");

// Instantiating a Workbook object with the stream
Workbook workbook = new Workbook(fstream);

// Removing a worksheet using its sheet index
workbook.getWorksheets().removeAt(0);

// Saving the Excel file
workbook.save(dataDir + "RWUsingSheetIndex_out.xls");

// Closing the file stream to free all resources
fstream.close();

// Print Message
System.out.println("Sheet removed successfully.");

Advance topics

• Activating Sheets and Activating a Cell in Worksheet
• Copy and Move Worksheets Within and Between Workbooks
• Copying and Moving Worksheets
• Count number of cells in the Worksheet
• Detecting Empty Worksheets
• Find if the Worksheet is Dialog Sheet
• Get worksheet unique id
• Insert Background Image to Excel
• Create, Manipulate or Remove Scenarios from Worksheets
• Managing Page Breaks
• Page Setup Features
• Update references in other worksheets while deleting blank columns and rows in a worksheet
• Utilize Sheet.SheetId property of OpenXml using Aspose.Cells
• Working with Background in ODS Files
• Worksheet Views