How to Export SQL Data to Excel Using C# with IronXL

What is the Best Way to Export Data from a SQL Server Database?

The most efficient approach to export data from SQL Server to an Excel file involves three steps: establish a database connection, retrieve data into a DataTable, and write the data to an Excel worksheet using IronXL. Unlike Microsoft Interop solutions, IronXL operates independently and handles large datasets with excellent performance.

How Do You Connect to SQL Server and Retrieve Data?

Before you can export SQL data, you need to establish a connection and execute a SQL query to fill a DataTable. The connection string contains the SQL Server database credentials and server information required to access your data.

You will need to import the following namespaces in your Visual Studio project:

using IronXL;
using System.Data;
using System.Data.SqlClient;
// Define connection string for SQL Server
string connectionString = @"Data Source=localhost;Initial Catalog=Northwind;Integrated Security=True";
// SQL query to retrieve data from SQL Server table
string sql = "SELECT * FROM Customers";
using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    // Use SqlDataAdapter to fill DataTable with query results
    SqlDataAdapter adapter = new SqlDataAdapter(sql, connection);
    DataTable dt = new DataTable();
    adapter.Fill(dt);
    // Create new workbook and worksheet
    WorkBook workBook = WorkBook.Create();
    WorkSheet workSheet = workBook.CreateWorkSheet("CustomerData");
    // Export DataTable to Excel worksheet with column names
    int rowIndex = 1;
    // Write column headers from DataTable
    for (int j = 0; j < dt.Columns.Count; j++)
    {
        workSheet.SetCellValue(0, j, dt.Columns[j].ColumnName);
    }
    // Fill worksheet with data values
    for (int i = 0; i < dt.Rows.Count; i++)
    {
        for (int j = 0; j < dt.Columns.Count; j++)
        {
            string cellValue = dt.Rows[i][j].ToString();
            workSheet.SetCellValue(rowIndex, j, cellValue);
        }
        rowIndex++;
    }
    // Save Excel file in xlsx format
    workBook.SaveAs("CustomerExport.xlsx");
}

using IronXL;
using System.Data;
using System.Data.SqlClient;
// Define connection string for SQL Server
string connectionString = @"Data Source=localhost;Initial Catalog=Northwind;Integrated Security=True";
// SQL query to retrieve data from SQL Server table
string sql = "SELECT * FROM Customers";
using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    // Use SqlDataAdapter to fill DataTable with query results
    SqlDataAdapter adapter = new SqlDataAdapter(sql, connection);
    DataTable dt = new DataTable();
    adapter.Fill(dt);
    // Create new workbook and worksheet
    WorkBook workBook = WorkBook.Create();
    WorkSheet workSheet = workBook.CreateWorkSheet("CustomerData");
    // Export DataTable to Excel worksheet with column names
    int rowIndex = 1;
    // Write column headers from DataTable
    for (int j = 0; j < dt.Columns.Count; j++)
    {
        workSheet.SetCellValue(0, j, dt.Columns[j].ColumnName);
    }
    // Fill worksheet with data values
    for (int i = 0; i < dt.Rows.Count; i++)
    {
        for (int j = 0; j < dt.Columns.Count; j++)
        {
            string cellValue = dt.Rows[i][j].ToString();
            workSheet.SetCellValue(rowIndex, j, cellValue);
        }
        rowIndex++;
    }
    // Save Excel file in xlsx format
    workBook.SaveAs("CustomerExport.xlsx");
}

IRON VB CONVERTER ERROR developers@ironsoftware.com

How Can You Export Data Using an ASP.NET Export Button?

For web applications, you'll typically trigger the export when a user clicks an export button. The following code shows how to handle the button click event and initiate a file download using Response.AddHeader for content disposition.

using IronXL;
using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Web.UI;
public partial class ExportPage : Page
{
    private void ExportButton_Click(object sender, EventArgs e)
    {
        string connectionString = @"Data Source=localhost;Initial Catalog=Sales;Integrated Security=True";
        try
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                // Execute SQL query to get data from SQL server
                SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM Orders", connection);
                DataSet dataSet = new DataSet();
                adapter.Fill(dataSet);
                // Create Excel workbook from DataSet - each table becomes a sheet
                WorkBook workBook = WorkBook.Create();
                WorkBook.LoadWorkSheetsFromDataSet(dataSet, workBook);
                // Convert to byte array for download
                byte[] fileBytes = workBook.ToByteArray();
                string filename = "OrdersExport.xlsx";
                // Set response headers for file download
                Response.Clear();
                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                Response.AddHeader("content-disposition", "attachment;filename=" + filename);
                Response.BinaryWrite(fileBytes);
                Response.End();
            }
        }
        catch (Exception ex)
        {
            // Handle any errors during export
            Response.Write("Export error: " + ex.Message);
        }
    }
}

