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

The JET/Access driver has a number of issues, particularly in the.NET Core era, despite the fact that it can read Excel files like databases:

1. It's Windows-only
2. It needs to be installed. It can't be packaged with your application
3. 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.

Excel files can be read directly by libraries. One such choice is ExcelDataReader, which overlays an Excel page with a DbDataReader. It supports both the dated xls format and the sixteen-year-old xlsx format (yes, the "new" xlsx format was introduced 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);
        ...
    }
}