EasyExcel导出带自定义下拉框数据的Excel模板

文章目录

前言📝
一、导入依赖
二、创建导出工具
- 1.创建模板实体类
- 2.创建自定义注解
- 3.添加动态选择接口
- 4.EasyExcelUtil工具类
三、导出、导入Excel接口
- 1.导出接口
- 2.导入接口
- 3.导出结果
总结

前言📝

在项目中导入excel时需要通过下拉框选择值传入，所以需要在导出模板的时候，把下拉框数据一起导出到excel中

在这里插入图片描述

一、导入依赖

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>${easyexcel.version}</version>
</dependency>

<easyexcel.version>2.2.10</easyexcel.version>

二、创建导出工具

1.创建模板实体类

创建Excel导出模板表头对应的实体类

代码如下（示例）：

/**
 * 表头类
 *
 * @author wangjian
 * @date 2024-04-22 10:32
 */
@Data
@ColumnWidth(25)
@HeadRowHeight(15)
@ContentRowHeight(15)
public class TravelGroupExportExcel implements Serializable {

    /**
     * 游客姓名
     */
    @ExcelProperty(index = 0, value = "*游客姓名")
    private String userName;

    /**
     * 国家地区
     */
    @ExcelProperty(index = 1, value = "*国家地区")
    @ExcelSelected(sourceClass = CountrySelected.class)
    private String countryAreaTypeStr;

    /**
     * 证件号
     */
    @ExcelProperty(index = 2, value = "*证件号")
    private String idCard;

    /**
     * 联系电话
     */
    @ExcelProperty(index = 3, value = "*联系电话")
    private String phone;

    /**
     * 优待身份
     */
    @ExcelProperty(index = 4, value = "优待身份（选填）")
    @ExcelSelected(sourceClass = IdentitySelected.class)
    private String specialCode;

    /**
     * 模板版本
     */
    @ExcelProperty(index = 5, value = "模板版本")
    private String version;
}

2.创建自定义注解

创建自定义注解，标注导出的列为下拉框类型，并为下拉框设置内容

代码如下（示例）：

/**
 * 标注导出的列为下拉框类型，并为下拉框设置内容
 *
 * @author MaoDeShu
 * @date 2024-04-22 10:32
 */
@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ExcelSelected {
    /**
     * 固定下拉内容
     */
    String[] source() default {};

    /**
     * 动态下拉内容
     */
    Class<? extends ExcelDynamicSelect>[] sourceClass() default {};

    /**
     * 设置下拉框的起始行，默认为第二行
     */
    int firstRow() default 1;

    /**
     * 设置下拉框的结束行，默认为最后一行
     */
    int lastRow() default 0x10000;
}

Excel选择解析类

/**
 * 导入Excel选择解析类
 *
 * @author MaoDeShu
 * @date 2024-04-22 10:32
 */
@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;
    }
}

3.添加动态选择接口

创建动态选择接口及实现类，这里可以根据自己的业务逻辑去实现

ExcelDynamicSelect.java

/**
 * 获取下拉框数据接口
 *
 * @author MaoDeShu
 * @date 2024-04-22 10:32
 */
public interface ExcelDynamicSelect {

    /**
     * 获取动态生成的下拉框可选数据
     *
     * @return
     */
    String[] getSource();
}

实现类

CountrySelected.java

/**
 * 获取下拉框数据接口-国家地区
 *
 * @author MaoDeShu
 * @date 2024-04-22 10:32
 */
@Service
public class CountrySelected implements ExcelDynamicSelect{
    @Override
    public String[] getSource() {
    	// 这里根据自己的业务处理，这里示例写死
        return new ArrayList<String>(){{add("中国");add("中国台湾");add("中国香港");add("中国澳门");}}.toArray(new String[]{});
    }
}

IdentitySelected .java

/**
 * 获取下拉框数据接口-身份选择
 *
 * @author MaoDeShu
 * @date 2024-04-22 10:32
 */
@Service
public class IdentitySelected implements ExcelDynamicSelect{
    @Override
    public String[] getSource() {
    	// 这里根据自己的业务处理，这里示例写死
        return new ArrayList<String>(){{add("单选");add("多选");add("判断");add("问答");}}.toArray(new String[]{});
    }
}

4.EasyExcelUtil工具类

创建EasyExcelUtil工具类，实现Excel模板导出、导入

/**
 * excel工具类
 *
 * @author MaoDeShu
 * @date 2024-04-22 10:32
 */
@Slf4j
public class EasyExcelUtil {

