Excel數(shù)據(jù)匹配研究

李博

摘? 要：Excel在日常辦公中占據(jù)著極其重要的地位，微軟也在不斷的增加新功能，使它的能力越來越強(qiáng)，眾多集成的函數(shù)方法讓使用者能輕松操作某個電子表格。如用戶寫一個VLOOKUP公式就能簡單的根據(jù)ID精確匹配出相關(guān)內(nèi)容，但這種匹配準(zhǔn)則相對簡單[1]，本文基于Excel表的匹配問題，提出利用Java編程完成復(fù)雜條件匹配，和另一種借助MySQL數(shù)據(jù)庫查詢的匹配方案，比較兩者之間的執(zhí)行情況，加以分析，得出借助MySQL數(shù)據(jù)庫查詢匹配方案優(yōu)于前者。

關(guān)鍵詞：Excel;Java;MySQL;數(shù)據(jù)匹配

中圖分類號：TP39;TP31 文獻(xiàn)標(biāo)識碼：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現(xiàn)已停更，本文使用Apache POI 3.17版本解析Excel表。針對Excel有兩個版本，可使用HSSFWorkbook讀取2003版本的Excel表，XSSFWorkbook讀取2007版本及以上的Excel表[2]。POI插件是Jakarta公司提供的一個完全用Java語言實現(xiàn)的免費插件，該插件可以把Excel表格指定Sheet表的指定行和列的Cell格的內(nèi)容讀出，而且可以把數(shù)據(jù)按照用戶指定的格式寫入到Excel文檔[3]。

部分代碼如下：

public class Main {

public static Statement statement=null;

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

statement = getConnectwithMysql.mainconn （“database”）;

//準(zhǔn)備匹配文檔TEST.XLSX

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

Workbook wb = null;

try {

//讀取file到緩存區(qū)，獲取工作簿

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

} catch （IOException e） {

e.printStackTrace（）;

}

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

Sheet sheet = wb.getSheetAt（0）;

//獲取工作表數(shù)據(jù)行總行數(shù)

int n=sheet.getLastRowNum（）;

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

//打印提示信息，開始匹配EXCEL表的第i+1行數(shù)據(jù)

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

Row row = sheet.getRow（i）;

//獲取單行數(shù)據(jù)匹配關(guān)鍵信息

String hetongcode=

getCellContent（row.getCell（1））;

….....

//在數(shù)據(jù)庫中匹配，匹配結(jié)果返回在結(jié)果集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 +”＼””;

//執(zhí)行查詢，返回查詢結(jié)果集rs

ResultSetrs=

statement.executeQuery（SQL）;

return rs;

}

//封裝類getCellContent讀取cell內(nèi)容，返回String方便統(tǒng)一處理

public static String getCellContent（Cell cell）{

String content=null;

CellType type = cell.getCellTypeEnum（）;

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

Content=cell.getNumericCellValue（）+””;

}

….....

Return content;

}

}

2? 兩種方案思路

準(zhǔn)備好兩個Excel表，分別為TEST.xlsx和MATCH.xlsx.目的是將TEST中的數(shù)據(jù)內(nèi)容讀出與MATCH中的數(shù)據(jù)按條件匹配，將匹配結(jié)果寫入TEST中記錄下來。

方案一：用Java解析兩張表，逐行提取TEST中的一條數(shù)據(jù)與MATCH逐行匹配，匹配記錄寫入TEST中，需匹配數(shù)據(jù)大小為t，匹配次數(shù)為m*t，t為TEST的數(shù)據(jù)行數(shù)，m為MATCH的數(shù)據(jù)行數(shù)。

方案二：使用Navicat將MATCH的內(nèi)容導(dǎo)入數(shù)據(jù)庫中，POI解析TEST表，逐行提取TEST某一條數(shù)據(jù)，根據(jù)匹配條件編寫SQL語句，將查詢結(jié)果解析后寫入TEST表中做匹配記錄。需匹配數(shù)據(jù)大小為t，匹配次數(shù)和查詢數(shù)據(jù)庫次數(shù)一致，也為t。

3? 比較執(zhí)行情況

兩種方案分別在下列3種實驗條件下執(zhí)行比較，3種實驗下的MATCH的數(shù)據(jù)量一致，均為7000行數(shù)據(jù)量大小。首先控制匹配條件為2，測試方案一與方案二在TEST大小為1000和9000不同的執(zhí)行情況。然后控制TEST大小為1000，考慮條件復(fù)雜度在2和3的兩種條件下，方案一與方案二的執(zhí)行情況。詳細(xì)比較執(zhí)行情況如表1，以下數(shù)據(jù)均為測試多次后采用的平均值記錄。

4? 結(jié)果分析

橫向比較方案一在3種實驗下的執(zhí)行情況可以看出，平均匹配一次的時間基本不變，花費時間都小于1ms。單改變TEST數(shù)據(jù)量為原來的9倍，平均匹配一條的時間增大1.3倍，總時間增大10倍有余。單改變匹配條件復(fù)雜度增加1，平均匹配一條的時間增加52%，總時間增加45%。方案二在執(zhí)行上增加了連接數(shù)據(jù)庫的時間，3種實驗連接數(shù)據(jù)庫時間差距不大，均低于500ms。匹配一條數(shù)據(jù)平均花費不受TEST數(shù)據(jù)量大小的影響，并且總花費時間不到原來的8倍。單控制條件復(fù)雜度增加1，平均匹配一條的時間增加10%，總時間增加不到9%。

縱向比較方案一與方案二在3種實驗下的執(zhí)行情況可以得出，方案二無論是在平均匹配一條數(shù)據(jù)時間花費，還是總的時間花費，均比方案一少的多。

可見方案二要優(yōu)于方案一。

5? 方案二的進(jìn)一步優(yōu)化

方案二的總時間花費基本有兩部分組成，一部分是前期的連接數(shù)據(jù)庫，二是數(shù)據(jù)匹配時間。因此，考慮優(yōu)化連接數(shù)據(jù)庫的方法，有利于減少匹配時間，但對整體實驗的優(yōu)化效果不明顯;考慮優(yōu)化數(shù)據(jù)匹配，而數(shù)據(jù)匹配時間約等于在數(shù)據(jù)庫查詢的時間，若是優(yōu)化數(shù)據(jù)庫查詢，將大幅提升整體的執(zhí)行情況。根據(jù)數(shù)據(jù)庫數(shù)據(jù)存儲的特殊結(jié)構(gòu)，在查詢SQL涉及到的字段上建立索引，可以提高查詢響應(yīng)效率;在未建立索引的情況下查詢，數(shù)據(jù)庫將遍歷表中所有信息，將所有信息與TEST表上的信息比較匹配，掃描未涉及的信息顯然浪費了查詢時間。在數(shù)據(jù)庫上對查詢所涉及到的字段建立索引，數(shù)據(jù)庫根據(jù)查詢語句SQL中的條件1快速定位到與條件1相關(guān)的字段1，返回滿足條件1的數(shù)據(jù)行號;根據(jù)條件2，在之前操作的基礎(chǔ)上直接定位到與之相關(guān)的字段2，此時返回的數(shù)據(jù)行既滿足條件1，又滿足條件2;最后將滿足查詢條件的結(jié)果返回rs。使用索引，找到了匹配的數(shù)據(jù)行在哪兒終止，并能夠忽略其它的數(shù)據(jù)行，快速的定位到匹配的值，節(jié)約了大量的搜索時間，也就減少了匹配時間[4]。建立索引前后實驗對比數(shù)據(jù)如表2所示。

結(jié)果顯示，建立索引與未建立索引相比，建立索引將少量增加連接數(shù)據(jù)庫的時間。未建立索引數(shù)據(jù)庫查詢一次平均花費大約在10ms一條，而建立索引后，數(shù)據(jù)庫查詢一次平均花費不到1ms，匹配一條平均花費的時間約為1ms，建立索引花費的總時間只占未建立索引的26%，匹配優(yōu)化效果顯著。

6? 結(jié)? 論

本文就Excel兩表匹配問題，提出了方案一與方案二，通過實驗測試數(shù)據(jù)，得出：方案二無論是在增加TEST數(shù)據(jù)量方面，還是增加條件復(fù)雜度方面，所花的時間均比方案一少，若是匹配條件沒有涉及MATCH包含的所有信息，根據(jù)匹配要求，在數(shù)據(jù)庫中對涉及到的字段建立索引，可以大大降低匹配時間，十分高效的得到匹配結(jié)果。

參考文獻(xiàn)：

[1] 李國雁.EXCEL中數(shù)據(jù)的自動匹配 [J].軟件工程師，2013（12）：21-22.

[2] 佘向飛，于萍.基于Java反射機(jī)制與POI自動導(dǎo)出excel的實現(xiàn) [J].數(shù)字技術(shù)與應(yīng)用，2014（9）：94.

[3] 陶袁，張志軍.XML文檔在采集和處理Excel文檔中的應(yīng)用 [J].白城師范學(xué)院學(xué)報，2005（3）：32-34.

[4] 殷麗，徐海華，吳海濤.MySQL查詢優(yōu)化技術(shù)——索引 [A].第八屆工業(yè)儀表與自動化學(xué)術(shù)會議論文集 [C].北京：中國儀器儀表學(xué)會，2007：490-491.