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?

Answer 1

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);