阿撒不魯の居場所

Excel檔

Public Class ExcelReader

    ''' <summary>
    ''' 讀取Excel到DataTable
    ''' </summary>
    ''' <param name="file">檔案</param>
    ''' <param name="sheet">工作表名</param>
    ''' <param name="startRow">開始行</param>
    ''' <param name="endRow">結束行</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ReadToDataTable(file As String, sheet As String, startRow As Integer, Optional endRow As Integer = 65535) As DataTable
        
        Dim excelCnString = String.Empty
        If IO.Path.GetExtension(file).ToUpper.Equals(".XLS") Then
            excelCnString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source={0}; Extended Properties=&quot;Excel 8.0;HDR=YES;;IMEX=1&quot;"
        Else
            excelCnString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source={0}; Extended Properties=&quot;Excel 12.0;HDR=YES;;IMEX=1&quot;"
        End If

        Dim dt = New DataTable
        Dim cnString = String.Format(excelCnString, file) 
        
        Using cn As IDbConnection = New OleDb.OleDbConnection(cnString)
            Try
                cn.Open()
                Dim cmdString As String = String.Format("SELECT * FROM [{0}${1}:{2}]", sheet, startRow, endRow)
                Using cmd As IDbCommand = New OleDb.OleDbCommand(cmdString, cn)
                    dt.Load(cmd.ExecuteReader)
                    Return dt
                End Using
            Catch ex As Exception
                Throw
            Finally
                cn.Close()
            End Try

        End Using
    End Function

End Class

CSV檔

Public Class CsvReader

    ''' <summary>
    ''' 讀取Csv到DataTable
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ReadToDataTable(file As String, Optional ByVal header As Boolean = True) As DataTable
        Dim dt = New DataTable
        Dim dir = IO.Path.GetDirectoryName(file)
        Dim cnString = String.Format("Provider=Microsoft.Jet.OLEDB.4.0; Data Source={0}\; Extended Properties=""Text;HDR={1};FMT=Delimited""", dir, IIf(header, "YES", "NO"))
        Dim cmdString = String.Format("SELECT * FROM [{0}]", IO.Path.GetFileName(file))

        Using cn As OleDb.OleDbConnection = New OleDb.OleDbConnection(cnString)
            Try
                cn.Open()
                Using cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand(cmdString, cn)
                    dt.Load(cmd.ExecuteReader)
                    Return dt
                End Using
            Catch ex As Exception
                Throw
            Finally
                cn.Close()
            End Try
        End Using

    End Function

End Class