Springboot实现导出报表功能

业务：导出近30天的营业额

1、准备好报表模板并引入到项目中

 

 

 

1、引入依赖

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

2、实现controller

    /*导出数据报表*/
    @GetMapping("/export")
    public Result export(HttpServletResponse response){

        reportService.exportBusinessData(response);

        return Result.success();
    }

3、service实现类

    /*导出报表数据*/
    @Override
    public void exportBusinessData(HttpServletResponse response) {
        /*获取时间*/
        //30天前的数据
        LocalDate begin = LocalDate.now().minusDays(30);
        //一天前的数据
        LocalDate end = LocalDate.now().minusDays(1);

        /*查询数据库*/
        BusinessDataVO businessData =
                workspaceService.getBusinessData
                        (LocalDateTime.of(begin,LocalTime.MIN),
                                LocalDateTime.of(end,LocalTime.MAX));
        /*获取类路径下的模板文件 运营数据报表模板.xlsx*/
        InputStream inputStream = this.getClass()
                .getClassLoader()
                .getResourceAsStream
                        ("template/运营数据报表模板.xlsx");
        System.out.println("inputStream" + inputStream);
        try {
            //基于提供好的文件创建一个新的Excel文件
            XSSFWorkbook excel = new XSSFWorkbook(inputStream);
            //获取Excel文件中的一个sheet页
            XSSFSheet sheet = excel.getSheet("Sheet1");

            //在第一行第一列存储数据
            sheet.getRow(1).getCell(1).setCellValue(begin + "至" + end);
            //获得第4行
            XSSFRow row = sheet.getRow(3);
            //获取单元格并存储数据
            row.getCell(2).setCellValue(businessData.getTurnover());
            row.getCell(4).setCellValue(businessData.getOrderCompletionRate());
            row.getCell(6).setCellValue(businessData.getNewUsers());
            row = sheet.getRow(4);
            row.getCell(2).setCellValue(businessData.getValidOrderCount());
            row.getCell(4).setCellValue(businessData.getUnitPrice());

            //循环添加30天的明细数据
            for (int i = 0; i < 30; i++) {
                LocalDate date = begin.plusDays(i);
                //准备明细数据
                businessData = workspaceService.getBusinessData(LocalDateTime.of(date,LocalTime.MIN), LocalDateTime.of(date, LocalTime.MAX));
                //从第8行开始循环添加
                row = sheet.getRow(7 + i);
                row.getCell(1).setCellValue(date.toString());
                row.getCell(2).setCellValue(businessData.getTurnover());
                row.getCell(3).setCellValue(businessData.getValidOrderCount());
                row.getCell(4).setCellValue(businessData.getOrderCompletionRate());
                row.getCell(5).setCellValue(businessData.getUnitPrice());
                row.getCell(6).setCellValue(businessData.getNewUsers());
            }
            //通过输出流将文件下载到客户端浏览器中
            ServletOutputStream out = response.getOutputStream();
            excel.write(out);
            //关闭资源
            out.flush();
            out.close();
            excel.close();

        } catch (IOException e) {
            e.printStackTrace();
        }
    }

3、订单查询类的service实现类

    /**
     * 根据时间段统计营业数据
     * @param begin
     * @param end
     * @return
     */
    public BusinessDataVO getBusinessData(LocalDateTime begin, LocalDateTime end) {
        /**
         * 营业额：当日已完成订单的总金额
         * 有效订单：当日已完成订单的数量
         * 订单完成率：有效订单数 / 总订单数
         * 平均客单价：营业额 / 有效订单数
         * 新增用户：当日新增用户的数量
         */

        Map map = new HashMap();
        map.put("begin",begin);
        map.put("end",end);

        //查询总订单数
        Integer totalOrderCount = orderMapper.countByOrder(map);

        map.put("status", Orders.COMPLETED);
        //营业额
        Double turnover = orderMapper.sumByMap(map);
        turnover = turnover == null? 0.0 : turnover;

        //有效订单数
        Integer validOrderCount = orderMapper.countByOrder(map);

        Double unitPrice = 0.0;

        Double orderCompletionRate = 0.0;
        if(totalOrderCount != 0 && validOrderCount != 0){
            //订单完成率
            orderCompletionRate = validOrderCount.doubleValue() / totalOrderCount;
            //平均客单价
            unitPrice = turnover / validOrderCount;
        }

        //新增用户数
        Integer newUsers = userMapper.countByMap(map);

        return BusinessDataVO.builder()
                .turnover(turnover)
                .validOrderCount(validOrderCount)
                .orderCompletionRate(orderCompletionRate)
                .unitPrice(unitPrice)
                .newUsers(newUsers)
                .build();
    }

4、这里省去mapper层的查询

5、测试

        在前端点击导出后打开文件数据已存在