vb.net - 从excel导入数据并合并

Question

我正在使用以下代码从 excel 文件中读取并将某些列添加到 ListView 中。导入后，我会将它们导出为 CSV(未显示代码)。

我的问题是excel文件是一个提取文件，它按事务显示数据，导致数千行。我想执行 SUMIF 的 excel 等效项基于 EPoS 线并在可能的情况下整合信息？

以下数据示例...

    Public Structure ExcelRows
    Dim Unit As String
    Dim Outlet As String
    Dim EPoS As String
    Dim Quantity As String
    Dim Value As String
    Dim DateSale As String

End Structure
Public ExcelRowList As List(Of ExcelRows) = New List(Of ExcelRows)

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

End Sub

Public Function GetInfo() As Boolean
    Dim Completed As Boolean = False
    Dim MyExcel As New Excel.Application
    Dim enUK As New CultureInfo("en-GB")
    Dim DOS As String = "01/04/15"
    MyExcel.Workbooks.Open("C:\Dropbox\Tills\taRunAction1.xlsx")

    MyExcel.Sheets("Report").Activate()
    MyExcel.Range("A10").Activate()

    Dim ThisRow As New ExcelRows

    Do
        If MyExcel.ActiveCell.Value > Nothing Or MyExcel.ActiveCell.Text > Nothing Then

            ThisRow.Unit = MyExcel.ActiveCell.Value
            MyExcel.ActiveCell.Offset(0, 1).Activate()

            ThisRow.Outlet = MyExcel.ActiveCell.Value
            MyExcel.ActiveCell.Offset(0, 1).Activate()

            ThisRow.DateSale = MyExcel.ActiveCell.Value

            MyExcel.ActiveCell.Offset(0, 2).Activate()

            ThisRow.EPoS = MyExcel.ActiveCell.Value
            MyExcel.ActiveCell.Offset(0, 1).Activate()

            ThisRow.Quantity = MyExcel.ActiveCell.Value
            MyExcel.ActiveCell.Offset(0, 1).Activate()

            ThisRow.Value = MyExcel.ActiveCell.Value

            ExcelRowList.Add(ThisRow)
            MyExcel.ActiveCell.Offset(1, -6).Activate()

        Else
            Completed = True
            Exit Do
        End If

    Loop
    MyExcel.Workbooks.Close()
    MyExcel = Nothing

    Return Completed

End Function

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

    If GetInfo() = True Then

        For Each xItem In ExcelRowList

            Dim lViewItem As ListViewItem

            lViewItem = ListView1.Items.Add(xItem.Unit)
            lViewItem.SubItems.AddRange(New String() {xItem.Outlet, xItem.EPoS, xItem.Quantity, xItem.Value, xItem.DateSale})

        Next

    End If
End Sub

Answer 1

@Plutonix 的回答是个好主意，我完全同意他关于将 ExcelRows 结构/类字段从字符串更改为适当的数字类型的评论。

另一种可能性是使用 LINQ 对 ExcelRowList 中的数据进行分组。类似于以下内容

Dim results = From r In ExcelRowList
              Group By r.EPoS
              Into Group, Sum(r.Value * r.Quantity)

请注意，这不会按原样工作，因为 r.Value 和 r.Quantity 是字符串，因此您不能将它们相乘。因此，您应该更改结构中的字段类型，然后在循环单元格时，将单元格值转换为正确的类型。如果 Actor 阵容失败，您将不得不决定该怎么做。

Group By on MSDN .