Write Excel .NET Functions
Many C# application projects require us to update files and write new data in Excel Spreadsheets programmatically. Excel .NET capabilities can sometimes be complicated, but using the IronXL library, this task is quite simple and allows working with Excel Spreadsheets in any format. No bulk lines of code, just access to the specific cells and the custom values you assign.
Install with NuGet
Install-Package IronXL.Excel
Download DLL
Manually install into your project
Install with NuGet
Install-Package IronXL.Excel
Download DLL
Manually install into your project
Start using IronPDF in your project today with a free trial.
Check out IronXL on Nuget for quick installation and deployment. With over 8 million downloads, it's transforming Excel with C#.
Install-Package IronXL.Excel
Consider installing the IronXL DLL directly. Download and manually install it for your project or GAC form: IronXL.zip
Manually install into your project
Download DLLAccess Excel Files
Let's start by accessing the Excel file in which we want to write the data. Lets open the Excel file in our project, and then open its specific WorkSheet, using the following code.
:path=/static-assets/excel/content-code-examples/how-to/write-excel-net-load-file.cs
// Load Excel file in the project
WorkBook workBook = WorkBook.Load("path");
' Load Excel file in the project
Dim workBook As WorkBook = WorkBook.Load("path")
The above will open the specified Excel file. Next, the Worksheet.
:path=/static-assets/excel/content-code-examples/how-to/write-excel-net-get-sheet.cs
// Open Excel WorkSheet
WorkSheet workSheet = workBook.GetWorkSheet("Sheet1");
' Open Excel WorkSheet
Dim workSheet As WorkSheet = workBook.GetWorkSheet("Sheet1")
The Excel WorkSheet will open in workSheet
and we can use it to write any type of data in Excel file. Learn more about how to load Excel file types and access worksheets in different ways through the examples in the link.
Note: Don't forget to add the reference of IronXL
in your project and import library by Using IronXL
.
Write Value in Specific Cell
We can write in an Excel file using many different methods, but the basic approach is using Excel Cell
. For this purpose, any cell of the opened Excel WorkSheet can be accessed and a value written in it as follow:
:path=/static-assets/excel/content-code-examples/how-to/write-excel-net-assign-cell.cs
workSheet["Cell Address"].Value="Assign the Value";
workSheet("Cell Address").Value="Assign the Value"
Here's an example of how to use the above function to write in an Excel Cell in our C# project.
:path=/static-assets/excel/content-code-examples/how-to/write-excel-net-assign-cell-full.cs
using IronXL;
// Load Excel file
WorkBook workBook = WorkBook.Load("sample.xlsx");
// Open WorkSheet of sample.xlsx
WorkSheet workSheet = workBook.GetWorkSheet("Sheet1");
// Access A1 cell and write the value
workSheet["A1"].Value = "new value";
// Save changes
workBook.SaveAs("sample.xlsx");
Imports IronXL
' Load Excel file
Private workBook As WorkBook = WorkBook.Load("sample.xlsx")
' Open WorkSheet of sample.xlsx
Private workSheet As WorkSheet = workBook.GetWorkSheet("Sheet1")
' Access A1 cell and write the value
Private workSheet("A1").Value = "new value"
' Save changes
workBook.SaveAs("sample.xlsx")
This code will write new value
in the A1
cell of the WorkSheet Sheet1
in the Excel file sample.xlsx
. In the same way, we can insert values in any cell address of an Excel file.
Note: Don't forget to save the Excel file after writing new values in the WorkSheet, as shown in the example above.
Force Assign the Exact Value
When setting the Value property, IronXL would try to convert it to its corresponding value type. Sometimes, this evaluation is undesirable since the user wants to force assign the exact value to the cell and not the converted one. The way to do this is to assign the value as a string. This can be done by placing an apostrophe before a cell value in Excel. In IronXL, simply use StringValue instead of Value to achieve the same thing.
:path=/static-assets/excel/content-code-examples/how-to/write-excel-net-assign-stringvalue.cs
// Assign value as string
workSheet["A1"].StringValue = "4402-12";
' Assign value as string
workSheet("A1").StringValue = "4402-12"
Write Static Values in a Range
We can write new values in multiple cells, called a Range, as followings:
:path=/static-assets/excel/content-code-examples/how-to/write-excel-net-assign-cell-range.cs
workSheet["From Cell Address:To Cell Address"].Value = "New Value";
workSheet("From Cell Address:To Cell Address").Value = "New Value"
In this way, we specify the range of cells From
to To
where the data will be written. After this, New Value
will be written in all the cells which lie in this range. The understand more about C# Excel Range check out the examples here.
Let's see how to write a range in action using the example below.
:path=/static-assets/excel/content-code-examples/how-to/write-excel-net-assign-cell-range-full.cs
using IronXL;
// Load Excel file
WorkBook workBook = WorkBook.Load("sample.xlsx");
// Open WorkSheet of sample.xlsx
WorkSheet workSheet = workBook.GetWorkSheet("Sheet1");
// Specify range row wise and write new value
workSheet["B2:B9"].Value = "new value";
// Specify range column wise and write new value
workSheet["C3:C7"].Value = "new value";
// Save changes
workBook.SaveAs("sample.xlsx");
Imports IronXL
' Load Excel file
Private workBook As WorkBook = WorkBook.Load("sample.xlsx")
' Open WorkSheet of sample.xlsx
Private workSheet As WorkSheet = workBook.GetWorkSheet("Sheet1")
' Specify range row wise and write new value
Private workSheet("B2:B9").Value = "new value"
' Specify range column wise and write new value
Private workSheet("C3:C7").Value = "new value"
' Save changes
workBook.SaveAs("sample.xlsx")
This code will write new value
from B2
to B9
(Row Wise Range) and from C3
to C7
(Column Wise Range) of WorkSheet sheet1
of Excel file sample.xlsx
. It used Static values for the Excel cells.
Write Dynamic Values in a Range
We can also add dynamic values to a range, as seen below.
:path=/static-assets/excel/content-code-examples/how-to/write-excel-net-assign-dynamic-value.cs
using IronXL;
// Load Excel file
WorkBook workBook = WorkBook.Load("sample.xlsx");
// Open WorkSheet of sample.xlsx
WorkSheet workSheet = workBook.GetWorkSheet("Sheet1");
// Specify range in which we want to write the values
for (int i = 2; i <= 7; i++)
{
// Write the Dynamic value in one row
workSheet["B" + i].Value = "Value" + i;
// Write the Dynamic value in another row
workSheet["D" + i].Value = "Value" + i;
}
// Save changes
workBook.SaveAs("sample.xlsx");
Imports IronXL
' Load Excel file
Private workBook As WorkBook = WorkBook.Load("sample.xlsx")
' Open WorkSheet of sample.xlsx
Private workSheet As WorkSheet = workBook.GetWorkSheet("Sheet1")
' Specify range in which we want to write the values
For i As Integer = 2 To 7
' Write the Dynamic value in one row
workSheet("B" & i).Value = "Value" & i
' Write the Dynamic value in another row
workSheet("D" & i).Value = "Value" & i
Next i
' Save changes
workBook.SaveAs("sample.xlsx")
The above code will write dynamic values in column B
and D
from 2
to 7
in Excel file sample.xlsx
. We can see the result of the code on sample.xlsx
:
Replace Excel Cell Value
Using IronXL, we can easily write a new value to replace the old value, using the Replace()
function as follows:
:path=/static-assets/excel/content-code-examples/how-to/write-excel-net-replace.cs
workSheet.Replace("old value", "new value");
workSheet.Replace("old value", "new value")
The above function will write new value
overwriting the old value
in the complete Excel WorkSheet.
Replace Cell Value in Specific Row
If we want to write a new value just in one specific row, then it can be done as follows:
:path=/static-assets/excel/content-code-examples/how-to/write-excel-net-replace-row.cs
workSheet.Rows[RowIndex].Replace("old value", "new value");
workSheet.Rows(RowIndex).Replace("old value", "new value")
This will write new value
over the old value
in only the specified row index.
Replace Cell Value in Specific Column
In the same way, if we want to write new value
over the old value
within a specific column, then it can be done as follows:
:path=/static-assets/excel/content-code-examples/how-to/write-excel-net-replace-column.cs
workSheet.Columns[ColumnIndex].Replace("old value", "new Value");
workSheet.Columns(ColumnIndex).Replace("old value", "new Value")
The above code will write new value
to replace the old value
, but only in the specified column index. The rest of the WorkSheet remains the same.
Replace Cell Value in Specific Range
IronXL also provides a way to write new value
replacing the old value
, only in a specific range.
:path=/static-assets/excel/content-code-examples/how-to/write-excel-net-replace-range.cs
workSheet["From Cell Address : To Cell Address"].Replace("old value", "new value");
workSheet("From Cell Address : To Cell Address").Replace("old value", "new value")
This will write new value
above old value
, just in the cells which lie in the specified range.
Let's see the example of how to use all of the above functions to write new values to replace old values in an Excel WorkSheet.
Example of all Functions
:path=/static-assets/excel/content-code-examples/how-to/write-excel-net-replace-full.cs
using IronXL;
WorkBook workBook = WorkBook.Load("sample.xlsx");
WorkSheet workSheet = workBook.GetWorkSheet("Sheet1");
// Write new above old in complete WorkSheet
workSheet.Replace("old", "new");
// Write new above old just in row no 6 of WorkSheet
workSheet.Rows[5].Replace("old", "new");
// Write new above old just in column no 5 of WorkSheet
workSheet.Columns[4].Replace("old", "new");
// Write new above old just from A5 to H5 of WorkSheet
workSheet["A5:H5"].Replace("old", "new");
workBook.SaveAs("sample.xlsx");
Imports IronXL
Private workBook As WorkBook = WorkBook.Load("sample.xlsx")
Private workSheet As WorkSheet = workBook.GetWorkSheet("Sheet1")
' Write new above old in complete WorkSheet
workSheet.Replace("old", "new")
' Write new above old just in row no 6 of WorkSheet
workSheet.Rows(5).Replace("old", "new")
' Write new above old just in column no 5 of WorkSheet
workSheet.Columns(4).Replace("old", "new")
' Write new above old just from A5 to H5 of WorkSheet
workSheet("A5:H5").Replace("old", "new")
workBook.SaveAs("sample.xlsx")
For more information about how to write Excel .NET applications and more, check out our full tutorial on how to Open and Write Excel Files C#.
Tutorial Quick Access
Read API Reference
Read the IronXL documentation including lists of all functions, features, namespaces, classes, and enums available to you in the library.
Read API Reference