Excel to DataSet using OLEDB working differently between .NET Core and .NET Framework

Question

I have a worker service that references a .NET Framework class library. The class library contains a method to convert Excel to a dataset. While debugging on Visual Studio, everything works as expected, but after publishing I get the error message:

The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.

To debug, I built the below two console apps with exactly the same code—one in .NET Core, the other .NET Framework. I then run the projects on two computers.

Computer A: Microsoft Office Tools installed, but no AccessDatabaseEngine.exe
Computer B: No Microsoft Office Tools, AccessDatabaseEngine installed

using System;
using System.Data;
using System.Data.OleDb;
using System.Text;
    
namespace ToDataSheetCore
{
    class Program
    {
        static void Main(string[] args)
        {
            DataSet dt2 = ExcelToDataSetCommon("C:\pathToExcel\excelFile - AUGUST 04 2021.xlsx");
            StringBuilder sb2 = new StringBuilder();
            foreach (DataTable table in dt2.Tables)
            {
                foreach (DataRow row in table.Rows)
                {
                    sb2.Append(string.Join(" ", row.ItemArray));
                    sb2.AppendLine();
                }
            }
            Console.WriteLine(sb2);
            Console.ReadLine();
        }

        public static DataSet ExcelToDataSetCommon(string SourceFilename)
        {
            DataSet ds = new DataSet();
            try
            {
                string connStr = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0;HDR=YES;\"", SourceFilename);

                OleDbConnection conn = new OleDbConnection(connStr);
                conn.Open();
                DataTable schemaDT = conn.GetSchema("Tables", new string[] { null, null, null, "TABLE" });
                conn.Close();

                string tableName = schemaDT.Rows[0]["TABLE_NAME"].ToString();

                OleDbCommand cmd = new OleDbCommand(string.Format("SELECT * FROM [{0}]", tableName), conn);

                OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
                adapter.Fill(ds);

            }
            catch (Exception ex)
            {
                Console.Error.WriteLine(ex.Message);
            }
            return ds;
        }
    }
}

Result

Computer A
- .NET Core
  1. Debug mode
    Prints excel content to console
  2. Publish mode - self-contained(win64, anypc)
    
    The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.
- .NET Framework
  1. Debug mode
    
    The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.
  2. Publish mode - self-contained
    
    The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.
Computer B
- .NET Core
  1. Debug mode
    
    The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.
  2. Publish mode - self-contained(win64, anypc)
    
    The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.
- .NET Framework
  1. Debug mode
    Prints excel content to console (Fails on uninstalling AccessDatabaseEngine)
  2. Publish mode - self-contained
    Prints excel content to console (Fails on uninstalling AccessDatabaseEngine)

I understand installing AccessDatabaseEngine provider has an effect, but I don't understand the different results from .NET Framework and .NET Core. Though I have resorted to using ExcelDataReader since I want to avoid external dependencies.

I am confused by the difference in the results using OleDB API between .NET Core and .NET Framework. Is there a way the AccessDatabaseEngine provider can be published together with a .NET project?

Excel to DataSet using OLEDB working differently between .NET Core and .NET Framework

0 Answers