Message icon
Skip to footer content
  1. IronXL
  2. IronXL Blog
  3. Using IronXL
  4. Convert Dataset to Excel VB .NET
USING IRONXL

How to Convert a DataSet to Excel in C# Using IronXL

Jordi Bardia
Jordi Bardia
Updated:

IronXL enables direct conversion of DataSets to Excel files without Microsoft Office dependencies, supporting XLSX, XLS, CSV formats with automated worksheet creation from DataTables -- ideal for containerized environments and automated pipelines.

Converting DataSets to Excel worksheet files is a common requirement in .NET applications, whether you are 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 straightforward approach that works independently of Microsoft Office.

This tutorial demonstrates how to efficiently convert DataSets with DataTable rows to Excel files in C# 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: Excel's built-in charting and analysis tools are available to end users

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 .NET Framework 4.6.2 and later, .NET Core, and .NET 5 through .NET 10, 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.

Why Does IronXL Work Well in Containerized Environments?

IronXL operates without COM dependencies or native Excel binaries, making it inherently container-friendly. Unlike Office Interop solutions that require complex Docker configurations with Windows containers, IronXL runs efficiently in lightweight Linux containers, reducing image sizes and deployment complexity. The library's architecture aligns well with microservices patterns.

For containerized deployments, IronXL requires no special privileges or system-level installations. The library works with standard Docker configurations and supports multi-stage builds for optimized container images. This compatibility extends to Linux and macOS systems, ensuring consistent behavior across development and production environments.

What Are the Performance Implications for Large DataSets?

IronXL handles memory efficiently through streaming capabilities, processing DataSets with millions of rows without loading everything into memory at once. The library optimizes file writing operations, typically converting a 100,000-row DataSet to Excel in under 5 seconds on standard server hardware. Advanced features like autosize rows and columns can be selectively applied to maintain performance while ensuring readability.

When working with extremely large datasets, consider implementing pagination strategies or splitting data across multiple worksheets. The library's memory-efficient architecture ensures stable performance even under heavy load, making it suitable for high-throughput ETL pipelines and batch processing scenarios. For tips on reading data back into your application, see how to read Excel files in the IronXL documentation.

How Do You Install IronXL in Your .NET Project?

Setting up IronXL takes just a few minutes. Open Visual Studio, navigate to your project, then install IronXL through the NuGet Package Manager. For full setup instructions, consult the IronXL getting started guide.

Using the Package Manager Console or the .NET CLI, run one of the following:

Install-Package IronXL.Excel
dotnet add package IronXL.Excel
Install-Package IronXL.Excel
dotnet add package IronXL.Excel
SHELL

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

Once installed, add the IronXL namespace to your C# file:

using IronXL;
using System.Data;
using IronXL;
using System.Data;
$vbLabelText   $csharpLabel

With these imports in place, you are ready to start converting DataSets to Excel files. For enterprise environments, review the licensing options and the trial license page to ensure proper deployment configuration.

How Do You Perform a Basic DataSet to Excel Conversion?

The following example creates a DataSet with sample product data and exports it directly to an Excel file using C# top-level statements (.NET 10):

using IronXL;
using System.Data;

// Create a sample DataSet with product information
DataSet dataSet = new DataSet("ProductData");

// Create a DataTable for products
DataTable productsTable = new DataTable("Products");
productsTable.Columns.Add("ProductID", typeof(int));
productsTable.Columns.Add("ProductName", typeof(string));
productsTable.Columns.Add("Price", typeof(decimal));
productsTable.Columns.Add("InStock", typeof(bool));

// Add sample rows
productsTable.Rows.Add(1, "Laptop", 999.99m, true);
productsTable.Rows.Add(2, "Mouse", 19.99m, true);
productsTable.Rows.Add(3, "Keyboard", 49.99m, false);
productsTable.Rows.Add(4, "Monitor", 299.99m, true);
productsTable.Rows.Add(5, "Headphones", 79.99m, true);

// Add the DataTable to the DataSet
dataSet.Tables.Add(productsTable);

// Export DataSet to Excel using IronXL
WorkBook workBook = WorkBook.Create(ExcelFileFormat.XLSX);
WorkBook.LoadWorkSheetsFromDataSet(dataSet, workBook);

// Save the Excel file
workBook.SaveAs("ProductData.xlsx");
using IronXL;
using System.Data;

// Create a sample DataSet with product information
DataSet dataSet = new DataSet("ProductData");

// Create a DataTable for products
DataTable productsTable = new DataTable("Products");
productsTable.Columns.Add("ProductID", typeof(int));
productsTable.Columns.Add("ProductName", typeof(string));
productsTable.Columns.Add("Price", typeof(decimal));
productsTable.Columns.Add("InStock", typeof(bool));

