—企業(yè)信息化探討之二
屠恩海
開發(fā)工具：microsoft visual studio .net 2003
操作系統(tǒng)：windows xp
用excel做企業(yè)生產(chǎn)報表的理由
excel表格生成和公式設(shè)置十分強(qiáng)大便利，是一個強(qiáng)有力的信息分析與處理工具。特別是excel的公式、函數(shù)、vba語言，功能極其強(qiáng)大。我試用過其他電子表格軟件，在功能上和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ù)量，是不是很方便。
