目錄

• POI具有全面而細(xì)致的xls讀寫能力SPL提供足夠的查詢計算能力SPL支持更優(yōu)的應(yīng)用架構(gòu)

對Excel進(jìn)行解析生成查詢計算等處理是Java下較常見的任務(wù)，但Excel的文件格式很復(fù)雜，自行編碼讀寫太困難，有了POIEasyExcelJExcel等類庫就方便多了，其中POI最為出色。

POI具有全面而細(xì)致的xls讀寫能力

POI可讀寫多種Excel文件格式，既支持古老的二進(jìn)制格式（xls），也支持現(xiàn)代的OOXML格式（xlsx），既支持全內(nèi)存一次性讀寫，也支持小內(nèi)存流式讀寫。POI為大量Excel元素設(shè)計了相應(yīng)的JAVA類，包括workbook、printer、sheet、row、cell，其中，與cell相關(guān)的類包括單元格樣式、字體、顏色、日期、對齊、邊框等。僅單元格樣式類，方法就超過了四十個，可進(jìn)行最全面最細(xì)致的讀寫操作。

POI的讀寫功能很底層

POI的讀寫功能全面而細(xì)致，但細(xì)致也意味著過于底層，開發(fā)者必須從頭寫起，自己處理每一處細(xì)節(jié)，即使簡單的操作也要編寫大量代碼。比如，讀入首行為列名的行式xls：

FileInputStream fileInputStream = new FileInputStream(“d:Orders.xls”);// get the excel bookWorkbook 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的順序進(jìn)行循環(huán)解析，造成了如此繁瑣的代碼。

這還只是將數(shù)據(jù)簡單讀出來，如果下一步想再處理數(shù)據(jù)，還要事先轉(zhuǎn)為結(jié)構(gòu)化數(shù)據(jù)對象，比如ArrayList或HashMap，代碼就更繁瑣了。

POI查詢計算困難

解析Excel并不是目標(biāo)，我們通常還要對這些文件進(jìn)查詢計算，但POI作為Excel的解析類，沒有也不合適再提供相關(guān)的方法，只能用JAVA手工硬寫。比如基礎(chǔ)的分組匯總運(yùn)算，JAVA代碼大概這樣：

Comparator comparator = new Comparator() {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 result=new ArrayList();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編碼實(shí)現(xiàn)計算不僅繁瑣，而且存在架構(gòu)性缺陷。代碼很難復(fù)用，數(shù)據(jù)結(jié)構(gòu)和計算代碼通常會耦合在一起，如果數(shù)據(jù)結(jié)構(gòu)發(fā)生變化，代碼就要重寫。查詢計算的要求靈活多變，而Java作為編譯型語言，每次修改代碼都要重啟應(yīng)用，維護(hù)工作量大，系統(tǒng)穩(wěn)定性差。

POI成熟穩(wěn)定，但讀寫能力過于底層，且未提供查詢計算能力，直接基于POI完成Excel文件的處理（特別是查詢計算）的開發(fā)效率很低。如果針對POI進(jìn)行封裝，形成簡單易用的高級讀寫函數(shù)，并額外提供查詢計算能力，就能大幅度提高開發(fā)效率了。

esProc SPL就是其中的佼佼者。

SPL內(nèi)置高級讀寫函數(shù)

SPL是JVM下開源的計算引擎，它對POI也進(jìn)行了封裝，內(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(jìn)行類型轉(zhuǎn)換，開發(fā)者無須在細(xì)節(jié)浪費(fèi)時間。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”))

先按字段取再按記錄序號?。篈1.(AMOUNT)(2)；等價于先按記錄序號取再按字段?。篈1(2).AMOUNT

解析格式較不規(guī)則的行式xls，SPL提供了xlsimport函數(shù)，內(nèi)置豐富而簡潔的讀取功能：

沒有列名，首行直接是數(shù)據(jù)：file(“D:Orders.xlsx”).xlsimport()

跳過前2行的標(biāo)題區(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中藍(lán)色單元格是不規(guī)則的表頭，需要在相應(yīng)的白色單元格中填入數(shù)據(jù)，如下圖：

直接用POI要大段冗長的代碼，而SPL代碼就簡短許多：

注意，第6、9、11行有連續(xù)單元格，SPL可以簡化代碼一起填入，POI只能依次填入。

SPL提供足夠的查詢計算能力

查詢計算是Excel處理任務(wù)的重點(diǎn)，SPL提供了豐富的計算函數(shù)、字符串函數(shù)、日期函數(shù)，以及標(biāo)準(zhǔn)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

按豎線拆成字符串?dāng)?shù)組：“aa|bb|cc”.split(“|”) //返回[“aa”,“bb”,“cc”]

SPL還支持年份增減、求年中第幾天、求季度、按正則表達(dá)式拆分字符串、拆出SQL的where或select部分、拆出單詞、按標(biāo)記拆HTML等功能。

SPL提供了標(biāo)準(zhǔn)SQL語法，可以像對數(shù)據(jù)庫表一樣直接對xls文件進(jìn)行查詢，極大地降低了數(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： 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 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

SPL支持SQL-92標(biāo)準(zhǔn)中大部分語法，包括集合計算、case when、with、嵌套子查詢等，詳見 《沒有 RDB 也敢攬 SQL 活的開源金剛鉆 SPL》

內(nèi)容不規(guī)則的xls，一般的類庫都無能為力，SPL語法靈活函數(shù)豐富，可輕松解決處理。比如Excel單元格里有很多”key=value”形式的字符串，需要整理成規(guī)范的二維表，以進(jìn)行后續(xù)計算：

邏輯復(fù)雜的計算，SQL和存儲過程都難以實(shí)現(xiàn)，SPL的計算能力更強(qiáng)，可輕松解決此類問題。比如，計算某支股票最長的連續(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是解釋型語言，通過外置代碼可實(shí)現(xiàn)熱切換。解釋型語言無須編譯，修改后可立即執(zhí)行，無須重啟JAVA應(yīng)用，可降低維護(hù)工作量，提高系統(tǒng)穩(wěn)定性。

SPL支持多種文件數(shù)據(jù)源，除了xls外，SPL還能讀寫csvxtXMLJson等文件，比如對txt進(jìn)行條件查詢：

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進(jìn)行了封裝，內(nèi)置高級讀寫函數(shù)，不僅可以大幅簡化代碼，還能進(jìn)行較不規(guī)則甚至很不規(guī)則的xls解析生成任務(wù)。SPL額外提供了強(qiáng)大的計算能力，不僅支持日常的Excel查詢計算，還可計算內(nèi)容不規(guī)則的xls和邏輯復(fù)雜的xls。SPL支持更優(yōu)的應(yīng)用架構(gòu)，可實(shí)現(xiàn)代碼低耦合和熱切換，支持多種數(shù)據(jù)源和跨數(shù)據(jù)源計算。