Archive

Posts Tagged ‘database’

Reading and Writing Excel Spreadsheets Using ADO.NET C# DbProviderFactory

March 12, 2010 Leave a comment

Original post by: David Hayden

It has been an absolute exhausting work week. My eyes, wrists, and back are sore from too many hours of coding. Thankfully I get to relax much of the Memorial Day Weekend 🙂 I hope you all enjoy the weekend, too.

This week a client sent me an Excel Spreadsheet that needed to populate several tables in a SQL Server Database. To know me knows I hate data entry of any kind and there was no chance I was entering the Excel data in manually.

Thankfully, we don’t have to. You can use the OleDb Managed Data Provider in the .NET Framework to read an Excel Spreadsheet using ADO.NET and C# just like you would with a database.

Shown below is a simple spreadsheet that lists a few cities ( Bradenton, Sarasota, and Tampa ) in Florida. Notice I have renamed the worksheet to Cities as opposed to the default of Sheet1. Also notice that the first row contains headers of the columns. These changes will impact the way we access the information as you will see in a moment.

Excel example

Excel Connection String for ADO.NET

You will first need a connection string to connect to the Excel Workbook, which would be the following:

string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;
    Data Source=Book1.xls;Extended Properties=
    ""Excel 8.0;HDR=YES;""";

This says the spreadsheet is located in the current directory and called Book1.xls, and the first row is a header row containing the names of the columns.

Read Excel Spreadsheet using ADO.NET and DbDataReader

Once you have the connection string all normal ADO.NET coding applies. Here is some sample code that reads each row of the excel worksheet using DbDataReader. You don’t have to use the DbProviderFactory Classes. I thought I would show it just for kicks.

string connectionString = @"Provider=Microsoft.Jet.
    OLEDB.4.0;Data Source=Book1.xls;Extended
    Properties=""Excel 8.0;HDR=YES;""";

DbProviderFactory factory =
  DbProviderFactories.GetFactory("System.Data.OleDb");

using (DbConnection connection = factory.CreateConnection())
{
    connection.ConnectionString = connectionString;

    using (DbCommand command = connection.CreateCommand())
    {
        // Cities$ comes from the name of the worksheet
        command.CommandText = "SELECT ID,City,State
                                      FROM [Cities$]";

        connection.Open();

        using (DbDataReader dr = command.ExecuteReader())
        {
            while (dr.Read())
            {
                Debug.WriteLine(dr["ID"].ToString());
            }
        }
    }
}

Read Excel Spreadsheet using ADO.NET and DataSet

Here is another example of reading an Excel spreadsheet using ADO.NET and a DataSet.

string connectionString = @"Provider=Microsoft.Jet.
    OLEDB.4.0;Data Source=Book1.xls;Extended
    Properties=""Excel 8.0;HDR=YES;""";

DbProviderFactory factory =
   DbProviderFactories.GetFactory("System.Data.OleDb");

DbDataAdapter adapter = factory.CreateDataAdapter();

DbCommand selectCommand = factory.CreateCommand();
selectCommand.CommandText = "SELECT ID,City,State
                                     FROM [Cities$]";

DbConnection connection = factory.CreateConnection();
connection.ConnectionString = connectionString;

selectCommand.Connection = connection;

adapter.SelectCommand = selectCommand;

DataSet cities = new DataSet();

adapter.Fill(cities);

gridEX1.SetDataBinding(cities.Tables[0], "");
gridEX1.RetrieveStructure();

I was binding to the Janus GridEx Control, which is why you see gridEX1 above. You could easily replace those 2 lines with

dataGridView1.DataSource = cities.Tables[0].DefaultView;

Inserting a Row into Excel Using ADO.NET

Here I will add a 4th city, Tampa, to the list of cities in Florida. This inserts it right into the Excel Worksheet as you would expect.

string connectionString = @"Provider=Microsoft.Jet.
   OLEDB.4.0;Data Source=Book1.xls;Extended
   Properties=""Excel 8.0;HDR=YES;""";

DbProviderFactory factory =
   DbProviderFactories.GetFactory("System.Data.OleDb");

using (DbConnection connection = factory.CreateConnection())
{
    connection.ConnectionString = connectionString;

    using (DbCommand command = connection.CreateCommand())
    {
        command.CommandText = "INSERT INTO [Cities$]
         (ID, City, State) VALUES(4,\"Tampa\",\"Florida\")";

        connection.Open();

        command.ExecuteNonQuery();
    }
}

Updating Excel Using ADO.NET

Let’s modify the name of the first city from Bradenton to Venice in the Excel Spreadsheet using ADO.NET:

string connectionString = @"Provider=Microsoft.Jet.
   OLEDB.4.0;Data Source=Book1.xls;Extended
   Properties=""Excel 8.0;HDR=YES;""";

DbProviderFactory factory =
   DbProviderFactories.GetFactory("System.Data.OleDb");

using (DbConnection connection = factory.CreateConnection())
{
    connection.ConnectionString = connectionString;

    using (DbCommand command = connection.CreateCommand())
    {
        command.CommandText = "Update [Cities$] Set City =
                                \"Venice\" WHERE ID = 1";

        connection.Open();

        command.ExecuteNonQuery();
    }
}

Conclusion

It is just too cool that we can use ADO.NET and the OleDb Managed Data Provider in the .NET Framework to insert, update, and delete information in an Excel Spreadsheet like it was a database.

Advertisements
Categories: C sharp, MS Excel Tags: , , , , , , ,