EasyExcel导出一个最后一行是合计行的Excel

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

---

前言

使用EasyExcel导出一个最后一行是合计行的Excel

一、前置操作

这里省略导包等的操作，使用的仍然是3.X以前的EasyExcel版本
在要导出的实体类上使用@ExcelIgnore注解标注不需要导出的属性，使用@ExcelProperty(value = “xxx”,index = 0)注解标注需要导出的属性，其中value是导出的Excel表头显示的内容，index是表头字段的下标，从0开始
这里写了一个工具类，指定哪几列进行合并计算，哪一列写入“合计”汉字

二、使用步骤

1.实体类

这里主要看一下两个注解是怎么使用的：

@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
public class WorkHourPerspectiveCell implements Serializable {
    private static final long serialVersionUID=1L;

    @ApiModelProperty(value = "工艺集工位id")
    @ExcelIgnore
    private String pCellId;

    @ApiModelProperty(value = "工艺集工位")
    @ExcelIgnore
    private String pCellName;

    @ApiModelProperty(value = "工厂结构工位id")
    @ExcelIgnore
    private String cellId;

    @ApiModelProperty(value = "工厂结构工位")
    @ExcelProperty(value = "工位名称",index = 0)
    @ColumnWidth(value = 20)
    private String cellName;

    @ApiModelProperty(value = "总工时")
    @ExcelProperty(value = "总工时",index = 1)
    @ColumnWidth(value = 20)
    private Integer sumHours;

    @ApiModelProperty(value = "机时")
    @ExcelProperty(value = "机时",index = 2)
    @ColumnWidth(value = 15)
    private Integer machineHours;

    @ApiModelProperty(value = "人时")
    @ExcelProperty(value = "人时",index = 3)
    @ColumnWidth(value = 15)
    private Integer personHours;

    @ApiModelProperty(value = "节拍")
    @ExcelProperty(value = "节拍",index = 4)
    @ColumnWidth(value = 15)
    private Integer hourBeat;

}

2.导出方法

public void export(WorkHourPerspectiveCellDto dto,HttpServletResponse response) {
        // 数据准备
        List<WorkHourPerspectiveCell> records = getCellHourList(dto);
        records.stream().forEach(e -> e.setSumHours(e.getMachineHours() + e.getMachineHours()));
        try {
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            ServletOutputStream outputStream = response.getOutputStream();
            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
            String fileName = null;
            try {
                fileName = URLEncoder.encode("工位标准工时", "UTF-8").replaceAll("\\+", "%20");
            } catch (UnsupportedEncodingException e) {
                e.printStackTrace();
            }
            response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
            // 这里数据长度+1的原因是在最后加一行合计
            TotalRowHandler totalRowHandler = new TotalRowHandler(records.size()+1);
            ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), WorkHourPerspectiveCell.class).registerWriteHandler(totalRowHandler).build();
            // 写入合计行样式；默认表头只有一行
            WriteSheet writeSheet = EasyExcel.writerSheet().build();
			//这里我指定第1、2、3列进行合计
            HashSet<Integer> indexSet = new HashSet<>();
            indexSet.add(1);
            indexSet.add(2);
            indexSet.add(3);
            // 合计行
            WorkHourPerspectiveCell sumCell = ExcelUtils.sumCell(true,0,indexSet,records,WorkHourPerspectiveCell.class);
            records.add(sumCell);
            excelWriter.write(records, writeSheet);
            // 关闭流
            excelWriter.finish();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

3.进行合计的工具类

这里为了更具有通用性使用了反射

public class ExcelUtils {
    /**
     *
     * @param ifSum 是否写入“合计”汉字
     * @param sumIndex 写入“合计”的列下标
     * @param indexSet 需要计算合计的列的下标集合，不能重复
     * @param records 数据集
     * @return
     */
    public static <T> T sumCell(Boolean ifSum, Integer sumIndex, HashSet<Integer> indexSet, List<T> records,Class<T> clazz) throws InstantiationException, IllegalAccessException {
        // 获取类的所有属性
        Field[] declaredFields = clazz.getDeclaredFields();
        T t = clazz.newInstance();
        for (Field field: declaredFields) {
            field.setAccessible(true);
            ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);
            if (annotation != null) {
                int index = annotation.index();
                if (ifSum) {
                    if (index == sumIndex) {
                        field.set(t,"合计");
                    }
                }
                if (indexSet.contains(index)){
                    String type = field.getType().getName();
                    // 进行合并的列一般为数值型，为更具通用性，判断是否是String类型,统一转成BigDecimal
                    BigDecimal total = BigDecimal.ZERO;
                    for (T temp : records){
                        BigDecimal count = new BigDecimal(String.valueOf(field.get(temp)));
                        total = total.add(count);
                    }
                    if (type.contains("String")){
                        // 转成String类型
                        field.set(t,total.toString());
                    }else if (type.contains("Integer")){
                        field.set(t,total.intValue());
                    }else if (type.contains("Double")){
                        field.set(t,total.doubleValue());
                    }
                }
            }
        }
        return t;
    }
}

4.设置单元格样式的拦截器

@Slf4j
public class TotalRowHandler implements CellWriteHandler {
    /**
     * 开始添加样式的行下标
     */
    private Integer startRow;

    public TotalRowHandler(Integer startRow) {
        this.startRow = startRow;
    }

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head,
                                 Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {

    }

    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head,
                                Integer relativeRowIndex, Boolean isHead) {

    }

    @Override
    public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData,
                                       Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {

    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList,
                                 Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        // 这里根据需要自行选择要设置的样式
        Workbook workbook = writeSheetHolder.getSheet().getWorkbook();
        if (cell != null) {
            // 非表头设置样式
            if (!isHead){
                // 设置内容字体样式
                WriteCellStyle contentStyle = new WriteCellStyle();
                WriteFont contentWriteFont = new WriteFont();
                contentWriteFont.setFontName("宋体");
                contentWriteFont.setFontHeightInPoints((short) 12);
                // 设置水平居中、垂直居中
               contentStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
                contentStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                contentStyle.setWriteFont(contentWriteFont);
                // 样式写入单元格
                CellStyle contentCellStyle = StyleUtil.buildContentCellStyle(workbook, contentStyle);
                cell.setCellStyle(contentCellStyle);
            }else {
                // 设置表头字体样式
                WriteCellStyle headStyle = new WriteCellStyle();
                WriteFont headWriteFont = new WriteFont();
                headWriteFont.setFontName("宋体");
                headWriteFont.setFontHeightInPoints((short) 5);
                headStyle.setWriteFont(headWriteFont);
                // 样式写入单元格
                CellStyle headCellStyle = StyleUtil.buildHeadCellStyle(workbook, headStyle);
                cell.setCellStyle(headCellStyle);
            }
        }
    }
}