Recently I needed to query an excel file and import that data into a database.  I could not immediately recall how to accomplish this, so with a bit of searching I found the connection settings that I needed.  Bellow is a code snippet that can be used to load all of the data in a workbook into a GridView on an ASP.NET page.  Note the use of the "Extended Properties='Excel 8.0;'" in the connection template as well as Sheet1$ in the SQL command to specify the name of the workbook in the worksheet.

using System;

using System.Data;

using System.Data.OleDb;

using System.Web.UI;

 

public partial class _Default : Page

{

    protected void Page_Load(Object sender, EventArgs e)

    {

        GridView1.DataSource = GetProductData();

        GridView1.DataBind();

    }

 

    private DataSet GetProductData()

    {

        String connectionTemplate = @"Provider=Microsoft.Jet.OLEDB.4.0;" +

            "Data Source={0}; Extended Properties='Excel 8.0;'";

 

        using (OleDbConnection connection = new OleDbConnection(

                String.Format(connectionTemplate,

                              Server.MapPath("~/App_Data/Products.xls"))

              ))

        {

            OleDbCommand command = new OleDbCommand(

                "Select * FROM [Sheet1$]", connection

            );

 

            OleDbDataAdapter adapter = new OleDbDataAdapter(command);

 

            DataSet dataSet = new DataSet();

 

            adapter.Fill(dataSet);

 

            return dataSet;

        }

    }

}