摘要：使用Excel表格實(shí)現(xiàn)證件申請(qǐng)信息批量填報(bào)，并按要求對(duì)有關(guān)內(nèi)容進(jìn)行有效性校驗(yàn)。通過(guò)分析JPG格式圖片文件的結(jié)構(gòu)，自行編寫(xiě)代碼獲取圖片尺寸等信息。利用ExcelVBA插件，實(shí)現(xiàn)了證件提報(bào)信息批量校驗(yàn)。

關(guān)鍵詞：ExcelVBA插件；JPG圖片文件；有效性校驗(yàn)

中圖分類(lèi)號(hào)：TP311 文獻(xiàn)標(biāo)識(shí)碼：A 文章編號(hào)：1007-9599 （2012） 24-0004-03

1 引言

舉辦重要會(huì)議、活動(dòng)時(shí)，通常需要為與會(huì)人員制作發(fā)放專(zhuān)用證件。因?yàn)榕c會(huì)人員數(shù)量較多，逐一錄入效率很低，一般情況下采用批量提報(bào)的方式采集數(shù)據(jù)。目前，我單位使用公安部第一研究所開(kāi)發(fā)的證件管理軟件制作證件，該軟件使用Excel表格批量導(dǎo)入制證信息。申請(qǐng)單位按要求將申請(qǐng)人姓名、身份證號(hào)碼、單位等信息填入既定格式的Excel表格，與申請(qǐng)人照片保存在同一文件夾內(nèi)打包提報(bào)。照片文件為JPG格式，以申請(qǐng)人姓名加“-”再加出生日期命名，如“張三-19810213.JPG”。為了保證照片質(zhì)量，通常需要對(duì)照片尺寸和文件大小提出要求，如照片尺寸指定為413×579像素，文件大小不超過(guò)300K。這種數(shù)據(jù)采集方式大大減輕了制證人員的工作量，提高了工作效率。

在實(shí)際應(yīng)用中我們也遇到了一些問(wèn)題：一是導(dǎo)入信息時(shí)缺少照片。有些是因?yàn)榇_實(shí)漏報(bào)了照片，有些是因?yàn)檎掌皇荍PG格式，更多的是因?yàn)檎掌募e(cuò)誤，比如姓名、出生日期與表格內(nèi)容不一致，文件名中有多余空格，以及因?yàn)閿U(kuò)展名被隱藏而在文件名中多輸入了“.JPG”，把照片文件命名成類(lèi)似“張三-19810213.JPG.JPG”的情況。二是提交的照片不符合要求。普遍存在尺寸不符的情況，并且很多照片的寬高比例不合適，制作證件時(shí)因?yàn)槔於鴩?yán)重變形。三是普遍反映填報(bào)信息時(shí)效率不高。特別是命名照片文件時(shí)，一一核對(duì)出生日期比較麻煩，很容易出現(xiàn)錯(cuò)誤。

為了解決以上問(wèn)題，本人對(duì)批量提報(bào)表格進(jìn)行了改進(jìn)。在使用Excel表格的有效性規(guī)則防止在表格內(nèi)輸入無(wú)效內(nèi)容的基礎(chǔ)上，利用ExcelVBA設(shè)計(jì)校驗(yàn)程序，對(duì)申請(qǐng)人信息以及照片的有效性進(jìn)行校驗(yàn)，對(duì)填報(bào)錯(cuò)誤或不完整數(shù)據(jù)進(jìn)行提醒或自動(dòng)修正，從而降低了信息填報(bào)的難度，提高了提報(bào)數(shù)據(jù)的質(zhì)量。

2 ExcelVBA及VBA插件介紹

VBA全稱(chēng)為“VisualBasicForApplication”，是一種依附于應(yīng)用軟件的二次開(kāi)發(fā)語(yǔ)言。作為VB家族的成員之一，它是一種面向?qū)ο蟮某绦蛘Z(yǔ)言，用一種所見(jiàn)即所得的方式編寫(xiě)代碼，這使它在學(xué)習(xí)和使用方面都非常簡(jiǎn)單。Excel是目前制表軟件中最優(yōu)秀、市場(chǎng)占有率最高的一款軟件，這歸功于它擁有強(qiáng)大的制表功能，還歸功于它提供了二次開(kāi)發(fā)平臺(tái)ExcelVBA，允許用戶(hù)自行開(kāi)發(fā)Excel本身不具備的功能，大大提升工作效率。通過(guò)VBA進(jìn)行二次開(kāi)發(fā)，可以強(qiáng)化Excel功能，將某些煩雜或者重復(fù)的日常工作簡(jiǎn)化，還可以開(kāi)發(fā)商業(yè)插件或者小型應(yīng)用軟件，其中比較常見(jiàn)和易于實(shí)現(xiàn)的就是通過(guò)開(kāi)發(fā)應(yīng)用VBA插件，來(lái)簡(jiǎn)化自己的實(shí)際工作。

