基于Excel+VBA 的實(shí)訓(xùn)室信息表一鍵批量生成系統(tǒng)

朱江，吳小香，王思璇

（江蘇農(nóng)牧科技職業(yè)技術(shù)學(xué)院，江蘇泰州 225300）

隨著學(xué)校辦學(xué)規(guī)模擴(kuò)大、實(shí)訓(xùn)室增多，采用傳統(tǒng)方法制作表格費(fèi)時(shí)費(fèi)力且生產(chǎn)效率低。運(yùn)用VBA 編程代替?zhèn)鹘y(tǒng)方法可多、快、好、省地完成實(shí)訓(xùn)室信息表的制作，該系統(tǒng)必須滿足如下幾點(diǎn)要求：保證所生成的實(shí)訓(xùn)室信息表與現(xiàn)有實(shí)訓(xùn)室信息表在內(nèi)容結(jié)構(gòu)、色彩搭配等方面的整體風(fēng)格相一致；所有實(shí)訓(xùn)室平常上課的課表部分信息來(lái)自于學(xué)校教務(wù)系統(tǒng)導(dǎo)出的實(shí)訓(xùn)室平時(shí)上課課表，且該信息必須與該實(shí)訓(xùn)室完全正確匹配；所有實(shí)訓(xùn)室教學(xué)實(shí)訓(xùn)周的上課信息來(lái)自于教務(wù)系統(tǒng)導(dǎo)出的教學(xué)實(shí)訓(xùn)表，同樣要求信息與所對(duì)應(yīng)的實(shí)訓(xùn)室完全正確匹配；由于批量生成的表格數(shù)量比較多，所以，采用傳統(tǒng)打印方式一張一張地打印比較費(fèi)時(shí)費(fèi)力，增加一鍵打印所有表格的功能，進(jìn)一步完善系統(tǒng)功能。

1 系統(tǒng)設(shè)計(jì)思路

使現(xiàn)有表格整體風(fēng)格與傳統(tǒng)表相一致的方法主要有以下2 種：①對(duì)現(xiàn)有實(shí)訓(xùn)室信息表進(jìn)行分析，利用VBA 程序來(lái)實(shí)現(xiàn)表格單元格的合并與拆分、表格字體顏色的設(shè)置、表格背景色的設(shè)置以及表格邊框的設(shè)置，使之與傳統(tǒng)表格相同。②將現(xiàn)有表格中的所有數(shù)據(jù)清空，得到一個(gè)空表。將此空表作為一個(gè)模板[1]，再?gòu)脑撃０灞砀裰胁粩鄰?fù)制生成所需數(shù)目的表格。表格模板如圖1 所示。

圖1 表格模板

同樣的功能雖然采用第一種方法也能實(shí)現(xiàn)，但在程序?qū)崿F(xiàn)方面需要的工作量更大，而采用第二種方法實(shí)現(xiàn)的表格與原有表格完全一致，且易于實(shí)現(xiàn)[2]，優(yōu)勢(shì)顯而易見(jiàn)，因此選擇第二種實(shí)現(xiàn)方法。

需要對(duì)所復(fù)制的眾多表格賦予相應(yīng)的表格名稱，通過(guò)進(jìn)一步分析研究教務(wù)系統(tǒng)導(dǎo)出的實(shí)訓(xùn)室課表內(nèi)容得出如下信息：①所有實(shí)訓(xùn)室日常上課信息都在同一張工作表里面；②在每張表的上方都會(huì)顯示“教室”2個(gè)字，后面緊接著就是實(shí)訓(xùn)室的名稱，然后再用短下劃線連接實(shí)訓(xùn)室地點(diǎn)，接著在括號(hào)里面注明該實(shí)訓(xùn)室所能夠容納的學(xué)生數(shù)，例如“教室：軟件開(kāi)發(fā)_經(jīng)C-307（50）”。并且所有實(shí)訓(xùn)室的這個(gè)信息都是在工作表的G 列，根據(jù)這一信息，可以從G1 單元格開(kāi)始一直往下循環(huán)遍列查找單元格內(nèi)容是否有“教室”2 字，找到符合條件的信息從字符串中提取出教室后面的實(shí)訓(xùn)室名稱，接著再將下方課表區(qū)域的信息內(nèi)容復(fù)制到對(duì)應(yīng)名稱的實(shí)訓(xùn)室信息表的日常課表信息區(qū)域即可，如此循環(huán)往復(fù)便完成全部表格上課信息內(nèi)容的制作，程序流程如圖2 所示。

