背景

有這樣一個場景：前端下載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);}
}

最后

以上是一個簡單操作下載導出模板并填充數據后下載模板接口，經供參考！