—企業(yè)信息化探討之二
　　 　　　　 　屠恩海

　　 開發(fā)工具：Microsoft Visual Studio .NET 2003
　　 操作系統(tǒng)：Windows XP





　　　　　　　　　　　　　　　用EXCEL做企業(yè)生產(chǎn)報表的理由

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

　　


　　　　　　　　 　　　　　　　軟件構(gòu)思


　　
　 軟件構(gòu)思是這樣的：
　　先在EXCEL里定制好名為《統(tǒng)計表》的樣表（模版），，在樣表中設(shè)置好各種格式，填寫好固定項。
　　在窗體上放很三個控件，兩個DateTimePicker控件，用來選擇開始統(tǒng)計時間和結(jié)束統(tǒng)計時間。一個Button以啟動程序。
　　軟件欲實現(xiàn)的功能是：點擊Button1，自動查找符合日期符合日期范圍的生產(chǎn)計劃工作表，然后利用SortedList統(tǒng)計各個辦事處的計劃數(shù)量和未完成數(shù)量，及各個產(chǎn)品型號的計劃數(shù)量和未完成數(shù)量。再把SortedList的數(shù)據(jù)讀出寫到《統(tǒng)計表》中。
　　這里要注意的是，各個生產(chǎn)報表格式必須規(guī)范統(tǒng)一，因為程序是按照固定單元格位置讀取數(shù)據(jù)的。





　　　　　　　　　　　　　　　　SortedList類

