How to Export to CSV in .NET Core

1.0 Introduction

One of the most well-known libraries, IronXL, will be used in this article to contrast and compare different ways that .NET technologies can interface programmatically with Microsoft Excel documents. It will also build an environment for reading, writing, and exporting Excel spreadsheets to CSV files.

2.0 IronXL

The IronXL for .NET, a C# Excel library, can be used to read and convert Microsoft Excel documents to CSV files. IronXL is a stand-alone .NET software library that may be used without the installation of Microsoft Office or Microsoft.Office.Interop.Excel. It can read a variety of spreadsheet formats.

Excel spreadsheets may be read, edited, and generated with ease in a .NET context thanks to IronXL's straightforward C# API. Xamarin, Linux, macOS, Azure, .NET Core, and .NET Framework are all fully supported by IronXL.

2.1 IronXL Library Features

• Among the greatest C# libraries for Excel spreadsheets is IronXL, which works with both .NET Core and .NET Framework.
• Virtually all .NET Frameworks are supported by IronXL, including console, Windows Forms, and Web Applications.
• Windows, macOS, and Linux are all compatible with IronXL.
• Excel files may be accessed quickly and easily using IronXL.
• Excel file formats like XLSX files, CSV files, XLS, XSLT, TSV, XLSM, and others, can be read by IronXL. Among many possibilities are functions for importing, updating, and exporting datasets and data tables.
• Calculations for the Excel spreadsheet can be generated by IronXL.
• For Excel columns, IronXL supports a wide range of data types, such as text, integers, dates, currencies, formulas, and percentages.
• Dates, currencies, percentages, text, numbers, formulas, and other Excel column data types are all supported by IronXL.

3.0 Creating a .NET Core 6 Project

You will see how easy it is to build a CSV file using the IronXL library in the following sections of this newsletter.

Step 1: Start a new project to generate a CSV file.

Open Visual Studio and choose "New Project" from the "File" menu.

Choose the "Console App" .NET Project templates from the ensuing dialogue box, then click "Next".

Create a new Console Application in Visual Studio

You can enter whatever name you want for the "Project name". Once the location of the new project has been provided in the "Location" section, click on the Next button to continue.

Configure the new project

The Framework drop-down menu can be used to choose a .NET Framework. In this case, the long-term supported version of .NET is 6.0. Next, click on the Create button.

.NET target framework selection

Install the IronXL library, as it is necessary for the subsequent resolution. Enter the following command into the Package Manager Console to accomplish this:

Install-Package IronXL.Excel

Install the IronXL package

An alternative is to search for the package "IronXL" using the NuGet Package Manager. Within the Browse tab, enter "IronXL" in the search box to search for the IronXL library. From this list of all the NuGet packages related to IronXL, then select the required package to download.

Search and install the IronXL package in NuGet Package Manager UI

4.0 Export data to CSV file

With IronXL, creating data tables to CSV files is simple and rapid. It facilitates writing data to a fresh CSV file. The first step is to include the IronXL namespace, as seen in the code screenshot below. Once IronXL is presented, then its classes and methods can be used in code.

Include the IronXL namespace

IronXL can be used to create Excel files, which are subsequently converted into WorkBook objects. After they become objects from WorkBook class, it is possible to work on them in several ways. By transforming a DataTable into an Excel worksheet, the sample source code below creates an Excel file.

using IronXL;
using System.Data;

class Program
{
    static void Main(string[] args)
    {
        ExportToExcel("test.csv");
    }

    // Method to export a DataTable to an Excel sheet which is then saved as a CSV file
    public static void ExportToExcel(string filepath)
    {
        // Create a DataTable and add columns & rows
        DataTable table = new DataTable();
        table.Columns.Add("DataSet_Animals", typeof(string));
        table.Rows.Add("Lion");
        table.Rows.Add("Tiger");
        table.Rows.Add("Leopard");
        table.Rows.Add("Cheetah");
        table.Rows.Add("Hyenas");

        // Create a new workbook and get the default worksheet
        var workbook = WorkBook.Create(ExcelFileFormat.XLS);
        var worksheet = workbook.DefaultWorkSheet;

        // Add table data to worksheet
        int rowCount = 1;
        foreach (DataRow row in table.Rows)
        {
            worksheet["A" + rowCount].Value = row[0].ToString();
            rowCount++;
        }

        // Save worksheet data to a CSV file
        workbook.SaveAsCsv(filepath, ";");

        // Optionally convert workbook to a stream, useful for web applications
        var stream = workbook.ToStream();
    }
}

