OleDB is a very useful API in .NET, especially when manipulating the data in an Excel spreadsheet. In fact, it can open an Excel spreadsheet and query the data using regular SQL statements.
using System.Data.OleDb;
OleDbConnection conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=somedataineed.xls;Extended Properties=\"Excel 12.0 Xml;HDR=NO\";");
The connection string is fairly standard. However, notice the 'HDR=No' under extended properties, this tells the connection to assume that the first row does not contain header text. By default, or if 'HDR=Yes', the first row will be disregarded as headers and the first row of data may be skipped, be CAREFUL.
Once the connection is open the spreadsheet can be queried like any SQL database,
OleDbCommand comm = conn.CreateCommand();
comm.CommandText = "SELECT * FROM Sheet1";
OleDbDataReader read = comm.ExecuteReader();
while (read.Read())
{
// Do something here, manipulate the data, get a coffeee, something!
}
read.Close();
conn.Close();
Of course, if the spreadsheet has no headers (in the first row) then you have to reference the fields by ordinal, otherwise you can reference the column names. Thats it, the spreadsheet and its data are now at you mercy!
Keen observers will have noticed that the default Excel sheet name ("'Sheet1") was used as the table name in the query. What happens if you don't know the name of the sheet? (or worse some pesky user didn't like the sheet name and saw fit to change it, how dare they?)
If this happens (and it probably will), take a peek at the workbook layout ("database schema") and find out the sheet names ("table names"). This is useful in a number of instances, for example, importing the data into another application without forcing the user to perform a text export from Excel.