除了 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 文件示例