接上一篇Easyexcel导出excel实战。

重复一遍需求:

假设有一批商品房,我们需要将其导出到excel上,批量维护价格和业态,再导入回系统.

1. 其中,业态为枚举值,具体取值如下:

@AllArgsConstructor
@Getter
public enum HouseTypeEnum {

    RESIDENTIAL("residential", "住宅"),
    PARKING("parking", "车位"),
    SHOP("shop","商铺"),
    ;
    private final String code;

    private final String desc;

}
复制代码

2. 要求输入总价后,根据面积计算出单价;输入单价后,根据面积计算出总价. 总价=单价*面积

上一篇文章中,我们已经实现了大部分需求,还剩下最后一个小尾巴:根据单价自动计算出总价,或根据总价自动计算出单价。

公式

​ 在excel中要实现这个功能,不用想肯定是用公式了,我们还是可以用注解+Handler的方式来解决这个问题

​ 首先定义一个公式枚举类,用来携带相关信息

@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
@Inherited
@Documented
public @interface ExcelFormula {

    /**
     * 对应公式模板
     * 可以使用预制参数如${RowNo}表示当前行号,预制函数如${GetCellAddress("id",${RowNo})}获取单元格地址,可用的参数有
     * RowNo 当前行号
     */
    String value();

}
复制代码

​ 这里我对公式模版做了一些格式上的定义,增加了两个预制的占位符

1. 使用$env的格式定义环境变量,如${rowNo}标识当前行号

