@Override public String expFundBalanceReportData1(String reportDate) { List<FundBalanceReportResp> list = null; try { list = getFundBalanceReportTree(reportDate); if (!CollectionUtils.isEmpty(list)) { //创建poi导出数据对象 SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(); //创建sheet页 SXSSFSheet sheet = sxssfWorkbook.createSheet("sheet页名字"); //左对齐 CellStyle cellStyleLeft = sxssfWorkbook.createCellStyle(); cellStyleLeft.setAlignment(HorizontalAlignment.LEFT); // cellStyleLeft.setFillForegroundColor(IndexedColors.RED.getIndex()); // cellStyleLeft.setFillPattern(FillPatternType.SOLID_FOREGROUND); // 创建字体样式 Font fontLeft = sxssfWorkbook.createFont(); fontLeft.setColor(IndexedColors.RED.getIndex()); // 设置字体样式到单元格样式 cellStyleLeft.setFont(fontLeft); //居中 CellStyle cellStyleCenter = sxssfWorkbook.createCellStyle(); cellStyleCenter.setAlignment(HorizontalAlignment.CENTER); // cellStyleCenter.setFillForegroundColor(IndexedColors.BLUE.getIndex()); // cellStyleCenter.setFillPattern(FillPatternType.SOLID_FOREGROUND); Font fontCenter = sxssfWorkbook.createFont(); fontCenter.setColor(IndexedColors.BLUE.getIndex()); // 设置字体样式到单元格样式 cellStyleCenter.setFont(fontCenter); //右对齐 CellStyle cellStyleRight = sxssfWorkbook.createCellStyle(); cellStyleRight.setAlignment(HorizontalAlignment.RIGHT); // cellStyleRight.setFillForegroundColor(IndexedColors.GREEN.getIndex()); // cellStyleRight.setFillPattern(FillPatternType.SOLID_FOREGROUND); Font fontRight = sxssfWorkbook.createFont(); fontRight.setColor(IndexedColors.GREEN.getIndex()); // 设置字体样式到单元格样式 cellStyleRight.setFont(fontRight); //创建表头 SXSSFRow headRow = sheet.createRow(0); //设置表头信息 headRow.createCell(0).setCellValue("层级"); headRow.createCell(1).setCellValue("报表项"); headRow.createCell(2).setCellValue("原币金额"); headRow.createCell(3).setCellValue("折合CNY"); headRow.createCell(4).setCellValue("汇兑损益"); // 遍历上面数据库查到的数据 for (FundBalanceReportResp resp : list) { //填充数据 SXSSFRow dataRow = sheet.createRow(sheet.getLastRowNum() + 1); dataRow.createCell(0).setCellValue(1); dataRow.createCell(1).setCellValue(resp.getReportItemName()); dataRow.getCell(1).setCellStyle(cellStyleLeft); dataRow.createCell(2).setCellValue(resp.getOrigMoney() != null ? resp.getOrigMoney().toString() : "" ); dataRow.createCell(3).setCellValue(resp.getConvertMoney() != null ? resp.getConvertMoney().toString() : "" ); dataRow.createCell(4).setCellValue(resp.getExchangeLoss() != null ? resp.getExchangeLoss().toString() : ""); resp.setColNumber(1); if (!CollectionUtils.isEmpty(resp.getChildList())) { expChild(resp.getChildList(), sheet, resp, cellStyleCenter, cellStyleRight); } } String fileName = "资金余额报表_"+reportDate + ".xlsx"; createFileInLocal(sxssfWorkbook,"/Users/zhaoliang/app/logs/solar/"+fileName); } } catch (Exception e) { e.printStackTrace(); } return null; } private void expChild(List<FundBalanceReportResp> childList, SXSSFSheet sheet, FundBalanceReportResp resp, CellStyle cellStyleCenter, CellStyle cellStyleRight){ for (FundBalanceReportResp childResp : childList) { // 获取sheet的最后一行 SXSSFRow childRow = sheet.createRow(sheet.getLastRowNum()+1); // 填充数据 childRow.createCell(0).setCellValue(resp.getColNumber()+1); childRow.createCell(1).setCellValue(childResp.getReportItemName()); childRow.createCell(2).setCellValue(childResp.getOrigMoney() != null ? childResp.getOrigMoney().toString() : "" ); childRow.createCell(3).setCellValue(childResp.getConvertMoney() != null ? childResp.getConvertMoney().toString() : "" ); childRow.createCell(4).setCellValue(childResp.getExchangeLoss() != null ? childResp.getExchangeLoss().toString() : ""); childResp.setColNumber(resp.getColNumber()+1); if (childResp.getColNumber() == 2) { childRow.getCell(1).setCellStyle(cellStyleCenter); } else if (childResp.getColNumber() == 3) { childRow.getCell(1).setCellStyle(cellStyleRight); } if(!CollectionUtils.isEmpty(childResp.getChildList())) { expChild(childResp.getChildList(), sheet, childResp, cellStyleCenter, cellStyleRight); } } } public void createFileInLocal(final SXSSFWorkbook workbook, final String fileName) { try { //目录 String path= fileName.substring(0,fileName.lastIndexOf("/")); String fileNameEncode =null; try { fileNameEncode = new String(fileName.getBytes("utf-8"),"utf-8"); } catch (UnsupportedEncodingException e) { e.printStackTrace(); } //创建目录 File pathFile = new File(path); if (!pathFile.exists()) { pathFile.mkdirs(); } final File file = new File(fileNameEncode); OutputStream outputStream = new FileOutputStream(file); workbook.write(outputStream); outputStream.close(); } catch (IOException e) { e.printStackTrace(); } }
12-18
947
04-24