利用ExcelVBA插件实现证件信息批量校验

利用ExcelVBA插件实现证件信息批量校验摘要：使用excel表格实现证件申请信息批量填报，并按要求对有关内容进行有效性校验。

通过分析jpg格式图片文件的结构，自行编写代码获取图片尺寸等信息。

利用excelvba插件，实现了证件提报信息批量校验。

关键词：excelvba插件；jpg图片文件；有效性校验中图分类号：tp311 文献标识码：a 文章编号：1007-9599 （2012）24-0004-031 引言举办重要会议、活动时，通常需要为与会人员制作发放专用证件。

因为与会人员数量较多，逐一录入效率很低，一般情况下采用批量提报的方式采集数据。

目前，我单位使用公安部第一研究所开发的证件管理软件制作证件，该软件使用excel表格批量导入制证信息。

申请单位按要求将申请人姓名、身份证号码、单位等信息填入既定格式的excel表格，与申请人照片保存在同一文件夹内打包提报。

照片文件为jpg格式，以申请人姓名加“-”再加出生日期命名，如“张三-19810213.jpg”。

为了保证照片质量，通常需要对照片尺寸和文件大小提出要求，如照片尺寸指定为413×579像素，文件大小不超过300k。

这种数据采集方式大大减轻了制证人员的工作量，提高了工作效率。

在实际应用中我们也遇到了一些问题：一是导入信息时缺少照片。

有些是因为确实漏报了照片，有些是因为照片不是jpg格式，更多的是因为照片文件命名错误，比如姓名、出生日期与表格内容不一致，文件名中有多余空格，以及因为扩展名被隐藏而在文件名中多输入了“.jpg”，把照片文件命名成类似“张三-19810213.jpg.jpg”的情况。

二是提交的照片不符合要求。

普遍存在尺寸不符的情况，并且很多照片的宽高比例不合适，制作证件时因为拉伸而严重变形。

三是普遍反映填报信息时效率不高。

特别是命名照片文件时，一一核对出生日期比较麻烦，很容易出现错误。

为了解决以上问题，本人对批量提报表格进行了改进。

在使用excel表格的有效性规则防止在表格内输入无效内容的基础上，利用excelvba设计校验程序，对申请人信息以及照片的有效性进行校验，对填报错误或不完整数据进行提醒或自动修正，从而降低了信息填报的难度，提高了提报数据的质量。

2 excelvba及vba插件介绍vba全称为“visualbasicforapplication”，是一种依附于应用软件的二次开发语言。

作为vb家族的成员之一，它是一种面向对象的程序语言，用一种所见即所得的方式编写代码，这使它在学习和使用方面都非常简单。

excel是目前制表软件中最优秀、市场占有率最高的一款软件，这归功于它拥有强大的制表功能，还归功于它提供了二次开发平台excelvba，允许用户自行开发excel本身不具备的功能，大大提升工作效率。

通过vba进行二次开发，可以强化excel功能，将某些烦杂或者重复的日常工作简化，还可以开发商业插件或者小型应用软件，其中比较常见和易于实现的就是通过开发应用vba插件，来简化自己的实际工作。

excel插件是利用vba开发的外置工具，通常是xla、xlam或者dll格式，其中xla和xlam插件可以直接用excel开发，而dll插件通常采用vb或才c++、等开发。

相对于excel的内置功能，vba插件具有很多优势。

它可以对操作对象进行批量处理，可以一键完成多个任务，可以将复杂的任务简单化，可以提升工作表数据的安全性和准确性，使工作更轻松，运算更快速、准确。

虽然vba插件具有以上优势，但它在某些方面也有一些限制。

比如说通用性方面，因为开发插件通常是个人行为，通用性可能不够完善，由于开发者测试的次数少以及测试条件不足等原因，导致某些插件藏有隐含缺陷。

另外，excelvba是依附于excel主体程序的附属程序，它可以开发强化excel功能的程序，但是不能开发脱离excel 单独存在的软件，不能用于开发全新而专业的应用程序。

3 利用excelvba实现证件照片批量校验为了解决前面提及的几个主要问题，需要利用excelvba编写三个函数（或过程），分别实现查找照片、分析尺寸和批量校验功能，并设置快捷键以方便调用。

3.1 查找照片并返回文件大小该函数根据申请人姓名和出生日期，在该申请表所在目录内查找申请人照片，并返回照片文件大小。

jpg格式文件有两种常见的扩展名：jpg和jpeg，编写程序时均需考虑。

以姓名为“张三”，出生日期为“19810215”为例，首先查找文件名为“张三-19810215.jpg”文件，如果找到，则返回文件大小，否则依次查找文件名为“张三.jpg”、“张三-19810215.jpg.jpg”、“张三.jpg.jpg”、“张三-19810215.jpeg”、”张三.jpeg”、“张三-19810215.jpg.jpeg”、“张三.jpg.jpeg”的文件，找到后将其文件名更改为“张三-19810215.jpg”，并返回文件大小。

