使用 POI操作Excel表格导出

使用 POI操作Excel表格导出

/**
  * 导出Excel报表
  * @return
*/
@RequestMapping("/exportBusinessReport")
public Result exportBusinessReport(HttpServletRequest request, HttpServletResponse response){
  try{
    //远程调用报表服务获取报表数据
    Map<String, Object> result = reportService.getBusinessReport();
    
    //取出返回结果数据，准备将报表数据写入到Excel文件中
    String reportDate = (String) result.get("reportDate");
    Integer todayNewMember = (Integer) result.get("todayNewMember");
    List<Map> hotSetmeal = (List<Map>) result.get("hotSetmeal");
    
    //获得Excel模板文件绝对路径
    String temlateRealPath = request.getSession().getServletContext().getRealPath("template") +
                                                File.separator + "report_template.xlsx";
    
    //读取模板文件创建Excel表格对象
    XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(new File(temlateRealPath)));
    XSSFSheet sheet = workbook.getSheetAt(0);
    ​//添加数据
    XSSFRow row = sheet.getRow(2);
    row.getCell(5).setCellValue(reportDate);//日期

    row = sheet.getRow(4);
    row.getCell(5).setCellValue(todayNewMember);//新增会员数（本日）
    row.getCell(7).setCellValue(totalMember);//总会员数
​
    row = sheet.getRow(5);
    row.getCell(5).setCellValue(thisWeekNewMember);//本周新增会员数
    row.getCell(7).setCellValue(thisMonthNewMember);//本月新增会员数
​
    
    int rowNum = 12;
    for(Map map : hotSetmeal){//热门套餐
      String name = (String) map.get("name");
      Long setmeal_count = (Long) map.get("setmeal_count");
      BigDecimal proportion = (BigDecimal) map.get("proportion");
      row = sheet.getRow(rowNum ++);
      row.getCell(4).setCellValue(name);//套餐名称
      row.getCell(5).setCellValue(setmeal_count);//预约数量
      row.getCell(6).setCellValue(proportion.doubleValue());//占比
    }
​
    //通过输出流进行文件下载
    ServletOutputStream out = response.getOutputStream();
    response.setContentType("application/vnd.ms-excel");
    response.setHeader("content-Disposition", "attachment;filename=report.xlsx");
    workbook.write(out);
    
    out.flush();
    out.close();
    workbook.close();
    
    return null;
  }catch (Exception e){
    return new Result(false, MessageConstant.GET_BUSINESS_REPORT_FAIL,null);
  }
}