When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips
Search

Sections:
Book Reviews
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
Web Hosts
XML
Information:
Advertise
Feedback
Author an Article

ASP ASP.NET ASP FAQs Message Board Feedback
 
Print this Page!
Published: Wednesday, March 5, 2008

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.
  • 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


    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!

    - continued -

    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:

    <table>
       <tr>
          <td></td>
          <td></td>
          <td></td>
       </tr>
    </table>

    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:

    <table>
       <tr>
          <td>
             <asp:Button ID="ButtonUpload" runat="server"
             Text="Upload Excel Spreadsheet" />
          </td>
          <td>
             <asp:Button ID="ButtonView" runat="server"
             Text="View Excel Data" />
          </td>
          <td>
             <asp:Button ID="ButtonImport" runat="server"
             Text="Import Excel Data" />
          </td>
       </tr>
    </table>

    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.

    Add three Panel Web controls to your page.

    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:

    <asp:Panel ID="PanelUpload" runat="server" Visible="False">
       Please select an Excel file to import:<br />
       <asp:FileUpload ID="FileUploadExcel" runat="server" />
       <br /><br />   
       <asp:Button ID="ButtonUploadFile" runat="server" Text="Upload File" />
       <br /><br />
       <asp:Label ID="LabelUpload" runat="server" Text=""></asp:Label>
    </asp:Panel>

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

    The FileUpload control is displayed when the 'Upload Excel Spreadsheet' button is clicked.

    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.

    May your dreams be in ASP.NET!

    By Nannette Thacker


    Further Readings:

  • Uploading Files in ASP.NET
  • The FileUpload Control QuickStarts
  • The Dark Side of File Uploads
  • 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/.

    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.


  • ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article