Message icon
Skip to footer content
  1. Iron Software
  2. IronXL Blog
  3. Using IronXL
  4. Export Datatble Using Oledb
USING IRONXL

Export DataTable to Excel C# Using OleDb vs IronXL

Jordi Bardia
Jordi Bardia
Updated:

Exporting a DataTable to an Excel file in C# -- without any Office dependencies -- is straightforward with the right library. You call WorkBook.Create(), write headers from dataTable.Columns, loop through dataTable.Rows to populate cells, then save with workbook.SaveAs("output.xlsx"). The result is a fully formatted XLSX file that works on any platform, including .NET 10, Linux containers, and Azure serverless functions.

This guide compares the legacy OleDb approach with the modern IronXL for .NET method. You will see why the OleDb driver creates deployment and compatibility problems, and how to replace it with clean, maintainable code that handles column headers, data types, cell formatting, and large datasets without SQL syntax or Windows-only driver installations.

How Do You Install IronXL for DataTable Export?

Before writing any export code, add IronXL to your project. Open the Package Manager Console in Visual Studio and run:

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

IronXL has no dependency on Microsoft Office, the Access Database Engine, or any COM interop layer. Once the package is installed, you can run the same code on Windows, Linux, macOS, Docker, Azure, and AWS without changing a single line.

Why OleDb Requires Driver Installation

The traditional OleDb approach treats an Excel file as a database. You establish an OleDbConnection using the Microsoft.ACE.OLEDB.12.0 provider (or the older Microsoft.Jet.OLEDB.4.0 for .xls files), then execute SQL CREATE TABLE and INSERT INTO commands against the spreadsheet. This works on developer machines where Microsoft Office or the standalone Access Database Engine redistributable is already installed. However, production environments -- especially cloud functions, Docker containers, and server farms running headless .NET 10 -- rarely have that driver available, and installing it requires elevated privileges and a matching bitness between the driver and your process.

Why Do Developers Still Use OleDb for Excel Exports?

OleDb became a default choice for Excel automation because it was already part of the .NET Framework. Developers already knew ADO.NET, so reusing OleDbConnection, OleDbCommand, and OleDbDataAdapter to write to Excel felt natural. No third-party package was needed, and the SQL-like syntax for CREATE TABLE and INSERT INTO made the intent obvious to anyone familiar with database programming.

Here is the traditional pattern for exporting a DataTable using OleDb:

using System.Data;
using System.Data.OleDb;

// Build a sample DataTable
DataTable dataTable = new DataTable("Products");
dataTable.Columns.Add("ProductID", typeof(int));
dataTable.Columns.Add("ProductName", typeof(string));
dataTable.Columns.Add("Price", typeof(decimal));
dataTable.Rows.Add(1, "Widget", 29.99m);
dataTable.Rows.Add(2, "Gadget", 49.99m);
dataTable.Rows.Add(3, "Gizmo", 19.99m);

// OleDb connection string -- file must already exist on disk
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'";

using OleDbConnection connection = new(connectionString);
connection.Open();

// Create the sheet structure with SQL DDL
string create = "CREATE TABLE [Sheet1$] (ProductID INT, ProductName CHAR(255), Price DOUBLE)";
using (var createCmd = new OleDbCommand(create, connection))
    createCmd.ExecuteNonQuery();

// Insert rows one at a time
string insert = "INSERT INTO [Sheet1$] (ProductID, ProductName, Price) VALUES (?, ?, ?)";
using OleDbCommand insertCmd = new(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));

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.Data;
using System.Data.OleDb;

// Build a sample DataTable
DataTable dataTable = new DataTable("Products");
dataTable.Columns.Add("ProductID", typeof(int));
dataTable.Columns.Add("ProductName", typeof(string));
dataTable.Columns.Add("Price", typeof(decimal));
dataTable.Rows.Add(1, "Widget", 29.99m);
dataTable.Rows.Add(2, "Gadget", 49.99m);
dataTable.Rows.Add(3, "Gizmo", 19.99m);

// OleDb connection string -- file must already exist on disk
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'";

using OleDbConnection connection = new(connectionString);
connection.Open();

// Create the sheet structure with SQL DDL
string create = "CREATE TABLE [Sheet1$] (ProductID INT, ProductName CHAR(255), Price DOUBLE)";
using (var createCmd = new OleDbCommand(create, connection))
    createCmd.ExecuteNonQuery();

