How to Export DataTable to Excel C# Using OleDb vs IronXL

Introduction to Export Data from DataTable to Excel Sheet

Exporting a DataTable to an Excel file is a fundamental process in many .NET applications, enabling users to share, analyze, and archive data in a universally accessible format. Whether you’re building reporting tools, data migration utilities, or business intelligence dashboards, the ability to move data from a DataTable to an Excel file is essential.

There are several approaches to this process. Traditionally, developers have relied on the OleDb driver to treat Excel files as data sources, using SQL-like commands to create tables and insert data. However, as .NET has evolved, third party libraries such as EPPlus and IronXL have emerged, offering more robust and flexible solutions for working with Excel files. These party libraries often provide advanced features like formatting, formula support, and compatibility with modern Excel formats, making them a popular choice for new projects.

When exporting data, it is important to pay attention to details such as column names and data types. Making sure that your DataTable structure matches the requirements of the Excel file will help prevent errors and ensure that your exported data is accurate and easy to use. The process you choose, whether using the OleDb driver or a third-party library, will affect not only the features available to you but also the complexity and maintainability of your code.

Why Do Developers Use OleDb for Excel Export?

OleDb (Object Linking and Embedding Database) became a popular choice for Excel automation because it treats Excel files like database tables. Developers could use familiar SQL-like syntax to query, import data, and write values to spreadsheets without installing MS Office or the Excel application on the server.

The process typically involves creating an OleDbConnection to an Excel file as the data source, then using OleDbCommand and OleDbDataAdapter objects to execute queries and fill a DataSet or DataTable with results. Here's how the traditional OleDb approach looks when you start exporting data to an XLSX file:

using System;
using System.Data;
using System.Data.OleDb;
// Create a sample DataTable with column names and data types
DataTable dataTable = new DataTable("Products");
dataTable.Columns.Add("ProductID", typeof(int));
dataTable.Columns.Add("ProductName", typeof(string));
dataTable.Columns.Add("Price", typeof(decimal));
// Add rows with values to the data table
dataTable.Rows.Add(1, "Widget", 29.99m);
dataTable.Rows.Add(2, "Gadget", 49.99m);
dataTable.Rows.Add(3, "Gizmo", 19.99m);
// OleDb connection string for Excel file - note the extended properties
string filename = @"C:\Output\Products.xlsx";
string connectionString = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={filename};Extended Properties='Excel 12.0 Xml;HDR=YES'";
// Create new OleDbConnection to the Excel file
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
    connection.Open();
    // Create table command to build the Excel sheet structure
    string create = "CREATE TABLE [Sheet1$] (ProductID INT, ProductName CHAR(255), Price DOUBLE)";
using (var createCmd = new OleDbCommand(create, connection))
    createCmd.ExecuteNonQuery();
     string insert = "INSERT INTO [Sheet1$] (ProductID, ProductName, Price) VALUES (?, ?, ?)";
 using (var insertCmd = new OleDbCommand(insert, connection))
 {
     insertCmd.Parameters.Add(new OleDbParameter("ProductID", OleDbType.Integer));
     insertCmd.Parameters.Add(new OleDbParameter("ProductName", OleDbType.VarChar, 255));
     insertCmd.Parameters.Add(new OleDbParameter("Price", OleDbType.Double));
// Export datatable to Excel C# using OleDb
     foreach (DataRow row in dataTable.Rows)
     {
         insertCmd.Parameters[0].Value = row["ProductID"];
         insertCmd.Parameters[1].Value = row["ProductName"];
         insertCmd.Parameters[2].Value = row["Price"];
         insertCmd.ExecuteNonQuery();
     }
 }
}

using System;
using System.Data;
using System.Data.OleDb;
// Create a sample DataTable with column names and data types
DataTable dataTable = new DataTable("Products");
dataTable.Columns.Add("ProductID", typeof(int));
dataTable.Columns.Add("ProductName", typeof(string));
dataTable.Columns.Add("Price", typeof(decimal));
// Add rows with values to the data table
dataTable.Rows.Add(1, "Widget", 29.99m);
dataTable.Rows.Add(2, "Gadget", 49.99m);
dataTable.Rows.Add(3, "Gizmo", 19.99m);
// OleDb connection string for Excel file - note the extended properties
string filename = @"C:\Output\Products.xlsx";
string connectionString = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={filename};Extended Properties='Excel 12.0 Xml;HDR=YES'";
// Create new OleDbConnection to the Excel file
using (OleDbConnection connection = new OleDbConnection(connectionString))
{
    connection.Open();
    // Create table command to build the Excel sheet structure
    string create = "CREATE TABLE [Sheet1$] (ProductID INT, ProductName CHAR(255), Price DOUBLE)";
using (var createCmd = new OleDbCommand(create, connection))
    createCmd.ExecuteNonQuery();
     string insert = "INSERT INTO [Sheet1$] (ProductID, ProductName, Price) VALUES (?, ?, ?)";
 using (var insertCmd = new OleDbCommand(insert, connection))
 {
     insertCmd.Parameters.Add(new OleDbParameter("ProductID", OleDbType.Integer));
     insertCmd.Parameters.Add(new OleDbParameter("ProductName", OleDbType.VarChar, 255));
     insertCmd.Parameters.Add(new OleDbParameter("Price", OleDbType.Double));
// Export datatable to Excel C# using OleDb
     foreach (DataRow row in dataTable.Rows)
     {
         insertCmd.Parameters[0].Value = row["ProductID"];
         insertCmd.Parameters[1].Value = row["ProductName"];
         insertCmd.Parameters[2].Value = row["Price"];
         insertCmd.ExecuteNonQuery();
     }
 }
}

This sample code creates a new OleDbConnection to an Excel file using the ACE.OLEDB.12.0 provider. For older XLS files, you would use Provider=Microsoft.Jet.OLEDB.4.0 instead. The Extended Properties parameter specifies the Excel format version and indicates that the first row contains headers (HDR=YES).

What Are the Key Limitations of OleDb?

While OleDb works for basic scenarios, developers quickly encounter significant roadblocks that make it unsuitable for production applications:

Driver dependencies present the most immediate challenge. The ACE.OLEDB.12.0 or Microsoft.Jet.OLEDB.4.0 provider must be installed on every machine running the application. This creates deployment headaches, especially in cloud environments, containers, and .NET Core applications where installing Microsoft Office components or the Access database engine isn't practical.

64-bit compatibility issues plague many installations. If your application runs as 64-bit but only the 32-bit Office drivers are installed, the connection fails entirely. Microsoft has acknowledged these limitations in their documentation, with many developers reporting problems with Office 365 configurations.

No formatting support means OleDb can only work with raw data values. Cell styling, fonts, colors, borders, and conditional formatting are completely inaccessible. For any report requiring professional presentation, this third party approach simply cannot deliver the actual output quality you need.

DELETE operations are unsupported, making data manipulation incomplete. You can INSERT and UPDATE but removing a particular row or specific row requires workarounds. Additionally you cannot use OleDb to create a new workbook from scratch, the Excel file must already exist in the target folder before the connection can open it. *

Preparing Data for Export

Before you export a DataTable to an Excel file, it’s crucial to properly prepare your data. This preparation step ensures that the export process runs smoothly and that the resulting Excel file accurately reflects your intended data structure. Start by creating a new DataTable and defining the column names and data types that match your export requirements. This helps prevent syntax errors and data mismatches during the export process.

In C#, you can use the Dim command (in VB.NET) or standard variable declarations to define your DataTable and its columns. Carefully specify the data types for each column, such as integer, string, or date—to ensure compatibility with the Excel file. Populate your DataTable with the data you wish to export, making sure to validate the data for consistency and completeness.

When you’re ready to connect to your data source, constructing the correct connection string is essential. The connection string should specify the path to your Excel file, the appropriate provider (such as Microsoft.Jet.OLEDB.4.0 for older files), and any necessary extended properties. This string acts as the bridge between your application and the Excel file, allowing you to execute commands and transfer data efficiently.

By taking the time to prepare your DataTable and connection details, you set the stage for a successful export process—whether you’re using OleDb, a third party library, or another method.

How to Export DataTable to Excel Without OleDb?

IronXL for .NET eliminates these limitations entirely. As a standalone library with no MS Office dependencies, it provides complete Excel functionality including formatting, formulas, and multi-sheet workbooks. Unlike a third party library that wraps OleDb, IronXL directly creates and manipulates Excel file formats.

