Anders G. Nordby

Lead Consultant at Itera

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

4 responses to “Reading an Excel File with C#

  1. jessie 2014-03-05 at 08:42

    Thanks alot for sharing this code, its very simple and easy to understand. I have found code for opening and reading excel file in C#/.NET/VB.NET on Aspose.Cells for .NET library documentation page which was also very helpful and they have also almost followed the same approach as you did.

  2. charles 2014-05-27 at 08:16

    A complete tutorial on c# excel automation C# excel tutorial

    charles

  3. Jagdish Rawat 2015-02-05 at 10:59

    Hi,
    I wish to open excel file which is having columns for File name and File Path. Using both column’s info Can I open the file mention in Excel.
    Please help.
    BR,Jagdish

  4. Anders G. Nordby 2015-02-06 at 11:23

    @Jagdish: I don’t understand your question.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: