none
想请指点有关user 第二次读取excel file 时如何reload excel file sheet RRS feed

  • 问题

  • 我篇写了读取excel sheet file 的代码。以下是我的代码请查看

     我遇到的问题是当user 第二次读取excel file 时无法reload excel file sheet

    If path = "" Then
                path = OpenFile()
                If path <> "" Then
                    Me.Refresh()


                    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 If
            Else
                dgv1.DataSource = Nothing
                dgv1.Rows.Clear()

                path = OpenFile()

            End If

    Private Function OpenFile() As String

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

            'show the ofd and if a file was selected return it, otherwise return nothing
            If OFD.ShowDialog = Windows.Forms.DialogResult.OK Then
                Return OFD.FileName
            Else
                Return Nothing
            End If
        End Function


    2020年4月5日 15:36

答案

  • Hi christing,

    根据你的问题,我进行了相应的测试,你可以参考以下代码:

        Dim path As String
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            If path = "" Then
                path = OpenFile()
                If path <> "" Then
                    Me.Refresh()
                    AddComboboxValue(path)
                End If
            Else
                dgv1.DataSource = Nothing
                path = OpenFile()
                AddComboboxValue(path)
            End If
        End Sub
        Private Sub AddComboboxValue(path As String)
            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)
            ComboBox1.SelectedIndex = 0
        End Sub

    每次button1选择要打开的Excel文件时,ComBobox将首先显示第一张表的值.

    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年4月8日 2:23
    版主

全部回复

  • 此外我尝试了以下方法想请帮忙看看。感恩

                

       If path = "" Then
                path = OpenFile()
                If path <> "" Then
                    Me.Refresh()


                    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 If
            Else
                ComboBox1.Text = ""
                ComboBox1.Items.Clear()
                'ComboBox1.Items.AddRange(excelSheets)
                dgv1.DataSource = Nothing
                dgv1.Rows.Clear()

                path = OpenFile()
                If path <> "" Then
                    Me.Refresh()


                    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 If


            End If
        End Sub

    谢谢

    2020年4月5日 15:50
  • Hi christing,

    根据你的问题,我进行了相应的测试,你可以参考以下代码:

        Dim path As String
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            If path = "" Then
                path = OpenFile()
                If path <> "" Then
                    Me.Refresh()
                    AddComboboxValue(path)
                End If
            Else
                dgv1.DataSource = Nothing
                path = OpenFile()
                AddComboboxValue(path)
            End If
        End Sub
        Private Sub AddComboboxValue(path As String)
            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)
            ComboBox1.SelectedIndex = 0
        End Sub

    每次button1选择要打开的Excel文件时,ComBobox将首先显示第一张表的值.

    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年4月8日 2:23
    版主
  • 可以用Free Spire.XLS试试(不依赖MS Excel),数据导入导出很方便

    Dim t As DataTable = dataSet.Tables(0)
    Dim book As Workbook = New Workbook
    Dim sheet As Worksheet = book.Worksheets(0)
    sheet.InsertDataTable(t, True, 1, 1)   book.SaveToFile("insertTableToExcel.xls",ExcelVersion.Version97to2003

    参考自:https://www.e-iceblue.com/Tutorials/Spire.XLS/Spire.XLS-Program-Guide/Data-Export-/Import-Export-Datatable-to-Excel-from-Database.html


    2020年4月8日 6:00
  • Hi christing,

    你的问题解决了吗?如果已经解决,请将正确的回答标记为答复,这将帮助遇到类似的其他人可以在论坛上快速找到答案。

    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年4月15日 7:04
    版主