Datatable to Excel in C# (Code Example Tutorial)

It can be tough to programmatically alter an Excel spreadsheet, for two main reasons. First, when dealing with spreadsheets, users must maintain a consistent interface and document structure, which is not always straightforward. Second, it's not always clear which code will work in specific situations, particularly when sophisticated computations are involved. There are numerous reasons why programmatically creating an Excel file is difficult. Programmers must first transform the data in the Excel document into a readable format, then parse the data and parse it back, which is even more difficult. I've come up with a solution to this issue. I'll teach you how to make an Excel file and programmatically export a Datatable to an Excel sheet without any problem using the IronXL .NET Excel library. Let's get started:

IronXL Features

• IronXL runs on a variety of Operating Systems, including Windows, Linux, and macOS.
• Almost all of the .NET Frameworks are supported by IronXL, including the Console, Windows Forms Application, and Web Application.
• IronXL makes reading Excel files straightforward and easy.
• We can load, edit, export Datatables, export datasets, and read Excel file formats such as XLSX files, XLS, CSV, TSV, XLST, XLSM files, and more with IronXL.
• IronXL allows us to save and export files with a variety of extensions, including XLS, CSV, TSV, JSON, XLSX, and others.
• Excel formulas can be created by IronXL.
• Text, Numbers, Formulas, Dates, Currency, Percentages, and other Excel cell data formats are supported by IronXL.
• It also provides numerous sorting options such as Range, Columns, and Rows, among others.
• Cell styling in IronXL includes font, size, background pattern, border, and alignment, among others.
• We can read and export data from an Excel file using IronXL.
• IronXL does not require Excel or Interop to be installed on your server. The IronXL API is faster and easier to use than Microsoft Office, Interop, and Excel.

1 Creating a New Project in Visual Studio

To use the IronXL library, we must create a .NET project in Visual Studio. You can use any version of Visual Studio, but the latest version is recommended. You can create an application like Windows Forms or different project templates, depending on your requirements. I will use the Console Application for this tutorial, for simplicity.

Create a new project UI in Visual Studio

Next, enter the project name and the location of the project.

Configuration step in creating a new project

Next, select the framework below. In this project, we are going to use .NET Core 6.

.Net Framework selection UI

Once the Application creates the solution, it will open the program.cs file where you can enter the code and build/run the application.

Empty program.cs file on the new project

Next, we can add the library to test the code.

2 Install the IronXL Library

The IronXL Library can be downloaded and installed in four ways.

These are:

• Using Visual Studio
• Using the Visual Studio Command Line.
• Direct download from the NuGet website.
• Direct download from the IronXL website.

2.1 Using Visual Studio

We can install the IronXL library using NuGet Package Manager. You must first open the NuGet Package manager and then search for IronXL in the browse tab. Select IronXL from the search results and install it. After that, our project will be ready to use in the IronXL library.

The below screenshot shows how we can open the NuGet Package Manager in Visual Studio.

Navigate to NuGet Package Manager in Visual Studio

IronXL in search results:

Search for IronXL library

2.2 Using the Visual Studio Command-Line

Many people love to use a Console to perform operations. So, we can install it by Console too. Follow the following steps to install IronXL by the command line.

• In Visual Studio, go to Tools-> NuGet Package Manager -> Package Manager Console.
• Enter the following line in the Package Manager Console tab:

Install-Package IronXL.Excel

Now the package will download/install to the current project and be ready to use.

Install IronXL package via Console

2.3 Direct download from the NuGet website

The third way is to download the NuGet package directly from the website.

• Navigate to the Nuget official IronXL package page.
• Select the download package option from the menu on the right-hand side.
• Double-click the downloaded package. It will be installed automatically.
• Next, reload the solution and start using it in the project.

2.4 Direct download from the IronXL website

Click the link to download the latest package directly from the website. Once downloaded, follow the steps below to add the package to the project.

• Right-click the project from the solution window.
• Then, select the options reference and browse the location of the downloaded reference.
• Next, click ok to add the reference.

3 Export Datatable to Excel File using IronXL

IronXL allows us to export Datatables to Excel files easily with a limited number of steps.

