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

2024-07-16 18:07| 来源: 网络整理| 查看: 265

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

文章目录前言一、前置操作二、使用步骤1.实体类2.导出方法3.进行合计的工具类4.设置单元格样式的拦截器

前言

使用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 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 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 sumCell(Boolean ifSum, Integer sumIndex, HashSet indexSet, List records,Class 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 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); } } } }

【本文地址】

公司简介

联系我们