Exporting Excel data to a DataTable gives you structured, in-memory access to spreadsheet content that works naturally with databases, UI controls, and LINQ queries. The reverse direction -- writing a DataTable back into an Excel file -- is just as important for report generation and data storage workflows. IronXL handles both directions in pure .NET, with no Microsoft Office dependency and no COM interop setup.
Install IronXL before running any of the examples below:
Once installed, the WorkBook and WorkSheet types expose ToDataTable and ToDataSet methods that map directly onto System.Data structures. You can also reverse the process by writing DataRow values back into a worksheet and saving the file in XLSX or XLS format.
The ToDataTable method converts an entire worksheet -- or a named range -- into a System.Data.DataTable. Pass true to treat the first row as column headers, so the resulting table's column names match your spreadsheet headings exactly.
using IronXL;
using System.Data;
// Load the workbook from disk
WorkBook workbook = WorkBook.Load("customers.xlsx");
// Grab the default (first) worksheet
WorkSheet worksheet = workbook.DefaultWorkSheet;
// Convert the entire sheet; first row becomes column headers
DataTable dataTable = worksheet.ToDataTable(true);
// Iterate every row and print each cell value
foreach (DataRow row in dataTable.Rows)
{
for (int i = 0; i < dataTable.Columns.Count; i++)
{
Console.Write(row[i] + "\t");
}
Console.WriteLine();
}Imports IronXL
Imports System.Data
' Load the workbook from disk
Dim workbook As WorkBook = WorkBook.Load("customers.xlsx")
' Grab the default (first) worksheet
Dim worksheet As WorkSheet = workbook.DefaultWorkSheet
' Convert the entire sheet; first row becomes column headers
Dim dataTable As DataTable = worksheet.ToDataTable(True)
' Iterate every row and print each cell value
For Each row As DataRow In dataTable.Rows
For i As Integer = 0 To dataTable.Columns.Count - 1
Console.Write(row(i) & vbTab)
Next
Console.WriteLine()
NextWorkBook.Load reads the XLSX file without any Office automation. worksheet.ToDataTable(true) iterates each cell in the sheet and maps it to a DataRow, using the first row as DataColumn names. The resulting DataTable is a standard System.Data object -- you can bind it to a DataGridView, pass it to a SqlBulkCopy, or run LINQ queries against it immediately.
The ToDataTable call supports both XLS and XLSX formats, so the same code works for legacy spreadsheets without modification. See the IronXL DataTable and DataSet documentation for the full API reference.
When a worksheet contains multiple data regions, or when you only need a subset of rows and columns, you can export a specific cell range instead of the full sheet. This approach is more efficient for large files because IronXL only processes the selected cells.
using IronXL;
using System.Data;
WorkBook workbook = WorkBook.Load("financial_report.xlsx");
// Access a named worksheet
WorkSheet worksheet = workbook.GetWorkSheet("Summary");
// Select only the region A1:D20 and convert it
DataTable dt = worksheet["A1:D20"].ToDataTable(true);
Console.WriteLine($"Rows: {dt.Rows.Count}, Columns: {dt.Columns.Count}");
foreach (DataRow row in dt.Rows)
{
foreach (var item in row.ItemArray)
{
Console.Write(item + "\t");
}
Console.WriteLine();
}Imports IronXL
Imports System.Data
Dim workbook As WorkBook = WorkBook.Load("financial_report.xlsx")
' Access a named worksheet
Dim worksheet As WorkSheet = workbook.GetWorkSheet("Summary")
' Select only the region A1:D20 and convert it
Dim dt As DataTable = worksheet("A1:D20").ToDataTable(True)
Console.WriteLine($"Rows: {dt.Rows.Count}, Columns: {dt.Columns.Count}")
For Each row As DataRow In dt.Rows
For Each item In row.ItemArray
Console.Write(item & vbTab)
Next
Console.WriteLine()
NextThe bracket syntax worksheet["A1:D20"] returns an IronXL.Range object. Calling ToDataTable on that range limits processing to exactly those cells, which means faster execution and a smaller memory footprint when the source file has tens of thousands of rows.
You can also specify named ranges, or build the range string dynamically at runtime -- for example, $"A1:D{lastRow}" -- making the approach flexible for variable-length data exports. The Range class API reference documents all available selection and query methods.
Workbooks with multiple sheets map naturally onto System.Data.DataSet, where each sheet becomes a separate DataTable. The ToDataSet method performs this conversion in a single call.
using IronXL;
using System.Data;
WorkBook workbook = WorkBook.Load("multi_sheet.xlsx");
// Each worksheet becomes a DataTable inside the DataSet
DataSet dataSet = workbook.ToDataSet();
foreach (DataTable table in dataSet.Tables)
{
Console.WriteLine($"Sheet: {table.TableName}");
Console.WriteLine($"Rows: {table.Rows.Count}");
Console.WriteLine();
}Imports IronXL
Imports System.Data
Dim workbook As WorkBook = WorkBook.Load("multi_sheet.xlsx")
' Each worksheet becomes a DataTable inside the DataSet
Dim dataSet As DataSet = workbook.ToDataSet()
For Each table As DataTable In dataSet.Tables
Console.WriteLine($"Sheet: {table.TableName}")
Console.WriteLine($"Rows: {table.Rows.Count}")
Console.WriteLine()
NextEach DataTable in the returned DataSet uses the original worksheet name as its TableName property, so you can retrieve a specific sheet with dataSet.Tables["Summary"] rather than iterating the collection. This makes it straightforward to build multi-sheet reporting logic or combine data from several worksheets before writing results to a database.
For more ways to work with multiple sheets and navigate workbook structure, see how to open and manage Excel worksheets.
The import-to-DataTable direction is only half the story. When you need to write structured data back out as a spreadsheet -- for reports, downloads, or archival -- you create a new workbook, populate cells from the DataTable, and save.
using IronXL;
using System.Data;
// Build a sample DataTable
DataTable dt = new DataTable();
dt.Columns.Add(new DataColumn("ProductID", typeof(int)));
dt.Columns.Add(new DataColumn("ProductName", typeof(string)));
dt.Columns.Add(new DataColumn("UnitPrice", typeof(decimal)));
dt.Rows.Add(1, "Widget Pro", 29.99m);
dt.Rows.Add(2, "Gadget Max", 49.99m);
dt.Rows.Add(3, "Sensor Kit", 14.50m);
// Create a new workbook and worksheet
WorkBook workbook = WorkBook.Create();
WorkSheet worksheet = workbook.CreateWorkSheet("Products");
// Write column headers from DataTable.Columns
for (int col = 0; col < dt.Columns.Count; col++)
{
worksheet.SetCellValue(0, col, dt.Columns[col].ColumnName);
}
// Write data rows
for (int row = 0; row < dt.Rows.Count; row++)
{
for (int col = 0; col < dt.Columns.Count; col++)
{
worksheet.SetCellValue(row + 1, col, dt.Rows[row][col]);
}
}
// Save as XLSX
workbook.SaveAs("products_export.xlsx");
Console.WriteLine("Export complete.");Imports IronXL
Imports System.Data
' Build a sample DataTable
Dim dt As New DataTable()
dt.Columns.Add(New DataColumn("ProductID", GetType(Integer)))
dt.Columns.Add(New DataColumn("ProductName", GetType(String)))
dt.Columns.Add(New DataColumn("UnitPrice", GetType(Decimal)))
dt.Rows.Add(1, "Widget Pro", 29.99D)
dt.Rows.Add(2, "Gadget Max", 49.99D)
dt.Rows.Add(3, "Sensor Kit", 14.50D)
' Create a new workbook and worksheet
Dim workbook As WorkBook = WorkBook.Create()
Dim worksheet As WorkSheet = workbook.CreateWorkSheet("Products")
' Write column headers from DataTable.Columns
For col As Integer = 0 To dt.Columns.Count - 1
worksheet.SetCellValue(0, col, dt.Columns(col).ColumnName)
Next
' Write data rows
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))
Next
Next
' Save as XLSX
workbook.SaveAs("products_export.xlsx")
Console.WriteLine("Export complete.")SetCellValue(rowIndex, columnIndex, value) accepts object values, so you pass column names for the header row and raw cell values for data rows without any type conversion. The row offset of row + 1 reserves row 0 for headers. The resulting XLSX file is a fully valid spreadsheet -- no placeholders, no temporary files.
For larger DataTables, consider applying column auto-sizing after writing data so content displays cleanly without manual formatting.
One of the most common reasons to export an Excel sheet to a DataTable is to display it in a Windows Forms DataGridView. Once you have a DataTable, binding is a single line of code.
using IronXL;
using System.Data;
using System.Windows.Forms;
WorkBook workbook = WorkBook.Load("inventory.xlsx");
WorkSheet worksheet = workbook.DefaultWorkSheet;
DataTable dataTable = worksheet.ToDataTable(true);
// Bind directly to a DataGridView
dataGridViewInventory.DataSource = dataTable;Imports IronXL
Imports System.Data
Imports System.Windows.Forms
Dim workbook As WorkBook = WorkBook.Load("inventory.xlsx")
Dim worksheet As WorkSheet = workbook.DefaultWorkSheet
Dim dataTable As DataTable = worksheet.ToDataTable(True)
' Bind directly to a DataGridView
dataGridViewInventory.DataSource = dataTableSetting DataSource triggers the DataGridView to read column names and row data from the DataTable. Each DataColumn becomes a grid column, and each DataRow becomes a visible row. You can apply sorting, filtering, and selection behavior through the grid's built-in controls without any additional code.
This pattern works equally well with WPF DataGrid, ASP.NET GridView, and any other .NET data-binding infrastructure. For a step-by-step guide, see how to export a DataGridView to Excel for the reverse direction.
After converting a worksheet to a DataTable, you can run LINQ queries using the AsEnumerable() extension method, which transforms each DataRow into a queryable element.
using IronXL;
using System.Data;
using System.Linq;
WorkBook workbook = WorkBook.Load("sales.xlsx");
DataTable dt = workbook.DefaultWorkSheet.ToDataTable(true);
// Filter rows where the "Revenue" column exceeds 10000
var highRevenue = dt.AsEnumerable()
.Where(row => row.Field<double>("Revenue") > 10000)
.OrderByDescending(row => row.Field<double>("Revenue"))
.ToList();
Console.WriteLine($"High-revenue records: {highRevenue.Count}");
foreach (var row in highRevenue)
{
Console.WriteLine($"{row["Product"]}: {row["Revenue"]:C}");
}Imports IronXL
Imports System.Data
Imports System.Linq
Dim workbook As WorkBook = WorkBook.Load("sales.xlsx")
Dim dt As DataTable = workbook.DefaultWorkSheet.ToDataTable(True)
' Filter rows where the "Revenue" column exceeds 10000
Dim highRevenue = dt.AsEnumerable() _
.Where(Function(row) row.Field(Of Double)("Revenue") > 10000) _
.OrderByDescending(Function(row) row.Field(Of Double)("Revenue")) _
.ToList()
Console.WriteLine($"High-revenue records: {highRevenue.Count}")
For Each row In highRevenue
Console.WriteLine($"{row("Product")}: {row("Revenue"):C}")
NextLINQ queries give you a readable, strongly typed way to filter, sort, group, and project data from a converted worksheet. row.Field<T>(columnName) handles type coercion cleanly, throwing an InvalidCastException if a value cannot be converted rather than returning a silent null. This makes it easier to catch data quality issues early.
Combine this approach with the range-based export from the earlier section to query specific sheet regions rather than full worksheets, keeping memory usage low for large files.
Excel spreadsheets often have gaps -- empty cells, merged regions, or placeholder text. IronXL maps empty cells to DBNull.Value in the exported DataTable, matching standard ADO.NET behavior so your existing null-handling code works without modification.
| Excel cell state | DataTable value | Notes |
|---|---|---|
| Text value | String | Returned as-is |
| Numeric value | Double or Decimal | Depends on cell format |
| Date value | DateTime | Parsed from serial number |
| Boolean value | Boolean | TRUE/FALSE in Excel |
| Empty cell | DBNull.Value | Standard ADO.NET null |
| Formula cell | Computed result | IronXL evaluates the formula first |
When writing a DataTable back to Excel, DBNull.Value fields produce empty cells, preserving the round-trip fidelity. If you need a default string for null fields, replace DBNull.Value with your placeholder before calling SetCellValue.
For further data preparation guidance, Microsoft's DataTable class documentation covers null handling, constraint management, and row states in depth.
For worksheets with many thousands of rows, exporting the entire sheet at once allocates all data in memory simultaneously. Two IronXL patterns reduce peak memory usage:
worksheet["A1:D5000"].ToDataTable(true) to process a bounded region instead of the full sheet.worksheet.RowCount, then loop over fixed-size ranges -- for example, 1,000 rows at a time -- and process each batch before moving to the next.The worksheet.RowCount and worksheet.ColumnCount properties let you build dynamic range strings without hard-coding dimensions:
using IronXL;
using System.Data;
WorkBook workbook = WorkBook.Load("large_dataset.xlsx");
WorkSheet worksheet = workbook.DefaultWorkSheet;
int batchSize = 1000;
int totalRows = worksheet.RowCount;
for (int startRow = 1; startRow <= totalRows; startRow += batchSize)
{
int endRow = Math.Min(startRow + batchSize - 1, totalRows);
string rangeAddress = $"A{startRow}:Z{endRow}";
DataTable batch = worksheet[rangeAddress].ToDataTable(false);
// Process each batch -- write to database, transform, etc.
Console.WriteLine($"Processed rows {startRow} to {endRow}");
}Imports IronXL
Imports System.Data
Dim workbook As WorkBook = WorkBook.Load("large_dataset.xlsx")
Dim worksheet As WorkSheet = workbook.DefaultWorkSheet
Dim batchSize As Integer = 1000
Dim totalRows As Integer = worksheet.RowCount
For startRow As Integer = 1 To totalRows Step batchSize
Dim endRow As Integer = Math.Min(startRow + batchSize - 1, totalRows)
Dim rangeAddress As String = $"A{startRow}:Z{endRow}"
Dim batch As DataTable = worksheet(rangeAddress).ToDataTable(False)
' Process each batch -- write to database, transform, etc.
Console.WriteLine($"Processed rows {startRow} to {endRow}")
NextThis pattern is particularly relevant for reading large Excel files in C# where memory constraints apply.
Once you have a DataTable, inserting it into SQL Server via SqlBulkCopy is the fastest path for large datasets. SqlBulkCopy accepts a DataTable directly and streams rows in batches without building individual INSERT statements.
using IronXL;
using System.Data;
using Microsoft.Data.SqlClient;
WorkBook workbook = WorkBook.Load("orders.xlsx");
DataTable dataTable = workbook.DefaultWorkSheet.ToDataTable(true);
string connectionString = "Server=.;Database=OrdersDB;Trusted_Connection=True;";
using SqlConnection connection = new(connectionString);
connection.Open();
using SqlBulkCopy bulkCopy = new(connection);
bulkCopy.DestinationTableName = "dbo.Orders";
bulkCopy.BatchSize = 500;
bulkCopy.BulkCopyTimeout = 60;
// Map DataTable columns to database columns
bulkCopy.ColumnMappings.Add("OrderID", "OrderID");
bulkCopy.ColumnMappings.Add("CustomerName", "CustomerName");
bulkCopy.ColumnMappings.Add("TotalAmount", "TotalAmount");
bulkCopy.WriteToServer(dataTable);
Console.WriteLine("Bulk insert complete.");Imports IronXL
Imports System.Data
Imports Microsoft.Data.SqlClient
Dim workbook As WorkBook = WorkBook.Load("orders.xlsx")
Dim dataTable As DataTable = workbook.DefaultWorkSheet.ToDataTable(True)
Dim connectionString As String = "Server=.;Database=OrdersDB;Trusted_Connection=True;"
Using connection As New SqlConnection(connectionString)
connection.Open()
Using bulkCopy As New SqlBulkCopy(connection)
bulkCopy.DestinationTableName = "dbo.Orders"
bulkCopy.BatchSize = 500
bulkCopy.BulkCopyTimeout = 60
' Map DataTable columns to database columns
bulkCopy.ColumnMappings.Add("OrderID", "OrderID")
bulkCopy.ColumnMappings.Add("CustomerName", "CustomerName")
bulkCopy.ColumnMappings.Add("TotalAmount", "TotalAmount")
bulkCopy.WriteToServer(dataTable)
End Using
End Using
Console.WriteLine("Bulk insert complete.")The ColumnMappings collection matches DataTable column names to database column names. If both names are identical, you can omit individual mappings and SqlBulkCopy will auto-match by name. Explicit mappings are safer when the spreadsheet column order differs from the database schema.
For more on combining IronXL with database workflows, the C# Excel import guide covers additional import scenarios including validation before insert.
For Entity Framework-based projects, convert your DataTable rows to typed model objects first, then use DbContext.BulkInsert from a library like EFCore.BulkExtensions for ORM-aware batch insertion. Microsoft's SqlBulkCopy documentation explains batching options and transaction support in detail.
You now have a complete toolkit for moving data between Excel and DataTable in both directions using IronXL:
worksheet.ToDataTable(true) for header-aware exportsworksheet["A1:D20"].ToDataTable(true) syntaxworkbook.ToDataSet() for full DataSet accessDataTable content back to XLSX by iterating DataRow values with SetCellValueDataTable to UI controls like DataGridView with a single DataSource assignmentSqlBulkCopy directly from a DataTable for high-throughput database insertsTo deepen your IronXL skills, explore these related topics:
Start with a free IronXL trial license to run these examples in your own projects. When you are ready to deploy, purchase a production license or chat with the team for licensing guidance.
Curtis Chau holds a Bachelor’s degree in Computer Science (Carleton University) and specializes in front-end development with expertise in Node.js, TypeScript, JavaScript, and React. Passionate about crafting intuitive and aesthetically pleasing user interfaces, Curtis enjoys working with modern frameworks and creating well-structured, visually appealing manuals.
IronXL is a part of IRONSUITE
10 .NET API products for your office documents
The Excel API you need, without the Office Interop hassle.
