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

This article will offer the most effective approach to extracting 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, and multiple sheets through the IronXL API. IronXL also provides detailed documentation of WorkSheet API.

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 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 to export data. The SQL command CREATE TABLE can be used to create such a table and define its structure.

2. Creating a 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.

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.

Project configuration

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

Project with code open

3. Installing IronXL

The IronXL library can be downloaded and installed using different methods:

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

NuGet Package Manager

IronXL in search results:

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.

Installing via command line

4. Export Data From DataTable to Excel File

To export data in a 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;

class LargeDataToExcel 
{
    static void Main()
    {
        // SQL query to select all data from the specified table
        string sql = "SELECT * FROM [dbo].[Table]";

        // SQL Server connection string
        string connectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\USERS\YOURUSERNAME\SOURCE\REPOS\YOURPROJECT\DATABASE1.MDF;Integrated Security=True";

        // Establishing a SQL connection using SqlConnection
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            // Open the connection to the database
            connection.Open();

            // Initialize the SqlDataAdapter with the SQL query and connection
            SqlDataAdapter adapter = new SqlDataAdapter(sql, connection);

            // Create a new DataSet to hold the data from the database
            DataSet ds = new DataSet();

            // Fill the DataSet with data from the database
            adapter.Fill(ds);

            // Create a new Excel workbook from the DataSet
            WorkBook workBook = WorkBook.Load(ds);

            // Save the workbook as an Excel file
            workBook.SaveAs("sample.xlsx");
        }
    }
}

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

class LargeDataToExcel 
{
    static void Main()
    {
        // SQL query to select all data from the specified table
        string sql = "SELECT * FROM [dbo].[Table]";

        // SQL Server connection string
        string connectionString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\USERS\YOURUSERNAME\SOURCE\REPOS\YOURPROJECT\DATABASE1.MDF;Integrated Security=True";

        // Establishing a SQL connection using SqlConnection
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            // Open the connection to the database
            connection.Open();

            // Initialize the SqlDataAdapter with the SQL query and connection
            SqlDataAdapter adapter = new SqlDataAdapter(sql, connection);

            // Create a new DataSet to hold the data from the database
            DataSet ds = new DataSet();

            // Fill the DataSet with data from the database
            adapter.Fill(ds);

            // Create a new Excel workbook from the DataSet
            WorkBook workBook = WorkBook.Load(ds);

            // Save the workbook as an Excel file
            workBook.SaveAs("sample.xlsx");
        }
    }
}

Imports IronXL
Imports System
Imports System.Data
Imports System.Data.SqlClient

Friend Class LargeDataToExcel
	Shared Sub Main()
		' SQL query to select all data from the specified table
		Dim sql As String = "SELECT * FROM [dbo].[Table]"

		' SQL Server connection string
		Dim connectionString As String = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\USERS\YOURUSERNAME\SOURCE\REPOS\YOURPROJECT\DATABASE1.MDF;Integrated Security=True"

		' Establishing a SQL connection using SqlConnection
		Using connection As New SqlConnection(connectionString)
			' Open the connection to the database
			connection.Open()

			' Initialize the SqlDataAdapter with the SQL query and connection
			Dim adapter As New SqlDataAdapter(sql, connection)

			' Create a new DataSet to hold the data from the database
			Dim ds As New DataSet()

			' Fill the DataSet with data from the database
			adapter.Fill(ds)

			' Create a new Excel workbook from the DataSet
			Dim workBook As WorkBook = WorkBook.Load(ds)

			' Save the workbook as an Excel file
			workBook.SaveAs("sample.xlsx")
		End Using
	End Sub
End Class

The extracted data in the Excel file

In the code above, a large data table is retrieved from a SQL Server database and exported to an Excel file. A connection between the project and the SQL server is established using a connection string.

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

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 the 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 using 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 workbook from the DataTable to export the data 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 tutorial for more detailed information on exporting data from DataTable to Excel. Please visit another tutorial for more information on how to export data from Excel files. 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.