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

1.接口层

    @ApiOperation("其他费用配置-模版下载")@GetMapping("/downloadTemplate")public void downloadTemplate(HttpServletResponse response) {try {List<String> list = Arrays.asList("集团", "平台", "部门", "店铺", "年月", "币别", "费用项目", "金额","分摊类型", "分摊依据", "订单号", "备注");ExcelUtils.exportTemplate(response, ExcelUtils.getSimpleHead(list), new SheetWriteHandler() {@Overridepublic 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()));}/*** 返回文件下载失败的json 原因** @param response  响应对象* @param exception 异常对象* @throws IOException IO异常*/private static void returnErrorMessage(HttpServletResponse response, Exception exception) throws IOException {// 重置responseresponse.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);}}

实现效果