基于Excel+VBA 的實訓室信息表一鍵批量生成系統

朱江，吳小香，王思璇

（江蘇農牧科技職業技術學院，江蘇泰州 225300）

隨著學校辦學規模擴大、實訓室增多，采用傳統方法制作表格費時費力且生產效率低。運用VBA 編程代替傳統方法可多、快、好、省地完成實訓室信息表的制作，該系統必須滿足如下幾點要求：保證所生成的實訓室信息表與現有實訓室信息表在內容結構、色彩搭配等方面的整體風格相一致；所有實訓室平常上課的課表部分信息來自于學校教務系統導出的實訓室平時上課課表，且該信息必須與該實訓室完全正確匹配；所有實訓室教學實訓周的上課信息來自于教務系統導出的教學實訓表，同樣要求信息與所對應的實訓室完全正確匹配；由于批量生成的表格數量比較多，所以，采用傳統打印方式一張一張地打印比較費時費力，增加一鍵打印所有表格的功能，進一步完善系統功能。

1 系統設計思路

使現有表格整體風格與傳統表相一致的方法主要有以下2 種：①對現有實訓室信息表進行分析，利用VBA 程序來實現表格單元格的合并與拆分、表格字體顏色的設置、表格背景色的設置以及表格邊框的設置，使之與傳統表格相同。②將現有表格中的所有數據清空，得到一個空表。將此空表作為一個模板[1]，再從該模板表格中不斷復制生成所需數目的表格。表格模板如圖1 所示。

圖1 表格模板

同樣的功能雖然采用第一種方法也能實現，但在程序實現方面需要的工作量更大，而采用第二種方法實現的表格與原有表格完全一致，且易于實現[2]，優勢顯而易見，因此選擇第二種實現方法。

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

圖2 復制上課信息程序流程圖

如圖2 所示，根據內容分布情況將實訓室信息表分為最上部的課表信息區、中間的教學實訓安排信息區和最下部的實訓室管理員信息區3 個區域。教學實訓安排信息區的內容來自于教務系統導出的教學實訓安排表，教學實訓安排表包含環節名稱、行政班級、人數、安排教師、地點、周次這6 個字段內容。其中環節名稱即實訓課程名稱，地點為實訓室名稱，在程序設計上可以設計出相應的變量來存放這些信息[3]。

在實訓室管理員信息區域需將實訓室名稱、該實訓室管理員以及管理員的聯系方式這些內容寫入指定的單元格內。而教務系統里面導出的這些信息里面并沒有實訓室管理員和管理員的聯系號碼等信息，因此需要設計出一張工作表用于存放實訓室名稱、地點、管理員姓名以及管理員聯系號碼等信息，表結構如表1所示。在系統開始運行的時候程序會根據該表地點列內容依次從模板工作表復制出信息表的空表，并將空表的表名改成此表中實訓室地點名稱直至全部實訓室對應表格創建完畢。而在創建實訓室管理員信息區域內容時，程序只需根據實訓室地點到該表里面獲取到相對應的實訓室管理員姓名以及聯系方式，并將這些內容填入相應單元格即可。

表1 “實訓室對照表”表結構

2 系統的實現

該系統共涉及的表格有教務系統導出的日常上課課表、教務系統導出的實訓安排表、實訓室名稱對照表以及實訓室上課信息表的模板表共4 張表，且這4張表來自于4 個獨立的文件。在系統實現過程中分別新建4 個Excel 工作簿。第1 個工作簿用于存放教務系統導出的日常上課信息表，設置名稱為“1 上課安排表”；第2 個工作簿用于存放教務系統導出的教學實訓安排表，設置名稱為“2 教學實訓安排表”；第3 個工作簿用于存放實訓室名稱及管理員相關信息的表，設置名稱為“3 實訓室名稱對照表”；第4 個工作簿則是存放最終生成表格模板的表，并且最終批量生成的信息表也是在這個工作簿里面的，所以給其命名為“4批量生成表”，再附上一個說明性文件readme.txt，用于記錄系統的使用方法、注意事項等內容，如圖3 所示。

圖3 各工作簿名稱

2.1 批量生成全部實訓室信息表空表的實現

程序通過遍列“3 實訓室名稱對照表”中地點列并從模板表sheet1 復制出一份新的工作表[4]，再對它進行重命名，從而實現所需功能，運行代碼如下：

運行結束后會在“4 批量生成表”中創建若干個以實訓室地點命名的實訓室信息表的空表。

2.2 批量生成教學實訓周實訓安排信息的實現

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

2.3 批量生成上課信息表內容的實現

與教學實訓安排信息區不同，課表信息區的內容與教務系統導出的上課信息表中的內容在結構上幾乎完全一致，所以在實現這部分功能的時候采用直接對指定區域進行整體復制粘貼的方式反而更容易理解與實現。

教務系統導出的上課信息表如圖4 所示。

圖4 教務系統導出的上課信息表

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

實現代碼如下：

2.4 批量生成實訓室管理員信息內容的實現

各實訓室管理員的信息存放于“3 實訓室名稱對照表”中，首先從對照表中找到實訓室地點的內容作為添加信息時打開表的名稱，再將該實訓室名稱、管理員姓名和聯系方式3 個信息分別賦值給表格對應的單元格，從而實現了實訓室管理員信息區內容的寫入。全部信息表的實訓室管理員信息的內容只需要按以上方法將實訓室名稱對照表里面的內容進行遍列即可獲得。其實現代碼如下所示：

2.5 各模塊功能的合并

通過前面的操作，實訓室信息表所需要的功能基本都能夠一一實現，但在每次批量生成實訓室信息表的操作過程中需要操作以下4 步：①運行第1 個程序，創建全部所需要的實訓室信息表的空表；②運行第2個程序，批量生成全部實訓室的教學實訓信息內容；③運行第3 個程序，批量生成全部表的教學安排信息內容；④最后運行第4 個程序，給所有的實訓室信息表填上正確的實訓室名稱、管理員姓名及聯系號碼信息。有沒有什么好的方法一步到位呢？讓程序執行一次使計算機一次性完成上面分4 步才能完成的所有功能，要想實現這一功能，可以把這4 部分功能的程序代碼整合到一個過程中，這樣操作理論上是可以現實的，但在實際實現過程中會使程序邏輯復雜、結構混亂不堪甚至經常出錯，顯然這條思路是走不通的。另外一種實現該功能的方法是在不改變原有各功能模塊代碼的同時，創建一個新的過程，在這個新創建的過程中按順序分別運行前面4 個過程，這樣每次使用時只需要運行一次這個新的過程即可。代碼如下：

2.6 批量打印實訓室信息表的實現

實訓室信息表生成以后，需要將這些生成的全部工作表一一打印[5]到紙上張貼到各實訓室門口的信息欄中。程序在運行時通過循環檢測工作表名是否為模板表“sheet1”，如果不是則進行打印，這樣可以避免將模板表打印出來，其實現代碼如下：

3 批量生成實訓室信息表系統功能驗證

按以上步驟實現系統全部功能后，需要對系統進行進一步系統的功能性驗證。首先需要準備原始數據，打開教務系統導出的上課信息表，手工復制全部內容到“1 上課安排表”中。打開教務系統導出的教學實訓安排表，將全部內容手工復制到“2 教學實訓安排表”的教學實訓工作表中，打開“3 實訓室名稱對照表”，最后再打開程序代碼所在的宏文檔“4 批量生成表”工作簿，具體測試方法與結果如表2 所示。

表2 測試方法說明

4 結束語

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