IronXL 和 CsvWriter 的比较

IronXL Feature Set

• Load, read, and edit data — from XLS/XLSX/CSV/TSV
• Saving and exporting — to XLS/XLSX/CSV/TSV/JSON
• Ranges — easy to use WorkSheet ["A1:B10"] syntax. Combine and create ranges intuitively.
• Sorting — sort ranges, columns, and rows.
• Styling — cell visual styles, font, size, background pattern, border, alignment, and number formats.

CsvWriter 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. Using Visual Studio with NuGet packages

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

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

Search for IronXL

• And we are done.

2.2. Download the NuGet Package Directly

This can be done by visiting the NuGet site directly and downloading the package. The steps are:

• Navigate tohttps://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. Manually Install with the DLL

Another way to download and install the IronXL C# Library is to make use of the following steps to install theIronXLNuGet packagethrough the Developer Command Prompt.

• Openthe Developer Command Prompt— usually found in the Visual Studio folder.
• Type the following command:

  Install-Package IronXL.Excel

• Press Enter
• This will download and install the package
• Reload your Visual Studio project and begin using it

2.4. Add Necessary Using Directives

1. InSolution Explorer, right-click theProgram.csfile and then clickView Code.
2. Add the followingusingdirectives to the top of the code file:

using IronXL;

using IronXL;

Imports IronXL

All done! IronXL is downloaded, installed, and ready to use. However, before that, we should install CsvHelper.

3.1. Using the NuGet Package Manager Console

To work with CSVWriter you need to download and install the CsvHelper C# Library. To do so, make use of the following steps to install theNuGet packagethrough the Developer Command Prompt.

• Openthe Developer Command Prompt— usually found in the Visual Studio folder.
• Type the following command:

  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
• Check the CsvHelper.CsvWriter method detail for implementation

3.2. Direct Download

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

4.1. Converting a CSV File to Excel Format

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 specify the format and delimiter
WorkBook workbook = WorkBook.LoadCSV("test.csv", fileFormat: ExcelFileFormat.XLSX, ListDelimiter: ",");
// Get the default worksheet
WorkSheet ws = workbook.DefaultWorkSheet;

// Save the workbook as an Excel file
workbook.SaveAs("CsvToExcelConversion.xlsx");

// Load the CSV file and specify the format and delimiter
WorkBook workbook = WorkBook.LoadCSV("test.csv", fileFormat: ExcelFileFormat.XLSX, ListDelimiter: ",");
// Get the default worksheet
WorkSheet ws = workbook.DefaultWorkSheet;

// Save the workbook as an Excel file
workbook.SaveAs("CsvToExcelConversion.xlsx");

' Load the CSV file and specify the format and delimiter
Dim workbook As WorkBook = WorkBook.LoadCSV("test.csv", fileFormat:= ExcelFileFormat.XLSX, ListDelimiter:= ",")
' Get 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 IronXLWorkBookclass 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 multipleWorkSheetobjects. These represent worksheets in the Excel document. If the workbook contains worksheets, retrieve them by name as follows:

// Open Sheet for reading
var worksheet = workbook.GetWorkSheet("sheetnamegoeshere");

// Open Sheet for reading
var worksheet = workbook.GetWorkSheet("sheetnamegoeshere");

' Open 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 formulas to specific cells. The code goes as follows:

// Set Formulas
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
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
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 workbook
WorkBook wb = WorkBook.Load("Normal_Excel_File.xlsx");
// Save as .csv file
wb.SaveAsCsv("SaveAsCSV.csv", ",");
// Saved as : SaveAsCSV.Sheet1.csv

// Load the workbook
WorkBook wb = WorkBook.Load("Normal_Excel_File.xlsx");
// Save as .csv file
wb.SaveAsCsv("SaveAsCSV.csv", ",");
// Saved as : SaveAsCSV.Sheet1.csv

' Load the workbook
Dim wb As WorkBook = WorkBook.Load("Normal_Excel_File.xlsx")
' Save as .csv file
wb.SaveAsCsv("SaveAsCSV.csv", ",")
' Saved as : SaveAsCSV.Sheet1.csv

5.1. Create a Simple Console Application

Create a simple CSVWriter with supplied separator console application using Visual Studio. Once the project is created, the next step is to create a Person class:

public class Person
{
    public int Id { get; set; }
    public string Name { get; set; }
    public bool IsLiving { get; set; }
}

