How to Convert a Dataset to Excel in VB.NET

Converting DataSets to Excel worksheet files is a common requirement in VB.NET applications, whether you're generating reports, exporting database queries, or creating data backups. While traditional methods using Office Interop require Excel installation and complex COM references, IronXL offers a streamlined approach that works independently of Microsoft Office.

This tutorial demonstrates how to efficiently convert DataSets with DataTable rows to Excel files in VB.NET using IronXL, complete with formatting options and support for multiple file formats.

What Makes the DataSet to Excel Conversion Essential?

A DataSet in .NET represents an in-memory cache of data, containing one or more DataTables with rows, columns, and relationships. Converting these DataSets to Excel enables:

• Business reporting: Transform database queries into formatted spreadsheets for stakeholders
• Data archival: Export application data for long-term storage and analysis
• Cross-platform compatibility: Share data with users who may not have access to your application
• Enhanced visualization: Leverage Excel's built-in charting and analysis tools

IronXL simplifies this process by providing an intuitive API that handles the conversion without requiring Excel to be installed on the server or development machine. The library supports the .NET Framework 4.6.2 and later, .NET Core, and .NET 5 and later, making it suitable for modern applications, including those deployed to Linux containers and cloud environments. For more details on supported platforms, visit the IronXL features page.

How to Get Started with IronXL in Your VB.NET Project?

Setting up IronXL in your VB.NET or .NET code project takes just minutes. Open Visual Studio and navigate to your project, then install IronXL through the NuGet Package Manager.

Using the Package Manager Console, execute:

Install-Package IronXL.Excel

Alternatively, search for "IronXL.Excel" in the NuGet Package Manager UI and click Install. The package will automatically download and configure all necessary dependencies. You can find more installation options in the IronXL installation guide.

Once installed, add the IronXL namespace to your VB.NET file:

Imports IronXL
Imports System.Data

Imports IronXL
Imports System.Data

With these imports in place, you're ready to start converting DataSets to Excel files.

How to Convert DataSet to Excel in VB .NET?

Let's start with a practical example that creates a DataSet with sample data and exports it to an Excel file:

' Create a sample DataSet with product information
Dim dataSet As New DataSet("ProductData")
' Create a DataTable object for products, column headers
Dim productsTable As New DataTable("Products")
productsTable.Columns.Add("ProductID", GetType(Integer))
productsTable.Columns.Add("ProductName", GetType(String))
productsTable.Columns.Add("Price", GetType(Decimal))
productsTable.Columns.Add("InStock", GetType(Boolean))
' Add sample data to export DataTable
productsTable.Rows.Add(1, "Laptop", 999.99, True)
productsTable.Rows.Add(2, "Mouse", 19.99, True)
productsTable.Rows.Add(3, "Keyboard", 49.99, False)
productsTable.Rows.Add(4, "Monitor", 299.99, True)
productsTable.Rows.Add(5, "Headphones", 79.99, True)
' Add the DataTable table to the DataSet
dataSet.Tables.Add(productsTable)
' Export DataSet to Excel using IronXL
Dim workBook As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)
WorkBook.LoadWorkSheetsFromDataSet(dataSet, workBook)
' Save the Excel file
workBook.SaveAs("ProductData.xlsx")

' Create a sample DataSet with product information
Dim dataSet As New DataSet("ProductData")
' Create a DataTable object for products, column headers
Dim productsTable As New DataTable("Products")
productsTable.Columns.Add("ProductID", GetType(Integer))
productsTable.Columns.Add("ProductName", GetType(String))
productsTable.Columns.Add("Price", GetType(Decimal))
productsTable.Columns.Add("InStock", GetType(Boolean))
' Add sample data to export DataTable
productsTable.Rows.Add(1, "Laptop", 999.99, True)
productsTable.Rows.Add(2, "Mouse", 19.99, True)
productsTable.Rows.Add(3, "Keyboard", 49.99, False)
productsTable.Rows.Add(4, "Monitor", 299.99, True)
productsTable.Rows.Add(5, "Headphones", 79.99, True)
' Add the DataTable table to the DataSet
dataSet.Tables.Add(productsTable)
' Export DataSet to Excel using IronXL
Dim workBook As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)
WorkBook.LoadWorkSheetsFromDataSet(dataSet, workBook)
' Save the Excel file
workBook.SaveAs("ProductData.xlsx")

This code creates a DataSet containing product information and converts it directly to an Excel file. The LoadWorkSheetsFromDataSet method automatically creates a worksheet for each DataTable in your DataSet. The resulting Excel file contains properly formatted data derived from your DataTable structure.

Output

You can also manipulate Excel files and save them in different formats:

' Save as legacy Excel versions format
workBook.SaveAs("ProductData.xls")
' Save as CSV (comma-separated values)
workBook.SaveAsCsv("ProductData.csv")
' Save as TSV (tab-separated values)
workBook.SaveAs("ProductData.tsv")

' Save as legacy Excel versions format
workBook.SaveAs("ProductData.xls")
' Save as CSV (comma-separated values)
workBook.SaveAsCsv("ProductData.csv")
' Save as TSV (tab-separated values)
workBook.SaveAs("ProductData.tsv")

Each format has its specific use cases: the XLSX file format for modern Excel compatibility, XLS for legacy systems, and CSV/TSV for universal data exchange with other applications and databases. Learn more about converting between spreadsheet formats.

How to Export Database Data Directly to an Excel File Format?

Real-world applications often require exporting data from SQL databases. Here's how to query a database and export the results to Excel:

Dim connectionString As String = "Data Source=server;Initial Catalog=SampleDB;Integrated Security=True"
Dim query As String = "SELECT OrderID, CustomerName, OrderDate, TotalAmount FROM Orders WHERE OrderDate >= '2024-01-01'"
Dim dataSet As New DataSet()
' Populate DataSet from database
Using connection As New SqlConnection(connectionString)
    Using adapter As New SqlDataAdapter(query, connection)
        adapter.Fill(dataSet, "Orders")
    End Using
End Using
' Create Excel workbook and load data
Dim workBook As WorkBook = WorkBook.Create()
WorkBook.LoadWorkSheetsFromDataSet(dataSet, workBook)
' Auto-size columns for better readability
Dim worksheet As WorkSheet = workBook.WorkSheets(0)
For i As Integer = 0 To worksheet.Columns.Count - 1
    worksheet.AutoSizeColumn(i)
Next
' Save with timestamp in filename
Dim fileName As String = $"OrdersExport_{DateTime.Now:yyyyMMdd_HHmmss}.xlsx"
workBook.SaveAs(fileName)

Dim connectionString As String = "Data Source=server;Initial Catalog=SampleDB;Integrated Security=True"
Dim query As String = "SELECT OrderID, CustomerName, OrderDate, TotalAmount FROM Orders WHERE OrderDate >= '2024-01-01'"
Dim dataSet As New DataSet()
' Populate DataSet from database
Using connection As New SqlConnection(connectionString)
    Using adapter As New SqlDataAdapter(query, connection)
        adapter.Fill(dataSet, "Orders")
    End Using
End Using
' Create Excel workbook and load data
Dim workBook As WorkBook = WorkBook.Create()
WorkBook.LoadWorkSheetsFromDataSet(dataSet, workBook)
' Auto-size columns for better readability
Dim worksheet As WorkSheet = workBook.WorkSheets(0)
For i As Integer = 0 To worksheet.Columns.Count - 1
    worksheet.AutoSizeColumn(i)
Next
' Save with timestamp in filename
Dim fileName As String = $"OrdersExport_{DateTime.Now:yyyyMMdd_HHmmss}.xlsx"
workBook.SaveAs(fileName)

This example demonstrates a complete workflow from database query to Excel file. The SqlDataAdapter fills the DataSet with query results, which IronXL then converts to a formatted Excel file. The auto-sizing feature ensures that all data is visible without manual column width adjustments, creating a professional-looking export that is ready for distribution. For handling larger datasets, see our guide on working with large Excel files.

Input

Output

How to Handle Multiple DataTables in One Excel File?

When working with related data, you might have multiple DataTables in a single DataSet. IronXL handles this seamlessly:

Dim dataSet As New DataSet("CompanyData")
' Create Employees table (dim dt)
Dim employeesTable As New DataTable("Employees")
employeesTable.Columns.Add("EmployeeID", GetType(Integer))
employeesTable.Columns.Add("Name", GetType(String))
employeesTable.Columns.Add("Department", GetType(String))
employeesTable.Rows.Add(1, "John Smith", "Sales")
employeesTable.Rows.Add(2, "Jane Doe", "Marketing")
' Create Departments table
Dim departmentsTable As New DataTable("Departments")
departmentsTable.Columns.Add("DepartmentName", GetType(String))
departmentsTable.Columns.Add("Budget", GetType(Decimal))
departmentsTable.Rows.Add("Sales", 100000)
departmentsTable.Rows.Add("Marketing", 75000)
' Add both tables to DataSet
dataSet.Tables.Add(employeesTable)
dataSet.Tables.Add(departmentsTable)
' Convert to Excel - each DataTable becomes a worksheet
Dim workBook As WorkBook = WorkBook.Create()
WorkBook.LoadWorkSheetsFromDataSet(dataSet, workBook)
' Optionally rename worksheets
workBook.WorkSheets(0).Name = "Employee List"
workBook.WorkSheets(1).Name = "Department Budgets"
workBook.SaveAs("CompanyData.xlsx")

