Click here to Skip to main content
11,482,103 members (66,259 online)
Click here to Skip to main content

Tagged as

Reading an Excel Sheet into a DataTable, generic method

, 8 Feb 2014 CPOL 6.4K 7
Rate this:
Please Sign up or sign in to vote.
Reading a sheet into a database isn't difficult - it's just I keep forgetting how... :sigh: So I abstracted this from my working code so I wouldn't lose it again.

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

OriginalGriff
CEO
Wales Wales
Born at an early age, he grew older. At the same time, his hair grew longer, and was tied up behind his head.
Has problems spelling the word "the".
Invented the portable cat-flap.
Currently, has not died yet. Or has he?
Follow on   Google+

Comments and Discussions

 
GeneralThoughts Pin
PIEBALDconsult13-Jun-14 10:15
memberPIEBALDconsult13-Jun-14 10:15 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.150520.1 | Last Updated 8 Feb 2014
Article Copyright 2014 by OriginalGriff
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid