none
想请教通过combo preview sheet data RRS feed

  • 问题

  • 我篇写了browse excel 的代码?以下是我的代码

    Private Sub SBrowse_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SBrowse.Click

            Dim xlApp As Microsoft.Office.Interop.Excel.Application = New Microsoft.Office.Interop.Excel.Application()
            'Dim excelBook As Microsoft.Office.Interop.Excel.Workbook = xlApp.Workbooks.Open()
            Dim excelBook As Microsoft.Office.Interop.Excel.Workbook = xlApp.Workbooks.Open(OpenFile)
            Dim excelSheets As String() = New String(excelBook.Worksheets.Count - 1) {}
            Dim i As Integer = 0

            For Each wSheet As Microsoft.Office.Interop.Excel.Worksheet In excelBook.Worksheets
                excelSheets(i) = wSheet.Name
                i += 1
            Next

            ComboBox2.Items.AddRange(excelSheets)

        End Sub

     Public Function OpenFile() As String
            'build and configure an OpenFileDialog

            Dim OFD As New OpenFileDialog
            With OFD
                .AddExtension = True
                .CheckFileExists = True
                .Filter = "Excel  files Excel files (*.xls)|*.xls"
                .Multiselect = False
                .Title = "Select an workbook to open"
            End With

            If OFD.ShowDialog = Windows.Forms.DialogResult.OK Then
                Return OFD.FileName
            Else
                Return Nothing
            End If
        End Function

    我问题出现在ComboBox 无法 preview  sheet data

    想请指点

    Private Sub ComboBox2_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox2.SelectedIndexChanged
            Dim path As String
            path = OpenFile()
            Dim ds = New DataSet()
            Dim con As String = "provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1;';"
            Using connection As OleDbConnection = New OleDbConnection(con)
                connection.Open()
                Dim sheets As DataTable = GetSchemaTable(con)
                For Each r As DataRow In sheets.Rows
                    Dim data As DataTable = New DataTable()
                    Dim query As String = "SELECT * FROM [" & r("TABLE_NAME").ToString() & "]"
                    Dim adapter = New OleDbDataAdapter(query, connection)
                    adapter.Fill(data)
                    ds.Tables.Add(data) 'table 1
                Next
            End Using

            DataGridView1.DataSource = ds.Tables(ComboBox1.SelectedIndex)
        End Sub

    2020年3月17日 7:43

全部回复

  • Hi christing,

    根据你提供的信息,我做了一个测试,你可以参考如下代码:

    Private path As String Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click path = OpenFile() Dim xlApp As Microsoft.Office.Interop.Excel.Application = New Microsoft.Office.Interop.Excel.Application() Dim excelBook As Microsoft.Office.Interop.Excel.Workbook = xlApp.Workbooks.Open(path) Dim excelSheets As String() = New String(excelBook.Worksheets.Count - 1) {} Dim i As Integer = 0 For Each wSheet As Microsoft.Office.Interop.Excel.Worksheet In excelBook.Worksheets excelSheets(i) = wSheet.Name i += 1 Next

    ComboBox1.Items.Clear() ComboBox1.Items.AddRange(excelSheets) End Sub Private Sub ComboBox1_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ComboBox1.SelectedIndexChanged Dim ds = New DataSet() Dim con As String = "provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & path & ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1;';" Using connection As OleDbConnection = New OleDbConnection(con) connection.Open() Dim cmd As OleDbCommand = New OleDbCommand() Dim oleda As OleDbDataAdapter = New OleDbDataAdapter() Dim dt As DataTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing) Dim sheetName As String = String.Empty If dt IsNot Nothing Then dt = (From dataRow In dt.AsEnumerable() Where Not dataRow("TABLE_NAME").ToString().Contains("FilterDatabase") Select dataRow).CopyToDataTable() sheetName = dt.Rows(ComboBox1.SelectedIndex)("TABLE_NAME").ToString() End If cmd.Connection = connection cmd.CommandType = CommandType.Text cmd.CommandText = "SELECT * FROM [" & sheetName & "]" oleda = New OleDbDataAdapter(cmd) oleda.Fill(ds, "excelData") End Using DataGridView1.DataSource = ds.Tables("excelData") End Sub

    希望对你有帮助。

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    2020年3月18日 3:20
    版主