C# WebApplication - System.Data.OleDb found but nearly empty

Question

I am currently trying to read from an Excel file with OleDB.

This is the code:

using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Text;

namespace WebApplication1.My_Logic.Worker
{
    public class ExcelLoader
    {
        private string GetConnectionString()
        {
            Dictionary<string, string> props = new Dictionary<string, string>();

            props["Provider"] = "Microsoft.ACE.OLEDB.12.0;";
            props["Extended Properties"] = "Excel 12.0 XML";
            props["Data Source"] = "C:\\MyExcel.xlsx";

            StringBuilder sb = new StringBuilder();

            foreach (KeyValuePair<string, string> prop in props)
            {
                sb.Append(prop.Key);
                sb.Append('=');
                sb.Append(prop.Value);
                sb.Append(';');
            }

            return sb.ToString();
        }

        private DataSet ReadExcelFile()
        {
            DataSet ds = new DataSet();
            string connectionString = GetConnectionString();
            using (OleDbConnection conn = new OleDbConnection(connectionString))
            {
                conn.Open();
                OleDbCommand cmd = new OleDbCommand();
                cmd.Connection = conn;

                // Get all Sheets in Excel File
                DataTable dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                // Loop through all Sheets to get data
                foreach (DataRow dr in dtSheet.Rows)
                {
                    string sheetName = dr["TABLE_NAME"].ToString();

                    if (!sheetName.EndsWith("$"))
                        continue;

                    // Get all rows from the Sheet
                    cmd.CommandText = "SELECT * FROM [" + sheetName + "]";

                    DataTable dt = new DataTable();
                    dt.TableName = sheetName;

                    OleDbDataAdapter da = new OleDbDataAdapter(cmd);
                    da.Fill(dt);

                    ds.Tables.Add(dt);
                }

                cmd = null;
                conn.Close();
            }

            return ds;
        }
    }
}

System.Data.OleDb is greyed out (not used) and following classes cannot be found:

OleDbCommand
OleDbConnection
OleDbSchemaGuid
OleDbDataAdapter

When I look into the System.Data.OleDb there are only two classes shown by Visual Studio:

System.Data.OleDb.OleDbPermission
System.Data.OleDb.OleDbPermissionAttribute

Everywhere I read on the Internet the only answer was to add using System.Data.OleDb but I do have it and Visual Studio seems to know it.

Its a freshly generated ASP.NET Core 2.1 API Project from Visual Studio 2017.

Answer 1

OleDb is not available in .NET Core since it's not implemented for other platforms rather than windows.

See the following github thread.

If you want to read from xlsx just use OpenXmlSdk which is cross platform and supported by .NET core.

See how to read excel file using OpenXmlSdk in the following thread.