Reading Excel Spreadsheets with C# and ADO.Net

How to read and write to Excel Spreadsheets with C# using ADO.Net and Microsoft Office data connectors for OleDb with examples.

By Tim TrottC# ASP.Net MVC • April 2, 2008
Reading Excel Spreadsheets with C# and ADO.Net

Microsoft .Net provides various methods to interact with Microsoft Office programs through ADO.Net. This tutorial will look at the methods and techniques for reading from and writing to Excel spreadsheets with C# ADO.Net.

Reading Excel Spreadsheets Using ADO.Net and OleDB Provider

We can use the in-built OleDB provider for .Net to access Excel .xls spreadsheets, but they need a little preparation before we do so. You can optionally create a Named Range to identify the table name; we also need column headings, so if the spreadsheet does not contain column headings, you will need to add them.

Creating a Named Range in Excel

  1. With the spreadsheet open, select the data you wish to include in the query, including headings.
  2. Select the Insert Menu, then Name and select Define....
  3. Enter a name for the table and click Add.
  4. Close the dialogue and save the workbook.
Excel OLEDB
Excel OLEDB

Reading Excel Spreadsheets with C# ADO.Net

I will create a simple console application to run through all the customers and display them on the screen. The code is straightforward; instead of SqlConnection, SqlCommand, etc., we use OleDbConnection and OleDbCommand.

C#
using System;
using System.Data.OleDb;

namespace ConsoleApplication1
{
  class Program
  {
    static void Main()
    {
      string connectionString = "Provider=Microsoft.Jet.OleDb.4.0; data source=c:customers.xls; Extended Properties=Excel 8.0;";

      // Select using a Named Range
      string selectString = "SELECT * FROM Customers";

      // Select using a Worksheet name
      string selectString = "SELECT * FROM [Sheet1$]";

      OleDbConnection con = new OleDbConnection(connectionString);
      OleDbCommand cmd = new OleDbCommand(selectString,con);

      try
      {
        con.Open();
        OleDbDataReader theData = cmd.ExecuteReader();
        while (theData.Read())
        {
          Console.WriteLine("{0}: {1} ({2}) - {3} ({4})", theData.GetString(0),theData.GetString(1),theData.GetString(2),theData.GetString(3),theData.GetString(4));
        }
      }
      catch (Exception ex)
      {
        Console.WriteLine(ex.Message);
      }
      finally
      {
        con.Dispose();
      }
    }
  }
}

Updating Excel Spreadsheets and Inserting Data with SQL

You can update records, insert data using regular SQL commands, and execute the command through OleDb.

C#
string connectionString = "Provider=Microsoft.Jet.OleDb.4.0; data source=c:customers.xls; Extended Properties=Excel 8.0;";
string selectString = "INSERT INTO Customers VALUES('12345', 'Acme Inc', 'Acme Way, CA', 'Testy McTest', '01234-987654')";

OleDbConnection con = new OleDbConnection(connectionString);
OleDbCommand cmd = new OleDbCommand(selectString, con);

try
{
  con.Open();
  cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
  Console.WriteLine(ex.Message);
}
finally
{
  con.Dispose();
}

About the Author

Tim Trott is a senior software engineer with over 20 years of experience in designing, building, and maintaining software systems across a range of industries. Passionate about clean code, scalable architecture, and continuous learning, he specialises in creating robust solutions that solve real-world problems. He is currently based in Edinburgh, where he develops innovative software and collaborates with teams around the globe.

Related ArticlesThese articles may also be of interest to you

CommentsShare your thoughts in the comments below

My website and its content are free to use without the clutter of adverts, popups, marketing messages or anything else like that. If you enjoyed reading this article, or it helped you in some way, all I ask in return is you leave a comment below or share this page with your friends. Thank you.

This post has 12 comments. Why not join the discussion!

New comments for this post are currently closed.