Reading an Excel Sheet into a DataTable, generic method

Introduction

Reading from Excel is easy, but I just spent four hours banging my head into "why doesn't it work" only to find I wasn't referring to the file: I'd forgotten the ".xlsx" on the end, and all you get for that is an error report about installable ISAMs...not helpful.

Using the code

Easy: Call the method(s)!

            string path = @"C:\Users\griff\Documents\My Spreadsheets\My SpreadSheet.xlsx";

Then:

            myDataGridView.DataSource = LoadExcel(path);

Or:

            myDataGridView.DataSource = GetExcelSheetNames(path).Select(s => new { SheetName = s }).ToArray(); 

I'll just explain the second one... 

If you return a List of strings, and set that as the DataSource of a control, you will not see the actual strings: you will see how long the strings are instead! This is because the String class has only one property: Length, so the control shows just that. If String had a Text property, that would also be shown, but...

So what we do is use a Linq method to create a new anonymous class that has just one property: SheetName which is the string. This "fools" the control into showing what we wanted in the first place! Unfortunately, the Linq method returns an IEnumerable, which doesn't work directly as a DataSource either, so we need the ToArray (or ToList would work as well) to get round that as well... 

The code 

        /// <summary>
        /// Load a sheet from an Excel file
        /// </summary>
        /// <param name="path">Path to excel file</param>
        /// <param name="columns">Comma separated list of columns to load. If not specified, loads all</param>
        /// <param name="sheet">Sheet name to load. If not specified, loads "Sheet1$"</param>
        /// <param name="verify">If true, checks that the file and sheet exist, and throws exceptions if not. Defaults to true</param>
        /// <returns>Table of data retrieved.</returns>
        public static System.Data.DataTable LoadExcel(string path, string columns = "*", string sheet = "Sheet1$", bool verify=true)
            {
            if (verify)
                {
                if (!File.Exists(path)) throw new IOException("The input file does not exist: " + path);
                if (!GetExcelSheetNames(path).Contains(sheet)) throw new ArgumentException("The requested sheet does not exist: " + sheet);
                }
            if (string.IsNullOrWhiteSpace(columns)) columns = "*";

            System.Data.DataTable dt = new System.Data.DataTable();
            using (OleDbConnection con = new OleDbConnection(GetExcelConnectionString(path)))
                {
                con.Open();
                using (OleDbCommand cmd = new OleDbCommand(string.Format("SELECT {0} FROM [{1}]", columns, sheet), con))
                    {
                    using (OleDbDataAdapter da = new OleDbDataAdapter(cmd))
                        {
                        da.Fill(dt);
                        }
                    }
                }
            return dt;
            }
        /// <summary>
        /// Returns a list of all sheet names in an Excel File.
        /// </summary>
        /// <param name="path"></param>
        /// <returns></returns>
        public static List<string> GetExcelSheetNames(string path)
            {
            List<string> sheetNames = new List<string>();
            using (OleDbConnection con = new OleDbConnection(GetExcelConnectionString(path)))
                {
                con.Open();
                using (DataTable sheets = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null))
                    {
                    foreach (DataRow sheet in sheets.Rows)
                        {
                        if (sheet["TABLE_NAME"].ToString().Contains("$"))
                            {
                            // Filtered to just sheets - they all end in '$'
                            sheetNames.Add(sheet["TABLE_NAME"].ToString());
                            }
                        }
                    }
                }
            return sheetNames;
            }
        /// <summary>
        /// Returns a connection string for an Excel file
        /// </summary>
        /// <param name="path"></param>
        /// <returns></returns>
        private static string GetExcelConnectionString(string path)
            {
            return string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}; Extended Properties=Excel 12.0", path);
            }

History

2014-02-08 Original Version