• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 354
  • Last Modified:

ASP.net - error trying to view an uploaded spreadsheet

JHi. I am getting the following error in the code below on the line marked XXXX
"External table is not in the expected format"
Protected Sub ButtonUpload_Click(ByVal sender As Object, ByVal e As EventArgs) Handles ButtonUpload.Click
        PanelUpload.Visible = True
        PanelView.Visible = False
        PanelImport.Visible = False
    End Sub

    Protected Sub ButtonUploadFile_Click(ByVal sender As Object, ByVal e As 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
    Protected Function ExcelConnection() As OleDbCommand

        ' Connect to the Excel Spreadsheet
        Dim xConnStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
              "Data Source=" & Server.MapPath("ExcelImport.xls") & ";" & _
              "Extended Properties=Excel 8.0;"

        ' create your excel connection object using the connection string
        Dim objXConn As New OleDbConnection(xConnStr)
        objXConn.Open() 'XXXX
        ' use a SQL Select command to retrieve the data from the Excel Spreadsheet
        ' the "table name" is the name of the worksheet within the spreadsheet
        ' in this case, the worksheet name is "Members" and is expressed as: [Members$]
        Dim objCommand As New OleDbCommand("SELECT * FROM [Members$]", objXConn)
        Return objCommand

    End Function



    Protected Sub ButtonView_Click(ByVal sender As Object, ByVal e As EventArgs) Handles ButtonView.Click

        'The ButtonView_Click event handler starts by hiding the PanelUpload and PanelImport Panels,
        ' while showing the PanelView interface (which is where the GridView is located). 
        PanelUpload.Visible = False
        PanelView.Visible = True
        PanelImport.Visible = False
        'Next, a new OleDbDataAdapter object is created and its SelectCommand is assigned the OleDbCommand object returned by the ExcelConnection function. 
        ' Create a new Adapter
        Dim objDataAdapter As New OleDbDataAdapter()

        ' retrieve the Select command for the Spreadsheet
        objDataAdapter.SelectCommand = ExcelConnection()
        'The purpose of this OleDbDataAdapter object is to populate a DataSet with the contents of the Excel query; this DataSet is then bound to the GridView.
        ' Create a DataSet
        Dim objDataSet As New DataSet()

        ' Populate the DataSet with the spreadsheet worksheet data
        objDataAdapter.Fill(objDataSet)

        ' Bind the data to the GridView
        GridViewExcel.DataSource = objDataSet.Tables(0).DefaultView
        GridViewExcel.DataBind()
    End Sub

Open in new window

0
Murray Brown
Asked:
Murray Brown
  • 2
1 Solution
 
Gary DavisDir Internet SvcsCommented:
The problem is the connection string or the file (perhaps wrong Excel version).

See these for formats and options that may be included on the connection string:

http://www.connectionstrings.com/excel
http://www.connectionstrings.com/excel-2007

Gary Davis
Webguild
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Hi. Could I include code to detect the Excel version. What code would I use for that?
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
thanks very much
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now