报表导出： 1.设定excel模板：放入webapp下的template文件夹下 2.前端： <!DOCTYPE html> <html> <head> <!-- 页面meta --> <meta charset="utf-8"> <meta http-equiv="X-UA-Compatible" content="IE=edge"> <title>传智健康</title> <meta name="description" content="传智健康"> <meta name="keywords" content="传智健康"> <meta content="width=device-width,initial-scale=1,maximum-scale=1,user-scalable=no" name="viewport"> <!-- 引入样式 --> <link rel="stylesheet" href="../plugins/elementui/index.css"> <link rel="stylesheet" href="../plugins/font-awesome/css/font-awesome.min.css"> <link rel="stylesheet" href="../css/style.css"> <style> .grid-content { border-radius: 4px; min-height: 40px; } </style> </head> <body class="hold-transition"> <div id="app"> <div class="content-header"> <h1>统计分析<small>运营数据</small></h1> <el-breadcrumb separator-class="el-icon-arrow-right" class="breadcrumb"> <el-breadcrumb-item :to="{ path: '/' }">首页</el-breadcrumb-item> <el-breadcrumb-item>统计分析</el-breadcrumb-item> <el-breadcrumb-item>运营数据</el-breadcrumb-item> </el-breadcrumb> </div> <div class="app-container"> <div class="box" style="height: 900px"> <div class="excelTitle" > <el-button @click="exportExcel">导出Excel</el-button>运营数据统计 </div> <div class="excelTime">日期：{{reportData.reportDate}}</div> <table class="exceTable" cellspacing="0" cellpadding="0"> <tr> <td colspan="4" class="headBody">会员数据统计</td> </tr> <tr> <td width='20%' class="tabletrBg">新增会员数</td> <td width='30%'>{{reportData.todayNewMember}}</td> <td width='20%' class="tabletrBg">总会员数</td> <td width='30%'>{{reportData.totalMember}}</td> </tr> <tr> <td class="tabletrBg">本周新增会员数</td> <td>{{reportData.thisWeekNewMember}}</td> <td class="tabletrBg">本月新增会员数</td> <td>{{reportData.thisMonthNewMember}}</td> </tr> <tr> <td colspan="4" class="headBody">预约到诊数据统计</td> </tr> <tr> <td class="tabletrBg">今日预约数</td> <td>{{reportData.todayOrderNumber}}</td> <td class="tabletrBg">今日到诊数</td> <td>{{reportData.todayVisitsNumber}}</td> </tr> <tr> <td class="tabletrBg">本周预约数</td> <td>{{reportData.thisWeekOrderNumber}}</td> <td class="tabletrBg">本周到诊数</td> <td>{{reportData.thisWeekVisitsNumber}}</td> </tr> <tr> <td class="tabletrBg">本月预约数</td> <td>{{reportData.thisMonthOrderNumber}}</td> <td class="tabletrBg">本月到诊数</td> <td>{{reportData.thisMonthVisitsNumber}}</td> </tr> <tr> <td colspan="4" class="headBody">热门套餐</td> </tr> <tr class="tabletrBg textCenter"> <td>套餐名称</td> <td>预约数量</td> <td>占比</td> <td>备注</td> </tr> <tr v-for="s in reportData.hotSetmeal"> <td>{{s.name}}</td> <td>{{s.setmeal_count}}</td> <td>{{s.proportion}}</td> <td></td> </tr> </table> </div> </div> </div> </body> <!-- 引入组件库 --> <script src="../js/vue.js"></script> <script src="../plugins/elementui/index.js"></script> <script type="text/javascript" src="../js/jquery.min.js"></script> <script src="../js/axios-0.18.0.js"></script> <script> var vue = new Vue({ el: '#app', data:{ reportData:{ reportDate:null, todayNewMember :0, totalMember :0, thisWeekNewMember :0, thisMonthNewMember :0, todayOrderNumber :0, todayVisitsNumber :0, thisWeekOrderNumber :0, thisWeekVisitsNumber :0, thisMonthOrderNumber :0, thisMonthVisitsNumber :0, hotSetmeal :[ {name:'阳光爸妈升级肿瘤12项筛查（男女单人）体检套餐',setmeal_count:200,proportion:0.222}, {name:'阳光爸妈升级肿瘤12项筛查体检套餐',setmeal_count:200,proportion:0.222} ] } }, created() { axios.get("/report/getBusinessReportData.do").then((res)=>{ // 返回Result(flag,message,data) ,data封装的是Map<String,Object> /** * Map集合的key： map集合的value reportDate: todayNewMember : 2 totalMember : 2 thisWeekNewMember : 2 thisMonthNewMember : 2 todayOrderNumber : 2 todayVisitsNumber : 2 thisWeekOrderNumber : 2 thisWeekVisitsNumber : 2 thisMonthOrderNumber : 2 thisMonthVisitsNumber : 2 hotSetmeal : */ this.reportData = res.data.data; }); }, methods:{ exportExcel(){ window.location.href = '/report/exportBusinessReport.do'; } } }) </script> </html> 3.后端： 1.Controller: package com.itheima.health.controller; import com.alibaba.dubbo.config.annotation.Reference; import com.itheima.health.constant.MessageConstant; import com.itheima.health.entity.Result; import com.itheima.health.service.MemberService; import com.itheima.health.service.ReportService; import com.itheima.health.service.SetmealService; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; import javax.servlet.ServletOutputStream; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import java.io.File; import java.math.BigDecimal; import java.text.SimpleDateFormat; import java.util.*; /** * @ClassName ReportContoller * @Description TODO * @Author ly * @Company 深圳黑马程序员 * @Date 2020/2/13 16:04 * @Version V1.0 */ @RestController @RequestMapping(value = "/report") public class ReportContoller { @Reference// 订阅 dubbo注解 MemberService memberService; @Reference SetmealService setmealService; @Reference ReportService reportService; // 统计报表（会员数量折线图统计） @RequestMapping(value = "/getMemberReport") public Result getMemberReport(){ try { // 组织结果集 /** * 返回Map<String,Object> map集合的key： map集合的value： months List<String> -->['2019-06','2019-07'] memberCount List<Integer> -->[10,35] */ // 使用日历的工具类，统计过去12个月的时间 Map<String,Object> map = new HashMap<>(); // 存放到List<String> 对应key：months List<String> months = new ArrayList<>(); // 获取Calendar对象 Calendar calendar = Calendar.getInstance(); calendar.add(Calendar.MONTH,-12); // 获取过去12个月，2019-2 for (int i = 0; i < 12; i++) { // 过去的12个月，输出 calendar.add(Calendar.MONTH,1); //2019-3 Date date = calendar.getTime(); String sDate = new SimpleDateFormat("yyyy-MM").format(date); months.add(sDate); } // 组织每个月查询的会员数量（sql：SELECT COUNT(*) FROM t_member WHERE regTime<= '2019-04-31' ） List<Integer> memberCount = memberService.findCountByBeforeRegTime(months); // [2019-03, 2019-04, 2019-05, 2019-06, 2019-07, 2019-08, 2019-09, 2019-10, 2019-11, 2019-12, 2020-01, 2020-02] map.put("months",months); // [3, 4, 5, 7, ...] map.put("memberCount",memberCount); return new Result(true, MessageConstant.GET_MEMBER_NUMBER_REPORT_SUCCESS,map); } catch (Exception e) { e.printStackTrace(); return new Result(false, MessageConstant.GET_MEMBER_NUMBER_REPORT_FAIL); } } // 统计报表（套餐预约占比饼形图统计） @RequestMapping(value = "/getSetmealReport") public Result getSetmealReport(){ try { // 组织结果集 /** * 返回Map<String,Object> map集合的key： map集合的value： setmealNames List<String> -->['入职体检套餐','妇女节套餐'] setmealCount List<Map<String,Object>> -->[ {value: 335, name: '入职体检套餐'}, {value: 310, name: '妇女节套餐'} ] */ Map<String,Object> map = new HashMap<>(); // 组织List<Map> List<Map> setmealCount = setmealService.findOrderCountBySetmealName(); // 组织List<String> List<String> setmealNames = new ArrayList<>(); // 遍历setmealCount if(setmealCount!=null && setmealCount.size()>0){ for (Map setmealMap : setmealCount) { String name = (String)setmealMap.get("name"); setmealNames.add(name); } } map.put("setmealNames",setmealNames); map.put("setmealCount",setmealCount); return new Result(true, MessageConstant.GET_SETMEAL_COUNT_REPORT_SUCCESS,map); } catch (Exception e) { e.printStackTrace(); return new Result(false, MessageConstant.GET_SETMEAL_COUNT_REPORT_FAIL); } } /** * ################################################################################# #对应SQL # 一：会员相关 # reportDate:null, 时间（当前时间） # todayNewMember :0,：今天新增会员数 SELECT COUNT(id) FROM t_member WHERE regTime = '2020-02-27' # totalMember :0,：总会员数 SELECT COUNT(id) FROM t_member # thisWeekNewMember :0, :本周新增会员数（计算本周的周一） SELECT COUNT(id) FROM t_member WHERE regTime >= '2020-02-24' # thisMonthNewMember :0, ：本月新增会员数（计算本月的1号） SELECT COUNT(id) FROM t_member WHERE regTime >= '2020-02-01' # 二：预约订单相关 # todayOrderNumber :0,：今天预约人数 SELECT COUNT(id) FROM t_order WHERE orderDate = '2020-02-27' # todayVisitsNumber :0,：今天到诊人数 SELECT COUNT(id) FROM t_order WHERE orderDate = '2020-02-27' AND orderStatus = '已到诊' # thisWeekOrderNumber :0, ：本周预约人数（计算本周的周一，计算本周的周日） SELECT COUNT(id) FROM t_order WHERE orderDate BETWEEN '2020-02-24' AND '2020-03-01' # thisWeekVisitsNumber :0, ：本周到诊人数（计算本周的周一，计算本周的周日） SELECT COUNT(id) FROM t_order WHERE orderDate BETWEEN '2020-02-24' AND '2020-03-01' AND orderStatus = '已到诊' # thisMonthOrderNumber :0, ：本月预约人数（计算本月的1号，本月的最后1天） SELECT COUNT(id) FROM t_order WHERE orderDate BETWEEN '2020-02-01' AND '2020-02-29' # thisMonthVisitsNumber :0, ：本月到诊人数（计算本月的1号，本月的最后1天） SELECT COUNT(id) FROM t_order WHERE orderDate BETWEEN '2020-02-01' AND '2020-02-29' AND orderStatus = '已到诊' # 三：套餐相关 #hotSetmeal :[ # 热门套餐（预约最多的放置到最前面，显示最热门的4个） #{name:'阳光爸妈升级肿瘤12项筛查（男女单人）体检套餐',setmeal_count:200,proportion:0.222}, #{name:'阳光爸妈升级肿瘤12项筛查体检套餐',setmeal_count:200,proportion:0.222} #] SELECT s.name,COUNT(*) setmeal_count,COUNT(*)/(SELECT COUNT(*) FROM t_order) proportion FROM t_order o,t_setmeal s WHERE o.setmeal_id = s.id GROUP BY s.name ORDER BY setmeal_count DESC LIMIT 0,4 */ // 统计报表，运营数据统计 @RequestMapping(value = "/getBusinessReportData") public Result getBusinessReportData(){ try { Map<String,Object> map = reportService.findBusinessReportData(); return new Result(true, MessageConstant.GET_BUSINESS_REPORT_SUCCESS,map); } catch (Exception e) { e.printStackTrace(); return new Result(false, MessageConstant.GET_BUSINESS_REPORT_FAIL); } } // 统计报表，运营数据统计（导出excel报表） @RequestMapping(value = "/exportBusinessReport") public Result exportBusinessReport(HttpServletRequest request, HttpServletResponse response){ try { // 1：读取放置到excel中的内容数据 Map<String,Object> map = reportService.findBusinessReportData(); String reportDate = (String)map.get("reportDate");//String ：存放当前时间 Integer todayNewMember = (Integer)map.get("todayNewMember");//Integer Integer totalMember = (Integer)map.get("totalMember");//Integer Integer thisWeekNewMember = (Integer)map.get("thisWeekNewMember");//Integer Integer thisMonthNewMember = (Integer)map.get("thisMonthNewMember");//Integer Integer todayOrderNumber = (Integer)map.get("todayOrderNumber");//Integer Integer todayVisitsNumber = (Integer)map.get("todayVisitsNumber");//Integer Integer thisWeekOrderNumber = (Integer)map.get("thisWeekOrderNumber");//Integer Integer thisWeekVisitsNumber = (Integer)map.get("thisWeekVisitsNumber");//Integer Integer thisMonthOrderNumber = (Integer)map.get("thisMonthOrderNumber");//Integer Integer thisMonthVisitsNumber = (Integer)map.get("thisMonthVisitsNumber");//Integer List<Map> hotSetmeal = (List<Map>)map.get("hotSetmeal");//List<Map> // 2：加载模板文件（位置：/webapp/template/report_template.xlsx） // String path = request.getSession().getServletContext().getRealPath("template/report_template.xlsx"); String path = request.getSession().getServletContext().getRealPath("template")+ File.separator+"report_template.xlsx"; // 3：使用POI，读取工作簿，读取工作表，读取行，将数据填充到单元格 XSSFWorkbook workbook = new XSSFWorkbook(new File(path)); XSSFSheet sheet = workbook.getSheetAt(0); XSSFRow row = sheet.getRow(2); // 从0开始 // 日期 row.getCell(5).setCellValue(reportDate); // 会员相关 row = sheet.getRow(4); row.getCell(5).setCellValue(todayNewMember); row.getCell(7).setCellValue(totalMember); row = sheet.getRow(5); row.getCell(5).setCellValue(thisWeekNewMember); row.getCell(7).setCellValue(thisMonthNewMember); // 预约订单相关 row = sheet.getRow(7); row.getCell(5).setCellValue(todayOrderNumber); row.getCell(7).setCellValue(todayVisitsNumber); row = sheet.getRow(8); row.getCell(5).setCellValue(thisWeekOrderNumber); row.getCell(7).setCellValue(thisWeekVisitsNumber); row = sheet.getRow(9); row.getCell(5).setCellValue(thisMonthOrderNumber); row.getCell(7).setCellValue(thisMonthVisitsNumber); // 从12开始读取 int rownum = 12; if(hotSetmeal!=null && hotSetmeal.size()>0){ for (Map map1 : hotSetmeal) { String name = (String)map1.get("name"); Long setmeal_count = (Long)map1.get("setmeal_count"); BigDecimal proportion = (BigDecimal)map1.get("proportion"); row = sheet.getRow(rownum++); // ++放置到后面，先12，根据循环累加 row.getCell(4).setCellValue(name); row.getCell(5).setCellValue(setmeal_count); row.getCell(6).setCellValue(String.valueOf(proportion)); } } // 4：将excel文件以IO的形式导出（设置类型和下载方式） ServletOutputStream out = response.getOutputStream(); // 设置类型 response.setContentType("application/vnd.ms-excel"); // 不指定。默认是以文本的形式输出 // 设置下载方式（"attachment;filename="+filename：表示附件的方式下载；默认inline：表示内连，在浏览器上直接查看） String filename = "businessReport85.xlsx"; response.setHeader("Content-Disposition","attachment;filename="+filename); workbook.write(out); // 刷新和关闭 out.flush(); out.close(); workbook.close(); return null; } catch (Exception e) { e.printStackTrace(); return null; } } } 2.Service: package com.itheima.health.service.impl; import com.alibaba.dubbo.config.annotation.Service; import com.itheima.health.dao.MemberDao; import com.itheima.health.dao.OrderDao; import com.itheima.health.service.ReportService; import com.itheima.health.utils.DateUtils; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.transaction.annotation.Transactional; import java.util.HashMap; import java.util.List; import java.util.Map; /** * @ClassName ReportServiceImpl * @Description TODO * @Author ly * @Company 深圳黑马程序员 * @Date 2020/2/13 16:03 * @Version V1.0 */ @Service // dubbo提供 @Transactional public class ReportServiceImpl implements ReportService { // 订单相关 @Autowired OrderDao orderDao; // 会员相关 @Autowired MemberDao memberDao; @Override public Map<String, Object> findBusinessReportData() { Map<String,Object> map = new HashMap<>(); try { // 当前时间 String date = DateUtils.parseDate2String(DateUtils.getToday()); // 计算本周的周一 String mondayOfWeek = DateUtils.parseDate2String(DateUtils.getThisWeekMonday()); // 计算本周的周日 String sundayOfWeek = DateUtils.parseDate2String(DateUtils.getSundayOfThisWeek()); // 计算本月的1号 String firstDatyOfMonth = DateUtils.parseDate2String(DateUtils.getFirstDay4ThisMonth()); // 计算本月的最后1天 String lastDatyOfMonth = DateUtils.parseDate2String(DateUtils.getLastDay4ThisMonth()); /**会员相关统计数据*/ // 今日新增会员数 Integer todayNewMember = memberDao.findTodayNewMember(date); // 总会员数 Integer totalMember = memberDao.findTotalMember(); // 本周新增会员数 Integer thisWeekNewMember = memberDao.findThisWeekAndMonthNewMember(mondayOfWeek); // 本月新增会员数 Integer thisMonthNewMember = memberDao.findThisWeekAndMonthNewMember(firstDatyOfMonth); /**预约订单相关统计数据*/ // 今日预约数 Integer todayOrderNumber = orderDao.findTodayOrderNumber(date); // 今日到诊数 Integer todayVisitsNumber = orderDao.findTodayVisitsNumber(date); // 周一到周日的参数 Map weekMap = new HashMap(); weekMap.put("begin",mondayOfWeek); weekMap.put("end",sundayOfWeek); // 1号到最后1号的参数 Map monthMap = new HashMap(); monthMap.put("begin",firstDatyOfMonth); monthMap.put("end",lastDatyOfMonth); // 本周预约数 Integer thisWeekOrderNumber = orderDao.findThisWeekAndMonthOrderNumber(weekMap); // 本周到诊数 Integer thisWeekVisitsNumber = orderDao.findThisWeekAndMonthVisitsNumber(weekMap); // 本月预约数 Integer thisMonthOrderNumber = orderDao.findThisWeekAndMonthOrderNumber(monthMap); // 本月到诊数 Integer thisMonthVisitsNumber = orderDao.findThisWeekAndMonthVisitsNumber(monthMap); // 热门套餐 List<Map> hotSetmeal = orderDao.findHotSetmeal(); map.put("reportDate",date); //String ：存放当前时间 map.put("todayNewMember",todayNewMember); //Integer map.put("totalMember",totalMember); //Integer map.put("thisWeekNewMember",thisWeekNewMember); //Integer map.put("thisMonthNewMember",thisMonthNewMember); //Integer map.put("todayOrderNumber",todayOrderNumber); //Integer map.put("todayVisitsNumber",todayVisitsNumber); //Integer map.put("thisWeekOrderNumber",thisWeekOrderNumber); //Integer map.put("thisWeekVisitsNumber",thisWeekVisitsNumber); //Integer map.put("thisMonthOrderNumber",thisMonthOrderNumber); //Integer map.put("thisMonthVisitsNumber",thisMonthVisitsNumber); //Integer map.put("hotSetmeal",hotSetmeal); //List<Map> } catch (Exception e) { e.printStackTrace(); throw new RuntimeException("抛出运行时异常..."); } return map; } } 3.Dao: package com.itheima.health.dao; import com.itheima.health.pojo.Order; import java.util.List; import java.util.Map; public interface OrderDao { List<Order> findOrderListByCondition(Order order); void add(Order order); Map findById(Integer id); Integer findTodayOrderNumber(String date); Integer findTodayVisitsNumber(String date); Integer findThisWeekAndMonthOrderNumber(Map map); Integer findThisWeekAndMonthVisitsNumber(Map map); List<Map> findHotSetmeal(); } 4.Dao.xml: <?xml version="1.0" encoding="utf-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" > <mapper namespace="com.itheima.health.dao.OrderDao"> <!--根据查询条件，查询预约订单表 SELECT * FROM t_order WHERE 1=1 AND member_id = 1 AND orderDate = '2020-02-29' AND setmeal_id = 6 将sql变成动态sql --> <!--使用resultMap处理当属性和数据库的字段不一致的情况--> <resultMap id="orderMap" type="order"> <id property="id" column="id"></id> <result property="memberId" column="member_id"></result> <result property="orderDate" column="orderDate"></result> <result property="orderType" column="orderType"></result> <result property="orderStatus" column="orderStatus"></result> <result property="setmealId" column="setmeal_id"></result> </resultMap> <select id="findOrderListByCondition" parameterType="order" resultMap="orderMap"> SELECT * FROM t_order <where> <if test="memberId!=null"> and member_id = #{memberId} </if> <if test="orderDate!=null"> and orderDate = #{orderDate} </if> <if test="orderType!=null"> and orderType = #{orderType} </if> <if test="orderStatus!=null"> and orderStatus = #{orderStatus} </if> <if test="setmealId!=null"> and setmeal_id = #{setmealId} </if> </where> </select> <!--新增预约订单表--> <insert id="add" parameterType="order"> <selectKey resultType="int" order="AFTER" keyProperty="id"> select last_insert_id() </selectKey> INSERT INTO t_order(member_id,orderDate,orderType,orderStatus,setmeal_id) VALUES(#{memberId},#{orderDate},#{orderType},#{orderStatus},#{setmealId}) </insert> <!--使用订单id，查询订单--> <select id="findById" parameterType="int" resultType="map"> SELECT m.name member,s.name setmeal,o.orderDate,o.orderType FROM t_order o,t_member m,t_setmeal s WHERE o.member_id = m.id AND o.setmeal_id = s.id AND o.id = #{id} </select> <!--今日预约数--> <select id="findTodayOrderNumber" parameterType="string" resultType="int"> SELECT COUNT(id) FROM t_order WHERE orderDate = #{date} </select> <!--今日到诊数--> <select id="findTodayVisitsNumber" parameterType="string" resultType="int"> SELECT COUNT(id) FROM t_order WHERE orderDate = #{date} AND orderStatus = '已到诊' </select> <!--本周/本月预约数--> <select id="findThisWeekAndMonthOrderNumber" parameterType="map" resultType="int"> SELECT COUNT(id) FROM t_order WHERE orderDate BETWEEN #{begin} AND #{end} </select> <!--本周/本月到诊数--> <select id="findThisWeekAndMonthVisitsNumber" parameterType="map" resultType="int"> SELECT COUNT(id) FROM t_order WHERE orderDate BETWEEN #{begin} AND #{end} AND orderStatus = '已到诊' </select> <!--热门套餐--> <select id="findHotSetmeal" resultType="map"> SELECT s.name,COUNT(*) setmeal_count,COUNT(*)/(SELECT COUNT(*) FROM t_order) proportion FROM t_order o,t_setmeal s WHERE o.setmeal_id = s.id GROUP BY s.name ORDER BY setmeal_count DESC LIMIT 0,4 </select> </mapper>