How to Export Dataset to Excel VB .NET: A Complete Guide with IronXL

Why IronXL to Export Data?

This library is the answer for developers who need to handle large datasets efficiently. Unlike standard COM interop, IronXL doesn't lag when the volume of data increases. Below, we provide details on how to set up your project and execute the export.

Get started with a free trial to explore how IronXL simplifies Dataset and DataTable operations in your .NET Framework projects.

What Is the Best Way to Export a Dataset to Excel in VB.NET?

The most efficient approach to export a Dataset to Excel in VB.NET uses IronXL's WorkBook and WorkSheet classes. While the library handles the modern XLSX format, it also supports legacy XLS and universal CSV formats, ensuring compatibility with other applications.

To begin, install IronXL via the NuGet Package Manager in Visual Studio by searching for "IronXL.Excel" or running Install-Package IronXL.Excel in the Package Manager Console.

In this example, we create an instance of a workbook to hold our data.

Imports IronXL
Imports System.Data

' Create a new DataTable object with sample data
Dim dt As New DataTable("Employees")
dt.Columns.Add("ID", GetType(Integer))
dt.Columns.Add("Name", GetType(String))
dt.Columns.Add("Department", GetType(String))

' Add rows to the DataTable
dt.Rows.Add(1, "John Smith", "Engineering")
dt.Rows.Add(2, "Sarah Jones", "Marketing")
dt.Rows.Add(3, "Mike Wilson", "Sales")

' Create a new workbook and worksheet
Dim workbook As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)
Dim worksheet As WorkSheet = workbook.CreateWorkSheet("Employees")

' Add column headers to the first worksheet
For col As Integer = 0 To dt.Columns.Count - 1
    worksheet.SetCellValue(0, col, dt.Columns(col).ColumnName)
Next

' Export data from DataTable to Excel worksheet
For row As Integer = 0 To dt.Rows.Count - 1
    For col As Integer = 0 To dt.Columns.Count - 1
        worksheet.SetCellValue(row + 1, col, dt.Rows(row)(col).ToString())
    Next
Next

' Save the Excel file
workbook.SaveAs("EmployeeData.xlsx")

Imports IronXL
Imports System.Data

' Create a new DataTable object with sample data
Dim dt As New DataTable("Employees")
dt.Columns.Add("ID", GetType(Integer))
dt.Columns.Add("Name", GetType(String))
dt.Columns.Add("Department", GetType(String))

' Add rows to the DataTable
dt.Rows.Add(1, "John Smith", "Engineering")
dt.Rows.Add(2, "Sarah Jones", "Marketing")
dt.Rows.Add(3, "Mike Wilson", "Sales")

' Create a new workbook and worksheet
Dim workbook As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)
Dim worksheet As WorkSheet = workbook.CreateWorkSheet("Employees")

' Add column headers to the first worksheet
For col As Integer = 0 To dt.Columns.Count - 1
    worksheet.SetCellValue(0, col, dt.Columns(col).ColumnName)
Next

' Export data from DataTable to Excel worksheet
For row As Integer = 0 To dt.Rows.Count - 1
    For col As Integer = 0 To dt.Columns.Count - 1
        worksheet.SetCellValue(row + 1, col, dt.Rows(row)(col).ToString())
    Next
Next

' Save the Excel file
workbook.SaveAs("EmployeeData.xlsx")

How Can DataTables Be Exported to Multiple Excel Worksheets?

When working with a complex dataset designer layout containing multiple tables, each DataTable can be exported to its own Excel sheet within a single Excel workbook. This is particularly useful when presenting data to a user who needs categorized information in a single file.

Imports IronXL
Imports System.Data

' Create a new Dataset with multiple tables
Dim ds As New DataSet("CompanyData")

' First DataTable - Products
Dim dtProducts As New DataTable("Products")
dtProducts.Columns.Add("ProductID", GetType(Integer))
dtProducts.Columns.Add("ProductName", GetType(String))
dtProducts.Columns.Add("Price", GetType(Decimal))
dtProducts.Rows.Add(101, "Widget A", 29.99D)
dtProducts.Rows.Add(102, "Widget B", 49.99D)
ds.Tables.Add(dtProducts)

' Second DataTable - Orders
Dim dtOrders As New DataTable("Orders")
dtOrders.Columns.Add("OrderID", GetType(Integer))
dtOrders.Columns.Add("Customer", GetType(String))
dtOrders.Columns.Add("Total", GetType(Decimal))
dtOrders.Rows.Add(1001, "Acme Corp", 599.90D)
dtOrders.Rows.Add(1002, "Tech Inc", 299.95D)
ds.Tables.Add(dtOrders)

' Create workbook and export each DataTable to separate sheets
Dim workbook As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)
For Each table As DataTable In ds.Tables
    Dim sheet As WorkSheet = workbook.CreateWorkSheet(table.TableName)
    ' Write column headers
    For col As Integer = 0 To table.Columns.Count - 1
        sheet.SetCellValue(0, col, table.Columns(col).ColumnName)
    Next
    ' Write data rows
    For row As Integer = 0 To table.Rows.Count - 1
        For col As Integer = 0 To table.Columns.Count - 1
            sheet.SetCellValue(row + 1, col, table.Rows(row)(col).ToString())
        Next
    Next
Next
workbook.SaveAs("CompanyReport.xlsx")

Imports IronXL
Imports System.Data

' Create a new Dataset with multiple tables
Dim ds As New DataSet("CompanyData")

