ＶＢ．ＮＥＴ與ＥＸＣＥＬ進(jìn)行數(shù)據(jù)處理的實現(xiàn)

摘要：通過對當(dāng)前Excel軟件的使用情況的調(diào)查、統(tǒng)計發(fā)現(xiàn)Excel對一些對有特殊要求的報表，Excel很難做到，比如對報表數(shù)據(jù)的自動處理上，調(diào)用Excel 內(nèi)部函數(shù)或者編輯一些函數(shù)有時非常麻煩。在VB.net 進(jìn)行數(shù)據(jù)處理中又發(fā)現(xiàn)VB.net在對單據(jù)輸出界面方面也有很大局限，VB.net與EXCEL的整合進(jìn)行數(shù)據(jù)處理，充分利用兩種軟件的特長，解決了單方面使用的不足。

關(guān)鍵詞：VB.net；EXCEL；工作簿；宏；元素；集合；SortedList類；Array數(shù)組

中圖分類號：TP311文獻(xiàn)標(biāo)識碼：A 文章編號：1009-3044(2008)30-0628-02

VB.NET and EXCEL Carry on the Data Processing the Realization

YIN Chuan-guang

(The Suzhou Ore Constructs The Middle School， Suzhou 234000，China)

Abstract: Through to the current Excel software service condition investigation， the statistics discovered Excel to has the special request report form to some， Excel is very difficult to achieve， sometimes for instance to the report form data automatic reduction in， transfers the Excel built-in function or edits some functions extremely to trouble. Carries on in the data processing in VB.net to discover VB.net in also has the very big limitation to the documentary evidence output contact surface aspect， VB.net and the EXCEL conformity carries on the data processing， uses two kind of softwares fully the special skills， has solved the insufficiency which uses unilaterally.

Key words: VB.net; EXCEL; work book; great; element; set; SortedList kind of; Array array

1 引言

Excel的表格生成和公式十分強(qiáng)大，是一個強(qiáng)有力的集息分析、加工、處理的工具，特別是EXCEL內(nèi)置的VBA語言功能可滿足各種用戶的特殊要求。而Visual.net同樣是Microsoft的產(chǎn)品。VB.net是它的集成開發(fā)環(huán)境中一部分。VB.net比起以前的VB版本功能更加強(qiáng)大，已經(jīng)是一個實實在在的面向?qū)ο蟮拈_發(fā)語言環(huán)境。它對數(shù)據(jù)的處理十分強(qiáng)大，可以處理各種數(shù)據(jù)類型。在數(shù)據(jù)的處理上利用VB.net作為開發(fā)工具應(yīng)是一個不錯的選擇。利用VB.NET開發(fā)前臺應(yīng)用程序，把數(shù)據(jù)的輸出交給EXCEL應(yīng)是一個完美的結(jié)合。通過VB.NET產(chǎn)生可以脫離開發(fā)環(huán)境的應(yīng)用程序可以移植到其它機(jī)器上，即使機(jī)器上沒有安裝Office 套裝軟件也不影響使用。更好地提高了數(shù)據(jù)的安全性。本文從以下幾個方面論述VB.net與Excel的結(jié)合應(yīng)用。

2 VB.NET中所用到的類

在VB.net中主要要用到SortedList類，它表示鍵/值對的集合。這些鍵/值按鍵排序并執(zhí)照鍵進(jìn)行索引訪問。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)行訪問的鍵/值對。鍵不能為空引用，但是值可以。SortedList的容量是列表可擁有的元素數(shù)。不斷地向SortedList中添加元素，容量通過重新分配按需要自動增加。可通過調(diào)用TrimToSize或通過顯示設(shè)置Capacity的屬性減少容量。SortedList的元素將按照特定ICompare實現(xiàn)或按照鍵本身提供的ICompare實現(xiàn)并依據(jù)鍵來排序。但是要注意的是，SortedList不準(zhǔn)許重復(fù)鍵。索引順序基于排序順序。當(dāng)添加元素時，元素將按照正確的排序順序插入SortedList，同時索引會相應(yīng)地進(jìn)行調(diào)整。如果移除了元素，索引也會相應(yīng)地進(jìn)行調(diào)整。因此，當(dāng)在SortedList中添加或移除元素時，特定的鍵/值對的索引可能會改變。事實上在SortedList上操作北在Hashtable上操作要慢。但是，SortedList準(zhǔn)許通過相關(guān)聯(lián)鍵或通過索引對值進(jìn)行訪問，靈活性會得到提高。

