easyexcel 导出下拉框

秒速五厘米 2024-03-23 17:44 65阅读 0赞

--------------------

官方文档：https://github.com/alibaba/easyexcel

目标：导出的 excel 是下拉框

![在这里插入图片描述][d0a2416ef023427fb8bf13b5641ea534.png]  
![在这里插入图片描述][7e024e7f9a6e4b719ed3757ee136699d.png]

maven 导入依赖

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

定义 SampleWriteHandler

*  根据参数 map 创建
 *  添加约束
 *  阻止输入非下拉选项的值

import com.alibaba.excel.write.handler.SheetWriteHandler;
    import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
    import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
    import org.apache.poi.ss.usermodel.DataValidation;
    import org.apache.poi.ss.usermodel.DataValidationConstraint;
    import org.apache.poi.ss.usermodel.DataValidationHelper;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.util.CellRangeAddressList;
    import java.util.Map;
     
    public class SampleWriteHandler implements SheetWriteHandler {
        
    
        private Map<Integer,String[]> map = null;
    
        public SampleWriteHandler(Map<Integer,String[]> map){
        
            this.map = map;
        }
    
        @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();
            // k 为存在下拉数据集的单元格下表 v为下拉数据集
            map.forEach((k, v) -> {
        
                // 下拉列表约束数据
                DataValidationConstraint constraint = helper.createExplicitListConstraint(v);
                // 设置下拉单元格的首行 末行 首列 末列
                CellRangeAddressList rangeList = new CellRangeAddressList(1, 65536, k, k);
                // 设置约束
                DataValidation validation = helper.createValidation(constraint, rangeList);
                // 阻止输入非下拉选项的值
                validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
                validation.setShowErrorBox(true);
                validation.setSuppressDropDownArrow(true);
                validation.createErrorBox("提示","此值与单元格定义格式不一致");
                // validation.createPromptBox("填写说明：","填写内容只能为下拉数据集中的单位，其他单位将会导致无法入仓");
                sheet.addValidationData(validation);
            });
        }
    }

自定义注解，用在需要下拉的属性上

*  source ：固定下拉内容
 *  sourceClass：动态下拉内容

import java.lang.annotation.*;
    
    @Documented
    @Target(ElementType.FIELD)
    @Retention(RetentionPolicy.RUNTIME)
    public @interface DropDownSetField {
        
    
        String[] source() default {
        };
        
        Class[] sourceClass() default {
        };
    }

定义获取下拉框数据源的工具类

*  根据注解，获取下拉框的数据源
 *  优先获取静态数据源

import com.xxx.mbm.mom.framework.excel.annotation.DropDownSetField;
    import com.xxx.mbm.mom.framework.excel.handler.DropDownSetInterface;
    import java.util.Arrays;
    import java.util.Optional;
    
    public class DropDownAnnotationUtil {
        
    
        public static String[] resove(DropDownSetField dropDownSetField){
        
            if(!Optional.ofNullable(dropDownSetField).isPresent()){
        
                return null;
            }
    
            // 获取固定下拉信息
            String[] source = dropDownSetField.source();
            if(null != source && source.length > 0){
        
                return source;
            }
    
            // 获取动态的下拉数据
            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();
                    if(null != dynamicSource && dynamicSource.length > 0){
        
                        return dynamicSource;
                    }
                } catch (InstantiationException e) {
        
                    e.printStackTrace();
                } catch (IllegalAccessException e) {
        
                    e.printStackTrace();
                }
            }
            return null;
        }
    }

定义导出excel的传参

public class DropDownExcelDTO {
        
    
        @Schema(description = "文件类")
        Class head;
    
        @Schema(description = "文件属性")
        Field[] fields;
    
        @Schema(description = "文件按名称")
        String fileName;
    
        @Schema(description = "sheet名称")
        String sheetName;
    
        @Schema(description = "输出列表")
        Collection<?> data;
    
        @Schema(description = "文件类型，后缀")
        ContentTypeEnum contentTypeEnum;
    }

定义 ContentType 的枚举类

