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

以上個人實踐操作,可根據個人情況使用

easyExcel導出資料并設定單元格下拉資料

繼續閱讀

關于Gradle配置的小結

Java小案例——随機數猜測随機數猜測

nginx location中斜線的位置的重要性

27 Best Free Eclipse Plug-ins for Java Developer to be ProductiveCode Quality PluginsText Editor PluginsDependency ManagementVersion Control Integration PluginsFramework Development Continuous Integration Related PluginsOther Utility Plugins

Java String.format方法的簡單使用

neo4j之cypher使用文檔

GitHub連夜封殺！這份阿裡 10W 字内部 Java 字面試手冊到底有多強？

spark/scala關于【資源檔案】加載方法概述外部檔案加載方案測試資源檔案打包入jar包中小結

mybatis_入門程式Mybatis入門

AOP程式設計_Android優雅權限架構(1)概念基礎，2021金三銀四前言正文大綱正文

Effective Java 8:通用程式設計

OOM三種類型

工廠模式-三種類型

【遞歸】高效率求2的n次幂

win10本地scala和spark安裝安裝scala安裝spark

scala (3) Function 和 Method