read Exel WorkSheet

read Exel WorkSheet readExelWorkSheet read Exel WorkSheet [VB.NET] do hoang hai /PreviewSnippet.aspx?SnippetID=9085a787-c05c-493a-bff0-f65ebc205bb5 SurroundsWith


    ''' read the sheet1 in exel file to a dataTable and return it
    ''' 
    ''' filePath to the file (include file name)
    ''' 
    ''' for more detail: http://support.microsoft.com/kb/316934
    Public Shared Function readExelFile(ByVal filePath As String) As DataTable
        Dim dtTable As New DataTable
        Dim Excel_Conn_string As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & filePath ; Extended Properties=Excel 8.0; HDR=NO"
        Dim odbconn As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection(Excel_Conn_string)
        Dim dbAdapt As System.Data.OleDb.OleDbDataAdapter = New System.Data.OleDb.OleDbDataAdapter("SELECT * from [sheet1$]", odbconn)
        Try
            Dim odbcmd As New System.Data.OleDb.OleDbCommand
            odbcmd.Connection = odbconn
            odbcmd.CommandText = "SELECT * from [sheet1$]"
            odbconn.Open()
            Dim dtSet As New DataSet
            dbAdapt.Fill(dtSet)
            dtTable = dtSet.Tables(0)
        Catch ex As Exception
        Finally
            odbconn.Close()
        End Try
        Return dtTable
    End Function]]>