Java嵌入SPL輕松實現Excel文件合并-創(chuàng)新互聯

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

創(chuàng)新互聯服務緊隨時代發(fā)展步伐，進行技術革新和技術進步，經過十年的發(fā)展和積累，已經匯集了一批資深網站策劃師、設計師、專業(yè)的網站實施團隊以及高素質售后服務人員，并且完全形成了一套成熟的業(yè)務流程，能夠完全依照客戶要求對網站進行成都網站設計、成都網站制作、建設、維護、更新和改版，實現客戶網站對外宣傳展示的首要目的，并為客戶企業(yè)品牌互聯網化提供全面的解決方案。

SPL實現

常規(guī)合并：

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

下面的例子是一個包含了銷售數據的 excel 文件，其中包含了按月劃分的 3 個結構相同的 sheet 工作表，數據如下：

January_2013:

Java 嵌入 SPL 輕松實現 Excel 文件合并

February_2013:

Java 嵌入 SPL 輕松實現 Excel 文件合并

March_2013:

Java 嵌入 SPL 輕松實現 Excel 文件合并

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

Java 嵌入 SPL 輕松實現 Excel 文件合并

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 函數遍歷 A1 序列中所有的成員工作表，導入每個工作表中指定列'Customer Name','Sale Amount'，并將數據并合并。其中 xlsimport 函數導入指定列，最后一列用分號; 隔開。 參數~.stname表示指定當前工作表，由于在 conj 函數的循環(huán)中，所以就可以逐個導入所有工作表。同時，xlsimport 使用選項@t指明將工作表的第一行記錄作為字段名。

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

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

B.  不同 excel 中的多表合并

下面是要合并的多個 excel 文件，它們都具有和上面例子相同的表結構，每個文件記錄了當年的數據：
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")


合并后的結果如下：

Java 嵌入 SPL 輕松實現 Excel 文件合并

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

腳本說明：

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

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

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

B3：將序表 B2 的數據與 @表示的本網格的值進行合并。

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

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

C.  合并出大文件

前面第一個例子中的 A2、第二個例子中的 B3 都是在內存中裝載了合并后的 Excel 的所有數據，然后一次性寫出。如果文件太多太大，那么對內存的占用也會很大，甚至超出內存允許的范圍。為此，我們可以在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")

合并后的效果如下：

Java 嵌入 SPL 輕松實現 Excel 文件合并

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

腳本說明：

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

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

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

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

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

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

JAVA調用

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

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

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

拓展節(jié)選

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

分組匯總

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


A.  字段分組

根據某個字段或多個字段實現分組計算，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的效果：

Java 嵌入 SPL 輕松實現 Excel 文件合并

 A4的效果：

Java 嵌入 SPL 輕松實現 Excel 文件合并

腳本說明：

A1: 打開指定的 excel 文件。

A2：讀取并合并文件中所有 sheet 工作表的數據。

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

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

B.  按序分組

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

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 分組效果：

Java 嵌入 SPL 輕松實現 Excel 文件合并

A6 分組效果：

Java 嵌入 SPL 輕松實現 Excel 文件合并

腳本說明：

A1至B3: 在前面的例子中已經介紹，將同一目錄下所有相同結構的 excel 文件的工作表進行合并。

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

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

A6：groups@o 按年月分組匯總銷售額、平均值, 帶參數 @o 實現分組歸并處理。

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

C.  分段分組

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

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)


分組效果：

Java 嵌入 SPL 輕松實現 Excel 文件合并

代碼說明：

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

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

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


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)


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

D.  大數據分組

前面的例子中，要讀取的 excel 文件都不能很大，也就是都能一次讀進內存。手工處理大文件，也會有類似的要求，因為同時打開多個文件，意味著把這些文件都裝入內存，很可能會超過機器的物理內存，而用 VBA 讀取的情況也差不多。這時，我們就需要用流式的方法讀取數據，不需一次讀進內存，而是邊讀取邊合并。

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")


篩選分組的效果：

Java 嵌入 SPL 輕松實現 Excel 文件合并

代碼說明：

A1: 使用 @r 選項指明以流式打開 excel 文件。

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

B2：使用 @c 選項指明以游標方式導入數據。

B3：將游標B2匯集到B3序列中。

A4：將游標序列B3的成員合并到一起組成新的游標。

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

A5：將結果保存。

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

去重處理

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

A.  主鍵去重

sales_2013中的數據，設其主鍵為’Invoice Number’，則根據主鍵去掉重復記錄。


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")

合并去重后的數據：

Java 嵌入 SPL 輕松實現 Excel 文件合并

代碼說明：

A1: 打開指定的 excel 文件。

A2：導入 sheet 工作表中指定列的數據。

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

A4：將結果保存。

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

B.  某字段去重

根據數據表 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：導入 sheet 工作表中指定列的數據。

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

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

A5：將序表 A4 另存，首行記錄為標題。

A3 數據去重結果：

Java 嵌入 SPL 輕松實現 Excel 文件合并

A4 數據去重結果：

Java 嵌入 SPL 輕松實現 Excel 文件合并

C.  聯合多字段去重

有的記錄雖然有主鍵，但判斷是否為重復的記錄，需要用其它幾個字段來確定，此時用多個字段聯合來確定是否有重復記錄.


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: 導入指定 excel 文件的數據。

A2：同上。

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

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

A5：將結果保存。

當然，也可以根據需要，參考更多的字段進行分組合并，去掉重復記錄。

D.  記錄級去重

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


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: 導入 excel 文件的數據。

B1：根據字段'Invoice Number'去掉序表 A1中的重復數據。

A2、B2：同上

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

B3：查看合并后的數據記錄數。

merge@u適合對多序表合并處理, 其中序表內部有序且無重復數據。

總結

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

SPL優(yōu)勢

  • 有庫寫 SQL，沒庫寫 SPL

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

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

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

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

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

另外有需要云服務器可以了解下創(chuàng)新互聯scvps.cn，海內外云服務器15元起步，三天無理由+7*72小時售后在線，公司持有idc許可證，提供“云服務器、裸金屬服務器、高防服務器、香港服務器、美國服務器、虛擬主機、免備案服務器”等云主機租用服務以及企業(yè)上云的綜合解決方案，具有“安全穩(wěn)定、簡單易用、服務可用性高、性價比高”等特點與優(yōu)勢，專為企業(yè)上云打造定制，能夠滿足用戶豐富、多元化的應用場景需求。

網站題目：Java嵌入SPL輕松實現Excel文件合并-創(chuàng)新互聯
標題來源：http://bm7419.com/article12/ippgc.html

成都網站建設公司_創(chuàng)新互聯，為您提供外貿網站建設、網站維護、小程序開發(fā)、服務器托管、網站導航、面包屑導航

廣告

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

營銷型網站建設