Excel操作题自动出题系统的设计与实现

郝晗瀚，康慕宁

( 西北工业大学计算机学院，陕西 西安 710129)

0 引 言

随着科学技术快速发展，计算机技术已经被广泛应用于生产技术的各个领域，因此每个人都应该掌握一些基本的计算机技能。对计算机技能的考核，如果采用传统的笔试方式，很难客观地反映学生的应用计算机的能力［1］，所以目前大多的计算机技能测试都采用上机考试代替笔试。

对学生的计算机技能的考核方式包括客观性试题测试和操作性试题测试［2］。客观性试题主要是基础知识测试。目前客观性试题的自动出题和自动判卷技术已经比较成熟;操作性试题主要是操作技能测试，考试内容一般是对Office 等办公软件的熟练应用。比如计算机一级考试的Excel 工作表的公式操作，单元格格式的设置等。

由于Excel 操作题的题干信息量比较大，且不易描述，如果采用传统的出题方式，利用人工来编辑试题，将会花费出题者的大量精力和时间，而自动出题技术能够解决这一难题。目前关于自动出题方面的参考文献比较少，常见的出题大致有从试题库中随机抽题和人工抽题两种方式［3-5］。传统的Excel 出题系统界面和数据库的设计非常繁杂，一般需要在界面上进行很多选择设置，例如操作的知识点，具体的操作类型，操作区域等。数据库中的也要设置很多字段存放操作类型、操作区域、属性值等。这样生成试题的过程含有大量的人工手操作，很难称之为自动出题［6-7］。

针对上述问题，有些文献提出了自动生成Excel操作题的方法［2，8-9］，它们基本上实现了自动出题，但是也需要手动输入一些信息，不是真正意义上的自动出题。本文提出一种自动生成Excel 操作题的方法，该方法能自动生成试题库中没有的新试题。出题者只需按照正确结果操作一次原始Excel 文件，然后上传原始和答案文件，通过比较两个文件的不同点，系统就能自动生成试题信息。

与传统的Excel 出题系统相比，本文设计的出题方法的操作过程十分简单，出题者无需具有任何计算机专业知识就可以上手，而且出的题目十分灵活，只需一次出题就可包含所有考点。本系统最大的亮点是题目信息完全自动化生成，整个出题过程无需出题者输入任何信息，即可让出题者根据自己的意愿生成新的操作题试题。

1 相关技术简介

本系统从易用性、运行速度及开发难度考虑，选用Microsoft Visual Studio 2008进行界面设计，C#作为开发语言。Microsoft Visual Studio 2008 整合了对象、关系型数据、XML的访问方式，它可以高效开发Office 应用和Mobile 应用。C#是一种简单、通用，以及面向对象的程序设计语言，此种语言实现了对以下软件工程要素的支持:强类型检查、数组维度检查、未初始化的变量引用检测、自动垃圾收集等。因此，使用它十分易于编写Web 应用程序［10］。

1.1 调用Excel的COM 组件

一个.NET 组件就是一个.NET下的DLL，它包含的不仅是运行程序本身，更重要的是包含这个DLL的描述信息，而一个COM 组件是用其类库储存其描述信息。这些COM 组件都是非受管代码，要在C#中使用这些非受管代码的COM 组件，就必须把它们转换成受管代码的.NET 组件［11-12］。所以在用C#调用Excel 表格之前，必须完成从COM 组件的非受管代码到受管代码的类库的转换。通过引用Microsoft Excel 11.0 Object Library，Visual C#.NET 会自动产生相应的.NET 组件文件，以后即可正常使用［13］。

1.2 Excel 对象模型［14］

Excel 对象模型有很多，本系统中主要用到的对象模型如下。

(1)Application 对象:Application 对象是Excel 对象模型中的顶级对象。使用Application 对象可以确定或指定应用程序级属性或执行应用程序级方法。Application 对象也是访问Excel 对象模型其它部分的基础。

(2)Workbooks 对象和Workbook 对象:Workbooks 对象包含Microsoft Excel中所有当前打开的Workbook 对象，是Workbook 对象的集合。Workbook对象是Workbooks 集合的成员，对象代表Microsoft Excel 工作簿。

(3)Worksheets 对象和Worksheet 对象:Worksheets 对象包含工作簿中所有的Worksheet 对象，是Worksheet 对象的集合。Worksheet 对象是Worksheets集合的成员，代表一张工作表。

(4)Range 对象:Range 对象代表某一单元格、某一行、某一列、某一选定区域(该区域可包含一个或若干连续单元格区域)，或者某一三维区域。

(5)ChartObjects 对象和ChartObject 对象:ChartObjects 对象为指定的图表工作表或工作表上所有ChartObject 对象的集合。每个ChartObject 对象代表一个嵌入图表。ChartObject 对象的作用是作为Chart对象的容器。ChartObject 对象的属性和方法控制工作表上嵌入图表的外观和尺寸。

1.3 打开文档

要比较原始文件和答案文件的不同点，就要获取它们的对象属性。在VS 工程中添加引用“Microsoft Excel 11.0 Object Library”后即可通过以下方法访问Excel 对象模型。

