需求背景: 我的系统中有这么多字段,可能分布在用户上传的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服务器.返给前台下载链接.导出结果:
