USING IRONXL

How to Convert Dataset to Excel in C#

This article will explore how to use C# to export data from a dataset to an Excel spreadsheet using the market-leading C# Excel library, IronXL.

1. IronXL

IronXL is a popular C# library that provides developers with the ability to read, write, and manipulate Excel files programmatically. With its simple and intuitive interface, IronXL makes it easy for developers to integrate Excel functionality into their C# applications. This powerful library supports various Excel file formats, including XLSX, XLS, and CSV, and offers a wide range of features such as cell styling, formula calculation, data validation, and charting.

With IronXL, developers can automate tasks related to Excel, such as importing data from external sources, generating reports, and exporting data to different file formats. Additionally, IronXL is highly efficient and capable of handling large Excel files with ease.

2. Prerequisites

To export data from a DataTable to Excel using the IronXL library, you need to meet a few prerequisites:

  1. Visual Studio: You must have Visual Studio installed on your system to create a C# project. If you don't have it installed, you can download it from the Microsoft website.
  2. ASP.NET: Before creating a C# project, ensure that ASP.NET is installed on your system. You can check for this by navigating to the Control Panel and searching for the "Turn Windows features on or off" option. Make sure that the ASP.NET option is enabled.
  3. IronXL Library: The IronXL library must be installed to export data from a DataTable to an Excel file. You can install it by downloading the IronXL NuGet package from the NuGet Package Manager within Visual Studio.

3. Creating a New Project on Visual Studio

To utilize the IronXL library with Excel, you need to create a .NET project in Visual Studio. You can use any version of Visual Studio, but it is recommended to use the latest version available. Depending on your requirements, you can choose from various project templates, such as Windows Forms or ASP.NET. For this tutorial, the Console Application project template is recommended to demonstrate the usage of IronXL.

How to Convert Dataset to Excel in C#, Figure 1: Create a New Project Window Create a New Project Window

After selecting the project type, provide a name for the project and designate its location. Choose the desired framework, such as .NET Core 6, for the project.

How to Convert Dataset to Excel in C#, Figure 2: Project configuration Project configuration

Once the solution is created, the program.cs file will be opened, enabling you to enter code and construct/run the application.

How to Convert Dataset to Excel in C#, Figure 3: Project with code open Project with code open

Finally, you can incorporate the library to test the code.

4. Install IronXL

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

These are:

  • Using Visual Studio NuGet packages
  • Using the Visual Studio Command-Line.

4.1 Using Visual Studio

To install the IronXL library, the simplest approach is to use the NuGet Package Manager in Visual Studio. Simply open the NuGet Package Manager and search for IronXL in the Browse tab. Once you have located IronXL in the search results, select it and proceed with the installation. Once the installation is complete, you can start using the IronXL library in your project.

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

How to Convert Dataset to Excel in C#, Figure 4: NuGet Package Manager NuGet Package Manager

IronXL in search results:

How to Convert Dataset to Excel in C#, Figure 5: IronXL search result IronXL search result

4.2 Using the Visual Studio Command-Line

Many developers prefer to install packages using the command line interface. To install IronXL using the command line, follow these steps:

  • 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.

How to Convert Dataset to Excel in C#, Figure 6: Installing via command line Installing via command line

5. Dataset to Excel file using IronXL

Creating an Excel file from a dataset using IronXL is straightforward, and anyone familiar with setting up the environment and running a program can easily convert a dataset to an Excel file with ease. The following section will explore how to convert data from CSV and XML file formats to an Excel sheet within the Microsoft Excel application using IronXL.

5.1. Convert Dataset CSV file to XLSX file format

The following code example shows how to convert CSV files to XLSX file format using IronXL in C# with just a few lines of code.

// Import the IronXL namespace for Excel operations
using IronXL;

// Load a CSV file into a workbook using IronXL, specifying the output Excel format as XLSX
WorkBook workbook = WorkBook.LoadCSV("demo.csv", fileFormat: ExcelFileFormat.XLSX, ListDelimiter: ",");

// Save the workbook as an Excel file in XLSX format
workbook.SaveAs("Csv_To_Excel.xlsx");
// Import the IronXL namespace for Excel operations
using IronXL;

// Load a CSV file into a workbook using IronXL, specifying the output Excel format as XLSX
WorkBook workbook = WorkBook.LoadCSV("demo.csv", fileFormat: ExcelFileFormat.XLSX, ListDelimiter: ",");

// Save the workbook as an Excel file in XLSX format
workbook.SaveAs("Csv_To_Excel.xlsx");
' Import the IronXL namespace for Excel operations
Imports IronXL

' Load a CSV file into a workbook using IronXL, specifying the output Excel format as XLSX
Private workbook As WorkBook = WorkBook.LoadCSV("demo.csv", fileFormat:= ExcelFileFormat.XLSX, ListDelimiter:= ",")