public enum ContentTypeEnum {
        
    
        LOAD(".load", "text/html"),
        V_123(".123", "application/vnd.lotus-1-2-3"),
        I_3DS(".3ds", "image/x-3ds"),
        V_3G2(".3g2", "video/3gpp"),
        V_3GA(".3ga", "video/3gpp"),
        F_3GP(".3gp", "video/3gpp"),
        V_3GPP(".3gpp", "video/3gpp"),
        X_602(".602", "application/x-t602"),
        X_669(".669", "audio/x-mod"),
        X_7Z(".7z", "application/x-7z-compressed"),
        ARCHIVE(".a", "application/x-archive"),
        AAC(".aac", "audio/mp4"),
        ABW(".abw", "application/x-abiword"),
        ABW_CRASHED(".abw.crashed", "application/x-abiword"),
        ABW_GZ(".abw.gz", "application/x-abiword"),
        AC3(".ac3", "audio/ac3"),
        ACE(".ace", "application/x-ace"),
        ADB(".adb", "text/x-adasrc"),
        ADS(".ads", "text/x-adasrc"),
        AFM(".afm", "application/x-font-afm"),
        AG(".ag", "image/x-applix-graphics"),
        AI(".ai", "application/illustrator"),
        AIF(".aif", "audio/x-aiff"),
        AIFC(".aifc", "audio/x-aiff"),
        AIFF(".aiff", "audio/x-aiff"),
        AL(".al", "application/x-perl"),
        ALZ(".alz", "application/x-alz"),
        AMR(".amr", "audio/amr"),
        ANI(".ani", "application/x-navi-animation"),
        ANIM_1_9(".anim[1-9j]", "video/x-anim"),
        ANX(".anx", "application/annodex"),
        APE(".ape", "audio/x-ape"),
        ARJ(".arj", "application/x-arj"),
        ARW(".arw", "image/x-sony-arw"),
        AS(".as", "application/x-applix-spreadsheet"),
        ASC(".asc", "text/plain"),
        ASR(".asf", "video/x-ms-asf"),
        ASP(".asp", "application/x-asp"),
        ASS(".ass", "text/x-ssa"),
        ASX(".asx", "audio/x-ms-asx"),
        ATOM(".atom", "application/atom+xml"),
        AU(".au", "audio/basic"),
        AVI(".avi", "video/x-msvideo"),
        AW(".aw", "application/x-applix-word"),
        AWB(".awb", "audio/amr-wb"),
        AWK(".awk", "application/x-awk"),
        AXA(".axa", "audio/annodex"),
        AXV(".axv", "video/annodex"),
        BAK(".bak", "application/x-trash"),
        BCPIO(".bcpio", "application/x-bcpio"),
        BDF(".bdf", "application/x-font-bdf"),
        BIB(".bib", "text/x-bibtex"),
        BIN(".bin", "application/octet-stream"),
        BLEND(".blend", "application/x-blender"),
        BLENDER(".blender", "application/x-blender"),
        BMP(".bmp", "image/bmp"),
        BZ(".bz", "application/x-bzip"),
        BZ2(".bz2", "application/x-bzip"),
        C(".c", "text/x-csrc"),
        C_C(".c++", "text/x-c++src"),
        CAB(".cab", "application/vnd.ms-cab-compressed"),
        CB7(".cb7", "application/x-cb7"),
        CBR(".cbr", "application/x-cbr"),
        CBT(".cbt", "application/x-cbt"),
        CBZ(".cbz", "application/x-cbz"),
        CC(".cc", "text/x-c++src"),
        CDF(".cdf", "application/x-netcdf"),
        CDR(".cdr", "application/vnd.corel-draw"),
        CER(".cer", "application/x-x509-ca-cert"),
        CERT(".cert", "application/x-x509-ca-cert"),
        CGM(".cgm", "image/cgm"),
        CHM(".chm", "application/x-chm"),
        CHRT(".chrt", "application/x-kchart"),
        CLASS(".class", "application/x-java"),
        CLS(".cls", "text/x-tex"),
        CMAKE(".cmake", "text/x-cmake"),
        CPIO(".cpio", "application/x-cpio"),
        CPIO_GZ(".cpio.gz", "application/x-cpio-compressed"),
        CPP(".cpp", "text/x-c++src"),
        CR2(".cr2", "image/x-canon-cr2"),
        CRT(".crt", "application/x-x509-ca-cert"),
        CRW(".crw", "image/x-canon-crw"),
        CSSHARP(".cs", "text/x-csharp"),
        CSH(".csh", "application/x-csh"),
        CSS(".css", "text/css"),
        CSSL(".cssl", "text/css"),
        CSV(".csv", "text/csv"),
        CUE(".cue", "application/x-cue"),
        CUR(".cur", "image/x-win-bitmap"),
        CXX(".cxx", "text/x-c++src"),
        DSRC(".d", "text/x-dsrc"),
        DAR(".dar", "application/x-dar"),
        DBF(".dbf", "application/x-dbf"),
        DC(".dc", "application/x-dc-rom"),
        DCL(".dcl", "text/x-dcl"),
        DCM(".dcm", "application/dicom"),
        KODAK_DCR(".dcr", "image/x-kodak-dcr"),
        DBS(".dds", "image/x-dds"),
        DEB(".deb", "application/x-deb"),
        DER(".der", "application/x-x509-ca-cert"),
        DESTOP(".desktop", "application/x-desktop"),
        DIA(".dia", "application/x-dia-diagram"),
        DIFF(".diff", "text/x-patch"),
        DIVX(".divx", "video/x-msvideo"),
        DJV(".djv", "image/vnd.djvu"),
        DJVU(".djvu", "image/vnd.djvu"),
        DNG(".dng", "image/x-adobe-dng"),
        DOC(".doc", "application/msword"),
        DOCBOOK(".docbook", "application/docbook+xml"),
        DOCM(".docm", "application/vnd.openxmlformats-officedocument.wordprocessingml.document"),
        DOCX(".docx", "application/vnd.openxmlformats-officedocument.wordprocessingml.document"),
        DOT(".dot", "text/vnd.graphviz"),
        DSL(".dsl", "text/x-dsl"),
        DTD(".dtd", "application/xml-dtd"),
        DTX(".dtx", "text/x-tex"),
        DV(".dv", "video/dv"),
        DVI(".dvi", "application/x-dvi"),
        DVI_BZ2(".dvi.bz2", "application/x-bzdvi"),
        DVI_GZ(".dvi.gz", "application/x-gzdvi"),
        DWG(".dwg", "image/vnd.dwg"),
        DXF(".dxf", "image/vnd.dxf"),
        E(".e", "text/x-eiffel"),
        EGON(".egon", "application/x-egon"),
        EIF(".eif", "text/x-eiffel"),
        EL(".el", "text/x-emacs-lisp"),
        EMF(".emf", "image/x-emf"),
        EMP(".emp", "application/vnd.emusic-emusic_package"),
        ENT(".ent", "application/xml-external-parsed-entity"),
        EPS(".eps", "image/x-eps"),
        EPS_BZ2(".eps.bz2", "image/x-bzeps"),
        EPS_GZ(".eps.gz", "image/x-gzeps"),
        EPSF(".epsf", "image/x-eps"),
        EPSF_BZ2(".epsf.bz2", "image/x-bzeps"),
        EPSF_GZ(".epsf.gz", "image/x-gzeps"),
        EPSI(".epsi", "image/x-eps"),
        EPSI_BZ2(".epsi.bz2", "image/x-bzeps"),
        EPSI_GZ(".epsi.gz", "image/x-gzeps"),
        EPUB(".epub", "application/epub+zip"),
        ERL(".erl", "text/x-erlang"),
        ES(".es", "application/ecmascript"),
        ETHME(".etheme", "application/x-e-theme"),
        ETX(".etx", "text/x-setext"),
        EXE(".exe", "application/x-ms-dos-executable"),
        EXR(".exr", "image/x-exr"),
        EZ(".ez", "application/andrew-inset"),
        F(".f", "text/x-fortran"),
        F90(".f90", "text/x-fortran"),
        F95(".f95", "text/x-fortran"),
        FB2(".fb2", "application/x-fictionbook+xml"),
        FIG(".fig", "image/x-xfig"),
        FITS(".fits", "image/fits"),
        FL(".fl", "application/x-fluid"),
        FLAC(".flac", "audio/x-flac"),
        FLC(".flc", "video/x-flic"),
        FLI(".fli", "video/x-flic"),
        FLV(".flv", "video/x-flv"),
        FLW(".flw", "application/x-kivio"),
        FO(".fo", "text/x-xslfo"),
        FOR(".for", "text/x-fortran"),
        G3(".g3", "image/fax-g3"),
        GB(".gb", "application/x-gameboy-rom"),
        GBA(".gba", "application/x-gba-rom"),
        GCRD(".gcrd", "text/directory"),
        GED(".ged", "application/x-gedcom"),
        GEDCOM(".gedcom", "application/x-gedcom"),
        GEN(".gen", "application/x-genesis-rom"),
        GF(".gf", "application/x-tex-gf"),
        GG(".gg", "application/x-sms-rom"),
        GIF(".gif", "image/gif"),
        GLADE(".glade", "application/x-glade"),
        GMO(".gmo", "application/x-gettext-translation"),
        GNC(".gnc", "application/x-gnucash"),
        GND(".gnd", "application/gnunet-directory"),
        GNUCASH(".gnucash", "application/x-gnucash"),
        GNUMERIC(".gnumeric", "application/x-gnumeric"),
        GNUPLOT(".gnuplot", "application/x-gnuplot"),
        GP(".gp", "application/x-gnuplot"),
        GPG(".gpg", "application/pgp-encrypted"),
        GPLT(".gplt", "application/x-gnuplot"),
        GRA(".gra", "application/x-graphite"),
        GSF(".gsf", "application/x-font-type1"),
        GSM(".gsm", "audio/x-gsm"),
        GTAR(".gtar", "application/x-tar"),
        GV(".gv", "text/vnd.graphviz"),
        GVP(".gvp", "text/x-google-video-pointer"),
        GZ(".gz", "application/x-gzip"),
        H(".h", "text/x-chdr"),
        HH(".h++", "text/x-c++hdr"),
        HDF(".hdf", "application/x-hdf"),
        HDR_HH(".hh", "text/x-c++hdr"),
        HP(".hp", "text/x-c++hdr"),
        HPGL(".hpgl", "application/vnd.hp-hpgl"),
        HPP(".hpp", "text/x-c++hdr"),
        HS(".hs", "text/x-haskell"),
        HTM(".htm", "text/html"),
        HTML(".html", "text/html"),
        HWP(".hwp", "application/x-hwp"),
        HWT(".hwt", "application/x-hwt"),
        HXX(".hxx", "text/x-c++hdr"),
        ICA(".ica", "application/x-ica"),
        ICB(".icb", "image/x-tga"),
        ICNS(".icns", "image/x-icns"),
        ICO(".ico", "image/vnd.microsoft.icon"),
        ICS(".ics", "text/calendar"),
        IDL(".idl", "text/x-idl"),
        IEF(".ief", "image/ief"),
        IFF(".iff", "image/x-iff"),
        ILBM(".ilbm", "image/x-ilbm"),
        IME(".ime", "text/x-imelody"),
        IMY(".imy", "text/x-imelody"),
        INS(".ins", "text/x-tex"),
        IPTABLES(".iptables", "text/x-iptables"),
        ISO(".iso", "application/x-cd-image"),
        ISO9660(".iso9660", "application/x-cd-image"),
        IT(".it", "audio/x-it"),
        J2K(".j2k", "image/jp2"),
        JAD(".jad", "text/vnd.sun.j2me.app-descriptor"),
        JAR(".jar", "application/x-java-archive"),
        JAVA(".java", "text/x-java"),
        JNG(".jng", "image/x-jng"),
        JNLP(".jnlp", "application/x-java-jnlp-file"),
        JP2(".jp2", "image/jp2"),
        JPC(".jpc", "image/jp2"),
        JPE(".jpe", "image/jpeg"),
        JPEG(".jpeg", "image/jpeg"),
        JPF(".jpf", "image/jp2"),
        JPG(".jpg", "image/jpeg"),
        JPR(".jpr", "application/x-jbuilder-project"),
        JPX(".jpx", "image/jp2"),
        JS(".js", "application/javascript"),
        JSON(".json", "application/json"),
        JSONP(".jsonp", "application/jsonp"),
        K25(".k25", "image/x-kodak-k25"),
        KAR(".kar", "audio/midi"),
        KARBON(".karbon", "application/x-karbon"),
        KDC(".kdc", "image/x-kodak-kdc"),
        KDELNK(".kdelnk", "application/x-desktop"),
        KEXI(".kexi", "application/x-kexiproject-sqlite3"),
        KEXIC(".kexic", "application/x-kexi-connectiondata"),
        KEXIS(".kexis", "application/x-kexiproject-shortcut"),
        KFO(".kfo", "application/x-kformula"),
        KIL(".kil", "application/x-killustrator"),
        KINO(".kino", "application/smil"),
        KML(".kml", "application/vnd.google-earth.kml+xml"),
        KMZ(".kmz", "application/vnd.google-earth.kmz"),
        KON(".kon", "application/x-kontour"),
        KPM(".kpm", "application/x-kpovmodeler"),
        KPR(".kpr", "application/x-kpresenter"),
        KPT(".kpt", "application/x-kpresenter"),
        KRA(".kra", "application/x-krita"),
        KSP(".ksp", "application/x-kspread"),
        KUD(".kud", "application/x-kugar"),
        KWD(".kwd", "application/x-kword"),
        KWT(".kwt", "application/x-kword"),
        LA(".la", "application/x-shared-library-la"),
        LATEX(".latex", "text/x-tex"),
        LDIF(".ldif", "text/x-ldif"),
        LHA(".lha", "application/x-lha"),
        LHS(".lhs", "text/x-literate-haskell"),
        LHZ(".lhz", "application/x-lhz"),
        LOG(".log", "text/x-log"),
        LTX(".ltx", "text/x-tex"),
        LUA(".lua", "text/x-lua"),
        LWO(".lwo", "image/x-lwo"),
        LWOB(".lwob", "image/x-lwo"),
        LWS(".lws", "image/x-lws"),
        LY(".ly", "text/x-lilypond"),
        LYX(".lyx", "application/x-lyx"),
        LZ(".lz", "application/x-lzip"),
        LZH(".lzh", "application/x-lha"),
        LZMA(".lzma", "application/x-lzma"),
        LZO(".lzo", "application/x-lzop"),
        M(".m", "text/x-matlab"),
        M15(".m15", "audio/x-mod"),
        M2T(".m2t", "video/mpeg"),
        M3U(".m3u", "audio/x-mpegurl"),
        M3U8(".m3u8", "audio/x-mpegurl"),
        M4(".m4", "application/x-m4"),
        M4A(".m4a", "audio/mp4"),
        M4B(".m4b", "audio/x-m4b"),
        M4V(".m4v", "video/mp4"),
        MAB(".mab", "application/x-markaby"),
        MAN(".man", "application/x-troff-man"),
        MBOX(".mbox", "application/mbox"),
        MD(".md", "application/x-genesis-rom"),
        MDB(".mdb", "application/vnd.ms-access"),
        MDI(".mdi", "image/vnd.ms-modi"),
        ME(".me", "text/x-troff-me"),
        MED(".med", "audio/x-mod"),
        METALINK(".metalink", "application/metalink+xml"),
        MGP(".mgp", "application/x-magicpoint"),
        MID(".mid", "audio/midi"),
        MIDI(".midi", "audio/midi"),
        MIF(".mif", "application/x-mif"),
        MINIPSF(".minipsf", "audio/x-minipsf"),
        MKA(".mka", "audio/x-matroska"),
        MKV(".mkv", "video/x-matroska"),
        NL(".ml", "text/x-ocaml"),
        MIL(".mli", "text/x-ocaml"),
        MM(".mm", "text/x-troff-mm"),
        MMF(".mmf", "application/x-smaf"),
        MML(".mml", "text/mathml"),
        MNG(".mng", "video/x-mng"),
        MO(".mo", "application/x-gettext-translation"),
        MO3(".mo3", "audio/x-mo3"),
        MOC(".moc", "text/x-moc"),
        MOD(".mod", "audio/x-mod"),
        MOF(".mof", "text/x-mof"),
        MOOV(".moov", "video/quicktime"),
        MOV(".mov", "video/quicktime"),
        MOVIE(".movie", "video/x-sgi-movie"),
        MP(".mp+", "audio/x-musepack"),
        MP2(".mp2", "video/mpeg"),
        MP3(".mp3", "audio/mpeg"),
        MP4(".mp4", "video/mp4"),
        MPC(".mpc", "audio/x-musepack"),
        MPE(".mpe", "video/mpeg"),
        MPEG(".mpeg", "video/mpeg"),
        MPG(".mpg", "video/mpeg"),
        MPGA(".mpga", "audio/mpeg"),
        MPP(".mpp", "audio/x-musepack"),
        MRL(".mrl", "text/x-mrml"),
        MRML(".mrml", "text/x-mrml"),
        MRW(".mrw", "image/x-minolta-mrw"),
        MS(".ms", "text/x-troff-ms"),
        MSI(".msi", "application/x-msi"),
        MSOD(".msod", "image/x-msod"),
        MSX(".msx", "application/x-msx-rom"),
        MTM(".mtm", "audio/x-mod"),
        MUP(".mup", "text/x-mup"),
        MXF(".mxf", "application/mxf"),
        N64(".n64", "application/x-n64-rom"),
        NB(".nb", "application/mathematica"),
        NC(".nc", "application/x-netcdf"),
        NDS(".nds", "application/x-nintendo-ds-rom"),
        NEF(".nef", "image/x-nikon-nef"),
        NES(".nes", "application/x-nes-rom"),
        NFO(".nfo", "text/x-nfo"),
        NOT(".not", "text/x-mup"),
        NSC(".nsc", "application/x-netshow-channel"),
        NSV(".nsv", "video/x-nsv"),
        O(".o", "application/x-object"),
        OBJ(".obj", "application/x-tgif"),
        OCL(".ocl", "text/x-ocl"),
        ODA(".oda", "application/oda"),
        ODB(".odb", "application/vnd.oasis.opendocument.database"),
        ODC(".odc", "application/vnd.oasis.opendocument.chart"),
        ODF(".odf", "application/vnd.oasis.opendocument.formula"),
        ODG(".odg", "application/vnd.oasis.opendocument.graphics"),
        ODI(".odi", "application/vnd.oasis.opendocument.image"),
        ODM(".odm", "application/vnd.oasis.opendocument.text-master"),
        ODP(".odp", "application/vnd.oasis.opendocument.presentation"),
        ODS(".ods", "application/vnd.oasis.opendocument.spreadsheet"),
        ODT(".odt", "application/vnd.oasis.opendocument.text"),
        OGA(".oga", "audio/ogg"),
        OGG(".ogg", "video/x-theora+ogg"),
        OGM(".ogm", "video/x-ogm+ogg"),
        OGV(".ogv", "video/ogg"),
        OGX(".ogx", "application/ogg"),
        OLD(".old", "application/x-trash"),
        OLEO(".oleo", "application/x-oleo"),
        OPML(".opml", "text/x-opml+xml"),
        ORA(".ora", "image/openraster"),
        ORF(".orf", "image/x-olympus-orf"),
        OTC(".otc", "application/vnd.oasis.opendocument.chart-template"),
        OTF(".otf", "application/x-font-otf"),
        OTG(".otg", "application/vnd.oasis.opendocument.graphics-template"),
        OTH(".oth", "application/vnd.oasis.opendocument.text-web"),
        OTP(".otp", "application/vnd.oasis.opendocument.presentation-template"),
        OTS(".ots", "application/vnd.oasis.opendocument.spreadsheet-template"),
        OTT(".ott", "application/vnd.oasis.opendocument.text-template"),
        OWL(".owl", "application/rdf+xml"),
        OXT(".oxt", "application/vnd.openofficeorg.extension"),
        P(".p", "text/x-pascal"),
        P10(".p10", "application/pkcs10"),
        P12(".p12", "application/x-pkcs12"),
        P1B(".p7b", "application/x-pkcs7-certificates"),
        P7S(".p7s", "application/pkcs7-signature"),
        PACK(".pack", "application/x-java-pack200"),
        PAK(".pak", "application/x-pak"),
        PAR2(".par2", "application/x-par2"),
        PAS(".pas", "text/x-pascal"),
        PATHC(".patch", "text/x-patch"),
        PBM(".pbm", "image/x-portable-bitmap"),
        PCD(".pcd", "image/x-photo-cd"),
        PCF(".pcf", "application/x-cisco-vpn-settings"),
        PCF_GZ(".pcf.gz", "application/x-font-pcf"),
        PCF_Z(".pcf.z", "application/x-font-pcf"),
        PCL(".pcl", "application/vnd.hp-pcl"),
        PCX(".pcx", "image/x-pcx"),
        PDB(".pdb", "chemical/x-pdb"),
        PDC(".pdc", "application/x-aportisdoc"),
        PDF(".pdf", "application/pdf"),
        PDF_BZ2(".pdf.bz2", "application/x-bzpdf"),
        PDF_GZ(".pdf.gz", "application/x-gzpdf"),
        PEF(".pef", "image/x-pentax-pef"),
        PEM(".pem", "application/x-x509-ca-cert"),
        PERL(".perl", "application/x-perl"),
        PFA(".pfa", "application/x-font-type1"),
        PFB(".pfb", "application/x-font-type1"),
        PFX(".pfx", "application/x-pkcs12"),
        PGM(".pgm", "image/x-portable-graymap"),
        PGN(".pgn", "application/x-chess-pgn"),
        PGP(".pgp", "application/pgp-encrypted"),
        PHP(".php", "application/x-php"),
        PHP3(".php3", "application/x-php"),
        PHP4(".php4", "application/x-php"),
        PICT(".pict", "image/x-pict"),
        PICT1(".pict1", "image/x-pict"),
        PICT2(".pict2", "image/x-pict"),
        PICKLE(".pickle", "application/python-pickle"),
        PK(".pk", "application/x-tex-pk"),
        PKIPATH(".pkipath", "application/pkix-pkipath"),
        PKR(".pkr", "application/pgp-keys"),
        PL(".pl", "application/x-perl"),
        PLA(".pla", "audio/x-iriver-pla"),
        PLN(".pln", "application/x-planperfect"),
        PLS(".pls", "audio/x-scpls"),
        PM(".pm", "application/x-perl"),
        PNG(".png", "image/png"),
        PNM(".pnm", "image/x-portable-anymap"),
        PNTG(".pntg", "image/x-macpaint"),
        PO(".po", "text/x-gettext-translation"),
        POR(".por", "application/x-spss-por"),
        POT(".pot", "text/x-gettext-translation-template"),
        PPM(".ppm", "image/x-portable-pixmap"),
        PPS(".pps", "application/vnd.ms-powerpoint"),
        PPT(".ppt", "application/vnd.ms-powerpoint"),
        PPTM(".pptm", "application/vnd.openxmlformats-officedocument.presentationml.presentation"),
        PPTX(".pptx", "application/vnd.openxmlformats-officedocument.presentationml.presentation"),
        PPZ(".ppz", "application/vnd.ms-powerpoint"),
        PRC(".prc", "application/x-palm-database"),
        PS(".ps", "application/postscript"),
        PS_BZ2(".ps.bz2", "application/x-bzpostscript"),
        PS_GZ(".ps.gz", "application/x-gzpostscript"),
        PSD(".psd", "image/vnd.adobe.photoshop"),
        PSF(".psf", "audio/x-psf"),
        PSF_GZ(".psf.gz", "application/x-gz-font-linux-psf"),
        PSFLIB(".psflib", "audio/x-psflib"),
        PSID(".psid", "audio/prs.sid"),
        PSW(".psw", "application/x-pocket-word"),
        PW(".pw", "application/x-pw"),
        PY(".py", "text/x-python"),
        PYC(".pyc", "application/x-python-bytecode"),
        PYO(".pyo", "application/x-python-bytecode"),
        QIF(".qif", "image/x-quicktime"),
        QT(".qt", "video/quicktime"),
        QTIF(".qtif", "image/x-quicktime"),
        QTL(".qtl", "application/x-quicktime-media-link"),
        QTVR(".qtvr", "video/quicktime"),
        RA(".ra", "audio/vnd.rn-realaudio"),
        RAF(".raf", "image/x-fuji-raf"),
        RAM(".ram", "application/ram"),
        RAR(".rar", "application/x-rar"),
        RAS(".ras", "image/x-cmu-raster"),
        RAW(".raw", "image/x-panasonic-raw"),
        RAX(".rax", "audio/vnd.rn-realaudio"),
        RB(".rb", "application/x-ruby"),
        RDF(".rdf", "application/rdf+xml"),
        RDFS(".rdfs", "application/rdf+xml"),
        REG(".reg", "text/x-ms-regedit"),
        REJ(".rej", "application/x-reject"),
        RGB(".rgb", "image/x-rgb"),
        RLE(".rle", "image/rle"),
        RM(".rm", "application/vnd.rn-realmedia"),
        RMJ(".rmj", "application/vnd.rn-realmedia"),
        RMM(".rmm", "application/vnd.rn-realmedia"),
        RMS(".rms", "application/vnd.rn-realmedia"),
        RMVB(".rmvb", "application/vnd.rn-realmedia"),
        RMX(".rmx", "application/vnd.rn-realmedia"),
        ROFF(".roff", "text/troff"),
        RP(".rp", "image/vnd.rn-realpix"),
        RPM(".rpm", "application/x-rpm"),
        RSS(".rss", "application/rss+xml"),
        RT(".rt", "text/vnd.rn-realtext"),
        RTF(".rtf", "application/rtf"),
        RTX(".rtx", "text/richtext"),
        RV(".rv", "video/vnd.rn-realvideo"),
        RVX(".rvx", "video/vnd.rn-realvideo"),
        S3M(".s3m", "audio/x-s3m"),
        SAM(".sam", "application/x-amipro"),
        SAMI(".sami", "application/x-sami"),
        SAV(".sav", "application/x-spss-sav"),
        SCM(".scm", "text/x-scheme"),
        SDA(".sda", "application/vnd.stardivision.draw"),
        SDC(".sdc", "application/vnd.stardivision.calc"),
        SDD(".sdd", "application/vnd.stardivision.impress"),
        SDP(".sdp", "application/sdp"),
        SDS(".sds", "application/vnd.stardivision.chart"),
        SDW(".sdw", "application/vnd.stardivision.writer"),
        SGF(".sgf", "application/x-go-sgf"),
        SGI(".sgi", "image/x-sgi"),
        SGL(".sgl", "application/vnd.stardivision.writer"),
        SGM(".sgm", "text/sgml"),
        SGML(".sgml", "text/sgml"),
        SH(".sh", "application/x-shellscript"),
        SHAR(".shar", "application/x-shar"),
        SHN(".shn", "application/x-shorten"),
        SIAG(".siag", "application/x-siag"),
        SID(".sid", "audio/prs.sid"),
        SIK(".sik", "application/x-trash"),
        SIS(".sis", "application/vnd.symbian.install"),
        SISX(".sisx", "x-epoc/x-sisx-app"),
        SIT(".sit", "application/x-stuffit"),
        SIV(".siv", "application/sieve"),
        SK(".sk", "image/x-skencil"),
        SK1(".sk1", "image/x-skencil"),
        SKR(".skr", "application/pgp-keys"),
        SLK(".slk", "text/spreadsheet"),
        SMAF(".smaf", "application/x-smaf"),
        SMC(".smc", "application/x-snes-rom"),
        SMD(".smd", "application/vnd.stardivision.mail"),
        SMF(".smf", "application/vnd.stardivision.math"),
        SMI(".smi", "application/x-sami"),
        SMIL(".smil", "application/smil"),
        SML(".sml", "application/smil"),
        SMS(".sms", "application/x-sms-rom"),
        SND(".snd", "audio/basic"),
        SO(".so", "application/x-sharedlib"),
        SPC(".spc", "application/x-pkcs7-certificates"),
        SPD(".spd", "application/x-font-speedo"),
        SPEC(".spec", "text/x-rpm-spec"),
        SPL(".spl", "application/x-shockwave-flash"),
        SPX(".spx", "audio/x-speex"),
        SQL(".sql", "text/x-sql"),
        SR2(".sr2", "image/x-sony-sr2"),
        SRC(".src", "application/x-wais-source"),
        SRF(".srf", "image/x-sony-srf"),
        SRT(".srt", "application/x-subrip"),
        SSA(".ssa", "text/x-ssa"),
        STC(".stc", "application/vnd.sun.xml.calc.template"),
        STD(".std", "application/vnd.sun.xml.draw.template"),
        STI(".sti", "application/vnd.sun.xml.impress.template"),
        STM(".stm", "audio/x-stm"),
        STW(".stw", "application/vnd.sun.xml.writer.template"),
        STY(".sty", "text/x-tex"),
        SUB(".sub", "text/x-subviewer"),
        SUM(".sun", "image/x-sun-raster"),
        SV4CPIO(".sv4cpio", "application/x-sv4cpio"),
        SV4CRC(".sv4crc", "application/x-sv4crc"),
        SVG(".svg", "image/svg+xml"),
        SVGZ(".svgz", "image/svg+xml-compressed"),
        SWF(".swf", "application/x-shockwave-flash"),
        SXC(".sxc", "application/vnd.sun.xml.calc"),
        SXD(".sxd", "application/vnd.sun.xml.draw"),
        SXG(".sxg", "application/vnd.sun.xml.writer.global"),
        SXI(".sxi", "application/vnd.sun.xml.impress"),
        SXM(".sxm", "application/vnd.sun.xml.math"),
        SXW(".sxw", "application/vnd.sun.xml.writer"),
        SYLK(".sylk", "text/spreadsheet"),
        T(".t", "text/troff"),
        T2T(".t2t", "text/x-txt2tags"),
        TAR(".tar", "application/x-tar"),
        TAR_BZ(".tar.bz", "application/x-bzip-compressed-tar"),
        TAR_BZ2(".tar.bz2", "application/x-bzip-compressed-tar"),
        TAR_GZ(".tar.gz", "application/x-compressed-tar"),
        TAR_LZMA(".tar.lzma", "application/x-lzma-compressed-tar"),
        TAR_LZO(".tar.lzo", "application/x-tzo"),
        TAG_XZ(".tar.xz", "application/x-xz-compressed-tar"),
        TAG_Z(".tar.z", "application/x-tarz"),
        TBZ(".tbz", "application/x-bzip-compressed-tar"),
        TBZ2(".tbz2", "application/x-bzip-compressed-tar"),
        TCL(".tcl", "text/x-tcl"),
        TEX(".tex", "text/x-tex"),
        TEXI(".texi", "text/x-texinfo"),
        TEXINFO(".texinfo", "text/x-texinfo"),
        TGA(".tga", "image/x-tga"),
        TGZ(".tgz", "application/x-compressed-tar"),
        THEME(".theme", "application/x-theme"),
        THEMEPACK(".themepack", "application/x-windows-themepack"),
        TIF(".tif", "image/tiff"),
        TIFF(".tiff", "image/tiff"),
        TK(".tk", "text/x-tcl"),
        TLZ(".tlz", "application/x-lzma-compressed-tar"),
        TNEF(".tnef", "application/vnd.ms-tnef"),
        TNF(".tnf", "application/vnd.ms-tnef"),
        TOC(".toc", "application/x-cdrdao-toc"),
        TORRENT(".torrent", "application/x-bittorrent"),
        TPIC(".tpic", "image/x-tga"),
        TR(".tr", "text/troff"),
        TS(".ts", "application/x-linguist"),
        TSC(".tsv", "text/tab-separated-values"),
        TTA(".tta", "audio/x-tta"),
        TTC(".ttc", "application/x-font-ttf"),
        TTF(".ttf", "application/x-font-ttf"),
        TTX(".ttx", "application/x-font-ttx"),
        TXT(".txt", "text/plain"),
        TXZ(".txz", "application/x-xz-compressed-tar"),
        TZO(".tzo", "application/x-tzo"),
        UFRAW(".ufraw", "application/x-ufraw"),
        UI(".ui", "application/x-designer"),
        UIL(".uil", "text/x-uil"),
        ULT(".ult", "audio/x-mod"),
        UNI(".uni", "audio/x-mod"),
        URI(".uri", "text/x-uri"),
        URL(".url", "text/x-uri"),
        USTAR(".ustar", "application/x-ustar"),
        VALA(".vala", "text/x-vala"),
        VAPI(".vapi", "text/x-vala"),
        VCF(".vcf", "text/directory"),
        VCS(".vcs", "text/calendar"),
        VCT(".vct", "text/directory"),
        VDA(".vda", "image/x-tga"),
        VDH(".vhd", "text/x-vhdl"),
        VDHL(".vhdl", "text/x-vhdl"),
        VIV(".viv", "video/vivo"),
        VIVO(".vivo", "video/vivo"),
        VLC(".vlc", "audio/x-mpegurl"),
        VOB(".vob", "video/mpeg"),
        VOC(".voc", "audio/x-voc"),
        VOR(".vor", "application/vnd.stardivision.writer"),
        VST(".vst", "image/x-tga"),
        WAV(".wav", "audio/x-wav"),
        WAX(".wax", "audio/x-ms-asx"),
        WB1(".wb1", "application/x-quattropro"),
        wb2(".wb2", "application/x-quattropro"),
        WB3(".wb3", "application/x-quattropro"),
        WBMP(".wbmp", "image/vnd.wap.wbmp"),
        WCM(".wcm", "application/vnd.ms-works"),
        WDB(".wdb", "application/vnd.ms-works"),
        WEBM(".webm", "video/webm"),
        WK1(".wk1", "application/vnd.lotus-1-2-3"),
        WK3(".wk3", "application/vnd.lotus-1-2-3"),
        WK4(".wk4", "application/vnd.lotus-1-2-3"),
        WKS(".wks", "application/vnd.ms-works"),
        WMA(".wma", "audio/x-ms-wma"),
        WMF(".wmf", "image/x-wmf"),
        WML(".wml", "text/vnd.wap.wml"),
        WMLS(".wmls", "text/vnd.wap.wmlscript"),
        WMV(".wmv", "video/x-ms-wmv"),
        WMX(".wmx", "audio/x-ms-asx"),
        WP(".wp", "application/vnd.wordperfect"),
        WP4(".wp4", "application/vnd.wordperfect"),
        WP5(".wp5", "application/vnd.wordperfect"),
        WP6(".wp6", "application/vnd.wordperfect"),
        WPD(".wpd", "application/vnd.wordperfect"),
        WPG(".wpg", "application/x-wpg"),
        WPL(".wpl", "application/vnd.ms-wpl"),
        WPP(".wpp", "application/vnd.wordperfect"),
        WPS(".wps", "application/vnd.ms-works"),
        WRI(".wri", "application/x-mswrite"),
        WRL(".wrl", "model/vrml"),
        WV(".wv", "audio/x-wavpack"),
        WVC(".wvc", "audio/x-wavpack-correction"),
        WVP(".wvp", "audio/x-wavpack"),
        WVX(".wvx", "audio/x-ms-asx"),
        X3F(".x3f", "image/x-sigma-x3f"),
        XAC(".xac", "application/x-gnucash"),
        XBEL(".xbel", "application/x-xbel"),
        XBL(".xbl", "application/xml"),
        XBM(".xbm", "image/x-xbitmap"),
        XCF(".xcf", "image/x-xcf"),
        XCF_BZ2(".xcf.bz2", "image/x-compressed-xcf"),
        XCF_GZ(".xcf.gz", "image/x-compressed-xcf"),
        XHTML(".xhtml", "application/xhtml+xml"),
        XI(".xi", "audio/x-xi"),
        XLA(".xla", "application/vnd.ms-excel"),
        XLC(".xlc", "application/vnd.ms-excel"),
        XLD(".xld", "application/vnd.ms-excel"),
        XLF(".xlf", "application/x-xliff"),
        XLIFF(".xliff", "application/x-xliff"),
        XLL(".xll", "application/vnd.ms-excel"),
        XLM(".xlm", "application/vnd.ms-excel"),
        XLS(".xls", "application/vnd.ms-excel"),
        XLSM(".xlsm", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"),
        XLSX(".xlsx", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"),
        XLT(".xlt", "application/vnd.ms-excel"),
        XLW(".xlw", "application/vnd.ms-excel"),
        XM(".xm", "audio/x-xm"),
        XMF(".xmf", "audio/x-xmf"),
        XMI(".xmi", "text/x-xmi"),
        XML(".xml", "application/xml"),
        XPM(".xpm", "image/x-xpixmap"),
        XPS(".xps", "application/vnd.ms-xpsdocument"),
        XSL(".xsl", "application/xml"),
        XSLFO(".xslfo", "text/x-xslfo"),
        XSLT(".xslt", "application/xml"),
        XSPF(".xspf", "application/xspf+xml"),
        XUL(".xul", "application/vnd.mozilla.xul+xml"),
        XWD(".xwd", "image/x-xwindowdump"),
        XYZ(".xyz", "chemical/x-pdb"),
        XZ(".xz", "application/x-xz"),
        W2P(".w2p", "application/w2p"),
        Z(".z", "application/x-compress"),
        ZABW(".zabw", "application/x-abiword"),
        ZIP(".zip", "application/zip");
    
    
        /**
         * 后缀
         */
        private String surffix;
    
        /**
         * 类型
         */
        private String centType;
    
        ContentTypeEnum(String surffix, String centType) {
        
            this.surffix = surffix;
            this.centType = centType;
        }
    
        public String getSurffix() {
        
            return surffix;
        }
    
        public void setSurffix(String surffix) {
        
            this.surffix = surffix;
        }
    
        public String getCentType() {
        
            return centType;
        }
    
        public void setCentType(String centType) {
        
            this.centType = centType;
        }
    
    }

重点来了  
定义导出方法

*  write：调用 EasyExcel.write 方法，根据 SampleWriteHandler 修改 excel 的返回样式
 *  getFields：根据 DropDownAnnotationUtil 获取下拉数据源
 *  getOutputStream：定义输出流，如文件名，后缀等

> 这里是引用

import com.alibaba.excel.EasyExcel;
    import com.alibaba.excel.ExcelWriter;
    import com.alibaba.excel.write.metadata.WriteSheet;
    import com.xxx.mbm.mom.framework.excel.annotation.DropDownSetField;
    import com.xxx.mbm.mom.framework.excel.enums.ContentTypeEnum;
    import com.xxx.mbm.mom.framework.excel.handler.SampleWriteHandler;
    import com.xxx.mbm.mom.framework.excel.vo.DropDownExcelDTO;
    import javax.servlet.http.HttpServletResponse;
    import java.io.IOException;
    import java.io.OutputStream;
    import java.lang.reflect.Field;
    import java.util.HashMap;
    import java.util.Map;
    
    public class ExcelDropDownUtil {
        
    
        public static void write(DropDownExcelDTO dto) throws IOException {
        
            String fileName = URLEncoder.encode(dto.getFileName(), UTF8);
            String sheetName = URLEncoder.encode(dto.getSheetName(), UTF8);
            ServletRequestAttributes servletRequestAttributes =  (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
            HttpServletResponse response = servletRequestAttributes.getResponse();
    
            OutputStream outputStream = getOutputStream(response, dto.getContentTypeEnum(), fileName);
            Map<Integer, String[]> map = getFields( dto.getFields());
            ExcelWriter excelWriter = EasyExcel.write(outputStream,
                    dto.getHead())
                    .registerWriteHandler(new SampleWriteHandler(map)).build();
    
            WriteSheet sheet = EasyExcel.writerSheet(0, sheetName).build();
            excelWriter.write(dto.getData(), sheet);
            excelWriter.finish();
            outputStream.close();
        }
     
        private static OutputStream getOutputStream(HttpServletResponse response, ContentTypeEnum contentTypeEnum, String fileName) throws IOException {
        
            OutputStream outputStream = response.getOutputStream();
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + contentTypeEnum.getSurffix());
            response.setContentType(contentTypeEnum.getCentType());// 定义输出类型
            response.setCharacterEncoding("UTF-8");
            return outputStream;
        }
     
        private static Map<Integer, String[]> getFields(Field[] fields) throws IOException {
        
            Map<Integer, String[]> map = new HashMap<>();
            Field field = null;
            for (int i = 0; i < fields.length; i++) {
        
                field = fields[i];
                // 解析注解信息
                DropDownSetField dropDownSetField = field.getAnnotation(DropDownSetField.class);
                if (null != dropDownSetField) {
        
                    String[] sources = DropDownAnnotationUtil.resove(dropDownSetField);
                    if (null != sources && sources.length > 0) {
        
                        map.put(i, sources);
                    }
                }
            }
            return map;
        }
    }

定义接口，用于动态下拉

public interface DropDownSetInterface {
        
    
        String[] getSource();
    
    }

实现接口

public class DropDownSetImpl implements DropDownSetInterface {
        
    
        @Override
        public String[] getSource() {
        
    		return new String[]{
        "一班", "二班", "三班" };
        }
    }

实体使用注解

@Data
    public class UserModel {
        
    
        @ExcelProperty(value = "名称")
        private String name;
    
        @ExcelProperty(value = "性别")
        @DropDownSetField(source = {
        "男","女"})
        private String type;
    
        @ExcelProperty(value = "班级")
        @DropDownSetField(sourceClass = DropDownSetImpl.class)
        private String unit;
    
    }

导出使用例子

@GetMapping("/export/template")
        @Operation(summary = "导出下载模板")
        public void exportTemplate() throws IOException {
        
            List<UserModel> list = new ArrayList<>();
            DropDownExcelDTO dto = new DropDownExcelDTO(
                    UserModel.class,
                    UserModel.class.getDeclaredFields(),
                    "文件名",
                    "sheet名",
                    list,
                    ContentTypeEnum.XLSX);
            ExcelDropDownUtil.write(dto);
        }

完成

[d0a2416ef023427fb8bf13b5641ea534.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/6223bbc001c44165889e17381ca9c98a.png
[7e024e7f9a6e4b719ed3757ee136699d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/df6f80ddad3a4dc9bc24a2bad00e5e70.png