一.pom依赖导入
<!-- easypoi -->
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-spring-boot-starter</artifactId>
<version>4.4.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.1.1</version>
</dependency>
二.excel模版如下:
三.业务代码如下:
//获取导出excel指定模版 TemplateExportParams params = new TemplateExportParams("template/YHYQ.xlsx",true); //获取详情数据 (数据结构切套) PlYhYqFEntityDto plYhYqDtos = (PlYhYqFEntityDto) plYhYqService.selectPlYhYq(fid).getData(); Map<Integer, List<Map<String, Object>>> sheetsMap = new HashMap<>(); //以下无非就是做了一件事、插入数据 try { //获取押汇批次下车辆 List<YhPcFEntityDto> yhPcList = plYhYqDtos.getFEntity(); int i = 0; // 将所有sheet使用得map进行包装 for (YhPcFEntityDto yhPcFEntityDto : yhPcList) { List<Map<String, Object>> list =new ArrayList<>(); Map<String, Object> map = new HashMap<>(); //添加批次数据 //信用证号 map.put("xyzh", yhPcFEntityDto.getFCreditNo()); //经销商名称 map.put("FNAME",plYhYqDtos.getFCustomer()); //进口合同号 map.put("FPurchaseNo",yhPcFEntityDto.getFPurchaseNo()); //上次到期日 String dqr = yhPcFEntityDto.getFLastDueDate(); //垫税开始日期 String startDate = yhPcFEntityDto.getFBpDate(); //上次到期日 - 垫税开始日期 = 押汇天数 int dayBetweenTwoDate = DateUtil.getDayBetweenTwoDate(dqr, startDate) + 1; map.put("yhts", dayBetweenTwoDate); //延期期限 map.put("qx", yhPcFEntityDto.getFDelayDays()); //延期计划开始日期 map.put("startjhDate", DateUtil.stringDate(yhPcFEntityDto.getFDelayStateDate(),DateUtil.YYYY_MM_DD)); //延期结束日期 map.put("endDate", DateUtil.stringDate(yhPcFEntityDto.getFDelayEndDate(),DateUtil.YYYY_MM_DD)); //延期年化利率 BigDecimal fYearRate =bigDecimalXsw(new BigDecimal(yhPcFEntityDto.getFYearRate()),2); map.put("nll", fYearRate); //服务费价格标准 String fPriceStandard = bigDecimalXsw(new BigDecimal(yhPcFEntityDto.getFPriceStandard()), 2).toPlainString(); //押汇利息 = 延期年利率(%) * 服务费价格标准 map.put("yhlx", "1".equals(fPriceStandard) ? fYearRate + "%" : "2".equals(fPriceStandard) ? fYearRate + "%×(" + "1-保证金比例)" : fYearRate + "%×(" + "1-累计保证金比例)"); //保证金汇率 BigDecimal fExceptExchangeRate=new BigDecimal(yhPcFEntityDto.getFDelayMarginRate()); //添加车辆信息 List<FEntityVehDto> fEntityVeh = yhPcFEntityDto.getFEntityVeh(); //多少辆车 map.put("number", fEntityVeh.size()); List<Map<String, Object>> listMap = new ArrayList<>(); //押汇总金金额 BigDecimal FPrice = BigDecimal.ZERO; //延期保证金总额 BigDecimal fMarginAmountTotal = BigDecimal.ZERO; for (FEntityVehDto fEntityVehDto : fEntityVeh) { Map<String, Object> lm = new HashMap<>(); //品牌型号(车系) lm.put("cx", fEntityVehDto.getFSerialName()); //VIN(车架号) lm.put("F_VIN", fEntityVehDto.getFVinName()); //延期本金(CAD) lm.put("yqbj", bigDecimalXsw(new BigDecimal(fEntityVehDto.getFPrice()), 2)); //应收保证金-RMB lm.put("ysbzj", bigDecimalXsw(new BigDecimal(fEntityVehDto.getFPlanMarginAmount()), 2)); //币种 lm.put("bz", fEntityVehDto.getFBpCurrencyName()); //押汇总金金额累加 FPrice = FPrice.add(new BigDecimal(fEntityVehDto.getFPrice())); //延期保证金累加金额 fMarginAmountTotal = fMarginAmountTotal.add(new BigDecimal(fEntityVehDto.getFPlanMarginAmount())); listMap.add(lm); } //押汇金额 = 车辆的本金之和 map.put("yhje", bigDecimalXsw(FPrice, 2)); map.put("mapList",listMap); list.add(map); sheetsMap.put(i++,list); } //导出excel Workbook workbook = ExcelExportUtil.exportExcelClone(sheetsMap, params); ByteArrayOutputStream baos = new ByteArrayOutputStream(); workbook.write(baos); byte[] documentContent = baos.toByteArray(); response.setContentType("application/octet-stream;charset=UTF-8"); // 指定下载的文件名--设置响应头 response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode("批量押汇延期申请.xlsx", "UTF-8")); // response响应头中设置传输文件长度 response.setContentLength(documentContent.length); BufferedOutputStream bos = new BufferedOutputStream(response.getOutputStream()); bos.write(documentContent); workbook.close(); baos.close(); bos.close(); buyerMap.remove(fid); } catch (IOException e) { log.error("批量押汇延期申请下载下载失败--->" + e.getMessage()); }
以上是比较完整的实现代码