public class Person
{
    public int Id { get; set; }
    public string Name { get; set; }
    public bool IsLiving { get; set; }
}

Public Class Person
	Public Property Id() As Integer
	Public Property Name() As String
	Public Property IsLiving() As Boolean
End Class

Within the Main method, let’s now make a list of Person objects which we will write to the CSV file:

var myPersonObjects = new List<Person>()
{
    new Person { Id = 1, IsLiving = true, Name = "John" },
    new Person { Id = 2, IsLiving = true, Name = "Steve" },
    new Person { Id = 3, IsLiving = true, Name = "James" }
};

var myPersonObjects = new List<Person>()
{
    new Person { Id = 1, IsLiving = true, Name = "John" },
    new Person { Id = 2, IsLiving = true, Name = "Steve" },
    new Person { Id = 3, IsLiving = true, Name = "James" }
};

Dim myPersonObjects = New List(Of Person)() From {
	New Person With {
		.Id = 1,
		.IsLiving = True,
		.Name = "John"
	},
	New Person With {
		.Id = 2,
		.IsLiving = True,
		.Name = "Steve"
	},
	New Person With {
		.Id = 3,
		.IsLiving = True,
		.Name = "James"
	}
}

5.2. Writing into a CSV File in C# with Default Settings

CsvHelper has emerged as the standard way to write CSV in C# using the CSVWriter method, and it is very easy to use:

using (var writer = new StreamWriter("filePersons.csv"))
using (var csv = new CsvWriter(writer, CultureInfo.InvariantCulture))
{
    csv.WriteRecords(myPersonObjects);
}

using (var writer = new StreamWriter("filePersons.csv"))
using (var csv = new CsvWriter(writer, CultureInfo.InvariantCulture))
{
    csv.WriteRecords(myPersonObjects);
}

Using writer = New StreamWriter("filePersons.csv")
Using csv = New CsvWriter(writer, CultureInfo.InvariantCulture)
	csv.WriteRecords(myPersonObjects)
End Using
End Using

5.3. Appending Data

We need to keep in mind the fact that every time we run our program, old CSV files will be overwritten. Sometimes we want the data to be appended at the end of a file, instead of overwriting existing data. CSVWriter doesn’t provide a method to do this, as opening/writing a file is not the responsibility of CSVWriter with supplied separator. We can achieve this by using a FileStream, and then constructing our StreamWriter by using that FileStream. Although this does not fall within the scope of this article, I will show you how to do it, as it is an integral part of any application.

var configPersons = new CsvConfiguration(CultureInfo.InvariantCulture)
{
    HasHeaderRecord = false
};
using (var stream = File.Open("filePersons.csv", FileMode.Append))
using (var writer = new StreamWriter(stream))
using (var csv = new CsvWriter(writer, configPersons))
{
    csv.WriteRecords(myPersonObjects);
}

var configPersons = new CsvConfiguration(CultureInfo.InvariantCulture)
{
    HasHeaderRecord = false
};
using (var stream = File.Open("filePersons.csv", FileMode.Append))
using (var writer = new StreamWriter(stream))
using (var csv = new CsvWriter(writer, configPersons))
{
    csv.WriteRecords(myPersonObjects);
}

Dim configPersons = New CsvConfiguration(CultureInfo.InvariantCulture) With {.HasHeaderRecord = False}
Using stream = File.Open("filePersons.csv", FileMode.Append)
Using writer = New StreamWriter(stream)
Using csv = New CsvWriter(writer, configPersons)
	csv.WriteRecords(myPersonObjects)
End Using
End Using
End Using

Appending to an existing file is tricky, however, as the ordering might be changed when we append, or we might have added new properties. We should make sure to take this into account when appending.

Summary

IronXL is a complete library offering everything you need to be able to manipulate an Excel file. It provides you the facility to convert various formats to XLSX, and also from XLSX to other formats, such as CSV. This interconversion provides users the flexibility to manipulate other file formats with ease.

On the other hand, CSVWriter works only with CSV file formats and with very limited options. Users cannot switch between different file formats, whereas with IronXL they can. All the code samples can be found in the CsvHelper documentation files. This document provides guidelines on how to use CSVWriter in your project.

Conclusion

IronXL has a clear advantage over CSVHelper.CSVWriter, as it supports working with multiple formats. Moreover, you can apply formulas and styles according to your choice, whereas CsvHelper only allows for the reading and writing of CSV files, and with limited options. Further, you cannot append new records to an existing CSV file, as it simply overwrites previous entries.