• 苍穹外卖-day12 - 工作台 - Apache POI - 导出运营数据Excel报表

    课程内容

    功能实现：工作台、数据导出

    工作台效果图：

    数据导出效果图：

    在数据统计页面点击数据导出：生成Excel报表

    1. 工作台

    1.1 需求分析和设计

    1.1.1 产品原型

    工作台是系统运营的数据看板，并提供快捷操作入口，可以有效提高商家的工作效率。

    工作台展示的数据：

    • 今日数据

    • 订单管理

    • 菜品总览

    • 套餐总览

    • 订单信息

    原型图：

    名词解释：

    • 营业额：已完成订单的总金额

    • 有效订单：已完成订单的数量

    • 订单完成率：有效订单数 / 总订单数 * 100%

    • 平均客单价：营业额 / 有效订单数

    • 新增用户：新增用户的数量

    1.1.2 接口设计

    通过上述原型图分析，共包含6个接口。

    接口设计：

    • 今日数据接口

    • 订单管理接口

    • 菜品总览接口

    • 套餐总览接口

    • 订单搜索（已完成）

    • 各个状态的订单数量统计（已完成）

    1). 今日数据的接口设计

    2). 订单管理的接口设计

    3). 菜品总览的接口设计

    4). 套餐总览的接口设计

    1.2 代码导入

    直接导入课程资料中的工作台模块功能代码即可：

    1.2.1 Controller层

    添加WorkSpaceController.java

    1. package com.sky.controller.admin;
    2. ​
    3. import com.sky.result.Result;
    4. import com.sky.service.WorkspaceService;
    5. import com.sky.vo.BusinessDataVO;
    6. import com.sky.vo.DishOverViewVO;
    7. import com.sky.vo.OrderOverViewVO;
    8. import com.sky.vo.SetmealOverViewVO;
    9. import io.swagger.annotations.Api;
    10. import io.swagger.annotations.ApiOperation;
    11. import lombok.extern.slf4j.Slf4j;
    12. import org.springframework.beans.factory.annotation.Autowired;
    13. import org.springframework.web.bind.annotation.GetMapping;
    14. import org.springframework.web.bind.annotation.RequestMapping;
    15. import org.springframework.web.bind.annotation.RestController;
    16. import java.time.LocalDateTime;
    17. import java.time.LocalTime;
    18. ​
    19. /**
    20.  * 工作台
    21.  */
    22. @RestController
    23. @RequestMapping("/admin/workspace")
    24. @Slf4j
    25. @Api(tags = "工作台相关接口")
    26. public class WorkSpaceController {
    27. ​
    28.     @Autowired
    29.     private WorkspaceService workspaceService;
    30. ​
    31.     /**
    32.      * 工作台今日数据查询
    33.      * @return
    34.      */
    35.     @GetMapping("/businessData")
    36.     @ApiOperation("工作台今日数据查询")
    37.     public Result<BusinessDataVO> businessData(){
    38.         //获得当天的开始时间
    39.         LocalDateTime begin = LocalDateTime.now().with(LocalTime.MIN);
    40.         //获得当天的结束时间
    41.         LocalDateTime end = LocalDateTime.now().with(LocalTime.MAX);
    42. ​
    43.         BusinessDataVO businessDataVO = workspaceService.getBusinessData(begin, end);
    44.         return Result.success(businessDataVO);
    45.     }
    46. ​
    47.     /**
    48.      * 查询订单管理数据
    49.      * @return
    50.      */
    51.     @GetMapping("/overviewOrders")
    52.     @ApiOperation("查询订单管理数据")
    53.     public Result<OrderOverViewVO> orderOverView(){
    54.         return Result.success(workspaceService.getOrderOverView());
    55.     }
    56. ​
    57.     /**
    58.      * 查询菜品总览
    59.      * @return
    60.      */
    61.     @GetMapping("/overviewDishes")
    62.     @ApiOperation("查询菜品总览")
    63.     public Result<DishOverViewVO> dishOverView(){
    64.         return Result.success(workspaceService.getDishOverView());
    65.     }
    66. ​
    67.     /**
    68.      * 查询套餐总览
    69.      * @return
    70.      */
    71.     @GetMapping("/overviewSetmeals")
    72.     @ApiOperation("查询套餐总览")
    73.     public Result<SetmealOverViewVO> setmealOverView(){
    74.         return Result.success(workspaceService.getSetmealOverView());
    75.     }
    76. }

    1.2.2 Service层接口

    添加WorkspaceService.java

    1. package com.sky.service;
    2. ​
    3. import com.sky.vo.BusinessDataVO;
    4. import com.sky.vo.DishOverViewVO;
    5. import com.sky.vo.OrderOverViewVO;
    6. import com.sky.vo.SetmealOverViewVO;
    7. import java.time.LocalDateTime;
    8. ​
    9. public interface WorkspaceService {
    10. ​
    11.     /**
    12.      * 根据时间段统计营业数据
    13.      * @param begin
    14.      * @param end
    15.      * @return
    16.      */
    17.     BusinessDataVO getBusinessData(LocalDateTime begin, LocalDateTime end);
    18. ​
    19.     /**
    20.      * 查询订单管理数据
    21.      * @return
    22.      */
    23.     OrderOverViewVO getOrderOverView();
    24. ​
    25.     /**
    26.      * 查询菜品总览
    27.      * @return
    28.      */
    29.     DishOverViewVO getDishOverView();
    30. ​
    31.     /**
    32.      * 查询套餐总览
    33.      * @return
    34.      */
    35.     SetmealOverViewVO getSetmealOverView();
    36. ​
    37. }

    1.2.3 Service层实现类

    添加WorkspaceServiceImpl.java

    1. package com.sky.service.impl;
    2. ​
    3. import com.sky.constant.StatusConstant;
    4. import com.sky.entity.Orders;
    5. import com.sky.mapper.DishMapper;
    6. import com.sky.mapper.OrderMapper;
    7. import com.sky.mapper.SetmealMapper;
    8. import com.sky.mapper.UserMapper;
    9. import com.sky.service.WorkspaceService;
    10. import com.sky.vo.BusinessDataVO;
    11. import com.sky.vo.DishOverViewVO;
    12. import com.sky.vo.OrderOverViewVO;
    13. import com.sky.vo.SetmealOverViewVO;
    14. import lombok.extern.slf4j.Slf4j;
    15. import org.springframework.beans.factory.annotation.Autowired;
    16. import org.springframework.stereotype.Service;
    17. import java.time.LocalDateTime;
    18. import java.time.LocalTime;
    19. import java.util.HashMap;
    20. import java.util.Map;
    21. ​
    22. @Service
    23. @Slf4j
    24. public class WorkspaceServiceImpl implements WorkspaceService {
    25. ​
    26.     @Autowired
    27.     private OrderMapper orderMapper;
    28.     @Autowired
    29.     private UserMapper userMapper;
    30.     @Autowired
    31.     private DishMapper dishMapper;
    32.     @Autowired
    33.     private SetmealMapper setmealMapper;
    34. ​
    35.     /**
    36.      * 根据时间段统计营业数据
    37.      * @param begin
    38.      * @param end
    39.      * @return
    40.      */
    41.     public BusinessDataVO getBusinessData(LocalDateTime begin, LocalDateTime end) {
    42.         /**
    43.          * 营业额：当日已完成订单的总金额
    44.          * 有效订单：当日已完成订单的数量
    45.          * 订单完成率：有效订单数 / 总订单数
    46.          * 平均客单价：营业额 / 有效订单数
    47.          * 新增用户：当日新增用户的数量
    48.          */
    49. ​
    50.         Map map = new HashMap();
    51.         map.put("begin",begin);
    52.         map.put("end",end);
    53. ​
    54.         //查询总订单数
    55.         Integer totalOrderCount = orderMapper.countByMap(map);
    56. ​
    57.         map.put("status", Orders.COMPLETED);
    58.         //营业额
    59.         Double turnover = orderMapper.sumByMap(map);
    60.         turnover = turnover == null? 0.0 : turnover;
    61. ​
    62.         //有效订单数
    63.         Integer validOrderCount = orderMapper.countByMap(map);
    64. ​
    65.         Double unitPrice = 0.0;
    66. ​
    67.         Double orderCompletionRate = 0.0;
    68.         if(totalOrderCount != 0 && validOrderCount != 0){
    69.             //订单完成率
    70.             orderCompletionRate = validOrderCount.doubleValue() / totalOrderCount;
    71.             //平均客单价
    72.             unitPrice = turnover / validOrderCount;
    73.         }
    74. ​
    75.         //新增用户数
    76.         Integer newUsers = userMapper.countByMap(map);
    77. ​
    78.         return BusinessDataVO.builder()
    79.                 .turnover(turnover)
    80.                 .validOrderCount(validOrderCount)
    81.                 .orderCompletionRate(orderCompletionRate)
    82.                 .unitPrice(unitPrice)
    83.                 .newUsers(newUsers)
    84.                 .build();
    85.     }
    86. ​
    87. ​
    88.     /**
    89.      * 查询订单管理数据
    90.      *
    91.      * @return
    92.      */
    93.     public OrderOverViewVO getOrderOverView() {
    94.         Map map = new HashMap();
    95.         map.put("begin", LocalDateTime.now().with(LocalTime.MIN));
    96.         map.put("status", Orders.TO_BE_CONFIRMED);
    97. ​
    98.         //待接单
    99.         Integer waitingOrders = orderMapper.countByMap(map);
    100. ​
    101.         //待派送
    102.         map.put("status", Orders.CONFIRMED);
    103.         Integer deliveredOrders = orderMapper.countByMap(map);
    104. ​
    105.         //已完成
    106.         map.put("status", Orders.COMPLETED);
    107.         Integer completedOrders = orderMapper.countByMap(map);
    108. ​
    109.         //已取消
    110.         map.put("status", Orders.CANCELLED);
    111.         Integer cancelledOrders = orderMapper.countByMap(map);
    112. ​
    113.         //全部订单
    114.         map.put("status", null);
    115.         Integer allOrders = orderMapper.countByMap(map);
    116. ​
    117.         return OrderOverViewVO.builder()
    118.                 .waitingOrders(waitingOrders)
    119.                 .deliveredOrders(deliveredOrders)
    120.                 .completedOrders(completedOrders)
    121.                 .cancelledOrders(cancelledOrders)
    122.                 .allOrders(allOrders)
    123.                 .build();
    124.     }
    125. ​
    126.     /**
    127.      * 查询菜品总览
    128.      *
    129.      * @return
    130.      */
    131.     public DishOverViewVO getDishOverView() {
    132.         Map map = new HashMap();
    133.         map.put("status", StatusConstant.ENABLE);
    134.         Integer sold = dishMapper.countByMap(map);
    135. ​
    136.         map.put("status", StatusConstant.DISABLE);
    137.         Integer discontinued = dishMapper.countByMap(map);
    138. ​
    139.         return DishOverViewVO.builder()
    140.                 .sold(sold)
    141.                 .discontinued(discontinued)
    142.                 .build();
    143.     }
    144. ​
    145.     /**
    146.      * 查询套餐总览
    147.      *
    148.      * @return
    149.      */
    150.     public SetmealOverViewVO getSetmealOverView() {
    151.         Map map = new HashMap();
    152.         map.put("status", StatusConstant.ENABLE);
    153.         Integer sold = setmealMapper.countByMap(map);
    154. ​
    155.         map.put("status", StatusConstant.DISABLE);
    156.         Integer discontinued = setmealMapper.countByMap(map);
    157. ​
    158.         return SetmealOverViewVO.builder()
    159.                 .sold(sold)
    160.                 .discontinued(discontinued)
    161.                 .build();
    162.     }
    163. }

    1.2.4 Mapper层

    在SetmealMapper中添加countByMap方法定义

    1.     /**
    2.      * 根据条件统计套餐数量
    3.      * @param map
    4.      * @return
    5.      */
    6.     Integer countByMap(Map map);

    在SetmealMapper.xml中添加对应SQL实现

    1. <select id="countByMap" resultType="java.lang.Integer">
    2.         select count(id) from setmeal
    3.         <where>
    4.             <if test="status != null">
    5.                 and status = #{status}
    6.             </if>
    7.             <if test="categoryId != null">
    8.                 and category_id = #{categoryId}
    9.             </if>
    10.         </where>
    11. </select>

    在DishMapper中添加countByMap方法定义

    1.     /**
    2.      * 根据条件统计菜品数量
    3.      * @param map
    4.      * @return
    5.      */
    6.     Integer countByMap(Map map);

    在DishMapper.xml中添加对应SQL实现

    1. <select id="countByMap" resultType="java.lang.Integer">
    2.         select count(id) from dish
    3.         <where>
    4.             <if test="status != null">
    5.                 and status = #{status}
    6.             </if>
    7.             <if test="categoryId != null">
    8.                 and category_id = #{categoryId}
    9.             </if>
    10.         </where>
    11. </select>

    1.3 功能测试

    可以通过如下方式进行测试：

    • 通过接口文档测试

    • 前后端联调测试

    接下来我们使用上述两种方式分别测试。

    1.3.1 前后端联调测试

    启动nginx,访问 http://localhost，进入工作台

    进入开发者模式，分别查看今日数据、订单管理、菜品总览、套餐总览

    1). 今日数据查询

    2). 订单管理数据查询

    3). 菜品总览查询

    4). 套餐总览查询

    2. Apache POI

    2.1 介绍

    Apache POI 是一个处理Miscrosoft Office各种文件格式的开源项目。简单来说就是，我们可以使用 POI 在 Java 程序中对Miscrosoft Office各种文件进行读写操作。 一般情况下，POI 都是用于操作 Excel 文件。

    Apache POI 的应用场景：

    • 银行网银系统导出交易明细

    • 批量导入业务数据

    2.2 入门案例

    Apache POI既可以将数据写入Excel文件，也可以读取Excel文件中的数据，接下来分别进行实现。

    Apache POI的maven坐标：(项目中已导入)

    1. <dependency>
    2.     <groupId>org.apache.poi</groupId>
    3.     <artifactId>poi</artifactId>
    4.     <version>3.16</version>
    5. </dependency>
    6. <dependency>
    7.     <groupId>org.apache.poi</groupId>
    8.     <artifactId>poi-ooxml</artifactId>
    9.     <version>3.16</version>
    10. </dependency>

    2.2.1 将数据写入Excel文件

    1). 代码开发

    1. package com.sky.test;
    2. ​
    3. import org.apache.poi.xssf.usermodel.XSSFCell;
    4. import org.apache.poi.xssf.usermodel.XSSFRow;
    5. import org.apache.poi.xssf.usermodel.XSSFSheet;
    6. import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    7. import java.io.File;
    8. import java.io.FileInputStream;
    9. import java.io.FileOutputStream;
    10. ​
    11. public class POITest {
    12. ​
    13.     /**
    14.      * 基于POI向Excel文件写入数据
    15.      * @throws Exception
    16.      */
    17.     public static void write() throws Exception{
    18.         //在内存中创建一个Excel文件对象
    19.         XSSFWorkbook excel = new XSSFWorkbook();
    20.         //创建Sheet页
    21.         XSSFSheet sheet = excel.createSheet("itcast");
    22. ​
    23.         //在Sheet页中创建行，0表示第1行
    24.         XSSFRow row1 = sheet.createRow(0);
    25.         //创建单元格并在单元格中设置值，单元格编号也是从0开始，1表示第2个单元格
    26.         row1.createCell(1).setCellValue("姓名");
    27.         row1.createCell(2).setCellValue("城市");
    28. ​
    29.         XSSFRow row2 = sheet.createRow(1);
    30.         row2.createCell(1).setCellValue("张三");
    31.         row2.createCell(2).setCellValue("北京");
    32. ​
    33.         XSSFRow row3 = sheet.createRow(2);
    34.         row3.createCell(1).setCellValue("李四");
    35.         row3.createCell(2).setCellValue("上海");
    36. ​
    37.         FileOutputStream out = new FileOutputStream(new File("D:\\itcast.xlsx"));
    38.         //通过输出流将内存中的Excel文件写入到磁盘上
    39.         excel.write(out);
    40. ​
    41.         //关闭资源
    42.         out.flush();
    43.         out.close();
    44.         excel.close();
    45.     }
    46.     public static void main(String[] args) throws Exception {
    47.         write();
    48.     }
    49. }

    2). 实现效果

    在D盘中生成itcast.xlsx文件，创建名称为itcast的Sheet页，同时将内容成功写入。

    2.2.2 读取Excel文件中的数据

    1). 代码开发

    1. package com.sky.test;
    2. ​
    3. import org.apache.poi.xssf.usermodel.XSSFCell;
    4. import org.apache.poi.xssf.usermodel.XSSFRow;
    5. import org.apache.poi.xssf.usermodel.XSSFSheet;
    6. import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    7. import java.io.File;
    8. import java.io.FileInputStream;
    9. import java.io.FileOutputStream;
    10. ​
    11. public class POITest {
    12.     /**
    13.      * 基于POI读取Excel文件
    14.      * @throws Exception
    15.      */
    16.     public static void read() throws Exception{
    17.         FileInputStream in = new FileInputStream(new File("D:\\itcast.xlsx"));
    18.         //通过输入流读取指定的Excel文件
    19.         XSSFWorkbook excel = new XSSFWorkbook(in);
    20.         //获取Excel文件的第1个Sheet页
    21.         XSSFSheet sheet = excel.getSheetAt(0);
    22. ​
    23.         //获取Sheet页中的最后一行的行号
    24.         int lastRowNum = sheet.getLastRowNum();
    25. ​
    26.         for (int i = 0; i <= lastRowNum; i++) {
    27.             //获取Sheet页中的行
    28.             XSSFRow titleRow = sheet.getRow(i);
    29.             //获取行的第2个单元格
    30.             XSSFCell cell1 = titleRow.getCell(1);
    31.             //获取单元格中的文本内容
    32.             String cellValue1 = cell1.getStringCellValue();
    33.             //获取行的第3个单元格
    34.             XSSFCell cell2 = titleRow.getCell(2);
    35.             //获取单元格中的文本内容
    36.             String cellValue2 = cell2.getStringCellValue();
    37. ​
    38.             System.out.println(cellValue1 + " " +cellValue2);
    39.         }
    40. ​
    41.         //关闭资源
    42.         in.close();
    43.         excel.close();
    44.     }
    45. ​
    46.     public static void main(String[] args) throws Exception {
    47.         read();
    48.     }
    49. }
     

    2). 实现效果
     

    将itcast.xlsx文件中的数据进行读取
     

     

    3. 导出运营数据Excel报表
     

    3.1 需求分析和设计
     

    3.1.1 产品原型
     

    在数据统计页面，有一个数据导出的按钮，点击该按钮时，其实就会下载一个文件。这个文件实际上是一个Excel形式的文件，文件中主要包含最近30日运营相关的数据。表格的形式已经固定，主要由概览数据和明细数据两部分组成。真正导出这个报表之后，相对应的数字就会填充在表格中，就可以进行存档。
     

    原型图：
     

     

    导出的Excel报表格式：
     

     

    业务规则：
     

    •  
      导出Excel形式的报表文件
       
    •  
      导出最近30天的运营数据
       
     

     

    3.1.2 接口设计
     

    通过上述原型图设计对应的接口。
     

     

    注意：
     

    •  
      当前接口没有传递参数，因为导出的是最近30天的运营数据，后端计算即可，所以不需要任何参数
       
    •  
      当前接口没有返回数据，因为报表导出功能本质上是文件下载，服务端会通过输出流将Excel文件下载到客户端浏览器
       
     

     

    3.2 代码开发
     

    3.2.1 实现步骤
     

    1). 设计Excel模板文件
     

    2). 查询近30天的运营数据
     

    3). 将查询到的运营数据写入模板文件
     

    4). 通过输出流将Excel文件下载到客户端浏览器
     

     

    3.2.2 Controller层
     

    根据接口定义，在ReportController中创建export方法：
     

    1.     /**
    2.      * 导出运营数据报表
    3.      * @param response
    4.      */
    5.     @GetMapping("/export")
    6.     @ApiOperation("导出运营数据报表")
    7.     public void export(HttpServletResponse response){
    8.         reportService.exportBusinessData(response);
    9.     }
     

     

    3.2.3 Service层接口
     

    在ReportService接口中声明导出运营数据报表的方法：
     

    1.     /**
    2.      * 导出近30天的运营数据报表
    3.      * @param response
    4.      **/
    5.     void exportBusinessData(HttpServletResponse response);
     

     

    3.2.4 Service层实现类
     

    在ReportServiceImpl实现类中实现导出运营数据报表的方法:
     

    提前将资料中的运营数据报表模板.xlsx拷贝到项目的resources/template目录中
     

    1.     /**导出近30天的运营数据报表
    2.      * @param response
    3.      **/
    4.     public void exportBusinessData(HttpServletResponse response) {
    5.         LocalDate begin = LocalDate.now().minusDays(30);
    6.         LocalDate end = LocalDate.now().minusDays(1);
    7.         //查询概览运营数据，提供给Excel模板文件
    8.         BusinessDataVO businessData = workspaceService.getBusinessData(LocalDateTime.of(begin,LocalTime.MIN), LocalDateTime.of(end, LocalTime.MAX));
    9.         InputStream inputStream = this.getClass().getClassLoader().getResourceAsStream("template/运营数据报表模板.xlsx");
    10.         try {
    11.             //基于提供好的模板文件创建一个新的Excel表格对象
    12.             XSSFWorkbook excel = new XSSFWorkbook(inputStream);
    13.             //获得Excel文件中的一个Sheet页
    14.             XSSFSheet sheet = excel.getSheet("Sheet1");
    15. ​
    16.             sheet.getRow(1).getCell(1).setCellValue(begin + "至" + end);
    17.             //获得第4行
    18.             XSSFRow row = sheet.getRow(3);
    19.             //获取单元格
    20.             row.getCell(2).setCellValue(businessData.getTurnover());
    21.             row.getCell(4).setCellValue(businessData.getOrderCompletionRate());
    22.             row.getCell(6).setCellValue(businessData.getNewUsers());
    23.             row = sheet.getRow(4);
    24.             row.getCell(2).setCellValue(businessData.getValidOrderCount());
    25.             row.getCell(4).setCellValue(businessData.getUnitPrice());
    26.             for (int i = 0; i < 30; i++) {
    27.                 LocalDate date = begin.plusDays(i);
    28.                //准备明细数据
    29.                 businessData = workspaceService.getBusinessData(LocalDateTime.of(date,LocalTime.MIN), LocalDateTime.of(date, LocalTime.MAX));
    30.                 row = sheet.getRow(7 + i);
    31.                 row.getCell(1).setCellValue(date.toString());
    32.                 row.getCell(2).setCellValue(businessData.getTurnover());
    33.                 row.getCell(3).setCellValue(businessData.getValidOrderCount());
    34.                 row.getCell(4).setCellValue(businessData.getOrderCompletionRate());
    35.                 row.getCell(5).setCellValue(businessData.getUnitPrice());
    36.                 row.getCell(6).setCellValue(businessData.getNewUsers());
    37.             }
    38.             //通过输出流将文件下载到客户端浏览器中
    39.             ServletOutputStream out = response.getOutputStream();
    40.             excel.write(out);
    41.             //关闭资源
    42.             out.flush();
    43.             out.close();
    44.             excel.close();
    45. ​
    46.         }catch (IOException e){
    47.             e.printStackTrace();
    48.         }
    49.     }
     

     

    3.3 功能测试
     

    直接使用前后端联调测试。
     

    进入数据统计
     

     

    点击数据导出：Excel报表下载成功
     

     

    
                
    
                    
    
                
    • 

                
  • 

                    相关阅读:
    
                    
【力客热题HOT100】-【052】146 LRU缓存                            
    
kubernetes——快速部署                            
    
uniapp封装mixins实现H5和微信小程序的微信支付                            
    
【Java基础知识 46】GET和POST的区别，java模拟postman发post请求                            
    
北斗导航 | 最小二乘模糊度估计方法（LSAR：Least Squares Ambiguity Resolution）                            
    
【算法 | 模拟No.4】AcWing 756. 蛇形矩阵 & AcWing 40. 顺时针打印矩阵                            
    
【深度学习1】 BGD梯度下降算法                            
    
MongoDB综合实战篇（超容易）                            
    
java 字符串练习（罗马数字的转换）                            
    
《爆肝整理》保姆级系列教程-玩转Charles抓包神器教程(16)-Charles其他骚操作之大结局                            
    
                        
                
    • 
                
  • 
                    原文地址：https://blog.csdn.net/ros275229/article/details/136311985
                
    •