当前位置：首页 > article >正文

Java学习Day58：相声二人组！(项目统计数据Excel图表导出)

article 2024/12/26 1:26:11

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/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)=>{
                    this.reportData = res.data.data;
                });
            },
            methods:{
                exportExcel(){
                    window.location.href = '/report/exportBusinessReport.do';
                }
            }
        })
    </script>
</html>

后端代码，先查询表上的数据，封装成对应的前端需要的形式的Map集合

@GetMapping("/getBusinessReportData")
    public Result getBusinessReportData(){
        Map<Object,Object> reportData=memberService.getReportExcelResult();
        return new Result(true,"success",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}
     * ]

数据形式如上，封装过程如下：

@Override
    public Map<Object, Object> getReportExcelResult(){
        Map<Object,Object> resultMap=new HashMap<>();
        try {
            System.out.println("=============================");
            //reportDate
            String data= Date2Utils.parseDate2String(new Date());
            System.out.println(data);
            resultMap.put("reportDate",data);
            //todayNewMember
            int count = memberMapper.selectCurrentDayMemberCount(data);
            resultMap.put("todayNewMember",count);
            //totalMember
            int totalMember=memberMapper.seelectTotalMember();
            resultMap.put("totalMember",totalMember);
            //thisWeekNewMember
            int thisWeekNewMember=memberMapper.selectthisWeekNewMember(data);
            resultMap.put("thisWeekNewMember",thisWeekNewMember);
            //thisMonthNewMember
            int thisMonthNewMember=memberMapper.selectthisMonthNewMember();
            resultMap.put("thisMonthNewMember",thisMonthNewMember);
            //todayOrderNumber
            int todayOrderNumber=orderSettingMapper.selecttodayOrderNumber();
            resultMap.put("todayOrderNumber",todayOrderNumber);
            //thisWeekOrderNumber
            int thisWeekOrderNumber=orderSettingMapper.selectthisWeekOrderNumber();
            resultMap.put("thisWeekOrderNumber",thisWeekOrderNumber);
            //thisMonthOrderNumber
            int thisMonthOrderNumber=orderSettingMapper.selectthisMonthOrderNumber();
            resultMap.put("thisMonthOrderNumber",thisMonthOrderNumber);
            //todayVisitsNumber
            int todayVisitsNumber=orderSettingMapper.selecttodayVisitsNumber();
            resultMap.put("todayVisitsNumber",todayVisitsNumber);
            //thisWeekVisitsNumber
            int thisWeekVisitsNumber=orderSettingMapper.selectthisWeekVisitsNumber();
            resultMap.put("thisWeekVisitsNumber",thisWeekVisitsNumber);
            //thisMonthVisitsNumber
            int thisMonthVisitsNumber=orderSettingMapper.selectthisMonthVisitsNumber();
            resultMap.put("thisMonthVisitsNumber",thisMonthVisitsNumber);
            //hotSetmeal
            List<Map<String,String>> hotSetmeal=new ArrayList<>();
            hotSetmeal=orderSettingMapper.selecthotSetmeal();
            resultMap.put("hotSetmeal",hotSetmeal);
            System.out.println(resultMap);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return resultMap;
    }

以下是一组时间范围查询的相关的SQL记录：

    @Select("SELECT COUNT(0) " +
            "FROM`t_order`  " +
            "WHERE orderDate = CURDATE() AND orderStatus='已到诊'")
    int selecttodayVisitsNumber();

 @Select("SELECT COUNT(*) " +
            "FROM `t_order` " +
            "WHERE orderDate >= DATE_SUB(CURDATE(), INTERVAL 1 WEEK) " +
            "  AND orderDate <= CURDATE() AND orderStatus='已到诊';")
    int selectthisWeekVisitsNumber();

 @Select("SELECT COUNT(*) " +
            "FROM `t_order` " +
            "WHERE orderDate >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH) " +
            "AND orderDate <= CURDATE() AND orderStatus='已到诊';")
    int selectthisMonthVisitsNumber();

@Select("SELECT \n" +
            "    s.`name` AS 'name',\n" +
            "    COUNT(0) AS 'setmeal_count',\n" +
            "    COUNT(0) * 1.0 / (SELECT COUNT(*) FROM `t_order`) AS 'proportion'\n" +
            "    \n" +
            "FROM \n" +
            "    `t_order` o \n" +
            "LEFT JOIN \n" +
            "    `t_setmeal` s ON o.`setmeal_id` = s.`id`\n" +
            "GROUP BY \n" +
            "    s.`id` LIMIT 4;")
    List<Map<String, String>> selecthotSetmeal();

然后是根据查到的数据导入定制好的格式的Excel表格，并可以使之导出；

 @RequestMapping("/exportBusinessReport")
    public void exportBusinessReport(HttpServletRequest request, HttpServletResponse response) {
        Map<Object, Object> result = memberService.getReportExcelResult();
        String reportDate = (String) result.get("reportDate");
        Integer todayNewMember = (Integer) result.get("todayNewMember");
        Integer totalMember = (Integer) result.get("totalMember");
        Integer thisWeekNewMember = (Integer) result.get("thisWeekNewMember");
        Integer thisMonthNewMember = (Integer) result.get("thisMonthNewMember");
        Integer todayOrderNumber = (Integer) result.get("todayOrderNumber");
        Integer thisWeekOrderNumber = (Integer) result.get("thisWeekOrderNumber");
        Integer thisMonthOrderNumber = (Integer) result.get("thisMonthOrderNumber");
        Integer todayVisitsNumber = (Integer) result.get("todayVisitsNumber");
        Integer thisWeekVisitsNumber = (Integer) result.get("thisWeekVisitsNumber");
        Integer thisMonthVisitsNumber = (Integer) result.get("thisMonthVisitsNumber");
        List<Map> hotSetmeal = (List<Map>) result.get("hotSetmeal");
        //获得Excel模板文件绝对路径
        String temlateRealPath = request.getSession().getServletContext().getRealPath("template") +
                File.separator + "report_template.xlsx";
        /**
         * 读取模板文件创建Excel表格对象
         * File.separator：根据操作系统获取文件分隔符（Windows为\，Linux/Unix为/）
         */
        XSSFWorkbook workbook = null;
        //初始化XSSFWorkbook对象，用于表示Excel文件。
        try {
            //读取Excel模板文件
            workbook = new XSSFWorkbook(new FileInputStream(new File(temlateRealPath)));
            //获取Excel的第一个工作表：
            XSSFSheet sheet = workbook.getSheetAt(0);

            //示例：获取第3行（索引从0开始），第6个单元格（索引从0开始），并设置其值为reportDate
            XSSFRow row = sheet.getRow(2);
            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);//本月到诊数
            int rowNum = 12;
            for (Map map : hotSetmeal) {//热门套餐
                String name = (String) map.get("name");
                Long setmeal_count = (Long) map.get("setmeal_count");
                BigDecimal proportion = (BigDecimal) map.get("proportion");
                row = sheet.getRow(rowNum++);
                row.getCell(4).setCellValue(name);//套餐名称
                row.getCell(5).setCellValue(setmeal_count);//预约数量
                row.getCell(6).setCellValue(proportion.doubleValue());//占比
            }

            /**
             * 获取Servlet的输出流。
             * 设置响应的内容类型为Excel文件。
             * 设置响应头，指示浏览器将响应作为附件下载，并指定下载的文件名为report.xlsx。
             * 将workbook的内容写入输出流。
             */
            ServletOutputStream out = response.getOutputStream();
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("content-Disposition", "attachment;filename=report.xlsx");
            workbook.write(out);
            /**
             * 刷新输出流，确保所有数据都被写出。
             * 关闭输出流和workbook对象，释放资源。
             */
            out.flush();
            out.close();
            workbook.close();
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

知识补充：

1.使用【always-use-default-target="true"】

配置在springSecurity的登陆界面配置中

 <!--配置自己的登录页面-->
        <security:form-login login-page="/login.html" username-parameter="username"
        password-parameter="password" login-processing-url="/login2.do"
        default-target-url="/pages/main.html" authentication-failure-url="/login.html"
        always-use-default-target="true"/>

可以避免登陆后跳转未放行的404或304界面