// Add sample rows
productsTable.Rows.Add(1, "Laptop", 999.99m, true);
productsTable.Rows.Add(2, "Mouse", 19.99m, true);
productsTable.Rows.Add(3, "Keyboard", 49.99m, false);
productsTable.Rows.Add(4, "Monitor", 299.99m, true);
productsTable.Rows.Add(5, "Headphones", 79.99m, true);

// Add the DataTable to the DataSet
dataSet.Tables.Add(productsTable);

// Export DataSet to Excel using IronXL
WorkBook workBook = WorkBook.Create(ExcelFileFormat.XLSX);
WorkBook.LoadWorkSheetsFromDataSet(dataSet, workBook);

// Save the Excel file
workBook.SaveAs("ProductData.xlsx");
$vbLabelText   $csharpLabel

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. For more complex scenarios, explore the IronXL how-to guides for creating workbooks.

What Happens to Data Types During Conversion?

Excel spreadsheet displaying converted product dataset with ID, Product Name, Price, and In Stock columns showing 5 products including laptop, mouse, keyboard, monitor, and headphones with proper data type preservation

IronXL preserves data types during conversion. Integer values remain numeric, decimals maintain their precision, and boolean values convert to Excel's TRUE/FALSE format. This automatic type preservation ensures that Excel formulas and functions work correctly with the exported data. For custom formatting requirements, use the cell formatting capabilities to apply specific number formats, date formats, or custom patterns.

You can also save the workbook in different formats:

// Save as legacy XLS 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 XLS 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");
$vbLabelText   $csharpLabel

Each format has specific use cases: XLSX for modern Excel compatibility, XLS for legacy systems, and CSV/TSV for universal data exchange with other applications and databases. Learn more about exporting Excel files in the IronXL documentation.

How Do You Export Database Data Directly to Excel?

Real-world applications often require exporting data from SQL databases. The following example queries a database and exports the results to Excel using C# top-level statements:

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

string connectionString = "Data Source=server;Initial Catalog=SampleDB;Integrated Security=True";
string query = "SELECT OrderID, CustomerName, OrderDate, TotalAmount FROM Orders WHERE OrderDate >= '2024-01-01'";

DataSet dataSet = new DataSet();

// Populate DataSet from database
using (SqlConnection connection = new SqlConnection(connectionString))
{
    using (SqlDataAdapter adapter = new SqlDataAdapter(query, connection))
    {
        adapter.Fill(dataSet, "Orders");
    }
}

// Create Excel workbook and load data
WorkBook workBook = WorkBook.Create();
WorkBook.LoadWorkSheetsFromDataSet(dataSet, workBook);

// Auto-size columns for readability
WorkSheet worksheet = workBook.WorkSheets[0];
for (int i = 0; i < worksheet.Columns.Count(); i++)
{
    worksheet.AutoSizeColumn(i);
}

// Save with timestamp in filename
string fileName = $"OrdersExport_{DateTime.Now:yyyyMMdd_HHmmss}.xlsx";
workBook.SaveAs(fileName);
using IronXL;
using System.Data;
using System.Data.SqlClient;

string connectionString = "Data Source=server;Initial Catalog=SampleDB;Integrated Security=True";
string query = "SELECT OrderID, CustomerName, OrderDate, TotalAmount FROM Orders WHERE OrderDate >= '2024-01-01'";

DataSet dataSet = new DataSet();

// Populate DataSet from database
using (SqlConnection connection = new SqlConnection(connectionString))
{
    using (SqlDataAdapter adapter = new SqlDataAdapter(query, connection))
    {
        adapter.Fill(dataSet, "Orders");
    }
}

// Create Excel workbook and load data
WorkBook workBook = WorkBook.Create();
WorkBook.LoadWorkSheetsFromDataSet(dataSet, workBook);

// Auto-size columns for readability
WorkSheet worksheet = workBook.WorkSheets[0];
for (int i = 0; i < worksheet.Columns.Count(); i++)
{
    worksheet.AutoSizeColumn(i);
}

// Save with timestamp in filename
string fileName = $"OrdersExport_{DateTime.Now:yyyyMMdd_HHmmss}.xlsx";
workBook.SaveAs(fileName);
$vbLabelText   $csharpLabel

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 all data is visible without manual column width adjustments. For handling larger datasets, see the guide on converting Excel to DataSet and importing data into workbooks.

How Do You Optimize Database Exports for Performance?

SQL query results showing OrderID, CustomerName, OrderDate, and TotalAmount columns from Orders table with sample data ready for Excel export

For optimal performance with large database exports, implement batch processing. Use SqlDataReader for memory-efficient data retrieval and process records in chunks. According to Microsoft's documentation on ADO.NET DataSets, batching is the recommended strategy for large result sets. The NuGet package page for IronXL.Excel lists all supported target frameworks and version history.

Consider implementing parallel processing for multi-table exports and use connection pooling to minimize database load during exports. The IronXL open workbook guide provides additional patterns for reading and modifying existing workbooks as part of an export pipeline.

What Security Considerations Apply to Database Exports?

Excel spreadsheet showing exported data with names, dates, times, and numerical values demonstrating successful dataset conversion with proper formatting

Implement connection string encryption, use parameterized queries to prevent SQL injection, and consider data masking for sensitive fields. In containerized environments, store connection strings as secrets rather than plain environment variables. The OWASP SQL Injection Prevention cheat sheet provides a thorough reference for securing database access in any .NET application.

Protecting the resulting Excel file is equally important. The IronXL write Excel file guide covers workbook creation and saving patterns. For access control, apply worksheet or workbook password protection for sensitive exports, and audit all export operations at the application level.

When Should You Implement Pagination for Large Exports?

For DataSets exceeding 100,000 rows, implement pagination to prevent memory exhaustion. Process data in chunks of 10,000 to 50,000 rows, writing to the Excel file incrementally. This approach maintains consistent memory usage regardless of total dataset size and keeps your application responsive under load.

How Do You Handle Multiple DataTables in One Excel File?

When working with related data, a single DataSet may contain multiple DataTables. IronXL converts each table into a separate worksheet automatically:

using IronXL;
using System.Data;

DataSet dataSet = new DataSet("CompanyData");

// Create Employees table
DataTable employeesTable = new DataTable("Employees");
employeesTable.Columns.Add("EmployeeID", typeof(int));
employeesTable.Columns.Add("Name", typeof(string));
employeesTable.Columns.Add("Department", typeof(string));
employeesTable.Rows.Add(1, "John Smith", "Sales");
employeesTable.Rows.Add(2, "Jane Doe", "Marketing");

// Create Departments table
DataTable departmentsTable = new DataTable("Departments");
departmentsTable.Columns.Add("DepartmentName", typeof(string));
departmentsTable.Columns.Add("Budget", typeof(decimal));
departmentsTable.Rows.Add("Sales", 100000m);
departmentsTable.Rows.Add("Marketing", 75000m);

// Add both tables to DataSet
dataSet.Tables.Add(employeesTable);
dataSet.Tables.Add(departmentsTable);

// Convert to Excel -- each DataTable becomes a worksheet
WorkBook workBook = WorkBook.Create();
WorkBook.LoadWorkSheetsFromDataSet(dataSet, workBook);

// Rename worksheets for clarity
workBook.WorkSheets[0].Name = "Employee List";
workBook.WorkSheets[1].Name = "Department Budgets";

workBook.SaveAs("CompanyData.xlsx");
using IronXL;
using System.Data;

DataSet dataSet = new DataSet("CompanyData");

// Create Employees table
DataTable employeesTable = new DataTable("Employees");
employeesTable.Columns.Add("EmployeeID", typeof(int));
employeesTable.Columns.Add("Name", typeof(string));
employeesTable.Columns.Add("Department", typeof(string));
employeesTable.Rows.Add(1, "John Smith", "Sales");
employeesTable.Rows.Add(2, "Jane Doe", "Marketing");

// Create Departments table
DataTable departmentsTable = new DataTable("Departments");
departmentsTable.Columns.Add("DepartmentName", typeof(string));
departmentsTable.Columns.Add("Budget", typeof(decimal));
departmentsTable.Rows.Add("Sales", 100000m);
departmentsTable.Rows.Add("Marketing", 75000m);

// Add both tables to DataSet
dataSet.Tables.Add(employeesTable);
dataSet.Tables.Add(departmentsTable);

// Convert to Excel -- each DataTable becomes a worksheet
WorkBook workBook = WorkBook.Create();
WorkBook.LoadWorkSheetsFromDataSet(dataSet, workBook);

// Rename worksheets for clarity
workBook.WorkSheets[0].Name = "Employee List";
workBook.WorkSheets[1].Name = "Department Budgets";

workBook.SaveAs("CompanyData.xlsx");
$vbLabelText   $csharpLabel

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 reports that require individual worksheets for different data categories, all within a single file.

What Are the Worksheet Naming Conventions and Limitations?

