大多數(shù)JAVA程序猿都選擇使用POI或者HSSFWorkbook等第三方類庫來實(shí)現(xiàn)Excel自動化合并，這樣一來不僅需要噼里啪啦的敲好多代碼，費(fèi)事費(fèi)力，而且用起來靈活度也不高，對Excel的格式要求也很嚴(yán)格。如果能有一個專用的外部數(shù)據(jù)工具，寫簡單類似SQL的腳本來實(shí)現(xiàn)，然后在JAVA中直接調(diào)用并返回結(jié)果集，就再好不過了。潤乾集算器就是這樣的機(jī)制，通過使用JDBC調(diào)用SPL腳本，使用起來方便快捷，下面就來學(xué)習(xí)下具體如何使用。

成都創(chuàng)新互聯(lián)專注為客戶提供全方位的互聯(lián)網(wǎng)綜合服務(wù)，包含不限于成都做網(wǎng)站、成都網(wǎng)站建設(shè)、舒城網(wǎng)絡(luò)推廣、微信小程序開發(fā)、舒城網(wǎng)絡(luò)營銷、舒城企業(yè)策劃、舒城品牌公關(guān)、搜索引擎seo、人物專訪、企業(yè)宣傳片、企業(yè)代運(yùn)營等，從售前售中售后，我們都將竭誠為您服務(wù)，您的肯定，是我們最大的嘉獎；成都創(chuàng)新互聯(lián)為所有大學(xué)生創(chuàng)業(yè)者提供舒城建站搭建服務(wù)，24小時(shí)服務(wù)熱線：028-86922220，官方網(wǎng)址：aaarwkj.com

SPL實(shí)現(xiàn)

常規(guī)合并：

A.  同一個 excel 中的多個Sheet表合并

下面的例子是一個包含了銷售數(shù)據(jù)的 excel 文件，其中包含了按月劃分的 3 個結(jié)構(gòu)相同的 sheet 工作表，數(shù)據(jù)如下：

January_2013:

 

February_2013:

 

March_2013:

在合并3個Sheet的同時(shí)，我們還可以同時(shí)從每個sheet中篩選出字段Customer Name, Sale Amount。最后的效果如下：

SPL腳本：

	A
1	=file(”D:/sales_2013.xlsx”).xlsopen()
2	=A1.conj(A1.xlsimport@t('Customer Name','Sale   Amount';~.stname))
3	>file(“D:/result_2013.xlsx”).     xlsexport@t(A2;"merge_sheets")

保存腳本文件oneExcel.dfx(嵌入Java會用到)

腳本說明：

A1: 打開指定的 excel 文件，創(chuàng)建一個由多個 sheet 工作表組成的序列。

A2：利用 conj 函數(shù)遍歷 A1 序列中所有的成員工作表，導(dǎo)入每個工作表中指定列'Customer Name','Sale Amount'，并將數(shù)據(jù)并合并。其中 xlsimport 函數(shù)導(dǎo)入指定列，最后一列用分號; 隔開。 參數(shù)~.stname表示指定當(dāng)前工作表，由于在 conj 函數(shù)的循環(huán)中，所以就可以逐個導(dǎo)入所有工作表。同時(shí)，xlsimport 使用選項(xiàng)@t指明將工作表的第一行記錄作為字段名。

A3:將序表 A2 作為一個新的工作表“merge_sheets”保存到原來的 excel 文件中，同樣用選項(xiàng) @t 指明首行記錄為標(biāo)題。

這段腳本只有三句話，短小精干之余，邏輯清晰，也比較容易理解。下面我們再看看如何合并多個文件中的多個工作表。

B.  不同 excel 中的多表合并

下面是要合并的多個 excel 文件，它們都具有和上面例子相同的表結(jié)構(gòu)，每個文件記錄了當(dāng)年的數(shù)據(jù)：
SPL腳本：

	A	B
