How to Export Datatable to CSV in C#

Introduction

IronXL is a popular library that allows developers to interact with Microsoft Excel documents in C# .NET technologies, including CSV files, without needing Microsoft Excel installed. It enables the automatic conversion of registered types to CSV files and the writing of CSV files with custom structures.

IronXL Library Features

Microsoft Excel documents can be read and converted to CSV files using the C# IronXL .NET library. IronXL is a standalone .NET software library that can read a variety of spreadsheet formats. It does not depend on Microsoft.Office.Interop.Excel or require the installation of Microsoft Excel.

With the help of the user-friendly C# API of IronXL, you can quickly read, modify, and create Excel spreadsheet files in the .NET environment. .NET Core, .NET Framework, Xamarin, Mobile, Linux, macOS, and Azure are all fully supported by IronXL.

• Leading .NET Core and .NET Framework Excel spreadsheet libraries for C# include IronXL.
• Virtually all of the .NET Frameworks, including the Console, Windows Forms, and Web, are supported by IronXL.
• IronXL operates on a variety of operating systems, including Windows, Linux, and macOS.
• IronXL makes it simple and quick to read Excel files.
• IronXL supports reading different Excel file formats including XLSX files, XLS, CSV, TSV, XLST, XLSM files, and more. We can also load, modify, export Datatables, export datasets, and more.
• IronXL can export and save files with a wide range of suffixes, including XLS, CSV, TSV, JSON, and others.
• IronXL can generate Excel calculations.
• IronXL supports a variety of Excel column data formats, including text, numbers, formulas, dates, currencies, and percentages.

For more details visit here.

1. Creating a New Project in Visual Studio

In Visual Studio, a.NET project must be created before the IronXL framework can be used. Any edition of Visual Studio will work, but the most recent one is advised. Depending on your needs, you can build a Windows Forms-like application or different project templates. To keep things simple, this lesson will use the Console Application.

Create a new project in Visual Studio

After that, input the project's name and location.

Configure the new project

Select the following structure next. .NET Core 6 will be used in this undertaking.

Select a .NET Framework version

The program.cs file will be opened after the application generates the solution so that you can enter the program code and build/run the application.

The newly created Console Application project

The library can then be added and used to evaluate the code.

2. Install the IronXL Library

There are four methods to download and install the IronXL Library.

Which are:

• Installing via Visual Studio
• Installing using the Visual Studio Package Manager Console
• Downloading directly from the NuGet website
• Downloading directly from the IronXL website

Installing IronXL using Visual Studio

Using NuGet Package Manager, the IronXL module can be installed. To find IronXL, you must first launch the NuGet Package Manager and then look in the browse pane. Install IronXL by choosing it from the search listings. After that, the IronXL library will be able to use this app.

The image below demonstrates how to launch Visual Studio's NuGet Package Manager.

Navigate to NuGet Package Manager

Install IronXL package in NuGet Package Manager UI

Installing IronXL using the Visual Studio NuGet Package Manager Console

Many individuals enjoy using a console to carry out tasks. So, a terminal installation is also an option. To install IronXL using the command line, adhere to the instructions below.

• Navigate to Tools > NuGet Package Manager > Package Manager interface in Visual Studio.

• Input the following command into the console tab of the package manager:

  Install-Package IronXL.Excel

• Wait for IronXL to be downloaded and installed into the active project.

Install th IronXL package in Package Manager Console UI

Downloading IronXL Directly from the NuGet Website

The NuGet package can be downloaded straight from the website as a third option.

• Explore the official NuGet link.
• The download package choice can be found in the menu on the right.
• Click the saved file twice. It will immediately be installed.
• Reload the answer after that and begin utilizing it in the project.

Getting directly from the IronXL website

To download the most recent package straight from the website, click this link to download an IronXL ZIP file. This link will download a ZIP file containing the latest version of the IronXL library DLL. Once the download finishes, extract the contents of the ZIP file to any directory of your choosing.

To add the file to the project after downloading, adhere to the steps listed below.

