背景
业务提出一个需求:在导出的Excel文档中行和列个添加合计项,方便数据统计。本项目的数据库用的是MySQL,后端实现是Java,使用POI实现的Excel导出。
问题梳理
列的合计,在查询的SQL中添加合计字段,将几个字段相加即可实现;
行的合计,根据实际业务场景有两类实现方案:导出时实现和查询时实现,以下分别说说不同的实现逻辑。
方案一、在Excel导出时添加公式实现求和
在导出Excel时,指定位置上添加求和公式,核心代码如下所示。
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.ss.usermodel.Cell;
// 指定行
Row row = sheet.createRow(clearList.size()+1);
// 指定列,从0开始
Cell formulaCell = row.createCell(3);
logger.info("=====打印公式: =====%s", "SUM(D2:K"+String.valueOf(clearList.size()+1)+")");
// 设置公式
formulaCell.setCellFormula("SUM(D2:K"+String.valueOf(clearList.size()+1)+")");
HSSFFormulaEvaluator formulaEvaluator =
(HSSFFormulaEvaluator) hssfWb.getCreationHelper().createFormulaEvaluator();
formulaEvaluator.evaluateFormulaCell(formulaCell);
导出结果如下所示。
以上方案可实现在导出的Excel中自动求和。
方案二、在SQL查询时实现
方案二有两种实现方式,第一种是函数实现,对于MySQL求和的写法,用 with rollup 函数实现。
原始数据
订单表中 is_enable 有两种不同类型
实现方式
利用 with rollup 函数实现分组求和,代码如下。
SELECT
COALESCE (is_enable, '合计') as is_enable,
count(*) as cnt,
sum(pay_amount) as pay_amount
FROM
tb_order
GROUP BY
is_enable
WITH ROLLUP;
最后一行为合计行,对数量和金额分别进行合并统计,查询结果如下所示。
以上方案支持多个字段,加上 user_id 字段后,各自进行合计,但展示的效果似乎不是想要的(多了两行),如下图所示。
第二种方式,可以使用数据集的拼接,即总和分两部分进行 union all 拼接结果集,代码如下。
SELECT
user_id,
NAME,
pay_amount,
num,
data_info,
is_enable,
create_time
FROM
tb_order
UNION all
SELECT
'合计' as user_id,
null as NAME,
sum(pay_amount) as pay_amount,
count(num) num,
null as data_info,
null as is_enable,
null as create_time
FROM
tb_order
即把所有列都展示出来,需要统计的列使用函数,非统计列置为null,执行结果如下所示。