using IronXL;
using System.Data;

class Program
{
    static void Main(string[] args)
    {
        ExportToExcel("test.csv");
    }

    // Method to export a DataTable to an Excel sheet which is then saved as a CSV file
    public static void ExportToExcel(string filepath)
    {
        // Create a DataTable and add columns & rows
        DataTable table = new DataTable();
        table.Columns.Add("DataSet_Animals", typeof(string));
        table.Rows.Add("Lion");
        table.Rows.Add("Tiger");
        table.Rows.Add("Leopard");
        table.Rows.Add("Cheetah");
        table.Rows.Add("Hyenas");

        // Create a new workbook and get the default worksheet
        var workbook = WorkBook.Create(ExcelFileFormat.XLS);
        var worksheet = workbook.DefaultWorkSheet;

        // Add table data to worksheet
        int rowCount = 1;
        foreach (DataRow row in table.Rows)
        {
            worksheet["A" + rowCount].Value = row[0].ToString();
            rowCount++;
        }

        // Save worksheet data to a CSV file
        workbook.SaveAsCsv(filepath, ";");

        // Optionally convert workbook to a stream, useful for web applications
        var stream = workbook.ToStream();
    }
}

Imports IronXL
Imports System.Data

Friend Class Program
	Shared Sub Main(ByVal args() As String)
		ExportToExcel("test.csv")
	End Sub

	' Method to export a DataTable to an Excel sheet which is then saved as a CSV file
	Public Shared Sub ExportToExcel(ByVal filepath As String)
		' Create a DataTable and add columns & rows
		Dim table As New DataTable()
		table.Columns.Add("DataSet_Animals", GetType(String))
		table.Rows.Add("Lion")
		table.Rows.Add("Tiger")
		table.Rows.Add("Leopard")
		table.Rows.Add("Cheetah")
		table.Rows.Add("Hyenas")

		' Create a new workbook and get the default worksheet
		Dim workbook = WorkBook.Create(ExcelFileFormat.XLS)
		Dim worksheet = workbook.DefaultWorkSheet

		' Add table data to worksheet
		Dim rowCount As Integer = 1
		For Each row As DataRow In table.Rows
			worksheet("A" & rowCount).Value = row(0).ToString()
			rowCount += 1
		Next row

		' Save worksheet data to a CSV file
		workbook.SaveAsCsv(filepath, ";")

		' Optionally convert workbook to a stream, useful for web applications
		Dim stream = workbook.ToStream()
	End Sub
End Class

The above CSV example shows how to export the DataTable into a CSV file. After a DataTable has been established, i.e., column headings are created, and once the first column is established, the rows are added one at a time. After adding the rows and columns to the DataTable object, the WorkBook object is constructed. The WorkBook object is used to add data to an Excel sheet, which can then be saved elsewhere. The next step is to initiate the WorkSheet object, which is linked to the workbook object.

Before adding the value to the worksheet, a foreach loop is used to read each value from the DataTable. The SaveAsCsv function is used to save the data into a CSV file once they have all been put into the worksheet using the file name as a parameter. A delimiter can be used as an optional argument if it is required. The library then assists in writing data to a CSV file. There is another way to read CSV files besides Microsoft Excel using Notepad. Also, the method Save is used to save the same into the given file format.

OUTPUT Excel file test.csv

Above is the output of the code sample that was run. Every piece of information from the data table has been separately added to the freshly created Excel sheet in the screenshot. Alternatively, it can also be converted into a stream as part of the Web Application to return files that can be downloaded from the client-side.

For more information on exporting data from DataTable to Excel, please check this tutorial page.

To know more about how to export data into Excel, please refer to this step-by-step tutorial.

5.0 Conclusion

IronXL is among the most widely used Excel utilities. It is not dependent on any other external libraries. It is self-contained and doesn't need Microsoft Excel installed. Furthermore, it functions through a variety of channels.

For all programmatically implemented Microsoft Excel document-related operations, IronXL offers a comprehensive solution. Calculating formulas, sorting strings or numbers, trimming, appending, finding and replacing, merging and unmerging, saving files, and more are all possible. Together with validating spreadsheet data, you can also establish cell data types. It facilitates working with Excel data and allows you to read and write files.

IronXL offers a free trial license, which allows users to try out and test all its amazing features for free.

At launch, IronXL is available for $749. Users can also opt to pay a one-year subscription fee to receive updates and product assistance. IronXL provides security for unrestricted redistribution for an extra charge. To look up more precise pricing data, please visit IronXL's license page.