Fully Scalable Excel File Importer class for .net using Microsoft Jet driver

Instead of using third-party drivers and losing control on file data imports from excel, we can write our own excel file importer class using JET OLDEDB drivers that are available from Microsoft.

How to use:
ExcelFileReader reader = new ExcelFileReader(Path.Combine(importDirectory, importFilename), true);
 
Then just say, reader.GetExcelAsDataSet() to get the dataset out. 

I have added necessary comments, otherwise
 
using System;
using System.Collections;
using System.Data;
using System.Data.OleDb;
using System.IO;
using Microsoft.Win32;


namespace Import.ImportCommon
{
    /// <summary>
    /// Summary description for ExcelFileReader.
    /// </summary>
    public class ExcelFileReader
    {

        private string _fileName;
        // NOTE: May need to support Jet 3.5 for Win2000 and NT.
        private static string EXCEL_DRIVER = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";
        private string _excelConnectionString = ";Extended Properties=\"Excel 8.0;HDR={0};IMEX=1\"";
        private bool _firstRowIsHeaders = false;

        /// <summary>
        /// Initializes a new instance of the <see cref="ExcelFileReader"/> class.
        /// </summary>
        /// <param name="fileName">Name of the file.</param>
        /// <param name="firstRowIsHeaders">if set to <c>true</c> [first row is headers].</param>
        public ExcelFileReader(string fileName, bool firstRowIsHeaders)
        {
            EnableMixedDataSuppport();
            FirstRowIsHeaders = firstRowIsHeaders;
            FileName = fileName;
            if (!File.Exists(fileName))
            {
                throw new ApplicationException(string.Concat("Cannot locate Excel file: ", fileName));
            }
            if (!fileName.ToLower().EndsWith(@".xls"))
            {
                throw new ApplicationException(string.Concat("Excel file must end in .xls: ", fileName));
            }

        }

        /// <summary>
        /// Enables the mixed data suppport.
        /// </summary>
        private void EnableMixedDataSuppport()
        {
            //The Jet driver's default behavior is to truncate Excel cell's content 
            //according to a setting in the registry. This resets that entry to allow
            //mixed data in a column by default.
            RegistryKey regKey = null;
            try
            {
                regKey = Registry.LocalMachine.OpenSubKey(@"Software\Microsoft\Jet\4.0\Engines\Excel", true);
                if (regKey != null)
                {
                    int guess = int.Parse(regKey.GetValue("TypeGuessRows", 0).ToString());
                    if (guess != 0)
                        regKey.SetValue("TypeGuessRows", 0);
                    // make mixed types text
                    string mix = regKey.GetValue("ImportMixedTypes", "Text").ToString();
                    if (!mix.Equals("Text"))
                        regKey.SetValue("ImportMixedTypes", "Text");
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Trace.WriteLine("Unable to set Jet registry settings for Excel import: " + ex.Message);
            }
            finally
            {
                if (regKey != null)
                {
                    regKey.Close();
                }
            }
        }

        /// <summary>
        /// Gets the excel sheet as data set.
        /// </summary>
        /// <returns></returns>
        public DataSet GetExcelSheetAsDataSet()
        {
            return GetExcelSheetAsDataSet(true, string.Empty);
        }

        /// <summary>
        /// Gets the excel sheet as data set.
        /// </summary>
        /// <param name="sheetToRead">The sheet to read.</param>
        /// <returns></returns>
        public DataSet GetExcelSheetAsDataSet(string sheetToRead)
        {
            return GetExcelSheetAsDataSet(false, sheetToRead);
        }

        /// <summary>
        /// Gets the excel sheet as data set.
        /// </summary>
        /// <param name="readFirstSheetOnly">if set to <c>true</c> [read first sheet only].</param>
        /// <param name="sheetToRead">The sheet to read.</param>
        /// <returns></returns>
        public DataSet GetExcelSheetAsDataSet(bool readFirstSheetOnly, string sheetToRead)
        {
            string select = string.Empty;
            ArrayList sheetNames = GetExcelSheetNames();
            if (readFirstSheetOnly)
            {
                select = string.Concat("select * from [", sheetNames[0], "]");
            }
            else if (!sheetToRead.Equals(string.Empty))
            {
                select = string.Concat("select * from [", sheetToRead, "]");
            }
            OleDbConnection con = null;
            DataSet dataSet = null;
            try
            {
                con = new OleDbConnection(GetConnectionString());
                OleDbDataAdapter da = new OleDbDataAdapter(select, con);
                dataSet = new DataSet("ExcelTables");
                DataTable dt = new DataTable();
                da.Fill(dt);
                dataSet.Tables.Add(dt);
                dataSet.AcceptChanges();
            }
            finally
            {
                if (con != null)
                {
                    con.Close();
                }
            }
            return dataSet;
        }

        /// <summary>
        /// Gets the excel as data set.
        /// </summary>
        /// <returns></returns>
        public DataSet GetExcelAsDataSet()
        {
            DataSet dataSet = new DataSet("ExcelTables");
            string select = string.Empty;
            ArrayList sheetNames = GetExcelSheetNames();
            OleDbConnection con = null;
            try
            {
                con = new OleDbConnection(GetConnectionString());
                foreach (object sheetName in sheetNames)
                {
                    select = string.Concat("select * from [", sheetName.ToString(), "]");
                    OleDbDataAdapter adapter = new OleDbDataAdapter(select, con);
                    DataTable table = new DataTable(sheetName.ToString());
                    adapter.Fill(table);
                    dataSet.Tables.Add(table);
                }
                dataSet.AcceptChanges();
            }
            finally
            {
                if (con != null)
                {
                    con.Close();
                }
            }
            return dataSet;
        }

        /// <summary>
        /// Gets the excel sheet names.
        /// </summary>
        /// <returns></returns>
        public ArrayList GetExcelSheetNames()
        {
            OleDbConnection objConn = null;
            DataTable dt = null;

            try
            {
                objConn = new OleDbConnection(GetConnectionString());
                objConn.Open();
                dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

                if (dt == null)
                {
                    return null;
                }

                ArrayList excelSheets = new ArrayList();
                foreach (DataRow row in dt.Rows)
                {
                    excelSheets.Add(row["TABLE_NAME"].ToString());
                }

                return excelSheets;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (objConn != null)
                {
                    objConn.Close();
                    objConn.Dispose();
                }
                if (dt != null)
                {
                    dt.Dispose();
                }
            }
        }

        /// <summary>
        /// Gets or sets the name of the file.
        /// </summary>
        /// <value>The name of the file.</value>
        public string FileName
        {
            get { return _fileName; }
            set { _fileName = value; }
        }

        /// <summary>
        /// Gets the excel connection string.
        /// </summary>
        /// <value>The excel connection string.</value>
        private string ExcelConnectionString
        {
            get
            {
                string flag = FirstRowIsHeaders ? "Yes" : "No";
                return string.Format(_excelConnectionString, flag);
            }
        }

        /// <summary>
        /// Gets or sets a value indicating whether [first row is headers].
        /// </summary>
        /// <value><c>true</c> if [first row is headers]; otherwise, <c>false</c>.</value>
        private bool FirstRowIsHeaders
        {
            get { return _firstRowIsHeaders; }
            set { _firstRowIsHeaders = value; }
        }

        /// <summary>
        /// Gets the connection string.
        /// </summary>
        /// <returns></returns>
        private string GetConnectionString()
        {
            return string.Concat(EXCEL_DRIVER, FileName, ExcelConnectionString);
        }

    }
}