Published June 21, 2023
How to Open Excel File and Write Data in C#
Microsoft Excel is a spreadsheet software application used to store data in tabular form. If you are a C# developer, you have likely encountered the need to work with Excel files. Building an application from scratch to handle Excel files can be a cumbersome task. Fortunately, there are several libraries available that simplify this process. One such library is IronXL.
In this article, we will explore the IronXL library to demonstrate how to open a Microsoft Excel file and write data to it in a C# console application.
IronXL - An Excel Library
IronXL is a .NET Excel Library that facilitates the creation, reading, and editing of Excel files in C# applications. It offers exceptional performance and accurate output. The library supports all Excel workbook file formats, including XLS, XLSX, XLSM, CSV, and TSV. Additionally, it allows data to be saved or exported in formats such as JSON, HTML, Binary, Byte Array, DataSet, or DataTable.
With IronXL, developers can work with worksheets and cell ranges in a seamless manner. It offers the ability to edit formulas and easily recalculate them within a sheet. Sorting data based on range, column, or row is straightforward. The library provides features to modify layouts, such as freezing panes, auto-sizing rows/columns, and adding/removing rows/columns.
IronXL also enables the protection of Excel files with user passwords and permissions for editing. Another notable feature is the ability to add, remove, and extract images from Excel worksheets. The library offers a wide range of Excel functions, supporting various cell data formats. These features make IronXL one of the most user-friendly APIs for working with Excel files.
One of the significant advantages of IronXL is that it does not require Microsoft Excel to be installed on the machine, eliminating the need for Office Interop or any other dependencies. It is compatible with multiple platforms and supports .NET 7, 6, and 5. It is also compatible with .NET Core 2 and 3, as well as .NET Framework 4.5 and later versions for working with Excel spreadsheets.
Create a Console Application
We will be using the Visual Studio IDE to create our application. Visual Studio is the official IDE for C# development, and it is assumed that you have already installed it. If you haven't installed Visual Studio, you can download it from the official Microsoft Visual Studio website.
Follow these steps to create a new project named "DemoApp".
Open Visual Studio and click on Create a New Project
Select Console Application and Click Next
Enter the name of the Project
Select the .NET version. Choose the stable version .NET 6.0.
Install IronXL Library
Once the project is created, the IronXL library needs to be installed in the project in order to use it. Follow these steps to install it.
Open NuGet Package Manager for Solutions either from Solution explorer or Tools.
Browse for IronXL Library and select the current project. Click install.
Add the following namespace at the top of Program.cs file
using IronXL;using IronXL;Imports IronXLOpen an Existing Excel Files in C#
IronXL provides the facility to open an existing Excel file, or you can create a new Excel file. Here we are going to open an existing file using C# IronXL.
// Supported spreadsheet formats for reading XLSX, XLS, XLSM, XLTX, CSV and TSV
WorkBook workBook = WorkBook.Load("sample.xlsx");// Supported spreadsheet formats for reading XLSX, XLS, XLSM, XLTX, CSV and TSV
WorkBook workBook = WorkBook.Load("sample.xlsx");' Supported spreadsheet formats for reading XLSX, XLS, XLSM, XLTX, CSV and TSV
Dim workBook As WorkBook = WorkBook.Load("sample.xlsx")Now, let's select its first worksheet. You can select a worksheet by index number or by name. To get the first sheet we can use DefaultWorkSheet property.
// Select worksheet at index 0
WorkSheet workSheet = workBook.WorkSheets[0];
// Select worksheet by name
WorkSheet ws = wb.GetWorkSheet("Sheet1");
// Get any existing worksheet
WorkSheet firstSheet = workBook.DefaultWorkSheet;// Select worksheet at index 0
WorkSheet workSheet = workBook.WorkSheets[0];
// Select worksheet by name
WorkSheet ws = wb.GetWorkSheet("Sheet1");
// Get any existing worksheet
WorkSheet firstSheet = workBook.DefaultWorkSheet;' Select worksheet at index 0
Dim workSheet As WorkSheet = workBook.WorkSheets(0)
' Select worksheet by name
Dim ws As WorkSheet = wb.GetWorkSheet("Sheet1")
' Get any existing worksheet
Dim firstSheet As WorkSheet = workBook.DefaultWorkSheetThe code above gets the first sheet from the Excel workbook. For creating new Excel file with data, check this code example page.
Now, let's write data to the Excel file using the IronXL object library.
Write Data to Excel File in C#
Writing data to an Excel file using IronXL is very easy. There are multiple ways of writing data to an Excel file, but the simplest method is to use the Excel cell reference.
// Access A1 cell and write the value
workSheet["A1"].Value = "Value using cell reference";// Access A1 cell and write the value
workSheet["A1"].Value = "Value using cell reference";' Access A1 cell and write the value
workSheet("A1").Value = "Value using cell reference"We can also write data to a range of cells. The following code writes data from cell B1 to B5.
workSheet["B1:B5"].Value = "Range value";workSheet["B1:B5"].Value = "Range value";workSheet("B1:B5").Value = "Range value"We can also fill in the range with a for loop making it dynamic. The code goes as follows:
//specify range in which we want to write the values
for (int i = 1; i <= 5; i++)
{
//write the Dynamic value in one row
workSheet["C" + i].Value = "Value: " + i;
//write the Dynamic value in another row
ws["D" + i].Value = "Value: " + i;
}//specify range in which we want to write the values
for (int i = 1; i <= 5; i++)
{
//write the Dynamic value in one row
workSheet["C" + i].Value = "Value: " + i;
//write the Dynamic value in another row
ws["D" + i].Value = "Value: " + i;
}'specify range in which we want to write the values
For i As Integer = 1 To 5
'write the Dynamic value in one row
workSheet("C" & i).Value = "Value: " & i
'write the Dynamic value in another row
ws("D" & i).Value = "Value: " & i
Next iAnother way of writing data to Excel file is by using the Replace method.
workSheet["D5"].Replace("Value: 5", "Replaced Value");workSheet["D5"].Replace("Value: 5", "Replaced Value");workSheet("D5").Replace("Value: 5", "Replaced Value")Save Excel File in C#
Finally, we are going to save the Excel file with newly written content to it.
workBook.SaveAs("sample.xlsx");workBook.SaveAs("sample.xlsx");workBook.SaveAs("sample.xlsx")The code goes as follows:
using System;
using IronXL;
static void Main(string[] args)
{
// Supported spreadsheet formats for reading XLSX, XLS, XLSM, XLTX, CSV and TSV
WorkBook workBook = WorkBook.Load("sample.xlsx");
// Select worksheet at index 0
WorkSheet workSheet = workBook.WorkSheets[0];
// Access A1 cell and write the value
workSheet["A1"].Value = "Value using cell reference";
workSheet["B1:B5"].Value = "Range value";
//specify range in which we want to write the values
for (int i = 1; i <= 5; i++)
{
//write the Dynamic value in one row
workSheet["C" + i].Value = "Value: " + i;
//write the Dynamic value in another row
workSheet["D" + i].Value = "Value: " + i;
}
workSheet["D5"].Replace("Value: 5", "Replaced Value");
workBook.SaveAs("sample.xlsx");
Console.WriteLine("successfully written in Excel File");
}using System;
using IronXL;
static void Main(string[] args)
{
// Supported spreadsheet formats for reading XLSX, XLS, XLSM, XLTX, CSV and TSV
WorkBook workBook = WorkBook.Load("sample.xlsx");
// Select worksheet at index 0
WorkSheet workSheet = workBook.WorkSheets[0];
// Access A1 cell and write the value
workSheet["A1"].Value = "Value using cell reference";
workSheet["B1:B5"].Value = "Range value";
//specify range in which we want to write the values
for (int i = 1; i <= 5; i++)
{
//write the Dynamic value in one row
workSheet["C" + i].Value = "Value: " + i;
//write the Dynamic value in another row
workSheet["D" + i].Value = "Value: " + i;
}
workSheet["D5"].Replace("Value: 5", "Replaced Value");
workBook.SaveAs("sample.xlsx");
Console.WriteLine("successfully written in Excel File");
}Imports System
Imports IronXL
Shared Sub Main(ByVal args() As String)
' Supported spreadsheet formats for reading XLSX, XLS, XLSM, XLTX, CSV and TSV
Dim workBook As WorkBook = WorkBook.Load("sample.xlsx")
' Select worksheet at index 0
Dim workSheet As WorkSheet = workBook.WorkSheets(0)
' Access A1 cell and write the value
workSheet("A1").Value = "Value using cell reference"
workSheet("B1:B5").Value = "Range value"
'specify range in which we want to write the values
For i As Integer = 1 To 5
'write the Dynamic value in one row
workSheet("C" & i).Value = "Value: " & i
'write the Dynamic value in another row
workSheet("D" & i).Value = "Value: " & i
Next i
workSheet("D5").Replace("Value: 5", "Replaced Value")
workBook.SaveAs("sample.xlsx")
Console.WriteLine("successfully written in Excel File")
End SubFor more detailed information on how to read Excel file data in C#, have a look at this example.
Output
The output of the file is:
Summary
In this article, we have looked at how to write data to Excel files in C# using IronXL. IronXL provides the facility to work with existing Excel files without any hassle. It also allows you to create new Excel files and write data to it with easy syntax. IronXL can also be used to read Excel files without the Microsoft Excel application installed. To read data from Excel files you can see this code example page.
IronXL is free for development and can be licensed for commercial use. You can also try IronXL free trial in commercial use.
