【苍穹外卖-day12】

0. 今日总结

1. 实现了工作台各项数据的显示
2. 了解了Apache POI:用于交互Microsoft的工具库
3. 基于Apache POI实现了导出运营数据excel报表

1. 工作台

1.1 需求分析和设计

1.2 代码开发

• controller

  packagecom.sky.controller.admin;importcom.sky.result.Result;importcom.sky.service.WorkspaceService;importcom.sky.vo.BusinessDataVO;importcom.sky.vo.DishOverViewVO;importcom.sky.vo.OrderOverViewVO;importcom.sky.vo.SetmealOverViewVO;importio.swagger.annotations.Api;importio.swagger.annotations.ApiOperation;importlombok.extern.slf4j.Slf4j;importorg.springframework.beans.factory.annotation.Autowired;importorg.springframework.web.bind.annotation.GetMapping;importorg.springframework.web.bind.annotation.RequestMapping;importorg.springframework.web.bind.annotation.RestController;importjava.time.LocalDate;@RestController@RequestMapping("/admin/workspace")@Api(tags="工作台相关接口")@Slf4jpublicclassWorkspaceController{@AutowiredprivateWorkspaceServiceworkspaceService;/** * 今日运营数据 * * @return */@GetMapping("/businessData")@ApiOperation("今日运营数据")publicResult<BusinessDataVO>businessData(){log.info("今日运营数据：{}",LocalDate.now());BusinessDataVObusinessDataVO=workspaceService.businessData();returnResult.success(businessDataVO);}/** * 查询套餐总览 * * @return */@GetMapping("/overviewSetmeals")@ApiOperation("查询套餐总览")publicResult<SetmealOverViewVO>overviewData(){log.info("查询套餐总览");SetmealOverViewVOsetmealOverViewVO=workspaceService.overviewData();returnResult.success(setmealOverViewVO);}/** * 查询菜品总览 * * @return */@GetMapping("/overviewDishes")@ApiOperation("查询菜品总览")publicResult<DishOverViewVO>dishOverviewData(){log.info("查询菜品总览");DishOverViewVOdishOverViewVO=workspaceService.dishOverviewData();returnResult.success(dishOverViewVO);}/** * 查询订单管理数据 * @return */@GetMapping("/overviewOrders")@ApiOperation("查询订单管理数据")publicResult<OrderOverViewVO>orderOverviewData(){log.info("查询订单管理数据");OrderOverViewVOorderOverViewVO=workspaceService.orderOverviewData();returnResult.success(orderOverViewVO);}}

