--- theme: github --- # 需求假设有一批商品房,我们需要将其导出到excel上,批量维护价格和业态,再导入回系统. | id | 业态 | 房间名称 | 面积 | 单价(元/m2) | 总价(元) | | ---- | ---- | -------------------------- | ---- | ----------- | -------- | | 1 | 商铺 | 万科城市之光-一期-一栋-101 | 50 | | | | 2 | 商铺 | 万科城市之光-一期-一栋-102 | 50 | | | | 3 | 商铺 | 万科城市之光-一期-一栋-103 | 50 | | | | 4 | 住宅 | 万科城市之光-一期-一栋-201 | 24 | | | | 5 | 住宅 | 万科城市之光-一期-一栋-202 | 35 | | | | 6 | 住宅 | 万科城市之光-一期-一栋-203 | 31 | | | > 1. 其中,业态为枚举值,具体取值如下: > > ```java > @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. 创建表头模型 ```java @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就简单很多了 ```java @SneakyThrows public String export2Excel(){ String filename = "房间列表"; String extName = ".xlsx"; File tempFile = File.createTempFile(filename, extName); log.info("temp file path: {}",tempFile.getAbsolutePath()); List 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](https://p1-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/e8dd7b169a164f6fb28c63e0bb24a9db~tplv-k3u1fbpfcp-watermark.image) 稍微调整下列宽和居中后,EasyExcel自带的样式已经看着很不错了,但还有几个问题: 1. id只是为了导入时能找到对应数据,不需要展示出来 2. 业态没有转换为对应的中文,且应该有下拉选择 3. 房间名称、面积应该不允许编辑下面我们来解决这些问题 # 隐藏列 `@ContentStyle`注解上有一个参数`hidden`,但这个参数目前并没有什么用,要隐藏某列,我们需要将该列的宽度设置为0,我们试一下 ```java /** * 房间id */ @ExcelProperty("id") @ColumnWidth(0) private Integer id; ``` 看看效果,可以看到id已经被隐藏了 ![image-20210620124733881.png](https://p9-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/bfed541875f34c8784944261514dd7f8~tplv-k3u1fbpfcp-watermark.image) # 保护工作表保护工作表需要注解和拦截器配合,首先使用`@ContentStyle`的`locked`参数加锁,然后使用拦截器启用保护工作表。 ```java @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 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](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/ab82ee4b046e479689c407546e23c4fe~tplv-k3u1fbpfcp-watermark.image) # 枚举之前的代码里，我使用了AbstractSheetWriteHandler拦截器,作用是在写入工作表的过程中进行一些额外的操作,EasyExcel提供了很多类似的拦截器: - WorkbookWriteHandler: 在写入工作簿前后提供额外处理 - SheetWriteHandler: 在写入工作表前后提供额外处理 - RowWriteHandler: 在写入行的过程中提供额外处理 - CellWriteHandler: 在写入单元格过程中提供额外处理一般情况下,EasyExcel会对字段自动使用合适的转换器,但我们也可以自己制定自定义的转换器,要将枚举值转换为对应的中文描述,就可以使用自定义转换器的方式来在写入单元格时做转换. 首先编写转换器逻辑: ```java /** * 需要转换的枚举类需要继承此接口 * @author 92339 */ public interface IExcelEnum { T getCode(); String getStringValue(); } /** * 枚举值转换器 * @author 92339 */ public abstract class AbstractEnum2StringConverter> implements Converter { private final Class typeClass; private final BiMap enumMap = HashBiMap.create(); @SuppressWarnings({"unchecked"}) public AbstractEnum2StringConverter() { Class> enumClass = (Class>) TypeUtil.getTypeArgument(getClass(), 1); if (!enumClass.isEnum()) { throw new IllegalArgumentException("ParameterizedType[1] must be enum"); } this.typeClass = (Class) TypeUtil.getTypeArgument(getClass(), 0); initEnumMap(enumClass); } private void initEnumMap(Class> enumClass) { for (IExcelEnum 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 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模型 ```java @AllArgsConstructor @Getter public enum HouseTypeEnum implements IExcelEnum { RESIDENTIAL("residential", "住宅"), PARKING("parking", "车位"), SHOP("shop","商铺"), ; private final String code; private final String stringValue; /** * 声明class即可,不需要有具体实现 */ static class HouseTypeEnum2StringConverter extends AbstractEnum2StringConverter{} } @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](https://p6-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/a3631c0189e34707b98cd9558e40c923~tplv-k3u1fbpfcp-watermark.image) 下一步是加上下拉校验,这里需要使用注解+CellWriteHandler来处理。 ```json /** * Excel枚举字段需要使用此注解声明 * * @author jingwen */ @Target(ElementType.FIELD) @Retention(RetentionPolicy.RUNTIME) @Inherited @Documented public @interface ExcelEnum { Class> 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 entry : writeSheetHolder.getExcelWriteHeadProperty().getContentPropertyMap().entrySet()) { int index = entry.getKey(); ExcelContentProperty excelContentProperty = entry.getValue(); ExcelEnum excelEnum = excelContentProperty.getField().getAnnotation(ExcelEnum.class); if (excelEnum != null) { Class> 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); } } } } ``` 再在导出的逻辑中加上对应的拦截器。 ```java //写入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`注解 ```java @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](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/89624b5ed2a04d679b8af56a9db635e4~tplv-k3u1fbpfcp-watermark.image)