' First DataTable - Products
Dim dtProducts As New DataTable("Products")
dtProducts.Columns.Add("ProductID", GetType(Integer))
dtProducts.Columns.Add("ProductName", GetType(String))
dtProducts.Columns.Add("Price", GetType(Decimal))
dtProducts.Rows.Add(101, "Widget A", 29.99D)
dtProducts.Rows.Add(102, "Widget B", 49.99D)
ds.Tables.Add(dtProducts)

' Second DataTable - Orders
Dim dtOrders As New DataTable("Orders")
dtOrders.Columns.Add("OrderID", GetType(Integer))
dtOrders.Columns.Add("Customer", GetType(String))
dtOrders.Columns.Add("Total", GetType(Decimal))
dtOrders.Rows.Add(1001, "Acme Corp", 599.90D)
dtOrders.Rows.Add(1002, "Tech Inc", 299.95D)
ds.Tables.Add(dtOrders)

' Create workbook and export each DataTable to separate sheets
Dim workbook As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)
For Each table As DataTable In ds.Tables
    Dim sheet As WorkSheet = workbook.CreateWorkSheet(table.TableName)
    ' Write column headers
    For col As Integer = 0 To table.Columns.Count - 1
        sheet.SetCellValue(0, col, table.Columns(col).ColumnName)
    Next
    ' Write data rows
    For row As Integer = 0 To table.Rows.Count - 1
        For col As Integer = 0 To table.Columns.Count - 1
            sheet.SetCellValue(row + 1, col, table.Rows(row)(col).ToString())
        Next
    Next
Next
workbook.SaveAs("CompanyReport.xlsx")

How Do Database Query Results Get Exported to Excel Files?

Connecting to a database data source and exporting results directly to a new Excel file is a common requirement. Using SqlDataAdapter with IronXL creates a seamless pipeline from a SQL system to a spreadsheet.

1. Initialize Connection: Set up your connection string.
2. Fill Dataset: Use the adapter to pull data into your DataSet designer.
3. Map to IronXL: Create a workbook instance and iterate through the results to manipulate Excel files as needed.

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

' Define connection string and SQL query
Dim connectionString As String = "Server=localhost;Database=SalesDB;Integrated Security=True"
Dim query As String = "SELECT CustomerID, CompanyName, ContactName, Country FROM Customers"

' Create Dataset and fill from database
Dim ds As New DataSet()
Using connection As New SqlConnection(connectionString)
    Dim adapter As New SqlDataAdapter(query, connection)
    adapter.Fill(ds, "Customers")
End Using

' Access the DataTable exported from the database
Dim dt As DataTable = ds.Tables("Customers")

' Create Excel file and export the DataTable
Dim workbook As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)
Dim worksheet As WorkSheet = workbook.CreateWorkSheet("CustomerData")

' Write column headers with formatting
For col As Integer = 0 To dt.Columns.Count - 1
    worksheet.SetCellValue(0, col, dt.Columns(col).ColumnName)
    worksheet.Rows(0).Style.Font.Bold = True
Next

' Execute data transfer to worksheet cells
For row As Integer = 0 To dt.Rows.Count - 1
    For col As Integer = 0 To dt.Columns.Count - 1
        Dim cellValue As Object = dt.Rows(row)(col)
        worksheet.SetCellValue(row + 1, col, If(cellValue Is DBNull.Value, "", cellValue.ToString()))
    Next
Next

' Save output file
workbook.SaveAs("CustomerExport.xlsx")
Console.WriteLine("Export complete!")

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

' Define connection string and SQL query
Dim connectionString As String = "Server=localhost;Database=SalesDB;Integrated Security=True"
Dim query As String = "SELECT CustomerID, CompanyName, ContactName, Country FROM Customers"

' Create Dataset and fill from database
Dim ds As New DataSet()
Using connection As New SqlConnection(connectionString)
    Dim adapter As New SqlDataAdapter(query, connection)
    adapter.Fill(ds, "Customers")
End Using

' Access the DataTable exported from the database
Dim dt As DataTable = ds.Tables("Customers")

' Create Excel file and export the DataTable
Dim workbook As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)
Dim worksheet As WorkSheet = workbook.CreateWorkSheet("CustomerData")

' Write column headers with formatting
For col As Integer = 0 To dt.Columns.Count - 1
    worksheet.SetCellValue(0, col, dt.Columns(col).ColumnName)
    worksheet.Rows(0).Style.Font.Bold = True
Next

' Execute data transfer to worksheet cells
For row As Integer = 0 To dt.Rows.Count - 1
    For col As Integer = 0 To dt.Columns.Count - 1
        Dim cellValue As Object = dt.Rows(row)(col)
        worksheet.SetCellValue(row + 1, col, If(cellValue Is DBNull.Value, "", cellValue.ToString()))
    Next
Next

' Save output file
workbook.SaveAs("CustomerExport.xlsx")
Console.WriteLine("Export complete!")

The SqlDataAdapter executes the command and retrieves data from the SQL database, storing it in the Dataset's Tables collection. The code iterates through the DataTable exported from the query, handling null values with a conditional check. Bold font styling on the column headers improves readability.

For XML data sources, IronXL also supports reading and writing XML-based spreadsheet formats, providing flexibility for different data interchange scenarios.

Conclusion

The task of exporting a Dataset to Excel in VB.NET becomes straightforward with IronXL's clean API. Whether working with a single DataTable object or multiple tables, the library handles file format conversion and worksheet management across .NET Framework and .NET Core projects.

The examples demonstrate how to create Excel files from in-memory data structures and database queries without relying on Microsoft Office Interop. IronXL's approach eliminates COM dependencies while providing font styling and multi-sheet support.

Ready to implement Dataset to Excel exports in your VB.NET applications? Purchase a license or explore additional DataTable tutorials to expand your Excel automation capabilities.