First, we need to include the namespace of IronXL as in the code screenshot below. Once it is added, we can use the IronXL classes and methods on our code.

Include namespace of IronXL before using the library

IronXL allows us to create Excel files and convert them into workbook objects. After converting them into objects, we can perform various types of operations. In the below sample code, we will convert a Datatable into an Excel worksheet, and then we can create an Excel file.

public static void ExportToExcel(string filePath)
{
    DataTable table = new DataTable();
    table.Columns.Add("DataSet_Animal", typeof(string));
    table.Rows.Add("Lion");
    table.Rows.Add("Tiger");
    table.Rows.Add("Cat");
    table.Rows.Add("Goat");
    table.Rows.Add("Panther");
    table.Rows.Add("Fox");
    table.Rows.Add("Cheetah");
    WorkBook wb = WorkBook.Create(ExcelFileFormat.XLS);
    WorkSheet ws = wb.DefaultWorkSheet;
    int rowCount = 1;
    foreach (DataRow row in table.Rows)
    {
        ws ["A" + (rowCount)].Value = row [0].ToString();
        rowCount++;
    }
    wb.SaveAsCsv(filePath, ";");
}

public static void ExportToExcel(string filePath)
{
    DataTable table = new DataTable();
    table.Columns.Add("DataSet_Animal", typeof(string));
    table.Rows.Add("Lion");
    table.Rows.Add("Tiger");
    table.Rows.Add("Cat");
    table.Rows.Add("Goat");
    table.Rows.Add("Panther");
    table.Rows.Add("Fox");
    table.Rows.Add("Cheetah");
    WorkBook wb = WorkBook.Create(ExcelFileFormat.XLS);
    WorkSheet ws = wb.DefaultWorkSheet;
    int rowCount = 1;
    foreach (DataRow row in table.Rows)
    {
        ws ["A" + (rowCount)].Value = row [0].ToString();
        rowCount++;
    }
    wb.SaveAsCsv(filePath, ";");
}

Public Shared Sub ExportToExcel(ByVal filePath As String)
	Dim table As New DataTable()
	table.Columns.Add("DataSet_Animal", GetType(String))
	table.Rows.Add("Lion")
	table.Rows.Add("Tiger")
	table.Rows.Add("Cat")
	table.Rows.Add("Goat")
	table.Rows.Add("Panther")
	table.Rows.Add("Fox")
	table.Rows.Add("Cheetah")
	Dim wb As WorkBook = WorkBook.Create(ExcelFileFormat.XLS)
	Dim ws As WorkSheet = wb.DefaultWorkSheet
	Dim rowCount As Integer = 1
	For Each row As DataRow In table.Rows
		ws ("A" & (rowCount)).Value = row (0).ToString()
		rowCount += 1
	Next row
	wb.SaveAsCsv(filePath, ";")
End Sub

In the above code, we are exporting the data table to an Excel file. First, we are creating a DataTable, and then we are creating column headers. After creating the columns, we add the columns one by one. Once we have added the column, then we create the WorkBook object. By using the object, we can add the data to the Excel sheet, and then we can save the data to the location. We are creating the WorkSheet object which allows for creating a worksheet, and then we can add this to the WorkBook object.

We are using foreach loop to read the data table values one by one and then we add the value to the worksheet. Once all the values are added to the worksheet, we then save them into a CSV file using the method called SaveAsCSV — we need to pass the delimiter and file name with location as a parameter. A delimiter is an optional parameter we can ignore if it is not required.

The completed code in Visual Studio

The above is the full screenshot of the code which we are using on the .NET core 6.

Result:

The result when opening the file in Microsoft Excel

Above is the result of the executed code sample. In the screenshot, all the data available on the data table has been added to the newly created Excel sheet one by one.

4 Conclusion

IronXL is one of the most commonly used Excel libraries. It does not depend on any other third-party library. It is independent and does not need MS Excel to be installed. It works on multiple platforms. The introductory price for IronXL starts at $749. Also, it provides the option of a one-year fee for product support and updates. IronXL provides royalty-free redistribution coverage at an extra cost. To know more about the price details, visit our licensing page.