Excel File Class for Reading Data

Imports System.Data

Partial Class _Default
    Inherits System.Web.UI.Page
    Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click

        'reset this incase you upload a new file
        'these session variables maybe used later if you decided to reread data or 
        'change the data that you are looking for
        Session("Path") = Nothing
        Session("FileName") = Nothing

        'clear the radio button list
        rblMultipleSheets.Items.Clear()

        'set all the local vars from the get go
        Dim intFileNameLength As Integer = Nothing
        Dim strFileNamePath As String = Nothing
        Dim strFileNameOnly As String = Nothing
        Dim strFileExtension As String = Nothing

        'Make sure that they uploaded something is probibly a good idea
        If Not (UploadFile.PostedFile Is Nothing) Then

            'Do some error checking eary on about file type or file size
            If UploadFile.PostedFile.ContentLength > 1000000 Then
                lblMessage.Text = "The Size of file is greater than 1 MB." & "Actual File Size is " & UploadFile.PostedFile.ContentLength
                Exit Sub
            End If

            'set the file name variable and clean up any funkyness that may exist
            strFileNamePath = UploadFile.PostedFile.FileName
            intFileNameLength = InStr(1, StrReverse(strFileNamePath), "\")
            strFileNameOnly = Mid(strFileNamePath, (Len(strFileNamePath) - intFileNameLength) + 2)

            'Created this as a friendly name to return to the user
            Dim strOriginalName As New String(strFileNameOnly)

            'set the file type
            strFileExtension = Right(strFileNameOnly, Len(strFileNameOnly) - InStr(strFileNameOnly, "."))

            'do some error checking around file type
            'from here we should call different subroutines based on the file type
            'The ExcelFile class also contains error checking on file type
            If UCase(strFileExtension) <> "XLS" Then
                lblMessage.Text = "Wrong file type Jerky try something else. The File you uploaded is a <i>" & strFileExtension & "</i>"
                Exit Sub
            End If

            'figure out the total path you are going to use really this
            'should be located in your web.config
            'this path really can vary depending on how you setup your project / site
            Dim paths = Server.MapPath("excelreading")
            paths = paths & "\Excel\"

            'rename the file to something unique that you can perserve it because you may read from it a couple times
            strFileNameOnly = Format(Date.Today, "mm-dd-yyyy").Replace("/", "") & Date.Now.TimeOfDay.Milliseconds & ".xls"

            'reset the file name and save it locally so you can mess with it
            UploadFile.PostedFile.SaveAs(paths & strFileNameOnly)

            'save this in the session incase we need to work on it later
            'these session variables maybe used later if you decided to reread data or 
            'change the data that you are looking for
            Session("Path") = paths
            Session("FileName") = strFileNameOnly

            'instanciate the new handy dandy new object I wrote :) 
            Dim Excel As New ExcelFile(paths, strFileNameOnly)

            'Tell some useless information about this file
            'Note this tells the original file name which will be more useful to the user
            lblMessage.Text = "You uploaded <i>" & strOriginalName & "</i><br>" & _
            strOriginalName & " contains <i>" & Excel.ExcelWorkSheets.Count & "</i> workbooks"

            '''''''''''''''''''''''''''''''''''''''''''''''''''''
            'load all the work sheet page names in a radio button list
            '''''''''''''''''''''''''''''''''''''''''''''''''''''
            'counter for loop 
            Dim i As Int16 = Nothing
            'rblMultipleSheets
            'loop through all the transactions and add aall the items
            For i = 0 To Excel.ExcelWorkSheets.Count - 1
                rblMultipleSheets.Items.Add(Excel.ExcelWorkSheets.Item(i).TableName)
            Next

            rblMultipleSheets.SelectedIndex = 0

            '''''''''''''''''''''''''''''''''''''''''''''''''''''
            'Bind to the data grid with the first work sheet
            '''''''''''''''''''''''''''''''''''''''''''''''''''''
            DataGrid1.DataSource = Excel.GetWorkSheetByName(Excel.ExcelWorkSheets.Item(0).TableName)
            DataGrid1.DataBind()

        End If

    End Sub


    Protected Sub rblMultipleSheets_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles rblMultipleSheets.SelectedIndexChanged

        'instanciate the new handy dandy new object I wrote :) 
        'session variables are reset / set when you upload a file
        Dim Excel As New ExcelFile(Session("path"), Session("FileName"))

        'create a new dataset and populated it with worksheet name
        Dim ds As New DataSet
        ds = Excel.GetWorkSheetByName(rblMultipleSheets.SelectedValue)

        'make sure there is data before you bind to it
        If ds.Tables.Count > 0 Then
            DataGrid1.DataSource = ds
            DataGrid1.DataBind()
        End If
    End Sub
End Class