Dev Direct Solution Center

Insert / Delete rows programmatically in an Excel Worksheet

  For a copy of the sample project please click here to download and install the product evaluation.

Introduction

There are two methods that Aspose.Cells offers i.e., InsertRows and DeleteRows, these two methods are optimized related performance and efficient enough to do the job very quickly. So if you are in need to insert some sets of rows or remove a number of rows, it is recommended that you should always use InsertRows and DeleteRows methods instead of repeatedly using InsertRow and DeleteRow methods in a loop.

Detail

Do you wish to have worksheets that can be easily updated to keep up with your constantly changing business needs? Using Aspose.Cells APIs, you can quickly insert new rows, columns, cells, and worksheets — and just as easily delete them — calling a few methods only and your task is performed with excellence. While you are creating a new worksheet from scratch or working with an existing worksheet, you might need to add extra rows / columns into your worksheet to accommodate more data or for your specific need. Alternatively, it can also be required to delete rows / columns from specified positions / locations in the worksheet. To fulfill these requirements, Aspose.Cells provides the simplest set of APIs that can easily perform the desired task in no time. There are two methods that Aspose.Cells offers in this regard i.e., InsertRows and DeleteRows, these two methods are performance optimized and efficient enough to do the job very quickly.

So if you need to insert some sets of rows or remove a number of rows, we recommended that you always use InsertRows and DeleteRows methods instead of repeatedly using the InsertRow and DeleteRow methods in a loop.

Aspose.Cells works in the same way as Microsoft Excel does. When rows or columns are added, the contents in the worksheet are shifted downwards or to the right but if rows or columns are removed, the contents in the worksheet will be shifted upwards or to the left. Moreover, the references in other worksheets are updated accordingly upon insertion / deletion of rows.

The following example shows the usage of InsertRows and DeleteRows methods

[C#]

//Instantiate a Workbook object.
Workbook workbook = new Workbook();
//Load a template file.
workbook.Open("d:\\test\\MyBook.xls");
//Get the first worksheet in the book.
Worksheet sheet = workbook.Worksheets[0];
//Insert 10 rows at row index 2 (insertion starts at 3rd row)
sheet.Cells.InsertRows(2, 10);
//Delete 5 rows now. (8th row - 12th row)
sheet.Cells.DeleteRows(7, 5);
//Save the excel file.
workbook.Save("d:\\test\\out_MyBook.xls");

[VB]

'Instantiate a Workbook object.
Dim workbook As Workbook = New Workbook
'Load a template file.
workbook.Open("d:\test\MyBook.xls")
'Get the first worksheet in the book.
Dim sheet As Worksheet = workbook.Worksheets(0)
'Insert 10 rows at row index 2 (insertion starts at 3rd row)
sheet.Cells.InsertRows(2, 10)
'Delete 5 rows now. (8th row - 12th row)
sheet.Cells.DeleteRows(7, 5)
'Save the excel file.
workbook.Save("d:\test\out_MyBook.xls")

[Java]

//Instantiate a Workbook object.
Workbook workbook = new Workbook();
//Load a template file.
workbook.open("d:\\test\\MyBook.xls");
//Get the first worksheet in the book.
Worksheet sheet = workbook.getWorksheets().getSheet(0);
//Insert 10 rows at row index 2 (insertion starts at 3rd row)
sheet.getCells().insertRows(2, 10);
//Delete 5 rows now. (8th row - 12th row)
sheet.getCells().deleteRows(7, 5,true);
//Save the excel file.
workbook.save("d:\\test\\out_MyBook.xls");

About Aspose.Cells for .NET

- Download evaluation version of Aspose.Cells for .NET.
- Online documentation of Aspose.Cells for .NET.
- Demos of Aspose.Cells for .NET.
- Post your technical questions/queries to Aspose.Cells for .NET Forum.

Contact Information
Suite 119, 272 Victoria Avenue
Chatswood, NSW, 2067
Australia
Aspose - The .NET and Java component publisher
sales@aspose.com
Phone: 888.277.6734
Fax: 866.810.9465l