C# and VB Project: A Tutorial Using Data Sets, Table Adapters, WebForms, Controls, File Upload, Excel Import
Section 5: Bind the Excel Data to a GridView using an OleDbDataAdapter
by Nannette Thacker

Retrieving and Viewing the Excel Spreadsheet Data

Our "View Excel Data" button will allow the user to Retrieve and View the Excel Spreadsheet Data within a GridView. We will learn how to create a connection to our Excel Spreadsheet using an OleDbConnection with a Microsoft.Jet.OLEDB Connection String. We will learn how to create a SELECT query to retrieve data from a given Excel worksheet using an OleDbCommand. We will populate and bind our data to our GridView using an OleDbDataAdapter and DataSet.

Our code behind has these imports, however, please see the ZIP project for details of putting it all together.

Imports System.Data
Imports System.Data.OleDb
Imports System.Data.SqlClient

View Excel Data Functionality

Before we can actually see the data, we must wire up the ButtonView_Click Sub procedure which handles our "ButtonView.Click" event. Our ButtonView_Click procedure will need a helper function to connect to the Excel spreadsheet and create a select query, so let's create our "ExcelConnection" function first.

In the code snippet below, notice our ExcelConnection function returns an OleDbCommand. We first create our connection string to a "Microsoft.Jet.OLEDB.4.0" Provider. Our Data Source is the Excel Spreadsheet that we already uploaded. We then create and open our Excel connection object using the connection string. Notice our path to our Data Source uses the Server.MapPath method to map the path to the corresponding physical directory on the server. See the Server.MapPath method on MSDN for further details.

VB:

Protected Function ExcelConnection() As OleDbCommand

        ' Connect to the Excel Spreadsheet
        Dim xConnStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                    "Data Source=" & Server.MapPath("ExcelImport.xls") & ";" & _
                    "Extended Properties=Excel 8.0;"

        ' create your excel connection object using the connection string
        Dim objXConn As New OleDbConnection(xConnStr)
        objXConn.Open()

        ' use a SQL Select command to retrieve the data from the Excel Spreadsheet
        ' the "table name" is the name of the worksheet within the spreadsheet
        ' in this case, the worksheet name is "Members" and is coded as: [Members$]
        Dim objCommand As New OleDbCommand("SELECT * FROM [Members$]", objXConn)
        Return objCommand

    End Function
C#:

protected OleDbCommand ExcelConnection()
    {

        // Connect to the Excel Spreadsheet
        string xConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + 
        "Data Source=" + Server.MapPath("ExcelImport.xls") + ";" + 
        "Extended Properties=Excel 8.0;";

        // create your excel connection object using the connection string
        OleDbConnection objXConn = new OleDbConnection(xConnStr);
        objXConn.Open();

        // use a SQL Select command to retrieve the data from the Excel Spreadsheet
        // the "table name" is the name of the worksheet within the spreadsheet
        // in this case, the worksheet name is "Members" and is coded as: [Members$]
        OleDbCommand objCommand = new OleDbCommand("SELECT * FROM [Members$]", objXConn);
        return objCommand;

    }
In this section of our function, we now want to create our SQL select command which will retrieve the data from the Excel Spreadsheet. Within our Excel spreadsheet, each worksheet is given a name. It is this name which is our "table name." In our example, our worksheet and table name are "Members" and is coded as "[Members$]." Our function will now return our "objCommand" OleDbCommand.

We are now ready to use this function within our "ButtonView_Click" Sub procedure.

Notice in our code below, we set the visibility of the PanelUpload to false so that we no longer see our Upload form. We set our PanelView visibility to true so that we may see our GridView that we just created. 

Protected Sub ButtonView_Click(ByVal sender As Object, _
        ByVal e As System.EventArgs) _
        Handles ButtonView.Click
        PanelUpload.Visible = False
        PanelView.Visible = True
        PanelImport.Visible = False
We are going to create a new OleDbDataAdapter to retrieve the spreadsheet data. We then use the adapter to retrieve our "SelectCommand" from our "ExcelConnection" function that we just wrote. 

        ' Create a new Adapter
        Dim objDataAdapter As New OleDbDataAdapter()

        ' retrieve the Select command for the Spreadsheet
        objDataAdapter.SelectCommand = ExcelConnection()
Next we will create a DataSet and populate it with the spreadsheet data using the "Fill" method. 

        ' Create a DataSet
        Dim objDataSet As New DataSet()
        ' Populate the DataSet with the spreadsheet worksheet data
        objDataAdapter.Fill(objDataSet)
Now we set our GridView datasource and bind the data to our GridView. 

        ' Bind the data to the GridView
        GridViewExcel.DataSource = objDataSet.Tables(0).DefaultView
        GridViewExcel.DataBind()

    End Sub
C#:

protected void ButtonView_Click(object sender, System.EventArgs e)
    {
        PanelUpload.Visible = false;
        PanelView.Visible = true;
        PanelImport.Visible = false;

        // Create a new Adapter
        OleDbDataAdapter objDataAdapter = new OleDbDataAdapter();

        // retrieve the Select command for the Spreadsheet
        objDataAdapter.SelectCommand = ExcelConnection();

        // Create a DataSet
        DataSet objDataSet = new DataSet();
        // Populate the DataSet with the spreadsheet worksheet data
        objDataAdapter.Fill(objDataSet);

        // Bind the data to the GridView
        GridViewExcel.DataSource = objDataSet.Tables[0].DefaultView;
        GridViewExcel.DataBind();
    }
Now when the user selects the "View Excel Data" button, it will retrieve the data from the uploaded spreadsheet and display it within the gridview. My example is too big for the screen, but you may alter your gridview to get it to fit beautifully on the screen.



Next, we'll continue with our "Import Excel Data" button functionality, so let's get started with creating our Data Access Layer: Section 6: Data Access Layer DataSet TableAdapters .

May your dreams be in ASP.NET!

Nannette Thacker

C# and VB Project: Importing an Excel Spreadsheet to a Database Using Data Sets and Table Adapters:

Introduction: C# and VB Project: A Tutorial Using Data Sets, Table Adapters, WebForms, Controls, File Upload, Excel Import
Section 1: Creating Our Project, Database and Tables
Section 2: WebForm: Table, Label, and Panel Controls
Section 3: FileUpload Control and Functionality
Section 4: Auto Formatting a Web Form GridView
Section 5: Bind the Excel Data to a GridView using an OleDbDataAdapter
Section 6: Data Access Layer DataSet TableAdapters
Section 7: TableAdapter Select and Insert Queries with Parameters
Section 8: Using an OleDbDataReader to Retrieve Our Data
Section 9: Using our TableAdapters, DataTables and Intellisense

Download the ZIP files:

C#: ShiningStarCExcel.zip
VB: ShiningStarVBExcel.zip

About the Author

Nannette Thacker is an ASP.NET web application developer and SQL Server developer. She is owner of the ASP.NET consulting firm, Shining Star Services, LLC in Kansas City. Nannette specializes in ASP Classic to ASP.NET conversions and custom Membership Provider solutions as well as existing or new ASP.NET development. Nannette's many articles on ASP.NET, ASP Classic, Javascript and more may be read at http://www.shiningstar.net. You may also view her http://weblogs.asp.net/nannettethacker/ web blog.