Importing an Excel Spreadsheet Using Typed DataSets and TableAdapters: Building the Importer Web Page and Uploading the 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.
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
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, Members
and Category.
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:
HTML <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.
In this example I am going to use HTML <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.
Open the 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 ButtonUpload
and set its Text property to "Upload Excel Spreadsheet." Name the second Button ButtonView and set its Text to
"View Excel Data." Finally, add a third Button control named ButtonImport and set its Text to "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 Visible property.
Drag three Panel controls from the Toolbox to just below the Table control, naming them PanelUpload, PanelView, and
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 ID to 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 ID to LabelUpload and clearing out its Text property.
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 PanelUpload Panel (and hide the other two Panels). To accomplish
this, create an event handler for the ButtonUpload's Click event. 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 PanelUpload Panel's Visible property to True, and the other
two Panels' Visible property to False.
Protected Sub ButtonUpload_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ButtonUpload.Click
PanelUpload.Visible=True
PanelView.Visible=False
PanelImport.Visible=False
End Sub
With this code in place, when the "Upload Excel Spreadsheet" button is clicked, the contents in the PanelUpload
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.
Protected Sub ButtonUploadFile_Click(ByVal sender As Object, ByVal e As System.EventArgs) _
Handles ButtonUploadFile.Click
If FileUploadExcel.HasFile Then
Try
' alter path for your project
FileUploadExcel.SaveAs(Server.MapPath("~/ExcelImport.xls"))
LabelUpload.Text="Upload File Name: " & _
FileUploadExcel.PostedFile.FileName & "<br>" & _
"Type: " & _
FileUploadExcel.PostedFile.ContentType & _
" File Size: " & _
FileUploadExcel.PostedFile.ContentLength & " kb<br>"
Catch ex As Exception
LabelUpload.Text="Error: " & ex.Message.ToString
End Try
Else
LabelUpload.Text="Please select a file to upload."
End If
End Sub
Take a moment to review this event handler. We check the FileUploadExcel's 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. The SaveAs method takes in a physical file path and saves the file to that location. We use the Server.MapPath method
to convert a virtual path - ~/ExcelImport.xls - into a physical path (such as C:\Inetpub\wwwroot\ExcelImport.xls).
For more information on this technique, see Scott Mitchell's article,
Using Server.MapPath.
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 ExcelImport.xls and save it in the web application's root
directory. But what if two users are visiting this page at the same time and both upload a spreadsheet? The latter user's spreadsheet will overwrite the
former's! If you expect this facility to be used by a single administrative user, then using a hard-coded file name is fine, but if you are anticipating
multiple users being able to visit this page simultaneously, then you'll need to take extra steps to ensure that the uploaded file name is unique.
One option is to use a globally unique identifier (GUID) as the file name.
Another concern is security. Because we are saving the file in the web site's folder, any user who enters the URL http://www.yoursite.com/ExcelImporter.xls
into their browser's Address bar can download the import spreadsheet. This may not be a major issue if the data is being imported into a public database,
but if the data is meant to be private or only accessible to certain users, then extra steps must be taken to ensure that the spreadsheet is uploaded to
a location that is not web accessible. See Another Potential Gotcha When Creating a Website that Allows Users to Share Uploaded Files
for a more detailed discussion on this topic.
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.
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/.
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.