2. 使用${funcName(*args)}的格式来定义方法调用(注意,这里不是excel的函数,而是我自己定义的额外的方法),如${getCellAddress("<fieldName>",<rowNo>)}获取对应字段的单元格地址

   接下来是解析公式注解的WriteHandler:

   /**
    * 前提条件:
    * 1. class模式
    *
    * @author jingwen
    */
   @Slf4j
   public class FormulaCellWriteHandler extends AbstractCellWriteHandler implements SheetWriteHandler {
   
       private static final String ENV_ROW_NO = "rowNo";
       private static final Pattern PATTERN_ENV = Pattern.compile("\\$\\{([a-zA-Z]\\w+)}");
       private static final Pattern PATTERN_FUNC = Pattern.compile("\\$\\{([a-zA-Z]\\w+)\\((((('[a-zA-Z]\\w+')|\\d+),?)*)\\)}");
       private static final Pattern PATTERN_ARGS = Pattern.compile("('([a-zA-Z]\\w*)')|(\\d+)");
   
       /**
        * easyExcel的元数据
        */
       private Map<String, ExcelContentProperty> contentPropertyMap = null;
       /**
        * 公式映射
        * 因为不会进行删除操作,且即使更新也是用同样的数据覆盖,故可以用HashMap
        */
       private final Map<String, ExcelFormula> formulaMap = new HashMap<>();
   
       private final Function<String[], String> getCellAddressFunc = (args) -> getCellAddress(args[0], Integer.parseInt(args[1]));
   
       private final Map<String, Function<String[], String>> functionMap = ImmutableMap.of(
               "getCellAddress", getCellAddressFunc
       );
   
       @Override
       public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
           //跳过头信息,如果不是class模式,也跳过
           if (!isHead && head != null && head.getFieldName() != null) {
               ExcelFormula excelFormula;
               if (formulaMap.containsKey(head.getFieldName())) {
                   excelFormula = formulaMap.get(head.getFieldName());
               } else {
                   ExcelContentProperty excelContentProperty = contentPropertyMap.get(head.getFieldName());
                   excelFormula = excelContentProperty.getField().getAnnotation(ExcelFormula.class);
                   //为空也put一下,这样下次containsKey会返回true
                   formulaMap.put(head.getFieldName(), excelFormula);
               }
               if (excelFormula == null) {
                   return;
               }
               String formulaTemplate = excelFormula.value();
               //解析公式和环境变量
               String formula= parseFunctionAndExecute(formulaTemplate, cell);
               cell.setCellFormula(formula);
           }
       }
   
   
       private String parseFunctionAndExecute(String formulaTemplate, Cell cell) {
   
           //先解析环境变量
           formulaTemplate = setEnvProperties(formulaTemplate, cell);
           log.info("=============开始编译公式模板: {}=============", formulaTemplate);
           Matcher matcher = PATTERN_FUNC.matcher(formulaTemplate);
           StringBuilder stringBuffer = new StringBuilder();
           while (matcher.find()) {
               String methodName = matcher.group(1);
               log.info("-----------methodName:{}-----------", methodName);
               Matcher argsMatcher = PATTERN_ARGS.matcher(matcher.group(2));
               //一般不会有这么多参数了
               List<String> args = new ArrayList<>(5);
               while (argsMatcher.find()) {
                   //可能是字符串或者数字
                   String strArgument = argsMatcher.group(2);
                   String digitArgument = argsMatcher.group(3);
                   String actualArgument = strArgument != null ? strArgument : digitArgument;
                   log.info("arg[{}]: {}", args.size(), actualArgument);
                   args.add(actualArgument);
               }
               log.info("-----------method解析完毕-----------");
               Function<String[], String> function = functionMap.get(methodName);
               if (function == null) {
                   throw new IllegalArgumentException("找不到对应的方法:" + methodName);
               }
               String result = function.apply(args.toArray(new String[0]));
               log.info("execute method, result: {}", result);
               matcher.appendReplacement(stringBuffer, result);
           }
           matcher.appendTail(stringBuffer);
           String formula = stringBuffer.toString();
           log.info("=============公式模板解析完毕: {}=============", formula);
           return formula;
       }
   
       private String setEnvProperties(String formulaTemplate, Cell cell) {
   
           CellAddress cellAddress = cell.getAddress();
           int currentRow = cellAddress.getRow() + 1;
           Matcher matcher = PATTERN_ENV.matcher(formulaTemplate);
           StringBuilder stringBuffer = new StringBuilder();
           while (matcher.find()) {
               String envName = matcher.group(1);
               if (ENV_ROW_NO.equals(envName)) {
                   matcher.appendReplacement(stringBuffer, String.valueOf(currentRow));
               } else {
                   throw new IllegalArgumentException("无法识别的变量");
               }
           }
           matcher.appendTail(stringBuffer);
           return stringBuffer.toString();
       }
   
   
       public String getCellAddress(String fieldName, int rowIndex) {
           ExcelContentProperty excelContentProperty = contentPropertyMap.get(fieldName);
           if (excelContentProperty == null) {
               throw new IllegalArgumentException("无效的字段名:" + fieldName);
           }
           int columnIndex = excelContentProperty.getHead().getColumnIndex();
           String columnStr = CellReference.convertNumToColString(columnIndex);
           return columnStr + rowIndex;
       }
   
   
       @Override
       public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
           if (writeSheetHolder.getClazz() == null) {
               throw new UnsupportedOperationException("只支持class模式写入");
           }
           initHeadMap(writeSheetHolder.getExcelWriteHeadProperty().getContentPropertyMap());
       }
   
       @Override
       public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
   
       }
   
       /**
        * 保存EasyExcel的模型数据
        * @param excelContentPropertyMap 
        */
       private synchronized void initHeadMap(Map<Integer, ExcelContentProperty> excelContentPropertyMap) {
           if (this.contentPropertyMap == null) {
               this.contentPropertyMap = excelContentPropertyMap.values()
                       .stream()
                       .collect(Collectors.toMap(
                               e -> e.getHead().getFieldName(),
                               Function.identity()
                       ));
           }
       }
   }
   复制代码

   ​ 这里我做了几件事:

   1. 定义了获取单元格地址的方法,同时将其封装成Function<String[],String>的形式,注册到方法列表中,方便调用
   2. 在创建工作表时,将EasyExcel的模型信息保存起来,方便后续使用
   3. 使用Map缓存公式注解信息,以免重复调用反射来获取,这里我使用的是HashMap,能够将null值也缓存起来,避免击穿
   4. 使用正则表达式来解析之前定义的公式模板中的环境变量和方法

