Porównanie IronXL i CsvHelper

Zestaw funkcji IronXL

• Wczytuj, przeglądaj i edytuj dane — z plików XLS/XLSX/CSV/TSV
• Zapisywanie i eksportowanie — do formatów XLS/XLSX/CSV/TSV/JSON
• Ranges — easy-to-use WorkSheet["A1:B10"] syntax. Intuicyjnie łącz i twórz zakresy.
• Sortowanie — sortowanie zakresów, kolumn i wierszy.
• Stylizacja — style wizualne komórek, czcionka, rozmiar, wzór tła, obramowanie, wyrównanie i formaty liczb.

CsvHelper Features

• Fast: compiles classes on the fly for extremely fast performance
• Flexible: conservative when writing, liberal when reading
• Easy to use: reading and writing are as simple as GetRecords<t>() and WriteRecords(records)
• Highly configurable
• Linux mode
• Low memory usage

2.1. Korzystanie z programu Visual Studio z pakietami NuGet

Visual Studio udostępnia menedżera pakietów NuGet do instalowania pakietów NuGet w projektach. Dostęp do niego można uzyskać poprzez menu Projekt lub klikając prawym przyciskiem myszy na projekt w Eksploratorze rozwiązań.

Select-Manage-NuGet-Package

• Now, from the browse tab -> search for IronXl.Excel -> Install

Search for IronXL

• And we are done.

2.2. Pobierz pakiet NuGet bezpośrednio

Można to zrobić, odwiedzając bezpośrednio stronę NuGet i pobierając pakiet. Kroki są następujące:

• Navigate to https://www.nuget.org/packages/IronXl.Excel
• Select "Download Package"
• Double-click the downloaded package
• The package will be installed
• Reload your Visual Studio project and begin using it

2.3. Ręczna instalacja za pomocą pliku DLL

Innym sposobem pobrania i zainstalowania biblioteki IronXL C# jest wykonanie poniższych kroków w celu zainstalowania pakietu IronXL NuGet za pomocą wiersza poleceń programisty.

• Open the Developer Command Prompt — usually found in the Visual Studio folder.
• Type the following command: PM> Install-Package IronXl.Excel
• Press Enter
• This will download and install the package
• Reload your Visual Studio project and begin using it

2.4. Dodaj niezbędne dyrektywy using

1. In Solution Explorer, right-click the Program.cs file and then click View Code.
2. Dodaj następujące dyrektywy using na początku pliku kodu:

using IronXL;

using IronXL;

Imports IronXL

Gotowe! IronXL jest pobrany, zainstalowany i gotowy do użycia. However, before that, we should install CsvHelper.

3.1. Using the NuGet Package Manager Console

To download and install the CsvHelper C# Library, make use of the following steps to install the NuGet package through the Developer Command Prompt.

• Open the Developer Command Prompt — usually found in the Visual Studio folder.
• Type the following command: PM> Install-Package CsvHelper -Version 27.2.1
• Press Enter
• This will download and install the package
• Reload your Visual Studio project and begin using it

3.2. Direct Download

Download from the NuGet website: https://www.nuget.org/packages/CsvHelper.

4.1. Converting a CSV File to Excel Format

Proces jest dość prosty i łatwy. Zazwyczaj wykonuje się to za pomocą jednej linii kodu.

Formaty CSV do Excel:

// 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")

4.2. Read and Manipulate Converted CSV Files using IronXL

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). Poniższy jednowierszowy kod służy do otwarcia pliku w celu odczytu:

// 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. Są to arkusze w dokumencie Excel. Jeśli skoroszyt zawiera arkusze, należy je pobrać według nazwy w następujący sposób:

// 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")

Kod do odczytu wartości komórek:

// 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. Kod wygląda następująco:

// 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()

4.3. Saving the Excel document back to CSV File

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"

5.1. Read CSV files with CsvHelper

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.

5.2. Convert Data in CSV Files from one Type to Another

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.

Summary

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.

Wnioski

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.