一，前端vue部分

        1.请求工具封装

                

import request from '@/utils/request'
import Qs from 'qs'

export function exportExcel(url,param,data) {
  return request({
    url: url,
    method: "post",
    params: param,
    data,
    responseType: 'blob',//将文件流转成blob对象
    noErrorMsg: true
  });
}

 2.发起导出模板请求

        

 this.saveButtonFlag=true
      if(this.$store.state.user.userInfo.userType==102){
        //这里需要从后端接收文件流，并把模板下载出来
        //根据自己的业务填写url地址和请求参数    
        const url = "/fittings/export";
        exportExcel(url,{type:102,platformId:this.$store.state.user.userInfo.platformId}).then((res) => {
          // res就是接口返回的文件流了; 定义请求的接口时需标明responseType: 'blob',//将文件流转成blob对象
          let objectUrl = window.URL.createObjectURL(new Blob([res]));
          const elink = document.createElement('a');
          elink.download = `${this.tableTitle}.xlsx`; //下载文件名称,
          elink.style.display = 'none';
          elink.href = objectUrl;
          document.body.appendChild(elink);
          elink.click();
          document.body.removeChild(elink);
          window.URL.revokeObjectURL(elink.href); //释放URL 对象
          this.saveButtonFlag=false
        })
        .catch((error) => {
          console.log(error);
        });

 二,后端部分

1.引入easyExcel依赖

        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>4.1.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>4.1.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>4.1.0</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.0-beta2</version>
        </dependency>

2.后端接口实现

    @PostMapping("/export")
    public Result export(HttpServletResponse response,Integer type,@RequestParam(required = false)Long platformId){
        try {
         
            ExportParams exportParams = new ExportParams();
            exportParams.setType(ExcelType.XSSF);
            //设置响应编码
            response.setCharacterEncoding("utf-8");
            //设置文件名的编码格式,防止文件名乱码
            String fileName = URLEncoder.encode("writeWithIndex", "UTF-8");
            //固定写法,设置响应头
            response.setHeader("Content-disposition", "attachment;filename="+ fileName + ".xlsx");
            //对专业类别，配件类型，二级平台设置下拉框
            //这个是父级下拉框数据集合，数据根据自己业务查询数据库
            List<CategoryExtend> majorNoPage = categoryService.listMajorNoPage();
            //这个是普通下拉框要填充的数据，数据根据自己业务查询数据库
            List<Platform> platformList = platformService.findAll();
            
           //这个是子集下拉框数据集合，数据根据自己业务查询数据库
                List<CategoryExtend> all  = categoryService.findAlls();
//               list有一条我添加的导入样式数据，也可以为null
                EasyExcel.write(response.getOutputStream(), FittingsExcel.class).autoCloseStream(Boolean.FALSE).registerWriteHandler(new SheetWriteHandlerForCascade(majorNoPage,all,CategoryEnum.FITTINGS_TYPE.getCode(),platformList)).sheet("配件信息").doWrite(list);
          

          return   ResultGenerator.genSuccessResult();
        }catch (Exception e){
            e.getCause().printStackTrace();
            return ResultGenerator.genFailResult("模板获取失败");
        }
    }

3.导出配置类

package com.jfl.onsiterepair.core;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.ArrayUtil;
import com.alibaba.excel.util.CollectionUtils;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import com.jfl.onsiterepair.enumeration.CategoryEnum;
import com.jfl.onsiterepair.model.Category;
import com.jfl.onsiterepair.model.Company;
import com.jfl.onsiterepair.model.Platform;
import com.jfl.onsiterepair.model.Template;
import com.jfl.onsiterepair.model.extend.CategoryExtend;
import com.jfl.onsiterepair.model.extend.UserCompanyExtend;
import com.jfl.onsiterepair.service.CategoryPlatformService;
import com.jfl.onsiterepair.service.CategoryService;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;

import javax.annotation.Resource;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.stream.Collectors;

public class SheetWriteHandlerForCascade implements SheetWriteHandler {

    private static final int XLS_MAX_ROW = 60000;
  

  
    Map<String,Object> areaMap;
    private String areaSheetName = "area";
    private int provinceIndex=1;

    private List<CategoryExtend> majorList;
    private List<CategoryExtend> categoryExtendList;
    private Integer childType;
    private List<Platform> platformList;
    private List<Company> companyList;
    private List<UserCompanyExtend> userList;
    private List<Template> templateList;

  



 

    public SheetWriteHandlerForCascade(List<CategoryExtend> majorList, List<CategoryExtend> categoryExtendList, Integer childType, List<Platform> platformList) {
        this.majorList = majorList;
        this.categoryExtendList = categoryExtendList;
        this.childType = childType;
        this.platformList = platformList;
    }


    private char[] alphabet = new char[]{'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L',
            'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'};

    public String getAreaSheetName() {
        return areaSheetName;
    }

    public void setAreaSheetName(String areaSheetName) {
        this.areaSheetName = areaSheetName;
    }

    public int getProvinceIndex() {
        return provinceIndex;
    }

    public void setProvinceIndex(int provinceIndex) {
        this.provinceIndex = provinceIndex;
    }

    public char[] getAlphabet() {
        return alphabet;
    }

    public void setAlphabet(char[] alphabet) {
        this.alphabet = alphabet;
    }

    public Map<String, Object> getAreaMap() {
        return areaMap;
    }

    public void setAreaMap(Map<String, Object> areaMap) {
        this.areaMap = areaMap;
    }

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


    }

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

        DataValidationHelper helper = writeSheetHolder.getSheet().getDataValidationHelper();
        //获取一个workbook
        Workbook workbook = writeWorkbookHolder.getWorkbook();

         //获取父节点 备品配件级联，通过关联数据递归获取每个父级下的子集合
        List<CategoryExtend> collect = majorList.stream().filter(m -> m.getpCategCode() == null).peek(
                (m) -> m.setChild(getChildrens(m, categoryExtendList,childType))
        ).collect(Collectors.toList());
        HashMap<String, List<String>> map = new HashMap<>();
        //这个为每个父级数据填充子数据
        for (CategoryExtend categoryExtend : collect) {
            map.put(categoryExtend.getCategName(),categoryExtend.getChild().stream().map(CategoryExtend::getCategName).collect(Collectors.toList()));

        }
        List<String> majorNameList = majorList.stream().map(CategoryExtend::getCategName).collect(Collectors.toList());
        List<String> allNameList = categoryExtendList.stream().map(Category::getCategName).collect(Collectors.toList());
        createHideSheetHSSF(workbook, majorNameList, allNameList, map);
        Sheet sheet=writeSheetHolder.getSheet();
        //处理对应级联关系

            //专业
            CellRangeAddressList provRangeAddressList = new CellRangeAddressList(1, XLS_MAX_ROW, provinceIndex, provinceIndex);
            DataValidationConstraint constraint = helper.createFormulaListConstraint(areaSheetName);
            DataValidation provinceDataValidation = helper.createValidation(constraint, provRangeAddressList);
            provinceDataValidation.createErrorBox("error", "请选择正确的专业类别");
            //配件类型
            CellRangeAddressList cityRange = new CellRangeAddressList(0, XLS_MAX_ROW, provinceIndex+1, provinceIndex+1);
            DataValidation cityValidation = helper.createValidation(helper.createFormulaListConstraint("INDIRECT($"+getExcelColumn(provinceIndex)+"1)"), cityRange);
            sheet.addValidationData(provinceDataValidation);
            sheet.addValidationData(cityValidation);
            //二级平台
            Map<Integer, String[]> mapDropDown = new HashMap<>();
            if(CollUtil.isNotEmpty(platformList)){
                List<String> platformNameList = platformList.stream().map(Platform::getPlatformName).collect(Collectors.toList());
                String[] platformArr = ArrayUtil.toArray(platformNameList, String.class);
                if(childType.equals(CategoryEnum.FITTINGS_TYPE.getCode())){
                    mapDropDown.put(8,platformArr);
                }
                
            }


     
                DataValidationHelper helperP = sheet.getDataValidationHelper();// 设置下拉框

                for (Map.Entry<Integer, String[]> entry : mapDropDown.entrySet()) {
                    // 起始行、终止行、起始列、终止列
                    CellRangeAddressList addressList = new CellRangeAddressList(1, 1000, entry.getKey(), entry.getKey());
                    // 设置下拉框数据
                    DataValidationConstraint constraintP = helperP.createExplicitListConstraint(entry.getValue());
                    DataValidation dataValidation = helperP.createValidation(constraintP, addressList);
                    // 处理Excel兼容性问题
                    if (dataValidation instanceof XSSFDataValidation) {
                        dataValidation.setSuppressDropDownArrow(true);
                        dataValidation.setShowErrorBox(true);
                    } else {
                        dataValidation.setSuppressDropDownArrow(false);
                    }
                    sheet.addValidationData(dataValidation);
                }


        





    }

    private List<CategoryExtend> getChildrens(CategoryExtend item, List<CategoryExtend> all,Integer childType) {
        List<CategoryExtend> children = all.stream().filter(m -> {
            return Objects.equals(m.getpCategCode(), item.getCategCode())&&m.getClassify().equals(childType);

        }).map(
                (m) -> {
                    m.setChild(getChildrens(m, all,childType));
                    return m;
                }
        ).collect(Collectors.toList());
        return children;


    }

    public void createHideSheetHSSF(Workbook wb, List<String> provinceArr, List<String> areaFatherNameArr, Map<String, List<String>> areaMap) {


        // 创建sheet，突破下拉框255的限制
        //定义sheet的名称
        //1.创建一个隐藏的sheet 名称为 proviceSheet
        Sheet sheet=wb.createSheet(areaSheetName);

        //2.循环赋值（为了防止下拉框的行数与隐藏域的行数相对应，将隐藏域加到结束行之后）
        for (int i = 0, length = provinceArr.size(); i < length; i++) {
            // i:表示你开始的行数  0表示你开始的列数
            sheet.createRow(i).createCell(0).setCellValue(provinceArr.get(i));
        }
        Name category1Name = wb.createName();
        category1Name.setNameName(areaSheetName);
        //4 $A$1:$A$N代表 以A列1行开始获取N行下拉数据
        category1Name.setRefersToFormula(areaSheetName + "!$A$1:$A$" + (provinceArr.size()));


        int rowId = provinceArr.size();

        // 将具体的数据写入到每一行中，行开头为父级区域，后面是子区域。
        for (int i = 0; i < areaFatherNameArr.size(); i++) {
            String key = areaFatherNameArr.get(i);
            List<String> son = areaMap.get(key);
            Row prow = sheet.createRow(rowId++);
            prow.createCell(0).setCellValue(key);
            System.out.println(key);
            if(!CollectionUtils.isEmpty(son)){
                for (int j = 0; j < son.size(); j++) {
                    Cell cell = prow.createCell(j + 1);
                    cell.setCellValue(son.get(j));
                }
            }

            if(CollectionUtils.isEmpty(son)){
                continue;
            }
            // 添加名称管理器
            String range = getRange(1, rowId, son.size());
            Name name = wb.createName();
            //key不可重复
            name.setNameName(key);
            String formula = areaSheetName+"!" + range;
            name.setRefersToFormula(formula);
        }
        wb.setSheetHidden(wb.getSheetIndex(areaSheetName), true);
    }
    /**
     * 计算formula
     *
     * @param offset   偏移量，如果给0，表示从A列开始，1，就是从B列
     * @param rowId    第几行
     * @param colCount 一共多少列
     * @return 如果给入参 1,1,10. 表示从B1-K1。最终返回 $B$1:$K$1
     */
    public String getRange(int offset, int rowId, int colCount) {
        char start = (char) ('A' + offset);
        if (colCount <= 25) {
            char end = (char) (start + colCount - 1);
            return "$" + start + "$" + rowId + ":$" + end + "$" + rowId;
        } else {
            char endPrefix = 'A';
            char endSuffix = 'A';
            if ((colCount - 25) / 26 == 0 || colCount == 51) {// 26-51之间，包括边界（仅两次字母表计算）
                if ((colCount - 25) % 26 == 0) {// 边界值
                    endSuffix = (char) ('A' + 25);
                } else {
                    endSuffix = (char) ('A' + (colCount - 25) % 26 - 1);
                }
            } else {// 51以上
                if ((colCount - 25) % 26 == 0) {
                    endSuffix = (char) ('A' + 25);
                    endPrefix = (char) (endPrefix + (colCount - 25) / 26 - 1);
                } else {
                    endSuffix = (char) ('A' + (colCount - 25) % 26 - 1);
                    endPrefix = (char) (endPrefix + (colCount - 25) / 26);
                }
            }
            return "$" + start + "$" + rowId + ":$" + endPrefix + endSuffix + "$" + rowId;
        }
    }
    /**
     * 将数字列转化成为字母列
     *
     * @param num
     * @return
     */
    private String getExcelColumn(int num) {
        String column = "";
        int len = alphabet.length - 1;
        int first = num / len;
        int second = num % len;
        if (num <= len) {
            column = alphabet[num] + "";
        } else {
            column = alphabet[first - 1] + "";
            if (second == 0) {
                column = column + alphabet[len] + "";
            } else {
                column = column + alphabet[second - 1] + "";
            }
        }
        return column;
    }




}