// Insert rows one at a time
string insert = "INSERT INTO [Sheet1$] (ProductID, ProductName, Price) VALUES (?, ?, ?)";
using OleDbCommand insertCmd = new(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));

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();
}
Imports System.Data
Imports System.Data.OleDb

' Build a sample DataTable
Dim dataTable As New DataTable("Products")
dataTable.Columns.Add("ProductID", GetType(Integer))
dataTable.Columns.Add("ProductName", GetType(String))
dataTable.Columns.Add("Price", GetType(Decimal))
dataTable.Rows.Add(1, "Widget", 29.99D)
dataTable.Rows.Add(2, "Gadget", 49.99D)
dataTable.Rows.Add(3, "Gizmo", 19.99D)

' OleDb connection string -- file must already exist on disk
Dim filename As String = "C:\Output\Products.xlsx"
Dim connectionString As String = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={filename};" &
                                 "Extended Properties='Excel 12.0 Xml;HDR=YES'"

Using connection As New OleDbConnection(connectionString)
    connection.Open()

    ' Create the sheet structure with SQL DDL
    Dim create As String = "CREATE TABLE [Sheet1$] (ProductID INT, ProductName CHAR(255), Price DOUBLE)"
    Using createCmd As New OleDbCommand(create, connection)
        createCmd.ExecuteNonQuery()
    End Using

    ' Insert rows one at a time
    Dim insert As String = "INSERT INTO [Sheet1$] (ProductID, ProductName, Price) VALUES (?, ?, ?)"
    Using insertCmd As 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))

        For Each row As DataRow In dataTable.Rows
            insertCmd.Parameters(0).Value = row("ProductID")
            insertCmd.Parameters(1).Value = row("ProductName")
            insertCmd.Parameters(2).Value = row("Price")
            insertCmd.ExecuteNonQuery()
        Next
    End Using
End Using
$vbLabelText   $csharpLabel

This code inserts each DataRow as a separate database round-trip. For large datasets, that loop becomes a bottleneck because there is no batch insert mechanism available through OleDb. Every row triggers a full SQL parse, parameter bind, and write cycle before moving to the next.

What Are the Key Limitations of OleDb for Excel Export?

Understanding why OleDb falls short helps you justify the migration to your team and choose the right replacement.

Driver Dependencies and Bitness Conflicts

The Microsoft.ACE.OLEDB.12.0 provider must be installed separately on every machine that runs your application. If your process is 64-bit but only the 32-bit Access Database Engine is available -- a common configuration when Microsoft Office 32-bit is installed alongside a 64-bit .NET runtime -- the connection throws an exception at runtime. Microsoft documents these bitness limitations and recommends using a matching bitness between the driver and the calling process, but this is often impractical in shared hosting or containerized environments.

No Cross-Platform Support

OleDb is a Windows-only technology. .NET 10 supports Linux and macOS natively, and modern architectures increasingly run application workloads in Docker containers on Linux nodes. Because the System.Data.OleDb namespace throws a PlatformNotSupportedException on non-Windows operating systems, any code path that uses OleDb is blocked from running on these platforms entirely.

No Cell Formatting Capabilities

OleDb treats the spreadsheet as a flat database table. You can insert raw values, but you have no mechanism to apply cell fonts, background colors, borders, number formats, or column widths. If your export requires a professional-looking report with header row styling, currency formatting, or conditional coloring, OleDb cannot deliver it. You would need a second pass with a different library or the Excel Interop COM object, which adds its own Office dependency.

Excel File Must Already Exist

OleDb cannot create a new Excel workbook from scratch. The file must already exist at the target path before you open the connection. This requires either a pre-seeded template file or a separate step to create the workbook using another library, adding unnecessary complexity to the deployment package.

DELETE Operations Are Not Supported

The OleDb driver for Excel supports INSERT and limited UPDATE operations, but row deletion using DELETE FROM is not supported. Any data manipulation workflow that requires removing rows after export must resort to manual file manipulation or a full file recreation, neither of which is straightforward through the OleDb interface.

How Do You Export a DataTable to Excel With IronXL?

