7,759
社区成员
发帖
与我相关
我的任务
分享
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