First, install IronXL via NuGet Package Manager in Visual Studio:

Install-Package IronXL.Excel

Here's how to export the same DataTable to an XLSX file using IronXL:

using IronXL;
using System;
using System.Data;
// Create a sample DataTable with column names
DataTable dataTable = new DataTable("Products");
dataTable.Columns.Add("ProductID", typeof(int));
dataTable.Columns.Add("ProductName", typeof(string));
dataTable.Columns.Add("Price", typeof(decimal));
// Add rows to the data table
dataTable.Rows.Add(1, "Widget", 29.99m);
dataTable.Rows.Add(2, "Gadget", 49.99m);
dataTable.Rows.Add(3, "Gizmo", 19.99m);
// Create new workbook and Excel worksheet
WorkBook workbook = WorkBook.Create(ExcelFileFormat.XLSX);
WorkSheet worksheet = workbook.CreateWorkSheet("Products");
// Write column headers to the first row
for (int col = 0; col < dataTable.Columns.Count; col++)
{
    worksheet.SetCellValue(0, col, dataTable.Columns[col].ColumnName);
}
// Write data rows - process each row and column
for (int row = 0; row < dataTable.Rows.Count; row++)
{
    for (int col = 0; col < dataTable.Columns.Count; col++)
    {
        worksheet.SetCellValue(row + 1, col, dataTable.Rows[row][col].ToString());
    }
}
// Save the workbook to an Excel file
workbook.SaveAs("Products.xlsx");

using IronXL;
using System;
using System.Data;
// Create a sample DataTable with column names
DataTable dataTable = new DataTable("Products");
dataTable.Columns.Add("ProductID", typeof(int));
dataTable.Columns.Add("ProductName", typeof(string));
dataTable.Columns.Add("Price", typeof(decimal));
// Add rows to the data table
dataTable.Rows.Add(1, "Widget", 29.99m);
dataTable.Rows.Add(2, "Gadget", 49.99m);
dataTable.Rows.Add(3, "Gizmo", 19.99m);
// Create new workbook and Excel worksheet
WorkBook workbook = WorkBook.Create(ExcelFileFormat.XLSX);
WorkSheet worksheet = workbook.CreateWorkSheet("Products");
// Write column headers to the first row
for (int col = 0; col < dataTable.Columns.Count; col++)
{
    worksheet.SetCellValue(0, col, dataTable.Columns[col].ColumnName);
}
// Write data rows - process each row and column
for (int row = 0; row < dataTable.Rows.Count; row++)
{
    for (int col = 0; col < dataTable.Columns.Count; col++)
    {
        worksheet.SetCellValue(row + 1, col, dataTable.Rows[row][col].ToString());
    }
}
// Save the workbook to an Excel file
workbook.SaveAs("Products.xlsx");

The WorkBook.Create() method initializes a new workbook object, with ExcelFileFormat.XLSX specifying the modern Excel format. The CreateWorkSheet() method adds a named Excel sheet where the data will reside.

The nested loops iterate through the DataTable structure, using SetCellValue() to write values to each cell. The first loop handles column names in the first row (index 0), while the second loop processes each data row. The count of rows and columns determines how many cells get populated. Unlike OleDb, this approach gives you direct control over cell placement without SQL query syntax or connection string configuration.

For more details on creating spreadsheets programmatically, the IronXL documentation provides additional code examples.

Working with Excel Files

Interacting with Excel files in .NET can be accomplished through several methods, each with its own advantages. One common approach is to use the Excel application object, which provides direct access to Excel’s features but requires MS Office to be installed on the host machine. Alternatively, third party libraries like IronXL or EPPlus allow you to work with Excel files programmatically, without the need for Office dependencies.

Conclusion

Migrating from OleDb to IronXL for DataTable to Excel exports eliminates driver dependencies, resolves 64-bit compatibility issues, and unlocks formatting capabilities that OleDb simply cannot provide. The transition requires minimal code changes while delivering substantially better reliability and performance across .NET Core, .NET Framework, and modern .NET applications.

Ready to move beyond OleDb limitations? Start a free trial of IronXL to test these capabilities in your own projects. For teams ready to deploy, explore licensing options that scale from individual developers to enterprise-wide deployments.