WPF:如何在不打开 excel 文件的情况下检索 excel 工作表名称 vb.net?

Question

xaml

<Window x:Class="MainWindow"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
Title="MainWindow" Height="350" Width="525">
<Grid>
<Button x:Name="Button1" Height="35" Width="100" Content="Click Me" Margin="50,0,0,0"/>
<ComboBox x:Name="Combobox1" Height="35" Width="100" Margin="50,100,0,0"/>
</Grid>
</Window>

vb.net

Imports System.Data
Class MainWindow
Private Sub Button1_Click(sender As Object, e As RoutedEventArgs) Handles Button1.Click
Dim myOleDbConnection As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:Book1.xlsx;Extended Properties=""Excel 12.0 Xml;HDR=YES"";")
myOleDbConnection.Open()
Dim myDataTable As System.Data.DataTable = myOleDbConnection.GetOleDbSchemaTable(schema:=System.Data.OleDb.OleDbSchemaGuid.Tables, restrictions:=Nothing)
Combobox1.ItemsSource = (myOleDbConnection.GetSchema("Tables", New String() {Nothing, Nothing, Nothing, "TABLE"}).AsEnumerable().Select(Function(d) d("TABLE_NAME").ToString.Replace("$", "")).Distinct().ToArray)
myOleDbConnection.Close()
End Sub
End Class

当我运行上面的代码时，我收到此错误：https://prnt.sc/jtiupv

Answer 1

下面的代码可能对你有帮助。 此处 FNAME 称为 Excel 文件位置。

Try
Dim StrConn As [String] = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & fname & ";Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
Dim Conn As New OleDbConnection(StrConn)
Conn.Open()
'Dim dtSheets As DataTable =
'    Conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, "Table"})
'Dim listSheet As New List(Of String)
Location.Text = fname  'Label Control
SheetName.Items.Clear() ' Combobox clear
SheetName.Items.AddRange(Conn.GetSchema("Tables", New String() {Nothing, Nothing, Nothing, "TABLE"}
).AsEnumerable().Select(Function(d) d("TABLE_NAME").ToString.Replace("$", "")).Distinct().ToArray)   'Binding All sheetname to combobox
Catch ex As Exception
Log.ErrorMessage(ex.Message, Me.Name) 'local log creation manual code
End Try