excel - 使用 Comparable Interface (VB.net) 比较 2 个 Excel 工作表并突出显示删除、更改或添加

Question

我是 Visual Basic 的血腥初学者，我只是不知道该怎么做。我需要通过表单添加 2 个 excel 文件，然后在新的 windows 表单中显示差异。这就是我做第一种形式的方式：

Private Sub btnDatei1_Click(sender As Object, e As EventArgs) Handles btnDatei1.Click
    'OpenFileDialog1.Filter = "EXCEL Spreadsheet | *.xlsm |Excel Spreadsheet with macros | *.xlsx"      Funktioniert nicht

    If OpenFileDialog1.ShowDialog() = DialogResult.OK Then
        txtDatei1.Text = OpenFileDialog1.FileName
        Worksheet1 = OpenFileDialog1.SafeFileName
    End If


End Sub

Private Sub btnDatei2_Click(sender As Object, e As EventArgs) Handles btnDatei2.Click
    If OpenFileDialog2.ShowDialog() = DialogResult.OK Then
        txtDatei2.Text = OpenFileDialog2.FileName
        Worksheet2 = OpenFileDialog2.SafeFileName
    End If
End Sub

Private Sub btnVergleich_Click(sender As Object, e As EventArgs) Handles btnVergleich.Click
    Form2.Show()
End Sub

现在我需要将 worksheet1 和 worksheet2 与可比较的界面进行比较，我什至不知道如何开始。我感谢每一个帮助，谢谢。您还可以在 C# 中编写提示。让我知道是否缺少任何信息。

标签： excelvb.net

Answer 1

我可以为您提供阅读 Excel 表格的代码。

首先，我使用的是从 Visual Studio 自己的 Nuget 包管理器下载的 OpenFileDialog。使用此 OpenFileDialog，您只需拥有比已包含的选项更多的选项。您还必须Microsoft.Office.Interop从 Nuget 下载该软件包。

其次，您应该小心使用 MS Office 产品 - 您必须使用 发布文件Marshal.ReleaseComObject()，否则当您在桌面上打开它们时将无法编辑它们。您将不得不重新启动 PC。

由于您没有确切说明您想比较什么以及如何比较，我只是创建了两个 Excel 文件并在每个文件中输入了 7 个测量值。当程序读入数字时，这 7 个值被保存在一个List(of Double). List(of ..) 的优点是您可以稍后对其进行排序（例如，按大小）。这是一种以集体方式存储数据的常用方法。

旁边的另一个注意事项：小心使用 Excel——我最近看到有人将英文符号 (7.112) 的数字复制到“德文” Excel 文件中，他想，“为什么它是 7112？” 因此，我添加Deu As New System.Globalization.CultureInfo("de-DE")了它，以便至少为您定义。这些数字在另一个 WinForm 上输出（我现在刚刚减去它们）。

Form1.vb

