How to Insert New Rows and Columns
The IronXL library offers a convenient way to insert single or multiple rows and columns in C# code without using Office Interop.
How to Insert New Rows and Columns in Excel
- Download the C# library for inserting new rows and columns in Excel
- Use the InsertRow and InsertRows methods to insert new rows
- Use the InsertColumn and InsertColumns methods to insert new columns
- Add data to the newly inserted rows and columns
- Export the edited Excel file to various file types
Get started with IronXL
Start using IronXL in your project today with a free trial.
Insert a New Row Example
Use the InsertRow and InsertRows methods to add new rows to the spreadsheet. These methods enable you to insert rows at a specific index position.
Please note
:path=/static-assets/excel/content-code-examples/how-to/add-rows-columns-rows.csusing IronXL;
// Load an existing spreadsheet from a specified path
// This will open the file "sample.xlsx" and parse it into a WorkBook object
WorkBook workBook = WorkBook.Load("sample.xlsx");
// Access the default worksheet of the loaded workbook
// This allows operations on the default sheet, typically the first sheet
WorkSheet workSheet = workBook.DefaultWorkSheet;
// Insert a new row before the existing row 2
// Since row indexing is zero-based; this will insert before the second row in human terms
workSheet.InsertRow(1);
// Insert three new rows starting after the existing row 3
// Inserts after the third row, effectively adding rows 4, 5, and 6
// Again, this uses zero-based indexing
workSheet.InsertRows(4, 3);
// Save the modified workbook to a new file
// This writes the changes to a new file named "addRow.xlsx"
workBook.SaveAs("addRow.xlsx");Imports IronXL
' Load an existing spreadsheet from a specified path
' This will open the file "sample.xlsx" and parse it into a WorkBook object
Private workBook As WorkBook = WorkBook.Load("sample.xlsx")
' Access the default worksheet of the loaded workbook
' This allows operations on the default sheet, typically the first sheet
Private workSheet As WorkSheet = workBook.DefaultWorkSheet
' Insert a new row before the existing row 2
' Since row indexing is zero-based; this will insert before the second row in human terms
workSheet.InsertRow(1)
' Insert three new rows starting after the existing row 3
' Inserts after the third row, effectively adding rows 4, 5, and 6
' Again, this uses zero-based indexing
workSheet.InsertRows(4, 3)
' Save the modified workbook to a new file
' This writes the changes to a new file named "addRow.xlsx"
workBook.SaveAs("addRow.xlsx")
Remove a Row Example
To remove a row from the spreadsheet, you can use the GetRow method to select the desired row and then use the RemoveRow method on the selected row.
Please note
:path=/static-assets/excel/content-code-examples/how-to/add-rows-columns-remove-row.csusing IronXL;
// The WorkBook class from IronXL is used to manipulate Excel files.
// Here, we load an existing Excel file named 'sample.xlsx' from the current directory.
WorkBook workBook = WorkBook.Load("sample.xlsx");
// The DefaultWorkSheet property accesses the first worksheet of the loaded workbook.
WorkSheet workSheet = workBook.DefaultWorkSheet;
// Removing row 5 from the worksheet
// IronXL uses zero-based indexing. Thus, workSheet.GetRow(4) refers to the 5th row because indexing starts at 0.
workSheet.GetRow(4).Remove();
// Save the changes we made to a new Excel file.
// The SaveAs method exports the current state of the workbook to 'removeRow.xlsx'.
workBook.SaveAs("removeRow.xlsx");Imports IronXL
' The WorkBook class from IronXL is used to manipulate Excel files.
' Here, we load an existing Excel file named 'sample.xlsx' from the current directory.
Private workBook As WorkBook = WorkBook.Load("sample.xlsx")
' The DefaultWorkSheet property accesses the first worksheet of the loaded workbook.
Private workSheet As WorkSheet = workBook.DefaultWorkSheet
' Removing row 5 from the worksheet
' IronXL uses zero-based indexing. Thus, workSheet.GetRow(4) refers to the 5th row because indexing starts at 0.
workSheet.GetRow(4).Remove()
' Save the changes we made to a new Excel file.
' The SaveAs method exports the current state of the workbook to 'removeRow.xlsx'.
workBook.SaveAs("removeRow.xlsx")
Insert a New Column Example
To add new column(s) before a specific index position in the table, you can utilize the InsertColumn and InsertColumns methods.
Please note
Before proceeding
System.InvalidOperationException with the message 'Sequence contains no elements'.:path=/static-assets/excel/content-code-examples/how-to/add-rows-columns-columns.csusing IronXL;
// Load an existing spreadsheet into a WorkBook object
// The "sample.xlsx" should be located in the same directory as the executable, or provide a full path
WorkBook workBook = WorkBook.Load("sample.xlsx");
// Access the default worksheet of the loaded workbook
WorkSheet workSheet = workBook.DefaultWorkSheet;
// Insert a new column before the current column A
// '0' is the index for the first column, so this inserts before the first column (A)
workSheet.InsertColumn(0);
// Insert two new columns after column B
// Column indices are zero-based, column B is index 1
// To insert after column B, we begin inserting at index 2
workSheet.InsertColumns(2, 2);
// Save the modified workbook to a new Excel file
workBook.SaveAs("addColumn.xlsx");Imports IronXL
' Load an existing spreadsheet into a WorkBook object
' The "sample.xlsx" should be located in the same directory as the executable, or provide a full path
Private workBook As WorkBook = WorkBook.Load("sample.xlsx")
' Access the default worksheet of the loaded workbook
Private workSheet As WorkSheet = workBook.DefaultWorkSheet
' Insert a new column before the current column A
' '0' is the index for the first column, so this inserts before the first column (A)
workSheet.InsertColumn(0)
' Insert two new columns after column B
' Column indices are zero-based, column B is index 1
' To insert after column B, we begin inserting at index 2
workSheet.InsertColumns(2, 2)
' Save the modified workbook to a new Excel file
workBook.SaveAs("addColumn.xlsx")
Tips
Frequently Asked Questions
How can I insert new rows in Excel using IronXL?
To insert new rows in Excel using IronXL, you can use the InsertRow and InsertRows methods. These methods allow you to add rows at a specific index position in your spreadsheet.
What methods are used to insert columns in an Excel sheet with IronXL?
To insert columns in an Excel sheet using IronXL, you can utilize the InsertColumn and InsertColumns methods. These methods let you add columns before a specific index position.
Is it possible to remove a row from an Excel spreadsheet using IronXL?
Yes, you can remove a row from an Excel spreadsheet using IronXL by selecting the desired row with the GetRow method and then using the RemoveRow method on the selected row.
Can I remove columns in Excel with IronXL?
Currently, removing columns is not supported in IronXL. However, you can insert new columns using the InsertColumn and InsertColumns methods.
Are there any precautions when inserting rows or columns in Excel using IronXL?
Yes, inserting rows or columns directly in certain areas, like the filter row or within a table range, may cause conflicts in the Excel file. You may need to run Excel repair to view the spreadsheet properly.
What happens if I try to insert a new column into an empty sheet using IronXL?
Inserting a new column into a completely empty sheet will result in a System.InvalidOperationException with the message 'Sequence contains no elements'.
How do I save changes to an Excel workbook after modifying it with IronXL?
After making modifications to an Excel workbook using IronXL, you can save the changes using the SaveAs method, specifying the file path for the updated workbook.
What indexing system does IronXL use for rows and columns?
IronXL uses zero-based indexing for row and column positions, meaning the index starts at 0.
