对 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 file InputStream = new File InputStream("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.getPhysical number OfCells(); // 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 Runtime Exception ("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, sum Value));
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=”#34; 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 script FileName(?, ?)}");
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 支持更优的应用架构，可实现代码低耦合和热切换，支持多种数据源和跨数据源计算。
