Reading Excel data to Data table

As a developer, you might have come across some situations where you need to read the excel data from C# and process the result as per the requirement.

Recently one of my colleagues requested support for converting an Excel file into a data table in C#. I spent some time on the internet and found the ClosedXML NuGet package. This package matches the requirements, and you can use this in commercial projects as well.

Install the ClosedXML using the NuGet package manager or package manager console.

PM> Install-Package ClosedXML

Use the code below to pass the file path as an input and get the Dataset as a return value. You should remember that the first row of Excel is expected to be the header.

static DataSet ExcelToDataSet(string filePath)
{
    var dataSet = new DataSet();
    using var workBook = new XLWorkbook(filePath);
    foreach (var workSheet in workBook.Worksheets)
    {
        var dt = new DataTable(workSheet.Name);
        //Assumes that the first column of the excel sheet having headers
        workSheet.FirstRowUsed().CellsUsed().ToList()
            .ForEach(x =>
            {
                dt.Columns.Add(x.Value.ToString());
            });

        foreach (var row in workSheet.RowsUsed().Skip(1))
        {
            var dr = dt.NewRow();
            for (var i = 0; i < dt.Columns.Count; i++)
            {
                dr[i] = row.Cell(i + 1).Value.ToString();
            }
            dt.Rows.Add(dr);
        }
        dataSet.Tables.Add(dt);
    }
    return dataset;
}

(code referenced from here)

The above code returns a Dataset which includes all the sheets from the given Excel as a Data table. If only one sheet is there in the Excel, you can take the first data table from the result.

The above code works but has not been tested for performance or any other metrics. Feel free to use this code and test for various data loads.

Happy coding...