    /**
     * 创建即将导出的sheet页（sheet页中含有带下拉框的列）
     *
     * @param head      导出的表头信息和配置
     * @param sheetNo   sheet索引
     * @param sheetName sheet名称
     * @param <T>       泛型
     * @return sheet页
     */
    public static <T> WriteSheet writeSelectedSheet(Class<T> head, Integer sheetNo, String sheetName) {
        Map<Integer, ExcelSelectedResolve> selectedMap = resolveSelectedAnnotation(head);

        return EasyExcel.writerSheet(sheetNo, sheetName)
                .head(head)
                .registerWriteHandler(new SelectedSheetWriteHandler(selectedMap))
                .build();
    }

    /**
     * 解析表头类中的下拉注解
     *
     * @param head 表头类
     * @param <T>  泛型
     * @return Map<下拉框列索引, 下拉框内容> map
     */
    private 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;
    }

    /**
     * 动态设置注解中的字段值
     *
     * @param clazz    注解所在的实体类
     * @param attrName 要修改的注解属性名
     * @param valueMap 要设置的属性值
     * @return
     */
    public static Class dynamicReviseAnnotationParam(Class clazz, String attrName
            , Map<String, String> valueMap) {

        Field[] declaredFields = clazz.getDeclaredFields();
        try {
            if (valueMap != null) {
                for (Field f : declaredFields) {
                    if (f.isAnnotationPresent(ExcelProperty.class)) {
                        ExcelProperty annotation = f.getAnnotation(ExcelProperty.class);
                        InvocationHandler handler = Proxy.getInvocationHandler(annotation);
                        Field field = handler.getClass().getDeclaredField("memberValues");
                        field.setAccessible(true);
                        // 注解信息
                        Map memberValues = (Map) field.get(handler);
                        String[] arr = (String[]) memberValues.get(attrName);
                        String oldValue = arr[0];
                        String newValue = valueMap.get(oldValue);
                        if (StrUtil.isNotBlank(newValue)) {
                            List newArr = new ArrayList();
                            newArr.add(newValue);
                            memberValues.put(attrName, newArr.toArray(new String[arr.length]));
                        }
                    }
                }
            }
        } catch (NoSuchFieldException | IllegalAccessException e) {
            log.error("动态添加注解数据失败！");
            e.printStackTrace();
            throw new BizException("动态添加注解数据失败！");
        }
        return clazz;
    }

    /**
     * 导出带下拉框的模板excel
     *
     * @param response
     * @param filename
     */
    public static void exportExcelTemplateBySelected(HttpServletRequest request, HttpServletResponse response
            , String filename, Class clazz, Map<String, String> replaceMap) {
        try {
            if (StrUtil.isBlank(filename)) {
                filename = "模板下载";
            }
            String agent = request.getHeader("USER-AGENT").toLowerCase();
            if (agent.contains("firefox")) {
                filename = new String(filename.getBytes(), "ISO8859-1");
            } else {
                filename = URLEncoder.encode(filename, "UTF-8");
            }
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            response.setHeader("Content-Disposition", String.format("attachment; filename=\"%s\"", filename + ".xlsx"));
            response.setHeader("Cache-Control", "no-cache");
            response.setHeader("Pragma", "no-cache");
            response.setDateHeader("Expires", -1);
            response.setCharacterEncoding("UTF-8");

            ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();

            Class dynamicClazz = dynamicReviseAnnotationParam(clazz, "value", replaceMap);

            WriteSheet writeSheet = EasyExcelUtil.writeSelectedSheet(dynamicClazz, 0, "sheet");

            excelWriter.write(new ArrayList<String>(), writeSheet);
            excelWriter.finish();
        } catch (Exception e) {
            e.printStackTrace();
            log.error("导出模板失败！");
            throw new BizException("导出模板失败！");
        }
    }

    /**
     * 模型解析监听器 -- 每解析一行会回调invoke()方法，整个excel解析结束会执行doAfterAllAnalysed()方法
     *
     * @param <E>
     */
    public static class ModelExcelListener<E> extends AnalysisEventListener<E> {

        private List<E> dataList = new ArrayList<E>();
        private Map<Integer, String> dataMap = new HashMap<Integer, String>(16);

        @Override
        public void invoke(E object, AnalysisContext context) {
            dataList.add(object);
        }

        @Override
        public void doAfterAllAnalysed(AnalysisContext context) {
        }

        @Override
        public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
            log.info("表头数据 excelHead= {}", headMap);
            dataMap.putAll(headMap);
        }

        public List<E> getDataList() {
            return dataList;
        }

        @SuppressWarnings("unused")
        public void setDataList(List<E> dataList) {
            this.dataList = dataList;
        }

        public Map<Integer, String> getDataMap() {
            return dataMap;
        }

        @SuppressWarnings("unused")
        public void setDataMap(Map<Integer, String> dataMap) {
            this.dataMap = dataMap;
        }
    }

    /**
     * 使用 模型 来读取Excel
     *
     * @param inputStream Excel的输入流
     * @param clazz       模型的类
     * @return 返回 模型 的列表
     */
    public static <E> List<E> readExcelWithModel(InputStream inputStream, Class<?> clazz) {
        // 解析每行结果在listener中处理
        ModelExcelListener<E> listener = new ModelExcelListener<>();
        try {
            EasyExcel.read(inputStream, clazz, listener).sheet().doRead();
        } catch (Exception e) {
            //throw new BusinessException("导入数据有问题，请修改后再上传");
            throw new BizException("导入数据有问题，请修改后再上传！");
        }
        return listener.getDataList();
    }

    /**
     * 使用 模型 来读取Excel
     *
     * @param inputStream Excel的输入流
     * @param clazz       模型的类
     * @return 返回 模型 的列表
     */
    public static <E> ModelExcelListener<E> readListener(InputStream inputStream, Class<?> clazz) {
        // 解析每行结果在listener中处理
        ModelExcelListener<E> listener = new ModelExcelListener<>();
        try {
            EasyExcel.read(inputStream, clazz, listener).sheet().doRead();
        } catch (Exception e) {
            //throw new BusinessException("导入数据有问题，请修改后再上传");
            throw new BizException("导入数据有问题，请修改后再上传！");
        }
        return listener;
    }

}

