使用开源计算引擎提升 Excel 格式文件处理效率

浅浅的花香味﹌ 2024-04-03 12:24 29阅读 0赞

对 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 函数都可以统一解析。可自动进行类型转换，开发者无须在细节浪费时间。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"))

先按字段取再按记录序号取：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，比如将下面的文件读为规范的二维表（序表）：

这个文件格式很不规则，直接基于 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"):O,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、嵌套子查询等，详见 <a href="http://c.raqsoft.com.cn/article/1638869916433" rel="nofollow">《没有 RDB 也敢揽 SQL 活的开源金刚钻 SPL》</a>

**内容不规则的 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"):O,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 支持更优的应用架构，可实现代码低耦合和热切换，支持多种数据源和跨数据源计算。