How to Open Excel File and Write Data in C#
This article 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 seamlessly. 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
The latest version of Visual Studio IDE is recommended to create the 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
New Project
Select Console Application and Click Next
New Project Type
Enter the name of the Project
New Project Name
Select the .NET version. Choose the stable version .NET 6.0.
New Project Additional Info
Install IronXL Library
Once the project is created, the IronXL library needs to be installed in the project to use it. Follow these steps to install it.
Open Manage NuGet Packages for Solutions either from Solution Explorer or Tools.
NuGet Package Manager
Browse for IronXL Library and select the current project. Click install.
Search and install the IronXL package in NuGet Package Manager UI
Add the following namespace at the top of Program.cs
file
using IronXL;
using IronXL;
Imports IronXL
Open an Existing Excel File in C#
IronXL provides the facility to open an existing Excel file, or you can create a new Excel file. This example is 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. The DefaultWorkSheet
property can help to get the first sheet.
// Select worksheet at index 0
WorkSheet workSheet = workBook.WorkSheets[0];
// Select worksheet by name
WorkSheet ws = workBook.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 = workBook.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 = workBook.GetWorkSheet("Sheet1")
' Get any existing worksheet
Dim firstSheet As WorkSheet = workBook.DefaultWorkSheet
The code above gets the first sheet from the Excel workbook. For creating a 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 to achieve it, 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"
It is also possible to write data to a range of cells. The following code writes data from cell B1 to B5.
// Write the same value to cells from B1 to B5
workSheet["B1:B5"].Value = "Range value";
// Write the same value to cells from B1 to B5
workSheet["B1:B5"].Value = "Range value";
' Write the same value to cells from B1 to B5
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 column C
workSheet["C" + i].Value = "Value: " + i;
// Write the Dynamic value in column D
workSheet["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 column C
workSheet["C" + i].Value = "Value: " + i;
// Write the Dynamic value in column D
workSheet["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 column C
workSheet("C" & i).Value = "Value: " & i
' Write the Dynamic value in column D
workSheet("D" & i).Value = "Value: " & i
Next i
Another way of writing data to an Excel file is by using the Replace
method.
// Replace the value in cell D5
workSheet["D5"].Replace("Value: 5", "Replaced Value");
// Replace the value in cell D5
workSheet["D5"].Replace("Value: 5", "Replaced Value");
' Replace the value in cell D5
workSheet("D5").Replace("Value: 5", "Replaced Value")
Save an Excel File in C#
This section explains how to save the Excel file with newly written content to it.
// Save the updated Excel workbook to a file
workBook.SaveAs("sample.xlsx");
// Save the updated Excel workbook to a file
workBook.SaveAs("sample.xlsx");
' Save the updated Excel workbook to a file
workBook.SaveAs("sample.xlsx")
The complete code goes as follows:
using System;
using IronXL;
class Program
{
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";
// Write the same value to cells from B1 to B5
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 column C
workSheet["C" + i].Value = "Value: " + i;
// Write the Dynamic value in column D
workSheet["D" + i].Value = "Value: " + i;
}
// Replace the value in cell D5
workSheet["D5"].Replace("Value: 5", "Replaced Value");
// Save the updated Excel workbook to a file
workBook.SaveAs("sample.xlsx");
Console.WriteLine("Successfully written to Excel File");
}
}
using System;
using IronXL;
class Program
{
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";
// Write the same value to cells from B1 to B5
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 column C
workSheet["C" + i].Value = "Value: " + i;
// Write the Dynamic value in column D
workSheet["D" + i].Value = "Value: " + i;
}
// Replace the value in cell D5
workSheet["D5"].Replace("Value: 5", "Replaced Value");
// Save the updated Excel workbook to a file
workBook.SaveAs("sample.xlsx");
Console.WriteLine("Successfully written to Excel File");
}
}
Imports System
Imports IronXL
Friend Class Program
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"
' Write the same value to cells from B1 to B5
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 column C
workSheet("C" & i).Value = "Value: " & i
' Write the Dynamic value in column D
workSheet("D" & i).Value = "Value: " & i
Next i
' Replace the value in cell D5
workSheet("D5").Replace("Value: 5", "Replaced Value")
' Save the updated Excel workbook to a file
workBook.SaveAs("sample.xlsx")
Console.WriteLine("Successfully written to Excel File")
End Sub
End Class
For 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:
The output Excel file
Summary
This article demonstrated 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 the IronXL free trial for commercial use.
Frequently Asked Questions
How can I open an Excel file in C# without using Interop?
You can open an Excel file in C# without using Interop by utilizing the IronXL library. IronXL allows you to efficiently handle Excel files without the need for Microsoft Excel installation, enhancing performance and compatibility.
How do I write data to a specific cell in an Excel file using C#?
Using IronXL, you can write data to a specific cell in an Excel file by accessing the workbook, selecting the worksheet, and then using cell references to assign values to the desired cells.
What are the advantages of using IronXL over Office Interop for Excel file manipulation?
IronXL offers several advantages over Office Interop, including the absence of dependency on Microsoft Excel installation, improved performance, cross-platform compatibility, and support for various Excel file formats like XLS, XLSX, and CSV.
Can I edit formulas in Excel files using this library?
Yes, IronXL allows you to edit formulas in Excel files. You can manipulate existing formulas or insert new ones using straightforward syntax, which the library processes efficiently.
How does IronXL support different Excel file formats?
IronXL supports multiple Excel file formats such as XLS, XLSX, XLSM, CSV, and TSV, enabling you to create, read, and edit these files seamlessly in a C# application.
Is it possible to protect an Excel file using IronXL?
Yes, IronXL provides functionality to protect Excel files by setting user passwords and permissions, ensuring data security and controlled access.
How can I integrate the IronXL library into my C# project?
To integrate IronXL into your C# project, you can use the NuGet Package Manager in Visual Studio. Search for IronXL, and add it to your project to start using its features.
What platforms are supported by IronXL?
IronXL supports multiple platforms, including .NET 5, 6, and 7, as well as .NET Core and Framework, making it a versatile choice for various development environments.
Can I use IronXL for free?
IronXL is free for development purposes. However, for commercial use, a license is required. A free trial is available to test the library's features in a commercial environment.
How can I export Excel data to JSON using IronXL?
IronXL allows you to export Excel data to JSON format by converting the worksheet or specific data ranges into JSON strings, which can then be used in applications requiring JSON data.