' Save the workbook as an Excel file in XLSX format
workbook.SaveAs("Csv_To_Excel.xlsx")
$vbLabelText   $csharpLabel

5.1.1. Input CSV file

How to Convert Dataset to Excel in C#, Figure 7: CSV file CSV file

5.1.2. Output XLSX file

How to Convert Dataset to Excel in C#, Figure 8: Output spreadsheet file Output spreadsheet file

You can also convert a CSV file to an XLS file by modifying a single line of code in the above example.

5.2. Convert Dataset CSV file to XLS file format

To convert a CSV file to an XLS file, replace the last line of code with this line, and the output file that gets generated will be in XLS format.

// Save the workbook as an Excel file in XLS format
workbook.SaveAs("Csv_To_Excel.xls");
// Save the workbook as an Excel file in XLS format
workbook.SaveAs("Csv_To_Excel.xls");
' Save the workbook as an Excel file in XLS format
workbook.SaveAs("Csv_To_Excel.xls")
$vbLabelText   $csharpLabel

How to Convert Dataset to Excel in C#, Figure 9: Spreadsheet file Spreadsheet file

6. Conclusion

Exporting a dataset to an Excel spreadsheet using C# is a crucial functionality that can save developers significant time and effort. Fortunately, IronXL, a powerful C# library, makes this task easy by providing capabilities to read, write, and manipulate Excel files programmatically. With IronXL, developers can automate various Excel-related tasks, including importing data from external sources, generating reports, and exporting data to different file formats.

To use IronXL with Excel, developers need to create a .NET project in Visual Studio, install the IronXL library, and then convert the dataset to an Excel file. This process is straightforward, and with just a few lines of code, developers can convert a CSV file or any other data format to an Excel file in either XLS or XLSX format.

Please visit another tutorial for a detailed tutorial on how to convert CSV to Excel using IronXL.

To gain more insights about IronXL and how to export a DataTable to various Excel formats, please visit the following blog for DataTable instructions. Additionally, you can refer to the pricing plans to obtain detailed information about prices and licensing.

Users can also benefit from Iron Suite, a suite of five professional libraries including IronXL, IronPDF, and others.

Frequently Asked Questions

What is this library used for Excel operations in C#?

IronXL is a popular C# library that enables developers to read, write, and manipulate Excel files programmatically. It supports various Excel file formats, including XLSX, XLS, and CSV, and offers features such as cell styling, formula calculation, data validation, and charting.

What are the prerequisites for exporting data from a DataTable to Excel using this library?

To export data from a DataTable to Excel using IronXL, you need to have Visual Studio and ASP.NET installed, as well as the IronXL library. Visual Studio can be downloaded from the Microsoft website, and the IronXL library can be installed via the NuGet Package Manager in Visual Studio.

How can I create a new project in Visual Studio to use this Excel library?

To create a new project in Visual Studio for using IronXL, open Visual Studio and select a project template, such as Console Application. Provide a project name and location, and select a framework like .NET Core 6. Once created, open the program.cs file to enter your code.

How do I install this Excel library in Visual Studio?

You can install IronXL in Visual Studio by using the NuGet Package Manager. Open the manager, search for IronXL in the Browse tab, and install it. Alternatively, you can use the Package Manager Console with the command 'Install-Package IronXL'.

How can I convert a CSV file to an XLSX file using this library?

To convert a CSV file to an XLSX file using IronXL, load the CSV file into a workbook with IronXL, specifying the output format as XLSX. Then, save the workbook as an Excel file in XLSX format using a few lines of C# code.

Can this library handle large Excel files efficiently?

Yes, IronXL is highly efficient and capable of handling large Excel files with ease, making it suitable for automating tasks like importing data from external sources, generating reports, and exporting data to different file formats.

Is it possible to convert a CSV file to an XLS file using this library?

Yes, you can convert a CSV file to an XLS file using IronXL by modifying the save method in your code to specify XLS as the output format.

What are some additional resources for learning about this Excel library?

For more information about IronXL, you can visit tutorials on converting CSV to Excel and exporting DataTables to Excel on the IronXL blog. You can also explore the pricing plans and licensing information on their website.

Regan Pun
Software Engineer
Regan graduated from the University of Reading, with a BA in Electronic Engineering. Before joining Iron Software, his previous job roles had him laser-focused on single tasks; and what he most enjoys at Iron Software is the spectrum of work he gets to undertake, whether it’s adding value to sales, technical support, product development or marketing. He enjoys understanding the way developers are using the Iron Software library, and using that knowledge to continually improve documentation and develop the products.
< PREVIOUS
Read Excel Data and Insert into Database with C#
NEXT >
How to Read CSV File in C#