如何使用带有Excel工作表的VB.net来计算和排序数据 [英] How to use VB.net with an Excel sheet to count and sort data
问题描述
就像标题所说的那样，我一直在使用VB.NET在Excel上做各种各样的事情并且有点新鲜。我无法真正地在vb.net中做这样的事情，这令人沮丧。如果我有一张Excel表格，其中包含各种数据，包括工作人员和销售的东西，我如何选择2列并对它们进行排序，以便计算每个人在每个消息箱或列表框中销售的物品数量？
例如，我正在寻找的输出类似于
员工已售出多少
NAME1 - PRODUCT1 - AMOUNTSOLDBYNAME1
NAME1 - PRODUCT2 - AMOUNTSOLDBYNAME1
NAME1 - PRODUCT3 - AMOUNTSOLDBYNAME1
NAME1 - PRODUCT4 - AMOUNTSOLDBYNAME1
NAME2 - PRODUCT1 - AMOUNTSOLDBYNAME2
NAME2 - PRODUCT2 - AMOUNTSOLDBYNAME2
等......
我得到的最远的是计算1列中每位员工的数量，但我想更进一步，获得2列并计算每个人销售的每种产品，但目前还不清楚去吧。
Pri vate Sub getexcelfile_Click（ ByVal sender As 系统。对象， ByVal e As System.EventArgs）句柄 Button3.Click
Dim excelfile 作为 新 OpenFileDialog（）
excelfile.ShowDialog（）
如果（excelfile.ShowDialog（）= DialogResult.Cancel）然后
返回
其他
Dim 文件 As 字符串 = excelfile.FileName
Dim xlApp 作为 Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet
xlApp = 新 Excel.Application
xlWorkBook = xlApp.Workbooks.Open（file）
xlWorkSheet = xlWorkBook.Worksheets（ PSSalesFullConnectionReport）
Dim col As String = N
对于行作为 整数 = 1 xlWorkSheet.UsedRange.Rows.Count - 1
Dim elemValue As 字符串 = xlWorkSheet.Range（col& row）.Text
ListBox1.Items.Add（elemValue）
Next
MessageBox.Show（ReturnDuplicateListBoxItems（ListBox1） ））
ListBox1.Items.Clear（）
结束 如果
结束 Sub
公共 共享 功能 ReturnDuplicateListBoxItems（ ByVal lBox As System.Windows.Forms.ListBox） As 字符串
Dim strReturn As 新 System.Text.StringBuilder
Dim lItems 作为 新字典（ 字符串， 整数）
Dim intCount As 整数 = 0
昏暗 strCurrentItem As String = String .Empty
尝试
' 循环列表框抓取项目......
对于 每个 nItem < span class =code-keyword> As String In lBox.Items
如果 不（lItems.ContainsKey（nItem））然后 ' 如果不在列表中，则将列表框项添加到字典中...
' 我们正在查看的当前项目......
strCurrentItem = nItem
' 检查引用列表框中此项目的出现次数...
对于 每个 sItem 作为 字符串 在 lBox.Items
如果 sItem.Equals（strCurrentItem）那么 我们有一个匹配添加到count ...
intCount + = 1
结束 < span class =code-keyword>如果
下一步
' 最后将项目添加到字典中，项目计数...
lItems.Add（nItem，intCount）
' 重置下一项的intCount ...和strCurrentItem
intCount = 0
strCurrentItem = 字符串 .Empty
结束 如果
下一步
' 添加到字符串构建r ...
对于 i 作为 整数 = 0 lItems.Count - 1
strReturn.AppendLine（lItems.Keys（i）.ToString& - & lItems.Values（i）.ToString）
下一步
Catch ex As 异常
返回 strReturn.ToString
结束 尝试
返回 strReturn.ToString
结束 功能
对此的任何指导都会帮助我很多
< blockquote>如果我看到它，你只能通过直接分配访问N列。
你需要做的是：
- 你建立了一个计算列号到字母的方法，因为Range等待总是一个字母和一个数字。
- 你访问的单元格单元格而不是范围。如果您使用.Cells，您可以按编号给出两个坐标...
（我更喜欢这种方式...;））
Just like the title says, I've been using VB.NET to do various things on Excel and am kinda new. I can't really wrap my head around doing something like this in vb.net and it's frustrating. If i have an Excel sheet with various data including staff and things sold, how can i select 2 Columns and sort them so it counts how many of each items were sold by each person into a Messagebox or Listbox?
for eg, the output i'm looking for is something like
Staff Sold how many
NAME1 - PRODUCT1 - AMOUNTSOLDBYNAME1
NAME1 - PRODUCT2 - AMOUNTSOLDBYNAME1
NAME1 - PRODUCT3 - AMOUNTSOLDBYNAME1
NAME1 - PRODUCT4 - AMOUNTSOLDBYNAME1
NAME2 - PRODUCT1 - AMOUNTSOLDBYNAME2
NAME2 - PRODUCT2 - AMOUNTSOLDBYNAME2
and so on...
the furthest i've gotten is counting how many of each staff member there are in a 1 column but i would like to go a step further on this and get 2 columns and count each product sold by each person but am unclear how to go about it.
Private Sub getexcelfile_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
Dim excelfile As New OpenFileDialog()
excelfile.ShowDialog()
If (excelfile.ShowDialog() = DialogResult.Cancel) Then
Return
Else
Dim file As String = excelfile.FileName
Dim xlApp As Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet
xlApp = New Excel.Application
xlWorkBook = xlApp.Workbooks.Open(file)
xlWorkSheet = xlWorkBook.Worksheets("PSSalesFullConnectionReport")
Dim col As String = "N"
For row As Integer = 1 To xlWorkSheet.UsedRange.Rows.Count - 1
Dim elemValue As String = xlWorkSheet.Range(col & row).Text
ListBox1.Items.Add(elemValue)
Next
MessageBox.Show(ReturnDuplicateListBoxItems(ListBox1))
ListBox1.Items.Clear()
End If
End Sub
Public Shared Function ReturnDuplicateListBoxItems(ByVal lBox As System.Windows.Forms.ListBox) As String
Dim strReturn As New System.Text.StringBuilder
Dim lItems As New Dictionary(Of String, Integer)
Dim intCount As Integer = 0
Dim strCurrentItem As String = String.Empty
Try
'Loop through listbox grabbing items...
For Each nItem As String In lBox.Items
If Not (lItems.ContainsKey(nItem)) Then 'Add listbox item to dictionary if not in there...
'The current item we are looking at...
strCurrentItem = nItem
'Check how many occurances of this items there are in the referenced listbox...
For Each sItem As String In lBox.Items
If sItem.Equals(strCurrentItem) Then 'We have a match add to the count...
intCount += 1
End If
Next
'Finally add the item to the dictionary with the items count...
lItems.Add(nItem, intCount)
'Reset intCount for next item... and strCurrentItem
intCount = 0
strCurrentItem = String.Empty
End If
Next
'Add to the string builder...
For i As Integer = 0 To lItems.Count - 1
strReturn.AppendLine(lItems.Keys(i).ToString & " - " & lItems.Values(i).ToString)
Next
Catch ex As Exception
Return strReturn.ToString
End Try
Return strReturn.ToString
End Function
any guidance on this would help me alot
If I saw it right, you only access the column "N" by direct assignment.
What you need to do could be :
- you build up a method which calculates the column-number to a letter, because Range awaits always a letter and a number.
- you access the cells of the sheet by Cells instead of Range. If you use .Cells you can give both coordinates by number ...
(I would prefer this way ... ;) )
这篇关于如何使用带有Excel工作表的VB.net来计算和排序数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
