"Excel: Add" in C#

When developing applications, you need to be able to manage data without Excel spreadsheets but still be able to communicate with the files. For example, you may need to insert new rows or columns in an existing Excel Spreadsheet programmatically. Using the functions for the umbrella concept "Excel: Add" in C#, we can add all these and more. See the examples below.

Add Row Last Position
using IronXL;
static void Main(string[] args)
{
    WorkBook wb = WorkBook.Load("sample.xlsx");
    WorkSheet ws = wb.GetWorkSheet("Sheet1");
    int i = ws.Rows.Count() + 1;
    ws["A" + i].Value = "New Row";
    ws["B" + i].Value = "New Row";
    ws["C" + i].Value = "New Row";
    ws["D" + i].Value = "New Row";
    ws["E" + i].Value = "New Row";
    wb.SaveAs("sample.xlsx");
}
Imports IronXL
Shared Sub Main(ByVal args() As String)
	Dim wb As WorkBook = WorkBook.Load("sample.xlsx")
	Dim ws As WorkSheet = wb.GetWorkSheet("Sheet1")
	Dim i As Integer = ws.Rows.Count() + 1
	ws("A" & i).Value = "New Row"
	ws("B" & i).Value = "New Row"
	ws("C" & i).Value = "New Row"
	ws("D" & i).Value = "New Row"
	ws("E" & i).Value = "New Row"
	wb.SaveAs("sample.xlsx")
End Sub
Jump to Article
Add Row First Position
using IronXL;
static void Main(string[] args)
{
    WorkBook wb = WorkBook.Load("sample.xlsx");
    WorkSheet ws = wb.GetWorkSheet("Sheet1");
    int j = ws.Rows.Count() + 1;
    for (int i = 1; i <= ws.Rows.Count(); i++)
    {
        if (j == 0) 
        {
            ws.Rows[0].Value = "new row"; 
            break; 
        };
        ws["A" + (j + 1)].Value = ws["A" + j].Value; 
        ws["B" + (j + 1)].Value = ws["B" + j].Value;
        ws["C" + (j + 1)].Value = ws["C" + j].Value;
        ws["D" + (j + 1)].Value = ws["D" + j].Value;
        ws["E" + (j + 1)].Value = ws["E" + j].Value;
        j = j - 1;
    }
 wb.SaveAs("sample.xlsx"); 
}
Imports IronXL
Shared Sub Main(ByVal args() As String)
	Dim wb As WorkBook = WorkBook.Load("sample.xlsx")
	Dim ws As WorkSheet = wb.GetWorkSheet("Sheet1")
	Dim j As Integer = ws.Rows.Count() + 1
	For i As Integer = 1 To ws.Rows.Count()
		If j = 0 Then
			ws.Rows(0).Value = "new row"
			Exit For
		End If
		ws("A" & (j + 1)).Value = ws("A" & j).Value
		ws("B" & (j + 1)).Value = ws("B" & j).Value
		ws("C" & (j + 1)).Value = ws("C" & j).Value
		ws("D" & (j + 1)).Value = ws("D" & j).Value
		ws("E" & (j + 1)).Value = ws("E" & j).Value
		j = j - 1
	Next i
 wb.SaveAs("sample.xlsx")
End Sub
Jump to Article
Add Column
using IronXL;
static void Main(string[] args)
{  
    WorkBook wb = WorkBook.Load("sample.xlsx");
    WorkSheet ws = wb.GetWorkSheet("Sheet1");
    for (int i = 1; i <= ws.Rows.Count(); i++)
    {
        ws["F" + i].Value = ws["E" + i].Value;
        ws["E" + i].Value = ws["D" + i].Value;
        ws["D" + i].Value = ws["C" + i].Value;
        ws["C" + i].Value = ws["B" + i].Value;
        ws["B" + i].Value = ws["A" + i].Value;
        ws["A" + i].Value = "New Column Added";
    }
    wb.SaveAs("sample.xlsx");
}
Imports IronXL
Shared Sub Main(ByVal args() As String)
	Dim wb As WorkBook = WorkBook.Load("sample.xlsx")
	Dim ws As WorkSheet = wb.GetWorkSheet("Sheet1")
	For i As Integer = 1 To ws.Rows.Count()
		ws("F" & i).Value = ws("E" & i).Value
		ws("E" & i).Value = ws("D" & i).Value
		ws("D" & i).Value = ws("C" & i).Value
		ws("C" & i).Value = ws("B" & i).Value
		ws("B" & i).Value = ws("A" & i).Value
		ws("A" & i).Value = "New Column Added"
	Next i
	wb.SaveAs("sample.xlsx")
