vb.net 讀取Excel

shaniushamao發表於2017-02-17
Excel

1、定義excel操作變數

dim objexcelfile as excel.application

dim objworkbook as excel.workbook

dim objimportsheet as excel.worksheet

2、開啟excel程式，並開啟目標excel檔案
set objexcelfile = new excel.application
objexcelfile.displayalerts = false
set objworkbook = objexcelfile.workbooks.open(strfilename)

set objimportsheet = objworkbook.sheets(1)

3、獲取excel有效區域的行和列數
intlastcolnum = objimportsheet.usedrange.columns.count

intlastrownum = objimportsheet.usedrange.rows.count

4、逐行讀取excel中資料

由於前兩行為header部分，所以需要從第三行讀取

如果第1到第10個單元格的值均為空或空格，則視為空行

for intcounti = 3 to intlastrownum

check if empty data row

blnnullrow = true

for inti = 1 to 10

if trim$(objimportsheet.cells(intcounti, inti).value) <> "" then

blnnullrow = false

end if

next inti

若不是空行，則進行讀取動作，否則繼續向後遍歷excel中的行

if blnnullrow = false then

獲取單元格中的資料，做有效性check，並將合法資料建立為實體存入物件陣列中

objimportsheet.cells(intcounti, 1).value

……

end if

next intcounti

5、退出excel程式，並關閉excel相關操作物件
objexcelfile.quit
set objworkbook = nothing
set objimportsheet = nothing

set objexcelfile = nothing




2

 Public Shared Function PFn_ExcelToDataTable(FileName As String) As DataTable


        Dim dtRetrunTable As New DataTable


        Dim strConn As String = ("Provider=Microsoft.Ace.OleDb.12.0;" & "data source=") + FileName & ";Extended Properties='Excel 12.0; HDR=YES; IMEX=1'"


        Dim conn As New OleDb.OleDbConnection(strConn)
        conn.Open()


        Dim myDataSet As DataSet = New DataSet


        Dim strExcel = "SELECT * FROM [sheet1$]"




        Using da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(strExcel, conn)
            Try
                da.Fill(myDataSet)
                dtRetrunTable = myDataSet.Tables(0)
            Catch ex As Exception


            Finally
                If conn.State = ConnectionState.Open Then
                    conn.Close()
                    conn = Nothing
                End If
                da.Dispose()
            End Try


        End Using


        Return dtRetrunTable


    End Function

相關文章