xcel等類庫就方便多了，其中POI最為出色。
POI具有全面而細致的xls讀寫能力
POI可讀寫多種Excel文件格式，既支持古老的二進制格式（xls），也支持現(xiàn)代的OOXML格式（xlsx），既支持全內(nèi)存一次性讀寫，也支持小內(nèi)存流式讀寫。POI為大量Excel元素設(shè)計了相應(yīng)的JAVA類，包括workbook、printer、sheet、row、cell，其中，與cell相關(guān)的類包括單元格樣式、字體、顏色、日期、對齊、邊框等。僅單元格樣式類，方法就超過了四十個，可進行最全面最細致的讀寫操作。
POI的讀寫功能很底層
POI的讀寫功能全面而細致，但細致也意味著過于底層，開發(fā)者必須從頭寫起，自己處理每一處細節(jié)，即使簡單的操作也要編寫大量代碼。比如，讀入首行為列名的行式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的順序進行循環(huán)解析，造成了如此繁瑣的代碼。
這還只是將數(shù)據(jù)簡單讀出來，如果下一步想再處理數(shù)據(jù)，還要事先轉(zhuǎn)為結(jié)構(gòu)化數(shù)據(jù)對象，比如ArrayList<實體類>或HashMap，代碼就更繁瑣了。
POI查詢計算困難
解析Excel并不是目標，我們通常還要對這些文件進查詢計算，但POI作為Excel的解析類，沒有也不合適再提供相關(guān)的方法，只能用JAVA手工硬寫。比如基礎(chǔ)的分組匯總運算，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編碼實現(xiàn)計算不僅繁瑣，而且存在架構(gòu)性缺陷。代碼很難復(fù)用，數(shù)據(jù)結(jié)構(gòu)和計算代碼通常會耦合在一起，如果數(shù)據(jù)結(jié)構(gòu)發(fā)生變化，代碼就要重寫。查詢計算的要求靈活多變，而Java作為編譯型語言，每次修改代碼都要重啟應(yīng)用，維護工作量大，系統(tǒng)穩(wěn)定性差。
POI成熟穩(wěn)定，但讀寫能力過于底層，且未提供查詢計算能力，直接基于POI完成Excel文件的處理（特別是查詢計算）的開發(fā)效率很低。如果針對POI進行封裝，形成簡單易用的高級讀寫函數(shù)，并額外提供查詢計算能力，就能大幅度提高開發(fā)效率了。
esProc SPL就是其中的佼佼者。
SPL內(nèi)置高級讀寫函數(shù)
SPL是JVM下開源的計算引擎，它對POI也進行了封裝，內(nèi)置簡單易用的高級函數(shù)，可解析\生成各類格式規(guī)則或不規(guī)則的xls，并自動生成結(jié)構(gòu)化數(shù)據(jù)對象。
解析格式規(guī)則的行式Excel， SPL提供了T函數(shù)。比如解析前面的xls文件，用封裝前的POI要幾十行，封裝后只要一句：
=T(“d:\Orders.xls”)
解析行式Excel是很常見的任務(wù)，SPL用T函數(shù)封裝了POI的功能，接口簡單易用。無論xls還是xlsx，T函數(shù)都可以統(tǒng)一解析?？勺詣舆M行類型轉(zhuǎn)換，開發(fā)者無須在細節(jié)浪費時間。T函數(shù)可自動區(qū)分首行的列名和其他行的數(shù)據(jù)，并根據(jù)列名創(chuàng)建序表（SPL的結(jié)構(gòu)化數(shù)據(jù)對象）并填入數(shù)據(jù)：
讀入并解析成序表后，就可以使用SPL提供的豐富的結(jié)構(gòu)化數(shù)據(jù)處理方法了：
取第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”))
先按字段取再按記錄序號取：A1.(AMOUNT)(2)；等價于先按記錄序號取再按字段?。篈1(2).AMOUNT
解析格式較不規(guī)則的行式xls，SPL提供了xlsimport函數(shù)，內(nèi)置豐富而簡潔的讀取功能：
沒有列名，首行直接是數(shù)據(jù)：file(“D:\Orders.xlsx”).xlsimport()
跳過前2行的標題區(qū)：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”)
函數(shù)xlsimport還具有讀取倒數(shù)N行、密碼打開文件、讀大文件等功能，這里不再詳述。
解析格式很不規(guī)則的xls，SPL提供了xlscell函數(shù)，可以讀寫指定sheet里指定片區(qū)的數(shù)據(jù)，比如讀取第1個sheet里的A2格：
=file(“d:/Orders.xlsx”).xlsopen().xlscell(“C2”)
配合SPL靈活的語法，就可以解析自由格式的xls，比如將下面的文件讀為規(guī)范的二維表（序表）：
這個文件格式很不規(guī)則，直接基于POI寫Java代碼是個浩大的工程，而SPL代碼就簡短得多：
生成規(guī)則的行式xls，SPL提供了xlsexport函數(shù)，用法也很簡單。比如，上面例子的解析結(jié)果是個序表，存在SPL的A1格中，下面將A1寫入新xls的第一個sheet，首行為列名，只要一句代碼：=file(“e:/result.xlsx”).xlsexport@t(A1)
xlsexport函數(shù)的功能豐富多樣，可以將序表寫入指定sheet，或只寫入序表的部分行，或只寫入指定的列：=file(“e:/scores.xlsx”).xlsexport@t(A1,No,Name,Class,Maths)
xlsexport函數(shù)還可以方便地追加數(shù)據(jù)，比如對于已經(jīng)存在且有數(shù)據(jù)的xls，將序表A1追加到該文件末尾，外觀風(fēng)格與原文件末行保持一致：=file(“e:/scores.xlsx”).xlsexport@a(A1)
不規(guī)則片區(qū)寫入數(shù)據(jù)，可以使用前面的xlscell函數(shù)。比如，xls中藍色單元格是不規(guī)則的表頭，需要在相應(yīng)的白色單元格中填入數(shù)據(jù)，如下圖：
直接用POI要大段冗長的代碼，而SPL代碼就簡短許多：
注意，第6、9、11行有連續(xù)單元格，SPL可以簡化代碼一起填入，POI只能依次填入。
SPL提供足夠的查詢計算能力
查詢計算是Excel處理任務(wù)的重點，SPL提供了豐富的計算函數(shù)、字符串函數(shù)、日期函數(shù)，以及標準SQL語法，不僅支持日常的xls計算，也能計算內(nèi)容不規(guī)則的xls和邏輯復(fù)雜的xls。
SPL提供了豐富的計算函數(shù)，可直接完成基礎(chǔ)計算。比如前面的分組匯總，只要一句：
A1.groups(SellerId;sum(Amount))
更多計算：
條件查詢：A1.select(Amount>1000 && Amount<=3000 && like(Client,“S”))
排序：A1.sort(Client,-Amount)"
去重：A1.id(Client)"
關(guān)聯(lián)兩個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)
組內(nèi)TopN （開窗函數(shù)）：T(“D:/Orders.xls”).groups(Client;top(3,Amount))
SPL支持大量日期函數(shù)和字符串函數(shù)，代碼量更短，開發(fā)效率更高。比如：
時間類函數(shù)，日期增減：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
字符串類函數(shù)，判斷是否全為數(shù)字：isdigit(“12345”) //返回true
取子串前面的字符串：substr@l(“abCDcdef”,“cd”) //返回abCD
按豎線拆成字符串數(shù)組：“aa|bb|cc”.split("|") //返回[“aa”,“bb”,“cc”]
SPL還支持年份增減、求年中第幾天、求季度、按正則表達式拆分字符串、拆出SQL的where或select部分、拆出單詞、按標記拆HTML等功能。
SPL提供了標準SQL語法，可以像對數(shù)據(jù)庫表一樣直接對xls文件進行查詢，極大地降低了數(shù)據(jù)庫程序員的學(xué)習(xí)門檻：
filter：s e l e c t ? f r o m d : / s O r d e r . x l s x w h e r e C l i e n t l i k e ′ select * from d:/sOrder.xlsx where Client like '%S%' or (Amount>1000 and Amount<=2000)sort：select?fromd:/sOrder.xlsxwhereClientlike
′
select * from sales.xls order by Client,Amont descdistinct：$ select distinct(sellerid) from sales.xls group by…h(huán)aving：KaTeX parse error: Expected 'EOF', got '&' at position 98: …ing sum(amount)&?gt;=2000000join…select e.name, s.orderdate, s.amount from sales.xls s left join employee.xlsx e on s.sellerid= e.eid
內(nèi)容不規(guī)則的xls，一般的類庫都無能為力，SPL語法靈活函數(shù)豐富，可輕松解決處理。比如Excel單元格里有很多”key=value”形式的字符串，需要整理成規(guī)范的二維表，以進行后續(xù)計算：
邏輯復(fù)雜的計算，SQL和存儲過程都難以實現(xiàn)，SPL的計算能力更強，可輕松解決此類問題。比如，計算某支股票最長的連續(xù)上漲天數(shù)：
SPL支持更優(yōu)的應(yīng)用架構(gòu)
SPL是解釋型語言，提供JDBC接口，可以用SQL或存儲過程的形式被JAVA集成，不僅降低了架構(gòu)的耦合性，還能支持熱切換。SPL還支持多種數(shù)據(jù)源，并支持跨數(shù)據(jù)源計算。
SPL提供了JDBC接口，可被JAVA輕松調(diào)用。簡單的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支持計算外置，可降低計算代碼和前端應(yīng)用的耦合性。復(fù)雜的SPL代碼可以先存為腳本文件，再以存儲過程的形式被JAVA調(diào)用：
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是解釋型語言，通過外置代碼可實現(xiàn)熱切換。解釋型語言無須編譯，修改后可立即執(zhí)行，無須重啟JAVA應(yīng)用，可降低維護工作量，提高系統(tǒng)穩(wěn)定性。
SPL支持多種文件數(shù)據(jù)源，除了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支持跨數(shù)據(jù)源計算，比如xls和txt的關(guān)聯(lián)計算：
=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還能訪問各類關(guān)系型數(shù)據(jù)庫，WebService、Restful等網(wǎng)絡(luò)服務(wù)， Hadoop、redis、Kafka、Cassandra等NoSQL。
POI只適合簡單的xls解析\生成任務(wù)，且未提供查詢計算能力。SPL對POI進行了封裝，內(nèi)置高級讀寫函數(shù)，不僅可以大幅簡化代碼，還能進行較不規(guī)則甚至很不規(guī)則的xls解析\生成任務(wù)。SPL額外提供了強大的計算能力，不僅支持日常的Excel查詢計算，還可計算內(nèi)容不規(guī)則的xls和邏輯復(fù)雜的xls。SPL支持更優(yōu)的應(yīng)用架構(gòu)，可實現(xiàn)代碼低耦合和熱切換，支持多種數(shù)據(jù)源和跨數(shù)據(jù)源計算。
