Recently I had to read some data from a Microsoft Excel Workbook using C# and OLEDB. Although I have done this several times before, I still had to Google a few times how to get things done. So I decided to create this post as a quick reference for everybody that wants to query an Excel Workbook using C# and OLEDB.

The connection string

First of all we have to connect to the Excel Workbook. Depending of the version of the Excel Workbook you can use one of the following connection strings:

• Microsoft Excel 2007

  Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myexcelfile.xlsx;Extended Properties="Excel 12.0;HDR=YES;IMEX=1";

• Microsoft Excel 2003

  Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\myexcelfile.xls;Extended Properties="Excel 8.0;HDR=YES;IMEX=1";

An important part from the connection strings listed above is the attribute ‘Extended Properties’. In this attribute you can include the following options:

• “HDR=Yes;” this option indicates that the first row from the selection will contain the columnnames and not data. “HDR=No;” obviously indicates the opposite and will return the first row of the selection as data. In this case the OLEDB driver will create columnnames for each column in the selection (F1 to Fn).
• “IMEX=1;” indicates that the OLEDB driver a column containing values of different types (e.g. numbers, strings and date values) should read these values as text. “IMEX=0;” indicates that the driver should read the values according to the source type of the Excel column.

NOTE: By default the OLEDB driver will only scan the first eight rows to determine if the column contains values of different types when IMEX is set to 1. If the first eight rows don’t contain any intermixed types but lets say only numeric values, the driver will read all values a numbers (and not as text). More information on this topic can be found here ‘http://www.connectionstrings.com/excel‘ and here ‘http://dougbert.com/blog/post/importing-from-excel-imex-and-mixed-data-types.aspx‘.

Querying data from an Excel sheet

Now we know what the connection string looks like, we can start querying the Excel sheet. The code example below shows how to read all data from the first sheet in the Excel sheet:

string connString = "Provider=Microsoft.Jet.OleDb.4.0; data source=c:\\myexcelfile.xls; Extended Properties=\"Excel 8.0;HDR=Yes\";";  
string query = "SELECT * FROM [Sheet1$]";  
using (var connection = new OleDbConnection(connString))  
{  
  using (var da = new OleDbDataAdapter(query, connection))  
  {  
    connection.Open();  
    DataSet ds = new DataSet();  
    da.Fill(ds);  
  }  
}

Sometimes the data you need is located at a specific position within the Excel sheet (for example starting at cell B12 to cell L502). In this case you can extend your query with a specific range, like so:

SELECT * FROM [Sheet1$B12:L502]

It is also possible to query the data defined in Excel as a ‘Named range’ (instructions on how to create a ‘Named range’ can be found here ‘http://www.contextures.com/xlnames01.html‘). To query a ‘Named range’ simple construct your select query like so:

SELECT * FROM MyRange

Querying the sheets in the Excel Workbook

Sometimes you don’t which sheets are available in a Excel Workbook beforehand. In these cases it can be useful to first query the Excel Workbook for the available sheets before you can query the sheet for the data you need. You can do this by make use of the ‘GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null)’ method of the ‘OleDbConnection’ class. Below is an example on how use this method:

private string _connString = "Provider=Microsoft.Jet.OleDb.4.0; data source=c:\\myexcelfile.xls; Extended Properties=\"Excel 8.0;HDR=Yes\";";
 
public string[] GetSheetNames()
{
  string[] workSheetNames;
 
  using (var connection = new OleDbConnection(_connString))   
  {
    connection.Open();
 
    DataTable dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);   
    workSheetNames = new string[dt.Rows.Count];   
    int i = 0;   
    foreach (DataRow row in dt.Rows)   
    {   
      workSheetNames[i] = row["TABLE_NAME"].ToString().Trim(new[] { '$' });   
      i++;   
    }   
  }   
 
  return workSheetNames;
}

Conclusion

In this post I have shown how to construct a connection string which can be used to connect to an Microsoft Excel Workbook using C# and OLEDB. After that I have shown a few examples on how to query the Excel sheets for data. And last I have shown you how to query the Excel Workbook for the available sheets.

I hope you have found this post helpful and enjoyed reading it. If you have any questions don’t hesitate to leave a comment.