解决办法：esProc - Java 专业计算包

esProc 是专门用于基于 Java 计算的类库，旨在简化 Java 代码。 SPL 是集算器所基于的脚本语言，用法类似于在 Java 程序中调用存储过程，只不过 SPL 独立存储于库外，和 Java 程序一起部署，通过 JDBC 接口传递给 Java 程序执行，实现结构化计算。SPL 非常轻巧，语法简单，类似执行 SQL 的方式计算 Excel 数据，返回 ResultSet 对象。

先看一个很简单的文件：第一行是列标题，第二行开始直到最后一行都是数据行。文件内容如下图：

用Apache为Java提供的开源包poi读写Excel文件，写出来的程序是这样：

DataSet ds = null;   //此类用来保存从Excel中读取的数据，需要自己编写
HSSFWorkbook wb = new HSSFWorkbook( new FileInputStream( "simple.xls" ) );
HSSFSheet sheet = wb.getSheetAt( 0 );  //假定要读取的数据在第一个sheet中
int rows = sheet.getLastRowNum();
int cols = sheet.getRow(0).getLastCellNum();
ds = new DataSet( rows, cols );
for( int row = 0; row <= rows; row++ ) {
	HSSFRow r = sheet.getRow( row );
	for( int col = 0; col <= cols; col++ ) {
		HSSFCell cell = r.getCell( col );
		int type = cell.getCellType();
		Object cellValue;   //单元格数据值对象
		switch( type ) {   //根据单元格数据类型，将格值处理成对应的Java对象
			case HSSFCell.CELL_TYPE_STRING:
				......
			case HSSFCell.CELL_TYPE_NUMERIC:
				......
			......
			//格值处理代码比较长，此处省略
		}
		if( row == 0 ) ds.setColTitle( col, (String)cellValue );
		else ds.setCellValue( row, col, cellValue );
		//如果是第一行，则将格值设成列标题，否则设成数据集单元格数据
	}
}

这段代码只能读取最简单格式的Excel文件，中间还省略了很多格值处理的代码，但代码已经不短了。如果文件格式更复杂，比如有合并格、复杂的多行表头表尾、数据记录分散于多行、交叉表等，读取数据的程序代码就会变得更长更复杂。可以看出，即使有了poi这样强大的开源包，使用Java来解析Excel仍然是非常麻烦的。

而且，Java 只提供比较基础的底层函数，缺乏专业的结构化数据计算函数，比如数据集的过滤、排序、分组统计、连接等，都需要程序员自己去编写，因此即使数据读出来了，但要进行后续的计算，仍然有大量的工作要做。

如果用 SPL 读取该文件则非常简单，只要写一行代码就可以：

	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)");
…

---

其实 esProc 计算库也是封装了 Apache POI，但提供了更易读取 Excel 文件的方法，通过 SPL 专业的数据处理语法，不仅可以非常简洁的读写 Excel，还可以完美地支持各种后续计算工作。

读写 Excel 表格

结构化的Excel表格比较规范， SPL可用xlsimport/xlsexport函数进行读写。

比如：ordersNT.xlsx中第1个sheet的各列的业务意义依次是订单ID、客户编号、销售ID、订单金额、订单日期。部分数据如下：

	A	B	C	D	E
1	26	TAS	1	2142.4	2009/8/5
2	33	DSGC	1	613.2	2009/8/14
3	84	GC	1	88.5	2009/10/16
4	133	HU	1	1419.8	2010/12/12
5	32	JFS	3	468	2009/8/13
6	39	NR	3	3016	2010/8/21
7	43	KT	3	2169	2009/8/27

将该表按客户编号的字母顺序从小到大的排序，相同的客户编号再按订单金额从大到小排序，最后保持原格式存入新Excel。

要计算出上述结果，可用如下SPL脚本：

	A
1	=file("D:/data/ordersNT.xlsx").xlsimport()
2	=A1.sort(_2,-_4)
3	=file("D:/data/ordersNT_sort.xlsx").xlsexport(A2)

A1、A3：读写excel的第1个sheet。如果要读入指定sheet，可用xlsimport(;sheet序号或sheet名)，如果要写入指定的sheet，可用xlsexport(A2; sheet序号或sheet名)

 

