Java使用EasyExcel动态添加自增序号列

目录

• 前言
  
• 实现
  
• 思路
  
• 其它
  
• 总结
  

/**
 * 自定义 excel 行处理器, 增加序号列
 *
 * @author butterfly
 * @date 2020-09-05
 */
@Component
public class AddNoHandler extends AbstractRowWriteHandler {

 private boolean init = true;

 @Override
 public void beforeRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, 
                                Integer rowIndex, Integer relativeRowIndex, Boolean isHead) {
  if (init) {
   // 修改存储头部及对应字段信息的 map, 将其中的内容均右移一位, 给新增的序列号预留为第一列
   ExcelWriteHeadProperty excelWriteHeadProperty = writeSheetHolder.excelWriteHeadProperty();
   Map headMap = excelWriteHeadProperty.getHeadMap();
   Map cOntentMap= excelWriteHeadProperty.getContentPropertyMap();
   int size = headMap.size();
   for (int current = size; current > 0; current--) {
    int previous = current - 1;
    headMap.put(current, headMap.get(previous));
    contentMap.put(current, contentMap.get(previous));
   }
   // 空出第一列
   headMap.remove(0);
   contentMap.remove(0);
   // 只需要修改一次 map 即可, 故使用 init 变量进行控制
   init = false;
  }
 }

 @Override
 public void afterRowCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,
          Integer relativeRowIndex, Boolean isHead) {
  // 在行创建完成后添加序号列
  Cell cell = row.createCell(0);
  int rowNum = row.getRowNum();
  if (rowNum == 0) {
   cell.setCellValue(ExcelConstant.TITLE);
  } else {
   cell.setCellValue(rowNum);
  }
 }

 @Override
 public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,
        Integer relativeRowIndex, Boolean isHead) {
  if (row.getLastCellNum() > 1) {
   // 将自定义新增的序号列的样式设置与默认的样式一致
   row.getCell(0).setCellStyle(row.getCell(1).getCellStyle());
  }
 }

}

private void addOneRowOfDataToExcel(Object oneRowData, int n, int relativeRowIndex,
                                    Map sortedAllFiledMap) {
    // 行数据为空, 直接返回
    if (OneRowData== null) {
        return;
    }
    // 创建数据行对象, 同时分别在创建行前后调用拦截器
    WriteHandlerUtils.beforeRowCreate(writeContext, n, relativeRowIndex, Boolean.FALSE);
    Row row = WorkBookUtil.createRow(writeContext.writeSheetHolder().getSheet(), n);
    WriteHandlerUtils.afterRowCreate(writeContext, row, relativeRowIndex, Boolean.FALSE);
    // 将实体数据内容填充到行中
    if (oneRowData instanceof List) {
        addBasicTypeToExcel((List) oneRowData, row, relativeRowIndex);
    } else {
        // 下面会继续查看这个方法
        addJavaObjectToExcel(oneRowData, row, relativeRowIndex, sortedAllFiledMap);
    }
    // 行创建完成后, 调用相应拦截器
    WriteHandlerUtils.afterRowDispose(writeContext, row, relativeRowIndex, Boolean.FALSE);
}

private void addJavaObjectToExcel(Object oneRowData, Row row, int relativeRowIndex,
                                  Map sortedAllFiledMap) {
        WriteHolder currentWriteHolder = writeContext.currentWriteHolder();
     // 将自己的实体数据映射到 beanMap
        BeanMap beanMap = BeanMap.create(oneRowData);
        Set beanMapHandledSet = new HashSet();
        int cellIndex = 0;
        // If it"s a class it needs to be cast by type
        if (HeadKindEnum.CLASS.equals(writeContext.currentWriteHolder().excelWriteHeadProperty().getHeadKind())) {
            // 我们修改的就是这里的 headMap 和 contentPropertyMap 内容
            Map headMap = writeContext.currentWriteHolder().excelWriteHeadProperty().getHeadMap();
            Map cOntentPropertyMap=
                writeContext.currentWriteHolder().excelWriteHeadProperty().getContentPropertyMap();
            // 遍历所有的列头, 插入数据
            for (Map.Entry entry : contentPropertyMap.entrySet()) {
                // 获取 cell 的下标, 后续将内容插入指定的列
                cellIndex = entry.getKey();
                ExcelContentProperty excelCOntentProperty= entry.getValue();
                String name = excelContentProperty.getField().getName();
                if (!beanMap.containsKey(name)) {
                    continue;
                }
                // 控制单元格的内容
                Head head = headMap.get(cellIndex);
                WriteHandlerUtils.beforeCellCreate(writeContext, row, head, cellIndex, 
                                                   relativeRowIndex, Boolean.FALSE);
                Cell cell = WorkBookUtil.createCell(row, cellIndex);
                WriteHandlerUtils.afterCellCreate(writeContext, cell, head, relativeRowIndex, Boolean.FALSE);
                Object value = beanMap.get(name);
                CellData cellData = converterAndSet(currentWriteHolder, excelContentProperty.getField().getType(), 
                                                    cell, value, excelContentProperty, head, relativeRowIndex);
                WriteHandlerUtils.afterCellDispose(writeContext, cellData, cell, head, 
                                                   relativeRowIndex, Boolean.FALSE);
                beanMapHandledSet.add(name);
            }
        }
        // 省略了后面无关的内容
    }

/**
 * Excel 下载控制器
 *
 * @author butterfly
 * @date 2021-09-05
 */
@RestController
public class ExcelController {

    /**
     * 添加序号列测试
     *
  * @param response response
     */
    @GetMapping("/col")
    public void col(HttpServletResponse response) {
        try {
            List students = getStudentList();
            EasyExcel.write(response.getOutputStream(), Student.class)
                    .registerWriteHandler(new AddNoHandler())
                    .sheet()
                    .doWrite(students);
        } catch (Exception e) {
            System.out.println(ExcelConstant.DOWNLOAD_FAILED);
        }
    }
    /**
     * 生成学生列表
     *
     * @return 学生列表
     */
    private List getStudentList() {
        return Arrays.asList(
                new Student("2021090101", "张三", 19),
                new Student("2021090102", "李四", 18),
                new Student("2021090103", "王二", 20)
        );
    }
}

    
    
    


    添加序号列测试