Question

VB OLE中显示EXCEL sheet问题

在VB的ole中显示Excel表格 ，一般都是显示sheet1；
现在表格有多个sheet，
通过怎么样的操作我可以指定在OLE中显示我指定的sheet

Answer 1

Private Function readExcel(ByVal index As Integer) As DataTable
Dim ExcelConn As New OleDbConnection
Try
Dim connstr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & 你的xls文件路径 & ";Extended Properties=""Excel 8.0;HDR=YES;"""

ExcelConn.ConnectionString = connstr

ExcelConn.Open()
Dim dt As DataTable = ExcelConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
If dt Is Nothing Then
arrayListlog.Add("Error on readExcel : SchemaGuid can not read.")
End If
Dim dr As DataRow = dt.Rows(0)
Dim sheetName As String = dr("TABLE_NAME").ToString()

Dim cmdText As String = "Select * from [$" & sheetName & "]"
Dim excelAdpt As New OleDbDataAdapter(cmdText, connstr)
Dim ds As New DataSet
excelAdpt.Fill(ds)

Return ds.Tables(0)

Catch ex As Exception
ableToUpload = False
arrayListlog.Add("Error on readExcel : " & ex.Message)
Return Nothing
Finally
ExcelConn.Close()
ExcelConn.Dispose()
End Try

End Function

sheetName 可以改的。通过更改dt.rows(index) <- index 就是sheet的序号
在这里放的是。。。 Dim dr As DataRow = dt.Rows(0) 。。。也就是第一个sheet 这个function 可以return 一个 datatable