• From the solution window, right-click the document.
• Select References, and then navigate to the extracted folder containing the IronXL DLLs.
• Select the DLL, and click OK to add it to the active project as a Reference.

3. Export to CSV File

DataTables can be easily and quickly created to CSV files using IronXL. It helps write data into a new CSV file.

First, as shown in the code image below, the IronXL namespace should be included to use the IronXL classes and methods.

Add common namespaces

Excel files can be created using IronXL, which then transforms them into WorkBook objects. Then conduct a variety of operations on them after turning them into objects. The sample code below will build an Excel file by converting a DataTable into an Excel worksheet.

using IronXL;
using IronXL.Options;
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_Fruits", typeof(string));
    table.Rows.Add("Apple");
    table.Rows.Add("Orange");
    table.Rows.Add("strawberry");
    table.Rows.Add("grapes");
    table.Rows.Add("watermelon");
    table.Rows.Add("bananas");
    table.Rows.Add("lemons");

    WorkBook wb = WorkBook.Create(ExcelFileFormat.XLS);
    var writer = wb.DefaultWorkSheet;
    int rowCount = 1;
    foreach (DataRow row in table.Rows)
    {
        writer ["A" + (rowCount)].Value = row [0].ToString();
        rowCount++;
    }
    wb.SaveAsCsv(filepath, ";");
}

using IronXL;
using IronXL.Options;
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_Fruits", typeof(string));
    table.Rows.Add("Apple");
    table.Rows.Add("Orange");
    table.Rows.Add("strawberry");
    table.Rows.Add("grapes");
    table.Rows.Add("watermelon");
    table.Rows.Add("bananas");
    table.Rows.Add("lemons");

    WorkBook wb = WorkBook.Create(ExcelFileFormat.XLS);
    var writer = wb.DefaultWorkSheet;
    int rowCount = 1;
    foreach (DataRow row in table.Rows)
    {
        writer ["A" + (rowCount)].Value = row [0].ToString();
        rowCount++;
    }
    wb.SaveAsCsv(filepath, ";");
}

Imports IronXL
Imports IronXL.Options
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_Fruits", GetType(String))
	table.Rows.Add("Apple")
	table.Rows.Add("Orange")
	table.Rows.Add("strawberry")
	table.Rows.Add("grapes")
	table.Rows.Add("watermelon")
	table.Rows.Add("bananas")
	table.Rows.Add("lemons")

	Dim wb As WorkBook = WorkBook.Create(ExcelFileFormat.XLS)
	Dim writer = wb.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
	wb.SaveAsCsv(filepath, ";")
End Sub

The above code exports the DataTable to an Excel file. Column headings are created once a DataTable is created. Then, add the rows one at a time after establishing the first column. The WorkBook object is created after adding the columns and rows to the DataTable object to hold those data. The WorkSheet object is then constructed, which add to the WorkBook object.

Each value from the DataTable is read and added using a foreach loop before adding the value to the WorkSheet. After all the values have been added to the worksheet, the SaveAsCsv method is used to save them into a CSV file; at the same time, we can also give the delimiter and file name with location as parameters.

The output CSV file

The output of the run code sample is shown above. In the screenshot, each piece of data from the data table has been individually added to the newly formed Excel sheet.

To learn more about the IronXL tutorial click on this how-to export to Excel formats.

Conclusion

One of the most popular Excel tools is IronXL. It doesn't rely on any other libraries from outside sources. It is autonomous and does not require the installation of Microsoft Excel. It operates across numerous channels.

IronXL provides an all-in-one solution for all Microsoft Excel document-related tasks to be implemented programmatically. You can perform formula calculation, string or number sorting, trimming and appending, find and replace, merge and unmerge, save files etc. You can also set cell data formats along with validate spreadsheet data. It also supports reading and writing CSV files and helps you to work like Excel data.

IronXL's starting price at launch is $749. It also offers users the choice of paying a one-year subscription fee for product assistance and updates. For an additional fee, IronXL offers security for unrestricted redistribution. To research greater approximate pricing information, please visit this licensing page