Excel插件是利用VBA開(kāi)發(fā)的外置工具，通常是xla、xlam或者DLL格式，其中xla和xlam插件可以直接用Excel開(kāi)發(fā)，而DLL插件通常采用VB或才C++、VB.NET等開(kāi)發(fā)。相對(duì)于Excel的內(nèi)置功能，VBA插件具有很多優(yōu)勢(shì)。它可以對(duì)操作對(duì)象進(jìn)行批量處理，可以一鍵完成多個(gè)任務(wù)，可以將復(fù)雜的任務(wù)簡(jiǎn)單化，可以提升工作表數(shù)據(jù)的安全性和準(zhǔn)確性，使工作更輕松，運(yùn)算更快速、準(zhǔn)確。雖然VBA插件具有以上優(yōu)勢(shì)，但它在某些方面也有一些限制。比如說(shuō)通用性方面，因?yàn)殚_(kāi)發(fā)插件通常是個(gè)人行為，通用性可能不夠完善，由于開(kāi)發(fā)者測(cè)試的次數(shù)少以及測(cè)試條件不足等原因，導(dǎo)致某些插件藏有隱含缺陷。另外，ExcelVBA是依附于Excel主體程序的附屬程序，它可以開(kāi)發(fā)強(qiáng)化Excel功能的程序，但是不能開(kāi)發(fā)脫離Excel單獨(dú)存在的軟件，不能用于開(kāi)發(fā)全新而專(zhuān)業(yè)的應(yīng)用程序。

3 利用ExcelVBA實(shí)現(xiàn)證件照片批量校驗(yàn)

為了解決前面提及的幾個(gè)主要問(wèn)題，需要利用ExcelVBA編寫(xiě)三個(gè)函數(shù)（或過(guò)程），分別實(shí)現(xiàn)查找照片、分析尺寸和批量校驗(yàn)功能，并設(shè)置快捷鍵以方便調(diào)用。

3.1 查找照片并返回文件大小

該函數(shù)根據(jù)申請(qǐng)人姓名和出生日期，在該申請(qǐng)表所在目錄內(nèi)查找申請(qǐng)人照片，并返回照片文件大小。JPG格式文件有兩種常見(jiàn)的擴(kuò)展名：JPG和JPEG，編寫(xiě)程序時(shí)均需考慮。以姓名為“張三”，出生日期為“19810215”為例，首先查找文件名為“張三-19810215.JPG”文件，如果找到，則返回文件大小，否則依次查找文件名為“張三.JPG”、“張三-19810215.JPG.JPG”、“張三.JPG.JPG”、“張三-19810215.JPEG”、“張三.JPEG”、“張三-19810215.JPG.JPEG”、“張三.JPG.JPEG”的文件，找到后將其文件名更改為“張三-19810215.JPG”，并返回文件大小。如果以上文件都未找到，則返回-1。這樣既能夠自動(dòng)修正一些輸入錯(cuò)誤，提高容錯(cuò)能力，還能夠簡(jiǎn)化操作，除了在申請(qǐng)人姓名相同時(shí)需要輸入完整文件名外，其他照片僅需使用姓名命名，程序?qū)⒆詣?dòng)為其補(bǔ)充完整。該函數(shù)的主要代碼如下：

'參數(shù)iName為申請(qǐng)人姓名，iBirthday為出生日期的8位阿拉字符串

FunctionFileLength（ByValiNameAsString，ByValiBirth dayAsString）AsLong

Dimfilename1AsString，filename2AsString

'查找文件名類(lèi)似“張三-19810215.JPG”的文件，如果找到返回文件大小

filename1=ActiveWorkbook.Path+\"＼\"+name+\"-\"+birthday+\".JPG\"

IfDir（filename1）<>\"\"ThenFileLength=FileLen（filename1）：ExitFunction

'查找文件名類(lèi)似“張三-19810215.JPG.JPG”的文件，如果找到，更名后返回文件大小

