日常开发中，难免遇到需要导入导出的业务场景，如果需要再表格的尾部插入一些自定义的统计项，传统的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。下载附件。导出结果如下：
导出结果
复杂的功能通过简单的几行代码就实现了。
项目源码地址： https://github.com/zouzhiy
国内镜像地址： https://gitee.com/zouzhiy/zouzhiy-excel
