Spring利用zouzhiy-excel实现自定义表尾导出

日常开发中，难免遇到需要导入导出的业务场景，如果需要再表格的尾部插入一些自定义的统计项，传统的excel工具并不能提供很好的支持。  
今天在这里给大家推荐一款非常好用的 Excel 导入导出工具工具： zouzhiy-excel 。可以实现自定义的表尾导出！

zouzhiy-excel 简介

zouzhiy-excel 是一款 Excel 导入导出的轻量级工具。对 POI 的接口做了一层封装，使导入导出更加简便快捷。

Spring 环境下集成

<dependency>
    <groupId>io.github.zouzhiy</groupId>
    <artifactId>zouzhiy-excel-boot-starter</artifactId>
    <version>1.1.1</version>
</dependency>

开始使用

• 首先创建一对多的数据对象

@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
@ExcelClass(rowFootWrite = CustomRowFootWrite.class)
public class FootVO {

    @ExcelField(title = "姓名")
    private String username;


    @ExcelField(title = "年龄")
    private Integer age;


    @ExcelField(title = "分数")
    private BigDecimal score;

}

rowFootWrite 指定自定义表尾写入的实现

• 实现我们自定义的表尾写入逻辑

@Component
public class CustomRowFootWrite implements RowFootWrite {

    @Override
    public int write(SheetContext sheetContext, List<?> dataList) {
        BigDecimal totalScore = BigDecimal.ZERO;

        for (Object item : dataList){
            FootVO footVO = (FootVO) item;
            BigDecimal score= footVO.getScore();
            if (score != null){
                totalScore = totalScore.add(score);
            }
        }
        BigDecimal averageScore = totalScore.divide(new BigDecimal(dataList.size()), RoundingMode.HALF_DOWN);

        Sheet sheet = sheetContext.getSheet();
        int rowNum = sheet.getLastRowNum();

        int totalRowNum = rowNum +1;
        int averageRowNum = rowNum + 2;

        Row totalRow = sheet.createRow(totalRowNum);
        Cell totalCellHead = totalRow.createCell(1);
        totalCellHead.setCellValue("合计");
        Cell totalCellValue = totalRow.createCell(2);
        totalCellValue.setCellValue(totalScore.doubleValue());

        Row averageRow = sheet.createRow(averageRowNum);
        Cell averageCellHead = averageRow.createCell(1);
        averageCellHead.setCellValue("平均值");
        Cell averageCellValue = averageRow.createCell(2);
        averageCellValue.setCellValue(averageScore.doubleValue());

        return 2;
    }
}

• 接下来我们在 Controller 中添加一个接口，用于导出列表Excel，具体代码如下：

@RestController
@RequestMapping("foot")
public class FootExportContrller {

  @Resource
  private ZouzhiyExcelFactory zouzhiyExcelFactory;

  @GetMapping("list/export")
  public void exportUserList(HttpServletResponse response) {
    List<FootVO> voList = this.listVo();

    response.addHeader("Content-Disposition"
            , "attachment; filename*=utf-8''" + URLEncoder.encode("列表导出（表尾）.xlsx", StandardCharsets.UTF_8.name()));
    zouzhiyExcelFactory
            .write(response.getOutputStream())
            .sheet()
            .title("列表导出（表尾）")
            .titleRowStartIndex(0)
            .dataRowStartIndex(2)
            .write(voList, FootVO.class);
  }


  private List<FootVO> listVo() {
    Random random = new Random(System.currentTimeMillis());
    List<FootVO> voList = new ArrayList<>();
    for (int i = 0; i < 5; i++) {
      FootVO vo = FootVO.builder()
              .username("姓名-" + i)
              .age(random.nextInt(30))
              .score(BigDecimal.valueOf(random.nextDouble()))
              .build();
      voList.add(vo);
    }
    return voList;
  }
}

• 通过浏览器访问： http://localhost:8080/foot/list/export。下载附件。导出结果如下：
  

复杂的功能通过简单的几行代码就实现了。