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.