导出实体类

import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;

@ExcelIgnoreUnannotated
public class FittingsXlsx extends Fittings {

    private String typeCode;
    @ExcelProperty(value = "二级平台", index = 8)
    @ColumnWidth(30)
    private String platformNames;

    //用于判断导入数据的用户类型
    private Integer userType;

    public Integer getUserType() {
        return userType;
    }

    public void setUserType(Integer userType) {
        this.userType = userType;
    }

    @Override
    public String getTypeCode() {
        return typeCode;
    }

    @Override
    public void setTypeCode(String typeCode) {
        this.typeCode = typeCode;
    }

    public String getPlatformNames() {
        return platformNames;
    }

    public void setPlatformNames(String platformNames) {
        this.platformNames = platformNames;
    }
}

@ExcelIgnoreUnannotated
public class Fittings {
    /**
     * 配件id
     */
    @Id
    @Column(name = "fittings_id")
    @GeneratedValue(generator="JDBC")
    private Long fittingsId;

    /**
     * 配件名称
     */
    @ExcelProperty(value = "配件名称", index = 0)
    @ColumnWidth(20)
    @ApiModelProperty(value="配件名称",example="xxx")
    private String name;

    /**
     * 配件类型
     */
//    @DynamicSelectData(message = "级联子列请填写给定的选项", handler = {ChildConstrainSourceHandler.class})
//    @ExcelSelect(parentColumn = "专业类别",handler = ExcelChildSourceHandler.class)
    @ExcelProperty(value = "配件类型", index = 2)
    @ColumnWidth(20)
    @ApiModelProperty(value="配件类型",example="xxx")
    @Column(name = "type_code")
    private String typeCode;