(1)定义一个Excel 应用程序对象:

(2)打开一个已有的Excel 文件(假设excelfilepath为文件的路径):

(3)读取属性。

基于面向对象技术，获取指定对象的相应属性的值，是自动出题的关键，以下是获得几个常用属性的示例，更详尽的技术，可查阅Office 相应软件的VBA中的帮助信息。

①单元格中的字体设置由Font 对象获得，字体设置中包括Name 属性—字体类型，Colour 属性—字体类型等。

②公式操作由Range 对象的Formula 属性获取设置在每一个单元格中数据类型为字符串类型的公式。

2 设计思路

2.1 知识点设计

一个Excel 文件包含多个工作表，每个工作表的结构特性又包含单元格格式、数据处理、图表格式3部分，本设计的出题知识点基本囊括了计算机一级考试需要掌握的Excel 知识点，主要内容如下:(1)工作表的操作。例如:插入、删除、重命名、移动工作表。(2)单元格的操作。例如:单元格合并、单元格对齐、单元格字体、单元格边框设置、单元格图案设置等。(3)公式函数操作。(4)排序操作。(5)筛选操作。例如:自动筛选操作和高级筛选操作。(6)图表操作。例如:图表类型、数据区域、系列产生在行或列、图表标题、分类轴标题、数值轴标题的设置等。

2.2 出题界面设计

本系统出题界面十分简单，如图1所示，出题界面只有知识点设置、上传原始文件、上传答案文件3部分。出题者只需要上传原始文件、答案文件，然后设置所要考查的知识点就可以完成出题。

图1 界面设计图

2.3 出题操作流程

具体流程图如图2所示。

图2 出题操作流程图

具体的出题步骤如下:

(1)选择本次出题需要包含的知识点，可以多选。

(2)将原始文件的副本按照要求正确操作一遍，保存为答案文件。

(3)上传原始文件和答案文件，进行比较。依次比较原始文件和答案文件中Workbook 对象，Worksheets 对象，Worksheet 对象和Range 对象中的属性值，自动生成题目信息。

(4)若自动生成的题目信息不通顺或表述不清，则可以编辑题目信息，然后设置每道小题的分数。

(5)点击“保存按钮”，将试题存到试题库中，完成出题。

2.4 生成题目信息的方法

2.4.1 出题规则

图3 划分区域示例

生成的题目信息应该遵循以下原则:若多个单元格都设置了相同的操作，则把能合并的操作区域尽量合并。例如:若出题想要将如图3所示的区域设为黑体，从图中可知最少可划分为5个独立的连续区域。如果不合并操作区域，则会生成很多条“设置为黑体”的题目信息，令题目看起来非常繁琐，不能准确表达老师的意图，所以题目信息应该尽量简化。上述示例中的题目信息应该表述为:

(1)将区域B2:C2中的字体设为黑体。

(2)将区域A3:A4中的字体设为黑体。

(3)将区域C3:D4中的字体设为黑体。

(4)将区域B4:B6中的字体设为黑体。

(5)将区域C3:D4中的字体设为黑体。

(6)将区域C5:C6中的字体设为黑体。

由于本系统中的操作区域是自动生成的，因此难点在于自动获取多个独立的连续区域，并且使连续区域个数达到最少。

2.4.2 生成题目信息的步骤

生成题目信息的步骤如下:

(1)根据知识点确定需要比较的对象，例如:对于工作表操作，比较的对象就是Worksheets;对于单元格操作，比较的对象就是Range。

(2)对于对象中的每一种属性，获取答案文件中这一属性值相同的多个独立的连续区域。

(3)将答案文件中的每一个独立的连续区域和原始文件中相对应的区域的属性值比较，若相等，则不生成题目信息;若不相等，则根据属性值的变化生成题目信息。

2.4.3 生成操作区域的算法

2.4.2 节中的步骤关键在于第(2)步，目的在于合并设置相同操作的区域。算法比较的对象是区域，在Excel中称为Range，Range中包含各种属性。属性的变化就是算法比较的结果。因为事先并不知道哪些Range 区域的哪些属性发生了变化，因此，对于一个随机的Excel 工作表的某个特定Range 区域，须将所有需要比较的属性逐一比较，才能判定该区域的属性是否发生了变化，并且是哪些具体属性发生了变化，例如:图4中的［A1:B4］的填充色。只有属性相同并且彼此行列相邻的一组单元格才能组成一个Range，但这种区域划分对于一个随机的Excel 工作表是无法事先确定的，而且还有可能存在交叉区域的情况(如图4的［A1:B4］和［B1:C4］)，因此，需要通过遍历所有可能的区域组合，将属性相同的单元格组合成为一个区域。

图4 遍历区域

以前自动出题系统所用的算法首先遍历最大区域(如图4中的［A1:C4］)，查看其所有属性是否发生了变化，如果某些属性发生了变化，就不再对这些属性进行后续的遍历;其次遍历后续子区域［A1:C3］、［A1:C2］、［A1:C1］、［B1:C4］、［B1:C3］、［B1:C2］…，遍历的方法相同。这样的算法既不会发生属性的漏盘，又可以产生有效的区域组合。但是，该算法需要执行多次遍历过程，当表格较大时，速度会严重受到影响，出一套题要数十分钟。

