SPL 使用润乾报表外部库生成复杂格式 PDF、XLS、DOC、HTML

SPL 不仅可以做数据准备和计算,还可以调用润乾报表的外部库来生成有格式要求的报表,比如导出 Excel、pdf、Word、Html 等格式,并且 SPL 脚本可以集成到 JAVA 程序中调用,能更加灵活满足业务需要。

SPL 外部库环境配置

润乾官网下载报表设计器,解压缩后直接安装,安装后默认是职场版,可以免费使用。

1、在 SPL【安装根目录】\esProc\ 下新建 extlib\Report5Cil 目录(也可以将 Report5Cil 放到其他目录下,在 SPL 工具中设置时能找到相应目录就行)

2、将报表设计器【安装根目录】\report\lib 下的 jboss-servlet-api_3.1-1.0.0.jar 复制到 SPL 新建的 Report5Cil 中

在报表设计器【安装根目录】\report\web\webapps\demo\WEB-INF\lib 中找到如下 jar 包:

esproc-ext-********.jar(* 代表不同版本发布日期)

htmlparser-1.6.jar

pdfbox-1.8.14.jar

mail-1.4.4.jar

raqsoftReport.jar

scu-report5-cli-2.10.jar

将上述 jar 包复制到 SPL 新建的 Report5Cil 中

3、启动 SPL,在菜单栏中选择【工具】-【选项】-【环境】,进入如下界面:

imagepng

确定后重启 SPL

4、更改配置文件,报表设计器【安装根目录】\report\config 下的 raqsoftConfig.xml,该文件中可以配置数据源连接、报表模板根目录等信息。本文例子中连接自带的 demo 数据源,SPL 工具中自带该数据源,此处暂时不修改,如要连接自己数据库,则将该文件复制到其他磁盘目录,在文件内增加数据源配置,在《JAVA 程序集成》中会做详细配置文件修改说明。。

JAVA 程序集成

在 myeclipse 中新建 JAVA 工程或者使用已有工程(其他开发工具集成方式相同)。

程序集成

1、集成 jar 包
所需 jar 包在【SPL 安装根目录】\esProc\lib 目录下获取,如是 web 应用,则将相应 jar 包放到 WEB-INF/lib 下):

esproc-bin-xxxx.jar 集算器计算引擎及 JDBC 驱动包

icu4j_60.3.jar 处理国际化

jdom-1.1.3.jar 解析配置文件

上述三个是 JAVA 调用脚本的基础 jar,继续增加其他 jar。

连接 demo 数据源的 jdbc 驱动,在,【SPL 安装根目录】\common\jdbc 下的 hsqldb-2.2.8.jar,如连接其他数据库,此处将对应驱动 jar 引入。

导出 Excel,需要引入:poi*.jar、commons-*.jar、core-3.3.0.jar、xmlbeans-4.0.0.jar

导出 pdf,需要引入:batik*.jar

2、集成配置文件
将 [报表设计器安装目录]\report\config 下的 raqsoftConfig.xml 复制到工程的类路径下(src 目录)

注:SPL 安装后也带了 raqsoftConfig.xml,本文主要是调用外部库计算报表,所以此处配置文件使用报表安装后自带的。

3、报表及脚本文件
将设计好的报表文件(rpx)以及编辑好的 splx 文件复制到工程类路径下。

更改配置文件

打开类路径下的 raqsoftConfig.xml:

1、配置数据源连接

<DBList>节点中增加 demo 数据源配置:

<DBList encryptLevel="0">
 <DB name="demo">
 <property name="url" value="jdbc:hsqldb:hsql://127.0.0.1/demo"/>
 <property name="driver" value="org.hsqldb.jdbcDriver"/>
 <property name="type" value="13"/>
 <property name="user" value="sa"/>
 <property name="password" value=""/>
 <property name="batchSize" value="1000"/>
 <property name="autoConnect" value="false"/>
 <property name="useSchema" value="false"/>
 <property name="addTilde" value="false"/>
 <property name="dbCharset" value="GBK"/>
 <property name="clientCharset" value="GBK"/>
 <property name="needTransContent" value="false"/>
 <property name="needTransSentence" value="false"_/>
 <property name="caseSentence" value="false"/>
 </DB>
</DBList>

其中 DB name 是数据源名称,要和报表以及 SPL 中用到名称保持一致,此处可增加多个数据源,按照实际配置就行。

2、设置外部库目录

在 Esproc 节点中配置外部库文件夹及名称(此处目录与 SPL 添加外部库时的设置目录相同):

 <extLibsPath>D:\\report2018\\esProc\\extlib</extLibsPath>
 <importLibs>
 <lib>Report5Cil</lib>
 </importLibs>

3、授权配置

如需要导出 HTML 格式文件,那么需要设置报表授权(其余格式此步可以忽略),在润乾官网下载试用授权或者申请开发授权,将授权文件(xml 格式)放到 JAVA 工程类路径下,更改 raqsoftConfig.xml 中节点内的 license 配置,如下:

<license>reportTrialLicense20221231.xml</license>

集成后的工程结构如下图:

imagepng

案例

文本数据集

需求

从《订单信息.xlsx》取出数据以列表方式呈现,要求数据区域单元格背景色隔行异色、并且将订单金额超过 2000 的数据行用红色字体标出,结果如下:

imagepng

先看下《订单信息.xlsx》中数据结构,里边记录了订单信息数据,如下图:

imagepng

将文件保存到 F 盘(也可以是其他目录,在报表设计或者 SPL 中能读取到就可以)

报表模板设计

数据集设置

新建数据集,数据集类型“文件数据集”,文件选择《订单信息.xlsx》

imagepng

imagepng

数据集名称这里用默认的 ds1,在报表模板设计时单元格表达式通过这个名称从数据集中取数,并且在 SPL 脚本中进行数据替换时也是根据这个数据集名称进行替换。

注:报表支持多数据集,这里可以增加多个数据集设置。下述其余案例数据集设置方式相同。

报表模板

imagepng

报表第三行是数据扩展区域,在第三行单元格的背景色表达式中写入公式:if(row()%2==0,-4417,-853778),该公式判奇偶行来返回不同的颜色值,在第三行的前景色表达式中写入公式:if(C3>2000,-65536,-16777216),判断如果订单金额大于 2000 则设置前景色为红色(前景色和背景色属性是每一个单元格的属性,所以要在第三行所有单元格的前景色和背景色表达式中写入表达式)。

报表文件保存到 JAVA 工程类路径,命名为 "订单明细.rpx"。

编写 SPL 脚本

A B
1 >report_config("raqsoftConfig.xml") // 读取配置文件信息
2 =report_open("订单明细.rpx") // 读取报表文件
3 =report_run(A2) // 运算报表
4 =report_exportXls@x(A2,"D:\\ 订单明细 \\ 订单明细.xlsx") // 导出 excel 文件保存到 D 盘订单明细目录下
5 =report_exportPdf(A2,"D:\\ 订单明细 \\ 订单明细.pdf") // 导出 pdf 文件保存到 D 盘订单明细目录下
6 =report_exportHtml(A2,"D:\\ 订单明细 \\ 订单明细.html") // 导出 html 文件
7 =report_exportDoc(A2,"D:\\ 订单明细 \\ 订单明细.docx") // 导出 word 文件

A1:>report_config("raqsoftConfig.xml") 读取配置文件信息,此处使用相对路径,JAVA 程序执行时相对工程类路径。如需要在 SPL 中执行脚本,则此处配置文件更改为读取 SPL 安装目录下的 config\raqsoftConfig.xml

A2:=report_open("订单明细.rpx"),读取报表文件,这里可以用绝对路径,也可以相对 JAVA 工程类路径。

A3:=report_run(A2),运算报表。

A4:=report_exportXls@x(A2,"D:\\ 订单明细 \\ 订单明细.xlsx"),导出 excel 文件保存到 D 盘订单明细目录下

A5:=report_exportPdf(A2,"D:\\ 订单明细 \\ 订单明细.pdf"),导出 pdf 文件到指定目录。

A6:=report_exportHtml(A2,"D:\\ 订单明细 \\ 订单明细.html"),导出 html 文件

A7:=report_exportDoc(A2,"D:\\ 订单明细 \\ 订单明细.docx"),导出 word 文件

JAVA 程序调用

在 JAVA 工程中新建类 callSPL1 ,代码如下:

import java.sql.*;
public class callSPL1 {
	public static void main(String[] args) {
		Connection con = null;
		PreparedStatement st;
		try{
		//通过JDBC方式连接SPL
		Class.forName("com.esproc.jdbc.InternalDriver");
		con= DriverManager.getConnection("jdbc:esproc:local://");
		//以调用存储过程方式调用 
		st =con.prepareCall("call orderlist.splx()");
		//将orderlist.splx放到类路径中,这里可以直接写名称调用
		boolean hasResult = st.execute();
		//没有返回值得调用
		con.close();
		st.close();
		}
		catch(Exception e){
		System.out.println(e);
		}
	}
}