IronXL replaces the entire OleDb pipeline with a direct object model. There are no connection strings, no SQL commands, and no driver requirements. You create a WorkBook, add a WorkSheet, and call SetCellValue() for each cell:

using IronXL;
using System.Data;

// Build the same DataTable
DataTable dataTable = new DataTable("Products");
dataTable.Columns.Add("ProductID", typeof(int));
dataTable.Columns.Add("ProductName", typeof(string));
dataTable.Columns.Add("Price", typeof(decimal));
dataTable.Rows.Add(1, "Widget", 29.99m);
dataTable.Rows.Add(2, "Gadget", 49.99m);
dataTable.Rows.Add(3, "Gizmo", 19.99m);

// Create a new workbook and worksheet -- no template file needed
WorkBook workbook = WorkBook.Create(ExcelFileFormat.XLSX);
WorkSheet worksheet = workbook.CreateWorkSheet("Products");

// Write column headers into the first row
for (int col = 0; col < dataTable.Columns.Count; col++)
{
    worksheet.SetCellValue(0, col, dataTable.Columns[col].ColumnName);
}

// Write data rows
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());
    }
}

workbook.SaveAs("Products.xlsx");
using IronXL;
using System.Data;

// Build the same DataTable
DataTable dataTable = new DataTable("Products");
dataTable.Columns.Add("ProductID", typeof(int));
dataTable.Columns.Add("ProductName", typeof(string));
dataTable.Columns.Add("Price", typeof(decimal));
dataTable.Rows.Add(1, "Widget", 29.99m);
dataTable.Rows.Add(2, "Gadget", 49.99m);
dataTable.Rows.Add(3, "Gizmo", 19.99m);

// Create a new workbook and worksheet -- no template file needed
WorkBook workbook = WorkBook.Create(ExcelFileFormat.XLSX);
WorkSheet worksheet = workbook.CreateWorkSheet("Products");

// Write column headers into the first row
for (int col = 0; col < dataTable.Columns.Count; col++)
{
    worksheet.SetCellValue(0, col, dataTable.Columns[col].ColumnName);
}

// Write data rows
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());
    }
}

workbook.SaveAs("Products.xlsx");
Imports IronXL
Imports System.Data

' Build the same DataTable
Dim dataTable As New DataTable("Products")
dataTable.Columns.Add("ProductID", GetType(Integer))
dataTable.Columns.Add("ProductName", GetType(String))
dataTable.Columns.Add("Price", GetType(Decimal))
dataTable.Rows.Add(1, "Widget", 29.99D)
dataTable.Rows.Add(2, "Gadget", 49.99D)
dataTable.Rows.Add(3, "Gizmo", 19.99D)

' Create a new workbook and worksheet -- no template file needed
Dim workbook As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)
Dim worksheet As WorkSheet = workbook.CreateWorkSheet("Products")

' Write column headers into the first row
For col As Integer = 0 To dataTable.Columns.Count - 1
    worksheet.SetCellValue(0, col, dataTable.Columns(col).ColumnName)
Next

' Write data rows
For row As Integer = 0 To dataTable.Rows.Count - 1
    For col As Integer = 0 To dataTable.Columns.Count - 1
        worksheet.SetCellValue(row + 1, col, dataTable.Rows(row)(col).ToString())
    Next
Next

workbook.SaveAs("Products.xlsx")
$vbLabelText   $csharpLabel

WorkBook.Create(ExcelFileFormat.XLSX) builds a fresh workbook in memory -- no file needs to exist beforehand. CreateWorkSheet("Products") appends a named tab. The nested loops mirror the DataTable's row and column counts exactly, writing one value per cell. SaveAs() flushes the workbook to disk as a fully valid XLSX file.

Output

How to Export DataTable to Excel C# Using OleDb vs IronXL: Image 1 - Excel Output

For a deeper look at the available options, the export DataSet and DataTable how-to guide covers additional overloads and batch-export patterns.

How Do You Add Cell Formatting When Exporting a DataTable?

One of the most common requirements after a basic export is styling the header row to distinguish it from data rows. IronXL exposes a full styling API that OleDb cannot match:

using IronXL;
using IronXL.Styles;
using System.Data;

