
提到报表里的表达式,很多人的第一反应是 =SUM(D4),合计一下就完了。其实 JimuReport积木报表的单元格表达式能做的远不止这些:算环比、按权重打分评级、合计为 0 时自动隐藏、防除零、截短长名称、写循环、调自己写的 Java 函数……很多原本要改 SQL 或者写代码的需求,在一个单元格里就能解决。
这些玩法过去有不少会踩坑:合计只算了第一行、case 里套 SUM 直接报错、外层 round 不生效等等。积木报表最近针对表达式函数做了一轮专项优化,把 GitHub 上用户反馈的问题逐个修掉,下面这些写法现在都能稳定使用了。改动随 JimuReport积木报表 v2.6.0 发布。
本文的写法都可以直接复制到单元格里。示例中的 e 是数据集编码,amount、score、name 等是字段名,换成自己报表里的即可。
玩法一:一个单元格算环比
月度销售明细按月份排成一列,想在旁边加"比上月增加多少"和"增长率"两列。不用改 SQL,在明细行里直接写上一行的坐标,积木报表会按行把 D3 换成当前行的上一行:
=case(isnumber(D3),D4-D3,'')
=case(isnumber(D3),round((D4-D3)/D3*100,2),'')
外面套一层 isnumber,是因为第一行的上一行是表头,不是数字,这时显示空白。
注意:分组报表不适合这么写,每组第一行会取到上一组的小计。
玩法二:加权打分 + 自动评级
考核表里两项得分分别占 25% 和 40%,合计行要算加权总分;再按总额给出"高 / 低"评级:
=SUM(E4*0.25,F4*0.4)
=case(SUM(E4)>300,'高','低')
合计函数可以放在括号里先乘权重再求和,也可以直接放进 case 做判断。这两种写法在优化前分别会"只算第一行"和"直接报错",现在都没问题了。
玩法三:合计为 0 时不显示,平均值跳过 0
合计行在没有数据时显示一个 0,容易被误读成"真的有一笔 0 元"。用脚本写法判断一下,为 0 时显示空白:
=(if(SUM(I4)==0){return '';} else {return SUM(I4);})
算平均值时,值为 0 的行往往不该参与平均,用 COUNTNZ 只数非零的行,再用 ROUND 保留两位小数:
=ROUND(SUM(F4)/COUNTNZ(E4),2)
玩法四:除数为 0 也不怕
算单价、完成率这类比率时,除数可能为 0。用 iferror 包起来,出错时显示自己指定的文字,而不是报错或者一个误导人的 0:
=iferror(#{e.amount}/#{e.zero},'除零')
字段值可能为空时,intval 也能正常处理:
=intval(#{e.bonus})+1
玩法五:先算比率再四舍五入
用 compute 在数据集字段之间直接做运算,外面再套 round 控制小数位:
=round(#{e.compute(amount/score)},2)
优化前外层的 round 不生效,现在会按两位小数显示。
玩法六:长名称自动截短
客户名称太长会把表格撑开,可以超过 2 个字时只显示前 3 个字,否则原样显示:
=case(string.length('#{e.name}')>2,string.substring('#{e.name}',0,3),'#{e.name}')
优化前,名称很短、条件不满足时也会去截取,导致报错;现在只在条件满足时才截取。
玩法七:日期想怎么显示就怎么显示
按指定格式显示日期,或者把年、月、日拼成自己想要的样子:
=date2Str('#{e.cdate}','yyyy-MM-dd',1)
=CONCAT(YEAR('#{e.cdate}'),'-',MONTH2('#{e.cdate}'),'-',DAY('#{e.cdate}'))
玩法八:在单元格里写脚本
积木报表的表达式引擎是 Aviator,除了调用函数,还能用 let 定义变量、用 for 写循环、用 if 写分支。简单函数写不出来的计算,可以直接写一小段脚本:
=(let t=0; for i in range(0,#{e.flag}+3){ t=t+i; } return t;)
if 判断里可以直接引用当前行的单元格,按行计算:
=(if(G4==1){return (G4+E4)*200;})
玩法九:集合和数组也能玩
seq.list 构造集合,concat 合并集合,count 数个数:
=count(concat(seq.list(1,2), seq.list(3)))
结果是 3。一个字段里存了用逗号分隔的多个值,只想显示第二个?拆开后直接用下标取:
=string.split('a,b,c',',')[1]
结果是 b。不加下标时,整个数组会显示成 [a, b, c]。
玩法十:调用自己写的 Java 函数
内置函数满足不了的业务规则(比如公司内部的计算口径),可以用 Java 写自定义函数注册进去,在单元格里像内置函数一样调用。
第一步:写一个类实现 IJmExpressCustom,注册成 Spring Bean
在项目里新建一个类,实现积木报表提供的 IJmExpressCustom 接口,加上 @Component 注解。在 addFunction 方法里把自己写的函数加到表达式引擎里:
import com.googlecode.aviator.AviatorEvaluatorInstance;
import com.googlecode.aviator.runtime.function.AbstractFunction;
import com.googlecode.aviator.runtime.function.AbstractVariadicFunction;
import com.googlecode.aviator.runtime.function.FunctionUtils;
import com.googlecode.aviator.runtime.type.AviatorObject;
import com.googlecode.aviator.runtime.type.AviatorRuntimeJavaType;
import org.jeecg.modules.jmreport.desreport.express.enhance.IJmExpressCustom;
import org.springframework.stereotype.Component;
import java.util.Collection;
import java.util.Map;
@Component
public class JimuExpressCustomDemo implements IJmExpressCustom {
@Override
public void addFunction(AviatorEvaluatorInstance instance) {
instance.addFunction(new MyMonthFunction());
instance.addFunction(new ArrTestFunction());
}
}
第二步:写函数本身
固定参数的函数继承 AbstractFunction,几个参数就重写对应参数个数的 call 方法,getName 返回在单元格里使用的函数名。下面这个 mymonth 从日期(yyyy-MM-dd 或 yyyy-MM-dd HH:mm:ss)中取出月份,并去掉前导 0:
public static class MyMonthFunction extends AbstractFunction {
@Override
public AviatorObject call(Map<String, Object> env, AviatorObject arg) {
String value = FunctionUtils.getStringValue(arg, env);
if (value == null || value.length() < 7) {
return AviatorRuntimeJavaType.valueOf(value);
}
return AviatorRuntimeJavaType.valueOf(Integer.parseInt(value.substring(5, 7)));
}
@Override
public String getName() {
return "mymonth";
}
}
参数个数不固定的函数继承 AbstractVariadicFunction,重写 variadicCall。下面这个 arr_test 返回每个参数的"类型:长度",多个参数用 | 分隔:
public static class ArrTestFunction extends AbstractVariadicFunction {
@Override
public AviatorObject variadicCall(Map<String, Object> env, AviatorObject... args) {
StringBuilder sb = new StringBuilder();
for (AviatorObject arg : args) {
Object value = arg.getValue(env);
String type = value == null ? "null" : value.getClass().getSimpleName();
int len = value == null ? 0 : (value instanceof Collection ? ((Collection<?>) value).size() : String.valueOf(value).length());
sb.append(sb.length() > 0 ? "|" : "").append(type).append(':').append(len);
}
return AviatorRuntimeJavaType.valueOf(sb.toString());
}
@Override
public String getName() {
return "arr_test";
}
}
两个函数类写成 JimuExpressCustomDemo 的静态内部类,或者单独的类都可以。
第三步:在单元格里调用
重启服务后,在单元格里像内置函数一样使用。参数可以传单元格坐标(明细行里按行取值),也可以直接传数据集字段:
=mymonth(J4)
=mymonth('#{ds.cdate}')
=arr_test('#{ds.payload}')
注意:字段值是原样替换进表达式的。传 JSON 数组、JSON 对象这类内容时要加引号,在函数里按字符串解析。
完整代码在积木报表示例工程 jimureport-example 的 JimuExpressCustomDemo 里,可以直接复制过去改。
玩法十一:列表下方的格子用 :: 互相引用
报表里常见这样的布局:上面是数据列表,列表下方有几个手写的汇总格子,比如"本月合计""上月结转""本月结余"。问题在于,列表展开后,下方格子的位置会往下移,设计时写的坐标就对不上了。
举个例子,在设计器里这样摆:
- 第 4 行是列表,
C4 里写 #{e.amount}
C6 是本月合计:=SUM(C4)
C7 是上月结转:手填一个数
C8 是本月结余,要用 C6 + C7
预览时如果查出 10 条数据,列表从 1 行展开成第 4~13 行,下方的格子跟着下移 9 行:本月合计实际到了第 15 行,上月结转到了第 16 行。
这时 C8 里如果写 =C6+C7,取到的是展开后第 6、7 行的数据,也就是列表里的两条明细,结果就错了。
:: 的意思是"这是设计时的坐标,请按列表展开后的位置换算"。 改成这样写:
=::C6+::C7
渲染时,积木报表会把 ::C6 换成"设计时的行号 + 列表多出来的行数",也就是 C15;::C7 换成 C16。不管查出来多少条数据,都能取到正确的格子。
:: 可以和常量、函数一起用,比如:
=::C69+::C70+10
=cnmoney(::A6)
简单记:引用列表里面的格子,直接写坐标;引用列表下方、会被列表挤下去的格子,坐标前加 ::。
玩法十二:把公式原样写出来给人看

给业务人员看的报表,常要在表头或表尾说明某一列怎么算。可是只要文字里出现 =函数(,就会被当成表达式算掉。
这是 v2.6.0 新增的规则:和 Excel 一样,在开头加一个英文单引号,公式就原样显示:
'=SUM(D4)
'说明:用 =abs(-3) 取绝对值
'参考 ::C69 的值
具体规则:单元格文字以 ' 开头、结尾不是 ',并且以 = 开头、含有 =函数( 或含有 ::坐标 时,去掉开头的单引号原样显示。前后都有单引号的 '=SUM(D4)'、不含函数或坐标的 '0012,保持原来的行为。网页预览、导出 Excel、PDF、Word 都按这条规则显示。
表达式写法速查
把上面用到的写法整理成两张表,写报表时可以对照着查。
引用和语法
| 写法 |
含义 |
示例 |
=... |
以 = 开头的单元格按表达式计算 |
=SUM(D4) |
#{e.字段} |
引用数据集 e 的字段值 |
=intval(#{e.bonus})+1 |
'#{e.字段}' |
把字段值当作字符串传给函数 |
=date2Str('#{e.cdate}','yyyy-MM-dd',1) |
#{e.compute(...)} |
在数据集字段之间做运算 |
=round(#{e.compute(amount/score)},2) |
D4 |
引用单元格,明细行里按行取当前行的值 |
=E4+#{e.flag} |
D3(写在第 4 行) |
引用上一行,明细行里按行换成当前行的上一行 |
=case(isnumber(D3),D4-D3,'') |
::C6 |
引用列表下方、会被列表挤下去的格子,按展开后的实际位置换算 |
=::C6+::C7 |
'=... |
单引号开头,原样显示、不计算(v2.6.0+) |
'=SUM(D4) 显示 =SUM(D4) |
=(...; return ...) |
写一段脚本,用 return 返回结果 |
=(if(SUM(I4)==0){return '';} else {return SUM(I4);}) |
[n] |
取数组的第 n+1 项(从 0 开始) |
=string.split('a,b,c',',')[1] 结果 b |
常用函数
| 类别 |
函数 |
作用 |
示例 |
| 统计 |
SUM |
求和,括号里可以先运算 |
=SUM(E4*0.25,F4*0.4) |
| 统计 |
COUNTNZ |
统计非零的个数 |
=ROUND(SUM(F4)/COUNTNZ(E4),2) |
| 统计 |
DBSUM / DBAVERAGE |
对数据集字段直接求和 / 求平均 |
=DBSUM(e.amount) |
| 数学 |
round / ROUND |
四舍五入到指定小数位 |
=round(#{e.compute(amount/score)},2) |
| 数学 |
math.ceil / math.floor |
向上 / 向下取整 |
=math.ceil(10/3) 结果 4 |
| 数学 |
abs |
绝对值 |
=abs(-3)*1.5 结果 4.5 |
| 数学 |
intval |
转成整数,空值也能处理 |
=intval(#{e.bonus})+1 |
| 数学 |
rand |
随机数 |
=rand(10)<10 结果 true |
| 条件 |
case |
条件成立返回第二个参数,否则返回第三个 |
=case(SUM(E4)>300,'高','低') |
| 条件 |
iferror |
计算出错时返回指定值 |
=iferror(#{e.amount}/#{e.zero},'除零') |
| 条件 |
isnumber |
判断是否为数字 |
=case(isnumber(D3),D4-D3,'') |
| 文本 |
string.length / string.substring |
字符串长度 / 截取字符串 |
=case(string.length('#{e.name}')>2,string.substring('#{e.name}',0,3),'#{e.name}') |
| 文本 |
string.split |
按分隔符拆成数组 |
=string.split('a,b,c',',') 显示 [a, b, c] |
| 文本 |
upper |
转大写 |
=upper('abc')+'XY' 结果 ABCXY |
| 文本 |
CONCAT |
拼接多个值 |
=CONCAT(YEAR('#{e.cdate}'),'-',MONTH2('#{e.cdate}'),'-',DAY('#{e.cdate}')) |
| 日期 |
date2Str |
日期按格式转成文字 |
=date2Str('#{e.cdate}','yyyy-MM-dd',1) |
| 日期 |
YEAR / MONTH2 / DAY |
取日期的年 / 月 / 日 |
见上一行 CONCAT 示例 |
| 日期 |
isdate / istime |
判断是否为日期 / 时间 |
=istime('#{e.ctime}') |
| 集合 |
seq.list / concat / count |
构造集合 / 合并集合 / 统计个数 |
=count(concat(seq.list(1,2), seq.list(3))) 结果 3 |
| 脚本 |
let / for ... in range |
定义变量 / 循环 |
=(let t=0; for i in range(0,#{e.flag}+3){ t=t+i; } return t;) |
| 脚本 |
undef / is_def |
删除变量 / 判断变量是否存在 |
=(let x=1; undef(x); is_def(x)) 结果 false |
| 样式 |
FONTBOLD |
加粗显示单元格的值 |
=FONTBOLD(C4) |
完整的函数列表和参数说明见在线文档 help.jimureport.com。
几个容易忽略的细节
除了上面这些玩法,这次还顺手修好了一批"小但烦人"的问题:
=rand(10)<10、=upper('abc')+'XY' 这类"函数后面接比较或运算"的写法,不会再被误当成文字拼接。
=abs(-3)*1.5 结果是 4.5,原来小数部分被当成文字,显示成 3.5。
='Tom'+'-'+100 结果是 Tom-100,引号里的文字不会再被转成小写。
=math.ceil(10/3) 显示 4,不再是 4.0;=math.ceil(#{e.amount}/7) 先运算再取整也不报错了。
- 明细行里
=FONTBOLD(C4)、=E4+#{e.flag} 每行取自己那一行的值,不会都取成第一行。
- 上下两个列表时,下面列表的合计不会再把上面的也算进去。
- 分组小计里的表达式列(包括两级分组)不再为空。
DBSUM、DBAVERAGE 在报表还没保存时也能显示结果。
- 不带
= 的表头文字(如 arr_test(E4))不会再被改写成 E4:E0。
这次一共处理了 29 项,其中 27 项改了代码,2 项原来就支持,补了示例和文档。
升级会影响老报表吗
我们用 107 张报表(含模板市场的 51 张)对比了新旧代码的结果:
| 场景 |
结果 |
| 网页预览 |
只有 3 张有差异,都是单引号规则带来的预期变化 |
| 导出 Excel |
98 张完全一致,其余差异来自随机数(rand)或单引号规则 |
| 导出 PDF |
96 张完全一致,其余差异来自随机数或单引号规则 |
| 前端截图 |
16 张中 15 张像素级一致,另 1 张是单引号规则的预期差异 |
另外有少数报表在新旧代码下都导出失败,是原来就有的问题,和这次改动无关。数据填报、设计器编辑保存、导出 Word 也都验证过,结果正常;10 万行导出 Excel 的耗时和改前相当,没有变慢。
唯一需要留意的是单引号规则:如果老报表里有以 ' 开头、又含 =函数( 或 ::坐标 的文字,原来会被计算,升级后会原样显示。
以下问题这次还没有处理:case 里混用两个数据集(#664,建议先在 SQL 里关联)、API 数据集的分页合计和条件求和(#2891)、分组报表里的环比。
总结
一个单元格能做的事,比大多数人想的要多:算环比、加权评级、隐藏 0、防除零、截短名称、格式化日期、写循环、处理数组、调自定义函数、原样显示公式。很多以前要改 SQL 或写代码的需求,在 JimuReport积木报表里写一行表达式就能解决。
这些玩法随 v2.6.0 发布,在线文档(help.jimureport.com)里 12 篇函数文档已经补充了示例,新功能标注为"v2.6.0+ 支持"。
本文为 JimuReport AI 专题研究系列文章。