Excel数据匹配研究

李博

摘  要：Excel在日常办公中占据着极其重要的地位，微软也在不断的增加新功能，使它的能力越来越强，众多集成的函数方法让使用者能轻松操作某个电子表格。如用户写一个VLOOKUP公式就能简单的根据ID精确匹配出相关内容，但这种匹配准则相对简单[1]，本文基于Excel表的匹配问题，提出利用Java编程完成复杂条件匹配，和另一种借助MySQL数据库查询的匹配方案，比较两者之间的执行情况，加以分析，得出借助MySQL数据库查询匹配方案优于前者。

关键词：Excel;Java;MySQL;数据匹配

中图分类号：TP39;TP31 文献标识码：A 文章编号：2096-4706（2019）01-0013-03

Excel Data Matching Research

LI Bo

（Southwest Minzu University，Chengdu  610041，China）

Abstract：Excel occupies an important position in daily office，Microsoft is constantly adding new features，making it more and more powerful，and many integrated function methods make it easy for users to operate a spreadsheet. For example，if the user writes a VLOOKUP formula，the relevant content can be accurately matched according to the ID，but the matching criterion is relatively simple[1]. Based on the matching problem of Excel tables，this paper puts forward a matching scheme using Java programming to complete complex condition matching and another matching scheme using MySQL database query，compares the implementation of the two schemes，and analyses them. It is concluded that the matching scheme using MySQL database query is better than the former.

Keywords：Excel;Java;MySQL;data matching

1  Java解析Excel表

Java解析Excel表常用的框架有两个，一种是借助日本开源的jxl.jar，一种是借助Apache POI.jar也是开源的，两者的差距不大。由于JXL现已停更，本文使用Apache POI 3.17版本解析Excel表。针对Excel有两个版本，可使用HSSFWorkbook读取2003版本的Excel表，XSSFWorkbook读取2007版本及以上的Excel表[2]。POI插件是Jakarta公司提供的一个完全用Java语言实现的免费插件，该插件可以把Excel表格指定Sheet表的指定行和列的Cell格的内容读出，而且可以把数据按照用户指定的格式写入到Excel文档[3]。

部分代码如下：

