网站运营师跨越网站建设科技有限公司

网站运营师,跨越网站建设科技有限公司,公司网站优化推广方案,wordpress图片变小0. 今日总结 实现了工作台各项数据的显示了解了Apache POI:用于交互Microsoft的工具库基于Apache POI实现了导出运营数据excel报表 1. 工作台 1.1 需求分析和设计 1.2 代码开发 controller package com.sky.controller.admin;import com.sky.result.Result; import com.sky.…0. 今日总结实现了工作台各项数据的显示了解了Apache POI:用于交互Microsoft的工具库基于Apache POI实现了导出运营数据excel报表1. 工作台1.1 需求分析和设计1.2 代码开发controllerpackagecom.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;RestControllerRequestMapping(/admin/workspace)Api(tags工作台相关接口)Slf4jpublicclassWorkspaceController{AutowiredprivateWorkspaceServiceworkspaceService;/** * 今日运营数据 * * return */GetMapping(/businessData)ApiOperation(今日运营数据)publicResultBusinessDataVObusinessData(){log.info(今日运营数据{},LocalDate.now());BusinessDataVObusinessDataVOworkspaceService.businessData();returnResult.success(businessDataVO);}/** * 查询套餐总览 * * return */GetMapping(/overviewSetmeals)ApiOperation(查询套餐总览)publicResultSetmealOverViewVOoverviewData(){log.info(查询套餐总览);SetmealOverViewVOsetmealOverViewVOworkspaceService.overviewData();returnResult.success(setmealOverViewVO);}/** * 查询菜品总览 * * return */GetMapping(/overviewDishes)ApiOperation(查询菜品总览)publicResultDishOverViewVOdishOverviewData(){log.info(查询菜品总览);DishOverViewVOdishOverViewVOworkspaceService.dishOverviewData();returnResult.success(dishOverViewVO);}/** * 查询订单管理数据 * return */GetMapping(/overviewOrders)ApiOperation(查询订单管理数据)publicResultOrderOverViewVOorderOverviewData(){log.info(查询订单管理数据);OrderOverViewVOorderOverViewVOworkspaceService.orderOverviewData();returnResult.success(orderOverViewVO);}}servicepackagecom.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(){LocalDatetodayLocalDate.now();LocalDateTimebeginLocalDateTime.of(today,LocalTime.MIN);LocalDateTimeendLocalDateTime.of(today,LocalTime.MAX);MapmapnewHashMap();map.put(begin,begin);map.put(end,end);//新增用户数IntegernewUsersuserMapper.countByMap(map);//订单完成率//总订单数map.put(status,null);doubletotalOrdersorderMapper.getByBeginAndEnd(map);//有效订单数map.put(status,Orders.COMPLETED);doubletotalCompletionOrdersorderMapper.getByBeginAndEnd(map);//订单完成率doubleorderCompletionRatetotalCompletionOrders/totalOrders;//营业额DoublesumorderMapper.sumByMap(map);//平均客单价doubleunitPrice0.0;if(totalCompletionOrders!0){unitPricesum/totalCompletionOrders;}returnBusinessDataVO.builder().newUsers(newUsers).validOrderCount((int)totalCompletionOrders).orderCompletionRate(orderCompletionRate).turnover(sum).unitPrice(unitPrice).build();}/** * 查询套餐总览 * * return */OverridepublicSetmealOverViewVOoverviewData(){Integersold;Integerdiscontinued;Integerstatus;statusStatusConstant.ENABLE;soldsetmealMapper.countByStatus(status);statusStatusConstant.DISABLE;discontinuedsetmealMapper.countByStatus(status);returnSetmealOverViewVO.builder().sold(sold).discontinued(discontinued).build();}/** * 查询菜品总览 * * return */OverridepublicDishOverViewVOdishOverviewData(){Integersold;Integerdiscontinued;Integerstatus;statusStatusConstant.ENABLE;solddishMapper.countByStatus(status);statusStatusConstant.DISABLE;discontinueddishMapper.countByStatus(status);returnDishOverViewVO.builder().sold(sold).discontinued(discontinued).build();}/** * 查询订单管理数据 * * return */OverridepublicOrderOverViewVOorderOverviewData(){LocalDatetodayLocalDate.now();LocalDateTimebeginLocalDateTime.of(today,LocalTime.MIN);LocalDateTimeendLocalDateTime.of(today,LocalTime.MAX);MapmapnewHashMap();map.put(begin,begin);map.put(end,end);map.put(status,null);//所有订单doubleallOrdersorderMapper.getByBeginAndEnd(map);//已取消订单map.put(status,Orders.CANCELLED);doublecancelledOrdersorderMapper.getByBeginAndEnd(map);//已完成订单map.put(status,Orders.COMPLETED);doublecompletedOrdersorderMapper.getByBeginAndEnd(map);//待派送订单map.put(status,Orders.CONFIRMED);doubledeliveredOrdersorderMapper.getByBeginAndEnd(map);//待接单订单map.put(status,Orders.TO_BE_CONFIRMED);doublewaitingOrdersorderMapper.getByBeginAndEnd(map);returnOrderOverViewVO.builder().allOrders((int)allOrders).cancelledOrders((int)cancelledOrders).completedOrders((int)completedOrders).deliveredOrders((int)deliveredOrders).waitingOrders((int)waitingOrders).build();}}mapperSelect(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 POI2.1 介绍介绍Apache POl是一个处理Microsoft Office各种文件格式的开源项目。简单来说就是我们可以使用 POI 在 Java 程序中对Microsoft Office各种文件进行读写操作。一般情况下POI都是用于操作 Excel 文件。应用场景银行网银系统导出交易明细各种业务系统导出Excel报表批量导入业务数据2.2 入门案例导入maven坐标通过POI向文件中写入内容publicstaticvoidwrite()throwsIOException{XSSFWorkbookexcelnewXSSFWorkbook();XSSFSheetsheetexcel.createSheet(info);XSSFRowrowsheet.createRow(1);//创建单元格写入文件内容row.createCell(1).setCellValue(姓名);row.createCell(2).setCellValue(城市);//创建一个新行rowsheet.createRow(2);row.createCell(1).setCellValue(张三);row.createCell(2).setCellValue(北京);//创建一个新行rowsheet.createRow(3);row.createCell(1).setCellValue(李四);row.createCell(2).setCellValue(南京);//通过输出流吧内存中的excel文件写入磁盘FileOutputStreamoutnewFileOutputStream(newFile(D:\\gjh\\java\\javaFile\\info.xlsx));excel.write(out);//关闭资源out.close();excel.close();}通过POI从xlsx文件读取内容publicstaticvoidread()throwsException{FileInputStreaminputnewFileInputStream(newFile(D:\\gjh\\java\\javaFile\\info.xlsx));XSSFWorkbookexcelnewXSSFWorkbook(input);//读取excel文件中的第一个sheetXSSFSheetsheetexcel.getSheetAt(0);//获取sheet页中最后一行行号intlastRowNumsheet.getLastRowNum();for(inti1;ilastRowNum;i){//获得某一行XSSFRowrowsheet.getRow(i);//获得单元格StringcellValue1row.getCell(1).getStringCellValue();StringcellValue2row.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天的数据LocalDatedateBeginLocalDate.now().minusDays(30);LocalDatedateEndLocalDate.now().minusDays(1);LocalDateTimebeginLocalDateTime.of(dateBegin,LocalTime.MIN);LocalDateTimeendLocalDateTime.of(dateEnd,LocalTime.MAX);//查询概览数据BusinessDataVObusinessDataVOworkspaceService.businessData(begin,end);//2.通过POI将数据写入excel文件中InputStreaminthis.getClass().getClassLoader().getResourceAsStream(template/运营数据报表模板.xlsx);//基于模板文件创建一个新的excel文件try{XSSFWorkbookexcelnewXSSFWorkbook(in);//获取表格文件的Sheet页XSSFSheetsheetexcel.getSheet(sheet1);//填充数据--时间第二行sheet.getRow(1).getCell(1).setCellValue(时间:dateBegin至dateEnd);//获取第四行XSSFRowrowsheet.getRow(3);row.getCell(2).setCellValue(businessDataVO.getTurnover());row.getCell(4).setCellValue(businessDataVO.getOrderCompletionRate());row.getCell(6).setCellValue(businessDataVO.getNewUsers());//获取第五行rowsheet.getRow(4);row.getCell(2).setCellValue(businessDataVO.getValidOrderCount());row.getCell(4).setCellValue(businessDataVO.getUnitPrice());//填充明细数据for(inti0;i30;i){LocalDatedatedateBegin.plusDays(i);//查询某一天的营业数据BusinessDataVObusinessDataworkspaceService.businessData(LocalDateTime.of(date,LocalTime.MIN),LocalDateTime.of(date,LocalTime.MAX));//获取某一行if(businessData!null){rowsheet.getRow(7i);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对象获取输出流ServletOutputStreamoutresponse.getOutputStream();excel.write(out);//关闭资源out.close();excel.close();}catch(IOExceptione){thrownewRuntimeException(e);}}获取最近三十天的起始天和结束天的LocalDateTime对象调用workspaceService的businessData的方法查询概览数据通过POI将数据写入excel中通过反射技术获取当前类的类加载器然后让它从类路径classpath的根目录开始根据提供的相对路径去查找资源文件并返回一个可用于读取文件内容的输入流创建excel文件基于刚刚获取的模板的输入流填充运营数据报表时间和概览数据循环30次填充三十天的每天明细数据通过response对象获取输出流并将刚刚准备好的的数据通过excel.write写到输出流中excel.write(out)方法的本质是将内存中构建好的XSSFWorkbook对象按照Office Open XML (OOXML) 标准格式即.xlsx文件的本质序列化成一连串的字节并直接写入到这个网络流中3.3 功能测试