DataTable dataTable = new DataTable("Products");
dataTable.Columns.Add("ProductID", typeof(int));
dataTable.Columns.Add("ProductName", typeof(string));
dataTable.Columns.Add("Price", typeof(decimal));
dataTable.Rows.Add(1, "Widget", 29.99m);
dataTable.Rows.Add(2, "Gadget", 49.99m);
dataTable.Rows.Add(3, "Gizmo", 19.99m);

WorkBook workbook = WorkBook.Create(ExcelFileFormat.XLSX);
WorkSheet worksheet = workbook.CreateWorkSheet("Products");

// Write and style the header row
for (int col = 0; col < dataTable.Columns.Count; col++)
{
    var headerCell = worksheet[$"{(char)('A' + col)}1"];
    headerCell.Value = dataTable.Columns[col].ColumnName;
    headerCell.Style.Font.Bold = true;
    headerCell.Style.BackgroundColor = "#4472C4";
    headerCell.Style.Font.FontColor = "#FFFFFF";
}

// Write data rows with price column formatted as currency
for (int row = 0; row < dataTable.Rows.Count; row++)
{
    for (int col = 0; col < dataTable.Columns.Count; col++)
    {
        var cell = worksheet[$"{(char)('A' + col)}{row + 2}"];
        cell.Value = dataTable.Rows[row][col].ToString();

        // Apply currency format to the Price column
        if (dataTable.Columns[col].ColumnName == "Price")
            cell.FormatString = "$#,##0.00";
    }
}

// Auto-size columns so content is not clipped
worksheet.AutoSizeColumn(0);
worksheet.AutoSizeColumn(1);
worksheet.AutoSizeColumn(2);

workbook.SaveAs("ProductsStyled.xlsx");
using IronXL;
using IronXL.Styles;
using System.Data;

DataTable dataTable = new DataTable("Products");
dataTable.Columns.Add("ProductID", typeof(int));
dataTable.Columns.Add("ProductName", typeof(string));
dataTable.Columns.Add("Price", typeof(decimal));
dataTable.Rows.Add(1, "Widget", 29.99m);
dataTable.Rows.Add(2, "Gadget", 49.99m);
dataTable.Rows.Add(3, "Gizmo", 19.99m);

WorkBook workbook = WorkBook.Create(ExcelFileFormat.XLSX);
WorkSheet worksheet = workbook.CreateWorkSheet("Products");

// Write and style the header row
for (int col = 0; col < dataTable.Columns.Count; col++)
{
    var headerCell = worksheet[$"{(char)('A' + col)}1"];
    headerCell.Value = dataTable.Columns[col].ColumnName;
    headerCell.Style.Font.Bold = true;
    headerCell.Style.BackgroundColor = "#4472C4";
    headerCell.Style.Font.FontColor = "#FFFFFF";
}

// Write data rows with price column formatted as currency
for (int row = 0; row < dataTable.Rows.Count; row++)
{
    for (int col = 0; col < dataTable.Columns.Count; col++)
    {
        var cell = worksheet[$"{(char)('A' + col)}{row + 2}"];
        cell.Value = dataTable.Rows[row][col].ToString();

        // Apply currency format to the Price column
        if (dataTable.Columns[col].ColumnName == "Price")
            cell.FormatString = "$#,##0.00";
    }
}

// Auto-size columns so content is not clipped
worksheet.AutoSizeColumn(0);
worksheet.AutoSizeColumn(1);
worksheet.AutoSizeColumn(2);

workbook.SaveAs("ProductsStyled.xlsx");
Imports IronXL
Imports IronXL.Styles
Imports System.Data

Dim dataTable As New DataTable("Products")
dataTable.Columns.Add("ProductID", GetType(Integer))
dataTable.Columns.Add("ProductName", GetType(String))
dataTable.Columns.Add("Price", GetType(Decimal))
dataTable.Rows.Add(1, "Widget", 29.99D)
dataTable.Rows.Add(2, "Gadget", 49.99D)
dataTable.Rows.Add(3, "Gizmo", 19.99D)

Dim workbook As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)
Dim worksheet As WorkSheet = workbook.CreateWorkSheet("Products")

' Write and style the header row
For col As Integer = 0 To dataTable.Columns.Count - 1
    Dim headerCell = worksheet($"{ChrW(AscW("A"c) + col)}1")
    headerCell.Value = dataTable.Columns(col).ColumnName
    headerCell.Style.Font.Bold = True
    headerCell.Style.BackgroundColor = "#4472C4"
    headerCell.Style.Font.FontColor = "#FFFFFF"
