EasyExcel导出带有下拉框的表头模板

1.接口层

    @ApiOperation("其他费用配置-模版下载")
    @GetMapping("/downloadTemplate")
    public void downloadTemplate(HttpServletResponse response) {
        try {
            List<String> list = Arrays.asList("集团", "平台", "部门", "店铺", "年月", "币别", "费用项目", "金额",
                    "分摊类型", "分摊依据", "订单号", "备注"
            );
            ExcelUtils.exportTemplate(response, ExcelUtils.getSimpleHead(list), new SheetWriteHandler() {
                @Override
                public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
                //费用项目的下拉框的值
                    String[] expenseItem = new String[]{"退款", "库存损益", "库存清理", "商品成本", "平台费", "收款费用", "物流成本", "包材费", "资金成本", "营销费",
                            "刊登费", "vat费", "炒分", "云主机及vps", "vps及其他", "固定服务费分摊", "GBC罚款", "销毁成本", "其他款项", "罚款或暂扣货款金额", "收款费及其他", "服务费"};
                     //分摊类型的下拉框的值
                    String[] shareType = new String[]{"店铺承担", "部门承担", "集团承担", "平台承担", "公司承担"};
                    //分摊依据的下拉框的值
                    String[] shareGist = new String[]{"总收入", "商品成本", "计算重量", "订单系数", "订单量"};
                    Map<Integer, String[]> mapDropDown = new HashMap<>();
                    mapDropDown.put(6, expenseItem);
                    mapDropDown.put(8, shareType);
                    mapDropDown.put(9, shareGist);
                    ExcelUtils.sheetConfig(writeSheetHolder, mapDropDown);
                }
            }, "其他费用配置", "其他费用配置");
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

2.ExcelUtils

  //带下拉的模板
    public static <T> void exportTemplate(HttpServletResponse response, List<List<String>> head, SheetWriteHandler handler, String fileName, String sheetName) throws IOException {
        try {
            //获取指定路径文件的MIME类型
            setDownloadResponse(response, fileName);
            EasyExcel.write(response.getOutputStream()).head(head).autoCloseStream(Boolean.FALSE).sheet(sheetName).registerWriteHandler(handler)
                    .doWrite(new ArrayList<>());
        } catch (Exception e) {
            returnErrorMessage(response, e);
        }
    }


   /**
     * 获取下载excel 的响应对象
     *
     * @param response 响应对象
     * @param fileName 文件路径
     * @return 响应对象
     * @throws Exception 异常对象
     */
    public static void setDownloadResponse(HttpServletResponse response, String fileName) throws Exception {
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding(StandardCharsets.UTF_8.name());
        //反馈给客户端文件用于下载
        response.setHeader(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=" + URLEncoder.encode(fileName + ExcelTypeEnum.XLSX.getValue(), StandardCharsets.UTF_8.name()));

    }



```java
  /**
     * 获取简单表头 用于空数据模板
     *
     * @param list 标题集合
     * @return easyExcel 需要的表头
     */
    public static List<List<String>> getSimpleHead(List<String> list) {
        List<List<String>> head = new ArrayList<>();
        for (String s : list) {
            List<String> title = new ArrayList<>();
            title.add(s);
            head.add(title);
        }
        return head;
    }

/**
 * 返回文件下载失败的json 原因
 *
 * @param response  响应对象
 * @param exception 异常对象
 * @throws IOException IO异常
 */
private static void returnErrorMessage(HttpServletResponse response, Exception exception) throws IOException {
    // 重置response
    response.reset();
    response.setContentType("application/json");
    response.setCharacterEncoding(StandardCharsets.UTF_8.name());
    Map<String, String> map = new HashMap<>();
    map.put("status", "failure");
    map.put("message", "下载文件失败" + exception.getMessage());
    response.getWriter().println(JSON.toJSONString(map));
}

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

##### 实现效果



![在这里插入图片描述](https://img-blog.csdnimg.cn/623378d464b94b9ea834f037bf67beb7.png)

![在这里插入图片描述](https://img-blog.csdnimg.cn/4f8c3bb580f04b97a449acd50f479175.png)

![在这里插入图片描述](https://img-blog.csdnimg.cn/0ebb4ceca33b4fa6b9def5f5c49d73b3.png)