宋士顯

(浙江旅游職業(yè)學(xué)院,浙江 杭州 311231)

一、無紙化考試的現(xiàn)狀分析

隨著互聯(lián)網(wǎng)的興起，信息技術(shù)開始助力教育發(fā)展，“互聯(lián)網(wǎng) + 教育”得到全面、深度的融合,使得教育考試模式發(fā)生了巨大變化[1]，無紙化考試因其具有的靈活性、科學(xué)性、公正性、高效性等優(yōu)勢，再加上在國家相關(guān)政策的推動，正成為教學(xué)考試評價的必然趨勢[2]。

目前國內(nèi)外的無紙化考試平臺有兩種方式：一是單一的課程考核平臺，只適用于課程考核環(huán)節(jié)；二是綜合學(xué)習(xí)平臺，比如當(dāng)下比較流行的云班課,智慧職教平臺等[3]。這些平臺是集教學(xué)過程管理、資源庫建設(shè)、無紙化考試等功能于一體的綜合性平臺，無紙化考試只是平臺的一個子系統(tǒng)。無論哪種方式，平臺軟件結(jié)構(gòu)都是B/S結(jié)構(gòu)[4],軟件結(jié)構(gòu)示意圖如圖1所示:

圖1 B/S結(jié)構(gòu)無紙化考試系統(tǒng)結(jié)構(gòu)

在B/S結(jié)構(gòu)的無紙化考試系統(tǒng)中，服務(wù)器承擔(dān)三種角色:

(一)網(wǎng)站服務(wù)器

用于設(shè)計實現(xiàn)無紙化考試前端的顯示內(nèi)容、結(jié)構(gòu)及樣式。

(二)數(shù)據(jù)庫服務(wù)器

用于在服務(wù)器端存儲數(shù)據(jù)，目前國內(nèi)的無紙化考試系統(tǒng)后臺數(shù)據(jù)庫以關(guān)系型數(shù)據(jù)庫為主，常見的數(shù)據(jù)有SQL Server數(shù)據(jù)庫、MySQL數(shù)據(jù)等[5]。這些數(shù)據(jù)庫中只能存儲關(guān)系型數(shù)據(jù)表，這就決定了數(shù)據(jù)庫只能實現(xiàn)單選題、多選題、判斷題等這一類客觀題型的存儲需求。

(三)FTP文件服務(wù)器

用于讓教師在瀏覽器端以文件的方式批量上傳試題到服務(wù)器中。

目前大多數(shù)無紙化考試系統(tǒng)都是用Excel作為試題數(shù)據(jù)的模板，模板的數(shù)據(jù)結(jié)構(gòu)一般為以下格式(表1)：

表1 Excel模板常見的數(shù)據(jù)結(jié)構(gòu)

在Excel模板中，序號、題面、各個選項字段各占一列，每一道試題的題號、題面、選項在同一行依次向右排列，這種數(shù)據(jù)結(jié)構(gòu)與關(guān)系型數(shù)據(jù)庫中數(shù)據(jù)的存儲格式一致，因而可以很方便的實現(xiàn)從Excel模板中快速導(dǎo)入數(shù)據(jù)庫中。然后現(xiàn)實中，教師們積累的試題資源大多為Word文檔格式，數(shù)據(jù)結(jié)構(gòu)一般為以下格式(圖2)：

圖2 Word文檔數(shù)據(jù)格式

要將上述Word格式轉(zhuǎn)化為表1所示的Excel模板中的結(jié)構(gòu)化數(shù)據(jù)，如果采用手工復(fù)制粘貼的方式轉(zhuǎn)化，那么工作效率將會非常低下，此時可以使用Excel自帶的VBA開發(fā)功能進(jìn)行一定的設(shè)計開發(fā)，實現(xiàn)由Word格式自動轉(zhuǎn)化為Excel模板中要求的數(shù)據(jù)結(jié)構(gòu)，將大大提高工作效率。

二、功能需求分析

要實現(xiàn)Word格式自動轉(zhuǎn)化為Excel格式，首先需要將Word文檔中所有的試題批量復(fù)制粘貼到Excel工作表中，效果如下圖(圖3)所示：

圖3 Word文檔內(nèi)容復(fù)制粘貼到Excel工作表后數(shù)據(jù)結(jié)構(gòu)

將Word文檔中的試題轉(zhuǎn)移到Excel工作表中以后，接下來需要詳細(xì)分析工作表中的數(shù)據(jù)結(jié)構(gòu)，為VBA代碼編寫自動實現(xiàn)數(shù)據(jù)結(jié)構(gòu)調(diào)整做準(zhǔn)備。

(一)數(shù)據(jù)清洗

直接復(fù)制粘貼到Excel工作表中的數(shù)據(jù)一般是不能直接來進(jìn)行處理的，因為在原Word文檔中可能包含一些特殊符號，比如空格、中文標(biāo)點符號混編等，再比如字母大小寫問題，這些問題一般稱之為雜音，在整理之前需要清除這些雜音，這里可以編寫一個數(shù)據(jù)清洗的宏過程WashData，自動清除雜音。

(二)提取題號和題面

在清除雜音后，接下來需要自動提取每一道試題的題號和題面。試題的題號和題面的數(shù)據(jù)結(jié)構(gòu)特點：題號必然是一個自然數(shù)，試題的題面緊跟在題號之后?？梢岳眠@一結(jié)構(gòu)特點編寫一個宏過程ExtrIndexContent，由于需要對每一道試題分別提取題號和題面，因此這里必須要結(jié)合循環(huán)語句。

(三)提取選項

在提取題號和題面之后，接下來需要將每一道試題的所有選項提取出來，依次放在對應(yīng)題面的右邊。我們注意到每一道試題的選項數(shù)量各不相同，有的題目有ABCD四個選項，有的題目有ABCDE五個選項；而且選項的排列結(jié)構(gòu)也各不相同，有的在同一個單元格內(nèi)顯示，有的分布在多個單元格中，如何實現(xiàn)各種情況下都能夠自動提取所有的選項，這是Excel模板自動整理功能實現(xiàn)的核心。

獲取所有選項需要編寫一個總過程GetOptions，該過程實現(xiàn)的功能比較復(fù)雜，需要對過程進(jìn)行分解，拆分封裝成多個不同的子過程，在分別實現(xiàn)每一個子過程后，最后通過調(diào)用子過程的方式實現(xiàn)GetOptions提取所有選項的功能。

第一，需要獲取每一道試題的選項區(qū)域，需要封裝一個GetArea函數(shù)，所需參數(shù)為每道試題的題號，返回值為對應(yīng)試題所有選項所在的單元格區(qū)域。

第二，需要將試題選項單元格區(qū)域內(nèi)的所有選項連接成一個字符串，需要封裝一個GetOptionString函數(shù),所需參數(shù)為試題選項對應(yīng)的單元格區(qū)域?qū)ο螅祷刂禐橛筛鱾€選項內(nèi)容連接成的一個字符串。

第三，在獲取每一道試題的選項字符串之后，需要從字符串中拆分出每一個選項，保存到一個數(shù)組變量中，這里需要封裝一個函數(shù)GetOptionArray，所需參數(shù)為選項字符串，返回值為由各個選項組成的一個數(shù)組。由于要對字符串中的每一個選項進(jìn)行拆分，且每道試題的選項數(shù)量不等，要實現(xiàn)拆分功能，還必須再封裝一個GetPart函數(shù)，在該函數(shù)中進(jìn)行判斷分揀。

第四，在提取試題的每一個選項存入數(shù)組變量后，還需要編寫一個過程將數(shù)組中的每一個選項放對應(yīng)題面的右邊，這里設(shè)計一個宏過程ArrayReWrite，實現(xiàn)自動整理過程。

(四)格式整理

在實現(xiàn)整個整理過程后，在Excel工作表會自動產(chǎn)生很多的空行，整理完成以后需要自動刪除多余的空行，并刪除整理前的試題內(nèi)容，得到可以直接上傳到服務(wù)器端的結(jié)構(gòu)化數(shù)據(jù)模塊。

三、功能設(shè)計與實現(xiàn)

(一)數(shù)據(jù)清洗功能的設(shè)計與實現(xiàn)