Next

' Write data rows with price column formatted as currency
For row As Integer = 0 To dataTable.Rows.Count - 1
    For col As Integer = 0 To dataTable.Columns.Count - 1
        Dim cell = worksheet($"{ChrW(AscW("A"c) + col)}{row + 2}")
        cell.Value = dataTable.Rows(row)(col).ToString()

        ' Apply currency format to the Price column
        If dataTable.Columns(col).ColumnName = "Price" Then
            cell.FormatString = "$#,##0.00"
        End If
    Next
Next

' Auto-size columns so content is not clipped
worksheet.AutoSizeColumn(0)
worksheet.AutoSizeColumn(1)
worksheet.AutoSizeColumn(2)

workbook.SaveAs("ProductsStyled.xlsx")
$vbLabelText   $csharpLabel

This produces a file with a blue header row, white header text, and price values displayed with a dollar sign and two decimal places. The cell font size guide and border and alignment reference explain the full set of styling options available.

Handling Large DataTable Exports

For datasets with thousands of rows, performance matters. IronXL writes all cell values in memory before a single disk write at SaveAs(), which is far more efficient than the OleDb pattern of one ExecuteNonQuery() per row. If you need to export very large tables, the fastest way to export DataTable to Excel guide covers memory-efficient streaming techniques.

You can also group export logic with formulas, ranges, and named tables. The create spreadsheet how-to and write Excel in .NET guide provide complete patterns for building multi-sheet workbooks from DataTables.

How Do You Export a DataTable to Excel in ASP.NET?

In a web application, you typically stream the workbook directly to the browser rather than saving it to disk. IronXL supports this with workbook.ToByteArray(), which returns the binary content that you can write into an HTTP response:

// In an ASP.NET controller action
WorkBook workbook = WorkBook.Create(ExcelFileFormat.XLSX);
WorkSheet worksheet = workbook.CreateWorkSheet("Report");

// Assume dataTable is populated from your data source
for (int col = 0; col < dataTable.Columns.Count; col++)
    worksheet.SetCellValue(0, col, dataTable.Columns[col].ColumnName);

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());

byte[] fileBytes = workbook.ToByteArray();
return File(fileBytes, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Report.xlsx");
// In an ASP.NET controller action
WorkBook workbook = WorkBook.Create(ExcelFileFormat.XLSX);
WorkSheet worksheet = workbook.CreateWorkSheet("Report");

// Assume dataTable is populated from your data source
for (int col = 0; col < dataTable.Columns.Count; col++)
    worksheet.SetCellValue(0, col, dataTable.Columns[col].ColumnName);

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());

byte[] fileBytes = workbook.ToByteArray();
return File(fileBytes, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Report.xlsx");
' In an ASP.NET controller action
Dim workbook As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)
Dim worksheet As WorkSheet = workbook.CreateWorkSheet("Report")

' Assume dataTable is populated from your data source
For col As Integer = 0 To dataTable.Columns.Count - 1
    worksheet.SetCellValue(0, col, dataTable.Columns(col).ColumnName)
Next

For row As Integer = 0 To dataTable.Rows.Count - 1
    For col As Integer = 0 To dataTable.Columns.Count - 1
        worksheet.SetCellValue(row + 1, col, dataTable.Rows(row)(col).ToString())
    Next
Next

