“互联网+”背景下题库Excel模板自动整理功能的设计与实现*

宋士显

(浙江旅游职业学院,浙江 杭州 311231)

一、无纸化考试的现状分析

随着互联网的兴起，信息技术开始助力教育发展，“互联网 + 教育”得到全面、深度的融合,使得教育考试模式发生了巨大变化[1]，无纸化考试因其具有的灵活性、科学性、公正性、高效性等优势，再加上在国家相关政策的推动，正成为教学考试评价的必然趋势[2]。

目前国内外的无纸化考试平台有两种方式：一是单一的课程考核平台，只适用于课程考核环节；二是综合学习平台，比如当下比较流行的云班课,智慧职教平台等[3]。这些平台是集教学过程管理、资源库建设、无纸化考试等功能于一体的综合性平台，无纸化考试只是平台的一个子系统。无论哪种方式，平台软件结构都是B/S结构[4],软件结构示意图如图1所示:

图1 B/S结构无纸化考试系统结构

在B/S结构的无纸化考试系统中，服务器承担三种角色:

(一)网站服务器

用于设计实现无纸化考试前端的显示内容、结构及样式。

(二)数据库服务器

用于在服务器端存储数据，目前国内的无纸化考试系统后台数据库以关系型数据库为主，常见的数据有SQL Server数据库、MySQL数据等[5]。这些数据库中只能存储关系型数据表，这就决定了数据库只能实现单选题、多选题、判断题等这一类客观题型的存储需求。

(三)FTP文件服务器

用于让教师在浏览器端以文件的方式批量上传试题到服务器中。

目前大多数无纸化考试系统都是用Excel作为试题数据的模板，模板的数据结构一般为以下格式(表1)：

表1 Excel模板常见的数据结构

在Excel模板中，序号、题面、各个选项字段各占一列，每一道试题的题号、题面、选项在同一行依次向右排列，这种数据结构与关系型数据库中数据的存储格式一致，因而可以很方便的实现从Excel模板中快速导入数据库中。然后现实中，教师们积累的试题资源大多为Word文档格式，数据结构一般为以下格式(图2)：

图2 Word文档数据格式

要将上述Word格式转化为表1所示的Excel模板中的结构化数据，如果采用手工复制粘贴的方式转化，那么工作效率将会非常低下，此时可以使用Excel自带的VBA开发功能进行一定的设计开发，实现由Word格式自动转化为Excel模板中要求的数据结构，将大大提高工作效率。

二、功能需求分析

要实现Word格式自动转化为Excel格式，首先需要将Word文档中所有的试题批量复制粘贴到Excel工作表中，效果如下图(图3)所示：

图3 Word文档内容复制粘贴到Excel工作表后数据结构

将Word文档中的试题转移到Excel工作表中以后，接下来需要详细分析工作表中的数据结构，为VBA代码编写自动实现数据结构调整做准备。

(一)数据清洗

直接复制粘贴到Excel工作表中的数据一般是不能直接来进行处理的，因为在原Word文档中可能包含一些特殊符号，比如空格、中文标点符号混编等，再比如字母大小写问题，这些问题一般称之为杂音，在整理之前需要清除这些杂音，这里可以编写一个数据清洗的宏过程WashData，自动清除杂音。

(二)提取题号和题面

在清除杂音后，接下来需要自动提取每一道试题的题号和题面。试题的题号和题面的数据结构特点：题号必然是一个自然数，试题的题面紧跟在题号之后。可以利用这一结构特点编写一个宏过程ExtrIndexContent，由于需要对每一道试题分别提取题号和题面，因此这里必须要结合循环语句。

(三)提取选项

在提取题号和题面之后，接下来需要将每一道试题的所有选项提取出来，依次放在对应题面的右边。我们注意到每一道试题的选项数量各不相同，有的题目有ABCD四个选项，有的题目有ABCDE五个选项；而且选项的排列结构也各不相同，有的在同一个单元格内显示，有的分布在多个单元格中，如何实现各种情况下都能够自动提取所有的选项，这是Excel模板自动整理功能实现的核心。

获取所有选项需要编写一个总过程GetOptions，该过程实现的功能比较复杂，需要对过程进行分解，拆分封装成多个不同的子过程，在分别实现每一个子过程后，最后通过调用子过程的方式实现GetOptions提取所有选项的功能。

第一，需要获取每一道试题的选项区域，需要封装一个GetArea函数，所需参数为每道试题的题号，返回值为对应试题所有选项所在的单元格区域。