执行程序查看结果

imagepng

imagepng

SPL 带数据源

需求

从《订单.xlsx》取数,要求结果按照地区、城市进行分组统计,列出明细数据,并按照地区、城市对订单金额做汇总统计生成如下 Excel 文件:

imagepng

先看下《订单.xlsx》中数据结构,里边记录了订单信息数据,如下图:

imagepng

报表模板设计

imagepng

前两列按照地区、城市分组扩展,设置合并单元格,C3 单元格通过 select 函数以列表方式取数扩展,D3、D4 单元格中对订单金额进行汇总操作。报表命名为 "汇总统计.rpx",保存到 JAVA 工程类路径中。

编写 SPL 脚本


A

B

1

=connect("demo")

//连接 demo 数据源

2

=A1.query("select * from 订单 where year(订购日期)=2012")

//从 demo 数据源的订单表中取数

3

>report_config("raqsoftConfig.xml")

//读取配置文件信息

4

=report_open("汇总统计.rpx")

//读取报表文件

5

=report_run(A3;A1:"ds1")

//将 A1 中的数据作为报表中 ds1 数据集的结果

6

=report_exportXls@x(A3,"D:\\汇总统计 \\ 汇总统计.xlsx")

//导出 excel 文件

7

=report_exportPdf(A3,"D:\\汇总统计 \\ 汇总统计.pdf")

//导出 pdf 文件

8

=report_exportHtml(A3,"D:\\汇总统计 \\ 汇总统计.html")

//导出 html

9

=report_exportDoc(A3,"D:\\汇总统计 \\ 汇总统计.docx")

//导出 word

10

>A1.close()

//关闭数据源连接


A1:=connect("demo"),连接 demo 数据源,注意:demo 数据源要在 raqsoftConfig.xml 中配置

A2:=A1.query("select * from 订单 where year( 订购日期)=2012"),从 demo 数据源的订单表中取 2012 年数据。

A5:=report_run(A3;A1:"ds1"),将 A1 中的数据作为报表中 ds1 数据集的结果,A1 中结构要和报表数据集 ds1 结构相同。

将脚本命名为 ordertotal.splx,保存到 JAVA 工程类路径下。

JAVA 程序调用

在 JAVA 工程中新建类 callSPL2 ,代码如下:

import java.sql.*;
public class callSPL2 {
	public static void main(String[] args) {
		Connection con = null;
		PreparedStatement st;
		try{
		Class.forName("com.esproc.jdbc.InternalDriver");
		con= DriverManager.getConnection("jdbc:esproc:local://");
		//以调用存储过程方式调用 
		st =con.prepareCall("call ordertotal.splx()");//将ordertotal.splx放到类路径中,这里可以直接调用
		boolean hasResult = st.execute();
		con.close();
		st.close();
		}
		catch(Exception e){
		System.out.println(e);
		}
	}
}

执行并查看结果

imagepng

imagepng

报表数据源带入参

需求

从报表设计器自带的 demo 数据库取出订单等数据,对订单金额做汇总统计,要求纵向按照员工维度、横向按照地区、产品类别两个维度分片统计,并将年份做为参数传入,取对应年数据将结果生成 Excel 和 pdf 文件,结果如下:

imagepng

报表模板设计

报表中增加参数 rq,用于接收年份参数:
imagepng

报表中增加数据集:
ds1:SELECT 客户. 地区, 客户. 城市, 订单明细. 数量, 订单明细. 折扣, 订单明细. 单价, 订单. 雇员 ID, 订单. 订购日期, 订单明细. 产品 ID FROM 订单明细, 订单, 客户 WHERE 客户. 客户 ID = 订单. 客户 ID AND 订单. 订单 ID = 订单明细. 订单 ID and 订单. 订购日期 is not null and 客户. 地区 in (‘华南’,‘西南’) and year(订单. 订购日期)>=?
数据集中通过参数对年份进行过滤,在 ds1 数据集参数选项卡处增加参数:
imagepng
ds2:SELECT 姓氏 || 名字 as 员工名称, 雇员. 雇员 ID FROM 雇员
ds3:SELECT 产品. 产品 ID, 类别. 类别名称, 产品. 类别 ID FROM 产品, 类别 WHERE 类别. 类别 ID = 产品. 类别 ID
将数据集对应数据源名称设置成 demo(报表运算时会根据此数据源名称找对应配置文件中数据库连接):

