Importing an Excel Spreadsheet Using Typed DataSets and TableAdapters: Displaying the Uploaded Excel Spreadsheet

By Nannette Thacker

Importing an Excel Spreadsheet Using Typed DataSets and TableAdapters

This article is one in a series of articles on importing a Microsoft Excel spreadsheet into a database table.

Building the Database - provides an overview of the tutorial series and the first steps, including creating the website project and database tables.

Building the Importer Web Page and Uploading the Excel Spreadsheet - adds the interface for uploading an Excel spreadsheet from the user's computer and saving it on the web server's file system.

Displaying the Uploaded Excel Spreadsheet - shows how to query and display the contents of an Excel spreadsheet in a GridView control.

Creating a Data Access Layer (DAL) - creates the Data Access Layer (DAL) used to communicate with the application database during the import process.

Importing the Excel Spreadsheet - looks at importing the uploaded Excel spreadsheet into the application database tables using the DAL.

Introduction

In Building the Importer Web Page and Uploading the Excel Spreadsheet we created an interface for users to upload an Excel spreadsheet to the web server with the end goal being that a user could import the uploaded spreadsheet's data into the application database. Before importing the data, it would be prudent to display the contents of the uploaded Excel spreadsheet to give the user one last time to look over the data and verify that they want to import it.

The contents of an Excel spreadsheet can be programmatically queried with code and syntax similar to that used when querying a "regular" database, like Microsoft SQL Server. In this article we will see how to bind the contents of the uploaded Excel spreadsheet to a GridView. Read on to learn more!

- continued -

Adding a GridView Control to the Web Form

In the previous installment of this article series we built a web page with three Button Web controls and a FileUpload control, enabling the user to select an Excel spreadsheet from their computer and click the "Upload Excel Spreadsheet" button to upload and save the spreadsheet to the web server's file system.

In addition to the "Upload Excel Spreadsheet" button, we added a "View Excel Data" button. The "View Excel Data" button offers the user a means to preview the contents of the uploaded Excel spreadsheet prior to starting the import process. ASP.NET offers a variety of Web controls for displaying data: the GridView, DetailsView, FormView, ListView, DataList, and Repeater, among others. We will use the GridView control to display the contents of the Excel spreadsheet.

To add a GridView to the page, go to the Data tab in the Toolbox and drag a GridView control onto the page, placing it within the PanelView Panel.

Once the Gridview has been added to your page, view it from the Design view. Hover your mouse over the Gridview; a little right arrow appears on the top right corner of the GridView. Click this arrow and you'll see the GridView's Smart Tag, which displays a number of options. One such option is "Auto Format," which makes it easy to quickly set a number of the GridView's formatting properties.

The Auto Format link is one of many options listed in the GridView's Smart Tag.

Click the "Auto Format" link; this displays the AutoFormat dialog box. Choose a scheme from the list on the left. As you select a scheme, a sample rendition is display on the right. I've selected "Rainy Day" for my example.

Choose a scheme from the AutoFormat dialog box.

Once you apply the scheme, view the GridView's declarative markup to see the added formatting properties.

<asp:GridView ID="GridViewExcel" runat="server" BackColor="White" BorderColor="#999999" BorderStyle="None" BorderWidth="1px" CellPadding="3" GridLines="Vertical"> <FooterStyle BackColor="#CCCCCC" ForeColor="Black" /> <RowStyle BackColor="#EEEEEE" ForeColor="Black" /> <PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" /> <SelectedRowStyle BackColor="#008A8C" Font-Bold="True" ForeColor="White" /> <HeaderStyle BackColor="#000084" Font-Bold="True" ForeColor="White" /> <AlternatingRowStyle BackColor="#DCDCDC" /> </asp:GridView>

You may easily edit any of these colors to create your own custom look and feel.

Adding a Cascading Style Sheet

As we just saw, aesthetic properties may be applied to a Web control through its properties. Style settings may also be applied through Cascading Style Sheets (CSS). CSS allows page developers to define a set of formatting rules that are applied to a particular HTML element, to a class of HTML elements, or to a specific HTML element. Let's use stylesheets to define formatting rules for the <body> element, which will apply to our entire page (because all visible content in the page is contained within the <body> element).

