springboot+poi 导出excle文件

.根据接口文档开发。完成一个简单的导出功能

 

1.导入pom依赖

<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>3.15</version>
</dependency>

2.把获取数据的方法写好， 这里直接上service层代码

ServiceImpl

 @Override
    public List<VApiRequestDaily> exportDailyBill(Long merchantUserId, String productCode, Date startDate, Date endDate)
    {
        return vApiRequestDailyMapper.getListByMerchantUserId(merchantUserId, productCode,
                                                              MdDateUtils.intValueOfDay(startDate),
                                                              MdDateUtils.intValueOfDay(endDate));
    }

3.BizImpl

 @Override
    public Workbook exportDailyBill(Long merchantUserId, String productCode, Date startDate, Date endDate)
    {
        List<VApiRequestDaily> dailyList =
                merchantService.exportDailyBill(merchantUserId, productCode, startDate, endDate);
        // 创建excel工作簿
        Workbook workbook = new XSSFWorkbook();
        // 创建第一个sheet（页），并命名
        Sheet sheet = workbook.createSheet("日账单");
        //设置首行标题标题
        Row row = sheet.createRow(0);
        row.createCell(0).setCellValue("统计日");
        row.createCell(1).setCellValue("请求次数");
        row.createCell(2).setCellValue("计费次数");
        row.createCell(3).setCellValue("计费金额");
        //新增数据行，并且设置单元格数据
        int rowNum = 1;
        for(VApiRequestDaily dailyBill : dailyList)
        {
            row = sheet.createRow(rowNum);
            row.createCell(0).setCellValue(formatIntDateVal(dailyBill.getStatsDay()));
            row.createCell(1).setCellValue(dailyBill.getRequestQty());
            row.createCell(2).setCellValue(dailyBill.getChargeAmt());
            row.createCell(3).setCellValue(dailyBill.getChargeAmt());
            rowNum++;
        }
        return workbook;
    }

Controller

   /**
     * 导出
     */
    @LoginRequired
    @GetMapping(value = "/user/{merchantUserId}/bill/export")
    public void getExcel(@PathVariable(value = "merchantUserId") Long merchantUserId,
            @RequestParam(value = "productCode") String productCode, @RequestParam(value = "startDate") Date startDate,
            @RequestParam(value = "endDate") Date endDate, HttpServletResponse response) throws Exception
    {
        Workbook workbook = merchantBiz.exportDailyBill(merchantUserId, productCode.toUpperCase(), startDate, endDate);
        response.setHeader("Content-type", "application/vnd.ms-excel");
        response.setCharacterEncoding("UTF-8");
        response.setHeader("Content-Disposition", "attachment;filename=" + System.currentTimeMillis() + ".xlsx");
        workbook.write(response.getOutputStream());
        workbook.close();
    }

用postman测试

成功