圖2 復(fù)制上課信息程序流程圖

如圖2 所示，根據(jù)內(nèi)容分布情況將實(shí)訓(xùn)室信息表分為最上部的課表信息區(qū)、中間的教學(xué)實(shí)訓(xùn)安排信息區(qū)和最下部的實(shí)訓(xùn)室管理員信息區(qū)3 個(gè)區(qū)域。教學(xué)實(shí)訓(xùn)安排信息區(qū)的內(nèi)容來(lái)自于教務(wù)系統(tǒng)導(dǎo)出的教學(xué)實(shí)訓(xùn)安排表，教學(xué)實(shí)訓(xùn)安排表包含環(huán)節(jié)名稱、行政班級(jí)、人數(shù)、安排教師、地點(diǎn)、周次這6 個(gè)字段內(nèi)容。其中環(huán)節(jié)名稱即實(shí)訓(xùn)課程名稱，地點(diǎn)為實(shí)訓(xùn)室名稱，在程序設(shè)計(jì)上可以設(shè)計(jì)出相應(yīng)的變量來(lái)存放這些信息[3]。

在實(shí)訓(xùn)室管理員信息區(qū)域需將實(shí)訓(xùn)室名稱、該實(shí)訓(xùn)室管理員以及管理員的聯(lián)系方式這些內(nèi)容寫入指定的單元格內(nèi)。而教務(wù)系統(tǒng)里面導(dǎo)出的這些信息里面并沒(méi)有實(shí)訓(xùn)室管理員和管理員的聯(lián)系號(hào)碼等信息，因此需要設(shè)計(jì)出一張工作表用于存放實(shí)訓(xùn)室名稱、地點(diǎn)、管理員姓名以及管理員聯(lián)系號(hào)碼等信息，表結(jié)構(gòu)如表1所示。在系統(tǒng)開(kāi)始運(yùn)行的時(shí)候程序會(huì)根據(jù)該表地點(diǎn)列內(nèi)容依次從模板工作表復(fù)制出信息表的空表，并將空表的表名改成此表中實(shí)訓(xùn)室地點(diǎn)名稱直至全部實(shí)訓(xùn)室對(duì)應(yīng)表格創(chuàng)建完畢。而在創(chuàng)建實(shí)訓(xùn)室管理員信息區(qū)域內(nèi)容時(shí)，程序只需根據(jù)實(shí)訓(xùn)室地點(diǎn)到該表里面獲取到相對(duì)應(yīng)的實(shí)訓(xùn)室管理員姓名以及聯(lián)系方式，并將這些內(nèi)容填入相應(yīng)單元格即可。

表1 “實(shí)訓(xùn)室對(duì)照表”表結(jié)構(gòu)

2 系統(tǒng)的實(shí)現(xiàn)

