Reading an Excel File

Question

         Dim connString As String = ""
    Dim strFileType As String = Path.GetExtension(FileUpload1.FileName).ToLower()
    Dim path__1 As String = Server.MapPath(FileUpload1.PostedFile.FileName)

    If strFileType.Trim() = ".xls" Then
        connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & path__1 & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=2"""
    ElseIf strFileType.Trim() = ".xlsx" Then
        connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & path__1 & ";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=2"""
    End If
    Dim query As String = "SELECT * FROM [Sheet1$]"
    Dim conn As New OleDbConnection(connString)
    If conn.State = ConnectionState.Closed Then
        conn.Open()
    End If
    Dim cmd As New OleDbCommand(query, conn)
    Dim da As New OleDbDataAdapter(cmd)
    Dim ds As New DataSet()
    da.Fill(ds)

    da.Dispose()
    conn.Close()
    conn.Dispose()

ON da.Fill(ds) An exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll but was not handled in user code

Additional information: The Microsoft Jet database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly.

Kindly Help Me :(

Answer 1

I suggest use EPPlus library to read Excel files easily.

Update:

EPPlus has from this new major version changed license from LGPL to Polyform Noncommercial 1.0.0.

With the new license EPPlus is still free to use in some cases, but will require a commercial license to be used in a commercial business.