private void downLoadFile(@RequestParam String jsonStr, HttpServletResponse response) { //转码 jsonStr = Utils.decodeURL(jsonStr); //将json字符传转换为json对象 JSONObject jsonObj = JSONObject.parseObject(jsonStr); //拼出文件名 String name="云端对账账单"+".xlsx"; //通过查询条件 List<CrdStaInterfacePrices> Billlist = crdStaInterfacePricesService.findBill(jsonObj.getString("startTime"), jsonObj.getString("endTime")); //创建一个xlxs文件 SXSSFWorkbook wb = new SXSSFWorkbook(); XSSFCellStyle style = (XSSFCellStyle) wb.createCellStyle(); Color c = new Color(147, 208, 15); XSSFColor xssfColor = new XSSFColor(c); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setFillForegroundColor(xssfColor); //往excel里面写数据 buildSheet(wb, Billlist, style); response.setContentType("application/x-xls"); response.setHeader("Content-Disposition", "attachment; filename=" + fileName); OutputStream outputStream = null; try { outputStream = response.getOutputStream(); wb.write(outputStream); outputStream.flush(); outputStream.close(); } catch (IOException e) { log.error("文件导出失败" + e); } }
/** * @return * @throws * @description 根据数据生成excel */ private void buildSheet(SXSSFWorkbook wb, List<CrdStaInterfacePrices> BillList, XSSFCellStyle style) { Sheet sheet = wb.createSheet("sheet1"); Row row = sheet.createRow(0); //写表头 setTitle(row, style); //写数据 setData(sheet, style, BillList); }
private void setData(Sheet sheet, XSSFCellStyle style, List<CrdStaInterfacePrices> BillList) { //循环将查询数据库获取到的数据写入excel文件 for (int i = 0; i < BillList.size(); i++) { Row row = sheet.createRow(i + 1); CrdStaInterfacePrices crdStaInterfacePrices = BillList.get(i); Cell cel0 = row.createCell(0); cel0.setCellValue("子产品"); Cell cel1 = row.createCell(1); cel1.setCellValue("用户评估报告"); Cell cel2 = row.createCell(2); cel2.setCellValue(""); Cell cel3 = row.createCell(3); cel3.setCellValue(""); Cell cel4 = row.createCell(4); cel4.setCellValue(crdStaInterfacePrices.getProdName()); Cell cel5 = row.createCell(5); cel5.setCellValue(crdStaInterfacePrices.getProdCode()); Cell cel6 = row.createCell(6); cel6.setCellValue("是"); Cell cel7 = row.createCell(7); cel7.setCellValue(crdStaInterfacePrices.getProTypename().substring(0,2)); Cell cel8 = row.createCell(8); cel8.setCellValue(crdStaInterfacePrices.getProTypename().substring(crdStaInterfacePrices.getProTypename().length()-2,crdStaInterfacePrices.getProTypename().length())); Cell cel9 = row.createCell(9); cel9.setCellValue(crdStaInterfacePrices.getNum()); Cell cel10 = row.createCell(10); cel10.setCellValue("0"); Cell cel11 = row.createCell(11); cel11.setCellValue(crdStaInterfacePrices.getNum()); Cell cel12 = row.createCell(12); cel12.setCellValue(crdStaInterfacePrices.getPrice()); Cell cel13 = row.createCell(13); cel13.setCellValue("0"); Cell cel14 = row.createCell(14); cel14.setCellValue("0"); Cell cel15 = row.createCell(15); Double price=crdStaInterfacePrices.getPrice(); if(price.compareTo(0.0)==0){ cel15.setCellValue("0.0"); }else { cel15.setCellValue(crdStaInterfacePrices.getSumPrices()); } } }
/** * @param style * @return * @throws * @description 第一行填写表头 */ public void setTitle(Row row, XSSFCellStyle style) { Cell cell = row.createCell(0); cell.setCellValue("子产品/打包N"); cell.setCellStyle(style); Cell cell1 = row.createCell(1); cell1.setCellValue("产品类型"); cell1.setCellStyle(style); Cell cell2 = row.createCell(2); cell2.setCellValue("数据来源"); cell2.setCellStyle(style); Cell cell3 = row.createCell(3); cell3.setCellValue("运营商"); cell3.setCellStyle(style); Cell cell4 = row.createCell(4); cell4.setCellValue("产品名称(中文)"); cell4.setCellStyle(style); Cell cell5 = row.createCell(5); cell5.setCellValue("产品名称(英文)"); cell5.setCellStyle(style); Cell cell6 = row.createCell(6); cell6.setCellValue("是否计费"); cell6.setCellStyle(style); Cell cell7 = row.createCell(7); cell7.setCellValue("计费逻辑1"); cell7.setCellStyle(style); Cell cell8 = row.createCell(8); cell8.setCellValue("计费逻辑2"); cell8.setCellStyle(style); Cell cell9 = row.createCell(9); cell9.setCellValue("计费量"); cell9.setCellStyle(style); Cell cell10 = row.createCell(10); cell10.setCellValue("冲减量"); cell10.setCellStyle(style); Cell cell11 = row.createCell(11); cell11.setCellValue("实际计费量"); cell11.setCellStyle(style); Cell cell12 = row.createCell(12); cell12.setCellValue("单价"); cell12.setCellStyle(style); Cell cell13 = row.createCell(13); cell13.setCellValue("免费期"); cell13.setCellStyle(style); Cell cell14 = row.createCell(14); cell14.setCellValue("免费条数"); cell14.setCellStyle(style); Cell cell15 = row.createCell(15); cell15.setCellValue("本期小计"); cell15.setCellStyle(style); }