Java可以对xls使用SQL吗？

间接的方式是用JAVA内嵌数据库，即先入库再执行SQL，这种方式可以充分利用SQL的计算能力，缺点是时效性非常差，而且入库过程繁琐，要检查表名重复、删表、建表、解析文件、插入数据、建索引等等，还经常遇到格式不够规范无法入库的情况。直接的方式是在JAVA中调用基于xls的JDBC驱动，常见的有XLSJDBC/xlSQL/CDATA Excel JDBC，这种方式的好处是整体效率高，架构也简单，缺点是计算能力太差，连集合计算、子查询、关联查询都不支持。

计算能力强，又可以直接在xls上执行SQL的JAVA开源库，集算器SPL是个更好的选择。

SPL的基本用法简单，比如sOrders.xlsx是tab分隔的订单表，对该表进行条件查询：

…
Class.forName("com.esproc.jdbc.InternalDriver");
Connection connection =DriverManager.getConnection("jdbc:esproc:local://");
Statement statement = connection.createStatement();

String str="$select * from d:/sOrder.xlsx   where Client like'%S%'or (Amount>1000 and Amount<=2000)";
ResultSet result = statement.executeQuery(str);
…

SPL 支持常见的SQL语法，可实现各类日常计算，下面再举一些例子：

#sort
$select   * from sales.xls order by Client,Amont desc
#distinct
$   select distinct(sellerid) from sales.xls
#group   by…having
$select   year(orderdate) y,sum(amount) s from sales.xls group by year(orderdate)   having sum(amount)>=2000000
#join
$select   e.name, s.orderdate, s.amount from sales.xls    s left join employee.csv e on s.sellerid= e.eid

SPL支持高级SQL语法，可实现难度较大的计算，比如集合计算、case  when、with、嵌套子查询等，详见《在文件上使用 SQL 查询的示例》

 

对于格式不规范的xls，一般的SQL工具很难解析，而SPL提供了扩展函数，可轻松解析此类文件，下面试举几例：

#没有列名，首行直接是数据：
$select   * from {file("D:/Orders.xlsx").xlsimport()}
#跳过前2行的标题区：
$select   * from {file("D:/Orders.xlsx").xlsimport@t(;,3)}
#读取名为"sheet3"的特定sheet：
$select   * from {file("D:/Orders.xlsx").xlsimport@t(;"sheet3")}

利用扩展函数，SPL还可解析更多格式复杂的文件，比如从第M行读到第N行、密码打开xls等，这里不再详述。

有时计算逻辑较为复杂，即使存储过程也难以实现，而SPL的扩展函数有足够的计算能力，可轻松解决此类问题。比如：商品在出库\入库时，日期上通常不连续，要据此计算出多种商品在指定的连续日期内的库存情况。首先，编写代码并存为SPL脚本文件。脚本文件的作用相当于存储过程，可降低代码耦合性，而且修改时无须编译：

A
1 =T("d:/inout.xls").group(product;~.align(A2,date):g)
2 =periods(argBeginDate,argEndDate)
3 =A2.news(g;A1.product,A2(#):date,ifn(in,0):in,   ifn(out,0):out, stock[-1]+in-out:stock)
4 $select * from {A3}

再在JAVA代码中以存储过程的方式调用脚本文件：

…
Class.forName("com.esproc.jdbc.InternalDriver");
Connection conn   =DriverManager.getConnection("jdbc:esproc:local://");
CallableStatement statement = conn.prepareCall("{call getStock(?,   ?)}");
statement.setObject(1,   "2020-01-01");
statement.setObject(2,   "2020-01-31");
statement.execute();
...

对于计算逻辑较复杂的脚本，SPL提供了专业的IDE，不仅有完整的调试功能，还能用表格的形式观察每一步的中间计算结果：

IDEpng

SPL不仅计算能力强，还支持多种数据源和跨源计算，包括csv、XML、Json、各类NoSQL和数据库，也支持超出内存的大xls的计算，感兴趣可到官网了解。