Load Excel From SQL Database

VBC#

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

// Your sql query
string sql = "SELECT * FROM Users";

// Your connection string
string connectionString = @"Data Source=.\SQLEXPRESS;Initial Catalog=usersdb;Integrated Security=True";

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

    // Create an Excel workbook from the SQL DataSet
    WorkBook workBook = WorkBook.Load(ds);
}

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

' Your sql query
Dim sql As String = "SELECT * FROM Users"

' Your connection string
Dim connectionString As String = "Data Source=.\SQLEXPRESS;Initial Catalog=usersdb;Integrated Security=True"

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

    ' Create an Excel workbook from the SQL DataSet
    Dim workBook As WorkBook = WorkBook.Load(ds)
End Using

PM > Install-Package IronXL.Excel

Install-Package IronXL.Excel

Load Excel From SQL Database

IronXL makes it easy to convert SQL query results into Excel workbooks, allowing developers to add data export functionality to their .NET applications quickly. Developers can skip manual data formatting and focus on creating clear and user-friendly reports by loading a DataSet directly into a WorkBook.

This feature is ideal for automating tasks like generating reports or sharing database insights. It supports popular file formats like XLS and XLSX, ensuring compatibility with most spreadsheet applications. Technically, it reduces the amount of code needed and uses IronXL's powerful API to handle large datasets efficiently, making it a practical choice for data-heavy applications.

5 Steps to Export SQL Data to Excel Using IronXL

SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM Products", @"yourConnectionString");
DataSet ds = new DataSet();
adapter.Fill(ds);
WorkBook workBook = WorkBook.Load(ds);
workBook.SaveAs("ExportedData.xlsx");

This snippet demonstrates how to export SQL query results to an Excel file using IronXL. First, an SqlDataAdapter is initialized with a query (SELECT * FROM Products) and a connection string pointing to the database. The SqlDataAdapter fetches data and populates a DataSet object, a flexible in-memory representation of the query results.

The WorkBook.Load method is then used to directly convert the DataSet into an Excel workbook. This method efficiently maps the tabular structure of the DataSet to a worksheet, preserving rows and columns. Finally, the SaveAs method is called to save the workbook as an .xlsx file.

This approach eliminates the need for manually iterating through rows or handling complex Excel formatting, making it a highly efficient solution for exporting database content. IronXL ensures that the generated Excel file is compatible with various spreadsheet applications while maintaining robust performance and ease of use.

Learn to Load Excel Sheets Easily with IronXL.

View on GitHub