—企業信息化探討之二
　　 　　　　 　屠恩海

　　 開發工具：Microsoft Visual Studio .NET 2003
　　 操作系統：Windows XP





　　　　　　　　　　　　　　　用EXCEL做企業生產報表的理由

　　Excel表格生成和公式設置十分強大便利，是一個強有力的信息分析與處理工具。特別是EXCEL的公式、函數、VBA語言，功能極其強大。我試用過其他電子表格軟件，在功能上和EXCEL根本沒有可比性。我為EXCEL的功能所折服，特別搜集整理了電子書——《EXCEL應用寶典》,放到網上供大家下載學習。欲助己，先助人，這也是我一貫的學習哲學。
　　Visual Studio .NET也同樣是MicroSoft的產品，Visual Studio .NET調用EXCEL做企業報表十分方便。
證明當時我選Visual Studio .NET作為首選開發工具是正確的。

　　


　　　　　　　　 　　　　　　　軟件構思


　　
　 軟件構思是這樣的：
　　先在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統計生產報表



　　以下是實現代碼，供參考。為方便初學者，部份地方加以注釋。

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 '第4列是城市名稱
　　　　　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,程序就自動統計出各個辦事處的及各個產品的有關計劃數量及未完成數量，是不是很方便。