需求背景: 我的系统中有这么多字段,可能分布在用户上传的excel文档中的任意一列, 现在按照用户的摆放的title位置,填充数据进excel.模板样式:
维护用户定义的title信息和系统内属性对应关系,如图:
上代码: /** * 按照模板填充Excel内容 * @param excelUrl * @param exportResult * @return */ public String exportExcelByTemplate(String excelUrl, VoucherExportResult exportResult) { SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); AliyunOssFeignResponse feignResponse = aliyunOssClient.getOssDownloadUrl("VOUCHER_TEMPLATE", excelUrl); Map<String, Integer> headers = new HashMap<>(); try (HSSFWorkbook workbook = new HSSFWorkbook(new URL(feignResponse.getUrl()).openStream())) { Sheet firstSheet = workbook.getSheetAt(0); Iterator<Row> rows = firstSheet.iterator(); Row headRow = rows.next(); for (int i = 0; i < 50; i++) { Cell cell = headRow.getCell(i); if (Objects.isNull(cell)) break; String cellValue = headRow.getCell(i).getStringCellValue().trim(); headers.put(cellValue, i); } if (MapUtils.isEmpty(headers)) throw new BadRequestException("模板设置不规范"); Map<String, Integer> titleWithIndexMap = headers.entrySet().stream().filter(it -> headerMap.containsKey(it.getKey())).collect(Collectors.toMap(it -> headerMap.get(it.getKey()), Map.Entry::getValue)); //开始填充数据 int i = 1; for (VoucherListModel voucher : exportResult.getVouchers()) { for (VoucherItemModel item : voucher.getVoucherItems()) { Row row = firstSheet.createRow(i++); for (Map.Entry<String, Integer> title : titleWithIndexMap.entrySet()) { try { Field field = item.getClass().getDeclaredField(title.getKey()); field.setAccessible(true); row.createCell(title.getValue()).setCellValue(Optional.ofNullable(field.get(item)).orElse("").toString()); } catch (NoSuchFieldException e) { try { Field field = voucher.getClass().getDeclaredField(title.getKey()); field.setAccessible(true); //判断是否为日期字段, 硬编码 boolean isDateType = false; if (dateTypeInVoucher.contains(title.getKey())) {//注意:日期字段暂时只有在voucher中出现,所以写在了这里,如果item中出现了日期格式,需要在上层try模块中完成转换 isDateType = true; } Object temp = Optional.ofNullable(field.get(voucher)).orElse(""); String cellValue = temp.toString(); if (!ObjectUtils.isEmpty(temp) && isDateType) { cellValue = sdf.format((Date) temp); } row.createCell(title.getValue()).setCellValue(cellValue); } catch (Exception e1) { e.printStackTrace(); } } catch (IllegalAccessException e) { e.printStackTrace(); } } } } return createUrl(workbook, "会计凭证"); } catch (MalformedURLException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return null; } 示例note: 模板是放在阿里云oss服务器中的. 使用的是Apache poi 3.16实现思路: 读取excel标题列,然后通过预定义好的关系图过滤title, 筛选出本系统内有的属性titile和位置,用map维护;循环数据–>循环上一步中的map结构,逐个获取属性,如果存在一对多的关系可以通过扁平化对象,或者再嵌套一层循环;填充完就上传到oss服务器.返给前台下载链接.导出结果: