Get Excel data in DataTable

Question

I'm currently trying to access data from an Excel file in my C# Code. Thats my try:

public static void Main(string[] args)
        {
            var filepath= ".../0f351ee0-0e7b-488b-80c5-db5da81f4bb5.xlsx";
            ReadExcel(file_path, ".xlsx");
            Console.ReadLine();
        }
    enter code here
        public static DataTable ReadExcel(string fileName, string fileExt)
        {
            string conn = string.Empty;
            DataTable dtexcel = new DataTable();
            if (fileExt.CompareTo(".xls") == 0)
                conn = @"provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties='Excel 8.0;HRD=Yes;IMEX=1';"; //for below excel 2007  
            else
                conn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties='Excel 12.0;HDR=NO';"; //for above excel 2007  
            using (OleDbConnection con = new OleDbConnection(conn))
            {
                try
                {
                    OleDbDataAdapter oleAdpt = new OleDbDataAdapter("select * from [Sheet1$]", con); //here we read data from sheet1  
                    oleAdpt.Fill(dtexcel); //fill excel data into dataTable  
                }
                catch { }
            }
            Console.WriteLine(dtexcel);
          
            return dtexcel;

the problem is that the DataTable is always empty. Where exactly can I solve this problem?

Does this answer your question? [Best /Fastest way to read an Excel Sheet into a DataTable?](https://stackoverflow.com/questions/14261655/best-fastest-way-to-read-an-excel-sheet-into-a-datatable) — tripleee, Feb 17 '22 at 08:35
Instead of using the Access drivers, which may not even be installed, use a package that can read Excel directly, like the [ExcelDataReader](https://github.com/ExcelDataReader/ExcelDataReader) package that not only opens a DbDataReader over an Excel file but can load the data from all sheets into a DataSet with a single call: `var ds=reader.AsDataSet();` — Panagiotis Kanavos, Feb 17 '22 at 09:20
@tripleee that's not a good duplicate. Yes, the question is the same but almost all of the answers are outdated or problematic - both Excel interop and JET work only on Windows and can have bitness issues. In the last 9 years other, native .NET and cross-platform options have become available, like ExcelDataReader, Epplus, NPOI, ClosedXML and many others — Panagiotis Kanavos, Feb 17 '22 at 09:24

score 2 · Accepted Answer · answered Feb 17 '22 at 09:34

While the JET/Access driver can read Excel files as if they were databases, it has several problems, especially in the .NET Core era:

It's Windows-only
It needs to be installed. It can't be packaged with your application
The installed version must match the bitness (32/64-bit) of any Office components. This in turn means that your application must match Office's bitness.

There are libraries that can read Excel files directly. One such option is ExcelDataReader, which opens a DbDataReader over an Excel sheet. It can handle both the obsolete xls format and the 16 year old xlsx format (yes, the "new" xlsx format was introduce in 2006, 16 years ago).

The generated data reader can be used to read the data or load a DataTable the same as any other data reader.

using (var stream = File.Open(filePath, FileMode.Open, FileAccess.Read))
{
    using (var reader = ExcelReaderFactory.CreateReader(stream))
    {
        var table=new DataTable();
        table.Load(reader);
        ...
    }
}

ExcelDataReader has an extension that allows reading all sheets in a workbook into a DataSet, with one DataTable for each sheet.

using (var stream = File.Open(filePath, FileMode.Open, FileAccess.Read))
{
    using (var reader = ExcelReaderFactory.CreateReader(stream))
    {
        var dataset= reader.AsDataSet();

        // The result of each spreadsheet is in dataset.Tables
    }
}

Mr.Itanutrof · Answer 2 · 2022-02-17T09:13:53.330

This is how I read from an Excel file:

public static DataSet Import(string path) 
{
    var dataStructure = new DataSet();

    // Create the connection string and connect to the excel table by OleDbConnection. 
    string connectionString = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={path};Extended Properties=\"Excel 12.0;IMEX=1;HDR=NO;TypeGuessRows=0;ImportMixedTypes=Text\"";
    using (var conn = new OleDbConnection(connectionString))
    {
        try
        {
            conn.Open();
        }
        catch (Exception e)
        {
            MessageBox.Show($"Cannot connect to the OLEDB (Excel) driver with the connection string \"{connectionString}\".\n{e}");

            return null;
        }

        DataTable sheets = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
        using (OleDbCommand cmd = conn.CreateCommand())
        {
            foreach (DataRow row in sheets.Rows)
            {
                var tableName = row["TABLE_NAME"].ToString();
                string sql = $"SELECT * FROM [{tableName}]";
                var oleDbDataAdapter = new OleDbDataAdapter(sql, conn);
                oleDbDataAdapter.Fill(dataStructure, tableName);
            }
        }

        conn.Close();
    }

    return dataStructure;
}

I would highly recommend you to use the Open-XML-SDK for reading Excel files instead: Open-XML-SDK nuget This will make life a bit easier. e.g.:

SpreadsheetDocument.Open(fileName, isEditable);

I edited my code snippet. The connection string does contain the file path in addition to other settings. — Mr.Itanutrof, Feb 17 '22 at 09:15

Get Excel data in DataTable

2 Answers2