Imports Microsoft.WindowsAPICodePack.Dialogs
Imports Microsoft.Office.Interop
Public NotInheritable Class FormMain
    Private xlApp As New Microsoft.Office.Interop.Excel.Application
    Private xlWorkBook As Microsoft.Office.Interop.Excel.Workbook
    Public MeaseringValues1 As New List(Of Double)
    Public MeaseringValues2 As New List(Of Double)
    Private Sub FormMain_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    End Sub

    Private Async Sub Button_read_Click(sender As Object, e As EventArgs) Handles Button_read.Click
        MeaseringValues1.Clear()
        MeaseringValues2.Clear()
        Await Task.Run(Sub() parseFile())
        If MeaseringValues1.Count = 0 Then Return

        Using FSD As New FormShowDifferences
            FSD.ShowDialog(Me)
        End Using
    End Sub

    Private Sub parseFile()
        Dim Path As String
        Using OFD1 As New CommonOpenFileDialog
            OFD1.Title = "Exceldatei auswählen"
            OFD1.Filters.Add(New CommonFileDialogFilter("Excel", ".xlsx"))
            OFD1.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop)
            Dim Result As CommonFileDialogResult
            Me.Invoke(Sub() Result = OFD1.ShowDialog())
            If Result = CommonFileDialogResult.Ok Then
                Path = OFD1.FileName
            Else
                Return
            End If
        End Using

        '–––––––––––––––––––––––––––––––––––––––––––––––––––––––––––
        ' File 1
        '–––––––––––––––––––––––––––––––––––––––––––––––––––––––––––

        xlWorkBook = xlApp.Workbooks.Open(Path)
        Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet = CType(xlWorkBook.Worksheets("Tabelle1"), Microsoft.Office.Interop.Excel.Worksheet)
        Dim xlRange As Microsoft.Office.Interop.Excel.Range = xlWorkSheet.UsedRange

        Dim ER As Microsoft.Office.Interop.Excel.Range

        For rCnt As Integer = 1 To xlRange.Rows.Count Step 1
            ER = CType(xlRange.Cells(rCnt, 1), Microsoft.Office.Interop.Excel.Range)
            MeaseringValues1.Add(CDbl(ER.Value))
        Next


        xlWorkBook.Save()
        xlWorkBook.Close()
        xlApp.Quit()


        '–––––––––––––––––––––––––––––––––––––––––––––––––––––––––––
        ' File 2
        '–––––––––––––––––––––––––––––––––––––––––––––––––––––––––––

        Using OFD2 As New CommonOpenFileDialog
            OFD2.Title = "Exceldatei auswählen"
            OFD2.Filters.Add(New CommonFileDialogFilter("Excel", ".xlsx"))
            OFD2.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop)
            Dim Result As CommonFileDialogResult
            Me.Invoke(Sub() Result = OFD2.ShowDialog())
            If Result = CommonFileDialogResult.Ok Then
                Path = OFD2.FileName
            Else
                Return
            End If
        End Using

        xlWorkBook = xlApp.Workbooks.Open(Path)
        Dim xlWorkSheet2 As Microsoft.Office.Interop.Excel.Worksheet = CType(xlWorkBook.Worksheets("Tabelle1"), Microsoft.Office.Interop.Excel.Worksheet)
        Dim xlRange2 As Microsoft.Office.Interop.Excel.Range = xlWorkSheet2.UsedRange

        Dim ER2 As Microsoft.Office.Interop.Excel.Range

        For rCnt As Integer = 1 To xlRange2.Rows.Count Step 1
            ER2 = CType(xlRange2.Cells(rCnt, 1), Microsoft.Office.Interop.Excel.Range)
            MeaseringValues2.Add(CDbl(ER2.Value))
        Next


        xlWorkBook.Save()
        xlWorkBook.Close()
        xlApp.Quit()

        '–––––––––––––––––––––––––––––––––––––––––––––––––––––––––––
        ' clean up after yourself ;)
        '–––––––––––––––––––––––––––––––––––––––––––––––––––––––––––

        If xlWorkSheet IsNot Nothing Then System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkSheet)
        If xlWorkSheet2 IsNot Nothing Then System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkSheet2)
        If xlWorkBook IsNot Nothing Then System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkBook)
        If xlApp IsNot Nothing Then System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp)
        xlApp = Nothing
        xlWorkBook = Nothing

    End Sub

    Private Sub FormMain_FormClosing(sender As Object, e As FormClosingEventArgs) Handles MyBase.FormClosing
        If xlWorkBook IsNot Nothing Then xlWorkBook.Close()
        If xlApp IsNot Nothing Then xlApp.Quit()
    End Sub
End Class

FormShowDifferences.vb

Public NotInheritable Class FormShowDifferences
    Private ReadOnly Deu As New System.Globalization.CultureInfo("de-DE")
    Private Sub FormShowDifferences_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        For i As Integer = 0 To FormMain.MeaseringValues1.Count - 1 Step 1
            ListBox1.Items.Add(Math.Round(FormMain.MeaseringValues1(i) - FormMain.MeaseringValues2(i), 3).ToString(Deu))
        Next
    End Sub
End Class

我还设计了异步的读入过程。这意味着它在与 GUI 线程不同的线程上运行，因此您的应用程序不会冻结。如果文件非常大，这可能很有用。对于这个有 7 个值的小例子，这实际上是过度的。但这就是你曾经见过的样子。对于用于与用户交互的事物，您必须调用。例如Me.Invoke(Sub() Result = OFD2.ShowDialog())；否则你会得到一个异常“跨线程操作无效”。