Reading cells from Excel file using OleDbConnection

For a little project I was working on a few weeks ago, I had to be able to read the content from a single cell in an Excel file. This is properly easiest done by using the Microsoft Excel Object Library, however for my project it was a requirement that the oledb api was used instead. 

In this post I have added a small example of how the connection to the Excel file is established, and how a single cell is read from a certain spreadsheet.

The following code snippet shows how to establish a connection to a Microsoft Excel 97-2003 file (.xls):

OleDbConnection connection = new OleDbConnection"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename +";Extended Properties=\"Excel 8.0;HDR=No;IMEX=1\"");

As it turns out, this only works for .xls files and not for the newer .xlsx format – so here is the code for establishing a connection to a .xlsx file:

OleDbConnection connection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";Extended Properties=\"Excel 12.0;HDR=No;IMEX=1\"");

Now that the connection to the excel file has been established, the following code snippet demonstrates how to read a single cell from a defined spreadsheet:

connection.Open();
OleDbCommand command = new OleDbCommand("SELECT * FROM [" + sheet + column + row +":"+ column + row +"]",connection);
DataSet dataSet = new DataSet();
OleDbDataAdapter adapter = new OleDbDataAdapter(command);
adapter.Fill(dataSet);

string value = dataSet.Tables[0].Select()[0][0].ToString();
connection.Close();

As it can be seen from the above code snippet, the sql syntax command selects from a defined spreadsheet, and a certain column and row (actually a range – though in this example only a single element is selected). I have chosen to add the source code for a small WPF based example so you can have a closer look for yourself.

Visual Studio Project – ExcelReader

Best Regards
/Peter

See larger image Microsoft Office Home & Student 2010 - 3PC/1User (Disc Version) (DVD-ROM) List Price: $149.99 USD New From: $124.99 In Stock Used from: $112.49 In Stock Release date June 15, 2010.