Java使用EasyExcel生成数据带有下拉框格式的Excel

原创已于 2024-07-18 16:46:56 修改 · 1.4k 阅读

8 ·

CC 4.0 BY-SA版权

文章标签：

#excel

于 2024-07-17 10:39:45 首次发布

Excel导出专栏收录该内容

5 篇文章

订阅专栏

由于在导入Excel前，为了方便用户输入数据，导入的模板字段需带有下拉框格式，使用EasyExcel

使用方法：

EasyExcelUtils.writeExcelBySelect(response, fileName, 0, "Sheet1", VO.class, voList);

解析方法:EasyExcelUtil.writeExcelBySelect方法：

 @Slf4j
public class EasyExcelUtils {   
    /**
    * 导出具有下拉框选择的Excel
    */
    public static <T> void writeExcelBySelect(HttpServletResponse response, String fileName, Integer sheetNo, String sheetName, Class<T> head, List<T> data) {
        try {
            fileName = URLEncoder.encode(fileName, StandardCharsets.UTF_8.name());
            response.setContentType("application/vnd.ms-excel");
            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");
            Map<Integer, ExcelSelectedResolve> selectedMap = EasyExcelUtils.resolveSelectedAnnotation(head);
            WriteSheet writeSheet = EasyExcel.writerSheet(sheetNo, StringUtils.isBlank(sheetName) ? "sheet1" : sheetName)
                    .head(head)
                    .registerWriteHandler(new SelectedSheetWriteHandler(selectedMap))
                    .build();
            EasyExcel.write(response.getOutputStream())
                    .build()
                    .write(data, writeSheet)
                    .finish();
        } catch (IOException e) {
            log.error("导出Excel编码异常", e.getMessage());
        }
    }

     /**
     * 解析表头类中的下拉注解
     *
     * @param head 表头类
     * @param <T>  泛型
     * @return Map<下拉框列索引, 下拉框内容> map
     */
    public static <T> Map<Integer, ExcelSelectedResolve> resolveSelectedAnnotation(Class<T> head) {
        Map<Integer, ExcelSelectedResolve> selectedMap = new HashMap<>();
        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();
                excelSelectedResolve.setColumnName(StringUtils.join(property.value(), "-"));
                // 处理下拉框内容
                String[] source = excelSelectedResolve.resolveSelectedSource(selected);
                if (source != null && source.length > 0) {
                    excelSelectedResolve.setSource(source);
                    excelSelectedResolve.setFirstRow(selected.firstRow());
                    excelSelectedResolve.setLastRow(selected.lastRow());
                    if (property.index() >= 0) {
                        selectedMap.put(property.index(), excelSelectedResolve);
                    } else {
                        selectedMap.put(i, excelSelectedResolve);
                    }
                }
            }
        }
        return selectedMap;
    }
}

下拉框 ExcelSelectedResolve解析器

@Data
@Slf4j
public class ExcelSelectedResolve {

    /**
     * 下拉内容
     */
    private String[] source;

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

    /**
     * 设置下拉框的结束行，默认为最后一行
     */
    private int lastRow;

    /**
     * 下拉列名
     */
    private String columnName;
    
    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;
    }
}

下拉框处理 SelectedSheetWriteHandler处理器

@Data
public class SelectedSheetWriteHandler implements SheetWriteHandler {

    /**
     * 构建下拉选的map
     */
    private final Map<Integer, ExcelSelectedResolve> selectedMap;

    private final int columnSelectMaxLength = 255;

    @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) -> {
            String[] source = v.getSource();
            String selectColumnName = v.getColumnName();
            // 下拉选总字符超过255,需要通过创建sheet关联方式构建下拉选
            if (StringUtils.join(source).length() > columnSelectMaxLength) {
                // 获取一个workbook
                Workbook workbook = writeWorkbookHolder.getWorkbook();
                // 定义sheet的名称
                String sheetName = selectColumnName + k;
                // 创建一个隐藏的sheet
                Sheet tmpSheet = workbook.createSheet(sheetName);
                // 循环赋值（为了防止下拉框的行数与隐藏域的行数相对应，将隐藏域加到结束行之后）
                for (int i = 0, length = source.length; i < length; i++) {
                    // i:表示你开始的行数 0表示你开始的列数
                    tmpSheet.createRow(i).createCell(0).setCellValue(source[i]);
                }
 Name category1Name = workbook.createName();
                category1Name.setNameName(sheetName);
                // $A$1:$A$N代表 以A列1行开始获取N行下拉数据
                category1Name.setRefersToFormula(sheetName + "!$A$1:$A$" + (source.length));
                // 将刚才设置的sheet引用到你的下拉列表中, (首行，末行，首列，末列)
                CellRangeAddressList addressList = new CellRangeAddressList(v.getFirstRow(), v.getLastRow(), k, k);
                DataValidationConstraint constraint = helper.createFormulaListConstraint(sheetName);
                DataValidation validation = helper.createValidation(constraint, addressList);
                // 阻止输入非下拉选项的值
                validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
                validation.setShowErrorBox(true);
                validation.setSuppressDropDownArrow(true);
                validation.createErrorBox("提示", "请输入下拉选项中的内容");
                sheet.addValidationData(validation);
            } else {
 // 设置下拉列表的行： 首行，末行，首列，末列
                CellRangeAddressList rangeList = new CellRangeAddressList(v.getFirstRow(), v.getLastRow(), k, k);
                // 设置下拉列表的值
                DataValidationConstraint constraint = helper.createExplicitListConstraint(source);
                // 设置约束
                DataValidation validation = helper.createValidation(constraint, rangeList);
                // 阻止输入非下拉选项的值
                validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
                validation.setShowErrorBox(true);
                validation.setSuppressDropDownArrow(true);
                validation.createErrorBox("提示", "请输入下拉选项中的内容");
                sheet.addValidationData(validation);
            }
        });
    }
}

下拉框选项注解 @ExcelSelected注解

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

实体

@Data
public class VO {

    @ExcelProperty("ID")
    private Integer id;

    @ExcelProperty("开关")
    @ExcelSelected(source = {"关闭", "开启"})
    private String type;

}

动态下拉框

public interface ExcelDynamicSelect {

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

}

使用说明:

下拉选的选项数量超过255个后则会通过创建额外sheet保存选项，再引用到下拉选

1.固定值的下拉选,使用

 @ExcelProperty("开关")
 @ExcelSelected(source = {"关闭", "开启"})
 private String type;

2.动态下拉选,需自定义实现ExcelDynamicSelect

public class SexSelector implements ExcelDynamicSelect {
 
    @Override
    public String[] getSource() {
        // todo 此处可通过获取dao实例来查询数据库,构建动态数据
        return new String[] {"男", "女", "保密"};
    }
}

3.引用动态选项类

@ExcelSelected(sourceClass = SexSelector.class)
@ExcelProperty("公司")
private String gender;