苍穹外卖 Apache POI 导出

demo

//通过poi创建新文件写入文件  
public static void  write(){  
    //在内存中创建excel文件  
    XSSFWorkbook excel = new XSSFWorkbook();  

    //创建 sheet页  
    XSSFSheet sheet_info = excel.createSheet("info");  

    //在sheet页中创建行对象  
    XSSFRow row = sheet_info.createRow(0);  

    //在 row 上创建单元格  
    XSSFCell cell = row.createCell(0);  
    cell.setCellValue("你好");  

    row.createCell(3).setCellValue("城市");  
}  


/**  
 * 读取excel 文件内容  
 */  
public static void  read() throws Exception{  
    FileInputStream fileInputStream = new FileInputStream(new File("\"D:\\BaiduNetdiskDownload\\java\\CangQiongWaiMai\\资料\\资料\\day12\\itcast.xlsx\""));  
    XSSFWorkbook excel = new XSSFWorkbook(fileInputStream);  

    XSSFSheet sheet1 = excel.getSheet("Sheet1");  

    int lastRowNum = sheet1.getLastRowNum();  

    for(int i =0;i<lastRowNum;i++){  
        XSSFRow row = sheet1.getRow(i);  
        String cell1 = row.getCell(1).getStringCellValue();  
        String cell2 = row.getCell(2).getStringCellValue();  

        log.info("cell1:{},cell2:{}",cell1,cell2);  
    }  

    excel.close();  
}

项目开发

• 使用模板

• 从 容器中获取 response

  
  @GetMapping("/export")  
  public void export(HttpServletResponse response){  
      reportService.exportBusinessData(response);  
  }
  

• 业务代码

  @Override  
  public void exportBusinessData(HttpServletResponse response) {  
      //获取营业数据  
      LocalDate nowDate = LocalDate.now().plusDays(-1);  
      LocalDate preDate = nowDate.plusDays(-29);  
  
  
      //查询 概览 数据  
      BusinessDataVO businessData = workspaceService.getBusinessData(LocalDateTime.of(preDate, LocalTime.MIN), LocalDateTime.of(nowDate, LocalTime.MAX));  
  
      //写入 excel 文件  
      InputStream excelStream = this.getClass().getClassLoader().getResourceAsStream("template/运营数据报表模板.xlsx");  
  
      XSSFWorkbook excel =null;  
      ServletOutputStream outputStream =null;  
      try {  
          excel = new XSSFWorkbook(excelStream);  
          XSSFSheet sheet1 = excel.getSheet("Sheet1");  
          sheet1.getRow(1).getCell(1).setCellValue("时间:"+preDate+"-"+nowDate);  
          XSSFRow row3 = sheet1.getRow(3);  
          row3.getCell(2).setCellValue(businessData.getTurnover());  
          row3.getCell(4).setCellValue(businessData.getOrderCompletionRate());  
          row3.getCell(6).setCellValue(businessData.getNewUsers());  
  
          XSSFRow row4 = sheet1.getRow(4);  
          row4.getCell(2).setCellValue(businessData.getValidOrderCount());  
          row4.getCell(4).setCellValue(businessData.getUnitPrice());  
  
          //填充明细  
  
          for (int i = 0; i < 30; i++) {  
              LocalDate date = preDate.plusDays(i);  
              BusinessDataVO businessData1 = workspaceService.getBusinessData(  
                      LocalDateTime.of(date, LocalTime.MIN),  
                      LocalDateTime.of(date, LocalTime.MAX)  
              );  
              XSSFRow row = sheet1.getRow(7 + i);  
              row.getCell(1).setCellValue(date.toString());  
              row.getCell(2).setCellValue(businessData1.getTurnover());  
              row.getCell(3).setCellValue(businessData1.getValidOrderCount());  
              row.getCell(4).setCellValue(businessData1.getOrderCompletionRate());  
              row.getCell(5).setCellValue(businessData1.getUnitPrice());  
              row.getCell(6).setCellValue(businessData1.getNewUsers());  
  
          }  
  
  
          // 写到输出流中  
          outputStream = response.getOutputStream();  
          excel.write(outputStream);  
  
  
          //通过输出流将excel文件输出到浏览器  
          outputStream.close();  
          excel.close();  
      } catch (IOException e) {  
          throw new RuntimeException(e);  
      }  
  
  }