filename2=filename1+\".JPG\"

IfDir（filename2）<>\"\"Then

Namefilename2Asfilename1

FileLength=FileLen（filename1）

ExitFunction

EndIf

……省略依次查找文件名類(lèi)似“張三.JPG”、“張三.JPG.JPG”等文件的過(guò)程

FileLength=-1 '未找到申請(qǐng)人照片，返回-1

EndFunction

3.2 獲取JPEG照片尺寸

JPG即JPEG，是由JointPhotographicExpertsGroup（聯(lián)合圖像專(zhuān)家組）制定的圖像壓縮標(biāo)準(zhǔn)，是常用的圖像文件格式之一。JPEG文件是分段存儲(chǔ)的，段的多少和長(zhǎng)度不是一定的，段的排列也沒(méi)有嚴(yán)格的順序。除了圖像開(kāi)始（SOI）和圖像結(jié)束（EOI）以外，每個(gè)段都是由段標(biāo)識(shí)、段類(lèi)型、段長(zhǎng)度和段內(nèi)容組成。段標(biāo)識(shí)1個(gè)字節(jié)，是十六進(jìn)制固定值0xFF，每個(gè)段標(biāo)識(shí)前可以添加數(shù)量不限的0xFF填充字節(jié)。段類(lèi)型也是1個(gè)字節(jié)，不同的段有不同的值。緊接其后的2個(gè)字節(jié)存放的是該段長(zhǎng)度（以字節(jié)為單位，不包括段標(biāo)識(shí)和段類(lèi)型），使用Motorola方式存儲(chǔ)，即高位在前，低位在后。圖像開(kāi)始段和圖像結(jié)束段只包含段標(biāo)識(shí)和段類(lèi)型組成，都是只有2個(gè)字節(jié)。

段的類(lèi)型很多，但是大多數(shù)的JPEG文件只包含幾種，如SOI（圖像開(kāi)始）、APP0（應(yīng)用數(shù)據(jù)塊）、DQT（量化表）、SOF0（幀圖像開(kāi)始）、DHT（霍夫曼表）、SOS（掃描線(xiàn)開(kāi)始）以及EOI（圖像結(jié)束）等，其中圖像的大小存儲(chǔ)于SOF0段中。同其它段一樣，SOF0段的起始字節(jié)值為0xFF，第二字節(jié)即段類(lèi)別字節(jié)值為0xC0，圖像的高度和寬度（以像素為單位）分別存放于該段的第6、7和第8、9字節(jié)內(nèi)，也是以Motorola方式存儲(chǔ)。因?yàn)槲覀冎皇切枰@取圖像的高度和寬度，因此只需在JPEG文件中找到該段，并根據(jù)其相應(yīng)位置的值計(jì)算圖像高和寬即可。當(dāng)然，在查找SOF0段以前，首先必須判斷該文件是不是JPEG文件，也就是判斷該文件開(kāi)始兩個(gè)字節(jié)的值是否為0xFF和0xD8（即是否為SOI段）。該函數(shù)的主要代碼如下：

'參數(shù)iWidth和iHeight以地址引用方式返回照片寬度和高度

'函數(shù)返回值：0正確，1未找到文件，2文件格式錯(cuò)誤

FunctionPhotoSize（iFileNameAsString，iWidthAsInteg er，iHeightAsInteger）AsInteger

DimFileBytes（）AsByte

DimFileLengthAsLong，IndexAsLong，F(xiàn)ileNumberas Integer

'如果文件不存在，返回1

iFileName=ActiveWorkbook.Path+\"＼\"+iFileName

IfDir（iFileName）=\"\"ThenPhotoSize=1：ExitFunction

'以二進(jìn)制形式打開(kāi)文件，如果錯(cuò)誤返回1

FileNumber=FreeFile '獲取可用文件句柄號(hào)

OnErrorResumeNext

OpeniFileNameForBinaryAsFileNumber

IfErr.Number<>0ThenPhotoSize=1：CloseFileNumber：ExitFunction

'獲取文件長(zhǎng)度，將文件內(nèi)容讀入數(shù)組后關(guān)閉文件

FileLength=LOF（FileNumber）

ReDimFileBytes（1ToFileLength）AsByte

GetFileNumber，，F(xiàn)ileBytes

CloseFileNumber

'判斷文件開(kāi)始是否為SOI段，即“FFD8”