在數(shù)據(jù)清洗WashData過程中需要對空格、頓號、點號、英文狀態(tài)下左右括號的批量處理，這里需要使用range區(qū)域?qū)ο蟮膔eplace方法實現(xiàn)，具體代碼如下：

Sub WashData ()

Dim rng As range

Set rng = ActiveSheet.UsedRange

rng.Replace "、",""

rng.Replace "(",""

rng.Replace ")",""

rng.Replace "(",""

rng.Replace ")",""

rng.Replace ".",""

rng.Replace " ",""

Set rng = Nothing

End Sub

(二)提取題號和題面

在數(shù)據(jù)清洗后，設(shè)計開發(fā)提取題號和題面的宏過程ExtrIndexContent。根據(jù)每一道試題只在第一行的前面包含序號，選項之前沒有序號這一特點，使用val函數(shù)進(jìn)行判斷,提取序號和題面后分別放到試題首行右邊兩個連續(xù)的單元格中，由于需要多道試題進(jìn)行批量操作，因此這里需要結(jié)合循環(huán)語句進(jìn)行，具體代碼如下：

Sub ExtrIndexContent()

Dim rng As range

Dim myRng As range

Dim cel As range

Set rng = ActiveSheet.range("a1048576").End(xlUp)

Set myRng = range(Cells(1,1),rng)

Dim str As String

Dim myIndex As Integer

ForEach cel In myRng

If Val(cel.Value) >0 Then

str = cel.Value

myIndex = Val(str)

cel.Offset(0,1) = myIndex

cel.Offset(0,2) = Right(str,Len(str) - Len(myIndex))

End If

Next

Set rng = Nothing

Set cel = Nothing

End Sub

(三)獲取選項區(qū)域?qū)ο?/h3>

在提取每一道試題的題號和題面之后，需要對每一道試題的選項進(jìn)行處理，首先需要獲取選項所處的區(qū)域，這里設(shè)計開發(fā)GetArea宏函數(shù)實現(xiàn)，需要每道試題的序號所在的單元格作為參數(shù)，返回值為對應(yīng)試題選項所在的單元格區(qū)域，具體代碼如下：

Function GetArea(rng As range)

Dim rngAns As range

Dim rngNext As range

Set rngNext = rng.End(xlDown)

If rngNext.Row <1048576 Then

Set rngAns = rng.Offset(1,-1).Resize(rngNext.Row - rng.Row - 1,1)

Else

Set rngAns = rng.Offset(1,-1).Resize(ActiveSheet.range("a1048576").End(xlUp).Row - rng.Row,1)

End If

Set GetArea = rngAns

Set rngNext = Nothing

Set rngAns = Nothing

End Function

(四)獲取選項字符串

在獲取每一道試題選項所在的單元格區(qū)域后，需要將選項區(qū)域內(nèi)的所有單元格內(nèi)容連接成一個字符串，這里需要設(shè)計一個宏函數(shù)GetOptionString，所需參數(shù)為試題的選項區(qū)域，返回值為區(qū)域內(nèi)單元格數(shù)據(jù)連接后的字符串，具體代碼如下：

Function GetOptionString(rng As range)

Dim str As String

For i = 1 To rng.Cells.Count

str = str &rng.Cells(i).Value

Next

GetOptionString = str

End Function

(五)獲取選項數(shù)組

在獲取選項字符串后，需要將同一道試題所有選項的內(nèi)容提取出來放到一個數(shù)組中，這里需要設(shè)計開發(fā)一個宏函數(shù)GetOptionArray，所需參數(shù)為選項字符串，返回值為所有選項內(nèi)容構(gòu)成的數(shù)組。從選項字符串中提取每一個選項的內(nèi)容可以單獨封裝成為GetPart函數(shù)，在GetOptionArray中調(diào)用GetPart函數(shù)，獲取每一個選項的內(nèi)容存入數(shù)組中。

Function GetOptionArray(ByVal str As String)

Dim arr(4)

arr(0) = GetPart(str,"A","B")

arr(1) = GetPart(str,"B","C")

arr(2) = GetPart(str,"C","D")

arr(3) = GetPart(str,"D","E")

arr(4) = GetPart(str,"E")