SelectedSheetWriteHandler.java

/**
 * 对cell进行下拉框设置操作
 *
 * @author MaoDeShu
 * @date 2024-04-22 10:32
 */
@AllArgsConstructor
public class SelectedSheetWriteHandler implements SheetWriteHandler {

    private final Map<Integer, ExcelSelectedResolve> selectedMap;

    /**
     * Called before create the sheet
     */
    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

    }

    /**
     * Called after the sheet is created
     */
    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        // 这里可以对cell进行任何操作globalCountryArea
        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);

            // 处理Excel兼容性问题
            if (validation instanceof XSSFDataValidation) {
                validation.setShowErrorBox(true);
                validation.setSuppressDropDownArrow(true);
            } else {
                validation.setSuppressDropDownArrow(false);
            }
            validation.createErrorBox("提示", "请输入下拉选项中的内容");
            sheet.addValidationData(validation);
        });
        // 冻结表头
        sheet.createFreezePane(0,1);
    }
}

三、导出、导入Excel接口

1.导出接口

@Slf4j
@RestController
@RequestMapping("/api/test")
@RequiredArgsConstructor
public class TestController {

    @PostMapping("/exportExcel")
    public void exportExcel(@RequestParam(value = "filename", required = false) @ApiParam(value = "文件名称") String filename,
                      HttpServletRequest request, HttpServletResponse response) {

        HashMap<String, String> replaceMap = new HashMap<>();
        replaceMap.put("模板版本", "V1.0");
		// 这里replaceMap是需要替换表头的值，将"模板版本"替换为"V1.0"
        EasyExcelUtil.exportExcelTemplateBySelected(request, response, filename, TravelGroupExportExcel.class, replaceMap);
    }
}

2.导入接口

@Slf4j
@RestController
@RequestMapping("/api/test")
@RequiredArgsConstructor
public class TestController {

    @PostMapping("/importExcel")
    public String importExcel(HttpServletRequest request, HttpServletResponse response) {

        try {
            MultipartHttpServletRequest multipart = (MultipartHttpServletRequest) request;
            MultiValueMap<String, MultipartFile> map = multipart.getMultiFileMap();
            MultipartFile file = null;
            for (Map.Entry<String, List<MultipartFile>> entry : map.entrySet()) {
                List<MultipartFile> files = entry.getValue();
                if (files == null || files.size() == 0) {
                    return "文件为空！";
                }
                file = files.get(0);
            }
            if (file != null) {
                EasyExcelUtil.ModelExcelListener listener = EasyExcelUtil.readListener(
                        file.getInputStream(), TravelGroupExcelModel.class);
				// 获取到excel信息
                List<TravelGroupExcelModel> excelModelList = listener.getDataList();

                // 获取excel表头
                Map<Integer, String> headMap = listener.getDataMap();
                if (headMap.isEmpty() || headMap.size() != 6) {
                    return "导入模板格式错误，请下载正确的模板修改后重新上传！";
                }

                if (excelProperties.getIsCheckVersion()) {
                    String version = headMap.get(headMap.size() - 1);
                    if (!excelProperties.getVersion().equalsIgnoreCase(version)) {
                        return "导入模板版本错误，请下载更新正确的模板后重新上传！";
                    }
                }

                if (CollUtil.isEmpty(excelModelList)) {
                    return "获取导入数据为空，请检查模板或数据格式是否正确！";
                }
              	// 后续处理 excelModelList 
            }
        } catch (Exception e) {
            e.printStackTrace();
            return e.getMessage();
        }
        return null;
    }
}