C#: Import excel file as dataset using OLEDB

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

namespace cltStammdatenImporterGUI
{
    public class ExcelFile
    {

        public static DataSet DataSetFromExcelFile(string sExcelFilename, string sExcelRange = "")
        {
            DataSet ds = new DataSet();

            string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sExcelFilename + ";Extended Properties='Excel 12.0 XML;HDR=NO;IMEX=1;';";

            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. sExcelRange must be in the form A1:C10 or sim or just empty
                    cmd.CommandText = "SELECT * FROM [" + sheetName + sExcelRange + "]";

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

[C#] How to fix the "Microsoft.ACE.OLEDB.12.0 provider is not registered on the local machine" error

If you are trying to read an excel file in C# and you get this error:

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

You need to install "Microsoft Access Database Engine 2010 Redistributable" that is available at the Microsoft's website:


https://www.microsoft.com/en-in/download/details.aspx?id=13255