How can you Insert / Delete rows programmatically in an Excel Worksheet?

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 simplest set of APIs that can easily perform your desired task within no time.

There are two methods that Aspose.Cells offers in this regard 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.

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

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

Pallavi
Guest

Getting the exception:
“To prevent possible loss of data, Excel cannot shift nonblank cells off of the worksheet. Select another location in which to insert new cells, or delete data from the end of your worksheet.”
Please help me come over. I need to insert row before some content…

cop
Guest

i’m getting same exception.
please help!

wpDiscuz