Skip to footer content
  1. Iron Software
  2. IronXL Blog
  3. Using IronXL
  4. Export Excel Datatable
USING IRONXL

How to Export Excel to DataTable C# with IronXL

Jordi Bardia
Jordi Bardia
Published:

Converting Excel data to a DataTable provides structured data ideal for database operations, data binding to UI controls, and seamless .NET integration. The reverse operation, exporting a DataTable to an Excel file, is equally valuable for generating reports and data storage solutions. Whether you need to export Excel to DataTable C# applications or import data from spreadsheets, IronXL handles both directions efficiently.

IronXL is a .NET library that simplifies these conversions without Microsoft Office dependencies. The ToDataTable method transforms an Excel worksheet into a DataTable object, while the library also supports creating new workbook files from DataTable sources. This .NET Framework and .NET Core compatible library works across Windows, Linux, and macOS. Start your free trial to explore these capabilities.

How Do I Convert an Entire Excel Worksheet to a DataTable?

The ToDataTable method (functioning like an ExportDataTable method) converts an entire worksheet or specific range into a System.Data.DataTable. Pass true as the parameter to use the first row as column headers, ensuring your column names match your Excel data structure.

The following code shows how to convert Excel to DataTable in C#:

using IronXL;
using System;
using System.Data;
class Program
{
    static void Main(string[] args)
    {
        // Load the Excel file workbook using string filepath
        string filepath = "customers.xlsx";
        WorkBook workbook = WorkBook.Load(filepath);
        // Access the first worksheet (default sheet)
        WorkSheet worksheet = workbook.DefaultWorkSheet;
        // Convert Excel to DataTable with first row as column headers
        DataTable dataTable = worksheet.ToDataTable(true);
        // Iterate through rows and columns to read Excel data
        foreach (DataRow row in dataTable.Rows)
        {
            for (int i = 0; i < dataTable.Columns.Count; i++)
            {
                Console.Write(row[i] + "\t");
            }
            Console.WriteLine();
        }
    }
}
using IronXL;
using System;
using System.Data;
class Program
{
    static void Main(string[] args)
    {
        // Load the Excel file workbook using string filepath
        string filepath = "customers.xlsx";
        WorkBook workbook = WorkBook.Load(filepath);
        // Access the first worksheet (default sheet)
        WorkSheet worksheet = workbook.DefaultWorkSheet;
        // Convert Excel to DataTable with first row as column headers
        DataTable dataTable = worksheet.ToDataTable(true);
        // Iterate through rows and columns to read Excel data
        foreach (DataRow row in dataTable.Rows)
        {
            for (int i = 0; i < dataTable.Columns.Count; i++)
            {
                Console.Write(row[i] + "\t");
            }
            Console.WriteLine();
        }
    }
}
$vbLabelText   $csharpLabel

Output

How to Export Excel to DataTable C# with IronXL: Image 1 - Output for converting Excel data into a DataTable

This code loads an Excel file using WorkBook.Load, then accesses the default worksheet. The ToDataTable(true) call performs converting Excel data to a DataTable object, treating the first row as column names. The foreach loop demonstrates how to read Excel data from the resulting DataTable by iterating through each DataRow row and accessing cells by index. You can also query this data using LINQ once converted.

IronXL supports XLSX files without requiring Microsoft Office installation. Install the .NET library via NuGet Package Manager in Visual Studio with the command Install-Package IronXL.Excel.

How Can I Export a Specific Range of Cells to a DataTable?

When you need only a portion of your Excel sheet data, use range selection to convert a specific range of cells. This approach is efficient for large Excel files where you only need certain rows and columns from the Excel worksheet.

using IronXL;
using System;
using System.Data;
class Program
{
    static void Main(string[] args)
    {
        string filepath = "financial_report.xlsx";
        WorkBook workbook = WorkBook.Load(filepath);
        // Get worksheet by sheet name
        string sheetName = "Summary";
        WorkSheet worksheet = workbook.GetWorkSheet(sheetName);
        // Export specific range to new DataTable
        DataTable dt = worksheet["A1:D20"].ToDataTable(true);
        // Process the structured data and return dt reference
        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();
        }
    }
}
using IronXL;
using System;
using System.Data;
class Program
{
    static void Main(string[] args)
    {
        string filepath = "financial_report.xlsx";
        WorkBook workbook = WorkBook.Load(filepath);
        // Get worksheet by sheet name
        string sheetName = "Summary";
        WorkSheet worksheet = workbook.GetWorkSheet(sheetName);
        // Export specific range to new DataTable
        DataTable dt = worksheet["A1:D20"].ToDataTable(true);
        // Process the structured data and return dt reference
        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();
        }
    }
}
$vbLabelText   $csharpLabel

Output

How to Export Excel to DataTable C# with IronXL: Image 2 - Exporting a range of cells to DataTable

The range syntax worksheet["A1:D20"] selects cells from A1 to D20, then the ExportDataTable method converts only that datatable worksheet sheet section. This is particularly useful when your Excel worksheet contains multiple data regions or when you need to import data from a specific range. The Range class documentation provides additional methods for working with cell selections and query operations.

How Do I Convert a Complete Excel File Workbook to a DataSet?

For Excel files containing multiple sheets, the ToDataSet method converts the entire workbook into a new DataSet where each Excel sheet becomes a separate DataTable. This is useful for query operations across multiple worksheets.

