Importing an Excel Spreadsheet Using Typed DataSets and TableAdapters: Building the Importer Web Page and Uploading the Excel SpreadsheetBy Nannette Thacker
This article is the second in a series of step-by-step tutorials that show how to build an ASP.NET page to import an Excel spreadsheet into a database using Typed DataSets and TableAdapters. Last week's article, Building the Database, started with an overview of the system. We then created the ASP.NET website project, the database, and the two database tables,
In this installment we will build the ASP.NET page for importing the Excel spreadsheet. This entails creating the page's user interface and file upload capabilities. At the conclusion of this article we will have created a page that enables users to upload their Excel spreadsheets. Read on to learn more!
Using Tables to Define a Page Layout
Developers migrating from a Windows development back to ASP.NET development are often frustrated with simple layout and display issues. When creating a Windows Form, developers can precisely place controls through drag and drop. With web pages, things aren't nearly as easy.
A web page's layout can be controlled using any one or combination of the following approaches:
<table>s - the HTML
<table>element displays a multi-column / multi-row table, which can be used to arrange the contents of a page in a variety of ways.
- Cascading Style Sheets (CSS) - CSS is a language used to define the presentation of content within a web page and includes a variety of layout options.
<table>, but I invite you to explore both options and use whatever approach you're most comfortable with. For more information on page layout using
<table>s, see the HTML Layout Tutorials. For a good primer on CSS, check out this CSS Tutorial.
Default.aspx page in your project and go to the Source view. Enter in the following HTML:
This creates a table with one row (
<tr>) and three cells within that row (
<td>). Return to the Designer.
You should see a small table with one row and three very thin cells.
Adding Button Controls
We need three Button Web controls on our page, one to upload the Excel spreadsheet, one to view the contents, and another to import the data into the database tables. Drag three Button controls from the Toolbox into each of the three table cells. (Note that once you place the Button in the cell, it expands to accommodate the Button's size.) Name the first Button control
ButtonUploadand set its
Textproperty to "Upload Excel Spreadsheet." Name the second Button
ButtonViewand set its
Textto "View Excel Data." Finally, add a third Button control named
ButtonImportand set its
Textto "Import Excel Data." After adding these three Button Web controls, your Table's declarative markup should look similar to the following:
Partitioning the Web Form Into Three Regions
The three Button Web controls we just added are shown at all times. The remainder of the page's controls, however, are only shown in certain situations. For example, when the "Upload Excel Spreadsheet" button is clicked, we need to display the FileUpload control to allow the user to upload the Excel spreadsheet. Clicking the "View Excel Data" needs to show a GridView summarizing the just-uploaded Excel spreadsheet. To show or hide a particular set of controls in a page, use a Panel Web control. The Panel can hold any number of Web controls, and the entire Panel can be shown or displayed by setting its
Drag three Panel controls from the Toolbox to just below the Table control, naming them
PanelImport. Set the
Visible properties for all three Panels to False.
Adding the FileUpload Control
Our "Excel Spreadsheet File Upload" button will display an interface that allows the user to select an Excel spreadsheet to upload from their computer. To render this interface we will use the FileUpload control. In addition to the FileUpload control we will add an "Upload File" button that the user will click after selecting the spreadsheet in order to upload it to the web server. Once the file has been uploaded to the web server, we will save it to the web server's file system.
Add a FileUpload control to the
PanelUpload Panel and set its
FileUploadExcel. Next, add text instructions for the
user, such as "Please select an Excel file to import:". Following that, add a Button Web control named
ButtonUploadFile; set its
Text property to "Upload File."
Finally, add a Label control to the same Panel, setting its
LabelUpload and clearing out its
We'll use this Label to display the results of the file upload action.
At this point your Panel's declarative markup should look similar to the following:
Uploading the Excel Spreadsheet
When the user clicks the "Upload Excel Spreadsheet" Button we need to display the
PanelUploadPanel (and hide the other two Panels). To accomplish this, create an event handler for the
Clickevent. The easiest way to create this is to double-click the Button in the Designer.
Add code to the
Click event handler that sets the
Visible property to True, and the other
Visible property to False.
With this code in place, when the "Upload Excel Spreadsheet" button is clicked, the contents in the
are shown (the FileUpload control and the "Upload File" button).
We now need to create an event handler for the "Upload File" Button's
Click event. This event handler needs to first check to ensure that the
user selected an Excel spreadsheet to upload, and then it needs to save the spreadsheet to the web server's hard drive.
Take a moment to review this event handler. We check the
HasFile property to check and see that the user
selected a file before clicking the "Upload File" button. If no file was uploaded, the message "Please select a file to upload" is displayed in the
LabelUpload control. If, however, a file has been uploaded, we save it to the web server's hard drive using the
SaveAs method takes in a physical file path and saves the file to that location. We use the
to convert a virtual path -
~/ExcelImport.xls - into a physical path (such as
For more information on this technique, see Scott Mitchell's article,
After saving the file, the Label's
Text property displays information about the just-uploaded file, including the name of the file the
user uploaded, its content-type, and file size. If an exception is raised during the upload process, the error message is displayed in the Label.
|Subtleties with Saving Uploaded Files to the File System|
There are a couple of common gotchas and subtleties involved with saving files to the web server's file system. One issue revolves around naming the
saved file. In our code, we save the uploaded Excel spreadsheet with the name |
Another concern is security. Because we are saving the file in the web site's folder, any user who enters the URL
With the upload capability complete, we're ready to look at displaying the uploaded Excel spreadsheet in a GridView. We'll tackle this piece in a future article.
May your dreams be in ASP.NET!
About the Author: