北京长空建设有限公司网站,酒泉网站建设有限公司,wordpress 移动导航,做餐饮网站需求背景#xff1a;有一个需求要做下拉框的值有100多条#xff0c;同时这个excel是一个多sheet的导入模板 直接用easyexcel 导出#xff0c;会出现下拉框的值过多#xff0c;导致生成出来的excel模板无法正常展示下拉功能
使用的easyexcel版本#xff1a;depende…需求背景有一个需求要做下拉框的值有100多条同时这个excel是一个多sheet的导入模板 直接用easyexcel 导出会出现下拉框的值过多导致生成出来的excel模板无法正常展示下拉功能
使用的easyexcel版本dependencygroupIdcom.alibaba/groupIdartifactIdeasyexcel/artifactIdversion2.2.6/version
/dependency自定义处理器package com.manager.utils;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.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import java.util.Map;/*** 解决使用 easyExcel导出模板下拉框数据超长导出模板后下拉框数据不展示问题* author yjj* date 2025/02/18 10:16**/
public class EasyExcelCellWriteHandler implements SheetWriteHandler {public static final String SHEET_NAME 下拉框隐藏表hidden;/*** 设置阈值避免生成的导入模板下拉值获取不到*/private static final Integer LIMIT_NUMBER 50;private MapInteger, String[] map null;public EasyExcelCellWriteHandler(MapInteger, String[] map) {this.map map;}Overridepublic void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {}Overridepublic void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {// 这里可以对cell进行任何操作Sheet sheet writeSheetHolder.getSheet();DataValidationHelper helper sheet.getDataValidationHelper();Class? headClass writeSheetHolder.getClazz();// k 为存在下拉数据集的单元格下表 v为下拉数据集map.forEach((k, v) - {System.out.println(Key k , Value v);// 设置下拉单元格的首行 末行 首列 末列CellRangeAddressList rangeList new CellRangeAddressList(EasyExcelUtils.headRowNumber(headClass), 65536,k,k);// 如果下拉值总数大于50则使用一个新sheet存储避免生成的导入模板下拉值获取不到if (v.length LIMIT_NUMBER) {//定义sheet的名称//1.创建一个隐藏的sheet 名称为 hidden kString sheetName SHEET_NAME sheet.getSheetName() k;Workbook workbook writeWorkbookHolder.getWorkbook();Sheet hiddenSheet workbook.createSheet(sheetName);for (int i 0, length v.length; i length; i) {// 开始的行数i列数khiddenSheet.createRow(i).createCell(k).setCellValue(v[i]);}Name category1Name workbook.createName();category1Name.setNameName(sheetName);String excelLine getExcelLine(k);// hidden!$H:$1:$H$50 sheet为hidden的 H1列开始H50行数据获取下拉数组String refers sheetName !$ excelLine $1:$ excelLine $ (v.length 1);// 将刚才设置的sheet引用到你的下拉列表中DataValidationConstraint constraint helper.createFormulaListConstraint(refers);DataValidation dataValidation helper.createValidation(constraint, rangeList);writeSheetHolder.getSheet().addValidationData(dataValidation);// 设置存储下拉列值得sheet为隐藏int hiddenIndex workbook.getSheetIndex(sheetName);if (!workbook.isSheetHidden(hiddenIndex)) {workbook.setSheetHidden(hiddenIndex, true);}}// 下拉列表约束数据DataValidationConstraint constraint helper.createExplicitListConstraint(v);// 设置约束DataValidation validation helper.createValidation(constraint, rangeList);// 阻止输入非下拉选项的值validation.setErrorStyle(DataValidation.ErrorStyle.STOP);validation.setShowErrorBox(true);validation.setSuppressDropDownArrow(true);validation.createErrorBox(提示, 此值与单元格定义格式不一致);// validation.createPromptBox(填写说明,填写内容只能为下拉数据集中的单位其他单位将会导致无法入仓);sheet.addValidationData(validation);});}/*** 返回excel列标A-Z-AA-ZZ** param num 列数* return java.lang.String*/private String getExcelLine(int num) {String line ;int first num / 26;int second num % 26;if (first 0) {line (char) (A first - 1) ;}line (char) (A second) ;return line;}}导出工具类/*** 支持超长下拉框展示* 下载导入模板 - 支持多sheet*/public static void writeTemplateBoxTooLong(HttpServletResponse response, ExcelModel excelModel) {ExcelWriter excelWriter null;try {excelWriter EasyExcel.write(outputStream(excelModel.getFileName(), response)).registerConverter(new DateConverter()).useDefaultStyle(false).build();ListExcelModel.Sheet? sheets excelModel.getSheets();for (int i 0; i sheets.size(); i) {ExcelModel.Sheet? sheet sheets.get(i);WriteSheet writeSheet;if (ExtraOption.class.isAssignableFrom(sheet.getHeadClass())) {writeSheet EasyExcel.writerSheet(i, sheet.getSheetName()).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).registerWriteHandler(HorizontalCellStyleStrategyFactory.optStyleStrategy()).head(sheet.getHeadClass()).sheetName(sheet.getSheetName()).build();} else {MapInteger, String[] map buildExcelDropDownSetField(sheet.getHeadClass());writeSheet EasyExcel.writerSheet(i, sheet.getSheetName()).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).registerWriteHandler(new EasyExcelCellWriteHandler(map)).registerWriteHandler(new ImportTempleRowWriteHandler()).registerWriteHandler(new ImportTempleCellWriteHandler()).head(sheet.getHeadClass()).sheetName(sheet.getSheetName()).relativeHeadRowIndex(RELATIVE_HEAD_ROW_INDEX).includeColumnFiledNames(sheet.getIncludeFiledNames()).build();}excelWriter.write(sheet.getData(), writeSheet);}} catch (Exception e) {Throwable cause Throwables.getRootCause(e);log.error(下载模板失败{}, cause.getMessage(), cause);throw new ServiceException(下载模板失败{0}, cause.getMessage());} finally {if (excelWriter ! null) {excelWriter.finish();}}} /*** 批量导入 - 支持多sheet*/SuppressWarnings(rawtypes)public static MapClass?, List? readMultiSheet(MultipartFile file, Class?... classes) {MapClass?, List? resultMap Maps.newHashMapWithExpectedSize(classes.length 1);try {checkExcelFile(file);ByteArrayInputStream inputStream deleteHiddenSheets(file);ExcelReader excelReader EasyExcel.read(inputStream).build();for (int i 0; i classes.length; i) {Class clazz classes[i];SimpleAnalysisEventListener listener SimpleAnalysisEventListener.factory(true);ReadSheet readSheet EasyExcel.readSheet(i).head(clazz).registerReadListener(listener).headRowNumber(headRowNumber(clazz)).build();excelReader.read(readSheet);resultMap.put(clazz, listener.getResults());}if (resultMap.values().stream().allMatch(CollectionUtils::isEmpty)) {throw new ServiceException(请至少录入一条数据);}} catch (Exception e) {Throwable cause Throwables.getRootCause(e);log.error(解析异常{}, cause.getMessage(), cause);throw new ServiceException(解析异常{0}, cause.getMessage());}return resultMap;}//删除导出模板时生成的隐藏sheet,避免导入时读取带隐藏sheet报错public static ByteArrayInputStream deleteHiddenSheets(MultipartFile file){try (InputStream inputStream file.getInputStream();ByteArrayOutputStream outputStream new ByteArrayOutputStream();){Workbook workbook new XSSFWorkbook(inputStream);for (int i 0; i workbook.getNumberOfSheets(); i) {Sheet sheet workbook.getSheetAt(i);if (sheet.getSheetName().contains(EasyExcelCellWriteHandler.SHEET_NAME)) {workbook.removeSheetAt(i);i--; // 因为删除了一个sheet索引需要调整}}workbook.write(outputStream);workbook.close();return new ByteArrayInputStream(outputStream.toByteArray());} catch (IOException e) {log.error(解析excel失败,e);throw new ServiceException(解析失败);}} 原生poi参考这位大佬解决POI的SXSSFSheet 创建excel下拉框下拉框内容过多时不显示的问题_java poi 下拉框数据7万行,隐藏sheet方法也看不不全-CSDN博客