第二，需要将试题选项单元格区域内的所有选项连接成一个字符串，需要封装一个GetOptionString函数,所需参数为试题选项对应的单元格区域对象，返回值为由各个选项内容连接成的一个字符串。

第三，在获取每一道试题的选项字符串之后，需要从字符串中拆分出每一个选项，保存到一个数组变量中，这里需要封装一个函数GetOptionArray，所需参数为选项字符串，返回值为由各个选项组成的一个数组。由于要对字符串中的每一个选项进行拆分，且每道试题的选项数量不等，要实现拆分功能，还必须再封装一个GetPart函数，在该函数中进行判断分拣。

第四，在提取试题的每一个选项存入数组变量后，还需要编写一个过程将数组中的每一个选项放对应题面的右边，这里设计一个宏过程ArrayReWrite，实现自动整理过程。

(四)格式整理

在实现整个整理过程后，在Excel工作表会自动产生很多的空行，整理完成以后需要自动删除多余的空行，并删除整理前的试题内容，得到可以直接上传到服务器端的结构化数据模块。

三、功能设计与实现

(一)数据清洗功能的设计与实现

在数据清洗WashData过程中需要对空格、顿号、点号、英文状态下左右括号的批量处理，这里需要使用range区域对象的replace方法实现，具体代码如下：

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

(二)提取题号和题面

在数据清洗后，设计开发提取题号和题面的宏过程ExtrIndexContent。根据每一道试题只在第一行的前面包含序号，选项之前没有序号这一特点，使用val函数进行判断,提取序号和题面后分别放到试题首行右边两个连续的单元格中，由于需要多道试题进行批量操作，因此这里需要结合循环语句进行，具体代码如下：

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

(三)获取选项区域对象

在提取每一道试题的题号和题面之后，需要对每一道试题的选项进行处理，首先需要获取选项所处的区域，这里设计开发GetArea宏函数实现，需要每道试题的序号所在的单元格作为参数，返回值为对应试题选项所在的单元格区域，具体代码如下：

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

(四)获取选项字符串

在获取每一道试题选项所在的单元格区域后，需要将选项区域内的所有单元格内容连接成一个字符串，这里需要设计一个宏函数GetOptionString，所需参数为试题的选项区域，返回值为区域内单元格数据连接后的字符串，具体代码如下：

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

(五)获取选项数组

在获取选项字符串后，需要将同一道试题所有选项的内容提取出来放到一个数组中，这里需要设计开发一个宏函数GetOptionArray，所需参数为选项字符串，返回值为所有选项内容构成的数组。从选项字符串中提取每一个选项的内容可以单独封装成为GetPart函数，在GetOptionArray中调用GetPart函数，获取每一个选项的内容存入数组中。

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

(六)提取选项内容

从选项字符串中提取每一个选项的内容，这个功能可以单独封装成一个宏函数GetPart，该函数需要三个参数，第一个参数为选项字符串，第二个参数为要提取的选项名称，第三个参数为可选参数，用于控制最后一个选项是否存在问题。具体代码如下所示：

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

(七)选项填充

在获取试题的选项内容数组后，接下来需要将数组中的每一个元素填依次充到题面右边的单元格中，这里需要设计一个宏过程ArrayReWrite，所需参数为第一道试题对应的题号单元格，具体代码如下：

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

(八)选项提取整体实现

在实现上述1～7步以后，可以将上述7步整合起来，编写一个宏过程GetOptions，通过过程调用、参数传递的方式，实现对单张工作表中所有试题的格式化转换，具体代码如下：

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

(九)工作表后期整理

在工作表中对每一道试题进行格式化转换后，每一道试题的序号、题面、各个选项将自动转化为以一行的方式显示，此时原有的试题内容就可以删除，同时删除表中多余的空行，转化为最终Excel模板中要求的数据结构，具体代码如下：

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

(十)主过程调用

上述1～9实现对单张工作表数据的自动整理，为了增强程序的可扩展性，编写一个主过程main，在该过程中对工作簿中的所有工作表进行循环遍历，这样可以实现对所有工作表的批量处理，实现代码如下：

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

四、结语

本文利用Excel自带的VBA开发功能，通过编写相应的代码，实现Word格式到Excel格式的自动批量转化，大大提高了工作效率，可以将教师从简单机械的重复劳动中解放出来，投身于更高价值量的工作当中，具有非常强的使用价值和现实意义。