easyExcel导出数据并设置单元格下拉数据

easyExcel由于导出和导入的注解差异,所以实现不了标题功能
此处不在介绍导出导出的注解

entity:

@DropDownSetField自行封装注解:稍后解释

public class SalesOrderExportExcel {

		
		    private static final long serialVersionUID = 1L;
		
		    /**
		     * 履约方式
		     */
		    @ExcelProperty(value = "履约方式[*]", index = 2,converter= ConverterHandler.class)
		    @DropDownSetField(sourceClass = PerformTypeOptions.class)
		    private Integer performType;
    }

注解说明:

/**
 * 标记导出excel的下拉数据集
 */
@Documented
// 作用在字段上
@Target(ElementType.FIELD)
// 运行时有效
@Retention(RetentionPolicy.RUNTIME)
public @interface DropDownSetField {
    // 固定下拉内容
    String[] source() default {};
    // 动态下拉内容
    Class[] sourceClass() default {};

    
}

需要设置数据源:

public class ConverterHandler implements Converter<Integer> {
    @Override
    public Class supportJavaTypeKey() {
        return null;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return null;
    }

    @Override
    public Integer convertToJavaData(CellData cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        return null;
    }

    @Override
    public CellData convertToExcelData(Integer value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        if (contentProperty.getHead().getFieldName().equals("performType")) {
            if (value != null) {

                if (value == 1) {
                    return new CellData("门店履约");
                }
                if (value == 3) {
                    return new CellData("总部履约");
                }
                if (value == 4) {
                    return new CellData("自行履约");
                }
                if (value == 5) {
                    return new CellData("平台履约");
                }

            }
        }
        return new CellData("");
    }


}

设置下拉内容:

public class PerformTypeOptions implements DropDownSetInterface {

    @Override
    public String[] getSource() {
        return new String[] {"门店履约","总部履约","自行履约","平台履约"};

    }

}

此处需要是实现Excel拦截器和单元格拦截器(设置样式或单元格格式):

public class ProductCellWriteHandler extends AbstractCellStyleStrategy implements SheetWriteHandler , CellWriteHandler  {


    private static final Integer width = 34;
    private List<Map<String, Object>> list = null;
    Workbook workbooks;

    public ProductCellWriteHandler(List<Map<String, Object>> list){
        this.list = list;
    }

    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

    }

    @Override
    protected void initCellStyle(Workbook workbook) {
        this.workbooks  = workbook;
    }

    @Override
    protected void setHeadCellStyle(Cell cell, Head head, Integer relativeRowIndex) {

    }

    @Override
    protected void setContentCellStyle(Cell cell, Head head, Integer relativeRowIndex) {

    }

    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {

    }

    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean isHead) {
        this.initCellStyle(writeSheetHolder.getSheet().getWorkbook());
        writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), width * 350);

        CellStyle cellStyle = workbooks.createCellStyle();
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);//设置前景填充样式
        cellStyle.setFillForegroundColor(IndexedColors.ROYAL_BLUE.getIndex());//前景填充色
        Font font1 = workbooks.createFont();//设置字体
        font1.setBold(true);
        font1.setColor((short)1);
        font1.setFontHeightInPoints((short)15);
        cellStyle.setFont(font1);
        cell.setCellStyle(cellStyle);
        //其他列
        if (!isHead){
            CellStyle style = workbooks.createCellStyle();
            DataFormat dataFormat = workbooks.createDataFormat();
            style.setDataFormat(dataFormat.getFormat("@"));
            style.setVerticalAlignment(VerticalAlignment.CENTER);
            style.setAlignment(HorizontalAlignment.CENTER);
            cell.setCellStyle(style);
        }
        //设置日期
        if (!isHead && cell.getColumnIndex()==19 || !isHead && cell.getColumnIndex()==21|| !isHead && cell.getColumnIndex()==20){
            CellStyle style = workbooks.createCellStyle();
            DataFormat dataFormat = workbooks.createDataFormat();
            style.setDataFormat(dataFormat.getFormat("yyyy/mm/dd hh:mm:ss"));
            style.setVerticalAlignment(VerticalAlignment.CENTER);
            style.setAlignment(HorizontalAlignment.CENTER);
            cell.setCellStyle(style);
        }
        //设置金额
        if (!isHead && cell.getColumnIndex()==15 ||!isHead && cell.getColumnIndex()==16||!isHead && cell.getColumnIndex()==22 ||!isHead && cell.getColumnIndex()==24||!isHead && cell.getColumnIndex()==25){
            CellStyle style = workbooks.createCellStyle();
            DataFormat dataFormat = workbooks.createDataFormat();
            style.setDataFormat(dataFormat.getFormat("0.00"));
            // style.setDataFormat((short) BuiltinFormats.getBuiltinFormat("#,##0.00")); //货币
            style.setVerticalAlignment(VerticalAlignment.CENTER);
            style.setAlignment(HorizontalAlignment.CENTER);
            cell.setCellStyle(style);
        }
        // 这里可以对cell进行任何操作
        Sheet sheet = writeSheetHolder.getSheet();
        DataValidationHelper helper = sheet.getDataValidationHelper();
        // k 为存在下拉数据集的单元格下表 v为下拉数据集
        if(!CollectionUtils.isEmpty(list)){
            list.forEach((item) -> {
                String [] arr = (String[]) item.get("source");
                // 下拉列表约束数据
                DataValidationConstraint constraint = helper.createExplicitListConstraint(arr);
                // 设置下拉单元格的首行 末行 首列 末列
                CellRangeAddressList rangeList = new CellRangeAddressList(1, 65536, Integer.valueOf(item.get("index").toString()), Integer.valueOf(item.get("index").toString()));
                // 设置约束
                DataValidation validation = helper.createValidation(constraint, rangeList);
                // 阻止输入非下拉选项的值
                validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
                validation.setShowErrorBox(true);
                validation.setSuppressDropDownArrow(true);
                validation.createErrorBox("提示","此值与单元格定义格式不一致");
                // validation.createPromptBox("填写说明：","填写内容只能为下拉数据集中的单位，其他单位将会导致无法入仓");
                sheet.addValidationData(validation);
            });
        }

        //this.setCellStyleType(cell,head,integer,writeSheetHolder);
    }

    @Override
    public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {

    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {

    }
}

