easypoi导出扩展

2024-04-29

easypoi 注解导出实践

本章记录使用easypoi导出 excel时解决的问题，全篇以基于 easypoi的注解导出为前提，扩展部分功能以满足业务需要。

1、注解的自定义列导出

实现思路

1、获取导出实体–>

2、遍历实体字段，找到@Excel 注解–>

3、手动 new 一个ExcelExportEntity–>

4、把注解中的参数设置到ExcelExportEntity中–>

5、最后将ExcelExportEntity放入 List<ExcelExportEntity> 中，在导出时传入（第二个字段）：cn.afterturn.easypoi.excel.ExcelExportUtil.exportExcel(exportParams, exportEntityList, dataList);

步骤示例

遍历实体获取@Excel

方法1：getEntityList

作用：设置导出实体、导出的列

入参：传输导出的实体 class、导出字段名（可选，动态字段名时使用）、需导出的字段名

List<ExcelExportEntity> getEntityList(Class exportClass, Map<String, String> nameMap, String columns)

完整代码：

private List<ExcelExportEntity> getEntityList(Class exportClass, Map<String, String> nameMap, String columns) {
    List<ExcelExportEntity> entityList = null;
    StringBuilder sb = null;
    Field[] declaredFields = exportClass.getDeclaredFields();
    // 列下标
    AtomicInteger offset = new AtomicInteger(0);
    for (Field declaredField : declaredFields) {
        String fieldName = declaredField.getName();
        if (sb == null) {
            sb = new StringBuilder();
        }
        sb.setLength(0);
        sb.append(",").append(fieldName).append(",");
        if (columns != null && !columns.contains(sb.toString())) {
            continue;
        }
        if (entityList == null) {
            entityList = new ArrayList<>();
        }
        Excel excelAnnotation = declaredField.getAnnotation(Excel.class);
        // name：列名，fieldName：取值字段名
        String name = (nameMap == null || (name = nameMap.get(fieldName)) == null) ? excelAnnotation.name() : name;
        ExcelExportEntity entity = new ExcelExportEntity(name, fieldName);
        entityList.add(entity);
        // 时间格式：相当于同时设置了exportFormat和importFormat
        entity.setFormat(excelAnnotation.exportFormat());
        // 数字格式化
        entity.setNumFormat(excelAnnotation.numFormat());
        // 列的排序
        entity.setOrderNum(Integer.parseInt(excelAnnotation.orderNum()));
        // 列宽
        entity.setWidth(excelAnnotation.width());
        // 是否需要合并
        entity.setNeedMerge(excelAnnotation.needMerge());
        // 需要设置单元格格式的列下标，生成时通过setExcelDataFormat设置单元格格式
        if (1 != excelAnnotation.type()) {
            dataFormatMap.put(offset.get(), excelAnnotation.type());
        }
        if (StringUtils.isNotBlank(excelAnnotation.dict())) {
            dataDictMap.put(offset.get(), excelAnnotation.dict());
        }
        // 有需要@Excel其他参数在这里继续添加
        entity.setSuffix(excelAnnotation.suffix());
        offset.getAndIncrement();
    }
    Class superclass = exportClass.getSuperclass();
    if (superclass != null) {
        List<ExcelExportEntity> entityList1 = getEntityList(superclass, nameMap, columns);
        if (entityList1 != null) {
            if (entityList == null) {
                entityList = entityList1;
            } else {
                entityList.addAll(entityList1);
            }
        }
    }
    return entityList;
}

方法 2：设置导出数据源

入参：导出的数据

dataList(Collection list)

完整代码

public ExcelExportUtil dataList(Collection list) {
    Collection dataList = new ArrayList(list.size());
    for (Object data : list) {
        dataList.add(BeanUtil.beanToMap(data));
    }
    this.dataList = dataList;
    return this;
}

导出主方法

调用者：

new ExcelExportUtil().title("批复文件")
    .sheetName("Sheet1")
    .entityList(ApprovalDocumentExportVo.class, nameMap, EasyPoiSheetMergeParam.DES_COLUMN_NAME + searchDto.getColumns())
    .dataList(exportList)
    .export(mergeParamList);

导出关键实现代码：

入参：导出配置（easypoi 原生配置）、exportEntityList（需导出的列，由上一步确定）、dataList（数据本身）

Workbook workbook = cn.afterturn.easypoi.excel.ExcelExportUtil.exportExcel(exportParams, exportEntityList, dataList);

完整示例：

public void export(List<EasyPoiSheetMergeParam> sheetMergeParams) throws IOException {
    Workbook workbook = null;
    ServletOutputStream outputStream = null;
    try {
        // 参数为空抛出异常？
        ExportParams exportParams = new ExportParams(title, sheetName);
        if (styleClass != null) {
            exportParams.setStyle(styleClass);
        }
        workbook = cn.afterturn.easypoi.excel.ExcelExportUtil.exportExcel(exportParams, exportEntityList, dataList);
        HttpService service = HttpServiceHolder.get();
        HttpServletResponse response = service.getResponse();
        outputStream = response.getOutputStream();
        workbook.write(outputStream);
        try {
            workbook.close();
        } catch (Exception e) {
            workbook = null;
            throw e;
        }
        try {
            outputStream.close();
        } catch (Exception e) {
            outputStream = null;
            throw e;
        }
    } catch (Exception e) {
        throw e;
    } finally {
        if (workbook != null) {
            workbook.close();
        }
        if (outputStream != null) {
            outputStream.close();
        }
    }
}

2、自定义合并单元格

实现思路

1、确定哪些列需要合并、确定基准列

2、导出时上下相邻单元格相互匹配，基准列相等则上下合并

3、最后删除基准列

步骤示例

改造注解的用途

由于我们无法自定义@Excel 注解中的参数，所以我们只能选用一些没有用的参数作为我们参数的额外扩展：

suffix：合并规则(参与第几轮的合并，每次合并都以第一列为基准列)
needMerge：是否需要合并

合并单元格配置类

用于定义合并单元格所需的参数，由每个导出模块自己 new

@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder
public class EasyPoiSheetMergeParam {

  /**
   * sheet下标
   */
  private int sheetIndex;

  /**
   * 合并规则(参与第几轮的合并)
   */
  private String suffix;

  /**
   * 偏移量(基准列不参与输出时需要将合并的列进行偏移,有几个基准列就设置几)
   */
  private int offset = 0;

  /**
   * 合并参考列列号，【注意】默认0，要求基准列必须为第一列，否则无法进行合并操作
   */
  private final int desColumnIndex = 0;

  /**
   * 合并单元格列列号数组
   */
  private List<Integer> mergeColumnIndexes;


  /**
   * 基准列名
   */
  public static final String DES_COLUMN_NAME = "id,";

}

调用示例：

合并策略是一个 list，支持多次合并

List<EasyPoiSheetMergeParam> mergeParamList = new ArrayList<>();
mergeParamList.add(new EasyPoiSheetMergeParam().builder().sheetIndex(0).suffix("1").offset(2).build());
mergeParamList.add(new EasyPoiSheetMergeParam().builder().sheetIndex(0).suffix("2").offset(2).build());

导出主方法

导出前先去除 suffix

注意⚠️：导出前必须对ExcelExportEntity中的 suffix 进行清空，否则会suffix 会对导出的数据做 replace 操作。原因：suffix 本身时@Excel 自带的属性，用于对导出数据做处理，我们这里用于其他用途，所以导出时不能传入该参数。

// 导出用的配置项，需要去除suffix
List<ExcelExportEntity> exportEntityList = new ArrayList<>();
entityList.forEach(item -> {
    ExcelExportEntity excelExportEntity = new ExcelExportEntity();
    BeanUtils.copyProperties(item, excelExportEntity, "suffix");
    exportEntityList.add(excelExportEntity);
});

