easyExcel导出下拉框
前两天做项目有个数据导入的需求，其中一设备类型是参照数据库中另一个表固定死的，而且有200多条记录，这时候手动去输入的话难免会出错，就想到了excel的下拉框，可以先给他导出一个模板，模板中的设备类型那一列是下拉框的形式，这里记录下实现过程。
一、写一个注解，用于指定包含下拉框的属性
package com.dosth.task.anno;
import com.dosth.task.selector.ExcelDynamicSelect;
import java.lang.annotation.Documented;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
/**
* 标注导出的列为下拉框类型，并为下拉框设置内容
*/
@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ExcelSelected {
/**
* 动态下拉内容
*/
Class<? extends ExcelDynamicSelect>[] sourceClass() default {};
/**
* 设置下拉框的起始行，默认为第二行
*/
int firstRow() default 1;
/**
* 设置下拉框的结束行，默认为最后一行
*/
int lastRow() default 0x10000;
}
二、添加动态下拉选择框的接口，然后写一个类型选择下拉框的数据获取；
- ExcelDynamicSelect接口
package com.dosth.task.selector;
public interface ExcelDynamicSelect {
/**
* 获取动态生成的下拉框可选数据
* @return 动态生成的下拉框可选数据
*/
String[] getSource();
- ExcelDynamicSelect实现类
package com.dosth.task.selector;
import com.dosth.task.repository.TypeRepository;
import com.dosth.task.selector.ExcelDynamicSelect;
import com.dosth.task.util.SpringContextUtil;
import java.util.List;
public class TypeSelector implements ExcelDynamicSelect {
@Override
public String[] getSource() {
TypeRepository repository = SpringContextUtil.getBean(TypeRepository.class);
List<String> allType = repository.findAllType();
String[] strings = allType.toArray(new String[0]);
return strings;
}
}
- 工具类(用于从数据库动态获取类型数据)
package cn.com.dosth.module.mss.service.equipment;
import cn.com.dosth.framework.common.util.spring.SpringContextUtils;
import cn.com.dosth.framework.excel.core.selector.ExcelDynamicSelect;
import cn.com.dosth.module.mss.dal.dataobject.equipmenttype.EquipmentTypeDO;
import cn.com.dosth.module.mss.dal.mysql.equipmenttype.EquipmentTypeMapper;
import java.util.List;
public class EquipmentTypeSelector implements ExcelDynamicSelect {
@Override
public String[] getSource() {
List<EquipmentTypeDO> equipmentTypeDOS = SpringContextUtils.getBean(EquipmentTypeMapper.class).selectList();
return equipmentTypeDOS.stream().map(EquipmentTypeDO::getEquipmentTypeName).toArray(String[]::new);
}
}
三、给需要添加下拉框的列对应的属性加上下拉框注解
@ExcelSelected(sourceClass = TypeSelector.class)
@ExcelProperty("设备类型")
private String typeName;
四、处理下拉框
动态下拉选择框是实现了
ExcelDynamicSelect接口的实现类，实现类中重写的 getSource() 方法，从数据库中动态获取String数组；这里使用到了反射的基本原理；
package com.dosth.task.anno;
import com.dosth.task.selector.ExcelDynamicSelect;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;
@Data
@Slf4j
public class ExcelSelectedResolve {
/**
* 下拉内容
*/
private String[] source;
/**
* 设置下拉框的起始行，默认为第二行
*/
private int firstRow;
/**
* 设置下拉框的结束行，默认为最后一行
*/
private int lastRow;
public String[] resolveSelectedSource(ExcelSelected excelSelected) {
if (excelSelected == null) {
return null;
}
// 获取固定下拉框的内容
String[] source = excelSelected.source();
if (source.length > 0) {
return source;
}
// 获取动态下拉框的内容
Class<? extends ExcelDynamicSelect>[] classes = excelSelected.sourceClass();
if (classes.length > 0) {
try {
ExcelDynamicSelect excelDynamicSelect = classes[0].newInstance();
String[] dynamicSelectSource = excelDynamicSelect.getSource();
if (dynamicSelectSource != null && dynamicSelectSource.length > 0) {
return dynamicSelectSource;
}
} catch (InstantiationException | IllegalAccessException e) {
log.error("解析动态下拉框数据异常", e);
}
}
return null;
}
}
五、借助EasyExcelUtil.java解析表头类中的下拉注解
package com.dosth.task.util;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.dosth.task.anno.ExcelSelected;
import com.dosth.task.anno.ExcelSelectedResolve;
import lombok.extern.slf4j.Slf4j;
import java.lang.reflect.Field;
import java.util.HashMap;
import java.util.Map;
@Slf4j
public class EasyExcelUtil {
/**
* 解析表头类中的下拉注解
* @param head 表头类
* @param <T> 泛型
* @return Map<下拉框列索引, 下拉框内容> map
*/
public static <T> Map<Integer, ExcelSelectedResolve> resolveSelectedAnnotation(Class<T> head) {
Map<Integer, ExcelSelectedResolve> selectedMap = new HashMap<>();
// getDeclaredFields(): 返回全部声明的属性；getFields(): 返回public类型的属性
Field[] fields = head.getDeclaredFields();
for (int i = 0; i < fields.length; i++){
Field field = fields[i];
// 解析注解信息
ExcelSelected selected = field.getAnnotation(ExcelSelected.class);
ExcelProperty property = field.getAnnotation(ExcelProperty.class);
if (selected != null) {
ExcelSelectedResolve excelSelectedResolve = new ExcelSelectedResolve();
// 处理下拉框内容
String[] source = excelSelectedResolve.resolveSelectedSource(selected);
if (source != null && source.length > 0){
excelSelectedResolve.setSource(source);
excelSelectedResolve.setFirstRow(selected.firstRow());
excelSelectedResolve.setLastRow(selected.lastRow());
if (property != null && property.index() >= 0){
selectedMap.put(property.index(), excelSelectedResolve);
} else {
selectedMap.put(i, excelSelectedResolve);
}
}
}
}
return selectedMap;
}
}
六、在导出代码中，处理下拉选择框
使用工具类，将下拉框做成一个map的形式（列号，下拉选择框String数组），注意列号是从0开始的；
- EquipmentController.java
public void importEquipmentTemplate(HttpServletResponse response) throws Exception {
// 获得导入设备模板
List<EquipmentImportExcelVO> importExcelVOS = Arrays.asList(
EquipmentImportExcelVO.builder().equipmentIp("192.168.0.1(示例)").threatPerceptionEquipment("0")
.macAddress("CD:98:33:AD:34:ZM").typeName("ATM一体机")
.equipmentNum("M1231233").maintainDueDate(new Date()).equipmentName("一体机一号").build()
);
// 设置下拉列表
Map<Integer, ExcelSelectedResolve> selectedMap = ExcelUtils.resolveSelectedAnnotation(EquipmentImportExcelVO.class);
//导出
ExcelUtils.write(response, "设备.xlsx", "设备", EquipmentImportExcelVO.class, importExcelVOS, selectedMap);
}
- ExcelUtils
public static <T> void write(HttpServletResponse response, String filename, String sheetName,
Class<T> head, List<T> data, Map<Integer, ExcelSelectedResolve> selectedMap) throws IOException {
// 输出 Excel
EasyExcel.write(response.getOutputStream(), head)
.autoCloseStream(false) // 不要自动关闭，交给 Servlet 自己处理
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()) // 基于 column 长度，自动适配。最大 255 宽度
.sheet(sheetName)
.registerWriteHandler(new SelectedSheetWriteHandler(selectedMap))
.doWrite(data);
// 设置 header 和 contentType。写在最后的原因是，避免报错时，响应 contentType 已经被修改了
response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(filename, "UTF-8"));
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
}
- SelectedSheetWriteHandler.java
package com.dosth.task.handler;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import com.dosth.task.anno.ExcelSelectedResolve;
import lombok.AllArgsConstructor;
import lombok.Data;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddressList;
import java.util.Map;
@Data
@AllArgsConstructor
public class SelectedSheetWriteHandler implements SheetWriteHandler {
private final Map<Integer, ExcelSelectedResolve> selectedMap;
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
// 这里可以对cell进行任何操作
Sheet sheet = writeSheetHolder.getSheet();
DataValidationHelper helper = sheet.getDataValidationHelper();
selectedMap.forEach((k, v) -> {
// 设置下拉列表的行： 首行，末行，首列，末列
CellRangeAddressList rangeList = new CellRangeAddressList(v.getFirstRow(), v.getLastRow(), k, k);
// 设置下拉列表的值
DataValidationConstraint constraint = helper.createExplicitListConstraint(v.getSource());
// 设置约束
DataValidation validation = helper.createValidation(constraint, rangeList);
// 阻止输入非下拉选项的值
validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
validation.setShowErrorBox(true);
validation.setSuppressDropDownArrow(true);
validation.createErrorBox("提示", "请输入下拉选项中的内容");
sheet.addValidationData(validation);
});
}
}
博主最后的测试是成功的，然鹅我在几次小bug修复后，打开却弹出以下提示：
点击是打开，导出数据都在，但是没有下拉框。
总以为是代码不对，跟博主的比对了N多次，折腾了一下午，最后将下拉框数据数组写死，只枚举出少量的数据，导出下拉成功！！！几次测试之后找到了问题所在。
- Excel下拉框最多255字符，我们导出的200条数据字符数肯定超过了255，导致数据损坏，下拉框无法实现
七、最终解决
既然直接放在下拉框不行，我们可以使用曲线救国的方式，将类型导出的Sheet2，然后使用Excel公式引用到Sheet1，并将Sheet2隐藏，在SelectedSheetWriteHandler类中添加以下代码
- 导出结果
但是这样会导致无法阻止输入非下拉框内容，也就是创建参考表后面的约束条件失效了。
八、导入问题
这要导出使用下拉框会很方便，但实测导入时也会读取隐藏表的内容，这属实没必要了，所以需要我们指定读取特定的表。这里百度可用方法有很多，我只贴下我用到的这种。
EquipmentController.java
@PostMapping("/import") public CommonResult<AssetImportRespVO> importExcel(@RequestParam("file") MultipartFile file) throws Exception { assetService.checkSuffix(file); List<AssetImportExcelVO> list = ExcelUtils.readOneSheet(file, AssetImportExcelVO.class); return success(assetService.importAssets(list)); }ExcelUtils.java
//文件中含有多个表时只读取特定表 public static <T> List<T> readOneSheet(MultipartFile file, Class<T> head) throws IOException { ExcelReaderBuilder excelReaderBuilder = EasyExcel.read(file.getInputStream(), head, null) .autoCloseStream(false); //参照ExcelReaderBuilder的doReadAllSync()方法; SyncReadListener syncReadListener = new SyncReadListener(); excelReaderBuilder.registerReadListener(syncReadListener); ExcelReader excelReader = excelReaderBuilder.build(); //默认数据存放在第一个sheet表中，下标从0开始 ReadSheet sheet = EasyExcel.readSheet(0).build(); excelReader.read(sheet); excelReader.finish(); return (List<T>) syncReadListener.getList(); }