• service

  packagecom.sky.service.impl;importcom.sky.constant.StatusConstant;importcom.sky.entity.Orders;importcom.sky.entity.Setmeal;importcom.sky.mapper.DishMapper;importcom.sky.mapper.OrderMapper;importcom.sky.mapper.SetmealMapper;importcom.sky.mapper.UserMapper;importcom.sky.service.WorkspaceService;importcom.sky.vo.BusinessDataVO;importcom.sky.vo.DishOverViewVO;importcom.sky.vo.OrderOverViewVO;importcom.sky.vo.SetmealOverViewVO;importorg.springframework.beans.factory.annotation.Autowired;importorg.springframework.stereotype.Service;importjava.time.LocalDate;importjava.time.LocalDateTime;importjava.time.LocalTime;importjava.util.HashMap;importjava.util.List;importjava.util.Map;@ServicepublicclassWorkspaceServiceImplimplementsWorkspaceService{@AutowiredprivateUserMapperuserMapper;@AutowiredprivateOrderMapperorderMapper;@AutowiredprivateSetmealMappersetmealMapper;@AutowiredprivateDishMapperdishMapper;/** * 今日运营数据 * * @return */@OverridepublicBusinessDataVObusinessData(){LocalDatetoday=LocalDate.now();LocalDateTimebegin=LocalDateTime.of(today,LocalTime.MIN);LocalDateTimeend=LocalDateTime.of(today,LocalTime.MAX);Mapmap=newHashMap();map.put("begin",begin);map.put("end",end);//新增用户数IntegernewUsers=userMapper.countByMap(map);//订单完成率//总订单数map.put("status",null);doubletotalOrders=orderMapper.getByBeginAndEnd(map);//有效订单数map.put("status",Orders.COMPLETED);doubletotalCompletionOrders=orderMapper.getByBeginAndEnd(map);//订单完成率doubleorderCompletionRate=totalCompletionOrders/totalOrders;//营业额Doublesum=orderMapper.sumByMap(map);//平均客单价doubleunitPrice=0.0;if(totalCompletionOrders!=0){unitPrice=sum/totalCompletionOrders;}returnBusinessDataVO.builder().newUsers(newUsers).validOrderCount((int)totalCompletionOrders).orderCompletionRate(orderCompletionRate).turnover(sum).unitPrice(unitPrice).build();}/** * 查询套餐总览 * * @return */@OverridepublicSetmealOverViewVOoverviewData(){Integersold;Integerdiscontinued;Integerstatus;status=StatusConstant.ENABLE;sold=setmealMapper.countByStatus(status);status=StatusConstant.DISABLE;discontinued=setmealMapper.countByStatus(status);returnSetmealOverViewVO.builder().sold(sold).discontinued(discontinued).build();}/** * 查询菜品总览 * * @return */@OverridepublicDishOverViewVOdishOverviewData(){Integersold;Integerdiscontinued;Integerstatus;status=StatusConstant.ENABLE;sold=dishMapper.countByStatus(status);status=StatusConstant.DISABLE;discontinued=dishMapper.countByStatus(status);returnDishOverViewVO.builder().sold(sold).discontinued(discontinued).build();}/** * 查询订单管理数据 * * @return */@OverridepublicOrderOverViewVOorderOverviewData(){LocalDatetoday=LocalDate.now();LocalDateTimebegin=LocalDateTime.of(today,LocalTime.MIN);LocalDateTimeend=LocalDateTime.of(today,LocalTime.MAX);Mapmap=newHashMap();map.put("begin",begin);map.put("end",end);map.put("status",null);//所有订单doubleallOrders=orderMapper.getByBeginAndEnd(map);//已取消订单map.put("status",Orders.CANCELLED);doublecancelledOrders=orderMapper.getByBeginAndEnd(map);//已完成订单map.put("status",Orders.COMPLETED);doublecompletedOrders=orderMapper.getByBeginAndEnd(map);//待派送订单map.put("status",Orders.CONFIRMED);doubledeliveredOrders=orderMapper.getByBeginAndEnd(map);//待接单订单map.put("status",Orders.TO_BE_CONFIRMED);doublewaitingOrders=orderMapper.getByBeginAndEnd(map);returnOrderOverViewVO.builder().allOrders((int)allOrders).cancelledOrders((int)cancelledOrders).completedOrders((int)completedOrders).deliveredOrders((int)deliveredOrders).waitingOrders((int)waitingOrders).build();}}

• mapper

  @Select("select count(id) from dish where status = #{status}")IntegercountByStatus(Integerstatus);

  @Select("select count(id) from setmeal where status = #{status}")IntegercountByStatus(Integerstatus);

1.3 功能测试

2. Apache POI

2.1 介绍

• 介绍

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

• 应用场景

  1. 银行网银系统导出交易明细
  2. 各种业务系统导出Excel报表
  3. 批量导入业务数据

2.2 入门案例

1. 导入maven坐标

2. 通过POI向文件中写入内容

   publicstaticvoidwrite()throwsIOException{XSSFWorkbookexcel=newXSSFWorkbook();XSSFSheetsheet=excel.createSheet("info");XSSFRowrow=sheet.createRow(1);//创建单元格，写入文件内容row.createCell(1).setCellValue("姓名");row.createCell(2).setCellValue("城市");//创建一个新行row=sheet.createRow(2);row.createCell(1).setCellValue("张三");row.createCell(2).setCellValue("北京");//创建一个新行row=sheet.createRow(3);row.createCell(1).setCellValue("李四");row.createCell(2).setCellValue("南京");//通过输出流吧内存中的excel文件写入磁盘FileOutputStreamout=newFileOutputStream(newFile("D:\\gjh\\java\\javaFile\\info.xlsx"));excel.write(out);//关闭资源out.close();excel.close();}

3. 通过POI从xlsx文件读取内容

   publicstaticvoidread()throwsException{FileInputStreaminput=newFileInputStream(newFile("D:\\gjh\\java\\javaFile\\info.xlsx"));XSSFWorkbookexcel=newXSSFWorkbook(input);//读取excel文件中的第一个sheetXSSFSheetsheet=excel.getSheetAt(0);//获取sheet页中最后一行行号intlastRowNum=sheet.getLastRowNum();for(inti=1;i<=lastRowNum;i++){//获得某一行XSSFRowrow=sheet.getRow(i);//获得单元格StringcellValue1=row.getCell(1).getStringCellValue();StringcellValue2=row.getCell(2).getStringCellValue();System.out.println(cellValue1+" "+cellValue2);}//关闭资源excel.close();input.close();}

3. 导出运营数据Excel报表

3.1 需求分析和设计

3.2 代码开发

• controller层

  HttpServletResponse是一个对前端的回信工具，常规情况下，如果返回数据是JSON类型的数据，则Spring MVC会调用HttpServletResponse自动封装并返回，但是这里返回值是void类型，我们要做的也是输出报表而非将数据返回给前端，因此获取HttpServletResponse对象，自行进行响应操作

• service层

  @OverridepublicvoidexportBusinessData(HttpServletResponseresponse){//1.查询数据库，获取营业数据，查询最近30天的数据LocalDatedateBegin=LocalDate.now().minusDays(30);LocalDatedateEnd=LocalDate.now().minusDays(1);LocalDateTimebegin=LocalDateTime.of(dateBegin,LocalTime.MIN);LocalDateTimeend=LocalDateTime.of(dateEnd,LocalTime.MAX);//查询概览数据BusinessDataVObusinessDataVO=workspaceService.businessData(begin,end);//2.通过POI将数据写入excel文件中InputStreamin=this.getClass().getClassLoader().getResourceAsStream("template/运营数据报表模板.xlsx");//基于模板文件创建一个新的excel文件try{XSSFWorkbookexcel=newXSSFWorkbook(in);//获取表格文件的Sheet页XSSFSheetsheet=excel.getSheet("sheet1");//填充数据--时间，第二行sheet.getRow(1).getCell(1).setCellValue("时间:"+dateBegin+"至"+dateEnd);//获取第四行XSSFRowrow=sheet.getRow(3);row.getCell(2).setCellValue(businessDataVO.getTurnover());row.getCell(4).setCellValue(businessDataVO.getOrderCompletionRate());row.getCell(6).setCellValue(businessDataVO.getNewUsers());//获取第五行row=sheet.getRow(4);row.getCell(2).setCellValue(businessDataVO.getValidOrderCount());row.getCell(4).setCellValue(businessDataVO.getUnitPrice());//填充明细数据for(inti=0;i<30;i++){LocalDatedate=dateBegin.plusDays(i);//查询某一天的营业数据BusinessDataVObusinessData=workspaceService.businessData(LocalDateTime.of(date,LocalTime.MIN),LocalDateTime.of(date,LocalTime.MAX));//获取某一行if(businessData!=null){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());}}//3.通过输出流将Excel文件下载到客户端浏览器//通过response对象获取输出流ServletOutputStreamout=response.getOutputStream();excel.write(out);//关闭资源out.close();excel.close();}catch(IOExceptione){thrownewRuntimeException(e);}}

1. 获取最近三十天的起始天和结束天的LocalDateTime对象

2. 调用workspaceService的businessData的方法，查询概览数据

3. 通过POI将数据写入excel中

   1. 通过反射技术获取当前类的类加载器，然后让它从类路径（classpath）的根目录开始，根据提供的相对路径去查找资源文件，并返回一个可用于读取文件内容的输入流

   2. 创建excel文件，基于刚刚获取的模板的输入流

   3. 填充运营数据报表时间和概览数据

   4. 循环30次填充三十天的每天明细数据

4. 通过response对象获取输出流，并将刚刚准备好的的数据通过excel.write写到输出流中（excel.write(out)方法的本质，是将内存中构建好的XSSFWorkbook对象，按照Office Open XML (OOXML) 标准格式（即.xlsx文件的本质）序列化成一连串的字节，并直接写入到这个网络流中）

3.3 功能测试