該系統(tǒng)共涉及的表格有教務(wù)系統(tǒng)導(dǎo)出的日常上課課表、教務(wù)系統(tǒng)導(dǎo)出的實(shí)訓(xùn)安排表、實(shí)訓(xùn)室名稱對(duì)照表以及實(shí)訓(xùn)室上課信息表的模板表共4 張表，且這4張表來(lái)自于4 個(gè)獨(dú)立的文件。在系統(tǒng)實(shí)現(xiàn)過(guò)程中分別新建4 個(gè)Excel 工作簿。第1 個(gè)工作簿用于存放教務(wù)系統(tǒng)導(dǎo)出的日常上課信息表，設(shè)置名稱為“1 上課安排表”；第2 個(gè)工作簿用于存放教務(wù)系統(tǒng)導(dǎo)出的教學(xué)實(shí)訓(xùn)安排表，設(shè)置名稱為“2 教學(xué)實(shí)訓(xùn)安排表”；第3 個(gè)工作簿用于存放實(shí)訓(xùn)室名稱及管理員相關(guān)信息的表，設(shè)置名稱為“3 實(shí)訓(xùn)室名稱對(duì)照表”；第4 個(gè)工作簿則是存放最終生成表格模板的表，并且最終批量生成的信息表也是在這個(gè)工作簿里面的，所以給其命名為“4批量生成表”，再附上一個(gè)說(shuō)明性文件readme.txt，用于記錄系統(tǒng)的使用方法、注意事項(xiàng)等內(nèi)容，如圖3 所示。

圖3 各工作簿名稱

2.1 批量生成全部實(shí)訓(xùn)室信息表空表的實(shí)現(xiàn)

程序通過(guò)遍列“3 實(shí)訓(xùn)室名稱對(duì)照表”中地點(diǎn)列并從模板表sheet1 復(fù)制出一份新的工作表[4]，再對(duì)它進(jìn)行重命名，從而實(shí)現(xiàn)所需功能，運(yùn)行代碼如下：

運(yùn)行結(jié)束后會(huì)在“4 批量生成表”中創(chuàng)建若干個(gè)以實(shí)訓(xùn)室地點(diǎn)命名的實(shí)訓(xùn)室信息表的空表。

2.2 批量生成教學(xué)實(shí)訓(xùn)周實(shí)訓(xùn)安排信息的實(shí)現(xiàn)

經(jīng)觀察發(fā)現(xiàn)從教務(wù)系統(tǒng)導(dǎo)出的教學(xué)實(shí)訓(xùn)安排信息表除包含需要的課程、班級(jí)、教師、人數(shù)、周別等信息之外中間還夾雜著其他無(wú)用信息，因此不能使用直接復(fù)制粘貼某個(gè)區(qū)域的方式快速實(shí)現(xiàn)。需設(shè)置幾個(gè)對(duì)應(yīng)的變量，將各相關(guān)信息賦值給變量，再根據(jù)實(shí)訓(xùn)地點(diǎn)變量?jī)?nèi)容，打開(kāi)以該地點(diǎn)為名稱的實(shí)訓(xùn)室工作表，再將課程、班級(jí)、教師、人數(shù)等信息存放在表格相應(yīng)單元格中即可，如此循環(huán)往復(fù)直到教學(xué)實(shí)訓(xùn)安排表中全部?jī)?nèi)容復(fù)制完畢即可[5]。這其中需要解決的一個(gè)重要難題是每次打開(kāi)一個(gè)需要存放內(nèi)容的實(shí)訓(xùn)室信息表后如何才能找到正確的存放位置，通過(guò)觀察發(fā)現(xiàn)存放實(shí)訓(xùn)信息內(nèi)容的區(qū)域在圖1 所示的表格中的D10:H15 單元格區(qū)域。于是就可以通過(guò)對(duì)D1 5 單元格運(yùn)用End（xlUp）函數(shù)向上找到有數(shù)據(jù)的那一個(gè)單元格，這個(gè)單元格是上一條數(shù)據(jù)的課程名稱列內(nèi)容，從這個(gè)單元格再向下偏移1 個(gè)單元格即是需要存放下一條實(shí)訓(xùn)數(shù)據(jù)的課程名稱內(nèi)容的位置，具體實(shí)現(xiàn)代碼如下所示：

2.3 批量生成上課信息表內(nèi)容的實(shí)現(xiàn)

