共计 8143 个字符，预计需要花费 21 分钟才能阅读完成。
对 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”)[email protected](;,3)
从第 3 行读到第 10 行：file(“D:/Orders.xlsx”)[email protected](;,3:10)
只读取其中 3 个列：file(“D:/Orders.xlsx”)[email protected](OrderID,Amount,OrderDate)
读取名为 ”sales” 的特定 sheet：file(“D:/Orders.xlsx”)[email protected](;”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”)[email protected](A1)
xlsexport 函数的功能丰富多样，能够将序表写入指定 sheet，或只写入序表的局部行，或只写入指定的列：=file(“e:/scores.xlsx”)[email protected](A1,No,Name,Class,Maths)
xlsexport 函数还能够不便地追加数据，比方对于曾经存在且有数据的 xls，将序表 A1 追加到该文件开端，外观格调与原文件末行保持一致：=file(“e:/scores.xlsx”)[email protected](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
星期几：[email protected](“2020-02-27”) // 返回 5，即星期 4
N 个工作日之后的日期：workday(date(“2022-01-01”),25) // 返回 2022-02-04
字符串类函数，判断是否全为数字：isdigit(“12345”) // 返回 true
取子串后面的字符串：[email protected](“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”模式的字符串，须要整顿成标准的二维表，以进行后续计算：
逻辑简单的计算，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 反对更优的利用架构，可实现代码低耦合和热切换，反对多种数据源和跨数据源计算。
## SPL 材料
- SPL 官网
- SPL 下载
- SPL 源代码
欢送对 SPL 有趣味的加小助手（VX 号：SPL-helper），进 SPL 技术交换群
欢送关注我的布告号：字母哥杂谈，回复 003 赠送作者专栏《docker 修炼之道》的 PDF 版本，30 余篇精品 docker 文章。字母哥博客：zimug.com
