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 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(); }

Submission Date: 9/23/2005 2:47:53 PM

Submitted By: Robbe Morris

Email:

My Home Page: http://www.robbemorris.com

My Biography

Robbe has been a Microsoft MVP for C# since 2004. He is also the co-founder of EggHeadCafe. Robbe has extensive experience with web technologies, .NET, C#, CTI based applications, system administration, .NET Compact Framework, and data modeling. In his spare time, he blogs from time to time at http://robbemorris.blogspot.com