GetOptionArray = arr

End Function

(六)提取選項內(nèi)容

從選項字符串中提取每一個選項的內(nèi)容，這個功能可以單獨封裝成一個宏函數(shù)GetPart，該函數(shù)需要三個參數(shù)，第一個參數(shù)為選項字符串，第二個參數(shù)為要提取的選項名稱，第三個參數(shù)為可選參數(shù)，用于控制最后一個選項是否存在問題。具體代碼如下所示：

Function GetPart(str As String,First As String,Optional Second)

Dim a As Integer

Dim b As Integer

On Error Resume Next

a = InStr(str,First)

If a = 0 Then Exit Function

If IsMissing(Second) Then

b = Len(str) + 1

Else

temp = InStr(str,Second)

b = IIf(temp = 0,Len(str) + 1,temp)

End If

GetPart = Mid(str,a + Len(First),b - a - Len(First))

On Error GoTo 0

End Function

(七)選項填充

在獲取試題的選項內(nèi)容數(shù)組后，接下來需要將數(shù)組中的每一個元素填依次充到題面右邊的單元格中，這里需要設(shè)計一個宏過程ArrayReWrite，所需參數(shù)為第一道試題對應(yīng)的題號單元格，具體代碼如下：

Sub ArrayReWrite (rng As range)

Dim rngAns As range

Dim str As String

Dim arr

If rng.Value <>"" Then

Set rngAns = GetArea (rng)

str = GetOptionString (rngAns)

arr = GetOptionArray (str)

rng.Offset(0,2).Resize(1,5) = arr

End If

Set rngAns = Nothing

End Sub

(八)選項提取整體實現(xiàn)

在實現(xiàn)上述1～7步以后，可以將上述7步整合起來，編寫一個宏過程GetOptions，通過過程調(diào)用、參數(shù)傳遞的方式，實現(xiàn)對單張工作表中所有試題的格式化轉(zhuǎn)換，具體代碼如下：

Sub GetOptions ()

Dim rng As range

Set rng = ActiveSheet.range("b1:b" &ActiveSheet.range("b1048576").End(xlUp).Row)

For i = 1 To rng.Cells.Count

ArrayReWrite rng.Cells(i)

Next

Set rng = Nothing

End Sub

(九)工作表后期整理

在工作表中對每一道試題進(jìn)行格式化轉(zhuǎn)換后，每一道試題的序號、題面、各個選項將自動轉(zhuǎn)化為以一行的方式顯示，此時原有的試題內(nèi)容就可以刪除，同時刪除表中多余的空行，轉(zhuǎn)化為最終Excel模板中要求的數(shù)據(jù)結(jié)構(gòu)，具體代碼如下：

Sub DeleteBlankRows()

Dim i As Integer

For i = ActiveSheet.range("a1048576").End(xlUp).Row To 1 Step -1

If ActiveSheet.range("b" &i) = "" Then

ActiveSheet.range("b" &i).EntireRow.Delete

End If

Next

ActiveSheet.range("a:a").EntireColumn.Delete

End Sub

(十)主過程調(diào)用

上述1～9實現(xiàn)對單張工作表數(shù)據(jù)的自動整理，為了增強(qiáng)程序的可擴(kuò)展性，編寫一個主過程main，在該過程中對工作簿中的所有工作表進(jìn)行循環(huán)遍歷，這樣可以實現(xiàn)對所有工作表的批量處理，實現(xiàn)代碼如下：

Sub main()

Dim ws As Worksheet

Application.ScreenUpdating = False

For Each ws In ThisWorkbook.Worksheets

ws.Activate

WashData

ExtrIndexContent

GetOptions

DeleteBlankRows

Next

Application.ScreenUpdating = True

End Sub

四、結(jié)語

本文利用Excel自帶的VBA開發(fā)功能，通過編寫相應(yīng)的代碼，實現(xiàn)Word格式到Excel格式的自動批量轉(zhuǎn)化，大大提高了工作效率，可以將教師從簡單機(jī)械的重復(fù)勞動中解放出來，投身于更高價值量的工作當(dāng)中，具有非常強(qiáng)的使用價值和現(xiàn)實意義。