SPL也可以处理带列名（标题）的结构化表格，方法与文本文件类似。比如orders.xlsx部分数据如下：

	A	B	C	D	E
1	OrderID	Client	SellerId	Amount	OrderDate
2	26	TAS	1	2142.4	2009/8/5
3	33	DSGC	1	613.2	2009/8/14
4	84	GC	1	88.5	2009/10/16
5	133	HU	1	1419.8	2010/12/12
6	32	JFS	3	468	2009/8/13
7	39	NR	3	3016	2010/8/21
8	43	KT	3	2169	2009/8/27

对该文件进行排序，结果带列名写入新文件，可用如下SPL脚本：

	A
1	=file("D:/data/orders.xlsx").xlsimport@t()
2	=A1.sort(Client,-Amount)
3	=file("D:/data/orders_sort.xlsx").xlsexport@t(A2)

 

有时候我们需要在原表格后面追加结构相同的新数据，这种情况下只需使用@a选项，形如：

=file(""D:/data/orders_sort.xlsx").xlsexport@at(A2)

如果原表格的最后一个有内容的行设置了外观属性，则追加的数据会继承该行的风格。比如原表格D列风格为#,##0.00，E列风格为mmm-dd-yyyy。如下表：

	A	B	C	D	E
1	OrderID	Client	SellerId	Amount	OrderDate
2	26	TAS	1	2,142.40	Aug-05-2009
3	33	DSGC	1	613.20	Aug-14-2009
4	84	GC	1	88.50	Oct-16-2009

则追加数据后，结果如下表：

	A	B	C	D	E
1	OrderID	Client	SellerId	Amount	OrderDate
2	26	TAS	1	2,142.40	Aug-05-2009
3	33	DSGC	1	613.20	Aug-14-2009
4	84	GC	1	88.50	Oct-16-2009
5	133	HU	1	1,419.80	Dec-12-2010
6	32	JFS	3	468.00	Aug-13-2009

如果原表格最后一个有内容的行之后的第一个空行设置了风格属性，则追加的数据会继承该行的风格属性。利用这个特点，我们可以实现按指定格式从无到有的输出数据。比如先建立空白Excel，设置第2行的D列风格为#,##0.00，E列风格为mmm-dd-yyyy。

	A	B	C	D	E
1	OrderID	Client	SellerId	Amount	OrderDate
2

再向该空白表格追加数据，结果如下：

	A	B	C	D	E
1	OrderID	Client	SellerId	Amount	OrderDate
2	133	HU	1	1,419.80	Dec-12-2010
3	32	JFS	3	468.00	Aug-13-2009

读写 Excel 单元格

前面的内容都是以表格或序列为单位读写Excel，有时候我们也会以精细的单元格为单位读写Excel。

比如：下面的表格在第1行有编辑者和编辑日期。

	A	B	C	D	E
1	editor:emily				date:Dec-30-2011
2	OrderID	Client	SellerId	Amount	OrderDate
3	26	TAS	1	2,142.40	Aug-05-2009
4	33	DSGC	1	613.20	Aug-14-2009
5	84	GC	1	88.50	Oct-16-2009
6
7

现在要将编辑者和编辑日期复制到第7行对应的位置上，结果应当如下：

	A	B	C	D	E
1	editor:emily				date:Dec-30-2011
2	OrderID	Client	SellerId	Amount	OrderDate
3	26	TAS	1	2,142.40	Aug-05-2009
4	33	DSGC	1	613.20	Aug-14-2009
5	84	GC	1	88.50	Oct-16-2009
6
7	editor:emily				date:Dec-30-2011

要计算出上述结果，可用如下SPL脚本：

	A	B
1	=file("D:/data/cell.xlsx")
2	=A1.xlsopen()
3	=str=A2.xlscell("A1")	=A2.xlscell("A7";str)
4	=str=A2.xlscell("E1")	=A2.xlscell("E7";str)
5	=A1.xlswrite(A2)

A2：以对象方式打开Excel文件。

A3：读Excel对象的A1格，赋予变量str。默认从第1个sheet读，如果想读取指定sheet里的A1单元格，可用A2.xlscell("A1",sheet序号或sheet名)

B3：将A1格内容写入A7格。类似地，如果想在指定sheet里写入A7单元格，可用A2.xlscell("A7",sheet序号或sheet名;str)

A4-B4：读E1格内容，并写入E7格

A5：将Excel对象写入Excel文件。

 

上面例子中，需要读入的A1-E1是连续单元格，需要写入的A7-E7也是连续单元格，对于这种连续单元格的读写，SPL可以用更简化的代码来实现。比如上例的代码可以改成：

	A
1	=file("cell.xlsx")
2	=A1.xlsopen()
3	=arry=A2.xlscell@w("A1":"E1")
4	=A2.xlscell("A7":"E7";arry)
5	=A1.xlswrite(A2)

A3：以序列的格式读入连续单元格

A4：向连续单元格写入序列，序列的每个成员对应一个单元格。既可以用序列向连续单元格写入数据，也可以用\t或\r分隔的字符串，其中\t表示横向分隔，\r表示纵向（跨行）分隔。

多sheet处理

使用Excel对象，不仅可用读写单元格，也可以处理多个sheet，下面举例说明。

某Excel用多个sheet存储订单表格，每个sheet的格式相同，但数量和名称不定。现在要将这些订单整理到新Excel中，每个sheet存一年的数据。

要计算出上述结果，可用如下SPL脚本：

	A
1	=file("orders_sheet.xlsx").xlsopen()
2	=A1.(stname).(A1.xlsimport@t(;~)).conj()
3	=A2.group(string(year(OrderDate)):name;~:content)
4	=file("orders_result.xlsx").xlsopen@w()
5	=A3.(A4.xlsexport@t(~.content;string(~.name)))
6	=A4.xlsclose()

A1：以对象方式打开源Excel文件。

A2：遍历每个sheet，读取每个sheet的订单，再合并所有订单。A1.(stname)表示从Excel对象A1中取出所有的sheet名。

A3：将订单按年份分组。

A4：以对象方式打开目标Excel文件。@w表示写模式，文件不存在时将新建文件。

A5：遍历A3的每组（每年）订单，依次写入到A4的新sheet中。

A6：@w模式打开的Excel对象必须用函数xlsclose关闭。

多文件关联计算

对两个数据表中的数据进行关联计算

示例：销售订单信息和产品信息分别存储在两个Excel文件中，计算各订单的销售额。两个文件数据结构如下图：

	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)

