Problems reading an Excel file in VB.net

Question

I have been trying to upload and read an Excel file (.xls, or .xlsx)

I am upploading sucessfully using this code:

Protected Sub btnUpload_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles    btnUpload.Click
    Dim filepath As String = ""
    If FileUpload1.HasFile Then
        Try
            Dim filename As String = FileUpload1.PostedFile.FileName
            Dim extension = (filename.Substring(filename.LastIndexOf("."), (filename.Length() - filename.LastIndexOf("."))))
            If extension = ".xlsx" Or extension = ".xls" Then
                filepath = "\" & Common.toUnix(Now) & "_" & filename
                FileUpload1.SaveAs(Server.MapPath("~/") & filepath)
                  ' ====   NOW READ THE FILE
            Else
                StatusLabel.InnerText = "Only Excel file types are accepted (.xls/.xlsx)<br> File Uploaded had extension: " & extension
            End If

        Catch ex As Exception
            StatusLabel.InnerText = "Upload status: The file could not be uploaded. The following error occured: " + ex.ToString()
        End Try

    End If
End Sub

It uploads OK, but when trying to read the file I get this error:

System.Data.OleDb.OleDbException (0x80004005): The Microsoft Jet database engine cannot open the file ''. It is already opened exclusively by another user, or you need permission to view its data.

I am using code to read similar to this:

vb.net traversing an xls / xlsx file?

Therefore the connection is as follows:

 Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0" & _
                           ";Data Source=" & ExcelFile & _
                           ";Extended Properties=Excel 8.0;"

    Dim conn As OleDbConnection = Nothing
    Dim dt As System.Data.DataTable = Nothing
    Dim excelDataSet As New DataSet()

    Try

        conn = New OleDbConnection(connString)

        conn.Open()  '<<< ERROR IS RAISED ON THIS LINE
        dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
        If dt Is Nothing Then
            Return Nothing
        End If

        Dim excelSheets(dt.Rows.Count) As String
        Dim i As Integer = 0
        For Each row As DataRow In dt.Rows
            excelSheets(i) = row("payments").ToString
            System.Math.Min(System.Threading.Interlocked.Increment(i), i - 1)
            If i = SheetNumber Then
                Exit For
            End If
        Next

     ..................

I'm uploading to a shared server so don't have control as to permissions as such, but I do have read/write permissions and uploading Images works OK, but it's reading this file that I can't get to work.

NOTE

This error occurs with .xls files, when using .xlsx I get this error:

System.Data.OleDb.OleDbException (0x80004005): Cannot update. Database or object is read-only. at System.Data.OleDb.OleDbConnectionInternal

This error occurs on this line:

 For Each row As DataRow In dt.Rows

So it appears it is uplpoading and opening the file OK, but not reading the rows....

I am not sure why that's happening either!

Any help would be much appreciated!

Answer 1

ACE is brutal, have troubles with it all the time and it cannot exist on a System in both 32 and 64 bit version at the same time. As a result I just dont use it at all.

To read an Excel XLSX file I use "EPPlus" which has proven to be very easy to deal with and extreamly efficient.

It ONLY works with XLSX (not XLS)

Example... (simple just pulls out first sheet as a datatable)

    Public Function XlsxToDataTable(byteStream As IO.MemoryStream) As DataTable
        Using pac As New OfficeOpenXml.ExcelPackage(byteStream)
            Dim wb As OfficeOpenXml.ExcelWorkbook = pac.Workbook
            Dim ws As OfficeOpenXml.ExcelWorksheet = wb.Worksheets(1) '1 based
            Dim out As New DataTable
            For iC As Integer = 1 To ws.Dimension.End.Column
                out.Columns.Add(ws.Cells(1, iC).Value.ToString)
            Next
            For iR As Integer = 2 To ws.Dimension.End.Row
                Dim nr As DataRow = out.NewRow
                For iC As Integer = 1 To ws.Dimension.End.Column
                    nr(iC - 1) = ws.Cells(iR, iC).Value
                Next
                out.Rows.Add(nr)
            Next
            Return out
        End Using
    End Function