public class Main {

public static Statement statement=null;

public static void main（String[] args） throws Exception{

statement = getConnectwithMysql.mainconn （“database”）;

//准备匹配文档TEST.XLSX

File file = new File（“J：＼＼TEST.XLSX”）;

Workbook wb = null;

try {

//读取file到缓存区，获取工作簿

wb = new XSSFWorkbook（new FileInput Stream（file））;

} catch （IOException e） {

e.printStackTrace（）;

}

//得到序號为0的工作表

Sheet sheet = wb.getSheetAt（0）;

//获取工作表数据行总行数

int n=sheet.getLastRowNum（）;

for （int i=1;i<=n;i++） {

//打印提示信息，开始匹配EXCEL表的第i+1行数据

System.out.println（“start to scan “+（i+1）+” row”）;

Row row = sheet.getRow（i）;

//获取单行数据匹配关键信息

String hetongcode=

getCellContent（row.getCell（1））;

….....

//在数据库中匹配，匹配结果返回在结果集rs中

ResultSet rs = select （daytime，name，money）;

//记录匹配信息

while（rs.next（）） {

hetongcode=hetongcode+”-（“+rs.getString （“@序号”）+”）”;

}

row.getCell（1）.setCellValue（hetongcode）;

}

//跟新工作薄，保存匹配信息

try （FileOutputStream fileOut = new FileOutput Stream（file）） {

wb.write（fileOut）;

fileOut.close（）;

}

//封装类select

public static ResultSet select（String daytime， String name， String money） throws SQLException {

//封裝查询语句SQL

String SQL=”select from WHERE （`日期`-“+daytime+”）>=0 AND （`日期`-“+daytime+”） <=5 AND （（`支出`-“+money+”）=0 or （`支出`-“+ money+”）>1500） AND `户名` LIKE ＼””+name +”＼””;

//执行查询，返回查询结果集rs

ResultSetrs=

statement.executeQuery（SQL）;

return rs;

}

//封装类getCellContent读取cell内容，返回String方便统一处理

public static String getCellContent（Cell cell）{

String content=null;

CellType type = cell.getCellTypeEnum（）;

If（type.equals（CellType.NUMERIC））{

Content=cell.getNumericCellValue（）+””;

}

….....

Return content;

}

}

2  两种方案思路

准备好两个Excel表，分别为TEST.xlsx和MATCH.xlsx.目的是将TEST中的数据内容读出与MATCH中的数据按条件匹配，将匹配结果写入TEST中记录下来。

方案一：用Java解析两张表，逐行提取TEST中的一条数据与MATCH逐行匹配，匹配记录写入TEST中，需匹配数据大小为t，匹配次数为m*t，t为TEST的数据行数，m为MATCH的数据行数。

方案二：使用Navicat将MATCH的内容导入数据库中，POI解析TEST表，逐行提取TEST某一条数据，根据匹配条件编写SQL语句，将查询结果解析后写入TEST表中做匹配记录。需匹配数据大小为t，匹配次数和查询数据库次数一致，也为t。

3  比较执行情况

两种方案分别在下列3种实验条件下执行比较，3种实验下的MATCH的数据量一致，均为7000行数据量大小。首先控制匹配条件为2，测试方案一与方案二在TEST大小为1000和9000不同的执行情况。然后控制TEST大小为1000，考虑条件复杂度在2和3的两种条件下，方案一与方案二的执行情况。详细比较执行情况如表1，以下数据均为测试多次后采用的平均值记录。

4  结果分析

横向比较方案一在3种实验下的执行情况可以看出，平均匹配一次的时间基本不变，花费时间都小于1ms。单改变TEST数据量为原来的9倍，平均匹配一条的时间增大1.3倍，总时间增大10倍有余。单改变匹配条件复杂度增加1，平均匹配一条的时间增加52%，总时间增加45%。方案二在执行上增加了连接数据库的时间，3种实验连接数据库时间差距不大，均低于500ms。匹配一条数据平均花费不受TEST数据量大小的影响，并且总花费时间不到原来的8倍。单控制条件复杂度增加1，平均匹配一条的时间增加10%，总时间增加不到9%。

纵向比较方案一与方案二在3种实验下的执行情况可以得出，方案二无论是在平均匹配一条数据时间花费，还是总的时间花费，均比方案一少的多。

可见方案二要优于方案一。

5  方案二的进一步优化

方案二的总时间花费基本有两部分组成，一部分是前期的连接数据库，二是数据匹配时间。因此，考虑优化连接数据库的方法，有利于减少匹配时间，但对整体实验的优化效果不明显;考虑优化数据匹配，而数据匹配时间约等于在数据库查询的时间，若是优化数据库查询，将大幅提升整体的执行情况。根据数据库数据存储的特殊结构，在查询SQL涉及到的字段上建立索引，可以提高查询响应效率;在未建立索引的情况下查询，数据库将遍历表中所有信息，将所有信息与TEST表上的信息比较匹配，扫描未涉及的信息显然浪费了查询时间。在数据库上对查询所涉及到的字段建立索引，数据库根据查询语句SQL中的条件1快速定位到与条件1相关的字段1，返回满足条件1的数据行号;根据条件2，在之前操作的基础上直接定位到与之相关的字段2，此时返回的数据行既满足条件1，又满足条件2;最后将满足查询条件的结果返回rs。使用索引，找到了匹配的数据行在哪儿终止，并能够忽略其它的数据行，快速的定位到匹配的值，节约了大量的搜索时间，也就减少了匹配时间[4]。建立索引前后实验对比数据如表2所示。

结果显示，建立索引与未建立索引相比，建立索引将少量增加连接数据库的时间。未建立索引数据库查询一次平均花费大约在10ms一条，而建立索引后，数据库查询一次平均花费不到1ms，匹配一条平均花费的时间约为1ms，建立索引花费的总时间只占未建立索引的26%，匹配优化效果显著。

6  结  论

本文就Excel两表匹配问题，提出了方案一与方案二，通过实验测试数据，得出：方案二无论是在增加TEST数据量方面，还是增加条件复杂度方面，所花的时间均比方案一少，若是匹配条件没有涉及MATCH包含的所有信息，根据匹配要求，在数据库中对涉及到的字段建立索引，可以大大降低匹配时间，十分高效的得到匹配结果。

参考文献：

[1] 李国雁.EXCEL中数据的自动匹配 [J].软件工程师，2013（12）：21-22.

[2] 佘向飞，于萍.基于Java反射机制与POI自动导出excel的实现 [J].数字技术与应用，2014（9）：94.

[3] 陶袁，张志军.XML文档在采集和处理Excel文档中的应用 [J].白城师范学院学报，2005（3）：32-34.

[4] 殷丽，徐海华，吴海涛.MySQL查询优化技术——索引 [A].第八届工业仪表与自动化学术会议论文集 [C].北京：中国仪器仪表学会，2007：490-491.