與教學(xué)實(shí)訓(xùn)安排信息區(qū)不同，課表信息區(qū)的內(nèi)容與教務(wù)系統(tǒng)導(dǎo)出的上課信息表中的內(nèi)容在結(jié)構(gòu)上幾乎完全一致，所以在實(shí)現(xiàn)這部分功能的時(shí)候采用直接對(duì)指定區(qū)域進(jìn)行整體復(fù)制粘貼的方式反而更容易理解與實(shí)現(xiàn)。

教務(wù)系統(tǒng)導(dǎo)出的上課信息表如圖4 所示。

圖4 教務(wù)系統(tǒng)導(dǎo)出的上課信息表

這些表都是存放在一張工作表里面的，在每個(gè)表的右上角G 列的位置會(huì)顯示實(shí)訓(xùn)室名稱，從圖中放大標(biāo)識(shí)的內(nèi)容“教室：軟件開(kāi)發(fā)_經(jīng)C-307（50）”中可以看到該實(shí)訓(xùn)室地點(diǎn)名稱就是下劃線和左半個(gè)括號(hào)之間的內(nèi)容“C-307”。由于每個(gè)表格結(jié)構(gòu)相同，除了這部分內(nèi)容之外沒(méi)有其他內(nèi)容會(huì)包含“教室”2 個(gè)字，所以可以通過(guò)在數(shù)據(jù)區(qū)域遍列G 列全部單元格，看是否以“教室”2 字開(kāi)頭，以此開(kāi)頭則進(jìn)一步提取實(shí)訓(xùn)室的地點(diǎn)內(nèi)容作為粘貼課表信息內(nèi)容時(shí)查找的目標(biāo)表格名稱，以G 列當(dāng)前單元格為基準(zhǔn)通過(guò)Offset 函數(shù)向下偏2 行[3]，向右偏4 列得到的單元格位置為起始單元格，再通過(guò)Resize 函數(shù)將選區(qū)擴(kuò)大到5 行7 列，此區(qū)域作為需要復(fù)制的源數(shù)據(jù)，復(fù)制到目標(biāo)表格的以D4 單元格為起始位置的同樣大小的區(qū)域即可。

實(shí)現(xiàn)代碼如下：

2.4 批量生成實(shí)訓(xùn)室管理員信息內(nèi)容的實(shí)現(xiàn)

各實(shí)訓(xùn)室管理員的信息存放于“3 實(shí)訓(xùn)室名稱對(duì)照表”中，首先從對(duì)照表中找到實(shí)訓(xùn)室地點(diǎn)的內(nèi)容作為添加信息時(shí)打開(kāi)表的名稱，再將該實(shí)訓(xùn)室名稱、管理員姓名和聯(lián)系方式3 個(gè)信息分別賦值給表格對(duì)應(yīng)的單元格，從而實(shí)現(xiàn)了實(shí)訓(xùn)室管理員信息區(qū)內(nèi)容的寫入。全部信息表的實(shí)訓(xùn)室管理員信息的內(nèi)容只需要按以上方法將實(shí)訓(xùn)室名稱對(duì)照表里面的內(nèi)容進(jìn)行遍列即可獲得。其實(shí)現(xiàn)代碼如下所示：

2.5 各模塊功能的合并

通過(guò)前面的操作，實(shí)訓(xùn)室信息表所需要的功能基本都能夠一一實(shí)現(xiàn)，但在每次批量生成實(shí)訓(xùn)室信息表的操作過(guò)程中需要操作以下4 步：①運(yùn)行第1 個(gè)程序，創(chuàng)建全部所需要的實(shí)訓(xùn)室信息表的空表；②運(yùn)行第2個(gè)程序，批量生成全部實(shí)訓(xùn)室的教學(xué)實(shí)訓(xùn)信息內(nèi)容；③運(yùn)行第3 個(gè)程序，批量生成全部表的教學(xué)安排信息內(nèi)容；④最后運(yùn)行第4 個(gè)程序，給所有的實(shí)訓(xùn)室信息表填上正確的實(shí)訓(xùn)室名稱、管理員姓名及聯(lián)系號(hào)碼信息。有沒(méi)有什么好的方法一步到位呢？讓程序執(zhí)行一次使計(jì)算機(jī)一次性完成上面分4 步才能完成的所有功能，要想實(shí)現(xiàn)這一功能，可以把這4 部分功能的程序代碼整合到一個(gè)過(guò)程中，這樣操作理論上是可以現(xiàn)實(shí)的，但在實(shí)際實(shí)現(xiàn)過(guò)程中會(huì)使程序邏輯復(fù)雜、結(jié)構(gòu)混亂不堪甚至經(jīng)常出錯(cuò)，顯然這條思路是走不通的。另外一種實(shí)現(xiàn)該功能的方法是在不改變?cè)懈鞴δ苣K代碼的同時(shí)，創(chuàng)建一個(gè)新的過(guò)程，在這個(gè)新創(chuàng)建的過(guò)程中按順序分別運(yùn)行前面4 個(gè)過(guò)程，這樣每次使用時(shí)只需要運(yùn)行一次這個(gè)新的過(guò)程即可。代碼如下：

2.6 批量打印實(shí)訓(xùn)室信息表的實(shí)現(xiàn)

實(shí)訓(xùn)室信息表生成以后，需要將這些生成的全部工作表一一打印[5]到紙上張貼到各實(shí)訓(xùn)室門口的信息欄中。程序在運(yùn)行時(shí)通過(guò)循環(huán)檢測(cè)工作表名是否為模板表“sheet1”，如果不是則進(jìn)行打印，這樣可以避免將模板表打印出來(lái)，其實(shí)現(xiàn)代碼如下：

3 批量生成實(shí)訓(xùn)室信息表系統(tǒng)功能驗(yàn)證

按以上步驟實(shí)現(xiàn)系統(tǒng)全部功能后，需要對(duì)系統(tǒng)進(jìn)行進(jìn)一步系統(tǒng)的功能性驗(yàn)證。首先需要準(zhǔn)備原始數(shù)據(jù)，打開(kāi)教務(wù)系統(tǒng)導(dǎo)出的上課信息表，手工復(fù)制全部?jī)?nèi)容到“1 上課安排表”中。打開(kāi)教務(wù)系統(tǒng)導(dǎo)出的教學(xué)實(shí)訓(xùn)安排表，將全部?jī)?nèi)容手工復(fù)制到“2 教學(xué)實(shí)訓(xùn)安排表”的教學(xué)實(shí)訓(xùn)工作表中，打開(kāi)“3 實(shí)訓(xùn)室名稱對(duì)照表”，最后再打開(kāi)程序代碼所在的宏文檔“4 批量生成表”工作簿，具體測(cè)試方法與結(jié)果如表2 所示。

表2 測(cè)試方法說(shuō)明

4 結(jié)束語(yǔ)

經(jīng)測(cè)試運(yùn)行宏后批量生成了所有實(shí)訓(xùn)室信息表，通過(guò)將這些表逐個(gè)與手工制作的表進(jìn)行比較后看出本文所采用的VBA+Excel 編寫的宏程序能夠滿足快速批量生成實(shí)訓(xùn)室信息表的功能，并且所生成的實(shí)訓(xùn)室信息表在整體風(fēng)格上與傳統(tǒng)方法制作的表格相一致，同時(shí)避免了手工制作表格過(guò)程中的人為因素造成的錯(cuò)誤。生成速度快，平時(shí)手工制作20 個(gè)這樣的實(shí)訓(xùn)室的信息表加上數(shù)據(jù)核對(duì)大約需要3 個(gè)多小時(shí)，而本程序批量生成所需時(shí)間在2 s 內(nèi)，即使算上前期數(shù)據(jù)表的整理也不會(huì)超過(guò)5 min，遠(yuǎn)快于手工操作。