取值的工具类:

public class ResoveDropAnnotationUtil {


    public static Map<String,Object> resove(DropDownSetField dropDownSetField){
        if(!Optional.ofNullable(dropDownSetField).isPresent()){
            return null;
        }
        Map<String,Object> map = new HashMap<>();
        // 获取固定下拉信息
        String[] source = dropDownSetField.source();
        int indexFlag = dropDownSetField.index();
        if(null != source && source.length > 0 && indexFlag >= 0){
            map.put("index",indexFlag);
            map.put("source",source);
            return map;
        }

        // 获取动态的下拉数据
        Class<? extends DropDownSetInterface>[] classes = dropDownSetField.sourceClass();
        if(null != classes && classes.length > 0){
            try {
                DropDownSetInterface dropDownSetInterface = Arrays.stream(classes).findFirst().get().newInstance();
                String[] dynamicSource = dropDownSetInterface.getSource();
                int index = dropDownSetInterface.getIndex();
                if(null != dynamicSource && dynamicSource.length > 0 && index >= 0){
                    map.put("index",index);
                    map.put("source",dynamicSource);
                    return map;
                }
            } catch (InstantiationException e) {
                e.printStackTrace();
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

}

导出并从注解中获取下拉内容:

 String fileName = URLEncoder.encode("销售订单" + sdf.format(new Date()), "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            Field[] fields = SalesOrderExportExcel.class.getDeclaredFields();
            // 响应字段对应的下拉集合
            List<Map<String, Object>> list = new ArrayList<>();
            Field field = null;
            // 循环判断哪些字段有下拉数据集，并获取
            for (int i = 0; i < fields.length; i++) {
                field = fields[i];
                // 解析注解信息
                DropDownSetField dropDownSetField = field.getAnnotation(DropDownSetField.class);
                if (null != dropDownSetField) {
                    Map<String, Object> sources = ResoveDropAnnotationUtil.resove(dropDownSetField);
                    if (sources != null) {
                        Map<String, Object> map = new HashMap<>();
                        map.put("index", sources.get("index"));
                        map.put("source", sources.get("source"));
                        list.add(map);
                    }
                }
            }
            EasyExcel.write(response.getOutputStream(), SalesOrderExportExcel.class)
                    .registerWriteHandler(new ProductCellWriteHandler(list))
                    .excelType(ExcelTypeEnum.XLSX)
                    .sheet("销售订单")
                    .doWrite(salesOrderExportExcelList);

以上个人实践操作,可根据个人情况使用