How to Export to CSV in .NET Core

1.0 Introduction

There are spreadsheet apps for Windows, macOS, iOS, Android, and iPadOS, such as Microsoft Excel. The curriculum includes lessons on pivot tables, graphing tools, mathematical concepts, and the macro programming language Visual Basic for Applications (VBA). One of the programs in the Microsoft 365 software package is Excel. Spreadsheets like Excel use a grid of cells arranged in numbered rows and letter-named columns to organize data manipulations, including arithmetic calculations. It provides integrated capabilities to satisfy domain requirements in the financial, statistical, and technological domains. It features a constrained three-dimensional graphical presentation and can also display data as charts, line graphs, and histograms. Data can be divided into segments to demonstrate how different things impact it from different angles.

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 our 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."

How to Export to CSV in .NET Core: Figure 1 - Open Visual Studio, go to File menu and select New Project. From the various .NET project templates choose the Console App.

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.

How to Export to CSV in .NET Core: Figure 2 - Enter the appropriate Project name and set the project's location path. Then click on the Next button to continue.

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.

How to Export to CSV in .NET Core: Figure 3 - Select an appropriate .NET Framework using the Framework drop-down. In this instance, the long-term supported version of .NET 6.0 is selected. Finally, click on the Create button to successfully create the console app project.

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

How to Export to CSV in .NET Core: Figure 4 - For installing the IronXL library, you can use the Package Manager Console and enter the given command: Install-Package IronXL.Excel

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, we can select the required package to download.

How to Export to CSV in .NET Core: Figure 5 - Another way to install IronXL is by using the NuGet Package Manager for Solutions. Enter ironxl in the search box to search for the IronXL library.

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. We must first include the IronXL namespace, as seen in the code screenshot below. Once IronXL is presented, we can use its classes and methods in our code.

How to Export to CSV in .NET Core: Figure 6 - Include the IronXL namespace within your program file using the command using IronXL. This ensures that we can use all the classes and methods of the IronXL library into our code.

IronXL can be used to create Excel files, which are subsequently converted into workbook objects. After they become objects, we can work on them in a number of ways. By transforming a DataTable into an Excel worksheet, the sample source code below creates an Excel file.

using IronXL;
using System.Data;

static void main(String[] arg)
{
    exporttoexcel("H:\\test.csv");
}
public static void exporttoexcel(string filepath)
{
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");
var workbook = WorkBook.Create(ExcelFileFormat.XLS);
var writer = workbook.DefaultWorkSheet;
int rowCount = 1;
foreach (DataRow row in table.Rows)
{
    writer["A" + (rowCount)].Value = row[0].ToString();
    rowCount++;
}
workbook.SaveAsCsv(filepath, ";");
//or 
var stream= workbook.ToStream();
}
using IronXL;
using System.Data;

static void main(String[] arg)
{
    exporttoexcel("H:\\test.csv");
}
public static void exporttoexcel(string filepath)
{
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");
var workbook = WorkBook.Create(ExcelFileFormat.XLS);
var writer = workbook.DefaultWorkSheet;
int rowCount = 1;
foreach (DataRow row in table.Rows)
{
    writer["A" + (rowCount)].Value = row[0].ToString();
    rowCount++;
}
workbook.SaveAsCsv(filepath, ";");
//or 
var stream= workbook.ToStream();
}
Imports IronXL
Imports System.Data

Shared Sub main(ByVal arg() As String)
	exporttoexcel("H:\test.csv")
End Sub
Public Shared Sub exporttoexcel(ByVal filepath As String)
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")
Dim workbook = WorkBook.Create(ExcelFileFormat.XLS)
Dim writer = workbook.DefaultWorkSheet
Dim rowCount As Integer = 1
For Each row As DataRow In table.Rows
	writer("A" & (rowCount)).Value = row(0).ToString()
	rowCount += 1
Next row
workbook.SaveAsCsv(filepath, ";")
'or 
Dim stream= workbook.ToStream()
End Sub
VB   C#

The above CSV example will allow us 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, we add the rows one at a time. After adding the rows and columns to the DataTable object, we construct the workbook object. The workbook object can be used to add data to an Excel sheet, which can then be saved elsewhere. We are building the worksheet object, which we can add to the workbook object, in order to generate worksheets. Also, we can use the model class to generate the files.

Before adding the value to the worksheet, we use a for each loop 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. It allows us to pass in parameters for the delimiter and file name with location. We can disregard a delimiter as an optional argument if it is not required. The library then assists in writing data to a CSV file. There is another way to read CSV files besides Microsoft Excel: we may use Notepad. Also, we can use the method Save() which allows us to save the same into the given file format.

How to Export to CSV in .NET Core: Figure 7 - 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, or we can convert the workbook into a stream. We can use the stream on the web application to return file 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 MS 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 free trial license which gives users an opportunity 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. In order to look up more precise pricing data, please visit IronXL's license page.