Dim fileBytes As Byte() = workbook.ToByteArray()
Return File(fileBytes, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Report.xlsx")
$vbLabelText   $csharpLabel

The ASP.NET export DataTable to Excel tutorial walks through the full controller and view wiring for an ASP.NET application. The DataTable to Excel C# guide covers additional scenarios including multi-sheet exports and DataSet support.

Comparing OleDb vs. IronXL at a Glance

Feature comparison between OleDb and IronXL for DataTable to Excel export in C#
Capability OleDb IronXL
Driver installation required Yes (ACE.OLEDB or Jet) No
Cross-platform (.NET 10 / Linux) No Yes
Create new workbook from scratch No Yes
Cell formatting and styling No Yes
Formula support No Yes
Batch insert performance Row-by-row (slow) In-memory (fast)
DELETE row support No Yes
Docker / container support No Yes

How Do You Handle Column Data Types During Export?

When a DataTable column holds numeric or date values, writing them as strings causes Excel to treat the cell as text, which disables sorting, filtering, and formula references. IronXL preserves native types when you pass the value directly rather than calling .ToString():

WorkBook workbook = WorkBook.Create(ExcelFileFormat.XLSX);
WorkSheet worksheet = workbook.CreateWorkSheet("TypedData");

DataTable table = new DataTable();
table.Columns.Add("ID", typeof(int));
table.Columns.Add("Amount", typeof(decimal));
table.Columns.Add("Date", typeof(DateTime));
table.Rows.Add(1, 1500.75m, new DateTime(2025, 6, 15));
table.Rows.Add(2, 3200.00m, new DateTime(2025, 7, 4));

// Write headers
for (int col = 0; col < table.Columns.Count; col++)
    worksheet.SetCellValue(0, col, table.Columns[col].ColumnName);

// Write typed values -- no .ToString() conversion
for (int row = 0; row < table.Rows.Count; row++)
{
    worksheet.SetCellValue(row + 1, 0, (int)table.Rows[row]["ID"]);
    worksheet.SetCellValue(row + 1, 1, (double)(decimal)table.Rows[row]["Amount"]);
    worksheet[$"C{row + 2}"].Value = (DateTime)table.Rows[row]["Date"];
    worksheet[$"C{row + 2}"].FormatString = "yyyy-MM-dd";
}

workbook.SaveAs("TypedData.xlsx");
WorkBook workbook = WorkBook.Create(ExcelFileFormat.XLSX);
WorkSheet worksheet = workbook.CreateWorkSheet("TypedData");

DataTable table = new DataTable();
table.Columns.Add("ID", typeof(int));
table.Columns.Add("Amount", typeof(decimal));
table.Columns.Add("Date", typeof(DateTime));
table.Rows.Add(1, 1500.75m, new DateTime(2025, 6, 15));
table.Rows.Add(2, 3200.00m, new DateTime(2025, 7, 4));

// Write headers
for (int col = 0; col < table.Columns.Count; col++)
    worksheet.SetCellValue(0, col, table.Columns[col].ColumnName);

// Write typed values -- no .ToString() conversion
for (int row = 0; row < table.Rows.Count; row++)
{
    worksheet.SetCellValue(row + 1, 0, (int)table.Rows[row]["ID"]);
    worksheet.SetCellValue(row + 1, 1, (double)(decimal)table.Rows[row]["Amount"]);
    worksheet[$"C{row + 2}"].Value = (DateTime)table.Rows[row]["Date"];
    worksheet[$"C{row + 2}"].FormatString = "yyyy-MM-dd";
}

workbook.SaveAs("TypedData.xlsx");
Imports System
Imports System.Data

Dim workbook As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)
Dim worksheet As WorkSheet = workbook.CreateWorkSheet("TypedData")

Dim table As New DataTable()
table.Columns.Add("ID", GetType(Integer))
table.Columns.Add("Amount", GetType(Decimal))
table.Columns.Add("Date", GetType(DateTime))
table.Rows.Add(1, 1500.75D, New DateTime(2025, 6, 15))
table.Rows.Add(2, 3200.00D, New DateTime(2025, 7, 4))

' Write headers
For col As Integer = 0 To table.Columns.Count - 1
    worksheet.SetCellValue(0, col, table.Columns(col).ColumnName)
Next

' Write typed values -- no .ToString() conversion
For row As Integer = 0 To table.Rows.Count - 1
    worksheet.SetCellValue(row + 1, 0, CInt(table.Rows(row)("ID")))
    worksheet.SetCellValue(row + 1, 1, CDbl(CDec(table.Rows(row)("Amount"))))
    worksheet($"C{row + 2}").Value = CType(table.Rows(row)("Date"), DateTime)
    worksheet($"C{row + 2}").FormatString = "yyyy-MM-dd"
Next

workbook.SaveAs("TypedData.xlsx")
$vbLabelText   $csharpLabel

Excel can now sort the Amount column numerically and filter the Date column with date pickers. For additional data format patterns, see the set cell data format guide. If you need to read data back after export, the Excel to DataTable tutorial covers the reverse operation.

Migrating Existing OleDb Export Code

