Easyexcel导出excel实战(1)

本文最后更新于：2023年8月15日 晚上

需求

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

id 业态 房间名称 面积 单价(元/m2) 总价(元)
1 商铺 万科城市之光-一期-一栋-101 50
2 商铺 万科城市之光-一期-一栋-102 50
3 商铺 万科城市之光-一期-一栋-103 50
4 住宅 万科城市之光-一期-一栋-201 24
5 住宅 万科城市之光-一期-一栋-202 35
6 住宅 万科城市之光-一期-一栋-203 31
  1. 其中,业态为枚举值,具体取值如下:
1
2
3
4
5
6
7
8
9
10
11
12
13
@AllArgsConstructor
@Getter
public enum HouseTypeEnum {

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

    private final String desc;

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

选型

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

第一版

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

  1. 创建表头模型

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    @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就简单很多了

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
@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);
   }

看一下导出的效果

image-20210620122216067.png

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

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

下面我们来解决这些问题

隐藏列

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

1
2
3
4
5
6
/**
 * 房间id
 */
@ExcelProperty("id")
@ColumnWidth(0)
private Integer id;

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

image-20210620124733881.png

保护工作表

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
@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,再来看下效果:

image-20210620130434743.png

枚举

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

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

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

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

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

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

    首先编写转换器逻辑:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
/**
 * 需要转换的枚举类需要继承此接口
 * @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模型

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
@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;

}

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

image-20210624004607962.png

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
/**
 * 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);
            }
        }
    }
}

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
//写入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注解

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
@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;

}

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

image-20210624005959093.png

后端
#java #excel
Easyexcel导出excel实战(1)
http://example.com/2021/06/24/java/Easyexcel导出excel实战-1/
作者
敬文
发布于
2021年6月24日
许可协议