Visual Basic处理多财务数据Excel表格
2019-03-12胡敏李梅郑增国
胡敏，李梅，郑增国
（上饶师范学院网络中心，上饶 334001）
0 引言
高校财务报账各二级部门都会将费用表格上交给财务部门审核，审核通过后才能够正常的划账下发。以我校为例，我校的专业经费报销表格每月个数平均是在60个以上，在高峰期时会超过100。不同类型费用表格经常是不同的人员制造，在造表过程中就人名这一基本信息有时会出现同音不同字或者同名同姓但不同人员等现象，人工的检测和校对需要耗费大量的时间在重复的比对上，虽然Excel表格中有匹配函数可以减轻工作量，但是频繁的打开关闭表格也需要人工的看守，为此通过Visual Basic与Excel的对接，对报销表格中的人名检测、结构检测等功能使用Visual Basic程序编辑完成。
1 Excel操作Visual Basic的关联
在VB中有专门连接Excel外部对象Microsoft Excel 11.0 Object Library对象库。在其中将Excel的结构和VB的结构一一对应。
（1）Excel是以层次结构组织对象的工作簿、工作表、行列单元格，其对象模型中含有许多不同的对象元素。编程过程中主要用到以下4个层次的对象，在Vi⁃sual Basic程序中有专门面向Excel的对象Excel Appli⁃cation[1]。
（2）Application 对象，即 Excel程序本身，Excel Ap⁃plication对象表示Excel应用程序本身。声明一个Ex⁃cel应用程序对象的代码为Dim xls As New Excel.Appli⁃cation。
（3）WorkBook对象，即Excel的工作簿文件对象；声明一个Excel工作薄对象的代码为Dim xbook As New Excel.Workbook[2]。
（4）WorkSheets对象，表示的是Excel的工作表对象集；
例如：worksheets（1）表示第一个工作表。声明一个Excel工作表象的代码为：
Dim xsheet As New Excel.Worksheet。
（5）Range对象：是Excel应用程序中最常用的对象。在能够处理Excel内的任何范围之前，必须将它表示为Range对象，并处理该对象的方法和属性[3]。
（6）Cells、Range、Rows、Columns 对象，分别表示Excel工作表中的单元格对象集、区域对象、行对象集、列对象集[4]。
2 实例应用及主要代码
我校财务报账人员姓名检测流程如图1。
图1
第一步：导入母本：
从人事部门获得学校在职教师信息Excel表格，包括姓名、工号、所在部门。在本系统中称为母本即凭证文件。导入效果如图2所示。
图2
定义人员信息类型：Public Type person_info
xm_info As String'姓名
gonghao_info As String'工号
End Type
Public zaizhi（）as person_info'将excel中在职人员的信息导入数组
在VB中定义表格，引用表格
Set xlApp=CreateObject（"Excel.Application"）
Set xlsheet=xlBook.Worksheets（"在职"）
xlsheet.Activate
zaizhi_num=xlsheet.UsedRange.Rows.Count-1
ReDim zaizhi（xlsheet.UsedRange.Rows.Count-1）
'根据人事部门给的人事Excel表格行数重新定义zaizhi数组的大小
For i=1 To xlsheet.UsedRange.Rows.Count-1'向zaizhi数组导入数据包括工号和姓名
zaizhi（i）.gonghao_info=Trimstring（xlsheet.Cells（i+1，1））'第一列工号
zaizhi（i）.xm_info=Trimstring（xlsheet.Cells（i+1，2））'第二列姓名
Next i
第二步：获得待报销表格中有效记录的开始行列以及条数信息，用于导入数组。
通过过程 zhiDuanPosition（xlsheet，r，excelfile）获得表格的行数和报销人员有效记录开始的位置，包括在Excel中的行列值。并将结构正常并且姓名正确的表格传入b_to_sz（）过程中转换为数组，便于后期的汇总分析。在导入的待测Excel表格中匹配带有姓字的字段，定位为报账人信息的开始包括行列。
For i=1 To R_Count'从待测Excel表格的第一行开始查找
flag=False'默认没有找到姓名字段
For j=1 To C_Count
If InStr（Trimstring（xlsheet.UsedRange.Cells（i，j）），"姓"）<>0 Then '遍历该行中的每一个单元格匹配是否有”姓”字，如果找到表示以下行列为报账人员的信息，记录行列信息用于后面b_sz（）数组内容的导入。
xm_j=j
flag=True
Exit For
End If
Next j
If flag=True Then Exit For‘如果没有姓字则认为该表格的结构异常，没有报账人员姓名字段。
Next i
第三步：将结构正常的Excel表格数据信息的导入到数组b_sz（），筛选出姓名行列信息导入数组b_szF（）中。
Public Sub b_to_sz（xlsheet As Object）
ReDim b_sz（1 To xlsheet.UsedRange.Rows.Count，1 To xlsheet.UsedRange.Columns.Count）As String‘根据表格大小先定义二维原始数组，将Excel中姓名对应列的内容和实发的金额列的内容存入b_sz数组中，精简成b_szF（）数组。
（1）找出字段开始的第一列zd_start_c For j=1 To UBound（b_sz，2）
If Len（Trimstring（xlsheet.UsedRange.Cells（zd_r，j）））<>0 Then
zd_start_c=j‘
Exit For
End If
Next j
（2）找出字段最后一列zd_end_c
For j=UBound（b_sz，2）To zd_start_c Step-1 '从后往前找字段最后一列zd_end_c
If Len（Trimstring（xlsheet.UsedRange.Cells（zd_r，j）））<>0 Then'从后往前找第一个不为零的就是字段结束列
zd_end_c=j
Exit For
End If
Next j
（3）找出最后一个姓名所在的行xm_end_r
For i=zd_r To UBound（b_sz，1）+1'找出最后一个姓名所在的行xm_end_r
If Len（Trimstring（xlsheet.UsedRange.Cells（i，xm_c）））=0 Then
xm_end_r=i-1
Exit For
End If
Next i
（4）根据报账人员信息开始的行列，定义精简数组存放报账人员姓名
ReDim b_szF（1 To xm_end_r-zd_r+1，1 To zd_end_c
zd_start_c+1）As String'定义精简的数组
For i=zd_r To xm_end_r
b_j=1
For j=zd_start_c To zd_end_c
b_szF（b_i，b_j） =Trimstring（xlsheet.
UsedRange.Cells（i，j））
b_j=b_j+1
End If
Next j
b_i=b_i+1
Next i
第四步：将选择后的数组b_szF（）与母本信息核对，并记录不规范的姓名所在行列便于人工核对。
判断标准和处理方法见表1。
（1）在母本中查找将待检测的姓名，如果姓名不在要记录在第几行，便于后期人工核查。
checkName_bsz（zaizhi（），b_szF（），excelfile，Dao_Excel_Name，zd_r）
For i=2 To UBound（b_szF，1） '从内容行开始
xm_is_in=False '先设定待测姓名不在母本中
For zaizhi_i=1 To UBound（zaizhi） '将待测姓名与zaizhi数组中依次对比查找
If Trimstring（b_szF（i，xm_c））=Trimstring
（zaizhi（zaizhi_i）.xm_info）Then
xm_is_in=True'如果找到了则xm_is_in
设置为True，并退出循环
Exit For
End If
Next zaizhi_i
If xm_is_in=False Then '遍历完zaizhi数组后，如果xm_is_in仍然为false表示待检测’的姓名没有在人事处给的在职母本中
xm_cw_tishi=xm_cw_tishi&"在第"&i+zd_r-1&"行，没有找到"&b_szF（i，xm_c）&vbCrLf '将这个没在在职人事的姓名的行列和内容记录
End If
Next i
（2）将姓名不在母本中的表格的信息记录保存在错误提示.txt文本中，如图3所示。
图3
（3）姓名无异常且表格结构正常的表格信息列出，如图4所示。
图4
3 结语
本文讲解了Visual Basic操作多张财务报账Excel表的实际应用，并取得了良好的效果。通过设计的Vi⁃sual Basic界面就能够操作多个Excel表格，利用数组的特性进行表结构和保障人员信息检测，并记录异常信息的位置便于人工核对，大大提高了工作的效率。
