Jeremy K.

Posted on Jan 22

Convert Excel to DataTable in C#

#csharp #programming

In enterprise-grade .NET development, converting data between Excel files and databases/in-memory datasets is a common high-priority requirement. As a universal in-memory data structure in the .NET ecosystem, DataTable is widely used to ingest Excel data for downstream processing—such as data validation, database persistence, and UI display. This guide walks through how to efficiently convert Excel files to DataTable using the free library Free Spire.XLS for .NET.

I. Overview & Installation of Free Spire.XLS

1. Key Benefits

Built-in ExportDataTable method eliminates manual cell iteration, reducing code volume by up to 80%;
No dependency on Microsoft Office/Excel client installations, enabling seamless cross-platform deployment;
Full support for mainstream Excel formats (.xls, .xlsx) with intuitive, beginner-friendly API design;

Note: The free edition has a data volume limit and is ideal for small projects.

2. Install Free Spire.XLS

The easiest way to install Free Spire.XLS is via NuGet Package Manager. Follow these steps:

Right-click your project in Visual Studio → Select Manage NuGet Packages;
Navigate to the Browse tab, search for "Free Spire.XLS", and install the latest stable version;
Alternatively, run this command in the Package Manager Console:

Install-Package FreeSpire.XLS

II. Core Implementation: Convert Excel to DataTable

1. Convert a Specific Worksheet to DataTable

The code below implements the end-to-end conversion workflow: Load Excel File → Select Target Worksheet → Convert Data to DataTable → Validate & Output Results. This is the minimal, production-ready implementation for the most common use cases:

using Spire.Xls;
using System;
using System.Data;

namespace ExcelToDataTableDemo
{
    internal class Program
    {
        static void Main()
        {
            // Define path to your Excel file (update this to your actual file path)
            string excelFilePath = "Sample.xlsx";

            // Step 1: Initialize Workbook and load the Excel file
            using (Workbook workbook = new Workbook()) // Use 'using' to dispose resources properly
            {
                workbook.LoadFromFile(excelFilePath);

                // Step 2: Get the first worksheet (index is 0-based)
                Worksheet targetSheet = workbook.Worksheets[0];

                // Step 3: Core conversion - use ExportDataTable (most critical step)
                // Parameters:
                // - AllocatedRange: Auto-detects the used data range in the worksheet (avoids empty rows/columns)
                // - exportColumnNames: Set to 'true' to use the first row as DataTable column headers
                DataTable dataTable = targetSheet.ExportDataTable(targetSheet.AllocatedRange, true);

                // Step 4: Validate and print conversion results
                PrintDataTable(dataTable);
            }
        }

        // Helper method to print DataTable content (improves code reusability)
        private static void PrintDataTable(DataTable dt)
        {
            Console.WriteLine("=== Excel to DataTable Conversion Results ===");
            Console.WriteLine($"Total Data Rows (excluding header): {dt.Rows.Count}");

            // Print column names
            Console.Write("Column Headers: ");
            foreach (DataColumn column in dt.Columns)
            {
                Console.Write($"{column.ColumnName}\t");
            }

            // Print row data
            Console.WriteLine("\n=== Data Content ===");
            foreach (DataRow row in dt.Rows)
            {
                foreach (var cellValue in row.ItemArray)
                {
                    // Handle null values to avoid empty tabs in output
                    Console.Write($"{(cellValue ?? "N/A")}\t");
                }
                Console.WriteLine();
            }
        }
    }
}

2. Advanced Usage: Customize Conversion Range & Parameters

The ExportDataTable method offers multiple overloads to support custom conversion rules (e.g., specific cell ranges, formula calculation). Below are the most common advanced scenarios:

Scenario 1: Convert a Specific Cell Range (Rows 2-10, Columns A-C)

Use this when you need to exclude headers or process only a subset of the worksheet data:

// Define target range (A2 to C10) - adjust to your needs
CellRange customRange = targetSheet.Range["A2:C10"];

// Convert range (set exportColumnNames to false since range starts at row 2)
DataTable filteredTable = targetSheet.ExportDataTable(customRange, false);

Scenario 2: Convert Excel with Formulas (Calculate Formula Values)

If your Excel file contains formulas, use this overload to export calculated values instead of the formula strings:

// Overload parameters:
// 1. range: The cell range to convert
// 2. exportColumnNames: Whether to use the first row as column headers
// 3. computedFormulaValue: Whether to calculate formula results (true = export values, false = export formulas)
DataTable tableWithFormulas = targetSheet.ExportDataTable(
    targetSheet.AllocatedRange,
    true,
    true
);

III. Best Practices for Production Use

Error Handling: Add try-catch blocks to handle common exceptions (e.g., FileNotFoundException for missing Excel files, InvalidDataException for corrupted files);
Resource Management: Always wrap Workbook in a using statement to release file handles immediately;
Data Validation: After conversion, validate DataTable data (e.g., check for required columns, data type consistency) before downstream processing;

With Free Spire.XLS’s ExportDataTable method, you can achieve production-grade Excel-to-DataTable conversion in a single line of core code—no manual cell looping, no Office dependencies, and minimal boilerplate. This approach balances simplicity, performance, and maintainability for most .NET enterprise applications.

DEV Community