Open Microsoft Excel spreadsheet with ADO.NET

If your spreadsheet is set up to look like a table, you can easily connect and work with it using SQL. Here's a quick sample to get you started.

If your spreadsheet is set up to look like a table, you can easily connect and work with it using SQL. Here's a quick sample to get you started.


If your spreadsheet is set up to look like a table, you can easily connect and
work with it using SQL. Here's a quick sample to get you started.

If you would like a more elaborate example of how to use ADO.NET with Excel including updating data, check out this article:

http://www.eggheadcafe.com/articles/20021221.asp


strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\TEMP\TEST.XLS;Extended Properties=Excel 8.0;";

string sList="myspreadsheetname";


OleDbConnection oConn = new OleDbConnection();

oConn.ConnectionString = strConn;
oConn.Open();

OleDbDataAdapter oCmd = new OleDbDataAdapter("SELECT * FROM [" + sList + "$]", oConn);
DataSet oDS = new DataSet();
oCmd.Fill(oDS);

foreach(DataRow oRow in oDS.Tables[0].Rows)
{
Response.Write("Row: " + oRow["COLUMNNAME"].ToString() + "<br>");
}


if (oConn.State == ConnectionState.Open) { oConn.Close(); }