环境变量

​ 对于${env}形式的环境变量,我们使用正则\$\{([a-zA-Z]\w+)}来解析,可以看到只有一个捕获组,比较简单

方法

​ 方法的解析我将其分为两步

1. 解析方法主体,获取方法名称及参数块,使用正则\$\{([a-zA-Z]\w+)\((((('[a-zA-Z]\w+')|\d+),?)*)\)},这个比较复杂,主要是因为需要同时匹配正确格式的参数块

2. 由参数块解析每一个参数,使用正则('([a-zA-Z]\w*)')|(\d+)，捕获组如图

接下来试试看效果吧,修改模型类,增加公式注解:

    /**
     * 总价
     */
    @ExcelProperty("总价(元)")
    @ContentStyle(horizontalAlignment = HorizontalAlignment.CENTER,locked = false)
    @ExcelFormula("=${getCellAddress('unitPrice',${rowNo})} * ${getCellAddress('area',${rowNo})}")
    private BigDecimal totalPrice;
复制代码

注册FormulaCellWriteHandler,然后导出,可以看到日志输出了解析过程。

=============开始编译公式模板: =${getCellAddress('unitPrice',2)} * ${getCellAddress('area',2)}=============
-----------methodName:getCellAddress-----------
arg[0]: unitPrice
arg[1]: 2
-----------method解析完毕-----------
execute method, result: D2
-----------methodName:getCellAddress-----------
arg[0]: area
arg[1]: 2
-----------method解析完毕-----------
execute method, result: C2
=============公式模板解析完毕: =D2 * C2=============
复制代码

再来看看导出的excel

可以看到有公式了!

简化版公式

​ 实际上,对于文中的需求,我们需要的只是获取同一行中其他单元格的值,并进行计算,并不需要用到excel自带的函数等,所以我们可以简化一下这个公式模板,直接使用<fieldName> <运算法> <fieldName>这样的格式,下面对代码改造一下.

1. 调整公式注解,增加简化版公式的开关

   @Target(ElementType.FIELD)
   @Retention(RetentionPolicy.RUNTIME)
   @Inherited
   @Documented
   public @interface ExcelFormula {
   
       /**
        * 对应公式模板
        * 可以使用预制参数如${RowNo}表示当前行号,预制函数如${GetCellAddress("id",${RowNo})}获取单元格地址,可用的参数有
        * RowNo 当前行号
        */
       String value();
   
       FormulaType type() default FormulaType.SIMPLE;
   
   
       enum FormulaType {
           /**
            * 简单模式,不支持调用excel函数,只支持加减乘除括号
            * 但可以直接使用变量名来指向该行的某个字段
            */
           SIMPLE,
           /**
            * 复杂模式,支持excel函数调用,支持引入自定义的环境变量
            */
           COMPLEX,
       }
   
   }
   复制代码

   2. 在FormulaCellWriteHandler中增加对简化版公式的支持

   @Slf4j
   public class FormulaCellWriteHandler extends AbstractCellWriteHandler implements SheetWriteHandler {
   
       private static final String ENV_ROW_NO = "rowNo";
       private static final Pattern PATTERN_ENV = Pattern.compile("\\$\\{([a-zA-Z]\\w+)}");
       private static final Pattern PATTERN_FUNC_COMPLEX = Pattern.compile("\\$\\{([a-zA-Z]\\w+)\\((((('[a-zA-Z]\\w+')|\\d+),?)*)\\)}");
       private static final Pattern PATTERN_FUNC_SIMPLE = Pattern.compile("([a-zA-Z]\\w*)");
       private static final Pattern PATTERN_ARGS = Pattern.compile("('([a-zA-Z]\\w*)')|(\\d+)");
   
       /**
        * easyExcel的元数据
        */
       private Map<String, ExcelContentProperty> contentPropertyMap = null;
       /**
        * 公式映射
        * 因为不会进行删除操作,且即使更新也是用同样的数据覆盖,故可以用HashMap
        */
       private final Map<String, ExcelFormula> formulaMap = new HashMap<>();
   
       private final Function<String[], String> getCellAddressFunc = (args) -> getCellAddress(args[0], Integer.parseInt(args[1]));
   
       private final Map<String, Function<String[], String>> functionMap = ImmutableMap.of(
               "getCellAddress", getCellAddressFunc
       );
   
       @Override
       public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
           //跳过头信息,如果不是class模式,也跳过
           if (!isHead && head != null && head.getFieldName() != null) {
               ExcelFormula excelFormula;
               if (formulaMap.containsKey(head.getFieldName())) {
                   excelFormula = formulaMap.get(head.getFieldName());
               } else {
                   ExcelContentProperty excelContentProperty = contentPropertyMap.get(head.getFieldName());
                   excelFormula = excelContentProperty.getField().getAnnotation(ExcelFormula.class);
                   //为空也put一下,这样下次containsKey会返回true
                   formulaMap.put(head.getFieldName(), excelFormula);
               }
               if (excelFormula == null) {
                   return;
               }
               String formulaTemplate = excelFormula.value();
               //替换环境变量
               String formula;
               if (excelFormula.type() == ExcelFormula.FormulaType.COMPLEX) {
                   formula = parseComplexFunctionAndExecute(formulaTemplate, cell);
               } else {
                   formula = parseSimpleFunctionAndExecute(formulaTemplate, cell);
               }
               cell.setCellFormula(formula);
           }
       }
   
       private String parseSimpleFunctionAndExecute(String formulaTemplate, Cell cell) {
           log.info("=============开始编译公式模板: {}=============", formulaTemplate);
           StringBuilder stringBuffer = new StringBuilder();
           Matcher matcher = PATTERN_FUNC_SIMPLE.matcher(formulaTemplate);
           int currentRow = cell.getAddress().getRow() + 1;
           while (matcher.find()) {
               String fieldName = matcher.group(1);
               String fieldCellAddress = getCellAddress(fieldName, currentRow);
               log.info("-----------fieldName:{},fieldCellAddress:{}-----------", fieldName, fieldCellAddress);
               matcher.appendReplacement(stringBuffer, fieldCellAddress);
           }
           matcher.appendTail(stringBuffer);
           String formula = stringBuffer.toString();
           log.info("=============公式模板解析完毕: {}=============", formula);
           return formula;
       }
   
   
       private String parseComplexFunctionAndExecute(String formulaTemplate, Cell cell) {
   
           formulaTemplate = setEnvProperties(formulaTemplate, cell);
           log.info("=============开始编译公式模板: {}=============", formulaTemplate);
           Matcher matcher = PATTERN_FUNC_COMPLEX.matcher(formulaTemplate);
           StringBuilder stringBuffer = new StringBuilder();
           while (matcher.find()) {
               String methodName = matcher.group(1);
               log.info("-----------methodName:{}-----------", methodName);
               Matcher argsMatcher = PATTERN_ARGS.matcher(matcher.group(2));
               //一般不会有这么多参数了
               List<String> args = new ArrayList<>(5);
               while (argsMatcher.find()) {
                   //可能是字符串或者数字
                   String strArgument = argsMatcher.group(2);
                   String digitArgument = argsMatcher.group(3);
                   String actualArgument = strArgument != null ? strArgument : digitArgument;
                   log.info("arg[{}]: {}", args.size(), actualArgument);
                   args.add(actualArgument);
               }
               log.info("-----------method解析完毕-----------");
               Function<String[], String> function = functionMap.get(methodName);
               if (function == null) {
                   throw new IllegalArgumentException("找不到对应的方法:" + methodName);
               }
               String result = function.apply(args.toArray(new String[0]));
               log.info("execute method, result: {}", result);
               matcher.appendReplacement(stringBuffer, result);
           }
           matcher.appendTail(stringBuffer);
           String formula = stringBuffer.toString();
           log.info("=============公式模板解析完毕: {}=============", formula);
           return formula;
       }
   
       private String setEnvProperties(String formulaTemplate, Cell cell) {
   
           CellAddress cellAddress = cell.getAddress();
           int currentRow = cellAddress.getRow() + 1;
           Matcher matcher = PATTERN_ENV.matcher(formulaTemplate);
           StringBuilder stringBuffer = new StringBuilder();
           while (matcher.find()) {
               String envName = matcher.group(1);
               if (ENV_ROW_NO.equals(envName)) {
                   matcher.appendReplacement(stringBuffer, String.valueOf(currentRow));
               } else {
                   throw new IllegalArgumentException("无法识别的变量");
               }
           }
           matcher.appendTail(stringBuffer);
           return stringBuffer.toString();
       }
   
   
       public String getCellAddress(String fieldName, int rowIndex) {
           ExcelContentProperty excelContentProperty = contentPropertyMap.get(fieldName);
           if (excelContentProperty == null) {
               throw new IllegalArgumentException("无效的字段名:" + fieldName);
           }
           int columnIndex = excelContentProperty.getHead().getColumnIndex();
           String columnStr = CellReference.convertNumToColString(columnIndex);
           return columnStr + rowIndex;
       }
   
   
       @Override
       public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
           if (writeSheetHolder.getClazz() == null) {
               throw new UnsupportedOperationException("只支持class模式写入");
           }
           initHeadMap(writeSheetHolder.getExcelWriteHeadProperty().getContentPropertyMap());
       }
   
       @Override
       public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
   
       }
   
       private synchronized void initHeadMap(Map<Integer, ExcelContentProperty> excelContentPropertyMap) {
           if (this.contentPropertyMap == null) {
               this.contentPropertyMap = excelContentPropertyMap.values()
                       .stream()
                       .collect(Collectors.toMap(
                               e -> e.getHead().getFieldName(),
                               e -> e
                       ));
           }
       }
   }
   复制代码

   这里我增加了判断,如果公式类型为简化版,则使用简化版的正则进行解析,简化版的正则为:([a-zA-Z]\w*),真的非常简单= =

   3. 修改模型类使用的公式模板

       /**
        * 总价
        */
       @ExcelProperty("总价(元)")
       @ContentStyle(horizontalAlignment = HorizontalAlignment.CENTER,locked = false)
       @ExcelFormula("=unitPrice*area")
       private BigDecimal totalPrice;
   复制代码

   导出一下,可以看到日志输出解析过程:

   =============开始编译公式模板: =unitPrice*area=============
   -----------fieldName:unitPrice,fieldCellAddress:D2-----------
   -----------fieldName:area,fieldCellAddress:C2-----------
   =============公式模板解析完毕: =D2*C2=============
   复制代码

   再看导出效果:

跟之前的效果是一样的.
复制代码

结语

​ 本篇EasyExcel导出excel到这里就结束了,这里我没有设计到导入,是因为目前遇到的导入场景都比较简单,官网上的demo就可以解决问题,就不搬运代码了.其实如果只是为了完成本文中的需求,也没必要搞这么多复杂的东西,但如果我们写的通用些,下次遇到类似的需求,就可以省下来一些时间摸鱼了不是。

​ 后面列举了一些我在使用过程中遇到的坑,希望能对你有所帮助.

1. 隐藏列,这个在文章中有提到过,@ExcelProperty中的hidden参数并不能隐藏列,要隐藏某列,需要用@ColumnWidth(0)将该列的宽度设置为0
2. EasyExcel解析模型类时会解析父类和子类的同名字段,所以要求父类和子类不能存在标注了@ExcelProperty的同名字段
3. @ContentStyle和@HeadStyle等注解可以使用在类上或字段上,但注意,类上的和字段上的注解参数并不会合并,而是优先取字段上,字段没有则取类上的.