【JAVA实战】JAVA实现Excel模板下载并填充模板下拉选项数据

背景

有这样一个场景：前端下载Excel模板，进行数据导入，这个下载模板过程需要经过后端接口去数据库查询数据进行某些列的下拉数据填充，下拉填充的数据过程中会出现错误String literals in formulas can’t be bigger than 255 characters ASCII，超过字符限制。
那么，如何解决？

解决方案

引入隐藏区域方式，比如可以创建一个隐藏Sheet专门存储该下拉填充数据，需要使用到的地方进行引用该Sheet区域范围内容。

实现过程

下载接口

@ApiOperation(value = "下载EXCEL文件模板", produces = MediaType.APPLICATION_OCTET_STREAM_VALUE)@GetMapping("/download/{bizType}")public void downloadExcel(HttpServletResponse response, @PathVariable("bizType") String bizType) throws IOException {if (Arrays.stream(AliYunOssBizTypeEnum.values()).noneMatch(x -> x.getCode().equals(bizType))) {throw new RuntimeException("类型有误!");}String fileName = bizType + ".xls";InputStream inputStream = aliYunOssService.download("template/" + fileName);if (inputStream == null) {throw new RuntimeException("获取阿里云文件模板失败，请检查是否上传到阿里云OSS");}HSSFWorkbook wb = new HSSFWorkbook(inputStream);Sheet sheet = wb.getSheetAt(0);ExcelAbstractHandler handler = ExcelHandlerFactory.getHandler(bizType);if (Objects.nonNull(handler)) {// 3. 创建隐藏工作表存储选项Sheet hiddenSheet = wb.createSheet("HiddenSheet");wb.setSheetHidden(wb.getSheetIndex(hiddenSheet), true); // 隐藏工作表handler.handle(hiddenSheet, sheet, bizType);}ServletOutputStream output = response.getOutputStream();response.setContentType("application/vnd.ms-excel");String desc = Arrays.stream(AliYunOssBizTypeEnum.values()).filter(x -> x.getCode().equals(bizType)).findFirst().get().getDesc();fileName = URLEncoder.encode(desc, "UTF-8");response.setHeader("Content-Disposition", "attachment;filename=" + fileName);wb.write(output);output.flush();output.close();wb.close();}

抽象处理器

public abstract class ExcelAbstractHandler {public abstract void handle(Sheet hiddenSheet, Sheet sheet, String bizType);protected void buildDropdownData(Sheet mainSheet, List<String> list, int col, String referenceRange) {if (CollectionUtil.isEmpty(list)) {return;}DataValidationHelper dvHelper = mainSheet.getDataValidationHelper();DataValidationConstraint dvConstraint = dvHelper.createFormulaListConstraint(referenceRange);CellRangeAddressList addressList = new CellRangeAddressList(1, 2000, col, col);DataValidation validation = dvHelper.createValidation(dvConstraint, addressList);mainSheet.addValidationData(validation);}
}

处理工厂类


public class ExcelHandlerFactory {private static final Map<String, ExcelAbstractHandler> handlerMap = new ConcurrentHashMap<>();public static ExcelAbstractHandler getHandler(String type) {return handlerMap.get(type);}public static void register(String type, ExcelAbstractHandler handler) {Assert.notNull(type, "type can't be null");handlerMap.put(type, handler);}
}

产品标准价格模板

@Component
@Slf4j
@RequiredArgsConstructor
public class ExcelProductPlatformPriceHandler extends ExcelAbstractHandler implements InitializingBean {private final BusinessBaseCountryMapper businessBaseCountryMapper;private final BusinessBaseShopPlatformMapper businessBaseShopPlatformMapper;@Overridepublic void afterPropertiesSet() throws Exception {ExcelHandlerFactory.register(AliYunOssBizTypeEnum.PRODUCT_PLATFORM_PRICE.getCode(), this);}@Overridepublic void handle(Sheet hiddenSheet, Sheet sheet, String bizType) {// 1.平台数据List<BusinessBaseShopPlatform> platforms = businessBaseShopPlatformMapper.selectList(Wrappers.lambdaQuery());List<String> platformList = platforms.stream().map(BusinessBaseShopPlatform::getPlatformName).collect(Collectors.toList());//   写入选项到隐藏工作表（逐行填充）for (int i = 0; i < platformList.size(); i++) {Row row = hiddenSheet.createRow(i);Cell cell = row.createCell(0);cell.setCellValue(platformList.get(i));}//   定义引用区域（例如：Hidden!A1:A100）String referenceRange = hiddenSheet.getSheetName() + "!$A$1:$A$" + platformList.size();buildDropdownData( sheet, platformList, 1, referenceRange);// 2.国家数据List<BusinessBaseCountry> countries = businessBaseCountryMapper.selectList(Wrappers.lambdaQuery());List<String> countryList = countries.stream().map(BusinessBaseCountry::getCountryName).collect(Collectors.toList());//  写入选项到隐藏工作表（逐行填充）int preCount = platformList.size();for (int i = preCount; i < (preCount + countryList.size()); i++) {Row row = hiddenSheet.createRow(i);Cell cell = row.createCell(0);cell.setCellValue(countryList.get(i - preCount));}//  定义引用区域（例如：Hidden!A1:A100）referenceRange = hiddenSheet.getSheetName() + "!$A$" + (preCount + 1) + ":$A$" + (preCount + countryList.size());buildDropdownData(sheet, countryList, 2 , referenceRange);}
}