A1   读取销售订单数据

A2   读取产品信息数据，设置ID为主键

A3   将A1按照ProductID与A2中的主键进行关联，同时引入Name、Price列数据

A4   A3中新增一列amount，其值为销售数量Quantity与产品价格Price的积

 

对两个数据表中的数据进行关联查询。

示例：仍用上节中的2个文件，查询产品价格大于20元的销售订单。

	A
1	=T(“e:/orders/sales.xlsx”)
2	=T(“e:/orders/product.xlsx”).select(Price>20).keys(ID)
3	=A1.switch@i(ProductID,A2)

A1   读取销售订单数据

A2   读取产品信息数据，选出价格大于20的产品信息，然后设置ID为主键

A3   将A1按照ProductID与A2中的主键进行关联，选项@i表示在A2中找不到与ProductID匹配的产品ID时，则过滤掉此条记录

 

对主表与明细表数据进行关联查询

示例：有员工信息表employee.xlsx与员工家庭成员表family.xlsx部分数据如下，请查询家中有70岁以上老人的员工信息。

	A
1	=T("e:/work/employee.xlsx")
2	=T("e:/work/family.xlsx").select(age(Birthday)>=70)
3	=join(A1:employee,Eid;A2:family,Eid)
4	=A3.conj(employee)

A1   读取员工信息数据

A2   读取员工家庭成员数据，选出年龄70以上的成员

A3   将A1与A2按照Eid进行关联过滤，删除不匹配的记录，将A1命名为employee，A2命名为family

A4   取出A3中的employee列，连接为序表

最后说下esProc的配置。在 esProc IDE 读写计算Excel是esProc的基本功能，无需额外配置。

了解更多

常规数据表计算，参考 SPL 常规数据表运算
Excel 解析和导出专题，参考 SPL 解析及导出 Excel
SPL 与 Java 程序集成，参考 Java 如何调用 SPL 脚本