信息化技术在教学管理工作中的应用实践
——VC 应用Automation技术完成Excel复杂管理工作
摘要：电子表格软件Excel以其操作简单、功能强大，被全社会广泛应用。但随着应用深入，用户日益扩大的专业化需求与相对滞后的Excel通用工具之间的矛盾越来越突出。文章运用Automation技术，借助面向对象编程系统VC ，精心制作Excel文件操作类；使程序开发人员能够将注意力集中于需要解决的问题，而不是如何操作Excel文件上。作者结合实际工作，以“正方现代教学管理信息系统”导出的全院学生成绩为基础，应用此操作类编写应用软件，分学科、教师、班级，完成学院级期末考试不及格率的统计，为评教、评学工作提供数据依据。
关键字：VC ；Automation；Excel；数据统计；正方现代教学管理信息系统
一、引言
Excel 是微软办公套装软件Office的一个重要组成部分，它可以进行各种数据的处理、统计分析操作。因其入门简单、集成工具功能强大，被广泛地应用于管理、统计、财经、金融等众多领域。但随着Excel应用范围不断扩大，应用层次不断深入，专业领域中许多工作无法直接通过集成工具得到满意的结果。如何高效、准确地获得管理数据，已经成为衡量员工能力和工作质量的重要标准。
Automation自动化技术是微软公司推出的一个技术标准，是OLE技术的进一步扩展。自动化技术使应用程序能够对另一个应用程序中实现的对象进行操作，或者将对象公开以便可以对其进行操作。自动化服务器是向其他应用程序（称为自动化客户端）公开可编程对象（称为自动化对象）的应用程序 [1]。Excel被设计成为一个自动化服务器，可将其内部对象公开给其他应用程序，以供调用。
VC 是一种面向对象的可视化集成编程系统。使用VC 编写程序，可以通过自动化技术对Excel文件直接操作，能够满足各领域对数据管理工作的复杂需求。本文首先构造Excel操作类，使读者能够将注意力集中于需要解决的问题上，从而提高程序开发效率，缩短开发周期；然后结合实际工作，应用此类编写学院级期末考试不及格率的统计程序。
文中程序均已通过VC 2010编译，并能够在Excel 2003支持下完成预设功能。
二、Excel层次结构
要通过Automation技术对Excel进行操作，必须与Excel对象模型提供的对象进行交互。Excel提供了一百多个可能需要与之交互的对象，但程序设计主要集中在以下六个，如图1所示。[2]
Application对象表示整个应用程序，处于模型的最顶层。Workbooks对象作为所有Workbook对象的集合包含在Application对象中。Worksheets对象作为所有Worksheet对象的集合包含在Workbook对象中。Range代表一个或者若干连续单元格区域。[3]
三、 构建ExcelOperate类
ExcelOperate类通过Automation技术直接调用Excel内部函数，完成查找、读取、写入、存储等基本操作。它由头文件ExcelOperate.h和定义文件ExcelOperate.cpp组成。头文件用于保存类中成员变量、函数的声明；定义文件主要保存成员函数的程序实现。
（一）ExcelOperate类函数调用流程
根据Excel层次结构，要对其进行操作必须按顺序创建和关联一系列对象。ExcelOperate类函数调用流程如图2所示。
（二）头文件ExcelOperate.h
#include 'comdef.h'
#include <atlstr.h>
class ExcelOperate
{
public:
CRange m_TemRge; //用于保存用户设定区域
ExcelOperate(); //构造函数
virtual ~ExcelOperate(); //析构函数
static void InitExcel(); //初始化Excel.Application对象
bool Open(CString FileName); //打开Excel数据文件
bool LoadSheet(int iIndex); //载入已打开Excel文件中第iIndex个表单
long *FindCell(CString CellContext); //全表单查找
long *FindInRge(CString CellContext,long StartX,long StartY,long EndX,long EndY);//范围查找
void SaveExcel(); //保存文件
void SaveNewFile(CString FileName); //新建Excel文件，并保存
CString GetCell(int iRow, int iColumn); //读取单元格数据
void WriteInCell(long x,long y,CString str);//写入单元格数据
void AppendDataRow(CString str1,CString str2,CString str3,CString str4,CString str5);//向表单中追加数据行
int GetRowCount(); //取得当前表单已经使用的总行数
void CloseBook(); //关闭Excel文件
void ShowInExcel(bool bShow); //设置是否在程序运行过程中显示Excel文件
void SetRange(VARIANT x,VARIANT y); //将一个矩形区域保存到成员变量m_TemRge中
CString ColumnNumToAlphameric(long row,long col);//将数字坐标转换为字母列数字行的形式，如“A7”
private:
static CApplication m_ExcelApp; //静态Application对象
CWorkbooks m_Books; //工作簿组对象
CWorkbook m_Book; //工作簿对象
CWorksheets m_sheets; //表单组对象
CWorksheet m_sheet; //表单对象
CRange m_Rge; //区域对象，保存表单整个区域
CString m_FileName; //保存已经打开的Excel文件名
};
（三）程序文件ExcelOperate.cpp
1.引入头文件
#include 'StdAfx.h'
#include 'ExcelOperate.h'
2.静态成员初始化
CApplication ExcelOperate::m_ExcelApp = NULL;
3.构造和析构函数
ExcelOperate::ExcelOperate() //构造函数
{
}
ExcelOperate::~ExcelOperate() //析构函数
{
m_Rge.ReleaseDispatch();
m_sheet.ReleaseDispatch();
m_sheets.ReleaseDispatch();
m_Book.ReleaseDispatch();
m_Books.ReleaseDispatch();
m_ExcelApp.Quit ();
m_ExcelApp.ReleaseDispatch();
}
4.初始化Excel服务器
初始化Excel.Application对象，如果过程中出现错误，则提示失败信息。由于m_ExcelApp为静态成员，所以在整个程序执行过程中，不论需要声明多少个类的对象，只需初始化一次即可。
void ExcelOperate::InitExcel()
{
if (!m_ExcelApp.CreateDispatch(_T('Excel.Application'),NULL))
{
AfxMessageBox(_T('创建Excel服务失败!'));
exit(1);
}
}
5.打开Excel文件
首先，使用已经完成初始化的Application对象m_ExcelApp创建工作簿组对象（Workbooks），并将其关联到类成员变量m_Books。然后，将参数FileName指示的Excel文件作为一个工作簿（Workbook），添加到m_Books中，并关联到类成员变量m_Book；提取其中所有表单（Worksheets），添加到类成员变量m_sheets中。最后，将文件名保存到成员变量m_FileName备用。
bool ExcelOperate::Open(CString FileName)
{
m_Books.AttachDispatch(m_ExcelApp.get_Workbooks(),true);
LPDISPATCH lpDis = NULL;
lpDis = m_Books.Add(_variant_t(FileName));
if (lpDis)
{
m_Book.AttachDispatch(lpDis);
m_sheets.AttachDispatch(m_Book.get_Worksheets(),true);
m_FileName=FileName;
return true;
}
return false;
}
6.载入表单
引用存有指定Excel文件所有表单的成员变量m_sheets，取得指定索引序号iIndex的表单，将其关联到类成员变量m_sheet。将该表单中被使用单元格区域关联到类成员变量m_Rge。至此，对Excel文件操作所必需的对象均已构建、关联完毕。
bool ExcelOperate::LoadSheet(int iIndex)
{
LPDISPATCH lpDis = NULL;
m_Rge.ReleaseDispatch();
m_sheet.ReleaseDispatch();
lpDis = m_sheets.get_Item(_variant_t((long)iIndex));
if (lpDis)
{
m_sheet.AttachDispatch(lpDis,true);
m_Rge.AttachDispatch(m_sheet.get_Cells(), true);
return true;
}
return false;
}
7.全表单查找
在整个表单的范围内，精确查找参数CellContext内容。如果找到，则返回第一个匹配单元格的横纵坐标；如果没找到，则横纵坐标均返回-1。
long * ExcelOperate::FindCell(CString CellContext)
{
LPDISPATCH lpDis = NULL;
long *a = new long [2];
a[0]=long(-1);
a[1]=long(-1);
CRange myRange=m_Rge;
lpDis = myRange.Find (_variant_t(CellContext),vtMissing,vtMissing,vtMissing,vtMissing,_variant_t((long)1),vtMissing,vtMissing,vtMissing);
if (lpDis)
{
myRange.AttachDispatch(lpDis,true);
long *b = new long [2];
b[0]=myRange.get_Row ();
b[1]=myRange.get_Column ();
CString m_Temp;
m_Temp = GetCell(b[0],b[1]);
if (m_Temp.GetLength () == CellContext.GetLength ())
{
a[0]=b[0];
a[1]=b[1];
}
}
myRange.ReleaseDispatch ();
return a;
}
8.范围查找
从单元格坐标（StartX , StartY）到（EndX , EndY）的矩形区域中查找CellContext内容，如果找到，则返回第一个匹配单元格的横纵坐标；如果没找到，则横纵坐标均返回-1。
long * ExcelOperate::FindInRge(CString CellContext,long StartX,long StartY,long EndX,long EndY)
{
LPDISPATCH lpDis = NULL;
long *a = new long [2];
a[0]=long(-1);
a[1]=long(-1);
if (StartX > EndX || StartY > EndY)
{
return a;
}
if (StartX == EndX && StartY == EndY)
{
CString str;
str = GetCell(StartX,StartY);
if (CellContext == str)
{
a[0] = StartX;
a[1] = StartY;
}
return a;
}
CString StartPoint,EndPoint;
StartPoint = ColumnNumToAlphameric(StartX,StartY);
EndPoint = ColumnNumToAlphameric(EndX,EndY);
SetRange(_variant_t(StartPoint),_variant_t(EndPoint));
lpDis = m_TemRge.Find (_variant_t(CellContext),vtMissing,vtMissing,vtMissing,vtMissing,_variant_t((long)1),vtMissing,vtMissing,vtMissing);
if (lpDis)
{
m_TemRge.AttachDispatch(lpDis,true);
a[0]=m_TemRge.get_Row ();
a[1]=m_TemRge.get_Column ();
}
m_TemRge.ReleaseDispatch ();
return a;
}
9.读取单元格内容
取得表单中坐标为（iRow , iColumn）的单元格内容。由于单元格中存储的数据类型不同，所以程序需要分别判断、转化为字符串型数据后输出。
CString ExcelOperate::GetCell(int iRow, int iColumn)
{
CRange range;
range.AttachDispatch(m_Rge.get_Item (COleVariant((long)iRow),COleVariant((long)iColumn)).pdispVal, true);
COleVariant vResult =range.get_Value2();
CString str;
if(vResult.vt == VT_BSTR) //字符串
{
str=vResult.bstrVal;
}
else if (vResult.vt==VT_INT) //整型数字
{
str.Format(_T('%d'),vResult.pintVal);
}
else if (vResult.vt==VT_R8) //8字节的数字
{
str.Format(_T('%.1f'),vResult.dblVal);
}
else if(vResult.vt==VT_DATE) //时间格式数据
{
SYSTEMTIME st;
VariantTimeToSystemTime(vResult.date, &st);
}
else if(vResult.vt==VT_EMPTY) //单元格为空
{
str='NULL';
}
range.ReleaseDispatch();
return str;
}
10.向指定单元格中写入数据
向当前表单坐标为（x , y）的单元格写入数据str。
void ExcelOperate::WriteInCell(long x,long y,CString str)
{
m_Rge.put_Item (_variant_t((long)x), _variant_t((long)y), _variant_t(str));
}
11.追加数据行
调用类中WriteInCell函数，向表单追加数据行。可根据实际情况添加重载函数，用于追加所需数量列的数据行。由于空表单和只有第一行数据的表单在取得被使用的总行数时，都会返回数字1，则需要在程序中加以判断：如果表单总行数为1，则根据左上角单元格是否被占用来断定表单中是否存在数据。
void ExcelOperate::AppendDataRow(CString str1,CString str2,CString str3,CString str4,CString str5)
{
long m_Row;
m_Row = GetRowCount();
if (GetCell(1,1)!=_T('NULL'))
m_Row ;
WriteInCell (m_Row,1,str1);
WriteInCell (m_Row,2,str2);
WriteInCell (m_Row,3,str3);
WriteInCell (m_Row,4,str4);
WriteInCell (m_Row,5,str5);
return;
}
12.保存文件
首先设置隐藏保存文件对话框，然后调用表单的SaveAs函数保存表单。
void ExcelOperate::SaveExcel()
{
m_ExcelApp.put_DisplayAlerts (FALSE);
m_sheet.SaveAs(m_FileName,vtMissing,vtMissing,vtMissing,vtMissing,
vtMissing,vtMissing,vtMissing,vtMissing,vtMissing);
}
13.关闭工作簿
工作簿（即Excel文件）使用后应及时关闭，否则数据将驻留内存，导致系统性能下降。
void ExcelOperate::CloseBook()
{
m_Book.Close (vtMissing,vtMissing,vtMissing);
}
14.取得表单中已使用的总行数
int ExcelOperate::GetRowCount()
{
CRange range;
CRange usedRange;
usedRange.AttachDispatch(m_sheet.get_UsedRange(), true);
range.AttachDispatch(usedRange.get_Rows(), true);
int count = range.get_Count();
usedRange.ReleaseDispatch();
range.ReleaseDispatch();
return count;
}
15.设置程序运行时是否显示Excel窗口
如将参数bShow设置为true，则程序运行时，被打开的Excel文件将显示在屏幕上；如设置为false，则被打开的Excel文件只调入内存，不会在屏幕上显示。
void ExcelOperate::ShowInExcel(bool bShow)
{
m_ExcelApp.put_Visible(bShow);
}
16.单元格坐标转换
本函数将数字形式的横纵坐标，转换成字母列数字行的形式输出。如坐标（20,7）转换后为T7。
CString ExcelOperate::ColumnNumToAlphameric(long row,long col)
{
CString m_str;
if( col > 26 )
{
m_str.Format (_T('%c%c%d'),'A' (col-1)/26-1,'A' (col-1)%26,row);
}
else
{
m_str.Format (_T('%c%d'),'A' (col-1)%26,row);
}
return m_str;
}
17.设置区域
给类成员变量m_TemRge设置区域。参数x、y是字母列和数字行的单元格坐标组合，如“A7”。必要时，可以先调用单元格坐标转换函数，转换坐标形式后，再使用本函数设置区域。
void ExcelOperate::SetRange(VARIANT x,VARIANT y)
{
m_TemRge.ReleaseDispatch ();
m_TemRge = m_sheet.get_Range (x,y);
}
四、 考试成绩不及格率统计
（一）需求分析
目前，广泛使用的“正方现代教学管理信息系统”是集学籍、课程、成绩等多方面管理功能为一体的综合系统。其中，学生成绩管理功能非常强大，不但支持网络录入，还包含大量查询和统计功能。但教学管理的需求会根据实际情况不断发生变化。日前，部门领导要求将期末必修考试课成绩，以教师、科目、班级的不同分别统计不及格率；以此作为评价任课教师教学质量的部分依据。考虑到教师可能给不同系、多个班讲授多门课程，统计完成后应输出如下信息：“教师编号、教师姓名、课程名称、班级、不及格率”。
虽然“信息系统”不能完成此项工作，却能将保存在数据库中的学生成绩以Excel表单的形式导出。按在校生1万，每名学生、每学期平均6门课程计算，一个学期就会产生6万条成绩记录。考虑到Excel对表单总行数的限制，将各年级、系别的学生成绩导出为不同数据表，从而形成数据源文件组；并以此为基础，编写程序完成统计工作。
（二）总体设计
用户将需要查询的任课教师信息保存到Excel表单，在用户界面中完成设置；主程序借助ExcelOperate类对Excel数据源文件组进行查询、统计，并将统计结果保存为Excel文件的形式供用户查阅，如图3所示