　　除了具備VB.NET調(diào)用EXCEL的基礎(chǔ)知識外，本例主要用到SortedList類。
　　SortedList類表示鍵/值對的集合，這些鍵和值按鍵排序并可按照鍵和索引訪問。
　　SortedList 是 Hashtable 和 Array 的混合。當(dāng)使用 Item 索引器屬性按照元素的鍵訪問元素時，其行為類似于 Hashtable。當(dāng)使用 GetByIndex 或 SetByIndex 按照元素的索引訪問元素時，其行為類似于 Array。
　　SortedList 在內(nèi)部維護(hù)兩個數(shù)組以將數(shù)組存儲到列表中；即，一個數(shù)組用于鍵，另一個數(shù)組用于相關(guān)聯(lián)的值。每個元素都是一個可作為 DictionaryEntry 對象進(jìn)行訪問的鍵/值對。鍵不能為空引用（Visual Basic 中為 Nothing），但值可以。
　　SortedList 的容量是列表可擁有的元素數(shù)。隨著向 SortedList 中添加元素，容量通過重新分配按需自動增加�？赏ㄟ^調(diào)用 TrimToSize 或通過顯式設(shè)置 Capacity 屬性減少容量。
　　SortedList 的元素將按照特定的 IComparer 實現(xiàn)（在創(chuàng)建 SortedList 時指定）或按照鍵本身提供的 IComparable 實現(xiàn)并依據(jù)鍵來進(jìn)行排序。不論在哪種情況下，SortedList 都不允許重復(fù)鍵。
索引順序基于排序順序。當(dāng)添加元素時，元素將按正確的排序順序插入 SortedList，同時索引會相應(yīng)地進(jìn)行調(diào)整。若移除了元素，索引也會相應(yīng)地進(jìn)行調(diào)整。因此，當(dāng)在 SortedList 中添加或移除元素時，特定鍵/值對的索引可能會更改。
　　由于要進(jìn)行排序，所以在 SortedList 上操作比在 Hashtable 上操作要慢。但是，SortedList 允許通過相關(guān)聯(lián)鍵或通過索引對值進(jìn)行訪問，可提供更大的靈活性。
此集合中的索引從零開始。
　　[Visual Basic, C#] C# 語言中的 foreach 語句（在 Visual Basic 中為 for each）需要集合中每個元素的類型。由于 SortedList 的每個元素都是一個鍵/值對，因此元素類型既不是鍵的類型，也不是值的類型。而是 DictionaryEntry 類型。例如：
　　Dim myDE As DictionaryEntry
　　For Each myDE In mySortedList
　　...
　　Next myDE





　　　　　　　　　　　　　　　　VB.NET結(jié)合EXCEL統(tǒng)計生產(chǎn)報表



　　以下是實現(xiàn)代碼，供參考。為方便初學(xué)者，部份地方加以注釋。

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 灶具分析統(tǒng)計()
　Call killEXCEL()

　Dim excelApp As New Excel.Application
　Dim 佳尼2004灶具生產(chǎn)計劃 As Excel.Workbook
　Dim 行號 As Integer = 1
　Dim 列號 As Integer = 1
　Dim 辦事處計劃數(shù)統(tǒng)計 As New SortedList
　Dim 辦事處完成數(shù)統(tǒng)計 As New SortedList

　Dim 型號計劃數(shù)統(tǒng)計 As New SortedList
　Dim 型號完成數(shù)統(tǒng)計 As New SortedList
　Dim 統(tǒng)計表 As Excel.Worksheet '


　Try
　　佳尼2004灶具生產(chǎn)計劃 = excelApp.Workbooks.Open("E:\my documents\生產(chǎn)計劃\佳尼2004灶具生產(chǎn)計劃.xls")
　　統(tǒng)計表 = CType(佳尼2004灶具生產(chǎn)計劃.Worksheets("統(tǒng)計表"), Excel.Worksheet)
　　統(tǒng)計表.Cells(1, 3).value = DateTimePicker1.Value.ToShortDateString '統(tǒng)計開始日期
　　統(tǒng)計表.Cells(1, 7).value = DateTimePicker2.Value.ToShortDateString '統(tǒng)計結(jié)束日期
　　統(tǒng)計表.Range("c4:z6").Value = "" '先清空統(tǒng)計表中原來有關(guān)數(shù)據(jù)
　　統(tǒng)計表.Range("c9:z11").Value = "" '


　　Dim 生產(chǎn)計劃表 As Excel.Worksheet
　　For Each 生產(chǎn)計劃表 In 佳尼2004灶具生產(chǎn)計劃.Worksheets '遍歷生產(chǎn)計劃表
　　　If Strings.Left(生產(chǎn)計劃表.Name, 1) = "0" Or Strings.Left(生產(chǎn)計劃表.Name, 1) = "1" Then '如果是0或1開頭的表名，因生產(chǎn)計劃表名是0或1開頭
　　　'MsgBox(生產(chǎn)計劃表.Name)
　　　行號 = 4 '生產(chǎn)計劃表中生產(chǎn)數(shù)據(jù)從第四行開始
　　　列號 = 14 '第14列是計劃下發(fā)日期。從計劃下發(fā)日期判斷是否是所要數(shù)據(jù)


　　　Dim 臨時行號 As Integer = 4 '求得工作表中最后一行所在的行號，從第四行開始往下計劃
　　　While 生產(chǎn)計劃表.Cells(臨時行號, 列號).value <> Nothing
　　　　'MsgBox(生產(chǎn)計劃表.Cells(行號, 列號).value, , 行號)
　　　　臨時行號 += 1
　　　End While
　　　臨時行號 -= 1 '得到生產(chǎn)計劃表中，最后數(shù)據(jù)行所在的行號
　　　' MsgBox(臨時行號)

　　　　For 行號 = 4 To 臨時行號 '生產(chǎn)計劃標(biāo)準(zhǔn)格式行號從4開始，到工作表中最后一行

　　　　　If (CDate(DateTimePicker1.Value.ToShortDateString) <= CDate(生產(chǎn)計劃表.Cells(行號, 列號).value)) And (CDate(DateTimePicker2.Value.ToShortDateString) >= CDate(生產(chǎn)計劃表.Cells(行號, 列號).value)) Then '如果日期在規(guī)定范圍內(nèi)

　　　　　'灶具各城市分布情況
　　　　　Dim 城市 As String = 生產(chǎn)計劃表.Cells(行號, 3).value '第4列是城市名稱
　　　　　Dim 計劃數(shù) As Decimal = CType(生產(chǎn)計劃表.Cells(行號, 7).value, Decimal) '第7列是計劃數(shù)
　　　　　Dim 完成數(shù) As Decimal = CType(生產(chǎn)計劃表.Cells(行號, 11).value, Decimal) '第11列是實際完成數(shù)
　　　　　Dim 未完成數(shù) As Decimal = 0 '用Decimal是因為后面要加小數(shù)
　　　　　　If 完成數(shù) < 計劃數(shù) Then '如果沒有完成
　　　　　　　未完成數(shù) = 計劃數(shù) - 完成數(shù)
　　　　　　End If

　　　　　　If 城市 <> "" Then
　　　　　　　If InStr(城市, "沈陽") <> 0 Or InStr(城市, "鞍山") <> 0 Or InStr(城市, "哈爾濱") 　　　　　　　　　<> 0 Or InStr(城市, "葫蘆島") <> 0 Then
　　　　　　　　If 辦事處計劃數(shù)統(tǒng)計.Contains("沈陽") Then
　　　　　　　　　辦事處計劃數(shù)統(tǒng)計.Item("沈陽") += 計劃數(shù)
　　　　　　　　Else
　　　　　　　　　辦事處計劃數(shù)統(tǒng)計.Add("沈陽", 計劃數(shù))
　　　　　　　　End If

　　　　　　　　If 辦事處完成數(shù)統(tǒng)計.Contains("沈陽") Then
　　　　　　　　　辦事處完成數(shù)統(tǒng)計.Item("沈陽") += 未完成數(shù)
　　　　　　　　Else
　　　　　　　　　辦事處完成數(shù)統(tǒng)計.Add("沈陽", 未完成數(shù))
　　　　　　　　End If


　　　　　　　Else　　
　　　　　　　　If 辦事處計劃數(shù)統(tǒng)計.Contains(城市) Then
　　　　　　　　　辦事處計劃數(shù)統(tǒng)計.Item(城市) += 計劃數(shù)
　　　　　　　　Else
　　　　　　　　　辦事處計劃數(shù)統(tǒng)計.Add(城市, 計劃數(shù))
　　　　　　　　End If


　　　　　　　　If 辦事處完成數(shù)統(tǒng)計.Contains(城市) Then
　　　　　　　　　辦事處完成數(shù)統(tǒng)計.Item(城市) += 未完成數(shù)
　　　　　　　　Else
　　　　　　　　　辦事處完成數(shù)統(tǒng)計.Add(城市, 未完成數(shù))
　　　　　　　　End If

　　　　　　　End If
　　　　　　Else
　　　　　　　MsgBox(生產(chǎn)計劃表.Name & "沒有城市名稱", MsgBoxStyle.Critical, "生產(chǎn)計劃中要有城市名稱")
　　　　　　　excelApp.Visible = True
　　　　　　　生產(chǎn)計劃表.Activate()
　　　　　　　生產(chǎn)計劃表.Select()
　　　　　　End If



　　　　　　'以下計算灶具型號分布情況
　　　　　　Dim 型號 As String = Strings.Left(生產(chǎn)計劃表.Cells(行號, 2).value, 3) '灶具型號在第3列
　　　　　　If 型號計劃數(shù)統(tǒng)計.Contains(型號) Then
　　　　　　　型號計劃數(shù)統(tǒng)計.Item(型號) += 計劃數(shù)
　　　　　　Else
　　　　　　　型號計劃數(shù)統(tǒng)計.Add(型號, 計劃數(shù))
　　　　　　End If


　　　　　　If 型號完成數(shù)統(tǒng)計.Contains(型號) Then
　　　　　　　型號完成數(shù)統(tǒng)計.Item(型號) += 未完成數(shù)
　　　　　　Else
　　　　　　　型號完成數(shù)統(tǒng)計.Add(型號, 未完成數(shù))
　　　　　　End If

　　　　　End If
　　　　Next

　　　End If

　　Next

　　Dim 城市數(shù) As Integer = 辦事處計劃數(shù)統(tǒng)計.Count
　　Dim 城市數(shù)clone As Integer = 辦事處計劃數(shù)統(tǒng)計.Count

　　行號 = 4

　　'依計劃數(shù)大小排序
　　Dim 辦事處計劃數(shù)統(tǒng)計副本 As New SortedList
　　Dim asa As Decimal = 0.001 '加上此數(shù)是為了防止鍵值的重復(fù)，在工作表中可選不顯示小數(shù)
　　Dim 辦事處計劃數(shù)Enum As IDictionaryEnumerator = 辦事處計劃數(shù)統(tǒng)計.GetEnumerator
　　While 辦事處計劃數(shù)Enum.MoveNext
　　　辦事處計劃數(shù)統(tǒng)計副本.Add(辦事處計劃數(shù)Enum.Value + asa, 辦事處計劃數(shù)Enum.Key)
　　　asa += 0.001
　　End While


　　'這里利用SortedList自動排序的功能
　　Dim i As Integer = 1
　　For 列號 = 3 To 3 + 城市數(shù) - 1 '從第三列開始填寫數(shù)據(jù)，這是預(yù)先定義的格式
　　　統(tǒng)計表.Cells(行號, 列號).value = 辦事處計劃數(shù)統(tǒng)計副本.GetByIndex(城市數(shù)clone - i) '城市
　　　統(tǒng)計表.Cells(行號 + 1, 列號).value = 辦事處計劃數(shù)統(tǒng)計副本.GetKey(城市數(shù)clone - i) '計劃數(shù)
　　　統(tǒng)計表.Cells(行號 + 2, 列號).value = 辦事處完成數(shù)統(tǒng)計.Item(辦事處計劃數(shù)統(tǒng)計副本　.GetByIndex(城市數(shù)clone - i)) '計劃數(shù)
　　　i += 1
　　Next


　　'以型號計劃數(shù)多少排序
　　Dim 型號計劃數(shù)統(tǒng)計副本 As New SortedList
　　辦事處計劃數(shù)Enum = 型號計劃數(shù)統(tǒng)計.GetEnumerator
　　While 辦事處計劃數(shù)Enum.MoveNext
　　　型號計劃數(shù)統(tǒng)計副本.Add(辦事處計劃數(shù)Enum.Value + asa, 辦事處計劃數(shù)Enum.Key)
　　　asa += 0.001
　　　' MsgBox(辦事處計劃數(shù)Enum.Value + asa, , 辦事處計劃數(shù)Enum.Key)
　　End While

　　Dim 型號數(shù) As Integer = 型號計劃數(shù)統(tǒng)計.Count
　　Dim 型號數(shù)clone As Integer = 型號計劃數(shù)統(tǒng)計.Count
　　'MsgBox(型號數(shù))
　　i = 1
　　行號 = 9
　　For 列號 = 3 To 3 + 型號數(shù) - 1
　　　統(tǒng)計表.Cells(行號, 列號).value = 型號計劃數(shù)統(tǒng)計副本.GetByIndex(型號數(shù)clone - i) '灶具型號
　　　統(tǒng)計表.Cells(行號 + 1, 列號).value = 型號計劃數(shù)統(tǒng)計副本.GetKey(型號數(shù)clone - i) '計劃數(shù)
　　　統(tǒng)計表.Cells(行號 + 2, 列號).value = 型號完成數(shù)統(tǒng)計.Item(型號計劃數(shù)統(tǒng)計副本　　.GetByIndex(型號數(shù)clone - i))
　　　i += 1
　　Next

　　' Dim WSf As Excel.WorksheetFunction = excelApp.WorksheetFunction
　　excelApp.Visible = True '顯示工作簿
　　統(tǒng)計表.Select() '選擇工作表
　　統(tǒng)計表.Activate() '激活工作表


　　Catch ex As Exception '捕捉錯誤

　　　辦事處計劃數(shù)統(tǒng)計 = Nothing
　　　辦事處完成數(shù)統(tǒng)計 = Nothing
　　　型號計劃數(shù)統(tǒng)計 = Nothing
　　　型號完成數(shù)統(tǒng)計 = Nothing
　　　統(tǒng)計表 = Nothing
　　　佳尼2004灶具生產(chǎn)計劃 = Nothing
　　　excelApp = Nothing
　　　GC.Collect(0)
　　　MsgBox(ex.ToString, MsgBoxStyle.Critical, "出現(xiàn)錯誤了")
　　Finally
　　　辦事處計劃數(shù)統(tǒng)計 = Nothing
　　　辦事處完成數(shù)統(tǒng)計 = Nothing
　　　型號計劃數(shù)統(tǒng)計 = Nothing
　　　型號完成數(shù)統(tǒng)計 = Nothing
　　　統(tǒng)計表 = Nothing
　　　佳尼2004灶具生產(chǎn)計劃 = Nothing
　　　excelApp = Nothing
　　　GC.Collect(0)
　　End Try
　　MsgBox("已經(jīng)統(tǒng)計好了,請查看", MsgBoxStyle.DefaultButton1, "灶具生產(chǎn)計劃統(tǒng)計") '如果完成就　　　　提示
End Sub


　　現(xiàn)在讓我們來試用一下，先選擇開始統(tǒng)計時間和結(jié)束統(tǒng)計時間，點擊Button1,程序就自動統(tǒng)計出各個辦事處的及各個產(chǎn)品的有關(guān)計劃數(shù)量及未完成數(shù)量，是不是很方便。