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.
From the Internet.com eBook Library: Navigating Your IT Career
A career in information technology usually has its share of ups and downs. Download this Internet.com eBook to learn where the jobs are in IT, how to negotiate a salary, and helpful advice on job security and how to deal with a layoff. Join Internet.com now to download! http://www.devx.com/ebook/Link/34938
Interested in placing your TEXT AD HERE? Click Here
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:InetpubwwwrootExcelImport.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/.
All newsletters are sent from the domain "internet.com." Please use this domain name (not the entire "from" address, which varies) when configuring e-mail or spam filter rules, if you use them.
No comments:
Post a Comment