Overview

This article explains how to read Excel documents with C#. After two or three projects dealing with copying and pasting data from sql server to excel, I decided that it would be a good time to write some code to help with the process. Here is the basic code for reading an excel spreadsheet.

Code

// Create the connection string
string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
    location + @";Extended Properties=""Excel 8.0;HDR=YES;""";

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

// Create an adapter
DbDataAdapter adapter = factory.CreateDataAdapter();

// Create a select command to get the dataset
DbCommand selectCommand = factory.CreateCommand();
selectCommand.CommandText = "SELECT * FROM [" + sheetName + "$]";

// Set up the connection and the connection string
DbConnection connection = factory.CreateConnection();
connection.ConnectionString = connectionString;
selectCommand.Connection = connection;

// Set up the command
adapter.SelectCommand = selectCommand;

// Create the dataset
DataSet ds = new DataSet();

// Load the results
adapter.Fill(ds);

return ds;

Happy Coding

Excel, C#