3 VB.net調(diào)用EXCEL

在實際使用過程中可以通過VB.net的自動化化功能來調(diào)用EXCEL工作表，主要是啟動和關(guān)閉EXCEL的宏命令。EXCEL本身提供一Visual Basic編輯器，打開Visual Basic編輯器，其中有一個工程屬性窗口，點擊右鍵菜單的“插入模塊”，則增加一“模塊１”（也可重新命名）在此模塊中可以運用Visual Basic語言編寫函數(shù)和過程并稱為宏。其中EXCEL有兩個自動宏：一個是啟動宏（Sub Auto_Open()）另一是關(guān)閉宏（Sub Auto_Close()）它們的特點是：當(dāng)用Excel打開含有啟動宏的工作簿時就會自動運行啟動宏，同理，當(dāng)關(guān)閉含有關(guān)閉宏的工作簿的就會自動運行關(guān)閉宏。在VB.net中通過對應(yīng)的命令XLBook.RunAutoMacros(xlAutoOpen)和XLBook.RunAutoMacros(xlAutoClose)來運行啟動和關(guān)閉宏。利用這個特性就可以實現(xiàn)VB.net和Excel之間的相互通信，在Excel的啟動宏中加入一段程序，其功能就是在磁盤中寫一標(biāo)志文件，同時在關(guān)閉宏中加入一段刪除此標(biāo)志文件的程序。VB.net在執(zhí)行時通過判斷此標(biāo)志文件存在與否來判斷Excel是否打開，如果標(biāo)志文件在，表明Excel對象正在運行，否則VB.net判斷Excel對象已被用戶關(guān)閉，如果要使用Excel對象運行，可用VB.net重新創(chuàng)建Excel對象。下面是一個打開和關(guān)閉Excel的一個例程。

在VB.net中建立一FORM 窗口，在上面放上兩個命令按鈕，將Button1 的Caption 屬性改為Excel，Button2的Caption 屬改為“結(jié)束”。在其中下面的程序：

Dim xlApp As Excel.Application '定義EXCEL類

Dim xlBook As Excel.Workbook '定義工件簿類

Dim xlsheet As Excel.Worksheet '定義工作表類

Private Sub Button1_Click(ByVal sender As System.Object， ByVal e As System.EventArgs) Handles Button1.Click