如果以上文件都未找到，则返回-1。

这样既能够自动修正一些输入错误，提高容错能力，还能够简化操作，除了在申请人姓名相同时需要输入完整文件名外，其他照片仅需使用姓名命名，程序将自动为其补充完整。

该函数的主要代码如下：‘参数iname为申请人姓名，ibirthday为出生日期的8位阿拉字符串functionfilelength（byvalinameasstring，byvalibirth dayasstring）aslongdimfilename1asstring，filename2asstring‘查找文件名类似“张三-19810215.jpg”的文件，如果找到返回文件大小filename1=activeworkbook.path+”＼”+name+”-”+birthday+”.jpg”ifdir（filename1）”“thennamefilename2asfilename1filelength=filelen（filename1）exitfunctionendif……省略依次查找文件名类似“张三.jpg”、“张三.jpg.jpg”等文件的过程filelength=-1 ‘未找到申请人照片，返回-1endfunction3.2 获取jpeg照片尺寸jpg即jpeg，是由jointphotographicexpertsgroup（联合图像专家组）制定的图像压缩标准，是常用的图像文件格式之一。

jpeg 文件是分段存储的，段的多少和长度不是一定的，段的排列也没有严格的顺序。

除了图像开始（soi）和图像结束（eoi）以外，每个段都是由段标识、段类型、段长度和段内容组成。

段标识1个字节，是十六进制固定值0xff，每个段标识前可以添加数量不限的0xff 填充字节。

段类型也是1个字节，不同的段有不同的值。

紧接其后的2个字节存放的是该段长度（以字节为单位，不包括段标识和段类型），使用motorola方式存储，即高位在前，低位在后。

图像开始段和图像结束段只包含段标识和段类型组成，都是只有2个字节。

段的类型很多，但是大多数的jpeg文件只包含几种，如soi（图像开始）、app0（应用数据块）、dqt（量化表）、sof0（帧图像开始）、dht（霍夫曼表）、sos（扫描线开始）以及eoi（图像结束）等，其中图像的大小存储于sof0段中。

同其它段一样，sof0段的起始字节值为0xff，第二字节即段类别字节值为0xc0，图像的高度和宽度（以像素为单位）分别存放于该段的第6、7和第8、9字节内，也是以motorola方式存储。

因为我们只是需要获取图像的高度和宽度，因此只需在jpeg文件中找到该段，并根据其相应位置的值计算图像高和宽即可。

当然，在查找sof0段以前，首先必须判断该文件是不是jpeg文件，也就是判断该文件开始两个字节的值是否为0xff和0xd8（即是否为soi段）。

该函数的主要代码如下：‘参数iwidth和iheight以地址引用方式返回照片宽度和高度‘函数返回值：0正确，1未找到文件，2文件格式错误functionphotosize（ifilenameasstring，iwidthasinteg er，iheightasinteger）asintegerdimfilebytes（）asbytedimfilelengthaslong，indexaslong，filenumberas integer ‘如果文件不存在，返回1ifilename=activeworkbook.path+”＼”+ifilenameifdir（ifilename）=““thenphotosize=1：exitfunction‘以二进制形式打开文件，如果错误返回1filenumber=freefile ‘获取可用文件句柄号onerrorresumenextopenifilenameforbinaryasfilenumberiferr.number255orfilebytes（2）=filelengththenexitdo‘否则指针移至下一段开始index=index+clng（filebytes（index+1））*256+filebytes （index+2）+1else ：index=filelength：endif ‘数据错误，指针移到数组结尾loopwhileindex’如果未到数组结尾，说明找到了sof0段，计算并返回照片尺寸ifindex<filelengththen‘图像帧段第6、7两字节的数值是图像高度iheight=cint（clng（filebytes（index+4））*256+filebytes （index+5））‘图像帧段第8、9两字节的数值是图像宽度iwidth=cint（clng（filebytes（index+6））*256+filebytes （index+7））photosize=0 ‘成功获取照片尺寸，返回0else：photosize=2：endif’否则，文件格式错误，返回2 endfunction调用该函数时，需要先定义两个整型变量，然后以引用参数的方式返回图像高度和宽度。

3.3 证件信息批量校验主要是按照有关要求，对表格内的所有申请人信息逐一进行必填项和有效性检查，并根据申请人姓名和出生日期查找校验申请人照片。

对不符合要求的信息，用背景色加以突出显示，并以批注形式说明原因，方便填报人检查修改。

具体代码不再赘述。

3.4 使用快捷键调用校验过程在excel2003中，可以采取自定义工具栏或自定义菜单的方式调用校验过程，非常直观、方便。

但是excel2010用功能区替换了早期版本的命令栏，将工具栏和菜单都屏蔽了，尽管它仍然允许通过vba代码产生自定义菜单和工具条，但是自定义菜单和工具栏都显示在加载项功能区中，不直观，使用不便。