IfFileBytes（1）<>255OrFileBytes（2）<>216Then

PhotoSize=2：ExitFunction

Else：Index=3：EndIf

'循環(huán)檢測(cè)每一段，直至找到幀圖像開(kāi)始段

Do

IfFileBytes（Index）=255Then'每一段的首字節(jié)均為“FF”

Do'循環(huán)過(guò)濾填充字節(jié)

Index=Index+1

LoopWhileFileBytes（Index）=255AndIndex

'第二字節(jié)為“C0”，該段SOF0，跳出循環(huán)

IfFileBytes（Index）=192ThenExitDo

'如果到了數(shù)組結(jié)尾，跳出循環(huán)

IfIndex>=FileLengthThenExitDo

'否則指針移至下一段開(kāi)始

Index=Index+CLng（FileBytes（Index+1））*256+FileBytes（Index+2）+1

Else：Index=FileLength：EndIf'數(shù)據(jù)錯(cuò)誤，指針移到數(shù)組結(jié)尾

LoopWhileIndex

'如果未到數(shù)組結(jié)尾，說(shuō)明找到了SOF0段，計(jì)算并返回照片尺寸

IfIndex

'圖像幀段第6、7兩字節(jié)的數(shù)值是圖像高度

iHeight=CInt（CLng（FileBytes（Index+4））*256+FileBytes（Index+5））

'圖像幀段第8、9兩字節(jié)的數(shù)值是圖像寬度

iWidth=CInt（CLng（FileBytes（Index+6））*256+FileBytes（Index+7））

PhotoSize=0'成功獲取照片尺寸，返回0

Else：PhotoSize=2：EndIf'否則，文件格式錯(cuò)誤，返回2

EndFunction

調(diào)用該函數(shù)時(shí)，需要先定義兩個(gè)整型變量，然后以引用參數(shù)的方式返回圖像高度和寬度。

3.3 證件信息批量校驗(yàn)

主要是按照有關(guān)要求，對(duì)表格內(nèi)的所有申請(qǐng)人信息逐一進(jìn)行必填項(xiàng)和有效性檢查，并根據(jù)申請(qǐng)人姓名和出生日期查找校驗(yàn)申請(qǐng)人照片。對(duì)不符合要求的信息，用背景色加以突出顯示，并以批注形式說(shuō)明原因，方便填報(bào)人檢查修改。具體代碼不再贅述。

3.4 使用快捷鍵調(diào)用校驗(yàn)過(guò)程

在Excel2003中，可以采取自定義工具欄或自定義菜單的方式調(diào)用校驗(yàn)過(guò)程，非常直觀、方便。但是Excel2010用功能區(qū)替換了早期版本的命令欄，將工具欄和菜單都屏蔽了，盡管它仍然允許通過(guò)VBA代碼產(chǎn)生自定義菜單和工具條，但是自定義菜單和工具欄都顯示在加載項(xiàng)功能區(qū)中，不直觀，使用不便?？紤]到兼容性問(wèn)題，本文使用快捷鍵調(diào)用校驗(yàn)過(guò)程。設(shè)置快捷鍵有兩種方法。一種方法是通過(guò)宏選項(xiàng)設(shè)置快捷鍵，但是只能設(shè)置Ctrl組合鍵。另一種方法是在工作簿打開(kāi)事件中調(diào)用Application.OnKey方法，將特定鍵或特定組合鍵綁定到校驗(yàn)過(guò)程，優(yōu)點(diǎn)是比較靈活，缺點(diǎn)是只要Excel程序未關(guān)閉就會(huì)一直起作用，關(guān)閉工作簿時(shí)應(yīng)予以恢復(fù)。本文使用的是第二種方法，將F10鍵綁定到校驗(yàn)過(guò)程。其代碼如下：

'設(shè)置快捷鍵，其中ValidateApplicants為校驗(yàn)過(guò)程的名字

PrivateSubWorkbook_Open（）

Application.OnKey\"{F10}\"，\"ValidateApplicants\"

EndSub

'恢復(fù)快捷鍵

PrivateSubWorkbook_BeforeClose（CancelAsBoolean）

Application.OnKey\"{F10}\"

EndSub

4 ExcelVBA插件的生成和安裝使用