using IronXL;
using System;
using System.Data;
class Program
{
    static void Main(string[] args)
    {
        // Load Excel file with multiple sheets
        WorkBook workbook = WorkBook.Load("multi_sheet.xlsx");
        // Convert entire workbook to new DataSet
        DataSet dataSet = workbook.ToDataSet();
        // Iterate through each DataTable (Excel sheet)
        foreach (DataTable table in dataSet.Tables)
        {
            Console.WriteLine($"Sheet: {table.TableName}");
            Console.WriteLine($"Row count: {table.Rows.Count}");
        }
    }
}
using IronXL;
using System;
using System.Data;
class Program
{
    static void Main(string[] args)
    {
        // Load Excel file with multiple sheets
        WorkBook workbook = WorkBook.Load("multi_sheet.xlsx");
        // Convert entire workbook to new DataSet
        DataSet dataSet = workbook.ToDataSet();
        // Iterate through each DataTable (Excel sheet)
        foreach (DataTable table in dataSet.Tables)
        {
            Console.WriteLine($"Sheet: {table.TableName}");
            Console.WriteLine($"Row count: {table.Rows.Count}");
        }
    }
}
$vbLabelText   $csharpLabel

Console Output

How to Export Excel to DataTable C# with IronXL: Image 3 - Console output for converting a multi-paged Excel file to DataSet

This approach is ideal when you need to query data across multiple worksheets or create a unified data source for reporting. Each DataTable in the DataSet retains the original sheet name, making it easy to reference specific sheets programmatically. Learn more about DataSet operations in the IronXL documentation.

How Can I Export DataTable to an Excel File?

Converting Excel data works both directions. To export DataTable content to a new Excel file, create a new workbook, populate cells from your DataTable rows, and save the file. This following example demonstrates the process:

using IronXL;
using System;
using System.Data;
class Program
{
    static void Main(string[] args)
    {
        // Create sample DataTable with new string column names
        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("Price", typeof(decimal)));
        dt.Rows.Add(1, "Widget", 29.99m);
        dt.Rows.Add(2, "Gadget", 49.99m);
        // Create new workbook and first worksheet
        WorkBook workbook = WorkBook.Create();
        WorkSheet worksheet = workbook.CreateWorkSheet("Products");
        // Write column headers from DataTable columns
        for (int i = 0; i < dt.Columns.Count; i++)
        {
            worksheet.SetCellValue(0, i, dt.Columns[i].ColumnName);
        }
        // Write data rows from DataTable
        for (int rowIndex = 0; rowIndex < dt.Rows.Count; rowIndex++)
        {
            DataRow row = dt.Rows[rowIndex];
            for (int colIndex = 0; colIndex < dt.Columns.Count; colIndex++)
            {
                worksheet.SetCellValue(rowIndex + 1, colIndex, row[colIndex]);
            }
        }
        // Save as XLSX file
        workbook.SaveAs("output.xlsx");
        Console.WriteLine("Export complete!");
    }
}
using IronXL;
using System;
using System.Data;
class Program
{
    static void Main(string[] args)
    {
        // Create sample DataTable with new string column names
        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("Price", typeof(decimal)));
        dt.Rows.Add(1, "Widget", 29.99m);
        dt.Rows.Add(2, "Gadget", 49.99m);
        // Create new workbook and first worksheet
        WorkBook workbook = WorkBook.Create();
        WorkSheet worksheet = workbook.CreateWorkSheet("Products");
        // Write column headers from DataTable columns
        for (int i = 0; i < dt.Columns.Count; i++)
        {
            worksheet.SetCellValue(0, i, dt.Columns[i].ColumnName);
        }
        // Write data rows from DataTable
        for (int rowIndex = 0; rowIndex < dt.Rows.Count; rowIndex++)
        {
            DataRow row = dt.Rows[rowIndex];
            for (int colIndex = 0; colIndex < dt.Columns.Count; colIndex++)
            {
                worksheet.SetCellValue(rowIndex + 1, colIndex, row[colIndex]);
            }
        }
        // Save as XLSX file
        workbook.SaveAs("output.xlsx");
        Console.WriteLine("Export complete!");
    }
}
$vbLabelText   $csharpLabel

Output

How to Export Excel to DataTable C# with IronXL: Image 4 - Excel file generated from our DataTable

This code creates a new DataTable with columns and rows, then writes that Excel data to a worksheet. The code writes column headers first, then iterates through each DataRow row to populate cells. The output file supports sorting, filtering, and all standard Excel features.

The following namespaces are required: IronXL, System, and System.Data. For applications using the .NET Framework or .NET Core, IronXL integrates seamlessly as a data source for DataGridView and other UI controls through standard data binding patterns. You can use DataTable in C# applications for efficient data storage and reference throughout your code.

Conclusion

IronXL simplifies converting Excel data to DataTable in C# with intuitive methods like ToDataTable and ToDataSet. Whether you need to read Excel files into structured data for database operations, export DataTable content to new Excel files, or import data from specific ranges, this library handles these tasks efficiently without Microsoft Office dependencies.

The library supports XLS and XLSX formats, provides methods for the entire worksheet or specific range selections, and integrates naturally with System.Data objects. Purchase a license to deploy IronXL in production environments, or chat with our team for technical guidance.

Get stated with IronXL now.
green arrow pointer

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