1. IronXL
  2. IronXL Blog
  3. Using IronXL
  4. How to Export Huge Data From DataTable to Excel in C#

Published June 7, 2023

How to Export Huge Data From Datatable to Excel in C#

DataTable is an object in the ADO.NET library, representing tabular in-memory data within a .NET Framework program as a database table. Much like Excel worksheets, DataTable in C# offer filtering and sorting methods for manipulating and exporting table data. It is created at runtime, and the data is lost when the application object is terminated. To save the data to a permanent source, the data has to be exported with various data types to either an Excel file or a CSV file.

Microsoft Excel is a highly regarded and well-known spreadsheet application designed to accommodate and manage large table data sets in tabular form. Its comprehensive suite of features, including data analysis, charting capabilities, pivot tables, and integrated macro Visual Basic programming, provides unparalleled user functionality across industries. Exporting data from DataTable to an Excel spreadsheet is common in many applications. However, when working with a large amount of data, the process of exporting data can be a challenging and resource-intensive task. In such cases, it is essential to utilize techniques to handle big data efficiently without compromising the application's performance.

In this process, you can use various libraries and techniques available in C# to export a large amount of data from a DataTable to an Excel sheet.

IronXL

IronXL is a cutting-edge C# library providing a simple, intuitive interface to read, write and edit Excel files programmatically. It is a tool that unlocks the full potential of Excel spreadsheets, giving developers and users the power to manipulate data easily. It has been designed to handle tasks in Excel that are more repetitive and time-consuming. Whether you're working on a financial model, a sales report, or a data visualization project, IronXL can help you get the job done quickly and efficiently.

With IronXL, you can streamline your workflow, reduce errors, and take your Excel automation to the next level. IronXL allows you to interact directly with Excel data without the need to interface the MS files or libraries. Developers can manage elements like cells, ranges, multiple sheets through IronXL API. Detailed documentation of IronXL API can be accessed here.

By utilizing IronXL, developers can easily export a large amount of data from a DataTable to an Excel spreadsheet while maintaining the application's performance and minimizing the chances of errors. Understanding how to export data from DataTable to Excel in C# can help developers effectively manage data in their applications and enhance the user experience.

This article will discuss how you can export large data from DataTable to an Excel file. It provides a step-by-step guide from creating a .NET project in Visual Studio to installing the IronXL library and code samples. Be sure to get ready to unleash your creativity and unlock the full potential of your data with IronXL.

1. Prerequisites

Several prerequisites must be met to utilize the IronXL library for writing to an Excel file from a database. These include:

  • Visual Studio must be installed on your computer to create a C# project.
  • ASP.NET must be installed on your system before creating a C# project.
  • The IronXL library must be installed on your system. You can obtain it by downloading the IronXL NuGet package from the NuGet package manager in Visual Studio.
  • SQL must also be installed in Visual Studio. Ideally, there should also be a table in the database with which the data we will be exporting. The SQL command CREATE TABLE can be used to create such a table and define its structure.

2. Creating New Project on Visual Studio

Before utilizing the IronXL library to perform Excel-related operations, you will need to first create a .NET project in Visual Studio. Although any version of Visual Studio is compatible, it is recommended to use the latest version.

You can choose from various project templates, such as Windows Forms and ASP.NET, based on your requirements.

This tutorial employs the Console Application project template to demonstrate working with IronXL.

How to Export Huge Data From DataTable to Excel in C#: Figure 1 - Create a new project window

Once you have chosen the project type, you need to name the project and select its location. You can also specify the preferred framework, such as .NET Core 6, for the project.

How to Export Huge Data From DataTable to Excel in C#: Figure 2 - Project configuration

After the solution is generated, you can access the program.cs file, where you can input code and create/execute the application.

How to Export Huge Data From DataTable to Excel in C#: Figure 3 - Project with code open

3. Installing IronXL

The IronXL library can be downloaded and installed in different methods. We will only discuss two of these in this article.

These are:

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

3.1 Using Visual Studio NuGet Packages

To install the IronXL library with NuGet Package Manager in Visual Studio, 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 use the IronXL library in your project.

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

How to Export Huge Data From DataTable to Excel in C#: Figure 4 - NuGet Package Manager

IronXL in search results:

How to Export Huge Data From DataTable to Excel in C#: Figure 5 - IronXL search result

3.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:

  • Go to Tools > NuGet Package Manager > Package Manager console in Visual Studio.

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

How to Export Huge Data From DataTable to Excel in C#: Figure 6 - Installing via command line

4. Export Data From DataTable to Excel File

To export data in DataTable to excel using IronXL, you need to have a database table integrated with the C# project.

