Wednesday, 20 June 2012 03:01

Insert Excel Row or Rows in C#, VB.NET

Written by  support iceblue
Rate this item
(0 votes)

This section is designed to introduce developers a solution on how to insert Excel row or rows in C#, VB.NET via this Excel component Spire.XLS for .NET.

Spire.XLS for .NET provides one method for you to insert multiple Excel rows: Spire.Xls.Worksheet.InsertRow(int rowIndex, int rowCount). Here there are two parameters: one is rowIndex that decides at which new row should be inserted; the other parameter rowCount is the number of rows to be inserted. When you insert only one excel row, the second parameter rowCount should be avoided. So the method should be Spire.Xls.Worksheet.InsertRow(int rowIndex). If you are not familiar with Spire.XLS for .NET, I hope below short description can help you know more about it.

Spire.XLS for .NET is an Excel library that enables developers to perform a wide range of processing tasks such as create, read, edit and handle Excel files without MS Excel. Here you can download Spire.XLS for .NET. After you install it on system, you can start your Visual Studio and create a new project on eith C#/VB.NET Windows Forms Application and Console Application. Please do not forget to add Spire.XLS.dll as reference. The default path is “..\Spire.XLS\Bin\NET4.0\ Spire.XLS.dll”. Below shows the detail codes in my project.

[C#]
//initialize a new instance of Workbook
Workbook workbook = new Workbook();
//open a template Excel file
workbook.LoadFromFile(@"..\excel.xlsx");
//get the first sheet 
Worksheet worksheet = workbook.Worksheets[0];
//insert new row at Row3
worksheet.InsertRow(3);
//copy row 1 data A1:E1 to row 3 
worksheet.Copy(worksheet.Range["A1:E1"], worksheet.Range["A3:E3"], true);
//insert new rows at Row 5 and Row 6
worksheet.InsertRow(5, 2);
//copy row 11 and row 12 data A11:E12 to row5 and row 6 A5:A6
worksheet.Copy(worksheet.Range["A11:E12"], worksheet.Range["A5:E6"], true);
//save Excel file
workbook.SaveToFile("sample.xlsx", ExcelVersion.Version2010);
//launch the Excel file
System.Diagnostics.Process.Start(workbook.FileName);
[VB.NET]
'initialize a new instance of Workbook
Dim workbook As New Workbook()
'open a template Excel file
workbook.LoadFromFile("..\excel.xlsx")
'get the first sheet 
Dim worksheet As Worksheet = workbook.Worksheets(0)
'insert new row at Row 3
worksheet.InsertRow(3)
'copy row 1 data A1:E1 to row 3 
worksheet.Copy(worksheet.Range("A1:E1"), worksheet.Range("A3:E3"), True)
'insert new rows at Row 5 and Row 6
worksheet.InsertRow(5, 2)
'copy row 11 and row 12 data A11:E12 to row5 and row 6 A5:A6
worksheet.Copy(worksheet.Range("A11:E12"), worksheet.Range("A5:E6"), True)
'save Excel file
workbook.SaveToFile("sample.xlsx", ExcelVersion.Version2010)
'launch the Excel file
System.Diagnostics.Process.Start(workbook.FileName)

Note: Above code fills data in newly inserted row by copying other row data, if you want to directly export row/rows from database to new Excel row or rows, and set data format, you can click to know other solution: Add Row in Excel Workbook.

Task Result:

After performing above code in my Visual Studio, I have added one row at Row 3 and copy the data from the first row to Row 3. I also add two rows at Row 5 and Row 6 and copy data of A11:A12 to A5:E6. Please compare the original Excel file and the output Excel file as below:

Insert Excel Rows

Original Excel File

Insert Excel Rows

Insert Excel Row/Rows

In this section, I have introduced a solution on how to insert Excel row/rows in C#, VB.NET by Spire.XLS for .NET. As you see above, the solution provided by Spire.XLS for .NET is very simple. Nothing more than the fact can prove the high efficiency of this Excel library. If you have any queries, comments and advice, you can visit E-iceblue Forum and put them there. We will give prompt reply.

Spire.XLS for .NET is an Excel component which is fully written in C#. It supports VB.NET also. You can use it on .NET Framework 2.0 and above.

More about this .NET Excel Library

Additional Info

  • tutorial_title: Insert Excel Row or Rows
Last modified on Wednesday, 08 January 2014 02:23