Easypoi导出合并单元格并统计

需求

将数据导出后并将其相关内容进行汇总求和，插入到Excel中最后一行中

如下图：

实现

可以通过easyPoi的Api属性isStatistics设置进行实现

pom配置文件

 <!-- easypoi导入导出excel -->
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version> 4.2.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>4.2.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>4.2.0</version>
        </dependency>

功能实现

情况①：如果插入的excel的列表类型为实体类，则可以直接通过在实体类上面添加@Excel注解，并设置其isStatistics属性为true即可

实体类：

@Data //lombok的getter和setter的省略包
@NoArgsConstructor
@AllArgsConstructor
public class TurnoverVipVO { 
 @Excel(name = "企业名称")
    private String vipName;


 @Excel(name = "下属企业")
    private String department;

 @Excel(name = "所属车队")
    private String carTeam;

    @Excel(name = "金额类型")
    private String payType;

    @Excel(name = "期初余额(元)",isStatistics = true)
    private String starPrice;

    @Excel(name = "期末余额(元)",isStatistics = true)
    private String endPrice;

    @Excel(name = "充值金额(元)",isStatistics = true)
    private String rechargePrice;

    @Excel(name = "消费金额(元)",isStatistics = true)
    private String comsumeMoney;

    @Excel(name = "操作时间")
    private String modifyTime;
}

业务层代码：

//设置导出参数
 ExportParams params = new ExportParams();
            params.setType(ExcelType.XSSF);
            params.setMaxNum(1000000);
            params.setTitle("******"); //excel表名
            params.setSheetName("*****"); //sheet表名
            Workbook workbook = ExcelExportUtil.exportExcel(params, TurnoverVipVO.class, list); //参数，实体类，查询的list内容
            workbook.write(response.getOutputStream()); //写流启动
            workbook.close(); //关闭工作流

情况②：如果插入的内容是不是实体类，而是对应的ExcelExportEntity类型

 List<ExcelExportEntity> entity = new ArrayList<>();
            entity.add(new ExcelExportEntity("企业名称", "govName"));
            entity.add(new ExcelExportEntity("下属企业名称", "departmentName"));
            entity.add(new ExcelExportEntity("金额类型", "govAccountType"));
            //设置对应的statistics属性
            ExcelExportEntity beginMoneyList = new ExcelExportEntity("期初余额(元)", "beginMoney");
            beginMoneyList.setStatistics(true);
            entity.add(beginMoneyList);
            //设置对应的statistics属性
            ExcelExportEntity rechargeMoney = new ExcelExportEntity("充值金额(元)", "rechargeMoney");
            rechargeMoney.setStatistics(true);
            entity.add(rechargeMoney);
            //设置对应的statistics属性
            ExcelExportEntity consumMoney = new ExcelExportEntity("消费金额(元)", "consumMoney");
            consumMoney.setStatistics(true);
            entity.add(consumMoney);
            //设置对应的statistics属性
            ExcelExportEntity finalMoneySurplus = new ExcelExportEntity("期末余额(元)", "finalMoneySurplus");
            finalMoneySurplus.setStatistics(true);
            entity.add(finalMoneySurplus);

 业务层代码：

//设置导出参数
 ExportParams params = new ExportParams();
            params.setType(ExcelType.XSSF);
            params.setMaxNum(1000000);
            params.setTitle("******"); //excel表名
            params.setSheetName("*****"); //sheet表名
            Workbook workbook = ExcelExportUtil.exportExcel(params, entity , list); //参数，实体类，查询的list内容
            workbook.write(response.getOutputStream()); //写流启动
            workbook.close(); //关闭工作流

五一将至，小董提前恭祝大家五一快乐！🌟 💥 🌟