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?

CodePudding user response：

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

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.

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
    }
}

CodePudding user response：

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