Excel workbook showing Employee List worksheet with employee names and departments in a multi-worksheet structure with Department Budgets tab visible

Department Budgets worksheet displaying Sales ($100,000) and Marketing ($75,000) budget allocations in a clean tabular format

Excel worksheet names must be 31 characters or fewer and cannot contain special characters such as :, \, /, ?, *, [, or ]. IronXL automatically sanitizes invalid names, but implementing your own validation ensures consistent naming across exports. For a full overview of worksheet operations, see the IronXL how-to guide on merging cells and related formatting tasks.

For multi-worksheet workbooks, adopt a naming strategy that includes prefixes for categorization and timestamps for versioning. This makes workbooks easier to navigate and audit in automated reporting scenarios.

How Do You Maintain Relationships Between DataTables in Excel?

While Excel does not support DataSet relationships directly, you can add hyperlinks between related worksheets or use VLOOKUP formulas to cross-reference data. A summary worksheet with navigation links to all data sheets also improves user experience considerably.

The following table summarizes recommended approaches for common multi-table scenarios:

Strategies for Multi-DataTable Excel Exports
Scenario Recommended Strategy IronXL Feature
Parent-child data Separate worksheets with VLOOKUP cross-references LoadWorkSheetsFromDataSet
Summary + detail Summary worksheet with hyperlinks to detail sheets WorkSheet.Name + hyperlink API
Versioned snapshots Timestamp-prefixed worksheet names WorkSheet.Name assignment
Large multi-table exports Paginated batch processing per table WorkBook.Create + incremental SaveAs

How Do You Add Formatting to Your Excel Export?

IronXL automatically handles basic formatting, but you can enhance your Excel exports with additional styling to create professional-looking reports:

using IronXL;

// Assume workBook is already populated from DataSet
WorkSheet worksheet = workBook.WorkSheets[0];

// Style the header row
IronXL.Range headerRange = worksheet.GetRange("A1:D1");
headerRange.Style.Font.Bold = true;
headerRange.Style.BackgroundColor = "#4472C4";
headerRange.Style.Font.Color = "#FFFFFF";

// Format currency column (column C, rows 2 onward)
for (int rowIndex = 1; rowIndex < worksheet.RowCount; rowIndex++)
{
    worksheet[$"C{rowIndex + 1}"].FormatString = "$#,##0.00";
}

// Add borders to the data range
IronXL.Range dataRange = worksheet.GetRange($"A1:D{worksheet.RowCount}");
dataRange.Style.Border.TopBorder.Type = IronXL.Styles.BorderType.Thin;
dataRange.Style.Border.BottomBorder.Type = IronXL.Styles.BorderType.Thin;

workBook.SaveAs("FormattedExport.xlsx");
using IronXL;

// Assume workBook is already populated from DataSet
WorkSheet worksheet = workBook.WorkSheets[0];

// Style the header row
IronXL.Range headerRange = worksheet.GetRange("A1:D1");
headerRange.Style.Font.Bold = true;
headerRange.Style.BackgroundColor = "#4472C4";
headerRange.Style.Font.Color = "#FFFFFF";

// Format currency column (column C, rows 2 onward)
for (int rowIndex = 1; rowIndex < worksheet.RowCount; rowIndex++)
{
    worksheet[$"C{rowIndex + 1}"].FormatString = "$#,##0.00";
}

// Add borders to the data range
IronXL.Range dataRange = worksheet.GetRange($"A1:D{worksheet.RowCount}");
dataRange.Style.Border.TopBorder.Type = IronXL.Styles.BorderType.Thin;
dataRange.Style.Border.BottomBorder.Type = IronXL.Styles.BorderType.Thin;

workBook.SaveAs("FormattedExport.xlsx");
$vbLabelText   $csharpLabel

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 reporting standards. For more on cell-level formatting, see the IronXL cell formatting guide.

How Does Formatting Affect File Size and Performance?

Formatted Excel export showing employee data with styled headers, proper column alignment, and professional appearance across multiple worksheets

Formatting adds minimal overhead -- typically 5-10% to file size. Apply formatting selectively to headers and summary rows rather than entire columns for optimal performance. Batch style operations using ranges instead of cell-by-cell formatting. For large-scale exports, consider implementing style templates that can be applied consistently across multiple worksheets.

For automated reports, conditional formatting, data bars, and color scales provide visual insights without manual intervention. Alternating row colors improve readability and freeze panes help users navigate large datasets. The IronXL documentation covers all available styling and layout options.

What Formatting Features Work Best in Automated Reports?

