Excel是很常见的文件格式，在Java中可以使用Apache Poi开源包读取Excel单元格数据。POI 虽然强大，但也仅限于读取数据，编程读取数据的工作量比较大，而且不能在不同格式的文件上通用。即使将数据读出来了，还是难以进行SQL式的结构化运算，还经常需要导入数据库才行，这很不方便。SPL可以很方便地实现Excel数据的计算，并可以轻松集成到Java应用中。

 

1.  读取

SPL用file函数打开Excel文件，并用xlsimport函数将文件读成序表就可以进一步计算了。下面是一些读取例子：

1、简单行式文件

行式Excel文件每行对应一条记录，各行有相同的列，相当于数据库中的一张数据表。

=file("e:/scores.xlsx").xlsimport@t()

file函数的参数可以是文件的绝对路径，也可以是相对路径(相对于SPL配置文件中的主目录mainPath），xlsimport函数默认读取文件中第1个Sheet所有行列数据生成序表，选项@t表示第1行是标题行，此行的列值为对应序表列的名称；若无选项@t，表示无标题行，此时用#1、#2等序号方式来引用列。

 

2、读取部分数据

=file("e:/scores.xlsx").xlsimport@t(No,Name,Class,Maths)

指定了列名参数，表示只读取No,Name,Class,Maths列的数据。

 

=file("e:/scores.xlsx").xlsimport@t(;,1:100)

读取文件中第1个Sheet中第1行到第100行的所有列数据。

 

=file("e:/scores.xlsx").xlsimport@t(;,1:-10)

读取文件中第1个Sheet中第1行到倒数第10行的所有列数据。负数表示倒数的行序号。

 

3、读取指定Sheet

=file("e:/scores.xlsx").xlsimport@t(;"School2")

表示读取名称为School2的Sheet中所有列数据。

 

4、读取Sheet信息

Excel文件中有多个Sheet，SPL提供xlsopen函数将文件打开生成一个Excel对象，它是一个由各Sheet信息组成的序表。

=file("e:/scores.xlsx").xlsopen()

打开后的Excel对象如下图：

每行表示一个Sheet，stname、nrows、ncols分别表示Sheet的名称、数据行数、数据列数。

 

 

更多读取介绍请参阅《SPL：Excel文件读写》

 

2.   运算

读入的数据就是内存中的序表，可以做任意的运算。比如过滤、聚合、分组汇总、排序、关联、去重、计算列等，用前图的学生成绩表scores.xlsx文件数据，下面举几种运算例子。

	A	说明
1	=file("e:/scores.xlsx").xlsimport@t()	数据读成序表，@t表示第1行是标题行
2	=A1.select(Sex=="M")	筛选出男生的学生成绩
3	=A1.groups(CLASS;min(English),max(Chinese),sum(Maths))	计算各班的英语最低分、语文最高分、数学总分
4	=A1.sort(CLASS,-Maths)	按照班号升序、数学降序的顺序排列

 

更多运算介绍请参阅《SPL：常规SQL式运算》

 

3.   向Java返回结果

SPL可以用JDBC方式被Java调用，例如执行SPL脚本的代码如下：

Class.forName("com.esproc.jdbc.InternalDriver");

Connection con = DriverManager.getConnection("jdbc:esproc:local://");

Statement st = con.createStatement ();

ResultSet rs = st.executeQuery("=file(\"e:/scores.xlsx\").xlsimport@t().select(Sex==\"M\")");

 

更深入用法可参考《Java 如何调用 SPL 脚本》

 

4.   使用SQL

借助SPL，还可以直接对Excel文件使用SQL，如：

	A
1	$select * from E:/scores.xlsx where Sex="M"
2	$select min(English),max(Chinese),sum(Maths) from E:/scores.xlsx group by CLASS
3	$select * from E:/scores.xlsx order by CLASS, Maths desc

使用SQL时要求文件第1行必须是标题行。

 

在JAVA中也可以直接用JDBC执行上述SQL语句返回查询结果。

ResultSet rs = st.executeQuery("select * from E:/scores.xlsx where Sex=\"M\"");

 

更多信息可参考《在文件上使用 SQL 查询的示例》

 

5.   大文件

SPL还可以用游标方式处理内存装不下的大文件，用法和前面的小文件类似，只要给xlsimport函数加上选项c即可返回游标，并支持在游标上附加各种运算。

如第2节中的几个例子：

	A	说明
1	=file("E:/scores.xlsx").xlsimport@tc()	数据读成游标，@t表示第1行是标题行
2	=A1.select(Sex=="M")	筛选出男生的学生成绩
3	=A1.groups(CLASS;min(English),max(Chinese),sum(Maths))	按班级分组计算,结果集不大时用groups返回序表

 

更多Excel读取介绍请参阅《SPL：Excel文件读写》

更多大文件游标运算介绍请参阅《SPL：游标上的常规SQL式运算》

 

SPL计算出的游标也可以返回给JAVA，用JDBC接口调用的方法和前面是一样的。

对于Excel大文件也可以使用SQL，与小文件时没有区别。