The following code sample shows how to export data from all DataTable columns to the Excel worksheet.

using IronXL;
using System;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

class LargeDataToExcel 
{
    static void Main()
    {
        // Your sql query
        string sql = "SELECT * FROM [dbo].[Table]";

        // Your connection string
        string connectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\USERS\BUTTW\SOURCE\REPOS\CREATE PDF\CREATE PDF\DATABASE1.MDF;Integrated Security=True";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            // Open connections to the database
            connection.Open();
            SqlDataAdapter adapter = new SqlDataAdapter(sql, connection);
            // Create new DataSet
            DataSet ds = new DataSet();
            // Fill DataSet with data
            adapter.Fill(ds);

            // Create an Excel new workbook from the SQL DataSet
            WorkBook workBook = WorkBook.Load(ds);
            workBook.SaveAs("sample.xlsx");
        }
    }
}
using IronXL;
using System;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

class LargeDataToExcel 
{
    static void Main()
    {
        // Your sql query
        string sql = "SELECT * FROM [dbo].[Table]";

        // Your connection string
        string connectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\USERS\BUTTW\SOURCE\REPOS\CREATE PDF\CREATE PDF\DATABASE1.MDF;Integrated Security=True";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            // Open connections to the database
            connection.Open();
            SqlDataAdapter adapter = new SqlDataAdapter(sql, connection);
            // Create new DataSet
            DataSet ds = new DataSet();
            // Fill DataSet with data
            adapter.Fill(ds);

            // Create an Excel new workbook from the SQL DataSet
            WorkBook workBook = WorkBook.Load(ds);
            workBook.SaveAs("sample.xlsx");
        }
    }
}
Imports IronXL
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Linq

Friend Class LargeDataToExcel
	Shared Sub Main()
		' Your sql query
		Dim sql As String = "SELECT * FROM [dbo].[Table]"

		' Your connection string
		Dim connectionString As String = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\USERS\BUTTW\SOURCE\REPOS\CREATE PDF\CREATE PDF\DATABASE1.MDF;Integrated Security=True"

		Using connection As New SqlConnection(connectionString)
			' Open connections to the database
			connection.Open()
			Dim adapter As New SqlDataAdapter(sql, connection)
			' Create new DataSet
			Dim ds As New DataSet()
			' Fill DataSet with data
			adapter.Fill(ds)

			' Create an Excel new workbook from the SQL DataSet
			Dim workBook As WorkBook = WorkBook.Load(ds)
			workBook.SaveAs("sample.xlsx")
		End Using
	End Sub
End Class
VB   C#

How to Export Huge Data From DataTable to Excel in C#: Figure 7

In the code above, we are retrieving a large data table from a SQL Server database and exporting data to an Excel file. Using our connection string, we establish a connection between our project and the SQL server.

Using the SqlDataAdapter object, we retrieve the data specified by the SQL query. The DataSet object is able to store a collection of DataTables, their relationships, and constraints. The SqlDataAdapter populates the DataSet with our data in the data table as the SQL query results.

We then create a new instance of the WorkBook class from the IronXL library and load the DataSet into it. Finally, the WorkBook is saved as an XLSX file, the extension for Excel files.

With this code, there is no need to create each header row or manually add columns. In just a few seconds, all the data in that table is exported to a new Excel file using IronXL Excel Library. Using this technique, developers can easily export data of different data types, sizes, and sources to Excel worksheets.

5. Conclusion

Data can be exported from a DataTable to an Excel spreadsheet in C# using various libraries and techniques. When working with a large amount of data, utilizing techniques that handle the data efficiently without compromising the application's performance is essential.

The IronXL library is an excellent solution, providing a simple, intuitive interface to read, write, and edit Excel files programmatically. It is essential to meet several prerequisites, such as installing Visual Studio and ASP.NET, to utilize the IronXL library for writing to a database from an Excel file. Once the prerequisites are met, the IronXL library can be downloaded and installed in different methods, such as using Visual Studio NuGet packages and the Visual Studio Command Line. After installation, the next step is to write the code that creates a new object sender of a new worksheet to export the DataTable to an Excel worksheet.

By understanding how to export data from a DataTable to an Excel worksheet in C#, developers can effectively manage data in their applications and enhance the user experience. Please visit the following link for a more detailed tutorial on exporting data from DataTable to Excel. For more information on how to export data from Excel files, please visit the following link. There is a free trial available for IronXL for users to try out all its functionality.

Users can also benefit from Iron Suite, a collection of software development tools including IronPDF, IronOCR, IronXL, IronBarcode, and IronWebscraper.