End Sub
Jump to Article
Try IronXL free for development


Step 1

1. Download the IronXL Excel Library

To access the functions to add rows and columns in Excel, first we need to download the IronXL Excel Library. This is free for development in your project. Download the DLL directly or you can use the NuGet install method.


 PM > Install-Package IronXL.Excel


How to Tutorial

2. Excel Add Row in C#

Now that we have installed IronXL, it will be easy to insert new rows and columns in existing Excel Spreadsheets using C# programming.

First, access your Excel SpreadSheet and specify the WorkSheet where new rows or columns need to be added.

2.1. Add Row in Last Position

In our first example, we'll show how to add the new row in the last position.

Suppose that our Excel file is named sample.xlsx, and has 5 columns from A to E. Using the following method we can add new row:

using IronXL;
static void Main(string[] args)
{
    WorkBook wb = WorkBook.Load("sample.xlsx");
    WorkSheet ws = wb.GetWorkSheet("Sheet1");
    int i = ws.Rows.Count() + 1;
    ws["A" + i].Value = "New Row";
    ws["B" + i].Value = "New Row";
    ws["C" + i].Value = "New Row";
    ws["D" + i].Value = "New Row";
    ws["E" + i].Value = "New Row";
    wb.SaveAs("sample.xlsx");
}
using IronXL;
static void Main(string[] args)
{
    WorkBook wb = WorkBook.Load("sample.xlsx");
    WorkSheet ws = wb.GetWorkSheet("Sheet1");
    int i = ws.Rows.Count() + 1;
    ws["A" + i].Value = "New Row";
    ws["B" + i].Value = "New Row";
    ws["C" + i].Value = "New Row";
    ws["D" + i].Value = "New Row";
    ws["E" + i].Value = "New Row";
    wb.SaveAs("sample.xlsx");
}
Imports IronXL
Shared Sub Main(ByVal args() As String)
	Dim wb As WorkBook = WorkBook.Load("sample.xlsx")
	Dim ws As WorkSheet = wb.GetWorkSheet("Sheet1")
	Dim i As Integer = ws.Rows.Count() + 1
	ws("A" & i).Value = "New Row"
	ws("B" & i).Value = "New Row"
	ws("C" & i).Value = "New Row"
	ws("D" & i).Value = "New Row"
	ws("E" & i).Value = "New Row"
	wb.SaveAs("sample.xlsx")
End Sub
VB   C#

This will create a new row in the last position with value New Row in the Excel Spreadsheet sample.xlsx.

2.2. Add Row in First Position

It is also very simple to add a new row in the first position of the Excel SpreadSheet.

For this purpose, firstly we will move all existing rows into the next rows, so that the first row slot is available for us to add.

Let's see the example for how to add a new row in the first position of the Excel SpreadSheet.

using IronXL;
static void Main(string[] args)
{
    WorkBook wb = WorkBook.Load("sample.xlsx");
    WorkSheet ws = wb.GetWorkSheet("Sheet1");
    int j = ws.Rows.Count() + 1;
    for (int i = 1; i <= ws.Rows.Count(); i++)
    {
        if (j == 0) 
        {
            ws.Rows[0].Value = "new row"; 
            break; 
        };
        ws["A" + (j + 1)].Value = ws["A" + j].Value; 
        ws["B" + (j + 1)].Value = ws["B" + j].Value;
        ws["C" + (j + 1)].Value = ws["C" + j].Value;
        ws["D" + (j + 1)].Value = ws["D" + j].Value;
        ws["E" + (j + 1)].Value = ws["E" + j].Value;
        j = j - 1;
    }
 wb.SaveAs("sample.xlsx"); 
}
using IronXL;
static void Main(string[] args)
{
    WorkBook wb = WorkBook.Load("sample.xlsx");
    WorkSheet ws = wb.GetWorkSheet("Sheet1");
    int j = ws.Rows.Count() + 1;
    for (int i = 1; i <= ws.Rows.Count(); i++)
    {
        if (j == 0) 
        {
            ws.Rows[0].Value = "new row"; 
            break; 
        };
        ws["A" + (j + 1)].Value = ws["A" + j].Value; 
        ws["B" + (j + 1)].Value = ws["B" + j].Value;
        ws["C" + (j + 1)].Value = ws["C" + j].Value;
        ws["D" + (j + 1)].Value = ws["D" + j].Value;
        ws["E" + (j + 1)].Value = ws["E" + j].Value;
        j = j - 1;
    }
 wb.SaveAs("sample.xlsx"); 
}
Imports IronXL
Shared Sub Main(ByVal args() As String)
	Dim wb As WorkBook = WorkBook.Load("sample.xlsx")
	Dim ws As WorkSheet = wb.GetWorkSheet("Sheet1")
	Dim j As Integer = ws.Rows.Count() + 1
	For i As Integer = 1 To ws.Rows.Count()
		If j = 0 Then
			ws.Rows(0).Value = "new row"
			Exit For
		End If
		ws("A" & (j + 1)).Value = ws("A" & j).Value
		ws("B" & (j + 1)).Value = ws("B" & j).Value
		ws("C" & (j + 1)).Value = ws("C" & j).Value
		ws("D" & (j + 1)).Value = ws("D" & j).Value
		ws("E" & (j + 1)).Value = ws("E" & j).Value
		j = j - 1
	Next i
 wb.SaveAs("sample.xlsx")