imagepng

注:设置数据集之前,先启动 demo 数据库,设计器—本地应用—启动示例数据库。

报表模板

imagepng

A4:=ds1.group(雇员 ID; 雇员 ID:1),按照雇员 ID 分组,并在 A4 单元格显示值表达式中写入: ds2.select(员工名称, 雇员 ID==value(),1),从 ds2 数据集中取出员工名称显示。

C2~C3:按照地区、城市横向分组扩展。

C4:=ds1.sum( 数量单价 ),先通过单价数量算出订单金额,再进行汇总统计

D3:按照类别名称横向分组扩展

D4:=ds1.sum(数量 * 单价, 产品 ID in ds3.select( 产品 ID)),ds3 数据集中,类别和产品是一对多关系,所以此处条件用 in

报表命名为 "订单分析.rpx",保存到 JAVA 工程类路径下。

编写 SPL 脚本

SPL 中增加参数 year1 用于年份过滤:

imagepng

A B
1 >report_config("raqsoftConfig.xml") // 读取配置文件信息
2 =report_open("订单分析.rpx") // 读取报表文件
3 =report_run(A2,year1:"rq") // 计算报表,将年份传递给 rq 参数
4 =report_exportXls@x(A2,"D:\\ 汇总分析 \\"+string(year1)+"年汇总分析.xlsx") // 将结果保存到 D 盘子目录汇总分析下,文件命名前增加年份
5 =report_exportPdf(A2,"D:\\ 汇总分析 \\"+string(year1)+"年汇总分析.pdf") // 生成 pdf 文件
6 =report_exportHtml(A2,"D:\\ 汇总分析 \\"+string(year1)+"年汇总分析.html") // 生成 html 文件
7 =report_exportDoc(A2,"D:\\ 汇总分析 \\"+string(year1)+"年汇总分析.docx") /// 生成 word 文件
8 =create(path).record(["D:\\ 汇总分析 \\"+string(year1)+"年汇总分析.xlsx","D:\\ 汇总分析 \\"+string(year1)+"年汇总分析.pdf","D:\\ 汇总分析 \\"+string(year1)+"年汇总分析.html","D:\\ 汇总分析 \\"+string(year1)+"年汇总分析.docx"]) //// 将导出文件路径名称放到序表中
9 return A6 // 返回序表,JAVA 程序可以获取导出文件地址

A8: =create(path).record(["D:\\ 汇总分析 \\"+string(year1)+"年汇总分析.xlsx","D:\\ 汇总分析 \\"+string(year1)+"年汇总分析.pdf","D:\\ 汇总分析 \\"+string(year1)+"年汇总分析.html","D:\\ 汇总分析 \\"+string(year1)+"年汇总分析.docx"]),新建序表,里边包含一个字段 path,将导出的 excel、pdf、html、word 路径及名称插入到序表中,这样 JAVA 程序可以获取到导出的文件地址,可以做其他业务处理,比如 WEB 端获取地址,可以进行下载。

将脚本命名为 "order.splx" 保存到 JAVA 工程类路径中。

JAVA 程序调用

在 JAVA 工程中新建类 callSPL3 ,代码如下:

import java.sql.*;
public class callSPL3 {
	public static void main(String[] args) {
		Connection con = null;
		PreparedStatement st;
		try{
		Class.forName("com.esproc.jdbc.InternalDriver");
		con= DriverManager.getConnection("jdbc:esproc:local://");
		//以调用存储过程方式调用 
		st =con.prepareCall("call order.splx(?)");
		//SPL脚本中设置了一个参数,此处用一个问号与之对应
		st.setObject(1, 2012);//给第一个参数传递参数值
		ResultSet rs = st.executeQuery();
		while (rs.next()) {
			System.out.println(rs.getString(1));//获取文件路径并输出
			
		}
		con.close();
		st.close();
		rs.close();
		}
		catch(Exception e){
		System.out.println(e);
		}
	}
}

执行并查看结果

imagepng

imagepng

并且 JAVA 程序执行时,能够获取文件路径:

imagepng

这样程序可以根据获取到的文件路径以及名称做自己业务相关处理,比如放到 web 端下载,或者需要在 web 展示报表,可以根据获取到的 html 的地址直接调用就行,html 展现结果如图:

imagepng