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.
//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; //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);
'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.
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:
Original Excel File
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.