Introduction

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

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

1. Create a new Excel file from scratch and add worksheet to it
2. Add worksheets to designer spreadsheets
3. Remove a worksheet from an Excel file using its sheet name
4. 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 Worksheets collection that allows to access 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 that how can we make use of these basic set of APIs.

1. 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 Worksheets collection. 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 Worksheets collection. 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.

Example:

[C#]

 

 

//Instantiating a Workbook object

Workbook workbook = new Workbook();

 

 

//Adding a new worksheet to the Workbook object

workbook.Worksheets.Add();

 

 

//Obtaining the reference of the newly added worksheet by passing its sheet index

Worksheet worksheet = workbook.Worksheets[0];

 

 

//Setting the name of the newly added worksheet

worksheet.Name="My Worksheet";

 

 

//Saving the Excel file

workbook.Save(saveFileDialog1.FileName,FileFormatType.Default);

 

 

[VB.NET]

 

 

'Instantiating a Workbook object

Dim workbook As Workbook = New Workbook()

 

 

'Adding a new worksheet to the Workbook object

workbook.Worksheets.Add()

 

 

'Obtaining the reference of the newly added worksheet by passing its sheet index

Dim worksheet As Worksheet = workbook.Worksheets(0)

 

 

'Setting the name of the newly added worksheet

worksheet.Name = "My Worksheet"

 

 

'Saving the Excel file

workbook.Save(saveFileDialog1.FileName,FileFormatType.Default)

 

 

[JAVA]

//Instantiating a Workbook object

Workbook workbook = new Workbook();

 

 

//Adding a new worksheet to the Workbook object

Worksheets worksheets = workbook.getWorksheets();

Worksheet worksheet = worksheets.addSheet();

 

 

//Setting the name of the newly added worksheet

worksheet.setName("My Worksheet");

 

 

//Saving the Excel file

workbook.save("C:\\book1.xls");

 

2. 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 worksheet to it. A designer spreadsheet can be opened by calling the Open method of the Workbook class.

Example:

[C#]

 

 

//Instantiating a Workbook object

Workbook workbook = new Workbook();

 

 

//Creating a file stream containing the Excel file to be opened

FileStream fstream=new FileStream("C:\\book1.xls",FileMode.Open);

 

 

//Opening the Excel file through the file stream

workbook.Open(fstream);

 

 

//Adding a new worksheet to the Workbook object

workbook.Worksheets.Add();

 

 

//Obtaining the reference of the newly added worksheet by passing its sheet index

Worksheet worksheet = workbook.Worksheets[0];

 

 

//Setting the name of the newly added worksheet

worksheet.Name = "My Worksheet";

 

 

//Saving the Excel file

workbook.Save(saveFileDialog1.FileName,FileFormatType.Default);

 

 

//Closing the file stream to free all resources

fstream.Close();

 

 

[VB.NET]

 

 

'Instantiating a Workbook object

Dim workbook As Workbook = New Workbook()

 

 

'Creating a file stream containing the Excel file to be opened

Dim fstream As FileStream = New FileStream("C:\\book1.xls",FileMode.Open)

 

 

'Opening the Excel file through the file stream

workbook.Open(fstream)

 

 

'Adding a new worksheet to the Workbook object

workbook.Worksheets.Add()

 

 

'Obtaining the reference of the newly added worksheet by passing its sheet index

Dim worksheet As Worksheet = workbook.Worksheets(0)

 

 

'Setting the name of the newly added worksheet

worksheet.Name="My Worksheet"

 

 

'Saving the Excel file

workbook.Save(saveFileDialog1.FileName,FileFormatType.Default)

 

 

'Closing the file stream to free all resources

fstream.Close()

 

 

[JAVA]

 

 

//Instantiating a Workbook object

Workbook workbook = new Workbook();

 

 

//Creating a file stream containing the Excel file to be opened

FileInputStream fstream=new FileInputStream("C:\\book1.xls");

 

 

//Opening the Excel file through the file stream

workbook.open(fstream);

 

 

//Adding a new worksheet to the Workbook object

Worksheets worksheets = workbook.getWorksheets();

Worksheet worksheet = worksheets.addSheet();

 

 

//Setting the name of the newly added worksheet

worksheet.setName("My Worksheet");

 

 

//Saving the Excel file

workbook.save("C:\\book1.xls");

 

 

//Closing the file stream to free all resources

fstream.close();

 

3. 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 Worksheets collection. We can pass sheet name to the RemoveAt method to remove a specific worksheet.

Example:

[C#]

 

 

//Instantiating a Workbook object

Workbook workbook = new Workbook();

 

 

//Creating a file stream containing the Excel file to be opened

FileStream fstream=new FileStream("C:\\book1.xls",FileMode.Open);

 

 

//Opening the Excel file through the file stream

workbook.Open(fstream);

 

 

//Removing a worksheet using its sheet name

workbook.Worksheets.RemoveAt("Sheet1");

 

 

[VB.NET]

 

 

'Instantiating a Workbook object

Dim workbook As Workbook = New Workbook()

 

 

'Creating a file stream containing the Excel file to be opened

Dim fstream As FileStream = New FileStream("C:\\book1.xls",FileMode.Open)

 

 

'Opening the Excel file through the file stream

workbook.Open(fstream)

 

 

'Removing a worksheet using its sheet name

workbook.Worksheets.RemoveAt("Sheet1")

 

 

[JAVA]

 

 

//Instantiating a Workbook object

Workbook workbook = new Workbook();

 

 

//Creating a file stream containing the Excel file to be opened

FileInputStream fstream = new FileInputStream("C:\\book1.xls");

 

 

//Opening the Excel file through the file stream

workbook.open(fstream);

 

 

//Removing a worksheet using its sheet name

worksheets.removeSheet("Sheet1");

 

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

Example:

[C#]

 

 

//Instantiating a Workbook object

Workbook workbook = new Workbook();

 

 

//Creating a file stream containing the Excel file to be opened

FileStream fstream=new FileStream("C:\\book1.xls",FileMode.Open);

 

 

//Opening the Excel file through the file stream

workbook.Open(fstream);

 

 

//Removing a worksheet using its sheet index

workbook.Worksheets.RemoveAt(0);

 

 

[VB.NET]

 

 

'Instantiating a Workbook object

Dim workbook As Workbook = New Workbook()

 

 

'Creating a file stream containing the Excel file to be opened

Dim fstream As FileStream = New FileStream("C:\\book1.xls",FileMode.Open)

 

 

'Opening the Excel file through the file stream

workbook.Open(fstream)

 

 

'Removing a worksheet using its sheet index

workbook.Worksheets.RemoveAt(0)

 

 

[JAVA]

 

 

//Instantiating a Workbook object

Workbook workbook = new Workbook();

 

 

//Creating a file stream containing the Excel file to be opened

FileInputStream fstream=new FileInputStream("C:\\book1.xls");

 

 

//Opening the Excel file through the file stream

workbook.open(fstream);

 

//Removing a worksheet using its sheet index

worksheets.removeSheet(0);