Dim dataSet As New DataSet("CompanyData")
' Create Employees table (dim dt)
Dim employeesTable As New DataTable("Employees")
employeesTable.Columns.Add("EmployeeID", GetType(Integer))
employeesTable.Columns.Add("Name", GetType(String))
employeesTable.Columns.Add("Department", GetType(String))
employeesTable.Rows.Add(1, "John Smith", "Sales")
employeesTable.Rows.Add(2, "Jane Doe", "Marketing")
' Create Departments table
Dim departmentsTable As New DataTable("Departments")
departmentsTable.Columns.Add("DepartmentName", GetType(String))
departmentsTable.Columns.Add("Budget", GetType(Decimal))
departmentsTable.Rows.Add("Sales", 100000)
departmentsTable.Rows.Add("Marketing", 75000)
' Add both tables to DataSet
dataSet.Tables.Add(employeesTable)
dataSet.Tables.Add(departmentsTable)
' Convert to Excel - each DataTable becomes a worksheet
Dim workBook As WorkBook = WorkBook.Create()
WorkBook.LoadWorkSheetsFromDataSet(dataSet, workBook)
' Optionally rename worksheets
workBook.WorkSheets(0).Name = "Employee List"
workBook.WorkSheets(1).Name = "Department Budgets"
workBook.SaveAs("CompanyData.xlsx")

Each DataTable in your DataSet becomes a separate worksheet in the Excel file, maintaining the logical separation of your data. This approach is particularly useful for creating comprehensive reports that require individual worksheets for different data categories, all within a single file. According to Microsoft's documentation on DataSets, this structure mirrors the relational nature of database schemas.

Output

How to Add Basic Formatting to Your Excel Export?

While IronXL automatically handles basic formatting, you can enhance your Excel exports with additional styling:

' After loading DataSet into WorkBook
Dim worksheet As WorkSheet = workBook.WorkSheets(0)
' Set header row style
Dim headerRange As Range = worksheet.GetRange("A1:D1")
headerRange.Style.Font.Bold = True
headerRange.Style.BackgroundColor = "#4472C4"
headerRange.Style.Font.Color = "#FFFFFF"
' Format currency columns
For rowIndex As Integer = 1 To worksheet.RowCount - 1
    worksheet($"C{rowIndex + 1}").FormatString = "$#,##0.00"
Next
' Add borders to data range
Dim dataRange As Range = worksheet.GetRange($"A1:D{worksheet.RowCount}")
dataRange.Style.Border.TopBorder.Type = BorderType.Thin
dataRange.Style.Border.BottomBorder.Type = BorderType.Thin

' After loading DataSet into WorkBook
Dim worksheet As WorkSheet = workBook.WorkSheets(0)
' Set header row style
Dim headerRange As Range = worksheet.GetRange("A1:D1")
headerRange.Style.Font.Bold = True
headerRange.Style.BackgroundColor = "#4472C4"
headerRange.Style.Font.Color = "#FFFFFF"
' Format currency columns
For rowIndex As Integer = 1 To worksheet.RowCount - 1
    worksheet($"C{rowIndex + 1}").FormatString = "$#,##0.00"
Next
' Add borders to data range
Dim dataRange As Range = worksheet.GetRange($"A1:D{worksheet.RowCount}")
dataRange.Style.Border.TopBorder.Type = BorderType.Thin
dataRange.Style.Border.BottomBorder.Type = BorderType.Thin

These formatting options transform plain data exports into professional-looking spreadsheets. The styling capabilities include font properties, colors, borders, and number formats, allowing you to create exports that match your organization's branding or reporting standards. Explore more cell styling options in the IronXL documentation.

Output

Start your free trial and experience the simplicity of converting DataSets to Excel in VB.NET with IronXL.

Conclusion

Converting DataSets to Excel in VB.NET becomes remarkably straightforward with IronXL. The library eliminates the complexity of traditional Interop approaches while providing powerful features for data export, formatting, and multi-format support. Whether you're exporting in-memory data or database query results, IronXL handles the conversion efficiently without requiring Excel installation.

Key benefits of using IronXL for DataSet to Excel conversion include:

• No Microsoft Office dependencies
• Support for multiple Excel formats (XLSX, XLS, CSV, TSV)
• Automatic worksheet creation from DataTables
• Cross-platform compatibility for cloud and container deployments
• Simple, intuitive API requiring minimal code

To explore additional features, such as chart creation, formula support, and advanced formatting options, visit the IronXL documentation that best fit your needs.

Ready to transform your data export capabilities? Download IronXL, which includes libraries for PDF, barcode, OCR, and more.