poi导出excel-实现多个sheel

依赖

 <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>

 @RequestMapping(value = "export_Monthtongji", method = RequestMethod.GET)
    public void export_Monthtongji(HttpServletResponse response, String month) throws IOException {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet(month+"消费统计表");
        List<Mconsume> all = consumeService.monthTongji(month);
        String fileName = URLEncoder.encode(month+ "统计.xls");//设置要导出的文件的名字
        //新增数据行，并且设置单元格数据
        int rowNum = 1;
        String[] headers = { "部门","用户名","早餐总计","午餐总计","晚餐总计","总计","条数","时间",month+"月总和"};
        HSSFRow row = sheet.createRow(0);
        //在excel表中添加表头
        for(int i=0;i<headers.length;i++){
            HSSFCell cell = row.createCell(i);
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            cell.setCellValue(text);
        }
        Double a=0.0;
        //在表中存放查询到的数据放入对应的列
        for (Mconsume mconsume : all) {
            HSSFRow row1 = sheet.createRow(rowNum);
            a = a+mconsume.getSum();
            row1.createCell(0).setCellValue(mconsume.getDname());
            row1.createCell(1).setCellValue(mconsume.getUsername());
            row1.createCell(2).setCellValue(mconsume.getBreakfast());
            row1.createCell(3).setCellValue(mconsume.getLunch());
            row1.createCell(4).setCellValue(mconsume.getDinner());
            row1.createCell(5).setCellValue(mconsume.getSum());
            row1.createCell(6).setCellValue(mconsume.getCount());
            row1.createCell(7).setCellValue(mconsume.getTime());
            if(all.size()==rowNum){
                row1.createCell(8).setCellValue(a);
            }
            rowNum++;
        }

        //表格2
        //查询出该月所有数据
        List<Consume> consumes = consumeService.selectAllMonth(month);
        HSSFSheet sheet2 = workbook.createSheet(month+"消费详情表");
        String[] headers2 = { "部门","用户名","早餐","午餐","晚餐","总计","时间"};
        HSSFRow row2 = sheet2.createRow(0);
        //在excel表中添加表头
        int rowNum1 = 1;
        for(int i=0;i<headers2.length;i++){
            HSSFCell cell = row2.createCell(i);
            HSSFRichTextString text = new HSSFRichTextString(headers2[i]);
            cell.setCellValue(text);
        }
        //在表中存放查询到的数据放入对应的列
        for (Consume consume : consumes) {
            HSSFRow row1 = sheet2.createRow(rowNum1);
            row1.createCell(0).setCellValue(consume.getDname());
            row1.createCell(1).setCellValue(consume.getUsername());
            row1.createCell(2).setCellValue(consume.getBreakfast());
            row1.createCell(3).setCellValue(consume.getLunch());
            row1.createCell(4).setCellValue(consume.getDinner());
            row1.createCell(5).setCellValue(consume.getBreakfast()+consume.getLunch()+consume.getDinner());
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy年MM月dd日");
            row1.createCell(6).setCellValue(sdf.format(consume.getTime()));
            rowNum1++;
        }

        response.setContentType("application/octet-stream");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName);
        response.flushBuffer();
        workbook.write(response.getOutputStream());
    }