If Dir(\"D:\emp\\excel.bz\") = \"\" Then '判斷EXCEL是否打開

Set xlApp = CreateObject(\"Excel.Application\") '創(chuàng)建EXCEL應(yīng)用類

xlApp.Visible = True '設(shè)置EXCEL可見

Set xlBook = xlApp.Workbooks.Open(\"D:\emp\\bb.xls\") '打開EXCEL工作簿

Set xlsheet = xlBook.Worksheets(1) '打開EXCEL工作表

xlsheet.Activate '激活工作表

xlsheet.Cells(1， 1) = \"abc\" '給單元格1行列賦值

xlBook.RunAutoMacros (xlAutoOpen) 運行EXCEL中的啟動宏

Else

MsgBox (\"EXCEL已打開\")

End If

End Sub

Private Sub Button2Click(ByVal sender As System.Object， ByVal e As System.EventArgs) Handles Button1.Click

If Dir(\"D:\emp\\excel.bz\") <> \"\" Then '由VB關(guān)閉EXCEL

xlBook.RunAutoMacros (xlAutoClose) '執(zhí)行EXCEL關(guān)閉宏

xlBook.Close (True) '關(guān)閉EXCEL工作簿

xlApp.Quit '關(guān)閉EXCEL

End If

Set xlApp = Nothing '釋放EXCEL對象

End

End Sub

在\"bb.xls\"中打開Visual Basic編輯器，在工程窗口中點鼠標(biāo)鍵選擇插入模塊，在模塊中輸入入下程序存盤：

Sub auto_open()

Open \"d:\emp\\excel.bz\" For Output As #1 '寫標(biāo)志文件

Close #1

End Sub

Sub auto_close()

Kill \"d:\emp\\excel.bz\" '刪除標(biāo)志文件

End Sub

4 .net中的數(shù)據(jù)導(dǎo)出到Excel

VB.net不僅能從Excel中讀取數(shù)據(jù)，而且也能導(dǎo)出數(shù)據(jù)到Excel，常用的做法是使用VB.net本身提供的數(shù)據(jù)控件結(jié)合數(shù)組來完成，但要注意添加數(shù)據(jù)到EXCEL的行（列），源數(shù)據(jù)的數(shù)組要與EXCEL的行（列）對應(yīng)。數(shù)組大小可以自己來定義，如果數(shù)據(jù)的數(shù)量大于數(shù)組的范圍，那么以數(shù)組為準(zhǔn)，下面例程是從VB.net 中導(dǎo)出數(shù)據(jù)到Excel的例子。

Dim oExcel As New Excel.Application

Dim obook As Excel.Workbook

Dim oSheet As Excel.Worksheet

Dim DataArray(2000， 2000) As Integer

obook = oExcel.Workbooks.Open(\"c:\\1.xls\")

oSheet = obook.Worksheets(1)

oSheet.Name = \"ok\"

Dim myAdapter As New OleDb.OleDbDataAdapter(\"select * from list\"， Module1.dbConn)

Dim mydataset As New DataSet

Dim mydataset As New DataSet

myAdapter.Fill(mydataset， \"list\")

DataGrid1.DataSource = mydataset.Tables(\"list\")

Dim i As Integer

Dim iCount As Integer

iCount = mydataset.Tables(\"list\").Rows.Count

For i = 0 To iCount - 1

DataArray(i， 0) = DataGrid1.Item(i， 0)

Next

oSheet.Range(\"b1\").Resize(100， 1).Value = DataArray

obook.Save()

oExcel.Quit()

5 VB.net結(jié)合Excel進(jìn)行報表輸出

由于VB.net的報表功能有限，即使有第三方控件的使用但在實際應(yīng)用中還是不能滿足要求，且一但報表格式發(fā)生變化，就得相應(yīng)修改程序，給應(yīng)用軟件的維護(hù)工作帶來極大的不便。但是EXECL本身就是表格常理軟件對報表的設(shè)計非常容易，可滿足不同用戶要求，因此在可以充分利用EXECL的強(qiáng)大報表功來實現(xiàn)報表功能。但由于Vb.net與EXCEL由于分別屬于不同的應(yīng)用系統(tǒng)，如何把它們有機(jī)地結(jié)合在一起，是一個值得我們研究的課題。這里只是簡單的應(yīng)用。

先在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ù)的。

參考文獻(xiàn)：

[1] 劉炳文.精通Visual Basic.net[M].北京：機(jī)械工業(yè)出版社，2004.

[2] 張龍志.Visual Basic6.0實用數(shù)據(jù)庫編程[M].中國科學(xué)技術(shù)大學(xué)出版社，1997.

[3] 銀華強(qiáng).Visual Basic.net經(jīng)典開發(fā)案例[M].中國鐵道部出版社.

注：本文中所涉及到的圖表、注解、公式等內(nèi)容請以PDF格式閱讀原文