    /**
     * 配件型号
     */
    @ExcelProperty(value = "型号", index = 3)
    @ColumnWidth(20)
    @ApiModelProperty(value="配件型号",example="xxx")
    @Column(name = "model_no")
    private String modelNo;

    /**
     * 品牌
     */
    @ExcelProperty(value = "品牌", index = 4)
    @ColumnWidth(20)
    @ApiModelProperty(value="品牌",example="xxx")
    private String brand;

    /**
     * 产地
     */
    @ExcelProperty(value = "产地", index = 5)
    @ColumnWidth(20)
    @ApiModelProperty(value="产地",example="xxx")
    @Column(name = "origin_place")
    private String originPlace;

    /**
     * 配件图片
     */
    @ApiModelProperty(value="配件图片",example="xxx")
    @Column(name = "img_url")
    private String imgUrl;

    /**
     * 供应商
     */
    @ExcelProperty(value = "厂家", index = 6)
    @ColumnWidth(20)
    @ApiModelProperty(value="供应商",example="xxx")
    private String supplier;

    /**
     * 创建人
     */
    @ApiModelProperty(value="创建人id",example = "1")
    @Column(name = "create_user_id")
    private Long createUserId;

    /**
     * 创建时间
     */
    @ApiModelProperty(value="创建时间",example="xxx")
    @Column(name = "create_time")
    private Date createTime;

    /**
     * 省份
     */
    @ApiModelProperty(value="省份",example="xxx")
    private String province;

    /**
     * 城市
     */
    @ApiModelProperty(value="城市",example="xxx")
    private String city;

    /**
     * 区域
     */
    @ApiModelProperty(value="区域",example="xxx")
    private String region;

    /**
     * 状态
     */
    @ApiModelProperty(value="状态")
    private Boolean state;

    /**
     * 总库存
     */
    @ApiModelProperty(value="总库存=网点库存+企业库存+二级平台库存",example="12")
    private Integer quantity;

    /**
     * 配件介绍
     */
    @ExcelProperty(value = "配件介绍", index = 7)
    @ColumnWidth(20)
    @ApiModelProperty(value="配件介绍",example="xxx")
    private String intro;

    /**
     * 专业类别
     */
//    @DynamicSelectData(message = "父列请填写给定的选项", handler = {ChildConstrainSourceHandler.class})
//    @ExcelSelect( handler = ExcelParentSourceHandler.class)
    @ExcelProperty(value = "专业类别", index = 1)
    @ColumnWidth(20)
    @ApiModelProperty(value="专业类别",example="xxx")
    @Column(name = "major_type_code")
    private String majorTypeCode;

    public String getProvince() {
        return province;
    }

