Anders G. Nordby

Lead Consultant at Itera

Monthly Archives: January 2014

Reading an Excel File with C#

A customer wants to give me data every so often by sending me an Excel file. So, just for the sake of remembering how to do this, I’m showing my simple Excel reading program here:

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

namespace ExcelReaderConsoleApp
{
    internal class Program
    {
        private static void Main(string[] args)
        {
            const string fileName = "C:\\Test\\TheExcelFile.xls";

            var ds = new DataSet();
            using (var conn = new OleDbConnection(GetExcelConnectionString(fileName)))
            {
                conn.Open();

                foreach (var sheet in GetSheets(conn))
                    FillTableWithData(sheet, conn, ds);
            }

            foreach (DataTable table in ds.Tables)
            {
                Console.WriteLine("TABLE: " + table.TableName);
                foreach (DataRow row in table.Rows)
                    Console.WriteLine("ROW: " + string.Join(", ", row.ItemArray));
            }
            
            Console.ReadLine();
        }

        private static void FillTableWithData(string tableName, OleDbConnection conn, DataSet ds)
        {
            var query = string.Format("SELECT * FROM [{0}]", tableName);
            using (var da = new OleDbDataAdapter(query, conn))
            {
                var dt = new DataTable(tableName);
                da.Fill(dt);
                ds.Tables.Add(dt);
            }
        }

        private static IEnumerable<string> GetSheets(OleDbConnection conn)
        {
            var sheets = new List<string>();
            var dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            if (dtSheet != null)
            {
                foreach (DataRow drSheet in dtSheet.Rows)
                {
                    string sheet = drSheet["TABLE_NAME"].ToString();
                    if (sheet.EndsWith("$") || sheet.StartsWith("'") && sheet.EndsWith("$'"))
                    {
                        sheets.Add(sheet);
                    }
                }
            }

            return sheets;
        }

        private static string GetExcelConnectionString(string fullPathToExcelFile)
        {
            var props = new Dictionary<string, string>();
            props["Provider"] = "Microsoft.Jet.OLEDB.4.0";
            props["Data Source"] = fullPathToExcelFile;
            props["Extended Properties"] = "Excel 8.0";

            var sb = new StringBuilder();
            foreach (var prop in props)
            {
                sb.Append(prop.Key);
                sb.Append('=');
                sb.Append(prop.Value);
                sb.Append(';');
            }

            return sb.ToString();
        }
    }
}

Obviously, one can always do more interesting stuff than simply printing the contents of the Excel file to the console…

Advertisements