Add a stylesheet file to your project. Right click our Project in the Solution Explorer and click to "Add New Item." Select the "StyleSheet" and option and name it StyleSheet.css. Next, add the following content to the stylesheet file:

body { font-family: Arial, Tahoma, Verdana; font-size: 13px; line-height: 1.5em; }

Finally, we need to add this stylesheet to our Web page. To accomplish this, drag the StyleSheet.css file from the Solution Explorer onto the ASP.NET page's Design view. This will add the following markup to your page's <head> element:

<link href="StyleSheet.css" rel="stylesheet" type="text/css" />

With this stylesheet applied to the page, note that the GridView uses these stylesheet settings. The following figure shows the GridView when viewed before the stylesheet was added to the page.

The GridView's apperance BEFORE the stylesheet has been added.

And here it is after the stylesheet has been added. Note that the font is now Arial and the size is smaller.

The GridView's apperance AFTER the stylesheet has been added.

Retrieving and Viewing the Excel Spreadsheet Data

The "View Excel Data" button allows the user to retrieve and view the contents of the Excel Spreadsheet within a GridView. But before we can actually see the data, we must create an event handler for the Button's Click event. To create this event handler, simply double-click the Button control in the Design view. This will create a method named ButtonView_Click that is wired up to the Button's Click event via the Handles ButtonView.Click syntax.

The ButtonView_Click method needs to connect to the Excel spreadsheet and issue a SELECT query to return its data. Let's create a method (named ExcelConnection) that connects to the spreadsheet and returns a command object that's ready to return the spreadsheet's contents.

The ExcelConnection method starts by connecting to the Excel spreadsheet using an OleDbConnection object. To accomplish this we must use a connection string that indicates that we want to use the OLE-DB provider and that points to the Excel spreadsheet to which we wish to connect. The provider - "Microsoft.Jet.OLEDB.4.0" - is specified in the first part of the connection string. The Excel spreadsheet to query has already been uploaded; it is named ExcelImport.xls and resides in the web application's root directory. To get the physical path to this Excel spreadsheet we use the Server.MapPath See Using Server.MapPath for more details on this useful method.

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

Once we've connected to the Excel spreadsheet we are ready to create a command object to retrieve the data. Within our Excel spreadsheet, each worksheet has a name. It is this name that is synonymous to the "table name" in a "normal" database, and is what appears in the FROM clause of our SQL SELECT query. However, when appearing in the FROM clause the worksheet name must be entered as [WorksheetName$]. For example, in our spreadsheet the worksheet is named Members. Therefore, to return all data from this worksheet we would use the SQL query: SELECT * FROM [Members$].

The remaining code of the ExcelConnection method creates an OleDbCommand object, specifying its SelectCommand and Connection properties via its constructor. This OleDbCommand object is then returned.

' 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 expressed as: [Members$] Dim objCommand As New OleDbCommand("SELECT * FROM [Members$]", objXConn) Return objCommand End Function

We are now ready to use this function within the ButtonView_Click event handler.

The ButtonView_Click event handler starts by hiding the PanelUpload and PanelImport Panels, while showing the PanelView interface (which is where the GridView is located).

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

Next, a new OleDbDataAdapter object is created and its SelectCommand is assigned the OleDbCommand object returned by the ExcelConnection function.

' Create a new Adapter Dim objDataAdapter As New OleDbDataAdapter() ' retrieve the Select command for the Spreadsheet objDataAdapter.SelectCommand=ExcelConnection()

The purpose of this OleDbDataAdapter object is to populate a DataSet with the contents of the Excel query; this DataSet is then bound to the GridView.

' Create a DataSet Dim objDataSet As 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() End Sub

Now when the user selects the "View Excel Data" button (after having uploaded the spreadsheet), the spreadsheet's data will be retreived and displayed within the GridView.

At this point a user can upload an Excel spreadsheet and view it within a GridView. Our next task is to enable the user to import the uploaded spreadsheet into the application database. We'll tackle this in a future installment. Until then, may your dreams be in ASP.NET!

By Nannette Thacker

Further Readings:

CSS Tutorials

Reading Excel Files with ADO.NET

Excel Spreadsheet Connection Strings (Connection Strings for Excel 2007)

Attachments

Download the Application (in ZIP format)

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. Her blog is online at http://weblogs.asp.net/nannettethacker/.