VB.net OLEDB连接Excel文件

2020-02-22 09:29:40 391 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

vb.net 读取Excel 1、定义excel操作变量 dim objexcelfile as excel.application dim objworkbook as excel.workbook dim objimportsheet as excel.worksheet 阅读详情

    Public Function ExcTansposeAbf(ByVal strCollection As List(Of String), ByVal strTableInExcel As String) As Array

        Dim brr(0 To 1, 0 To 1) As String

        For intIndexInCollection As Integer = 0 To strCollection.Count - 1

            If Strings.Right(strCollection(intIndexInCollection), 4) Like "*xls*" Then

                Dim cnString As String = "provider=Microsoft.ACE.OLEDB.12.0;Data Source =" & strCollection(intIndexInCollection) & ";Extended Properties ='Excel 12.0;HDR = No;IMEX = 0';"

                Dim cnDataset As New DataSet

                Using cnCollection As New OleDbConnection(cnString)

                    Using cnAdapter As New OleDbDataAdapter("select * from [" & strTableInExcel & "$]", cnCollection)

                        Try

                            cnAdapter.Fill(cnDataset, strTableInExcel)

                        Catch ex As System.InvalidOperationException

                            brr(0, 0) = "Error_Oledb_InvalidOperationException"

                            Return brr

                        Catch ex As System.Data.OleDb.OleDbException

                            Continue For

                        Catch ex As System.Runtime.InteropServices.SEHException

                            brr(0, 0) = "Error_Oledb_InteropServices.SEHException"

                            Return brr

                        Finally

                            cnCollection.Dispose()

                            cnAdapter.Dispose()

                        End Try

                    End Using

                End Using

                Dim arr(0 To cnDataset.Tables(strTableInExcel).Rows.Count - 1, 0 To cnDataset.Tables(strTableInExcel).Columns.Count - 1) As String

                Try

                    For i As Integer = 0 To cnDataset.Tables(strTableInExcel).Rows.Count - 1

                        For j As Integer = 0 To cnDataset.Tables(strTableInExcel).Columns.Count - 1

                            If Not IsDBNull(cnDataset.Tables(strTableInExcel).Rows(i).Item(j)) Then

                                arr(i, j) = cnDataset.Tables(strTableInExcel).Rows(i).Item(j)

                            End If

                        Next

                    Next

                    If Not arr(0, 18) Like "*中华人民共和国海关进口货物报关单*" Then arr(0, 0) = "Err_Arr_RangeS2Lost"

                    Return arr

                Catch

                    brr(0, 0) = "Error_Oledb_DataSetToArray"

                    Return brr

                Finally

                    cnDataset.Dispose()

                End Try

            End If

        Next

        brr(0, 0) = "Error_Oledb_CantFindExcel"

        Return brr

    End Function