在我们的日常数据清理工作中,经常需要把EDC数据导成Excel清单报表,然后将不同日期版本的Excel清单进行比对,有利于我们快速并且准确地发现两版数据之间的差异,从而有助于我们去进行下一步的工作。为使Excel比对能够高效而方便,Excel-VB小组开发出了Excel比对工具,能够批量地比对相同Sheet,从而让我们DM小伙伴比对Excel更加轻松且高效。本篇主要介绍实现Excel比对工具的步骤。
步骤一:确认需求, 确定Excel比对结果差异呈现形式
✦ 例如 ✦
“绿色”:代表新增;
“粉色”:代表新增列;
“蓝色”&“黄色”:蓝色代表行有修改,黄色代表具体表格内容有修改;
“Compare”列:比对状态说明,如New , Change。
步骤二:设计比对配置页面,目的为了方便底台程序去识别需比对内容,并且返回Log,方便比对后查询问题。
在比对配置页面中有:
(1)当前、既往和生成的文件名以及各自的路径;
(2)目录,用来识别需要比对的Sheet;
(3)Header所在行,用来识别新旧文件列的所在位置,当如果不存在Header时,则会按照ABC......列比对;
(4)连接变量(By),横向识别新旧文件行所在位置,当不填写时,则无法识别Change;
(5)不比对列;
(6)功能列:如只保留New/Change功能,保留改变点前后结果功能等;
(7)Log列,显示程序Check出来的问题,比如需比对的Sheet不存在两份文件中等。
其中的代码实现思路介绍如下:
(1)为了获取Excel单元值,可以根据实际需要选择用Sheets.Cells或者Sheets.Range
(2)检查目录中的Sheets名字是否都在新旧文件中,可选择用For循环和For Each循环去查询Sheets的存在情况,不存在时,则输出到Log里面,使用者可根据Log去修改文件。
(3)检查Header列名是否存在于Sheets中,可以通过识别Header所在行,然后在对应的数组中检查;
步骤三:撰写比对代码核心程序
(1)新旧数据的获取,通过Workbooks.Open打开Excel文件,通过定义数组,记录Sheets中的数据;
(2)通过对数组进行循环,创建连接变量;
a) 第一个为Word变量,记录的是每一行所有字符的内容;
b) 第二个为By_Word变量,记录的是连接变量字符内容;
(3)使用SQL查询出需检查列;
(4)通过需检查列的状态,判断出Compare列是New或者为Change,通过Rows.Interior.ColorIndex标记行的颜色,Cells.Interior.ColorIndex标记单元格的颜色;
注意:
a) 当能连接Word变量值,则该行没有变化;
b) 当不能连上Word变量值,但连上了By_Word变量值,则该行为Change;
c) 当不能连上Word和By_Word变量值,则该行为New;
步骤四:比对结束,Workbook.Close关掉新旧文件,用Workbook.SaveAs另存为比对文件。
通过以上四个步骤,一个v1.0的比对工具已经完成了。
但是,作为一名工具开发人员,对工具的要求不仅仅局限于实现输出结果的功能,而且还要从用户角度出发,思考如何提升用户友好度。所以在v2.0的时候,添加了操作界面和操作按钮,使得我们数据管理人员更加容易理解比对工具,操作起来也更加方便。另外,添加了操作界面和操作按钮,也更容易规范比对配置页面里面的内容,减少后台程序运行出现bug的风险。全新的v2.0版本如下: