使用開源計算引擎提升Excel格式文件處理效率
對Excel進行解析\生成\查詢\計算等處理是Java下較常見的任務，但Excel的文件格式很復雜，自行編碼讀寫太困難，有了POI\EasyExcel\JExcel等類庫就方便多了，其中POI最為出色。
POI具有全面而細致的xls讀寫能力
POI可讀寫多種Excel文件格式，既支持古老的二進制格式（xls），也支持現代的OOXML格式（xlsx），既支持全內存一次性讀寫，也支持小內存流式讀寫。POI為大量Excel元素設計了相應的JAVA類，包括workbook、printer、sheet、row、cell，其中，與cell相關的類包括單元格樣式、字體、顏色、日期、對齊、邊框等。僅單元格樣式類，方法就超過了四十個，可進行最全面最細致的讀寫操作。
POI的讀寫功能很底層
POI的讀寫功能全面而細致，但細致也意味著過于底層，開發者必須從頭寫起，自己處理每一處細節，即使簡單的操作也要編寫大量代碼。比如，讀入首行為列名的行式xls：
FileInputStream fileInputStream = new FileInputStream("d:\\Orders.xls");
// get the excel book
Workbook workbook = new HSSFWorkbook(fileInputStream);
if (workbook != null) {
// get the first sheet
Sheet sheet = workbook.getSheetAt(0);
if (sheet != null) {
//get the col name/first line
Row rowTitle = sheet.getRow(0); // first line
if (rowTitle != null) {
int cellTitles = rowTitle.getPhysicalNumberOfCells(); // get column number
for (int i = 0; i < cellTitles; i++) {
Cell cell = rowTitle.getCell(i); //the cell!
if (cell != null) {
System.out.print(cell.getStringCellValue() + " | ");
}
}
}
//get the value/other lines
int rows = sheet.getPhysicalNumberOfRows(); // get line number
for (int i = 1; i < rows; i++) {
Row row = sheet.getRow(i); // get row i
if (row != null) {
int cells = row.getPhysicalNumberOfCells(); // get column number
for (int j = 0; j < cells; j++) {
// line number and row number
System.out.print("[" + i + "-" + j + "]");
Cell cell = row.getCell(j); // the cell!
if (cell != null) {
int cellType = cell.getCellType();
Object value = "";
switch (cellType) {
case HSSFCell.CELL_TYPE_STRING: // string
value = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_BLANK: // 空
break;
case HSSFCell.CELL_TYPE_BOOLEAN: // boolean
value = cell.getBooleanCellValue();
break;
case HSSFCell.CELL_TYPE_NUMERIC: // number
if (HSSFDateUtil.isCellDateFormatted(cell)) { // date number
Date date = cell.getDateCellValue();
value = new DateTime(date).toString("yyyy-MM-dd HH:mm:ss");
}else { // normal number
// change to string to avoid being too long
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
value = cell;
}
break;
case HSSFCell.CELL_TYPE_ERROR:
throw new RuntimeException("data type mistaken");
}
System.out.println(value);
}
}
}
System.out.println("end of the "+i+" line");
}
System.out.println("end of the value lines=======================================");
}
}
行式xls是最常見的格式，但POI并沒有為此提供方便的處理方法，只能按照workbook->sheet->line->cell的順序進行循環解析，造成了如此繁瑣的代碼。
這還只是將數據簡單讀出來，如果下一步想再處理數據，還要事先轉為結構化數據對象，比如ArrayList<實體類>或HashMap，代碼就更繁瑣了。
POI查詢計算困難
解析Excel并不是目標，我們通常還要對這些文件進查詢計算，但POI作為Excel的解析類，沒有也不合適再提供相關的方法，只能用JAVA手工硬寫。比如基礎的分組匯總運算，JAVA代碼大概這樣：
Comparator<salesRecord> comparator = new Comparator<salesRecord>() {
public int compare(salesRecord s1, salesRecord s2) {
if (!s1.salesman.equals(s2.salesman)) {
return s1.salesman.compareTo(s2.salesman);
} else {
return s1.ID.compareTo(s2.ID);
}
}
};
Collections.sort(sales, comparator);
ArrayList<resultRecord> result=new ArrayList<resultRecord>();
salesRecord standard=sales.get(0);
float sumValue=standard.value;
for(int i = 1;i < sales.size(); i ++){
salesRecord rd=sales.get(i);
if(rd.salesman.equals(standard.salesman)){
sumValue=sumValue+rd.value;
}else{
result.add(new resultRecord(standard.salesman,sumValue));
standard=rd;
sumValue=standard.value;
}
}
result.add(new resultRecord(standard.salesman,sumValue));
Java編碼實現計算不僅繁瑣，而且存在架構性缺陷。代碼很難復用，數據結構和計算代碼通常會耦合在一起，如果數據結構發生變化，代碼就要重寫。查詢計算的要求靈活多變，而Java作為編譯型語言，每次修改代碼都要重啟應用，維護工作量大，系統穩定性差。
POI成熟穩定，但讀寫能力過于底層，且未提供查詢計算能力，直接基于POI完成Excel文件的處理（特別是查詢計算）的開發效率很低。如果針對POI進行封裝，形成簡單易用的高級讀寫函數，并額外提供查詢計算能力，就能大幅度提高開發效率了。
esProc SPL就是其中的佼佼者。
SPL內置高級讀寫函數
SPL是JVM下開源的計算引擎，它對POI也進行了封裝，內置簡單易用的高級函數，可解析\生成各類格式規則或不規則的xls，并自動生成結構化數據對象。
解析格式規則的行式Excel，SPL提供了T函數。比如解析前面的xls文件，用封裝前的POI要幾十行，封裝后只要一句：
=T("d:\Orders.xls")
解析行式Excel是很常見的任務，SPL用T函數封裝了POI的功能，接口簡單易用。無論xls還是xlsx，T函數都可以統一解析?？勺詣舆M行類型轉換，開發者無須在細節浪費時間。T函數可自動區分首行的列名和其他行的數據，并根據列名創建序表（SPL的結構化數據對象）并填入數據：
讀入并解析成序表后，就可以使用SPL提供的豐富的結構化數據處理方法了：
取第3條記錄：A1(3)
取后3條記錄:A1.m([-1,-2,-3])
取記錄的字段值：A1(3).Amount*0.05
修改記錄的字段值：A1(3).Amount = A1(3). Amount*1.05
取一列，返回集合：A1.(Amount)
取幾列，返回集合的集合：A1.([CLIENT,AMOUNT])
追加記錄：A1.insert(200,"APPL",10,2400.4,date("2010-10-10"))
先按字段取再按記錄序號?。篈1.(AMOUNT)(2)；等價于先按記錄序號取再按字段?。篈1(2).AMOUNT
解析格式較不規則的行式xls，SPL提供了xlsimport函數，內置豐富而簡潔的讀取功能：
沒有列名，首行直接是數據：file("D:\Orders.xlsx").xlsimport()
跳過前2行的標題區：file("D:/Orders.xlsx").xlsimport@t(;,3)
從第3行讀到第10行：file("D:/Orders.xlsx").xlsimport@t(;,3:10)
只讀取其中3個列：file("D:/Orders.xlsx").xlsimport@t(OrderID,Amount,OrderDate)
讀取名為"sales"的特定sheet：file("D:/Orders.xlsx").xlsimport@t(;"sales")
函數xlsimport還具有讀取倒數N行、密碼打開文件、讀大文件等功能，這里不再詳述。
解析格式很不規則的xls，SPL提供了xlscell函數，可以讀寫指定sheet里指定片區的數據，比如讀取第1個sheet里的A2格：
=file("d:/Orders.xlsx").xlsopen().xlscell("C2")
配合SPL靈活的語法，就可以解析自由格式的xls，比如將下面的文件讀為規范的二維表（序表）：
這個文件格式很不規則，直接基于POI寫Java代碼是個浩大的工程，而SPL代碼就簡短得多：
生成規則的行式xls，SPL提供了xlsexport函數，用法也很簡單。比如，上面例子的解析結果是個序表，存在SPL的A1格中，下面將A1寫入新xls的第一個sheet，首行為列名，只要一句代碼：=file("e:/result.xlsx").xlsexport@t(A1)
xlsexport函數的功能豐富多樣，可以將序表寫入指定sheet，或只寫入序表的部分行，或只寫入指定的列：=file("e:/scores.xlsx").xlsexport@t(A1,No,Name,Class,Maths)
xlsexport函數還可以方便地追加數據，比如對于已經存在且有數據的xls，將序表A1追加到該文件末尾，外觀風格與原文件末行保持一致：=file("e:/scores.xlsx").xlsexport@a(A1)
不規則片區寫入數據，可以使用前面的xlscell函數。比如，xls中藍色單元格是不規則的表頭，需要在相應的白色單元格中填入數據，如下圖：
直接用POI要大段冗長的代碼，而SPL代碼就簡短許多：
注意，第6、9、11行有連續單元格，SPL可以簡化代碼一起填入，POI只能依次填入。
SPL提供足夠的查詢計算能力
查詢計算是Excel處理任務的重點，SPL提供了豐富的計算函數、字符串函數、日期函數，以及標準SQL語法，不僅支持日常的xls計算，也能計算內容不規則的xls和邏輯復雜的xls。
SPL提供了豐富的計算函數，可直接完成基礎計算。比如前面的分組匯總，只要一句：
A1.groups(SellerId;sum(Amount))
更多計算：
條件查詢：A1.select(Amount>1000 && Amount<=3000 && like(Client,"S"))
排序：A1.sort(Client,-Amount)"
去重：A1.id(Client)"
關聯兩個xlsx：join(T("D:/Orders.xlsx")??,SellerId; T("D:/Employees.xls"):E,EId).new(O.OrderID,O.Client,O.SellerId,O.Amount,O.OrderDate, E.Name,E.Gender,E.Dept)"
TopN：T("D:/Orders.xls").top(-3;Amount)
組內TopN （開窗函數）：T("D:/Orders.xls").groups(Client;top(3,Amount))
SPL支持大量日期函數和字符串函數，代碼量更短，開發效率更高。比如：
時間類函數，日期增減：elapse("2020-02-27",5) //返回2020-03-03
星期幾：day@w("2020-02-27") //返回5，即星期4
N個工作日之后的日期：workday(date("2022-01-01"),25) //返回2022-02-04
字符串類函數，判斷是否全為數字：isdigit("12345") //返回true
取子串前面的字符串：substr@l("abCDcdef","cd") //返回abCD
按豎線拆成字符串數組："aa|bb|cc".split("|") //返回["aa","bb","cc"]
SPL還支持年份增減、求年中第幾天、求季度、按正則表達式拆分字符串、拆出SQL的where或select部分、拆出單詞、按標記拆HTML等功能。
SPL提供了標準SQL語法，可以像對數據庫表一樣直接對xls文件進行查詢，極大地降低了數據庫程序員的學習門檻：
filter：$select * from d:/sOrder.xlsx where Client like '%S%' or (Amount>1000 and Amount<=2000)sort：$select * from sales.xls order by Client,Amont descdistinct：$ select distinct(sellerid) from sales.xls group by…having：$select year(orderdate) y,sum(amount) s from sales.xls group by year(orderdate) having sum(amount)>=2000000join：$select e.name, s.orderdate, s.amount from sales.xls s left join employee.xlsx e on s.sellerid= e.eid
SPL支持SQL-92標準中大部分語法，包括集合計算、case when、with、嵌套子查詢等，詳見《沒有 RDB 也敢攬 SQL 活的開源金剛鉆 SPL》
內容不規則的xls，一般的類庫都無能為力，SPL語法靈活函數豐富，可輕松解決處理。比如Excel單元格里有很多”key=value”形式的字符串，需要整理成規范的二維表，以進行后續計算：
邏輯復雜的計算，SQL和存儲過程都難以實現，SPL的計算能力更強，可輕松解決此類問題。比如，計算某支股票最長的連續上漲天數：
SPL支持更優的應用架構
SPL是解釋型語言，提供JDBC接口，可以用SQL或存儲過程的形式被JAVA集成，不僅降低了架構的耦合性，還能支持熱切換。SPL還支持多種數據源，并支持跨數據源計算。
SPL提供了JDBC接口，可被JAVA輕松調用。簡單的SPL代碼可以像SQL一樣，直接嵌入JAVA，比如條件查詢：
Class.forName("com.esproc.jdbc.InternalDriver");
Connection connection =DriverManager.getConnection("jdbc:esproc:local://");
Statement statement = connection.createStatement();
String str="=T(\"D:/Orders.xls\").select(Amount>1000 && Amount<=3000 && like(Client,\"*S*\"))";
ResultSet result = statement.executeQuery(str);
SPL支持計算外置，可降低計算代碼和前端應用的耦合性。復雜的SPL代碼可以先存為腳本文件，再以存儲過程的形式被JAVA調用：
Class.forName("com.esproc.jdbc.InternalDriver");
Connection conn =DriverManager.getConnection("jdbc:esproc:local://");
CallableStatement statement = conn.prepareCall("{call scriptFileName(?, ?)}");
statement.setObject(1, "2020-01-01");
statement.setObject(2, "2020-01-31");
statement.execute();
SPL是解釋型語言，通過外置代碼可實現熱切換。解釋型語言無須編譯，修改后可立即執行，無須重啟JAVA應用，可降低維護工作量，提高系統穩定性。
SPL支持多種文件數據源，除了xls外，SPL還能讀寫csv\txt\XML\Json等文件，比如對txt進行條件查詢：
T("sOrders.txt").groups(SellerId;sum(Amount))
$select * from d:/sOrders.txt where Client like '%S%' or (Amount>1000 and Amount<=2000)
SPL支持跨數據源計算，比如xls和txt的關聯計算：
=join(T("D:/Orders.xlsx")??,SellerId; T("D:/Employees.txt"):E,EId).new(O.OrderID,O.Client,O.SellerId,O.Amount,O.OrderDate, E.Name,E.Gender,E.Dept)"
SPL還能訪問各類關系型數據庫，WebService、Restful等網絡服務， Hadoop、redis、Kafka、Cassandra等NoSQL。
POI只適合簡單的xls解析\生成任務，且未提供查詢計算能力。SPL對POI進行了封裝，內置高級讀寫函數，不僅可以大幅簡化代碼，還能進行較不規則甚至很不規則的xls解析\生成任務。SPL額外提供了強大的計算能力，不僅支持日常的Excel查詢計算，還可計算內容不規則的xls和邏輯復雜的xls。SPL支持更優的應用架構，可實現代碼低耦合和熱切換，支持多種數據源和跨數據源計算。
SPL資料
歡迎對SPL有興趣的加小助手（VX號：SPL-helper），進SPL技術交流群
歡迎關注我的公告號：字母哥雜談，回復003贈送作者專欄《docker修煉之道》的PDF版本，30余篇精品docker文章。字母哥博客：zimug.com
浙公網安備 33010602011771號