导出

workbook = cn.afterturn.easypoi.excel.ExcelExportUtil.exportExcel(exportParams, exportEntityList, dataList);

导出后进行单元格合并

// 合并单元格
if (CollectionUtils.isNotEmpty(sheetMergeParams)) {
    for (EasyPoiSheetMergeParam sheetMergeParam : sheetMergeParams) {
        // 查询需要合并单元格的列
        List<Integer> mergeColumnIndexList = new ArrayList<>();
        for (int i = 0; i < entityList.size(); i++) {
            ExcelExportEntity excelExportEntity = entityList.get(i);
            if (excelExportEntity.isNeedMerge() && StringUtils.equals(excelExportEntity.getSuffix(), sheetMergeParam.getSuffix())) {
                // 固定第1列为基准列，根据需要进行偏移
                mergeColumnIndexList.add(i - sheetMergeParam.getOffset());
            }
        }
        sheetMergeParam.setMergeColumnIndexes(mergeColumnIndexList);
        // 执行合并方法
        mergeExcel(workbook, sheetMergeParam);
    }
}

mergeExcel方法：

/**
 * 合并列
 *
 * @param workbook        workbook
 * @param sheetMergeParam 合并列参数
 */
private void mergeExcel(Workbook workbook, EasyPoiSheetMergeParam sheetMergeParam){
    Sheet sheet = workbook.getSheetAt(sheetMergeParam.getSheetIndex());
    int lastRowNum = sheet.getLastRowNum() + 1;
    int i;
    for(i = 0;i < lastRowNum;i++){
        //获取每行第一个单元格
        if(null == sheet.getRow(i) || null == sheet.getRow(i).getCell(0)){
            continue;
        }
        /*Cell cell = sheet.getRow(i).getCell(0);
        if(sheetMergeParam.getIgnoreCellValues().contains(cell.getStringCellValue()) || StringUtils.isEmpty(cell.getStringCellValue()) ){
            // 暂不处理忽略合并行
            continue;
        }*/
        //定义合并终止行数
        int endRowNum = 0;
        for(int j = i + 1 ;j < lastRowNum;j++){
            Cell desColumn = sheet.getRow(i).getCell(sheetMergeParam.getDesColumnIndex());
            Cell nextDesColumn = sheet.getRow(j).getCell(sheetMergeParam.getDesColumnIndex());
            if(!desColumn.getStringCellValue().equals(nextDesColumn.getStringCellValue())){
                //值不同，终止此层循环
                break;
            }
            endRowNum ++;
        }

        //判断是否有合并项
        if(endRowNum == 0){
            continue;
        }
        //合并单元格操作
        for(int z = 0; z < sheetMergeParam.getMergeColumnIndexes().size(); z++){
            //合并起始行，终止行，起始列，终止列
            int firstRow = i;
            int lastRow = i + endRowNum;
            int firstCol = sheetMergeParam.getMergeColumnIndexes().get(z);
            int lastCol = sheetMergeParam.getMergeColumnIndexes().get(z);
            PoiMergeCellUtil.addMergedRegion(sheet,firstRow,lastRow,firstCol,lastCol);
        }

        //合并后行号下移
        i = i + endRowNum;
    }
    // 删除基准列
    deleteColumn(sheet, sheetMergeParam.getDesColumnIndex());
    // 回填标题
    if (StringUtils.isNotBlank(title)) {
        Row row = sheet.getRow(0);
        row.getCell(0).setCellValue(title);
    }
}


/**
 * 删除列
 *
 * @param sheet
 * @param columnToDelete 要删除的列号
 */
private void deleteColumn(Sheet sheet, int columnToDelete) {
    for (int r = 0; r <= sheet.getLastRowNum(); r++) {
        Row row = sheet.getRow(r);
        for (int c = columnToDelete; c <= row.getLastCellNum(); c++) {
            Cell cOld = row.getCell(c);
            if (cOld != null) {
                row.removeCell(cOld);
            }
            Cell cNext = row.getCell(c + 1);
            if (cNext != null) {
                Cell cNew = row.createCell(c, cNext.getCellType());
                cloneCell(cNew, cNext);
                if (r == 0) {
                    sheet.setColumnWidth(c, sheet.getColumnWidth(c + 1));
                }
            }
        }
    }
}


/**
 * 右边列左移,样式值设置
 * @param cNew
 * @param cOld
 */
private static void cloneCell(Cell cNew, Cell cOld) {
    cNew.setCellComment(cOld.getCellComment());
    cNew.setCellStyle(cOld.getCellStyle());

    if (CellType.BOOLEAN == cNew.getCellType()) {
        cNew.setCellValue(cOld.getBooleanCellValue());
    } else if (CellType.NUMERIC == cNew.getCellType()) {
        cNew.setCellValue(cOld.getNumericCellValue());
    } else if (CellType.STRING == cNew.getCellType()) {
        cNew.setCellValue(cOld.getStringCellValue());
    } else if (CellType.ERROR == cNew.getCellType()) {
        cNew.setCellValue(cOld.getErrorCellValue());
    } else if (CellType.FORMULA == cNew.getCellType()) {
        cNew.setCellValue(cOld.getCellFormula());
    }
}

3、单元格支持下拉

实现思路

为了满足导出使用了数据字典的字段，我们增加了单元格下拉，可以利用 excel 本身的限制性校验，提高用户数据准确性。

实现思路很简单，使用 poi 本身的 api 进行设置。

步骤示例

@Excel 中设置 dict

由于@Excel 注解的 dict 属性较为鸡肋，因此我们使用了该属性记录字段的字典值。

@ApiModelProperty(value = "项目分类")
@Excel(name = "*项目分类", isColumnHidden = true, orderNum = "30", width = 35, dict = "PROJ_CLASSIFY")
@NotBlank
private String projClassify;

@ApiModelProperty(value = "行业分类")
@Excel(name = "*行业分类", isColumnHidden = true, orderNum = "30", width = 25, dict = "P_PROF_TYPE")
@NotBlank
private String projProfType;

导出前先处理dict

代码中if (StringUtils.isNotBlank(excelAnnotation.dict())) { dataDictMap.put(offset.get(), excelAnnotation.dict()); }使用dataDictMap记录字段名与匹配的字典类型。

private List<ExcelExportEntity> getEntityList(Class exportClass, Map<String, String> nameMap, String columns) {
    List<ExcelExportEntity> entityList = null;
    StringBuilder sb = null;
    Field[] declaredFields = exportClass.getDeclaredFields();
    // 列下标
    AtomicInteger offset = new AtomicInteger(0);
    for (Field declaredField : declaredFields) {
        String fieldName = declaredField.getName();
        if (sb == null) {
            sb = new StringBuilder();
        }
        sb.setLength(0);
        sb.append(",").append(fieldName).append(",");
        if (columns != null && !columns.contains(sb.toString())) {
            continue;
        }
        if (entityList == null) {
            entityList = new ArrayList<>();
        }
        Excel excelAnnotation = declaredField.getAnnotation(Excel.class);
        // name：列名，fieldName：取值字段名
        String name = (nameMap == null || (name = nameMap.get(fieldName)) == null) ? excelAnnotation.name() : name;
        ExcelExportEntity entity = new ExcelExportEntity(name, fieldName);
        entityList.add(entity);
        // 时间格式：相当于同时设置了exportFormat和importFormat
        entity.setFormat(excelAnnotation.exportFormat());
        // 数字格式化
        entity.setNumFormat(excelAnnotation.numFormat());
        // 列的排序
        entity.setOrderNum(Integer.parseInt(excelAnnotation.orderNum()));
        // 列宽
        entity.setWidth(excelAnnotation.width());
        // 是否需要合并
        entity.setNeedMerge(excelAnnotation.needMerge());
        // 需要设置单元格格式的列下标，生成时通过setExcelDataFormat设置单元格格式
        if (1 != excelAnnotation.type()) {
            dataFormatMap.put(offset.get(), excelAnnotation.type());
        }
        if (StringUtils.isNotBlank(excelAnnotation.dict())) {
            dataDictMap.put(offset.get(), excelAnnotation.dict());
        }
        // 有需要@Excel其他参数在这里继续添加
        entity.setSuffix(excelAnnotation.suffix());
        offset.getAndIncrement();
    }
    Class superclass = exportClass.getSuperclass();
    if (superclass != null) {
        List<ExcelExportEntity> entityList1 = getEntityList(superclass, nameMap, columns);
        if (entityList1 != null) {
            if (entityList == null) {
                entityList = entityList1;
            } else {
                entityList.addAll(entityList1);
            }
        }
    }
    return entityList;
}

设置下拉选项

/**
 * firstRow 开始行号 根据此项目，默认为2(下标0开始)
 * lastRow  根据此项目，默认为最大65535
 * firstCol 区域中第一个单元格的列号 (下标0开始)
 * lastCol 区域中最后一个单元格的列号
 * strings 下拉内容
 * */
private void addDropdown(Workbook workbook){
    if (dataDictMap.size() == 0) {
        return;
    }
    //对工作簿中第一个工作表进行操作
    Sheet sheet = workbook.getSheetAt(0);
    for (Map.Entry<Integer, String> dataDictEntry : dataDictMap.entrySet()) {
        Integer col = dataDictEntry.getKey();
        String dict = dataDictEntry.getValue();
        Set<String> nameSet = DictionaryUtils.getNameArray(dict);
        if (nameSet.size() == 0) {
            continue;
        }
        // 创建`CellRangeAddressList`对象，该对象指定下拉列表的适用区域：
        // 该区域从`firstRow`起始行（包含）到第65535行（包含），在`col`列上。（由于我们作用于同一列，所以firstCol=lastCol）
        CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(firstRow, 65535, col, col);
        DataValidationHelper helper = sheet.getDataValidationHelper();
        // 使用传入的字符串数组创建一个显式列表约束，确定下拉列表中的选项
        DataValidationConstraint dvConstraint = helper.createExplicitListConstraint(nameSet.toArray(new String[0]));
        // 把创建的列表约束作用到那些范围内的单元格
        DataValidation validation = helper.createValidation(dvConstraint, cellRangeAddressList);
        // 作用到sheet上
        sheet.addValidationData(validation);
    }
}

导出主方法

在workbook 生成之后调用方法去添加限制性选项

workbook = cn.afterturn.easypoi.excel.ExcelExportUtil.exportExcel(exportParams, exportEntityList, dataList);
// 设置单元格格式
setExcelNumberFormat(workbook);
// 数据字典下拉
addDropdown(workbook);

4、自定义导出金额格式

起因：使用easypoi的注解导出时，我们通过设置type属性改变单元格为数字类型，但只能格式化成BuiltinFormats类中已定义好的格式，无法满足业务需求。

实现思路

解决：通过使用org.apache.poi.ss.util.CellUtil方法设置自定义单元格类型。为了兼容easypoi的原有使用方式，我们扩展了type类型，保留了原本的设置方式，尽最大可能不使用额外的参数，避免混乱。

@Excel(name = "*项目总投资(万元)", isColumnHidden = true, orderNum = "70", width = 25, type = DataFormatTypeConstants.TEN_THOUSAND_YUAN_TYPE)
private String totalMoney;

步骤示例

自定义type类型

可以根据业务，自定义类型，支持各种类型，只需要你想都能设置。

public class DataFormatTypeConstants implements BaseEntityTypeConstants {
       
    // 万元，千分位并保留6位小数
    public static final int TEN_THOUSAND_YUAN_TYPE = 11;
    // 万元，千分位并保留6位小数，带¥
    public static final int TEN_THOUSAND_WITH_YUAN_SYMBOL_TYPE = 12;
    // ...可扩展，在formatMap设置对应的格式
       
       
    // 存储type对应的格式
    static Map<Integer, String> FORMAT_MAP = new HashMap<>();
    // type是否数值类型(导出时用于判断转换)
    static List<Integer> NUMERIC_LIST = new ArrayList<>();
       
    static {
        FORMAT_MAP.put(DOUBLE_TYPE, "#,##0.00");
        FORMAT_MAP.put(TEN_THOUSAND_YUAN_TYPE, "#,##0.000000");
        FORMAT_MAP.put(TEN_THOUSAND_WITH_YUAN_SYMBOL_TYPE, "¥#,##0.000000");
           
   
        NUMERIC_LIST.add(DOUBLE_TYPE);
        NUMERIC_LIST.add(TEN_THOUSAND_YUAN_TYPE);
        NUMERIC_LIST.add(TEN_THOUSAND_WITH_YUAN_SYMBOL_TYPE);
    }
}

在导出方法执行后设置单元格格式

// 导出方法
Workbook workbook = cn.afterturn.easypoi.excel.ExcelExportUtil.exportExcel(exportParams, exportEntityList, dataList);
   
// 设置单元格格式
setExcelNumberFormat(workbook);

设置单元格格式方法

逻辑：先根据type找自定义格式，自定义格式没找到去BuiltinFormats找，同时使用format属性设置格式。既不影响easypoi原生的格式化，又能兼容自定义格式化。

private void setExcelNumberFormat(Workbook workbook) {
  Sheet sheet = workbook.getSheetAt(0);
  int lastRowNum = sheet.getLastRowNum() + 1;
  int i;
  for (i = 0; i < lastRowNum; i++) {
    if (null == sheet.getRow(i) || null == sheet.getRow(i).getCell(0)) {
      continue;
    }
    for (Map.Entry<Integer, Integer> entry : dataFormatMap.entrySet()) {
      Integer cellOffset = entry.getKey();
      Integer type = entry.getValue();
      Cell cell = sheet.getRow(i).getCell(cellOffset);
      String cellValue = cell.getStringCellValue();
   
      if (DataFormatTypeConstants.NUMERIC_LIST.contains(type)) {
        // 数字类型处理
        if (!NumberUtil.isNumber(cellValue)) {
          continue;
        }
        // 内容转数值，必须做这步处理否则数值格式会不生效
        cell.setCellValue(Double.parseDouble(cellValue));
      }
      String format = DataFormatTypeConstants.FORMAT_MAP.get(type);
      if (format == null) {
        continue;
      }
      int builtinFormat = BuiltinFormats.getBuiltinFormat(format);
      //要更改单元格的样式而不影响使用相同样式的其他单元格，
      // 请使用org.apache.poi.ss.util.CellUtil.setCellStyleProperties(Cell, Map)
      Map<String, Object> map = new HashMap<>();
      if (builtinFormat != -1) {
        map.put(CellUtil.DATA_FORMAT, builtinFormat);
      } else {
        // 使用自定义格式
        DataFormat dataFormat = workbook.createDataFormat();
        map.put(CellUtil.DATA_FORMAT, dataFormat.getFormat(format));
      }
      CellUtil.setCellStyleProperties(cell, map);
    }
  }
}

可选字段导出

起因：虽然easypoi提供了isColumnHidden属性，但该属性的作用只能将字段隐藏，其实仍然导出到了excel中，存在数据泄露风险，为此我们修改了导出方法实现了「真正的」可选字段导出。