Test in production without watermarks.
Works wherever you need it to.
Get 30 days of fully functional product.
Have it up and running in minutes.
Full access to our support engineering team during your product trial
Working with CSV files can be a challenging task. There are many libraries available today to help developers with this task without the need to have Microsoft Excel installed.
In this article, we are going to discuss and compare how to work with Microsoft Excel documents programmatically in C# .NET Technologies, either in CSV format or standard XLSX format, using two of the most popular libraries, IronXL and CsvHelper.
Let's look firstly at what both libraries have to offer.
IronXL is a .NET library that facilitates the reading and editing of Microsoft Excel documents with C#. IronXL.Excel is a standalone .NET software library for reading a wide range of spreadsheet formats. It does not require Microsoft Excel to be installed, nor does it depend on Interop. It works very smoothly with CSV files.
IronXL is an intuitive C# API that allows you to read, edit, and create Excel spreadsheet files in .NET with lightning-fast performance. IronXL fully supports .NET Core, .NET Framework, Xamarin, Mobile, Linux, macOS, and Azure.
IronXL is a leading .NET core and .NET framework Excel spreadsheet library for C#.
WorkSheet["A1:B10"]
syntax. Combine and create ranges intuitively.A .NET library for reading and writing CSV files. Extremely fast, flexible, and easy to use. Supports the reading and writing of custom class objects. All sample codes are available in the package CsvHelper documentation.
CsvConfiguration
class to set the configuration for CSVStreamReader
CsvReader
class to readDefaultTypeConverter
classGetRecords<T>()
and WriteRecords(records)
Use the following steps to create a Console Application:
Create Project - Console Application
Create Project - .NET Framework
The project is now created and we are almost ready to test the libraries. However, we still need to install and integrate them into our project. Let's install IronXL first.
You can download and install the IronXL library using the following methods:
Let’s take a closer look at each one.
Visual Studio provides the NuGet Package Manager to install NuGet packages in your projects. You can access it through the Project Menu, or by right-clicking your project in the Solution Explorer.
Select-Manage-NuGet-Package
Search for IronXL
This can be done by visiting the NuGet site directly and downloading the package. The steps are:
Another way to download and install the IronXL C# Library is to make use of the following steps to install the IronXL NuGet package through the Developer Command Prompt.
PM> Install-Package IronXL.Excel
using IronXL;
using IronXL;
Imports IronXL
All done! IronXL is downloaded, installed, and ready to use. However, before that, we should install CsvHelper.
To download and install the CsvHelper C# Library, make use of the following steps to install the NuGet package through the Developer Command Prompt.
PM> Install-Package CsvHelper -Version 27.2.1
Download from the NuGet website: https://www.nuget.org/packages/CsvHelper.
A comma-separated values (CSV) file is a delimited text file that uses a comma to separate values. Each line of the file is a data record. To manipulate these files for calculations can be a challenging task, and IronXL provides a pretty good and easy option to do it without Microsoft Excel. Let's first convert a CSV file to a normal Excel file.
The process is pretty simple and easy. It is usually done with one line of code.
CSV to Excel formats:
// Load the CSV file and convert it to an Excel format
WorkBook workbook = WorkBook.LoadCSV("test.csv", fileFormat: ExcelFileFormat.XLSX, ListDelimiter: ",");
// Access the default worksheet
WorkSheet ws = workbook.DefaultWorkSheet;
// Save the workbook as an Excel file
workbook.SaveAs("CsvToExcelConversion.xlsx");
// Load the CSV file and convert it to an Excel format
WorkBook workbook = WorkBook.LoadCSV("test.csv", fileFormat: ExcelFileFormat.XLSX, ListDelimiter: ",");
// Access the default worksheet
WorkSheet ws = workbook.DefaultWorkSheet;
// Save the workbook as an Excel file
workbook.SaveAs("CsvToExcelConversion.xlsx");
' Load the CSV file and convert it to an Excel format
Dim workbook As WorkBook = WorkBook.LoadCSV("test.csv", fileFormat:= ExcelFileFormat.XLSX, ListDelimiter:= ",")
' Access the default worksheet
Dim ws As WorkSheet = workbook.DefaultWorkSheet
' Save the workbook as an Excel file
workbook.SaveAs("CsvToExcelConversion.xlsx")
The IronXL WorkBook
class represents an Excel sheet. To open an Excel File using C#, we use WorkBook.Load
and specify the path of the Excel file (.xlsx). The following one-line code is used to open the file for reading:
// Load WorkBook
var workbook = WorkBook.Load(@"Spreadsheets/sample.xlsx");
// Load WorkBook
var workbook = WorkBook.Load(@"Spreadsheets/sample.xlsx");
' Load WorkBook
Dim workbook = WorkBook.Load("Spreadsheets/sample.xlsx")
Each WorkBook
can contain multiple WorkSheet
objects. These represent worksheets in the Excel document. If the workbook contains worksheets, retrieve them by name as follows:
// Open the sheet for reading
var worksheet = workbook.GetWorkSheet("sheetnamegoeshere");
// Open the sheet for reading
var worksheet = workbook.GetWorkSheet("sheetnamegoeshere");
' Open the sheet for reading
Dim worksheet = workbook.GetWorkSheet("sheetnamegoeshere")
Code for reading the cell values:
// Read from ranges of cells elegantly
foreach (var cell in worksheet["A2:A10"])
{
Console.WriteLine("Cell {0} has value '{1}'", cell.AddressString, cell.Text);
}
// Read from ranges of cells elegantly
foreach (var cell in worksheet["A2:A10"])
{
Console.WriteLine("Cell {0} has value '{1}'", cell.AddressString, cell.Text);
}
' Read from ranges of cells elegantly
For Each cell In worksheet("A2:A10")
Console.WriteLine("Cell {0} has value '{1}'", cell.AddressString, cell.Text)
Next cell
After loading and reading the workbook and worksheet, the following code sample can be used to either make changes to formulas or apply them to specific cells. The code goes as follows:
// Set formulas in specific cells
worksheet["A1"].Formula = "SUM(B8:C12)";
worksheet["B8"].Formula = "=C9/C11";
worksheet["G30"].Formula = "MAX(C3:C7)";
// Force recalculate all formula values in all sheets
workbook.EvaluateAll();
// Set formulas in specific cells
worksheet["A1"].Formula = "SUM(B8:C12)";
worksheet["B8"].Formula = "=C9/C11";
worksheet["G30"].Formula = "MAX(C3:C7)";
// Force recalculate all formula values in all sheets
workbook.EvaluateAll();
' Set formulas in specific cells
worksheet("A1").Formula = "SUM(B8:C12)"
worksheet("B8").Formula = "=C9/C11"
worksheet("G30").Formula = "MAX(C3:C7)"
' Force recalculate all formula values in all sheets
workbook.EvaluateAll()
Writing CSV files is an easy process with IronXL. The following code simply saves the Excel file in the CSV format by using the SaveAsCsv
method.
// Load the Excel Workbook
WorkBook wb = WorkBook.Load("Normal_Excel_File.xlsx");
// Save the workbook as a CSV file
wb.SaveAsCsv("SaveAsCSV.csv", ",");
// This will save as "SaveAsCSV.Sheet1.csv"
// Load the Excel Workbook
WorkBook wb = WorkBook.Load("Normal_Excel_File.xlsx");
// Save the workbook as a CSV file
wb.SaveAsCsv("SaveAsCSV.csv", ",");
// This will save as "SaveAsCSV.Sheet1.csv"
' Load the Excel Workbook
Dim wb As WorkBook = WorkBook.Load("Normal_Excel_File.xlsx")
' Save the workbook as a CSV file
wb.SaveAsCsv("SaveAsCSV.csv", ",")
' This will save as "SaveAsCSV.Sheet1.csv"
Reading CSV files is one of those tasks that seem much easier than they actually are. The CsvHelper library makes it easy to write code that is type-safe, fast, and flexible.
This is the sample CSV file, it has three text columns and one number column.
FirstName,LastName,Age,IsActive
Ali,Talal,30,Yes
Affan,Ahmad,31,No
Saad,Bhatti,31,Yes
We will map each row into an object of type Person
.
// Define the Person class to map CSV records
public class Person
{
public string FirstName { get; set; }
public string LastName { get; set; }
public int? Age { get; set; }
public string IsActive { get; set; }
}
// Define the Person class to map CSV records
public class Person
{
public string FirstName { get; set; }
public string LastName { get; set; }
public int? Age { get; set; }
public string IsActive { get; set; }
}
' Define the Person class to map CSV records
Public Class Person
Public Property FirstName() As String
Public Property LastName() As String
Public Property Age() As Integer?
Public Property IsActive() As String
End Class
The code to read our CSV file is below.
// Define the path to the CSV file
var fileName = @"<path to our CSV file>";
// Configure CsvHelper
var configuration = new CsvConfiguration(CultureInfo.InvariantCulture)
{
Encoding = Encoding.UTF8,
Delimiter = ","
};
// Read CSV file
using (var fs = File.Open(fileName, FileMode.Open, FileAccess.Read, FileShare.Read))
{
using (var textReader = new StreamReader(fs, Encoding.UTF8))
using (var csv = new CsvReader(textReader, configuration))
{
var data = csv.GetRecords<Person>();
// Iterate over each person record
foreach (var person in data)
{
// Process each Person object
}
}
}
// Define the path to the CSV file
var fileName = @"<path to our CSV file>";
// Configure CsvHelper
var configuration = new CsvConfiguration(CultureInfo.InvariantCulture)
{
Encoding = Encoding.UTF8,
Delimiter = ","
};
// Read CSV file
using (var fs = File.Open(fileName, FileMode.Open, FileAccess.Read, FileShare.Read))
{
using (var textReader = new StreamReader(fs, Encoding.UTF8))
using (var csv = new CsvReader(textReader, configuration))
{
var data = csv.GetRecords<Person>();
// Iterate over each person record
foreach (var person in data)
{
// Process each Person object
}
}
}
' Define the path to the CSV file
Dim fileName = "<path to our CSV file>"
' Configure CsvHelper
Dim configuration = New CsvConfiguration(CultureInfo.InvariantCulture) With {
.Encoding = Encoding.UTF8,
.Delimiter = ","
}
' Read CSV file
Using fs = File.Open(fileName, FileMode.Open, FileAccess.Read, FileShare.Read)
Using textReader = New StreamReader(fs, Encoding.UTF8)
Using csv = New CsvReader(textReader, configuration)
Dim data = csv.GetRecords(Of Person)()
' Iterate over each person record
For Each person In data
' Process each Person object
Next person
End Using
End Using
End Using
The type of data
is IEnumerable<Person>
. CsvHelper will automatically map each column to the property with the same name. For example, the value in the FirstName
column will be mapped into Person.FirstName
. We can then iterate data
and access the values in each row.
CSV files mostly contain text data. For example, the age column is an integer value and the CSV file contains text only. CsvHelper can convert data from strings into standard .NET types (Boolean, Int32, Int64, Enum). In our case, we have an IsActive
bool datatype, which can only have a True/False
, and contains non-standard values. It can be converted by creating a custom converter. The code styling goes as follows:
// Custom Boolean Converter for CsvHelper
public class CustomBooleanConverter : DefaultTypeConverter
{
public override object ConvertFromString(string text, IReaderRow row, MemberMapData memberMapData)
{
// Convert "Yes" to true and anything else to false
return text.Equals("Yes", StringComparison.OrdinalIgnoreCase);
}
}
// Custom Boolean Converter for CsvHelper
public class CustomBooleanConverter : DefaultTypeConverter
{
public override object ConvertFromString(string text, IReaderRow row, MemberMapData memberMapData)
{
// Convert "Yes" to true and anything else to false
return text.Equals("Yes", StringComparison.OrdinalIgnoreCase);
}
}
' Custom Boolean Converter for CsvHelper
Public Class CustomBooleanConverter
Inherits DefaultTypeConverter
Public Overrides Function ConvertFromString(ByVal text As String, ByVal row As IReaderRow, ByVal memberMapData As MemberMapData) As Object
' Convert "Yes" to true and anything else to false
Return text.Equals("Yes", StringComparison.OrdinalIgnoreCase)
End Function
End Class
Even though bool
is a standard .NET type, the default converter can only handle True/False
value, while our CSV file has Yes/No
. Here, we need to subclass the DefaultTypeConverter
, then override the ConvertFromString
method.
IronXL is an openly commercial C# Excel library. It is free for development and can always be licensed for commercial deployment. Licenses are available for single-project use, single developers, agencies, and global corporations, as well as SaaS and OEM redistribution. All licenses include a 30-day money-back guarantee, one year of product support and updates, validity for dev/staging/production, and also a permanent license (one-time purchase). The Lite package starts from $749.
CsvHelper — reading and writing CSV files is completely free for commercial use.
IronXL is a complete library offering everything you need to be able to manipulate an Excel file. It is easy to use and provides you the facility to convert various formats to XLSX, as well as from XLSX to other formats, such as CSV. This interconversion offers the user flexibility to work with various file formats with ease.
On the other hand, CsvHelper is designed specifically to work with CSV file formats, meaning it can only deal with CSV files. All the code samples can be found in the CsvHelper documentation files. The documentation gives you guidelines on how to use CsvHelper in your project.
IronXL has a clear advantage over CsvHelper, as it supports users working with multiple formats. Moreover, you can apply formulas and styles according to your choice, whereas CsvHelper only allows for CSV file reading and writing with limited options. Also, you can not append new records to an existing CSV file, as it simply overwrites previous entries.
IronXL is a .NET library that facilitates the reading and editing of Microsoft Excel documents with C#. It supports a wide range of spreadsheet formats and does not require Microsoft Excel to be installed.
IronXL offers features such as loading, reading, and editing data from XLS/XLSX/CSV/TSV files, saving and exporting to various formats including JSON, sorting, styling, and more.
CsvHelper is a .NET library for reading and writing CSV files. It is known for being extremely fast, flexible, and easy to use, and it supports the reading and writing of custom class objects.
You can install the IronXL library using Visual Studio with NuGet packages, by downloading the NuGet package directly, or by manually installing it with the DLL.
To read CSV files using CsvHelper, define a class to map CSV records, configure CsvHelper, and use CsvReader to read records and convert them into instances of your defined class.
Yes, IronXL can handle various spreadsheet formats including XLS, XLSX, CSV, and TSV, making it versatile for different Excel document manipulations.
Yes, CsvHelper is completely free for commercial use.
If you need to work with multiple Excel formats beyond CSV and require features like styling and formula application, IronXL is the preferred choice. For straightforward CSV file manipulation, CsvHelper is efficient and easy to use.