使用Hutool的ExcelWriter导出复杂模板，支持下拉选项级联筛选

昨天刚接了一个导入导出的需求前端页面有三个下拉选择框有相对应的关联关系。在生成模板的时候也需要把这种关系给设置出来。最终实现的效果为 A D E三列的数据能够实现级联效果A列下拉选择之后根据选择的内容是否包含特定条件给D设置不同的数据有效性。D列选择之后匹配对应的id给X列设置值E列的数据有效性根据X列的值匹配对应的数据Y列的值根据E列的值匹配对应的Code相关操作已经上传到maven中心仓库。可以统一下列依赖引入dependencygroupIdio.github.chargeduck/groupIdartifactIdlesscoding-util/artifactIdversion1.0.0/version/dependency另有自定义注解同步增删改导入数据到Neo4j的注解及策略类可以使用dependencygroupIdio.github.chargeduck/groupIdartifactIdlesscoding-neo4j-spring-boot-starter/artifactIdversion1.0.0/version/dependency1. 具体的实现思路通过隐藏Sheet页将需要用到的数据写入通过名称管理器设置数据引用通过几组特定的函数实现功能函数作用IF(ISNUMBER(FIND(“中介”,A{})),hsAgencyNames,CompanyNames)从A列判断是否包含特定字符串然后设置对应的 名称管理器引用IFERROR(VLOOKUP($D{}, CompanyIdMap, 2, FALSE), “”)根据D列的值去CompanyIdMap这个名称引用中查询对应的IDCompanyData!$A$2:$A:$100创建名称管理器的引用范围2. 拆分实现1. 导出模板功能publicvoiddownloadTemplateV2(HttpServletResponseresponse)throwsException{// 1. 设置响应头response.setContentType(application/vnd.openxmlformats-officedocument.spreadsheetml.sheet);response.setCharacterEncoding(utf-8);StringfileNameURLEncoder.encode(级联导入模板,UTF-8);response.setHeader(Content-disposition,attachment;filename*utf-8fileName.xlsx);// 从自己的数据库获取数据MapString,CompanyVoseaCmpNameIdMapgetCompanyMap();// 根据自己的业务调整MapString,ListOrgorgMapnewHashMap();StringtopTipStr导入提示信息;try(OutputStreamoutresponse.getOutputStream()){// 2. 创建ExcelWriterExcelWriterwriterExcelUtil.getWriter(true);// true表示创建xlsx格式Sheetsheetwriter.getSheet();Workbookworkbookwriter.getWorkbook();// 写入映射数据到工作表并创建名称管理器writeCompanyAndOrgData(workbook,seaCmpNameIdMap,orgMap);// 设置级联下拉和对应列填充setCompanyValidationAndFormula(sheet,workbook);setDeptValidationAndFormula(sheet,workbook);ListStringheadersArrays.asList(表头1表头2表头3表头4);writer.merge(0,0,0,24,topTipStr,true);writer.passRows(1);writer.writeHeadRow(headers);// 日期类型限制restrictCell2DateFormat(sheet,1);;// 字典项类型的数据下拉设置setDictDataValidation(sheet,spiChecktypeHs,0);for(inti0;iheaders.size();i){StringheaderTextheaders.get(i);// 计算宽度文字长度 × 2 × 256Excel的宽度单位intcolumnWidthheaderText.length()*2*256;// 设置最小宽度避免过窄columnWidthMath.max(columnWidth,8*256);// 设置列宽sheet.setColumnWidth(i,columnWidth);}writer.flush(out).close();}catch(Exceptione){log.info(下载模板失败,e);}}2.写入映射数据和创建名称管理器privatevoidwriteCompanyAndOrgData(Workbookworkbook,MapString,HsCompanyVoseaCmpNameIdMap,MapString,ListOrgorgMap){// 1. 写入企业数据 SheetcompanySheetworkbook.createSheet(CompanyData);workbook.setSheetHidden(workbook.getSheetIndex(companySheet),true);// 企业数据表头RowcompanyHeadercompanySheet.createRow(0);companyHeader.createCell(0).setCellValue(企业名称);companyHeader.createCell(1).setCellValue(企业ID);intcompanyRow1;for(Map.EntryString,HsCompanyVoentry:seaCmpNameIdMap.entrySet()){RowrowcompanySheet.createRow(companyRow);row.createCell(0).setCellValue(entry.getKey());row.createCell(1).setCellValue(entry.getValue().getId());}// 创建企业名称列表和ID映射的名称管理器// 这里我遇到了一个问题 就是第二个参数也就是名称管理器的名字 必须以_或者 字母开头createName(workbook,CompanyNames,CompanyData!$A$2:$A$companyRow);//createName(workbook,CompanyIdMap,CompanyData!$A$2:$B$companyRow);SheetorgSheetworkbook.createSheet(OrgData);workbook.setSheetHidden(workbook.getSheetIndex(orgSheet),true);// 单位数据表头RoworgHeaderorgSheet.createRow(0);orgHeader.createCell(0).setCellValue(企业ID);orgHeader.createCell(1).setCellValue(单位名称);orgHeader.createCell(2).setCellValue(单位Code);intorgRow1;intstartRow2;StringorgDataNameNamePrefix_;// 写入所有单位数据for(Map.EntryString,ListOrgentry:orgMap.entrySet()){StringcompanyIdentry.getKey();ListOrgorgsentry.getValue();if(CollUtil.isNotEmpty(orgs)){for(Orgorg:orgs){RowroworgSheet.createRow(orgRow);row.createCell(0).setCellValue(companyId);row.createCell(1).setCellValue(org.getShortName());row.createCell(2).setCellValue(org.getCode());// 正确写入code}StringreferenceStrStrUtil.format(OrgData!$B${}:$B${},startRow,orgRow);log.info({} ::: {},orgDataNameNamePrefixcompanyId,referenceStr);createName(workbook,orgDataNameNamePrefixcompanyId,referenceStr);startRoworgRow1;}}log.info(最终的startRow: {},startRow);createName(workbook,OrgDataMapping,OrgData!$B$2:$C$(startRow-1));// 保护工作表companySheet.protectSheet(protected);orgSheet.protectSheet(protected);}// 创建名称管理器还有引用范围privatevoidcreateName(Workbookworkbook,StringnameName,Stringreference){if(workbookinstanceofXSSFWorkbook){XSSFNamename((XSSFWorkbook)workbook).createName();name.setNameName(nameName);name.setRefersToFormula(reference);}}3. 设置数据有效性和根据这一列自动填充值的方法我的E列的数据是根据Y列的数据设置的数据有效性所以我把Y列用了 _id的方式做了 名称管理器 引用。所以给E列设置 formula的时候直接写 下百年的代码就可以了 通过INDIRECT函数引用对应的名称管理器StrUtil.format(INDIRECT(_X{}),rowIndex1)privatevoidsetCompanyValidationAndFormula(Sheetsheet,Workbookworkbook){XSSFDataValidationHelperdvHelpernewXSSFDataValidationHelper((XSSFSheet)sheet);CellRangeAddressListcompanyRange;Stringformula;StringorgIdFormula;XSSFDataValidationConstraintcompanyConstraint;XSSFDataValidationcompanyValidation;Rowrow;// 2. 创建锁定样式用于受检企业ID列CellStylelockedStyleworkbook.createCellStyle();lockedStyle.setLocked(true);for(introwIndex1;rowIndextemplateEndRow;rowIndex){// 只为当前行的第4列索引3设置数据验证companyRangenewCellRangeAddressList(rowIndex,rowIndex,3,3);// 判断A列选中的数据中是否有删选条件// 有的话用 trueName这个名称管理器 否则用falseName 这个要根据自己的条件还有业务逻辑自己修改一下formulaStrUtil.format(IF(ISNUMBER(FIND(筛选条件,A{})),trueName,falseName),rowIndex1);log.info(当前函数公式 {},formula);companyConstraint(XSSFDataValidationConstraint)dvHelper.createFormulaListConstraint(formula);companyValidation(XSSFDataValidation)dvHelper.createValidation(companyConstraint,companyRange);companyValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);companyValidation.createErrorBox(输入错误,请从下拉列表选择受检企业);companyValidation.setSuppressDropDownArrow(true);// 对于动态公式建议隐藏下拉箭头sheet.addValidationData(companyValidation);rowsheet.getRow(rowIndex)!null?sheet.getRow(rowIndex):sheet.createRow(rowIndex);CellidCellrow.createCell(23);// 设置VLOOKUP公式根据D列查找对应的ID CompanyIdMap可以从 公式 名称管理器中查询到orgIdFormulaStrUtil.format(IFERROR(VLOOKUP($D{}, CompanyIdMap, 2, FALSE), ),rowIndex1);idCell.setCellFormula(orgIdFormula);// 应用锁定样式idCell.setCellStyle(lockedStyle);}}4. 设置指定列只能填写日期类型和字典项数据有效性privatevoidrestrictCell2DateFormat(Sheetsheet,intcol){CellRangeAddressListregionsnewCellRangeAddressList(1,templateEndRow,col,col);XSSFDataValidationHelperdvHelpernewXSSFDataValidationHelper((XSSFSheet)sheet);// 设置数据验证约束日期格式介于最小日期和最大日期之间XSSFDataValidationConstraintdvConstraint(XSSFDataValidationConstraint)dvHelper.createDateConstraint(XSSFDataValidationConstraint.OperatorType.BETWEEN,DATE(1900,1,1),// 最小日期DATE(2100,12,31),// 最大日期yyyy-mm-dd// 日期格式);// 创建数据验证规则XSSFDataValidationvalidation(XSSFDataValidation)dvHelper.createValidation(dvConstraint,regions);// 设置验证失败时的提示信息validation.setErrorStyle(DataValidation.ErrorStyle.STOP);validation.createErrorBox(输入错误,请输入正确的日期格式yyyy-MM-dd);// 设置输入提示信息validation.createPromptBox(日期格式提示,请输入 yyyy-MM-dd 格式的日期例如2024-01-01);validation.setShowPromptBox(true);validation.setSuppressDropDownArrow(false);// 将数据验证添加到工作表sheet.addValidationData(validation);// 设置单元格格式为日期格式CellStyledateCellStylesheet.getWorkbook().createCellStyle();DataFormatformatsheet.getWorkbook().createDataFormat();dateCellStyle.setDataFormat(format.getFormat(yyyy-MM-dd));sheet.addValidationData(validation);}/** * 设置字典项数据有效性 * * param dictType */privatevoidsetDictDataValidation(Sheetsheet,StringdictType,intcol){MapString,StringdictMapdictUtil.getDictMap(dictType);log.info(当前获取的dictMap {},dictMap);DataValidationHelperdvHelpersheet.getDataValidationHelper();DataValidationConstraintconstraintdvHelper.createExplicitListConstraint(dictMap.values().toArray(newString[0]));CellRangeAddressListcellRangeAddressListnewCellRangeAddressList(1,templateEndRow,col,col);DataValidationvalidationdvHelper.createValidation(constraint,cellRangeAddressList);// 显示下拉箭头validation.setSuppressDropDownArrow(true);// 显示提示框validation.setShowPromptBox(true);// 设置为INFO级别允许输入validation.setErrorStyle(DataValidation.ErrorStyle.INFO);sheet.addValidationData(validation);}