需求背景:
需要做一款类似excel的在线报表工具,可以支持引用其他单元格的数据进行加减乘除,也可以跨报表取数,同时可以嵌套取数。
实现基础:
使用工具:com.googlecode.aviator.AviatorEvaluator做表达式计算,将所有单元格的公式,转化为字符串,通过 AviatorEvaluator.execute 方法计算出结果,作为输出插入到数据库
实现逻辑:
1、设计模板
首先需要做一个了类似excel的表格,进行模板设置,默认只有一个单元格,可以添加行、添加列。
选中单元格,可以设置单元格的类型:
文本输入框、数字输入框、当前报表公式计算、跨报表公式计算、文本、下拉选项、其他接口自定义的数据字典下拉框。
扩展一下,可以设置单元格验证。
模板数据每个单元格是一条数据存入数据库,如果模板有1000个单元格,100行,10列,那这个模板就是1000条数据。
如果选择当前报表公式计算,或者跨报表公式计算,可以使用前端页面,进行简单的公式编辑,如下图
公式的格式是个数组,作为json字符串存到数据库,每次计算根据公式进行具体的计算
[
{"text":"1","type":"text","timeScope":"","param":"","custom1":"{}"},
{"text":"+","type":"operation","timeScope":"","param":"","custom1":"{}"},
{"text":"2","type":"text","timeScope":"","param":"","custom1":"{}"},
{"text":"-","type":"operation","timeScope":"","param":"","custom1":"{}"},
{"text":"3","type":"text","timeScope":"","param":"","custom1":"{}"},
{"text":"*","type":"operation","timeScope":"","param":"","custom1":"{}"},
{"text":"4","type":"text","timeScope":"","param":"","custom1":"{}"},
{"text":"/","type":"operation","timeScope":"","param":"","custom1":"{}"},
{"text":"5","type":"text","timeScope":"","param":"","custom1":"{}"},
{"text":"-","type":"operation","timeScope":"","param":"","custom1":"{}"},
{"text":"(","type":"operation","timeScope":"","param":"","custom1":"{}"},
{"text":"6","type":"text","timeScope":"","param":"","custom1":"{}"},
{"text":"*","type":"operation","timeScope":"","param":"","custom1":"{}"},
{"text":"7","type":"text","timeScope":"","param":"","custom1":"{}"},
{"text":")","type":"operation","timeScope":"","param":"","custom1":"{}"},
{"text":"+","type":"operation","timeScope":"","param":"","custom1":"{}"},
{"text":"FN_AVG","type":"FUNCTION","timeScope":"","param":[
{"text":"12","type":"text","timeScope":"","param":[],"custom1":"{}"},
{"text":"45","type":"text","timeScope":"","param":[],"custom1":"{}"},
{"text":"98","type":"text","timeScope":"","param":[],"custom1":"{}"}
]
}
]
2、计算逻辑
1)根据模板Id,通过type查询出所有需要计算的单元格,如果是输入框类型的则不需要计算
2)取出所有的单元格的数据,存入hashmap,设置isNew:false
3)遍历单元格,根据公式进行计算,公式中取值可能是单元格数据,也可能是正常的数字,单元格数据有可能是另外一个公式,因此就是一个递归的过程,公式中每个参数,都是先找到最底层,进行计算,计算之后,将hashmap中的isNew改为true,下次再用到此参数的时候,不需要重新计算
4)所有的都计算之后,将新的数据存入数据库
优势:
实现相对简单,不需要动态设置数据库表字段,将公式以数据的形式存入数据库,修改公式之后,实时生效
劣势:
完全依赖com.googlecode.aviator.AviatorEvaluator,数据量太大的情况下,计算量很大,如果一个报表100行,10列,就需要计算1000次,比较好的方式是将所有的模板,可能用到的数据都查询到内存里,再内存中进行计算,如果每个格子都进行数据库io,效率会降低1000倍以上(实测,踩过坑),目前虽然会占用一部分内存,但是保证项目可以正常运行
最后:
如果有需要的,可以私信了解具体解决方案。