除了 POI,Java 用什么开源包适合处理 Excel?
Java 程序员有时会有编程方式执行读取或写入 Excel 的任务,通常是编写一些自定义程序来处理,开源Apache POI 包是完成此类任务非常有用的工具。POI 内部提供了 HSSF 和 XSSF 两种模式来读取、创建、修改、读取和写入 XLS 表格。如果用户需求涉及 Excel 对象细节读取和修改,比如读取公式批量修改,通过 HSSF 可以对 Excel 完成细节操控;如果用户需求更关心数据的读取效率,则用 XSSF 模式更高效。POI 虽然强大,但是学习曲线不算很平,如果遇到文件格式复杂,比如有合并格、复杂的多行表头表尾、数据记录分散于多行、交叉表等,读取数据的程序代码就会变得更长更复杂。
即便有了 POI 这样强大的开源包,使用 Java 来解析 Excel 仍然是非常麻烦的。通常 Excel 解析后是导入数据库,应用程序再利用数据库完成后续计算。对于需要反复利用 Excel 数据计算的场景,导入数据库是划算的,但往往导入数据库只是为了利用 SQL 来计算数据,这种情况就不妨使用既能解析 Excel 数据,又能做结构化计算的开源库。Open-esProc 就是这种类型的开源包,但 Open-esProc 和一般 Java 包有所不同,是将数据类型和计算方法封装到一个叫 SPL 的脚本语言中,然后在 Java 程序中调用 SPL 脚本,返回 ResultSet 对象。
举个简单的例子加以说明,先用 SPL 读取 Excel 文件,然后做条件查询:
A |
|
1 |
=file("simple.xls").xlsimport@t() |
2 |
=A1.select(amount>500 && amount<=2000) |
选项 @t 表示第一行是列标题,之后用select函数完成条件查询。
这段代码可在 esProc IDE 中调试执行,然后将其存为脚本文件(比如 condition.dfx),通过JDBC接口在JAVA中调用,具体代码如下:
package Test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class test1 {
public static void main(String[] args)throws Exception {
Class.forName("com.esproc.jdbc.InternalDriver");
Connection connection =DriverManager.getConnection("jdbc:esproc:local://");
Statement statement = connection.createStatement();
ResultSet result = statement.executeQuery("call condition()");
printResult(result);
if(connection != null) connection.close();
}
…
}
上面的用法类似存储过程,其实SPL也支持类似SQL的用法,即无须脚本文件,直接将SPL script 嵌入JAVA,代码如下:
…
ResultSet result = statement.executeQuery("
=file(\"D:\\data\\simple.xls\").xlsimport@t().select(amount>500 && amount<=3000)");
…
其实 Open-esProc 也是封装了 Apache POI,但提供了更易读取 Excel 文件的方法,通过 SPL 专业的数据处理语法,不仅可以非常简洁的读写 Excel,还可以完美地支持各种后续计算工作。另外,用 SPL 计算过程都是独立的,当遇到需求变动后,改动也非常方便。
再举两个例子,有时候 Excel 文件的表头格式并不简单,往往是由多行构成的,解析这种格式的文件时,需要跳过复杂的表头,指定从数据行的位置开始读取,然后指定结构化后的数据各列的列名。比如:在项目造价文件 itemPrices.xlsx 中,计算项目总造价,部分数据如下图:
用 SPL 可以轻松处理如下:
A |
注释 |
|
1 |
=file(“e:/excel/itemPrices.xlsx").xlsimport(;1,5) |
参数“1,5”表示读第一个sheet,从第5行开始读,一直读到sheet结尾 |
2 |
=A1.rename(#1:No,#2:ItemCode,#3:ItemName,#4:Unit,#5:Quantity,#6:Price,#7:Sum) |
修改各列的列名 |
3 |
=A2.sum(Sum) |
计算项目总造价 |
再比如,需要两个数据表中的数据进行关联计算, 销售订单信息和产品信息分别存储在两个 Excel 文件中,计算各订单的销售额并导出结果。两个文件数据结构如下图:
用 SPL 能直接计算并导出:
A |
|
1 |
=T(“e:/orders/sales.xlsx”) |
2 |
=T(“e:/orders/product.xlsx”).keys(ID) |
3 |
=A1.join(ProductID,A2,Name,Price) |
4 |
=A3.derive(Quantity*Price:amount) |
5 |
=file("D:/orders/amount.xlsx").xlsexport(A4) |
想了解更多用 SPL 处理 Excel 示例, 可以参考 SPL 常规数据表运算 , Excel 文件结构化解析示例 , 生成各种格式的 Excel 文件示例