Conditional formatting and color scales provide visual cues based on data values, enhancing readability without manual work. Freeze panes for header rows in large datasets improve navigation, and print setup options ensure automated reports print correctly without manual adjustments.

For scheduled or pipeline-driven reports, combine header styling, number formatting, and auto-sized columns in a reusable helper method. This keeps export code consistent across multiple report types and reduces maintenance overhead.

What Should You Know Before Deploying to Production?

Before deploying a DataSet-to-Excel pipeline to production, verify the following considerations:

Licensing: IronXL requires a valid license key for production use. You can start with a free trial license and upgrade to a paid plan as needed. License tiers are detailed on the IronXL licensing page.

IronXL flexible licensing tiers showing Subscription for single developers, Perpetual licenses starting at $599 with team options, and Enterprise solutions with support

Error handling: Implement try-catch blocks around workbook creation and file save operations. Validate DataSet content -- specifically that tables and columns are non-null -- before passing to IronXL. Log failures with enough context to diagnose issues in production.

File path management: In containerized environments, use environment variables or volume mounts for output paths. Avoid hardcoded paths that may not exist in all deployment targets. Here is a container-friendly pattern:

using IronXL;

string outputPath = Environment.GetEnvironmentVariable("EXCEL_OUTPUT_PATH") ?? "/app/output";
string fileName = System.IO.Path.Combine(outputPath, $"Export_{Guid.NewGuid()}.xlsx");
workBook.SaveAs(fileName);
using IronXL;

string outputPath = Environment.GetEnvironmentVariable("EXCEL_OUTPUT_PATH") ?? "/app/output";
string fileName = System.IO.Path.Combine(outputPath, $"Export_{Guid.NewGuid()}.xlsx");
workBook.SaveAs(fileName);
$vbLabelText   $csharpLabel

Platform support: Verify that your target environment is listed on the IronXL features page. IronXL supports Windows, Linux, macOS, Docker, AWS, and Azure out of the box.

Key benefits recap:

  • No Microsoft Office dependencies -- suitable for server and container deployments
  • Support for multiple Excel formats (XLSX, XLS, CSV, TSV)
  • Automatic worksheet creation from DataTables
  • Cross-platform compatibility for cloud and container deployments
  • Intuitive API requiring minimal code to convert full DataSets

To explore additional features such as chart creation, formula support, and advanced formatting, visit the IronXL documentation and the IronXL how-to library.

Frequently Asked Questions

How can I convert a DataSet to an Excel file in VB.NET?

You can efficiently convert a DataSet to an Excel file in VB.NET by using IronXL. It provides a straightforward method that does not require Microsoft Office or complex COM references.

What are the benefits of using IronXL for converting DataSets to Excel?

IronXL simplifies the process of converting DataSets to Excel by eliminating the need for Microsoft Office and complex COM references. It offers a streamlined and efficient solution for VB.NET developers.

Do I need Microsoft Office installed to use IronXL?

No, IronXL operates independently of Microsoft Office, so you don't need Office installed to convert DataSets to Excel files.

Is IronXL compatible with VB.NET applications?

Yes, IronXL is fully compatible with VB.NET applications, making it easy to integrate and use for exporting DataSets to Excel.

Can IronXL handle large DataSets for Excel conversion?

IronXL is designed to handle large DataSets efficiently, ensuring smooth conversion to Excel without performance issues.

What are the steps to convert a DataSet to Excel using IronXL?

The conversion involves loading your DataSet into IronXL and then saving it as an Excel file. IronXL's API provides clear instructions and functions for each step.

Does IronXL support exporting database queries?

Yes, IronXL supports exporting database queries to Excel, simplifying report generation and data analysis tasks.

Are there any prerequisites for using IronXL in VB.NET?

There are no specific prerequisites other than having a VB.NET development environment. IronXL does not require additional installations like Microsoft Office.

How does IronXL improve the data backup process in VB.NET?

IronXL streamlines data backup by allowing easy conversion of DataSets to Excel, providing a reliable and accessible format for data storage.

Is there a guide for using IronXL with VB.NET?

Yes, IronXL provides detailed documentation and guides specifically for VB.NET developers, including step-by-step instructions for tasks like converting DataSets to Excel.

Jordi Bardia
Jordi Bardia
  Chat with engineering team now
Software Engineer
Jordi is most proficient in Python, C# and C++, when he isn’t leveraging his skills at Iron Software; he’s game programming. Sharing responsibilities for product testing, product development and research, Jordi adds immense value to continual product improvement. The varied experience keeps him challenged and engaged, and he ...
Read More

Related Articles

30-day trial → Full product. No limits. No card. Start Free Trial