End Sub
VB   C#

Let's compare the changes done by the above code in our sample.xlsx document.

BeforeAfter
beforeafter

Here we can see that before, the sample.xlsx file had 10 rows. With the new row added in the first position, it 11 became 11 rows.

2.3. Add Row in First with Column Header

In the case that we have the first column as a header name, then we can change the loop break condition at row 0, and have the new row added at position 1.


3. Excel Add Column in C#

It is also to add a new column in an existing Excel SpreadSheet for our C# project.

Suppose that we have 5 columns in our Excel SpreadSheet called sample.xlsx, from A to E, and we want to add a new column in the first position, A. See the code sample below.

using IronXL;
static void Main(string[] args)
{  
    WorkBook wb = WorkBook.Load("sample.xlsx");
    WorkSheet ws = wb.GetWorkSheet("Sheet1");
    for (int i = 1; i <= ws.Rows.Count(); i++)
    {
        ws["F" + i].Value = ws["E" + i].Value;
        ws["E" + i].Value = ws["D" + i].Value;
        ws["D" + i].Value = ws["C" + i].Value;
        ws["C" + i].Value = ws["B" + i].Value;
        ws["B" + i].Value = ws["A" + i].Value;
        ws["A" + i].Value = "New Column Added";
    }
    wb.SaveAs("sample.xlsx");
}
using IronXL;
static void Main(string[] args)
{  
    WorkBook wb = WorkBook.Load("sample.xlsx");
    WorkSheet ws = wb.GetWorkSheet("Sheet1");
    for (int i = 1; i <= ws.Rows.Count(); i++)
    {
        ws["F" + i].Value = ws["E" + i].Value;
        ws["E" + i].Value = ws["D" + i].Value;
        ws["D" + i].Value = ws["C" + i].Value;
        ws["C" + i].Value = ws["B" + i].Value;
        ws["B" + i].Value = ws["A" + i].Value;
        ws["A" + i].Value = "New Column Added";
    }
    wb.SaveAs("sample.xlsx");
}
Imports IronXL
Shared Sub Main(ByVal args() As String)
	Dim wb As WorkBook = WorkBook.Load("sample.xlsx")
	Dim ws As WorkSheet = wb.GetWorkSheet("Sheet1")
	For i As Integer = 1 To ws.Rows.Count()
		ws("F" & i).Value = ws("E" & i).Value
		ws("E" & i).Value = ws("D" & i).Value
		ws("D" & i).Value = ws("C" & i).Value
		ws("C" & i).Value = ws("B" & i).Value
		ws("B" & i).Value = ws("A" & i).Value
		ws("A" & i).Value = "New Column Added"
	Next i
	wb.SaveAs("sample.xlsx")
End Sub
VB   C#

Let's view the changes done by the above code in sample.xlsx:

BeforeAfter
beforeafter

We can see that new column was added in first position A, and the rest were pushed back, so now we have a total of 6 columns from A to F.


Library Quick Access

Read the IronXL Documentation

Read the documentation for IronXL for more functions and information on adding rows, columns, and other Excel C# functionality.

Read the IronXL Documentation