Published April 20, 2023
How to Export Datatable to CSV in C#
Introduction
The chore of working with CSV (Comma Separated Values) files might be difficult. Today, there are a number of libraries that can assist developers with this task without the requirement for Microsoft Excel to be installed. Using one of the most well-liked libraries, IronXL, we will examine and contrast how to interact with Microsoft Excel documents programmatically in C# .NET technologies, either in the CSV format or the default Excel format. It is possible to register types and have the library, convert them automatically to CSV files, as well as to writing CSV files with custom structures.
IronXL Library Features
Microsoft Excel documents can be read and converted to CSV files using 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 Form, 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.
- With IronXL, we 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, we'll be using the Console Application for this lesson.
After that, input the project's name and location.
Select the following structure next. Dot Net Core 6 will be used in this undertaking.
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 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 our app.
The image below demonstrates how to launch Visual Studio's NuGet Package Manager.
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.
Downloading IronXL Directly from the NuGet Website
The NuGet package can be downloaded straight from the website as a third option.
- Explore the Link.
- The download package choice can be found in the menu on the right.
- Click the saved file twice. It will immediately install.
- 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. 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, we must include the IronXL namespace. We can use the IronXL classes and methods on our code once it has been introduced.
Excel files can be created using IronXL, which then transforms them into WorkBook objects. We can 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 SubWe are exporting the DataTable to an Excel file in the above code example. Column headings are created once we have created a DataTable. We add the rows one at a time after establishing the first column. We build the WorkBook object after adding the column and rows to the DataTable object. We may use the WorkBook object to add data to an Excel sheet, after which we can save the data somewhere. In order to create worksheets, we are constructing the WorkSheet object, which we can then add to the WorkBook object.
We read each value from the DataTable using a for each loop before adding the value to the WorkSheet. After all the values have been added to the worksheet, we use the SaveAsCsv method to save them into a CSV file; at the same time, we can also give the delimiter and file name with location as parameters. If a delimiter is optional, we can ignore it as an optional parameter. Then the library helps to Writing data to a CSV file. Reading a CSV file using MS Excel is not an only option, we also read them using Notepad.
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 know more about the IronXL tutorial click here.
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 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 read and write csv files and helps you to work like excel data.
IronXL's starting price at launch is $499. 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 approximately the pricing information, click here