Migrating from OleDb to IronXL typically takes less time than a single sprint. The structural changes are:

  1. Remove all using System.Data.OleDb references and NuGet references to any ACE driver wrapper package.
  2. Replace the OleDbConnection initialization block with WorkBook.Create(ExcelFileFormat.XLSX).
  3. Replace the CREATE TABLE command with workbook.CreateWorkSheet("SheetName").
  4. Replace the INSERT INTO loop with nested SetCellValue() calls, one per cell.
  5. Replace connection.Close() with workbook.SaveAs("output.xlsx").

You can also call workbook.SaveAs() with an .xls extension to produce legacy format files if downstream consumers have not yet upgraded to Excel 2007 or later. The convert spreadsheet file types guide lists all supported output formats.

How Do You Test and License IronXL?

IronXL is free to use in development with a trial license. You apply the key in code before the first IronXL call:

IronXL.License.LicenseKey = "YOUR-LICENSE-KEY-HERE";
IronXL.License.LicenseKey = "YOUR-LICENSE-KEY-HERE";
Imports IronXL

IronXL.License.LicenseKey = "YOUR-LICENSE-KEY-HERE"
$vbLabelText   $csharpLabel

The license key application guide walks through all placement options, including configuration files and environment variables. For cloud deployments, the Docker and Linux getting-started pages cover platform-specific setup steps.

Microsoft documents the known limitations of the Access Database Engine for OleDb scenarios, and NuGet.org hosts the IronXL package with full version history and download statistics. The official Open XML SDK documentation explains the underlying XLSX specification that IronXL implements.

What Are Your Next Steps?

You now have a working pattern for exporting a DataTable to an XLSX file without OleDb drivers, without SQL syntax, and without platform restrictions. The core workflow -- WorkBook.Create(), CreateWorkSheet(), SetCellValue() in a loop, then SaveAs() -- scales from a three-row sample to datasets with hundreds of thousands of rows.

Start by replacing your existing OleDb export with the basic IronXL pattern shown above, then layer in formatting using the styling API once the data export is verified. The get started guide for IronXL and the features overview are good next reads to understand the full scope of what the library supports. If you work with CSV files as part of the same pipeline, the C# DataTable to CSV guide shows how to produce comma-delimited output from the same DataTable object.

Frequently Asked Questions

What are the limitations of using OleDb to export DataTable to Excel in C#?

Using OleDb to export DataTable to Excel in C# can be frustrating due to its legacy nature. Developers often encounter limitations such as compatibility issues, slower performance, and more complex error handling, making modern alternatives like IronXL more appealing.

How does IronXL improve the process of exporting DataTable to Excel?

IronXL offers a modern approach to exporting DataTable to Excel, providing better performance, broader compatibility, and simplified code. It eliminates common OleDb frustrations, making it easier for .NET developers to manage Excel exports.

Why should I consider switching from OleDb to IronXL for DataTable exports?

Switching to IronXL for DataTable exports offers several advantages, including improved performance, easier implementation, and enhanced compatibility with modern .NET applications, reducing the time and effort needed for development.

Can IronXL handle large DataTable exports more efficiently than OleDb?

Yes, IronXL is designed to efficiently handle large DataTable exports, offering faster processing times and reducing memory usage compared to OleDb, making it ideal for applications dealing with substantial data sets.

Is IronXL compatible with the latest versions of C# and .NET?

IronXL is fully compatible with the latest versions of C# and .NET, ensuring seamless integration with modern applications while providing ongoing updates to support new features and improvements.

What benefits do modern alternatives like IronXL provide over traditional OleDb?

Modern alternatives like IronXL provide benefits such as improved performance, easier error handling, better compatibility with various Excel formats, and simplified code, which can greatly enhance developer productivity.

How does IronXL handle error management compared to OleDb?

IronXL offers simplified error management with clear exception handling, reducing the complexity and potential issues associated with OleDb, thus making it easier for developers to debug and maintain their applications.

What are the common use cases for exporting DataTable to Excel in UI-based .NET applications?

In UI-based .NET applications, exporting DataTable to Excel is commonly used for generating reports, data analysis, and user-friendly data manipulation, often triggered from event handlers using object sender patterns.

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

Iron Support Team

We're online 24 hours, 5 days a week.
Chat
Email
Call Me