VB.NET结合EXCEL统计生产报表

软件构思

　
　 软件构思是这样的：
　　先在EXCEL里定制好名为《统计表》的样表（模版），，在样表中设置好各种格式，填写好固定项。
　　在窗体上放很三个控件，两个DateTimePicker控件，用来选择开始统计时间和结束统计时间。一个Button以启动程序。
　　软件欲实现的功能是：点击Button1，自动查找符合日期符合日期范围的生产计划工作表，然后利用SortedList统计各个办事处的计划数量和未完成数量，及各个产品型号的计划数量和未完成数量。再把SortedList的数据读出写到《统计表》中。
　　这里要注意的是，各个生产报表格式必须规范统一，因为程序是按照固定单元格位置读取数据的。

　　　　　　　　　　　　　　　SortedList类

　　除了具备VB.NET调用EXCEL的基础知识外，本例主要用到SortedList类。
　　SortedList类表示键/值对的集合，这些键和值按键排序并可按照键和索引访问。
　　SortedList 是 Hashtable 和 Array 的混合。当使用 Item 索引器属性按照元素的键访问元素时，其行为类似于 Hashtable。当使用 GetByIndex 或 SetByIndex 按照元素的索引访问元素时，其行为类似于 Array。
　　SortedList 在内部维护两个数组以将数组存储到列表中；即，一个数组用于键，另一个数组用于相关联的值。每个元素都是一个可作为 DictionaryEntry 对象进行访问的键/值对。键不能为空引用（Visual Basic 中为 Nothing），但值可以。
　　SortedList 的容量是列表可拥有的元素数。随着向 SortedList 中添加元素，容量通过重新分配按需自动增加。可通过调用 TrimToSize 或通过显式设置 Capacity 属性减少容量。
　　SortedList 的元素将按照特定的 IComparer 实现（在创建 SortedList 时指定）或按照键本身提供的 IComparable 实现并依据键来进行排序。不论在哪种情况下，SortedList 都不允许重复键。
索引顺序基于排序顺序。当添加元素时，元素将按正确的排序顺序插入 SortedList，同时索引会相应地进行调整。若移除了元素，索引也会相应地进行调整。因此，当在 SortedList 中添加或移除元素时，特定键/值对的索引可能会更改。
　　由于要进行排序，所以在 SortedList 上操作比在 Hashtable 上操作要慢。但是，SortedList 允许通过相关联键或通过索引对值进行访问，可提供更大的灵活性。
此集合中的索引从零开始。
　　[Visual Basic, C#] C# 语言中的 foreach 语句（在 Visual Basic 中为 for each）需要集合中每个元素的类型。由于 SortedList 的每个元素都是一个键/值对，因此元素类型既不是键的类型，也不是值的类型。而是 DictionaryEntry 类型。例如：
　　Dim myDE As DictionaryEntry
　　For Each myDE In mySortedList
　　...
　　Next myDE

 　　　　　　　　　　　　　　　VB.NET结合EXCEL统计生产报表

　以下是实现代码，供参考。为方便初学者，tuenhai对部份地方加以注释。

Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
　DateTimePicker1.MaxDate = Date.Now
　DateTimePicker1.MinDate = #1/1/2004#
　DateTimePicker2.MaxDate = Date.Now
　DateTimePicker2.MinDate = #1/1/2004#
End Sub

 

Private Sub 灶具分析统计()

　Call killEXCEL()

　Dim excelApp As New Excel.Application
　Dim 佳尼2004灶具生产计划 As Excel.Workbook
　Dim 行号 As Integer = 1
　Dim 列号 As Integer = 1
　Dim 办事处计划数统计 As New SortedList
　Dim 办事处完成数统计 As New SortedList

　Dim 型号计划数统计 As New SortedList
　Dim 型号完成数统计 As New SortedList
　Dim 统计表 As Excel.Worksheet '

 

　Try
　　佳尼2004灶具生产计划 = excelApp.Workbooks.Open("E:my documents生产计划佳尼2004灶具生产计划.xls")
　　统计表 = CType(佳尼2004灶具生产计划.Worksheets("统计表"), Excel.Worksheet)
　　统计表.Cells(1, 3).value = DateTimePicker1.Value.ToShortDateString '统计开始日期
　　统计表.Cells(1, 7).value = DateTimePicker2.Value.ToShortDateString '统计结束日期
　　统计表.Range("c4:z6").Value = "" '先清空统计表中原来有关数据
　　统计表.Range("c9:z11").Value = "" '

　　Dim 生产计划表 As Excel.Worksheet
　　For Each 生产计划表 In 佳尼2004灶具生产计划.Worksheets '遍历生产计划表
　　　If Strings.Left(生产计划表.Name, 1) = "0" Or Strings.Left(生产计划表.Name, 1) = "1" Then '如果是0或1开头的表名，因生产计划表名是0或1开头
　　　'MsgBox(生产计划表.Name)
　　　行号 = 4 '生产计划表中生产数据从第四行开始
　　　列号 = 14 '第14列是计划下发日期。从计划下发日期判断是否是所要数据

　　　Dim 临时行号 As Integer = 4 '求得工作表中最后一行所在的行号，从第四行开始往下计算
　　　While 生产计划表.Cells(临时行号, 列号).value <> Nothing
　　　　'MsgBox(生产计划表.Cells(行号, 列号).value, , 行号)
　　　　临时行号 += 1
　　　End While
　　　临时行号 -= 1 '得到生产计划表中，最后数据行所在的行号
　　　' MsgBox(临时行号)

　　　　For 行号 = 4 To 临时行号 '生产计划标准格式行号从4开始，到工作表中最后一行

　　　　　If (CDate(DateTimePicker1.Value.ToShortDateString) <= CDate(生产计划表.Cells(行号, 列号).value)) And (CDate(DateTimePicker2.Value.ToShortDateString) >= CDate(生产计划表.Cells(行号, 列号).value)) Then '如果日期在规定范围内

　　　　　'灶具各城市分布情况
　　　　　Dim 城市 As String = 生产计划表.Cells(行号, 3).value '第3列是城市名称
　　　　　Dim 计划数 As Decimal = CType(生产计划表.Cells(行号, 7).value, Decimal) '第7列是计划数
　　　　　Dim 完成数 As Decimal = CType(生产计划表.Cells(行号, 11).value, Decimal) '第11列是实际完成数
　　　　　Dim 未完成数 As Decimal = 0 '用Decimal是因为后面要加小数
　　　　　　If 完成数 < 计划数 Then '如果没有完成
　　　　　　　未完成数 = 计划数 - 完成数
　　　　　　End If

　　　　　　If 城市 <> "" Then
　　　　　　　If InStr(城市, "沈阳") <> 0 Or InStr(城市, "鞍山") <> 0 Or InStr(城市, "哈尔滨") 　　　　　　　　　<> 0 Or InStr(城市, "葫芦岛") <> 0 Then
　　　　　　　　If 办事处计划数统计.Contains("沈阳") Then
　　　　　　　　　办事处计划数统计.Item("沈阳") += 计划数
　　　　　　　　Else
　　　　　　　　　办事处计划数统计.Add("沈阳", 计划数)
　　　　　　　　End If

　　　　　　　　If 办事处完成数统计.Contains("沈阳") Then
　　　　　　　　　办事处完成数统计.Item("沈阳") += 未完成数
　　　　　　　　Else
　　　　　　　　　办事处完成数统计.Add("沈阳", 未完成数)
　　　　　　　　End If

　　　　　　　Else　　
　　　　　　　　If 办事处计划数统计.Contains(城市) Then
　　　　　　　　　办事处计划数统计.Item(城市) += 计划数
　　　　　　　　Else
　　　　　　　　　办事处计划数统计.Add(城市, 计划数)
　　　　　　　　End If

　　　　　　　　If 办事处完成数统计.Contains(城市) Then
　　　　　　　　　办事处完成数统计.Item(城市) += 未完成数
　　　　　　　　Else
　　　　　　　　　办事处完成数统计.Add(城市, 未完成数)
　　　　　　　　End If

　　　　　　　End If
　　　　　　Else
　　　　　　　MsgBox(生产计划表.Name & "没有城市名称", MsgBoxStyle.Critical, "生产计划中要有城市名称")
　　　　　　　excelApp.Visible = True
　　　　　　　生产计划表.Activate()
　　　　　　　生产计划表.Select()
　　　　　　End If

 

　　　　　　'以下计算灶具型号分布情况
　　　　　　Dim 型号 As String = Strings.Left(生产计划表.Cells(行号, 2).value, 3) '灶具型号在第3列
　　　　　　If 型号计划数统计.Contains(型号) Then
　　　　　　　型号计划数统计.Item(型号) += 计划数
　　　　　　Else
　　　　　　　型号计划数统计.Add(型号, 计划数)
　　　　　　End If

　　　　　　If 型号完成数统计.Contains(型号) Then
　　　　　　　型号完成数统计.Item(型号) += 未完成数
　　　　　　Else
　　　　　　　型号完成数统计.Add(型号, 未完成数)
　　　　　　End If

　　　　　End If
　　　　Next

　　　End If

　　Next

　　Dim 城市数 As Integer = 办事处计划数统计.Count
　　Dim 城市数clone As Integer = 办事处计划数统计.Count

　　行号 = 4

　　'依计划数大小排序
　　Dim 办事处计划数统计副本 As New SortedList
　　Dim asa As Decimal = 0.001 '加上此数是为了防止键值的重复，在工作表中可选不显示小数
　　Dim 办事处计划数Enum As IDictionaryEnumerator = 办事处计划数统计.GetEnumerator
　　While 办事处计划数Enum.MoveNext
　　　办事处计划数统计副本.Add(办事处计划数Enum.Value + asa, 办事处计划数Enum.Key)
　　　asa += 0.001
　　End While

　　'这里利用SortedList自动排序的功能
　　Dim i As Integer = 1
　　For 列号 = 3 To 3 + 城市数 - 1 '从第三列开始填写数据，这是预先定义的格式
　　　统计表.Cells(行号, 列号).value = 办事处计划数统计副本.GetByIndex(城市数clone - i) '城市
　　　统计表.Cells(行号 + 1, 列号).value = 办事处计划数统计副本.GetKey(城市数clone - i) '计划数
　　　统计表.Cells(行号 + 2, 列号).value = 办事处完成数统计.Item(办事处计划数统计副本　.GetByIndex(城市数clone - i)) '计划数
　　　i += 1
　　Next

　　'以型号计划数多少排序
　　Dim 型号计划数统计副本 As New SortedList
　　办事处计划数Enum = 型号计划数统计.GetEnumerator
　　While 办事处计划数Enum.MoveNext
　　　型号计划数统计副本.Add(办事处计划数Enum.Value + asa, 办事处计划数Enum.Key)
　　　asa += 0.001
　　　' MsgBox(办事处计划数Enum.Value + asa, , 办事处计划数Enum.Key)
　　End While

　　Dim 型号数 As Integer = 型号计划数统计.Count
　　Dim 型号数clone As Integer = 型号计划数统计.Count
　　'MsgBox(型号数)
　　i = 1
　　行号 = 9
　　For 列号 = 3 To 3 + 型号数 - 1
　　　统计表.Cells(行号, 列号).value = 型号计划数统计副本.GetByIndex(型号数clone - i) '灶具型号
　　　统计表.Cells(行号 + 1, 列号).value = 型号计划数统计副本.GetKey(型号数clone - i) '计划数
　　　统计表.Cells(行号 + 2, 列号).value = 型号完成数统计.Item(型号计划数统计副本　　.GetByIndex(型号数clone - i))
　　　i += 1
　　Next

　　' Dim WSf As Excel.WorksheetFunction = excelApp.WorksheetFunction
　　excelApp.Visible = True '显示工作簿
　　统计表.Select() '选择工作表
　　统计表.Activate() '激活工作表

　　Catch ex As Exception '捕捉错误

　　　办事处计划数统计 = Nothing
　　　办事处完成数统计 = Nothing
　　　型号计划数统计 = Nothing
　　　型号完成数统计 = Nothing
　　　统计表 = Nothing
　　　佳尼2004灶具生产计划 = Nothing
　　　excelApp = Nothing
　　　GC.Collect(0)
　　　MsgBox(ex.ToString, MsgBoxStyle.Critical, "出现错误了")
　　Finally
　　　办事处计划数统计 = Nothing
　　　办事处完成数统计 = Nothing
　　　型号计划数统计 = Nothing
　　　型号完成数统计 = Nothing
　　　统计表 = Nothing
　　　佳尼2004灶具生产计划 = Nothing
　　　excelApp = Nothing
　　　GC.Collect(0)
　　End Try
　　MsgBox("已经统计好了,请查看", MsgBoxStyle.DefaultButton1, "灶具生产计划统计") '如果完成就　　　　提示
End Sub

　　现在让我们来试用一下，先选择开始统计时间和结束统计时间，点击Button1,程序就自动统计出各个办事处的及各个产品的有关计划数量及未完成数量，是不是很方便。