Excel 上的 SQL 式运算

 

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

 

1.  读取

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

1、简单行式文件

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

..

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

file函数的参数可以是文件的绝对路径,也可以是相对路径(相对于SPL配置文件中的主目录mainPath),xlsimport函数默认读取文件中第1Sheet所有行列数据生成序表,选项@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)

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

 

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

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

 

3、读取指定Sheet

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

表示读取名称为School2Sheet中所有列数据。

 

4、读取Sheet信息

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

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

打开后的Excel对象如下图:

..

每行表示一个Sheetstnamenrowsncols分别表示Sheet的名称、数据行数、数据列数。

 

 

更多读取介绍请参阅《SPLExcel文件读写

 

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读取介绍请参阅《SPLExcel文件读写

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

 

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

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

 


以下是广告时间

对润乾产品感兴趣的小伙伴,一定要知道软件还能这样卖哟性价比还不过瘾? 欢迎加入好多乾计划。
这里可以低价购买软件产品,让已经亲民的价格更加便宜!
这里可以销售产品获取佣金,赚满钱包成为土豪不再是梦!
这里还可以推荐分享抢红包,每次都是好几块钱的巨款哟!
来吧,现在就加入,拿起手机扫码,开始乾包之旅



嗯,还不太了解好多乾?
猛戳这里
玩转好多乾