Excel插件包括加載宏和加載項(xiàng)。加載項(xiàng)是Excel外置插件，最常見(jiàn)的是DLL格式，不能用ExcelVBA開(kāi)發(fā)。加載宏是從Excel97版開(kāi)始推廣的一類(lèi)工作簿格式，文件擴(kuò)展名為xlam，是一種特殊的工作簿。它不屬于Workbooks成員，其窗口是隱藏的，結(jié)構(gòu)和內(nèi)容無(wú)法在前端修改。通過(guò)簡(jiǎn)單的安裝即可將加載宏的所有程序運(yùn)用到當(dāng)前工作簿，而且運(yùn)行簽署過(guò)的加載宏時(shí)不會(huì)出現(xiàn)關(guān)于宏的安全性方面的警告提示。將帶有宏的普通工作簿另存為加載宏，即可生成加載宏插件。

安裝自定義加載宏通常有兩種方式。一種是將加載宏文件存于自動(dòng)文件夾中。以Excel2003為例，其自動(dòng)文件夾為Office安裝目錄下的Office11＼XLSTART，Excel2007和Excel2010則分別對(duì)應(yīng)Office12和Office14。另一種方法是通過(guò)加載宏管理器瀏覽安裝加載宏文件。關(guān)閉自定義加載宏也比較簡(jiǎn)單，只需要在加載宏管理器中，去除該加載宏名稱(chēng)前的鉤即可。

本文設(shè)計(jì)的插件就是以加載宏的方式保存和安裝的。首先，按照規(guī)定格式和有效性規(guī)則設(shè)計(jì)證件申請(qǐng)表模板，使用固定的絕對(duì)路徑存放到指定位置。然后，打開(kāi)存有校驗(yàn)程序的工作簿，添加一個(gè)新過(guò)程，用以根據(jù)申請(qǐng)表模板創(chuàng)建申請(qǐng)表，并將該工作簿保存為加載宏。在其它電腦上安裝使用時(shí)，將申請(qǐng)表模板復(fù)制到相同位置，并按前面所述方法安裝插件，就可以方便地使用有關(guān)功能。

5 應(yīng)用效果及改進(jìn)設(shè)計(jì)

從使用的情況看，普遍反映安裝簡(jiǎn)單，操作方便，功能實(shí)用，能有效提高工作效率，提報(bào)數(shù)據(jù)的總體質(zhì)量也有了明顯提高。目前，根據(jù)用戶(hù)提供的改進(jìn)意見(jiàn)，對(duì)插件進(jìn)行了逐步改進(jìn)，主要包括以下幾個(gè)部分：一是使用Excel窗體開(kāi)發(fā)了錄入界面，采取全鍵盤(pán)錄入和代碼錄入等方式，提高了錄入效率；二是增設(shè)了照片預(yù)覽功能；三是將申請(qǐng)表模板和加載宏整合成一個(gè)文件，進(jìn)一步簡(jiǎn)化了安裝操作。

6 結(jié)語(yǔ)

ExcelVBA易學(xué)易用，功能強(qiáng)大。加載宏插件開(kāi)發(fā)簡(jiǎn)單，安裝、應(yīng)用方便。本文使用ExcelVBA插件解決了證件管理工作中申請(qǐng)人信息批量校驗(yàn)的問(wèn)題，既提高了工作效率，也保證了數(shù)據(jù)質(zhì)量。文中獲取JPEG圖像尺寸的思路，可供開(kāi)發(fā)類(lèi)似應(yīng)用時(shí)借鑒。

參考文獻(xiàn)：

[1]羅剛君.ExcelVBA程序開(kāi)發(fā)自學(xué)寶典[M].北京：電子工業(yè)出版社，2011.

[2]邱磊，朱相鳴.JPEG文件結(jié)構(gòu)研究[J].電腦學(xué)習(xí)，2009，4.

[3]徐勝祥，徐運(yùn)清.利用ActiveXDLL開(kāi)發(fā)獲取圖片尺寸大小的Web應(yīng)用系統(tǒng)[J].現(xiàn)代圖書(shū)情報(bào)技術(shù)，2006，12.

[作者簡(jiǎn)介]宋義河（1972.3-），男，山東東阿人，2004年山東大學(xué)齊魯軟件學(xué)院研究生畢業(yè)，碩士，現(xiàn)就職于山東省公安廳警衛(wèi)局，主要負(fù)責(zé)本單位信息化建設(shè)和保障工作，研究方向：計(jì)算機(jī)應(yīng)用、軟件開(kāi)發(fā)和信息通信。