本文设计的算法使用了一个标记数组，只需一次遍历Excel中的已使用区域就可以实现上述过程的第(2)步，完全克服了以前算法的缺点，几十秒内就可以完成出题，而且可以保证不会遗漏属性。具体算法描述如下:

(1)分别获取原始文件和答案文件的Sheet.UsedRange 即已使用区域，求这两个区域的交集，记为Range［row，cols］其中rows 指区域的行数，cols 指区域的列数，执行下一步。

(2)建立一个和Range［row，cols］大小相同的Range 对象记为newRange，获取答案文件Range［row，cols］中每一个单元格中的字体属性值，并填充到newRange 相应的单元格中。

(3)建立一个长度为cols的标记数组记为mark，数组的初始值都为0(注:若:mark [ i]=j 代表第i 列单元格的连续区域已经查找到了第j 行)。

(4)把Excel 工作表中的第一行的行号记为0，第A 列的列号记为0，开始查找newRange中数值相同的连续方形区域，设r=0，c=0。(注:r为行号，c为列号)。

(5)当r ＜rows，c ＜cols 时从第r 行第c 列开始，若r≥mark［c］从第r 行的第一个单元格开始，向右查找与它的属性值相同的连续单元格的最大列号，记为maxcol，把标记数组mark［0］～mark［cols-1］置为r，把(cell［r，c］，cell［r，max col］)记为一个待扩展的区域，执行步骤(6);若r ＜mark［c］，令c=c+1，执行步骤(5)。

图5 自动生成的题目信息

(6)当r ＜row，c ＜cols 时，从第r+1 行第c 列开始，向右查找与上述属性值相同的连续单元格的最大列号，若小于maxcol，则把第(5)步中的连续区域保存为一个扩展完毕的独立连续区域，然后令r=r+1，c=maxcol+1，执行步骤(5);若等于maxcol，则把mark［0］～mark［cols-1］置为r+1，并把第(5)步中的连续区域扩展为(cell［r，c］，cell［r+1，max col］)。

使用该方法生成的题目信息界面如图5所示。

题目信息包括如下内容:(1)试题编号。(2)试题的文字描述。如果系统生成的文字词不达意，出题者可以做出适当的修改。(3)题目类型，如公式计算、字体颜色、字体大小、单元格对齐方式等。(4)操作区域，该区域就是要求考生进行操作的区域。(5)每道小题的分数，该分数只是系统默认的值，出题者根据需要可以适当修改。

3 结束语

本系统最大的特点是自动化程度高，出题者不用手动设置操作区域和属性值，题目信息即可自动生成。使用本系统，出题者只需设置好想要考查的知识点，然后按照正确答案操作一遍，就可以自动生成完整的题目信息，省去了额外添加试题文本信息操作，大大减少了出题工作量。本系统还有两个明显的特点:(1)出题者出一次题就可包含多个知识点。(2)出题者可对自动生成的试题文字信息进行修改润色，以便更准确地表达本次出题操作的真实意图，从而保证出题的高效准确。

［1］陈秀敏，闫忠文，崔勇，等.基于网络环境的计算机技能考试系统的设计与实现［J］.河北科技师范学院学报，2006，20(1):48-51.

［2］李旌燕.EXCEL 操作题自动出题和自动阅卷系统的设计与实现［D］.长沙:中南大学，2009.

［3］李美满，易德成.通用考试系统开放题库与组卷及评卷算法研究［J］.计算机工程与设计，2005，26(10):2743-2745，2755.

［4］李弋，姜志刚，赵一鸣.基于规范的考试系统［J］.计算机工程，2004，30(10):195-197.

［5］肖洋，王骁，刘凤新.在线考试组卷算法研究［J］.北京化工大学学报:自然科学版，2006，33(4):44-47.

［6］彭作民.EXCEL 文档自动阅卷评分算法设计［J］.南京师范大学学报:工程技术版，2007，7(3):70-73.

［7］刘建舟，刘晓华.主观题自动批改技术的研究［J］.湖北工业大学学报，2006，21(4):62-65.

［8］李旌燕，王国军.EXCEL 操作题自动出题系统设计［J］.惠州学院学报:自然科学版，2008，28(3):66-70.

［9］杨秋叶，禤世丽.EXCEL 操作题出题系统设计与实现［J］.新余学院学报，2011，16(3):13-15.

［10］王东明，葛武滇.Visual C#.NET 程序设计与应用开发［M］.北京:清华大学出版社，2008:18.

［11］杜秋霞.Visual C#的Excel 编程［J］.青海师范大学学报:自然科学版，2006(3):53-55.

［12］臧武军.用Visual C#调用Excel的编程方法［J］.计算机与网络，2007，33(21):62-63.

［13］贲黎明.Excel 操作题自动阅卷系统的设计与实现［J］.常熟理工学院学报:自然科学版，2011，25(8):115-120.

［14］Microsoft Inc.Excel 对象模型概述［DB/OL］.http://msdn.microsoft.com/zh-cn/library/wss56bz7.aspx，2013-07-11.