Importing an Excel Spreadsheet Using Typed DataSets and TableAdapters: Displaying the Uploaded Excel SpreadsheetBy Nannette Thacker
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!
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
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.
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.
Once you apply the scheme, view the GridView's declarative markup to see the added formatting properties.
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
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:
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
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.
And here it is after the stylesheet has been added. Note that the font is now Arial and the size is smaller.
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
Clickevent. To create this event handler, simply double-click the Button control in the Design view. This will create a method named
ButtonView_Clickthat is wired up to the Button's
Clickevent via 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.
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
for more details on this useful method.
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
Connection properties via its constructor. This
OleDbCommand object is then returned.
We are now ready to use this function within the
ButtonView_Click event handler.
ButtonView_Click event handler starts by hiding the
PanelImport Panels, while showing the
PanelView interface (which is where the GridView is located).
Next, a new
OleDbDataAdapter object is created and its
SelectCommand is assigned the
returned by the
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.
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!
About the Author: