需求

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

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

@AllArgsConstructor
@Getter
public enum HouseTypeEnum {

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

    private final String desc;

}
复制代码

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

选型

​ EasyExcel是阿里巴巴开源的一款基于POI的excel解析工具,相比POI在内存占用上优化巨大,并且不会出现内存溢出问题.此外,基于Class的表头模型,使用起来相比POI方便许多,能减少很多开发量,就他了.

第一版

​ 按照需求,我们先做一版最简单的.

1. 创建表头模型

   @Getter
   @Setter
   @HeadStyle(horizontalAlignment = HorizontalAlignment.CENTER)
   @ContentStyle(horizontalAlignment = HorizontalAlignment.CENTER)
   public class HouseExcelModel {
   
       /**
        * 房间id
        */
       @ExcelProperty("id")
       private Integer id;
   
       /**
        * 房间名称
        */
       @ExcelProperty("房间名称")
       @ColumnWidth(30)
       private String houseName;
   
       /**
        * 面积
        */
       @ExcelProperty("面积")
       private BigDecimal area;
   
       /**
        * 单价
        */
       @ExcelProperty("单价(元/平方米)")
       private BigDecimal unitPrice;
   
       /**
        * 总价
        */
       @ExcelProperty("总价(元)")
       private BigDecimal totalPrice;
   
       /**
        * 业态
        *
        * @see com.example.easyexcel.constant.HouseTypeEnum
        */
       @ExcelProperty("业态")
       @ColumnWidth(15)
       private String type;
   
   }
   复制代码

2. 编写导出代码,这个相比POI就简单很多了

	@SneakyThrows
    public String export2Excel(){
        String filename = "房间列表";
        String extName = ".xlsx";
        File tempFile = File.createTempFile(filename, extName);
        log.info("temp file path: {}",tempFile.getAbsolutePath());

        List<HouseExcelModel> houseExcelModelList = queryHouseList()
                .stream()
                .map(house -> {
                    HouseExcelModel houseExcelModel = new HouseExcelModel();
                    BeanUtil.copyProperties(house, houseExcelModel);
                    return houseExcelModel;
                })
                .collect(Collectors.toList());

        //写入excel
        EasyExcel.write(tempFile)
                .head(HouseExcelModel.class)
                .sheet("房间列表")
                .doWrite(houseExcelModelList);

        //上传到oss,返回url给前端
        return fileService.upload(tempFile, filename+extName);
    }
复制代码

看一下导出的效果

稍微调整下列宽和居中后,EasyExcel自带的样式已经看着很不错了,但还有几个问题:

1. id只是为了导入时能找到对应数据,不需要展示出来
2. 业态没有转换为对应的中文,且应该有下拉选择
3. 房间名称、面积应该不允许编辑

下面我们来解决这些问题

隐藏列

​ @ContentStyle注解上有一个参数hidden,但这个参数目前并没有什么用,要隐藏某列,我们需要将该列的宽度设置为0,我们试一下

    /**
     * 房间id
     */
    @ExcelProperty("id")
    @ColumnWidth(0)
    private Integer id;
复制代码

看看效果,可以看到id已经被隐藏了

保护工作表

​ 保护工作表需要注解和拦截器配合,首先使用@ContentStyle的locked参数加锁,然后使用拦截器启用保护工作表。

@Getter
@Setter
@HeadStyle(horizontalAlignment = HorizontalAlignment.CENTER, locked = true)
@ContentStyle(horizontalAlignment = HorizontalAlignment.CENTER,locked = true)
public class HouseExcelModel {

    /**
     * 房间id
     */
    @ExcelProperty("id")
    @ColumnWidth(0)
    private Integer id;

    /**
     * 房间名称
     */
    @ExcelProperty("房间名称")
    @ColumnWidth(30)
    private String houseName;

    /**
     * 面积
     */
    @ExcelProperty("面积")
    private BigDecimal area;

    /**
     * 单价
     */
    @ExcelProperty("单价(元/平方米)")
    @ContentStyle(horizontalAlignment = HorizontalAlignment.CENTER,locked = false)
    private BigDecimal unitPrice;

    /**
     * 总价
     */
    @ExcelProperty("总价(元)")
    @ContentStyle(horizontalAlignment = HorizontalAlignment.CENTER,locked = false)
    private BigDecimal totalPrice;

    /**
     * 业态
     *
     * @see com.example.easyexcel.constant.HouseTypeEnum
     */
    @ExcelProperty("业态")
    @ColumnWidth(15)
    private String type;

}

@SneakyThrows
public String export2Excel() {
    String filename = "房间列表";
    String extName = ".xlsx";
    File tempFile = File.createTempFile(filename, extName);
    log.info("temp file path: {}", tempFile.getAbsolutePath());

    List<HouseExcelModel> houseExcelModelList = queryHouseList()
        .stream()
        .map(house -> {
            HouseExcelModel houseExcelModel = new HouseExcelModel();
            BeanUtil.copyProperties(house, houseExcelModel);
            return houseExcelModel;
        })
        .collect(Collectors.toList());

    //写入excel
    EasyExcel.write(tempFile)
        .head(HouseExcelModel.class)
        .sheet("房间列表")
        .registerWriteHandler(
        new AbstractSheetWriteHandler() {
            @Override
            public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
                Sheet sheet = writeSheetHolder.getSheet();
                sheet.protectSheet(UUID.fastUUID().toString(true));
                if (sheet instanceof XSSFSheet) {
                    ((XSSFSheet) sheet).enableLocking();
                } else if (sheet instanceof SXSSFSheet) {
                    ((SXSSFSheet) sheet).enableLocking();
                }
            }
        }
    )
        .doWrite(houseExcelModelList);

    //上传到oss,返回url给前端
    return fileService.upload(tempFile, filename + extName);
}
复制代码

​ 这里我在类上统一加了锁定的参数,然后在需要可编辑的字段上加了locked=false,再来看下效果:

枚举

​ 之前的代码里，我使用了AbstractSheetWriteHandler拦截器,作用是在写入工作表的过程中进行一些额外的操作,EasyExcel提供了很多类似的拦截器:

• WorkbookWriteHandler: 在写入工作簿前后提供额外处理

• SheetWriteHandler: 在写入工作表前后提供额外处理

• RowWriteHandler: 在写入行的过程中提供额外处理

• CellWriteHandler: 在写入单元格过程中提供额外处理

  一般情况下,EasyExcel会对字段自动使用合适的转换器,但我们也可以自己制定自定义的转换器,要将枚举值转换为对应的中文描述,就可以使用自定义转换器的方式来在写入单元格时做转换.

  首先编写转换器逻辑:

/**
 * 需要转换的枚举类需要继承此接口
 * @author 92339
 */
public interface IExcelEnum<T> {

    T getCode();

    String getStringValue();

}
/**
 * 枚举值转换器
 * @author 92339
 */
public abstract class AbstractEnum2StringConverter<T, E extends IExcelEnum<T>> implements Converter<T> {

    private final Class<T> typeClass;
    private final BiMap<T, String> enumMap = HashBiMap.create();

    @SuppressWarnings({"unchecked"})
    public AbstractEnum2StringConverter() {
        Class<IExcelEnum<T>> enumClass = (Class<IExcelEnum<T>>) TypeUtil.getTypeArgument(getClass(), 1);
        if (!enumClass.isEnum()) {
            throw new IllegalArgumentException("ParameterizedType[1] must be enum");
        }
        this.typeClass = (Class<T>) TypeUtil.getTypeArgument(getClass(), 0);
        initEnumMap(enumClass);
    }

    private void initEnumMap(Class<IExcelEnum<T>> enumClass) {
        for (IExcelEnum<T> enumConstant : enumClass.getEnumConstants()) {
            this.enumMap.put(enumConstant.getCode(), enumConstant.getStringValue());
        }
    }

    @Override
    public Class<?> supportJavaTypeKey() {
        return typeClass;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }

    @Override
    public T convertToJavaData(CellData cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {
        String stringValue = cellData.getStringValue();
        if (stringValue == null) {
            return null;
        }
        T t = enumMap.inverse().get(stringValue);
        if (t == null) {
            throw new IllegalArgumentException(String.format("invalid value in cell: %s, row: %d",
                    contentProperty.getHead().getFieldName(), cellData.getRowIndex()));
        }
        return t;
    }

    @Override
    public CellData<String> convertToExcelData(T value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) {

        String stringValue = enumMap.get(value);
        if (stringValue == null) {
            throw new IllegalArgumentException(String.format("invalid value in model, fieldName: %s",
                    contentProperty.getHead().getFieldName()));
        }
        return new CellData<>(stringValue);
    }
}
复制代码

​ 然后改造原有的枚举类和Head模型

@AllArgsConstructor
@Getter
public enum HouseTypeEnum implements IExcelEnum<String> {

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

    private final String stringValue;

    /**
     * 声明class即可,不需要有具体实现
     */
    static class HouseTypeEnum2StringConverter extends AbstractEnum2StringConverter<String,HouseTypeEnum>{}

}
@Getter
@Setter
@HeadStyle(horizontalAlignment = HorizontalAlignment.CENTER, locked = true)
@ContentStyle(horizontalAlignment = HorizontalAlignment.CENTER,locked = true)
public class HouseExcelModel {

    /**
     * 房间id
     */
    @ExcelProperty("id")
    @ColumnWidth(0)
    private Integer id;

    /**
     * 房间名称
     */
    @ExcelProperty("房间名称")
    @ColumnWidth(30)
    private String houseName;

    /**
     * 面积
     */
    @ExcelProperty("面积")
    private BigDecimal area;

    /**
     * 单价
     */
    @ExcelProperty("单价(元/平方米)")
    @ContentStyle(horizontalAlignment = HorizontalAlignment.CENTER,locked = false)
    private BigDecimal unitPrice;

    /**
     * 总价
     */
    @ExcelProperty("总价(元)")
    @ContentStyle(horizontalAlignment = HorizontalAlignment.CENTER,locked = false)
    private BigDecimal totalPrice;

    /**
     * 业态
     *
     * @see com.example.easyexcel.constant.HouseTypeEnum
     */
    @ExcelProperty(value = "业态",converter = HouseTypeEnum.HouseTypeEnum2StringConverter.class)
    @ContentStyle(horizontalAlignment = HorizontalAlignment.CENTER,locked = false)
    @ColumnWidth(15)
    private String type;

}
复制代码

再导出一次,可以看到业态已经转换为中文了.

下一步是加上下拉校验,这里需要使用注解+CellWriteHandler来处理。

/**
 * Excel枚举字段需要使用此注解声明
 *
 * @author jingwen
 */
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
@Inherited
@Documented
public @interface ExcelEnum {

    Class<? extends IExcelEnum<?>> value();

}
public class EnumConstraintSheetWriteHandler extends AbstractSheetWriteHandler {

    private final int dataSize;

    public EnumConstraintSheetWriteHandler(int dataSize) {
        this.dataSize = dataSize;
    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        Sheet sheet = writeSheetHolder.getSheet();
        DataValidationHelper helper = sheet.getDataValidationHelper();
        for (Map.Entry<Integer, ExcelContentProperty> entry : writeSheetHolder.getExcelWriteHeadProperty().getContentPropertyMap().entrySet()) {
            int index = entry.getKey();
            ExcelContentProperty excelContentProperty = entry.getValue();
            ExcelEnum excelEnum = excelContentProperty.getField().getAnnotation(ExcelEnum.class);
            if (excelEnum != null) {
                Class<? extends IExcelEnum<?>> enumClass = excelEnum.value();
                if (!enumClass.isEnum()) {
                    throw new IllegalArgumentException("ExcelEnum's value must be enum class");
                }
                String[] values = Arrays.stream(enumClass.getEnumConstants())
                        .map(IExcelEnum::getStringValue)
                        .toArray(String[]::new);
                DataValidationConstraint constraint = helper.createExplicitListConstraint(values);
                CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(1, dataSize + 1, index, index);
                DataValidation validation = helper.createValidation(constraint, cellRangeAddressList);
                //设置枚举列,提供下拉框,防止误操作
                sheet.addValidationData(validation);
            }
        }
    }
}
复制代码

再在导出的逻辑中加上对应的拦截器。

//写入excel
EasyExcel.write(tempFile)
    .head(HouseExcelModel.class)
    .sheet("房间列表")
    .registerWriteHandler(
    new AbstractSheetWriteHandler() {
        @Override
        public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
            Sheet sheet = writeSheetHolder.getSheet();
            sheet.protectSheet(UUID.fastUUID().toString(true));
            if (sheet instanceof XSSFSheet) {
                ((XSSFSheet) sheet).enableLocking();
            } else if (sheet instanceof SXSSFSheet) {
                ((SXSSFSheet) sheet).enableLocking();
            }
        }
    }
)
    .registerWriteHandler(new EnumConstraintSheetWriteHandler(houseExcelModelList.size()))
    .doWrite(houseExcelModelList);
复制代码

表头模型类增加@ExcelEnum注解

@Getter
@Setter
@HeadStyle(horizontalAlignment = HorizontalAlignment.CENTER, locked = true)
@ContentStyle(horizontalAlignment = HorizontalAlignment.CENTER,locked = true)
public class HouseExcelModel {

    /**
     * 房间id
     */
    @ExcelProperty("id")
    @ColumnWidth(0)
    private Integer id;

    /**
     * 房间名称
     */
    @ExcelProperty("房间名称")
    @ColumnWidth(30)
    private String houseName;

    /**
     * 面积
     */
    @ExcelProperty("面积")
    private BigDecimal area;

    /**
     * 单价
     */
    @ExcelProperty("单价(元/平方米)")
    @ContentStyle(horizontalAlignment = HorizontalAlignment.CENTER,locked = false)
    private BigDecimal unitPrice;

    /**
     * 总价
     */
    @ExcelProperty("总价(元)")
    @ContentStyle(horizontalAlignment = HorizontalAlignment.CENTER,locked = false)
    private BigDecimal totalPrice;

    /**
     * 业态
     *
     * @see com.example.easyexcel.constant.HouseTypeEnum
     */
    @ExcelProperty(value = "业态",converter = HouseTypeEnum.HouseTypeEnum2StringConverter.class)
    @ContentStyle(horizontalAlignment = HorizontalAlignment.CENTER,locked = false)
    @ColumnWidth(15)
    @ExcelEnum(HouseTypeEnum.class)
    private String type;

}
复制代码

再导出一次看看,可以看到下拉框了