数据库数据写入到EXCEL表格

使用的Excel表格：

Snipaste_2019-07-29_11-29-22.png

这里省略数据库查询部分代码(数据库查询到的数据放在了MAP集合中)

直接上控制器代码：
说明：我将report_template.xlsx模板放到了resources目录下。

@RequestMapping("/exportBusinessReport")
    public Result exportBusinessReport(HttpServletRequest req, HttpServletResponse resp){
        try {
        Map<String, Object> result = memberService.getBusinessReportData();
        // 取出返回结果
        String orderDate = (String)result.get("reportDate");
        // 获取会员相关数据
        Integer todayNewMember = (Integer)result.get("todayNewMember");
        Integer thisWeekNewMember = (Integer)result.get("thisWeekNewMember");
        Integer thisMonthNewMember = (Integer)result.get("thisMonthNewMember");
        Integer totalMember = (Integer)result.get("totalMember");

        // 获取套餐数据
        List<Map> hotSetmeal = (List<Map>) result.get("hotSetmeal");
        //读取excel模板到流
        InputStream excelfileInputStream = this.getClass().getResourceAsStream("/report_template.xlsx");
        //构建workbook对象
        XSSFWorkbook workbook = new XSSFWorkbook(excelfileInputStream);
        //得到第一个sheet
        XSSFSheet sheet = workbook.getSheetAt(0);
        //得到第二行对象
        XSSFRow row = sheet.getRow(2);
        //得到第二行第五列对象并写入数据
        row.getCell(5).setCellValue(orderDate);

        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 rowstar=12;
        for (Map map : hotSetmeal) {
            String name = (String)map.get("name");
            Long setmealCount = (Long)map.get("setmeal_count");
            //在java.math包中提供的API类BigDecimal，用来对超过16位有效位的数进行精确的运算
            BigDecimal proportion = (BigDecimal) map.get("proportion");
            row=sheet.getRow(rowstar);
            row.getCell(4).setCellValue(name);
            row.getCell(5).setCellValue(setmealCount);
            row.getCell(6).setCellValue(proportion.doubleValue());
            rowstar++;
        }
        //获取输出客户端的流
            ServletOutputStream  outputStream = resp.getOutputStream();
        //告诉客户端文件类型
            resp.setContentType("application/vnd.ms-excel");
            resp.setHeader("content-Disposition","attachment;filename="+orderDate+"_report.xlsx");
            //将workbook输出到流
            workbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
            workbook.close();
            return null;

        } catch (IOException e) {
            e.printStackTrace();
            return new Result(false,MessageConst.ACTION_FAIL);
        }
    }

前端使用此种方式发送请求：

window.location.href = 'http://localhost:9002/report/exportBusinessReport.do';