1	for   directory@p(”d:/excel/*.xlsx“)	=file(A1).xlsopen()
2		=B1.conj(B1.xlsimport@t('Customer     Name','Sale Amount','Purchase Date';~.stname))
3		=@|B2
4	>file(“d:/result.xlsx”).   xlsexport@t(B3;"merge_data")

合并后的結(jié)果如下：

保存腳本文件MergeExcels.dfx(嵌入Java會用到)

腳本說明：

A1: 通過 for 循環(huán)，遍歷指定目錄下的 excel 文件，在 B1 到 B3 之間進(jìn)行循環(huán)內(nèi)處理。

B1：打開目錄下的一個 excel 文件，生成序列。

B2：導(dǎo)入當(dāng)前文件中的每個 sheet 工作表中指定列'Customer Name','Sale Amount'，'Purchase Date'的數(shù)據(jù)，然后合并這些數(shù)據(jù)，與前面例子中的 A2 類似。

B3：將序表 B2 的數(shù)據(jù)與 @表示的本網(wǎng)格的值進(jìn)行合并。

A4：將序表 B3 保存到result.xlsx文件中的 merge_data 工作表中。

上面程序用兩個循環(huán)就實(shí)現(xiàn)了多個 excel 文件數(shù)據(jù)合并，外循環(huán) for 遍歷了目錄下所有的 excel 文件，內(nèi)循環(huán)B1.conj則合并每個excel文件中的多個sheet工作表的數(shù)據(jù)。

 

C.  合并出大文件

前面第一個例子中的 A2、第二個例子中的 B3 都是在內(nèi)存中裝載了合并后的 Excel 的所有數(shù)據(jù)，然后一次性寫出。如果文件太多太大，那么對內(nèi)存的占用也會很大，甚至超出內(nèi)存允許的范圍。為此，我們可以在SPL腳本中采用流式追加的方式生成大文件。

SPL腳本：

	A	B
1	=file("D:/out.xlsx")
2	for   directory@p(”d:/excel/*.xlsx“)	=file(A2).xlsopen()
3		=if(A1.exists(),B2.xlsimport@t(),B2.xlsimport())
4		>A1.xlsexport@s(B3;"merger")

合并后的效果如下：

保存腳本文件BigExcel.dfx(嵌入Java會用到)

腳本說明：

A1：打開指定輸出的文件。

A2：遍歷目錄下需要合并的 excel 文件。

B2：打開一個需要合并的 excel 文件。

B3：如果輸出文件不存在，讀取 sheet 工作表的所有數(shù)據(jù)，包括標(biāo)題行；如果輸出文件已經(jīng)有了，就通過 @t 選項(xiàng)指明第一行是標(biāo)題，從第二行開始讀取數(shù)據(jù)。

B4：將 B3 讀取的數(shù)據(jù)以流式追加到 A1 指定的輸出文件的 merger 工作表中。

通過流式逐個讀取文件數(shù)據(jù)后追加寫入，這個方式適合將大量小的 excel 文件合并成一個大的 excel 文件。

JAVA調(diào)用

SPL嵌入到Java應(yīng)用程序十分方便，通過JDBC調(diào)用存儲過程方法加載，用同一個excel中的多個Sheet表合并保存的文件OneExcel.dfx，示例調(diào)用如下：

...
Connection con = null;
Class.forName("com.esproc.jdbc.InternalDriver");
con= DriverManager.getConnection("jdbc:esproc:local://");//調(diào)用存儲過程，其中OneExcel是dfx的文件名
st =(com. esproc.jdbc.InternalCStatement)con.prepareCall("call OneExcel()");//執(zhí)行存儲過程
st.execute();//獲取結(jié)果集
ResultSet rs = st.getResultSet();
......
Connection con = null;
Class.forName("com.esproc.jdbc.InternalDriver");
con= DriverManager.getConnection("jdbc:esproc:local://");//調(diào)用存儲過程，其中OneExcel是dfx的文件名st =(com. esproc.jdbc.InternalCStatement)con.prepareCall("call OneExcel()");//執(zhí)行存儲過程st.execute();//獲取結(jié)果集ResultSet rs = st.getResultSet();
...

替換成MergeExcels.dfx/BigExcel.dfx是同樣的道理，只需call MergeExcels()或者call BigExcel()即可。這里只用 Java 片段粗略解釋了如何嵌入 SPL，詳細(xì)步驟請參閱Java 如何調(diào)用 SPL 腳本，也非常簡單，不再贅述。同時(shí)，SPL 也支持 ODBC 驅(qū)動，集成到支持 ODBC 的語言，嵌入過程類似。

拓展節(jié)選

關(guān)于Excel文件的處理除了像上面講的普通合并外，還可以在SPL腳本中對匯總后的結(jié)果分組去重，然后再將結(jié)果集導(dǎo)出。

分組匯總

下面繼續(xù)以前面的銷售數(shù)據(jù) excel 文件為例。

A.  字段分組

根據(jù)某個字段或多個字段實(shí)現(xiàn)分組計(jì)算，SPL 腳本如下：

	A
1	=file(”D:/sales_2013.xlsx”).xlsopen()
2	=A1.conj(A1.xlsimport@t(;~.stname))
3	=A2.groups('Customer ID';sum('Sale     Amount'):Total,avg('Sale Amount'):Average)
4	=A2.groups('Customer ID','Purchase   Date';sum('Sale   Amount'):Total)

A3的效果：

 A4的效果：

腳本說明：

A1: 打開指定的 excel 文件。

A2：讀取并合并文件中所有 sheet 工作表的數(shù)據(jù)。

A3：在合并后的數(shù)據(jù)上按字段 'Customer ID' 分組求銷售額、平均值。

A4：在合并后的數(shù)據(jù)上按字段 'Customer ID', 'Purchase Date' 分組求銷售額。

B.  按序分組

集算器在進(jìn)行分組聚合時(shí)還可以和相鄰數(shù)據(jù)行對比，在原數(shù)據(jù)已經(jīng)有序時(shí)可以不再排序，從而節(jié)省時(shí)間，并保持原有的次序。假設(shè)原數(shù)據(jù)已經(jīng)按日期排序，我們想按月份分組統(tǒng)計(jì)時(shí)，代碼如下。

SPL 腳本：

	A	B
1	for directory@p(”d:/excel/*.xlsx“)	=file(A1).xlsopen()
2		=B1.conj(B1.xlsimport@t(;~.stname))
3		=@|B2
4	=B3.derive(year('Purchase Date'):Year,month('Purchase     Date'):Month)
5	=A4.groups (month('Purchase   Date'):Month;sum('Sale   Amount'):Total,avg('Sale Amount'):Average)
6	=A4.groups@o (month('Purchase   Date'):Month;sum('Sale   Amount'):Total,avg('Sale Amount'):Average)

A5 分組效果：

A6 分組效果：

腳本說明：

A1至B3: 在前面的例子中已經(jīng)介紹，將同一目錄下所有相同結(jié)構(gòu)的 excel 文件的工作表進(jìn)行合并。

A4：在序表 B3 的基本上重新構(gòu)造了一個序表 A4，將日期拆分，新增年、月字段。

A5：groups 跨年度按月分組匯總銷售額、平均值。

A6：groups@o 按年月分組匯總銷售額、平均值, 帶參數(shù) @o 實(shí)現(xiàn)分組歸并處理。

其中，A4 為數(shù)據(jù)記錄明細(xì)；A5 按月統(tǒng)計(jì), 不區(qū)分年；A6 則按年月統(tǒng)計(jì)。這三個單元格中的數(shù)據(jù)展現(xiàn)出了不同層次的合并匯總結(jié)果。

C.  分段分組

將要統(tǒng)計(jì)的數(shù)據(jù)按條件分成幾段，統(tǒng)計(jì)各組的情況。

SPL 腳本：

	A	B
1	for   directory@p(”d:/excel/*.xlsx“)	=file(A1).xlsopen()
2		=B1.conj(B1.xlsimport@t(;~.stname))
3		=@|B2
4	=B3.groups(if   ('Sale   Amount'<1000,"1::<1000",if   ('Sale   Amount'<1500,"2::1000~~1500",if   ('Sale Amount'<2000,"3::1500~~2000",if     ('Sale   Amount'<2500,"4::2000~~2500","5::>=2500")))):Segment;count(1):Number,sum('Sale     Amount'):Total)

分組效果：

代碼說明：

步驟A1到 B3 之間參考前面例子的說明。

A4：字段'Sale Amount'金額的范圍分成 5 段，然后累計(jì)求出各段的數(shù)量及總數(shù)。

不過，這樣的寫法不夠方便，如果我們想調(diào)整分段方案，就需要修改 groups 函數(shù)的參數(shù)，而這個參數(shù)表達(dá)式還是比較復(fù)雜的。這時(shí)，我們還可以利用集算器中另一個 pseg 函數(shù)，更方便地實(shí)現(xiàn)這個功能，腳本如下：

	A	B
1	[0,1000,1500,2000,2500]
2	for directory@p(”d:/excel/*.xlsx“)	=file(A1).xlsopen()
3		=B1.conj(B1.xlsimport@t(;~.stname))
4		=@|B2
5	=B4.groups(A1.pseg(~.'Sale     Amount'):Segment;count(1):Number,sum('Sale Amount'):Total)

當(dāng)然，我們也可以根據(jù)需要，按不同字段不同要求進(jìn)行分組，然后進(jìn)行統(tǒng)計(jì)處理。例如，在統(tǒng)計(jì)班級考生成績時(shí)，各科成績可劃分成優(yōu)、良、中、差、及格的分?jǐn)?shù)區(qū)段，一次為條件進(jìn)行統(tǒng)計(jì)。groups 用法還有很多，可以參考函數(shù)手冊中相應(yīng)的章節(jié)。

D.  大數(shù)據(jù)分組

前面的例子中，要讀取的 excel 文件都不能很大，也就是都能一次讀進(jìn)內(nèi)存。手工處理大文件，也會有類似的要求，因?yàn)橥瑫r(shí)打開多個文件，意味著把這些文件都裝入內(nèi)存，很可能會超過機(jī)器的物理內(nèi)存，而用 VBA 讀取的情況也差不多。這時(shí)，我們就需要用流式的方法讀取數(shù)據(jù)，不需一次讀進(jìn)內(nèi)存，而是邊讀取邊合并。 

SPL 腳本：

	A	B
1	=file(“d:/tdata.xlsx”).xlsopen@r()
2	for   A1.count()	=A1.xlsimport@ct(;A1(A2). stname)
3		=@|B2
4	= B3.conjx()	=A4.groups('Customer   ID';sum('Sale   Amount'):SaleTotal)
5	>file(“d:/out.xlsx”).exportxls@bt(B4;"Customer&Sales")

篩選分組的效果：

代碼說明：

A1: 使用 @r 選項(xiàng)指明以流式打開 excel 文件。

A2：遍歷 excel 中的 sheet 工作表。

B2：使用 @c 選項(xiàng)指明以游標(biāo)方式導(dǎo)入數(shù)據(jù)。

B3：將游標(biāo)B2匯集到B3序列中。

A4：將游標(biāo)序列B3的成員合并到一起組成新的游標(biāo)。

B4：序列A4按‘Customer ID’分組累計(jì)‘Sale Amount’。

A5：將結(jié)果保存。

通過游標(biāo)以流的方式循環(huán)從大文件中讀取一段段數(shù)據(jù)，實(shí)現(xiàn)對數(shù)據(jù)的分組合并。

去重處理

實(shí)際數(shù)據(jù)合并過程中，往往會出現(xiàn)數(shù)據(jù)重復(fù)的現(xiàn)象，重復(fù)數(shù)據(jù)肯定會影響到我們對數(shù)據(jù)的計(jì)算分析。下面介紹使用集算器 SPL 腳本去除重復(fù)數(shù)據(jù)的幾種主要解決方法。

A.  主鍵去重

sales_2013中的數(shù)據(jù)，設(shè)其主鍵為’Invoice Number’，則根據(jù)主鍵去掉重復(fù)記錄。

	A
1	=file(“d:/sales_2013.xlsx”).xlsopen()
2	=A1.conj(A1.xlsimport@t('Customer   Name', 'Invoice   Number', 'Sale Amount';~. stname))
3	=A2.group@1('Invoice   Number')
4	>file(“d:/out.xlsx”).     xlsexport@t(A3;"result")

合并去重后的數(shù)據(jù)：

代碼說明：

A1: 打開指定的 excel 文件。

A2：導(dǎo)入 sheet 工作表中指定列的數(shù)據(jù)。

A3：將序表 A2 按主鍵'Invoice Number'分組去重處理, 其中參數(shù) @1 表示取每一個分組的第一條記錄組成排列后返回（注意是數(shù)字 1，不是字母 l）。

A4：將結(jié)果保存。

各個 sheet> 中的數(shù)據(jù)是唯一的，但合并的數(shù)據(jù)不一定是唯一的，因此采用主鍵方式去掉重復(fù)數(shù)據(jù)。

B.  某字段去重

根據(jù)數(shù)據(jù)表 sales_2013 中的某字段去重處理, 查看不同姓名的雇員記錄.

	A
1	=file(“d:/sales_2013.xlsx”).xlsopen()
2	=A1.conj(A1.xlsimport@t('Customer   ID', 'Customer   Name';~. stname))
3	=A2.id('Customer   Name')
4	=A2.group@1('  Customer Name')
5	>file(“d:/out.xlsx”).     xlsexport@t(A4;"result")

代碼說明：

A1: 打開指定的 excel 文件。

A2：導(dǎo)入 sheet 工作表中指定列的數(shù)據(jù)。

A3：從序表 A2 中獲取不重復(fù)姓名的記錄。

A4：從序表 A2中獲取不重復(fù)姓名的記錄列表。

A5：將序表 A4 另存，首行記錄為標(biāo)題。

A3 數(shù)據(jù)去重結(jié)果：

A4 數(shù)據(jù)去重結(jié)果：

C.  聯(lián)合多字段去重

有的記錄雖然有主鍵，但判斷是否為重復(fù)的記錄，需要用其它幾個字段來確定，此時(shí)用多個字段聯(lián)合來確定是否有重復(fù)記錄.

	A
1	=file(“d:/sales_2013.xlsx”).importxls@t()
2	=file(“d:/sales_2014.xlsx”).importxls@t()
3	=[A1,A2].merge('Customer   ID', 'Purchase Date')
4	=A3.group@1('Customer   ID', 'Purchase Date')
5	>file(“d:/out.xlsx”).     xlsexport@t(A4;"result")

代碼說明：

A1: 導(dǎo)入指定 excel 文件的數(shù)據(jù)。

A2：同上。

A3：按字段 'Customer ID', 'Purchase Date' 合并序表 A1，A2，返回序表 A3。

A4：序表 A3 按 'Customer ID', 'Purchase Date' 分組去重。

A5：將結(jié)果保存。

當(dāng)然，也可以根據(jù)需要，參考更多的字段進(jìn)行分組合并，去掉重復(fù)記錄。

D.  記錄級去重

解決要合并的每個文件中的記錄本身是不重復(fù)的，但合并后可能存在重復(fù)記錄。

	A	B
1	=file(“d:/sales_2013.xlsx”).importxls@t()	=A1.group@1('Invoice   Number')
2	=file(“d:/sales_2014.xlsx”).importxls@t()	=A2.group@1('Invoice   Number')
3	=[B1,B2].merge@u()	=A3.count()

代碼說明：

A1: 導(dǎo)入 excel 文件的數(shù)據(jù)。

B1：根據(jù)字段'Invoice Number'去掉序表 A1中的重復(fù)數(shù)據(jù)。

A2、B2：同上

A3：合并序表 B1，B2 的數(shù)據(jù)，并去掉重復(fù)數(shù)據(jù)記錄返回序表 A3。選項(xiàng) @u 表示序表成員按順序合并到一起組成新的序表, 去掉重復(fù)的記錄。

B3：查看合并后的數(shù)據(jù)記錄數(shù)。

merge@u適合對多序表合并處理, 其中序表內(nèi)部有序且無重復(fù)數(shù)據(jù)。

總結(jié)

使用SPL處理同構(gòu)/異構(gòu)excel 多文件合并、分組匯總數(shù)據(jù)及數(shù)據(jù)去重時(shí)，都是只要把需要合并的字段讀成集算器的集合對象就可以了。學(xué)會了用這種專業(yè)數(shù)據(jù)處理工具，不僅能合并 Excel 文件, 合并其他文本數(shù)據(jù)方法也是一致的，再也不用擔(dān)心合并數(shù)據(jù)中的多文件、大文件和結(jié)構(gòu)差異問題了。

SPL優(yōu)勢

• 有庫寫 SQL，沒庫寫 SPL

用Java程序直接匯總計(jì)算數(shù)據(jù)，還是比較累的，代碼很長，并且不可復(fù)用，很多情況數(shù)據(jù)也不在數(shù)據(jù)庫里，有了SPL，就能像在Java中用SQL一樣了，十分方便。

• 常用無憂，不花錢就能取得終身使用權(quán)的入門版

如果要分析的數(shù)據(jù)是一次性或臨時(shí)性的，潤乾集算器每個月都提供免費(fèi)試用授權(quán)，可以循環(huán)免費(fèi)使用。但要和Java應(yīng)用程序集成起來部署到服務(wù)器上長期使用，定期更換試用授權(quán)還是比較麻煩，潤乾提供了有終身使用權(quán)的入門版，解決了這個后顧之憂，獲得方式參考 如何免費(fèi)使用潤乾集算器？

• 技術(shù)文檔和社區(qū)支持

官方提供的集算器技術(shù)文檔本身就有很多現(xiàn)成的例子，常規(guī)問題從文檔里都能找到解決方法。如果獲得了入門版，不僅能夠使用SPL的常規(guī)功能，碰到任何問題都可以去乾學(xué)院上去咨詢，官方通過該社區(qū)對入門版用戶提供免費(fèi)的技術(shù)支持。

網(wǎng)站名稱：Java嵌入SPL輕松實(shí)現(xiàn)Excel文件合并
URL地址：http://aaarwkj.com/article0/gghdio.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站維護(hù)、小程序開發(fā)、網(wǎng)站導(dǎo)航、建站公司、網(wǎng)頁設(shè)計(jì)公司、面包屑導(dǎo)航

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)