    public void setProvince(String province) {
        this.province = province;
    }

    public String getCity() {
        return city;
    }

    public void setCity(String city) {
        this.city = city;
    }

    public String getRegion() {
        return region;
    }

    public void setRegion(String region) {
        this.region = region;
    }

    public String getMajorTypeCode() {
        return majorTypeCode;
    }

    public void setMajorTypeCode(String majorTypeCode) {
        this.majorTypeCode = majorTypeCode;
    }

    /**
     * 获取配件id
     *
     * @return fittingsId - 配件id
     */
    public Long getFittingsId() {
        return fittingsId;
    }

    /**
     * 设置配件id
     *
     * @param fittingsId 配件id
     */
    public void setFittingsId(Long fittingsId) {
        this.fittingsId = fittingsId;
    }

    /**
     * 获取配件名称
     *
     * @return name - 配件名称
     */
    public String getName() {
        return name;
    }

    /**
     * 设置配件名称
     *
     * @param name 配件名称
     */
    public void setName(String name) {
        this.name = name;
    }

    /**
     * 获取配件类型
     *
     * @return typeCode - 配件类型
     */
    public String getTypeCode() {
        return typeCode;
    }

    /**
     * 设置配件类型
     *
     * @param typeCode 配件类型
     */
    public void setTypeCode(String typeCode) {
        this.typeCode = typeCode;
    }

    /**
     * 获取配件型号
     *
     * @return modelNo - 配件型号
     */
    public String getModelNo() {
        return modelNo;
    }

    /**
     * 设置配件型号
     *
     * @param modelNo 配件型号
     */
    public void setModelNo(String modelNo) {
        this.modelNo = modelNo;
    }

    /**
     * 获取品牌
     *
     * @return brand - 品牌
     */
    public String getBrand() {
        return brand;
    }

    /**
     * 设置品牌
     *
     * @param brand 品牌
     */
    public void setBrand(String brand) {
        this.brand = brand;
    }

    /**
     * 获取产地
     *
     * @return originPlace - 产地
     */
    public String getOriginPlace() {
        return originPlace;
    }

    /**
     * 设置产地
     *
     * @param originPlace 产地
     */
    public void setOriginPlace(String originPlace) {
        this.originPlace = originPlace;
    }

    /**
     * 获取配件图片
     *
     * @return imgUrl - 配件图片
     */
    public String getImgUrl() {
        return imgUrl;
    }

    /**
     * 设置配件图片
     *
     * @param imgUrl 配件图片
     */
    public void setImgUrl(String imgUrl) {
        this.imgUrl = imgUrl;
    }

    /**
     * 获取供应商
     *
     * @return supplier - 供应商
     */
    public String getSupplier() {
        return supplier;
    }

    /**
     * 设置供应商
     *
     * @param supplier 供应商
     */
    public void setSupplier(String supplier) {
        this.supplier = supplier;
    }

    /**
     * 获取创建人
     *
     * @return createUserId - 创建人
     */
    public Long getCreateUserId() {
        return createUserId;
    }

    /**
     * 设置创建人
     *
     * @param createUserId 创建人
     */
    public void setCreateUserId(Long createUserId) {
        this.createUserId = createUserId;
    }

    /**
     * 获取创建时间
     *
     * @return createTime - 创建时间
     */
    public Date getCreateTime() {
        return createTime;
    }

    /**
     * 设置创建时间
     *
     * @param createTime 创建时间
     */
    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }

    /**
     * 获取状态
     *
     * @return state - 状态
     */
    public Boolean getState() {
        return state;
    }

    /**
     * 设置状态
     *
     * @param state 状态
     */
    public void setState(Boolean state) {
        this.state = state;
    }

    /**
     * 获取总库存
     *
     * @return quantity - 总库存
     */
    public Integer getQuantity() {
        return quantity;
    }

    /**
     * 设置总库存
     *
     * @param quantity 总库存
     */
    public void setQuantity(Integer quantity) {
        this.quantity = quantity;
    }

    /**
     * 获取配件介绍
     *
     * @return intro - 配件介绍
     */
    public String getIntro() {
        return intro;
    }

    /**
     * 设置配件介绍
     *
     * @param intro 配件介绍
     */
    public void setIntro(String intro) {
        this.intro = intro;
    }
}

4.实现效果

 

5. 导出涉及业务，有看不清楚的地方可以联系我或评论区指出