Using ADO.Net and OleDB Provider

We can use the inbuilt OleDB provider for .Net to access Excel .xls spreadsheets, but before we do so, they need a little preparation. 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 then you will need to add them.

Creating a Named Range

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 dialog and save the workbook.

Accessing the Data

I am just going to create a simple console application that will run through all the customers and display them on screen. The code itself is pretty straight forward, instead of SqlConnection, SqlCommand etc… we are using OleDbConnection and OleDbCommand.

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 Spreadsheets and Inserting Data

You can update records and insert data using regular SQL
commands and executing the command through OleDb.
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();