使用開源計算引擎提升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 valuehttps://www.cnblogs.com/zimug/archive/2022/10/13/= "";
                        switch (cellType) {
                            case HSSFCell.CELL_TYPE_STRING: // string
                                value = https://www.cnblogs.com/zimug/archive/2022/10/13/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 = https://www.cnblogs.com/zimug/archive/2022/10/13/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=https://www.cnblogs.com/zimug/archive/2022/10/13/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函式都可以統一決議，可自動進行型別轉換，開發者無須在細節浪費時間，T函式可自動區分首行的列名和其他行的資料，并根據列名創建序表（SPL的結構化資料物件）并填入資料：

3png

讀入并決議成序表后，就可以使用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"))

先按欄位取再按記錄序號取：A1.(AMOUNT)(2)；等價于先按記錄序號取再按欄位取：A1(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，比如將下面的檔案讀為規范的二維表（序表）：

4png

這個檔案格式很不規則，直接基于POI寫Java代碼是個浩大的工程，而SPL代碼就簡短得多：

file

生成規則的行式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中藍色單元格是不規則的表頭，需要在相應的白色單元格中填入資料，如下圖：

5png

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

file

注意，第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”形式的字串，需要整理成規范的二維表，以進行后續計算：

file

邏輯復雜的計算，SQL和存盤程序都難以實作，SPL的計算能力更強，可輕松解決此類問題，比如，計算某支股票最長的連續上漲天數：

file

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官網
SPL下載
SPL源代碼

歡迎對SPL有興趣的加小助手（VX號：SPL-helper），進SPL技術交流群
歡迎關注我的公告號：字母哥雜談，回復003贈送作者專欄《docker修煉之道》的PDF版本，30余篇精品docker文章，字母哥博客：zimug.com

轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/514148.html

標籤：其他

上一篇：【Python】關于Django如何處理前后端分離時的POST請求

下一篇：Tabby，一款老外都在用的 SSH工具，竟然還支持網頁操作