EasyExcel注解实现导出动态下拉框字符超出255

最新推荐文章于 2024-04-29 22:27:52 发布

CodersCoder

最新推荐文章于 2024-04-29 22:27:52 发布

阅读量809

点赞数 8

文章标签： spring boot java

本文链接：https://blog.csdn.net/coderscoder/article/details/136669940

版权

背景

项目需求要把某些用户数据通过下拉框的形式带出，供用户手动选择与自己相关的数据，从而实现excel的数据权限。由于字段有两个，后面可能更多，方便起见，使用自定义注解的形式，动态配置，并且动态实现下拉数据。

依赖

		<dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.3.1</version>
        </dependency>

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>5.0.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>5.0.0</version>
        </dependency>

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-scratchpad</artifactId>
            <version>5.0.0</version>
        </dependency>

实现

1.创建动态配置

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

}

2.创建动态导出自定义注解，用来制定动态导出项

/**
 * 标注导出的列为下拉框类型，并为下拉框设置内容
 */
@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;

}

3.设置动态解析类

@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;
    }

}

4.创建动态解析拦截器

@Data
@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进行任何操作
        Sheet sheet = writeSheetHolder.getSheet();
        DataValidationHelper helper = sheet.getDataValidationHelper();
        selectedMap.forEach((k, v) -> {
            // 设置下拉列表的行： 首行，末行，首列，末列
            CellRangeAddressList rangeList = new CellRangeAddressList(v.getFirstRow(), v.getLastRow(), k, k);
            // 设置下拉列表的值
            int sheetTotal = sheet.getWorkbook().getNumberOfSheets();
            List<String> source = Arrays.asList(v.getSource());
            //这里**为了防止下拉框超过255限制，将数据先放在一个新建的sheet页，设置约束时再把值拿回来
            String name = k.toString();
            Sheet  hidden = sheet.getWorkbook().createSheet(name);
            for (int i = 0; i < source.size(); i++) {
                Row row = hidden.createRow(i);
                Cell cell = row.createCell(0);
                cell.setCellValue(source.get(i));
            }
            sheet.getWorkbook().setSheetHidden(sheetTotal, true);
            String strFormula = name + "!$A$1:$A$65535";
            XSSFDataValidationConstraint constraint = new XSSFDataValidationConstraint(DataValidationConstraint.ValidationType.LIST, strFormula);
            // 设置约束
            DataValidation validation = helper.createValidation(constraint, rangeList);
            // 阻止输入非下拉选项的值
            validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
            validation.setShowErrorBox(true);
            validation.setSuppressDropDownArrow(true);
            validation.createErrorBox("提示", "请输入下拉选项中的内容");
            sheet.addValidationData(validation);
        });
    }
}

5.创建导出工具类

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;
    }

}

6.测试验证
编写导出实体类，并在需要动态导出的字段上使用自定义注解

@Data
public class DataExcel {
    /**
     * 工号
     */
    @ExcelProperty(index = 0,value ="工号")
    private String userCode;
    /**
     * 产业
     */
    @ExcelProperty(index = 1,value ="产业")
    @ColumnWidth(25)
    @ExcelSelected(sourceClass = IndustrySelected.class)
    private String industry;
    /**
     * 设计
     */
    @ExcelProperty(index = 2,value ="设计")
    @ColumnWidth(25)
    @ExcelSelected(sourceClass = DesignMicroSelected.class)
    private String designMicro;
}

编写动态导出源数据获取

public class IndustrySelected implements ExcelDynamicSelect {

    @Override
    public String[] getSource() {
        UtilsDao bean = SpringContextUtil.getBean(UtilsDao.class);
        return bean.queryNames().toArray(new String[]{});
    }
}

public class DesignMicroSelected implements ExcelDynamicSelect {
    @Override
    public String[] getSource() {
        UtilsDao bean = SpringContextUtil.getBean(UtilsDao.class);
        return bean.queryNames2().toArray(new String[]{});
    }
}

bean.queryNames()和bean.queryNames2()是数据库查询语句，查询的是要导出的列

编写controller实现接口调用导出

@RestController
@RequestMapping("/util")
public class UtilController {
@RequestMapping("/report")
    public void report(HttpServletRequest request, HttpServletResponse response){
        String fileName = "test";
        ExcelWriter writer = null;
        WriteSheet test = EasyExcelUtil.writeSelectedSheet(DataExcel.class, 1, "test");
        try {
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); //设置响应内容类型
            response.setCharacterEncoding("utf-8");//编码
            // 设置文件名, ps:把字符串中所有的'+'替换成'%20'，在URL中%20代表空格
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");//设置响应头
            response.setHeader("fileName", fileName + ".xlsx");//设置响应头
            /** 导出的数据集合 */
            List<DataExcel> modelList = new ArrayList<>();
            /** registerWriteHandler(合并行数,那些列需要合并) */
            WriteSheet sheet = test;
            writer = EasyExcel.write(response.getOutputStream(), DataExcel.class).build();//获取写出流
            writer.write(modelList, sheet);//读出

        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            if (null != writer) {
                writer.finish();//关闭流
            }
        }

    }
 }

至此已经实现动态下拉框的数据导出，并且下拉数据没有被255字符所限制。

额外

思考
在动态获取下拉数据源的IndustrySelected 和 DesignMicroSelected 中，最开始时是使用@Autowired的方式注入UtilsDao，但是发现实际使用时，却出现了空指针，发现没有获取到UtilsDao对象，但是bean容器中也确实有。后来查阅资料，发现很多情景会出现这个情况