EasyExcel 动态下拉选择

引入easyexcel jar

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

1.easyExcel下拉处理器

import cn.hutool.core.util.ArrayUtil;
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.List;
import java.util.Map;

/**
 * easyExcel下拉处理器
 */
public class SpinnerWriteHandler implements SheetWriteHandler {


    private Map<Integer, List<String>> dropDownMap;

    public SpinnerWriteHandler(Map<Integer, List<String>> dropDownMap) {
        this.dropDownMap = dropDownMap;
    }

    public SpinnerWriteHandler() {
    }

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

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

        Sheet sheet = writeSheetHolder.getSheet();
        ///开始设置下拉框
        DataValidationHelper helper = sheet.getDataValidationHelper();
        //设置下拉框
        dropDownMap.forEach((k, v) -> {
            /*起始行、终止行、起始列、终止列**/
            CellRangeAddressList addressList = new CellRangeAddressList(1, 65535, k, k);
            /*设置下拉框数据**/
            DataValidationConstraint constraint = helper.createExplicitListConstraint(ArrayUtil.toArray(v, String.class));
            DataValidation dataValidation = helper.createValidation(constraint, addressList);
            sheet.addValidationData(dataValidation);
        });
    }
}

2. service impl

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
import com.google.common.collect.ImmutableMap;
import com.google.common.collect.Lists;
import org.song.spike.sso.dto.EasyExcelImportDTO;
import org.song.spike.sso.service.EasyExcelService;
import org.song.spike.sso.utils.excel.SpinnerWriteHandler;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
@Service
public class EasyExcelServiceImpl implements EasyExcelService {
    public void downloadQuoteTemplate(HttpServletResponse response) {
        String fileName = "项目报价模板";
        try {
            fileName = URLEncoder.encode(fileName, "UTF-8");
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf8");
            response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".xlsx");
            response.setHeader("Pragma", "public");
            response.setHeader("Cache-Control", "no-store");
            response.addHeader("Cache-Control", "max-age=0");

            List<String> requied_product = Arrays.asList("冰箱","空调");
            List<String> refrigeration_unit = Arrays.asList("台","个");

            Map<Integer, List<String>> dropDownMap = ImmutableMap.of(
                    0, requied_product,
                    4, refrigeration_unit);


            String sheetName = "Data Export";
            ExcelWriterSheetBuilder sheetBuilder = EasyExcel.write(response.getOutputStream(), EasyExcelImportDTO.class).excelType(ExcelTypeEnum.XLSX).sheet(sheetName);
            //此方法会将在指定的Excel表格处增加下拉
            sheetBuilder.registerWriteHandler(new SpinnerWriteHandler(dropDownMap));
            sheetBuilder.doWrite(Lists.newArrayList());
        } catch (UnsupportedEncodingException var6) {
            var6.printStackTrace();
        } catch (IOException var7) {
            var7.printStackTrace();
        }
    }
}

3. service 

import javax.servlet.http.HttpServletResponse;

public interface EasyExcelService {
    void downloadQuoteTemplate(HttpServletResponse response);
}

4. controller

import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
@RestController
@RequestMapping("/easyExcel")
public class EasyExcelController {
    @Resource
    private EasyExcelService easyExcelService;
    @GetMapping("/download")
    public void downloadQuoteTemplate(HttpServletResponse response) {
        easyExcelService.downloadQuoteTemplate(response);
    }
}

5. excel dto

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import lombok.Data;

/**
 * 项目方案报价导入实体类
 */
@Data
public class EasyExcelImportDTO {


    //产品类型
    @ExcelProperty(value = "*产品类型", index = 0)
    @ColumnWidth(30)
    private String productType;

    //系列
    @ExcelProperty(value = "*系列", index = 1)
    @ColumnWidth(30)
    private String series;

    //出口型号
    @ExcelProperty(value = "*出口型号", index = 2)
    @ColumnWidth(30)
    private String exportModel;

    //描述
    @ExcelProperty(value = "描述", index = 3)
    @ColumnWidth(30)
    private String exportDesc;

    //单位
    @ExcelProperty(value = "*单位", index = 4)
    @ColumnWidth(30)
    private String unit;

    //数量
    @ExcelProperty(value = "*数量", index = 8)
    @ColumnWidth(30)
    private Integer quantity;

}