using IronXL;
using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Web.UI;
public partial class ExportPage : Page
{
    private void ExportButton_Click(object sender, EventArgs e)
    {
        string connectionString = @"Data Source=localhost;Initial Catalog=Sales;Integrated Security=True";
        try
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                // Execute SQL query to get data from SQL server
                SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM Orders", connection);
                DataSet dataSet = new DataSet();
                adapter.Fill(dataSet);
                // Create Excel workbook from DataSet - each table becomes a sheet
                WorkBook workBook = WorkBook.Create();
                WorkBook.LoadWorkSheetsFromDataSet(dataSet, workBook);
                // Convert to byte array for download
                byte[] fileBytes = workBook.ToByteArray();
                string filename = "OrdersExport.xlsx";
                // Set response headers for file download
                Response.Clear();
                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                Response.AddHeader("content-disposition", "attachment;filename=" + filename);
                Response.BinaryWrite(fileBytes);
                Response.End();
            }
        }
        catch (Exception ex)
        {
            // Handle any errors during export
            Response.Write("Export error: " + ex.Message);
        }
    }
}

IRON VB CONVERTER ERROR developers@ironsoftware.com

How Do You Import Data and Create Excel Files with Column Headers?

IronXL provides multiple methods to create Excel files and import data efficiently. The LoadWorkSheetsFromDataSet method accepts DataTable collections and automatically preserves column names as headers in your Excel worksheet. This approach works seamlessly with System.Data objects and Microsoft SQL Server.

using IronXL;
using System.Data;
using System.Data.SqlClient;
// Connection to SQL database
string connectionString = @"Data Source=.;Initial Catalog=Inventory;Integrated Security=True";
using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    // Query multiple tables from database
    SqlDataAdapter productsAdapter = new SqlDataAdapter("SELECT * FROM Products", connection);
    SqlDataAdapter categoriesAdapter = new SqlDataAdapter("SELECT * FROM Categories", connection);
    DataSet dataSet = new DataSet();
    productsAdapter.Fill(dataSet, "Products");
    categoriesAdapter.Fill(dataSet, "Categories");
    // Create new workbook and load all tables
    WorkBook workBook = WorkBook.Create();
    WorkBook.LoadWorkSheetsFromDataSet(dataSet, workBook);
    // Save as xlsx file
    workBook.SaveAs("InventoryReport.xlsx");
}

using IronXL;
using System.Data;
using System.Data.SqlClient;
// Connection to SQL database
string connectionString = @"Data Source=.;Initial Catalog=Inventory;Integrated Security=True";
using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    // Query multiple tables from database
    SqlDataAdapter productsAdapter = new SqlDataAdapter("SELECT * FROM Products", connection);
    SqlDataAdapter categoriesAdapter = new SqlDataAdapter("SELECT * FROM Categories", connection);
    DataSet dataSet = new DataSet();
    productsAdapter.Fill(dataSet, "Products");
    categoriesAdapter.Fill(dataSet, "Categories");
    // Create new workbook and load all tables
    WorkBook workBook = WorkBook.Create();
    WorkBook.LoadWorkSheetsFromDataSet(dataSet, workBook);
    // Save as xlsx file
    workBook.SaveAs("InventoryReport.xlsx");
}

IRON VB CONVERTER ERROR developers@ironsoftware.com

What About Web Applications with HTML Markup?

In ASP.NET applications where the HTML markup consists of buttons and data grids, IronXL integrates seamlessly. Your page's HTML markup consists of standard web controls that trigger server-side export operations, while IronXL handles all Excel generation without dependencies on Microsoft Office.

For detailed guidance on exporting DataSet and DataTable objects, visit the IronXL documentation.

Conclusion

Exporting SQL data to Excel using C# becomes straightforward with IronXL. The library eliminates complex Interop dependencies while providing robust support for DataTable, DataSet, and direct database integration. Whether building console applications or web-based reporting systems with file download capabilities, IronXL delivers reliable Excel file generation.

Purchase a license for production deployment, or continue exploring with a free trial to test the full capabilities of this Excel library in your .NET projects.