网站百度权重查询,网站单子,专门做进口产品的网站,网站备案备注信息背景
有这样一个场景#xff1a;前端下载Excel模板#xff0c;进行数据导入#xff0c;这个下载模板过程需要经过后端接口去数据库查询数据进行某些列的下拉数据填充#xff0c;下拉填充的数据过程中会出现错误String literals in formulas can’t be bigger than 255 cha…背景
有这样一个场景前端下载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, ListString 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 MapString, 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 cant 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.平台数据ListBusinessBaseShopPlatform platforms businessBaseShopPlatformMapper.selectList(Wrappers.lambdaQuery());ListString 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:A100String referenceRange hiddenSheet.getSheetName() !$A$1:$A$ platformList.size();buildDropdownData( sheet, platformList, 1, referenceRange);// 2.国家数据ListBusinessBaseCountry countries businessBaseCountryMapper.selectList(Wrappers.lambdaQuery());ListString 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:A100referenceRange hiddenSheet.getSheetName() !$A$ (preCount 1) :$A$ (preCount countryList.size());buildDropdownData(sheet, countryList, 2 , referenceRange);}
}
最后
以上是一个简单操作下载导出模板并填充数据后下载模板接口经供参考
