[TOC]

图形报表，POI报表

1：掌握Echarts实现套餐预约占比饼形图的实现过程
2：掌握运营数据统计的实现过程
3：掌握运营数据统计报表导出的实现过程

第一章 套餐预约占比饼形图

1.1. 需求分析

【目标】

会员可以通过移动端自助进行预约，在预约时需要选择预约的旅游套餐。本章节我们需要通过饼形图直观的展示出会员预约的各个套餐占比情况。统计订单中每个套餐的数量，展示效果如下图：
13_美年旅游_图表报表_V1.0 - 图1

【路径】

1：前台页面
（1）修改main.html
（2）导入ECharts库
（3）参照官方实例导入饼形图
（4）分析需要构造的数据格式和sql语句
（5）饼图API介绍
2：后台代码
（1）ReportController.java
（2）SetmealService.java
（3）SetmealServiceImpl.java
（4）SetmealDao.java
（5）SetmealDao.xml

【讲解】

1.2. 前台页面

套餐预约占比饼形图对应的页面为/pages/report_setmeal.html。
13_美年旅游_图表报表_V1.0 - 图2

1.2.1. 修改main.html

添加 report_setmeal.html 的 url
{
“path“: “/5-1”,
“title“: “会员数量统计”,
“linkUrl“:”report_member.html”,
“children“:[]
},
{
“path“: “/5-2”,
“title“: “预约套餐占比统计”,
“linkUrl“:”report_setmeal.html”,
“children“:[]
}

1.2.2. 导入ECharts库

<script src=”../js/echarts.js”></script>

1.2.3. 参照官方实例导入饼形图

<div class=”app-container”>
<div class=”box”>

<div id=”chart1” style=”height:600px;”></div>
</div>
</div>
Js代码：

（2）：数据展示
<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>

2.2.3.对应后台sql语句

— 今天新增会员数
— 总会员数
— 本周新增会员数(>=本周的周一的日期)
— 本月新增会员数(>=本月的第一天的日期)
———————————————————————————————————————-
— 今日预约数
— 今日已出游数
— 本周预约数(>=本周的周一的日期 <=本周的周日的日期)
— 本周已出游数
— 本月预约数(>=每月的第一天的日期 <=每月的最后一天的日期)
— 本月已出游数

— 热门套餐

— 今天新增会员数
SELECT COUNT() FROM t_member WHERE regTime = ‘2019-06-26’
— 总会员数
SELECT COUNT() FROM t_member
— 本周新增会员数(>=本周的周一的日期)
SELECT COUNT() FROM t_member WHERE regTime >= ‘2019-06-24’
— 本月新增会员数(>=本月的第一天的日期)
SELECT COUNT() FROM t_member WHERE regTime >= ‘2019-06-01’
———————————————————————————————————————-
— 今日预约数
SELECT COUNT() FROM t_order WHERE orderDate = ‘2019-06-26’
— 今日已出游数
SELECT COUNT() FROM t_order WHERE orderDate = ‘2019-06-26’ AND orderStatus = ‘已出游’
— 本周预约数(>=本周的周一的日期 <=本周的周日的日期)
SELECT COUNT() FROM t_order WHERE orderDate between ‘2019-06-24’ and ‘2019-06-31’
— 本周已出游数
SELECT COUNT() FROM t_order WHERE orderDate between ‘2019-06-24’ and ‘2019-06-31’ AND orderStatus = ‘已出游’
— 本月预约数(>=每月的第一天的日期 <=每月的最后一天的日期)
SELECT COUNT() FROM t_order WHERE orderDate between ‘2019-06-01’ and ‘2019-06-31’
— 本月已出游数
SELECT COUNT() FROM t_order WHERE orderDate between ‘2019-06-01’ and ‘2019-06-31’ AND orderStatus = ‘已出游’

— 热门套餐
SELECT s.name, COUNT(o.id) setmeal_count, COUNT(o.id)/(SELECT COUNT(id) FROM t_order ) proportion
FROM t_setmeal s, t_order o WHERE s.id = o.setmeal_id
GROUP BY s.name ORDER BY setmeal_count DESC LIMIT 0,4

2.2.4. 发送请求获取动态数据

（1）在VUE的钩子函数中发送ajax请求获取动态数据，通过VUE的数据绑定将数据展示到页面

（2）根据页面对数据格式的要求，我们发送ajax请求，服务端需要返回如下格式的数据：
{
“data“:{
“todayVisitsNumber“:0,
“reportDate“:”2019-04-25”,
“todayNewMember“:0,
“thisWeekVisitsNumber“:0,
“thisMonthNewMember“:2,
“thisWeekNewMember“:0,
“totalMember“:10,
“thisMonthOrderNumber“:2,
“thisMonthVisitsNumber“:0,
“todayOrderNumber“:0,
“thisWeekOrderNumber“:0,
“hotSetmeal“:[
{“proportion“:0.4545,”name“:”尚硅谷旅游套餐”,”setmeal_count“:5},
{“proportion“:0.1818,”name“:”新疆西藏10天10晚旅游套餐”,”setmeal_count“:2},
{“proportion“:0.1818,”name“:”乌鲁木齐旅游套餐”,”setmeal_count“:2},
{“proportion“:0.0909,”name“:”东北旅游套餐”,”setmeal_count“:1}
],
},
“flag“:true,
“message“:”获取运营统计数据成功”
}

2.3. 后台代码

2.3.1. Controller

在 ReportController 中提供 getBusinessReportData 方法
package com.atguigu.controller;

import com.alibaba.dubbo.config.annotation.Reference;
import com.atguigu.constant.MessageConstant;
import com.atguigu.entity.Result;
import com.atguigu.service.MemberService;
import com.atguigu.service.ReportService;
import com.atguigu.service.SetmealService;
import org.jboss.netty.util.internal.ReusableIterator;
import org.springframework.test.context.testng.AbstractTestNGSpringContextTests;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import java.text.SimpleDateFormat;
import java.util.;
import java.util.ArrayList;
import java.util.Calendar;
*import java.util.List;

@RestController
@RequestMapping(“/report”)
public class ReportController {

@Reference<br />ReportService reportService;

// 运营数据统计（页面）<br />    @RequestMapping(value = "/getBusinessReportData")<br />    **public** Result **getBusinessReportData**(){<br />        **try** {<br />            Map<String,Object> map = reportService.getBusinessReportData();<br />            **return** **new** **Result**(**true**, MessageConstant.GET_BUSINESS_REPORT_SUCCESS,map);<br />        } **catch** (Exception e) {<br />            e.printStackTrace();<br />            **return** **new** **Result**(**false**, MessageConstant.GET_BUSINESS_REPORT_FAIL);<br />        }<br />    }

2.3.2. 服务接口

在 meinian_interface 工程中创建 ReportService 服务接口并声明 getBusinessReport 方法
public interface ReportService {
/
获得运营统计数据
 Map数据格式：
reportDate（当前时间）—String
 todayNewMember（今日新增会员数） -> number
totalMember（总会员数） -> number
 thisWeekNewMember（本周新增会员数） -> number
thisMonthNewMember（本月新增会员数） -> number
 todayOrderNumber（今日预约数） -> number
todayVisitsNumber（今日出游数） -> number
 thisWeekOrderNumber（本周预约数） -> number
thisWeekVisitsNumber（本周出游数） -> number
 thisMonthOrderNumber（本月预约数） -> number
thisMonthVisitsNumber（本月出游数） -> number
 hotSetmeal（热门套餐（取前4）） -> List
*/
public** Map getBusinessReport();
}

2.3.3. 服务实现类

在 meinian_service 工程中创建服务实现类 ReportServiceImpl 并实现 ReportService 接口
package com.atguigu.service.impl;

import com.alibaba.dubbo.config.annotation.Service;
import com.atguigu.dao.MemberDao;
import com.atguigu.dao.OrderDao;
import com.atguigu.service.ReportService;
import com.atguigu.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;

@Service(interfaceClass = ReportService.class)
@Transactional
public class ReportServiceImpl implements ReportService {

@Autowired<br />    OrderDao orderDao;

@Autowired<br />    MemberDao memberDao;

// 运营统计数据<br />    @Override<br />    **public** Map<String, Object> getBusinessReportData() {<br />        Map<String,Object> map = **null**;<br />        **try** {<br />            // 日期工具类<br />            // 1：当前时间<br />            String today = DateUtils.parseDate2String(DateUtils.getToday());<br />            // 2：本周（周一）<br />            String weekMonday = DateUtils.parseDate2String(DateUtils.getThisWeekMonday());<br />            // 3：本周（周日）<br />            String weekSunday = DateUtils.parseDate2String(DateUtils.getSundayOfThisWeek());<br />            // 4：本月（1号）<br />            String monthFirst = DateUtils.parseDate2String(DateUtils.getFirstDay4ThisMonth());<br />            // 5：本月（31号）<br />            String monthLast = DateUtils.parseDate2String(DateUtils.getLastDay4ThisMonth());

        // （1）今日新增会员数<br />            **int** todayNewMember = memberDao.getTodayNewMember(today);<br />            // （2）总会员数<br />            **int** totalMember = memberDao.getTotalMember();<br />            // （3）本周新增会员数<br />            **int** thisWeekNewMember = memberDao.getThisWeekAndMonthNewMember(weekMonday);<br />            // （4）本月新增会员数<br />            **int** thisMonthNewMember = memberDao.getThisWeekAndMonthNewMember(monthFirst);

        // （5）今日预约数<br />            **int** todayOrderNumber = orderDao.getTodayOrderNumber(today);<br />            // （6）今日出游数<br />            **int** todayVisitsNumber = orderDao.getTodayVisitsNumber(today);<br />            // （7）本周预约数<br />            Map<String,Object> paramWeek = **new** HashMap<String,Object>();<br />            paramWeek.put("begin",weekMonday);<br />            paramWeek.put("end",weekSunday);<br />            **int** thisWeekOrderNumber = orderDao.getThisWeekAndMonthOrderNumber(paramWeek);<br />        // （8）本周出游数<br />            **int** thisWeekVisitsNumber = orderDao.getThisWeekAndMonthVisitsNumber(paramWeek);<br />            // （9）本月预约数<br />            Map<String,Object> paramMonth = **new** HashMap<String,Object>();<br />            paramMonth.put("begin",monthFirst);<br />            paramMonth.put("end",monthLast);<br />            **int** thisMonthOrderNumber = orderDao.getThisWeekAndMonthOrderNumber(paramMonth);<br />            // （10）本月出游数<br />            **int** thisMonthVisitsNumber = orderDao.getThisWeekAndMonthVisitsNumber(paramMonth);

        // （11）热门套餐<br />            List<Map<String,Object>> hotSetmeal = orderDao.findHotSetmeal();

        map = **new** HashMap<String,Object>();<br />            map.put("reportDate",today);<br />            map.put("todayNewMember",todayNewMember);<br />            map.put("totalMember",totalMember);<br />            map.put("thisWeekNewMember",thisWeekNewMember);<br />            map.put("thisMonthNewMember",thisMonthNewMember);

        map.put("todayOrderNumber",todayOrderNumber);<br />            map.put("todayVisitsNumber",todayVisitsNumber);<br />            map.put("thisWeekOrderNumber",thisWeekOrderNumber);<br />            map.put("thisWeekVisitsNumber",thisWeekVisitsNumber);<br />            map.put("thisMonthOrderNumber",thisMonthOrderNumber);<br />            map.put("thisMonthVisitsNumber",thisMonthVisitsNumber);

        map.put("hotSetmeal",hotSetmeal);<br />        } **catch** (Exception e) {<br />            e.printStackTrace();<br />        }<br />        **return** map;<br />    }<br />}

2.3.4. Dao接口

在OrderDao 和 MemberDao 中声明相关统计查询方法

2.3.4.1. OrderDao.java

package com.atguigu.dao;

import com.atguigu.pojo.Order;
import org.springframework.stereotype.Repository;
import java.util.List;
import java.util.Map;

public interface OrderDao {
int getTodayOrderNumber(String date);
int getTodayVisitsNumber(String date);
int getThisWeekAndMonthOrderNumber(Map map);
int getThisWeekAndMonthVisitsNumber(Map map);
List> findHotSetmeal();
}

2.3.4.2. MemberDao.java

package com.atguigu.dao;

import java.util.List;
import java.util.Map;

public interface MemberDao {
public Integer findMemberCountBeforeDate(String date);
List> findSetmealCount();
int getTodayNewMember(String date);
int getTotalMember();
int getThisWeekAndMonthNewMember(String date);
}

2.3.5. Mapper映射文件

在OrderDao.xml和MemberDao.xml中定义SQL语句

2.3.5.1. OrderDao.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.atguigu.dao.OrderDao”>

<!--今日预约数--><br />    <**select** **id**="getTodayOrderNumber" **parameterType**="string" **resultType**="int"><br />        SELECT COUNT(id) FROM t_order WHERE orderDate = #{date}<br />    </**select**>

<!--今日已出游数--><br />    <**select** **id**="getTodayVisitsNumber" **parameterType**="string" **resultType**="int"><br />        SELECT COUNT(id) FROM t_order WHERE orderDate = #{date} AND orderStatus = '已出游'<br />    </**select**>

<!--本周/本月，预约数--><br />    <**select** **id**="getThisWeekAndMonthOrderNumber" **parameterType**="map" **resultType**="int"><br />        SELECT COUNT(id) FROM t_order WHERE orderDate BETWEEN #{begin} AND #{end}<br />    </**select**><br />    <!--本周/本月，已出游数--><br />    <**select** **id**="getThisWeekAndMonthVisitsNumber" **parameterType**="map" **resultType**="int"><br />        SELECT COUNT(id) FROM t_order WHERE orderDate BETWEEN #{begin} AND #{end} AND orderStatus = '已出游'<br />    </**select**>

<!--热门套餐--><br />    <**select** **id**="findHotSetmeal" **resultType**="map"><br />        SELECT s.name name,COUNT(s.id) setmeal_count,COUNT(s.id)/(SELECT COUNT(id) FROM t_order) proportion<br />        FROM t_setmeal s,t_order o WHERE s.id = o.setmeal_id<br />        GROUP BY s.name<br />        ORDER BY setmeal_count DESC<br />        LIMIT 0,4<br />    </**select**><br /></**mapper**>

2.3.5.2. MemberDao.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.atguigu.dao.MemberDao”>

<!--根据日期统计会员数，统计指定日期之前的会员数<br />    注意：上午已经实现了这个方法<br />    --><br />    <**select** **id**="findMemberCountBeforeDate" **parameterType**="string" **resultType**="int"><br />        select count(id) from t_member where regTime &lt;= #{value}<br />    </**select**>

<!--今日新增会员数--><br />    <**select** **id**="getTodayNewMember" **parameterType**="string" **resultType**="int"><br />        SELECT COUNT(id) FROM t_member WHERE regTime = #{date}<br />    </**select**>

<!--总会员数--><br />    <**select** **id**="getTotalMember" **resultType**="int"><br />        SELECT COUNT(id) FROM t_member<br />    </**select**>

<!--本周/本月新增会员数--><br />    <**select** **id**="getThisWeekAndMonthNewMember" **parameterType**="string" **resultType**="int"><br />        SELECT COUNT(id) FROM t_member WHERE regTime &gt;= #{date}<br />    </**select**><br /></**mapper**>

【小结】

  1. 数据查询的比较多, 数据怎么封装 —>选择Map
  2. 数据怎么查询出来

— 今天新增会员数
SELECT COUNT() FROM t_member WHERE regTime = ‘2019-06-26’
— 总会员数
SELECT COUNT() FROM t_member
— 本周新增会员数(>=本周的周一的日期)
SELECT COUNT() FROM t_member WHERE regTime >= ‘2019-06-24’
— 本月新增会员数(>=本月的第一天的日期)
SELECT COUNT() FROM t_member WHERE regTime >= ‘2019-06-01’

— 今日预约数
SELECT COUNT() FROM t_order WHERE orderDate = ‘2019-06-26’
— 今日已出游数
SELECT COUNT() FROM t_order WHERE orderDate = ‘2019-06-26’ AND orderStatus = ‘已出游’
— 本周预约数(>=本周的周一的日期 <=本周的周日的日期)
SELECT COUNT() FROM t_order WHERE orderDate between ‘2019-06-24’ and ‘2019-06-31’
— 本周已出游数
SELECT COUNT() FROM t_order WHERE orderDate between ‘2019-06-24’ and ‘2019-06-31’ AND orderStatus = ‘已出游’
— 本月预约数(>=每月的第一天的日期 <=每月的最后一天的日期)
SELECT COUNT() FROM t_order WHERE orderDate between ‘2019-06-01’ and ‘2019-06-31’
— 本月已出游数
SELECT COUNT() FROM t_order WHERE orderDate between ‘2019-06-01’ and ‘2019-06-31’ AND orderStatus = ‘已出游’

— 热门套餐
SELECT s.name, COUNT(s.id) setmeal_count, COUNT(s.id)/(SELECT COUNT(id) FROM t_order ) proportion
FROM t_setmeal s, t_order o WHERE s.id = o.setmeal_id
GROUP BY s.name ORDER BY setmeal_count DESC LIMIT 0,4

第三章 运营数据统计报表导出

3.1. 需求分析

【目标】

运营数据统计报表导出就是将统计数据写入到Excel并提供给客户端浏览器进行下载，以便机构管理人员对运营数据的查看和存档。

【路径】

1：提供模板文件
2：前台代码

  • 在report_business.html页面提供“导出”按钮并绑定事件

3：后台代码
（1）ReportController.java
（2）ReportService.java
（3）ReportServiceImpl.java

【讲解】

3.2. 提供模板文件

本章节我们需要将运营统计数据通过POI写入到Excel文件，对应的Excel效果如下：
13_美年旅游_图表报表_V1.0 - 图3
通过上面的Excel效果可以看到，表格比较复杂，涉及到合并单元格、字体、字号、字体加粗、对齐方式等的设置。如果我们通过POI编程的方式来设置这些效果代码会非常繁琐。
在企业实际开发中，对于这种比较复杂的表格导出一般我们会提前设计一个Excel模板文件，在这个模板文件中提前将表格的结构和样式设置好，我们的程序只需要读取这个文件并在文件中的相应位置写入具体的值就可以了。
在本章节资料中已经提供了一个名为 report_template.xlsx 的模板文件
13_美年旅游_图表报表_V1.0 - 图4
需要将这个文件复制到 web_web工程中
13_美年旅游_图表报表_V1.0 - 图5

3.3. 前台代码

（1）在report_business.html页面提供“导出”按钮并绑定事件
<div class=”excelTitle” >
<el-button @click=”exportExcel”>导出Excel</el-button>运营数据统计
</div>
（2）导出方法
methods:{
exportExcel(){
window.location.href = ‘/report/exportBusinessReport.do’;
}
}

3.4. 后台代码

在 ReportController 中提供 exportBusinessReport 方法，基于POI将数据写入到Excel中并通过输出流下载到客户端
/
导出Excel报表
 @return
*/
@RequestMapping(“/exportBusinessReport”)
public Result exportBusinessReport(HttpSession session, HttpServletResponse response){
try**{
//远程调用报表服务获取报表数据
Map result = reportService.getBusinessReportData();

    //取出返回结果数据，准备将报表数据写入到Excel文件中<br />        String reportDate = (String) result.get("reportDate");<br />        Integer todayNewMember = (Integer) result.get("todayNewMember");<br />        Integer totalMember = (Integer) result.get("totalMember");<br />        Integer thisWeekNewMember = (Integer) result.get("thisWeekNewMember");<br />        Integer thisMonthNewMember = (Integer) result.get("thisMonthNewMember");<br />        Integer todayOrderNumber = (Integer) result.get("todayOrderNumber");<br />        Integer thisWeekOrderNumber = (Integer) result.get("thisWeekOrderNumber");<br />        Integer thisMonthOrderNumber = (Integer) result.get("thisMonthOrderNumber");<br />        Integer todayVisitsNumber = (Integer) result.get("todayVisitsNumber");<br />        Integer thisWeekVisitsNumber = (Integer) result.get("thisWeekVisitsNumber");<br />        Integer thisMonthVisitsNumber = (Integer) result.get("thisMonthVisitsNumber");<br />        List<Map> hotSetmeal = (List<Map>) result.get("hotSetmeal");

    //获取ServletContext对象<br />        ServletContext servletContext = session.getServletContext();<br />        //获取模板文件的真实路径<br />        String realPath = servletContext.getRealPath("/template/report_template.xlsx");

    //读取模板文件创建Excel表格对象<br />        XSSFWorkbook workbook = **new** XSSFWorkbook(**realPath**);<br />        XSSFSheet sheet = workbook.getSheetAt(0);

    XSSFRow row = sheet.getRow(2);<br />        row.getCell(5).setCellValue(reportDate);//日期

    row = sheet.getRow(4);<br />        row.getCell(5).setCellValue(todayNewMember);//新增会员数（本日）<br />        row.getCell(7).setCellValue(totalMember);//总会员数

    row = sheet.getRow(5);<br />        row.getCell(5).setCellValue(thisWeekNewMember);//本周新增会员数<br />        row.getCell(7).setCellValue(thisMonthNewMember);//本月新增会员数

    row = sheet.getRow(7);<br />        row.getCell(5).setCellValue(todayOrderNumber);//今日预约数<br />        row.getCell(7).setCellValue(todayVisitsNumber);//今日出游数

    row = sheet.getRow(8);<br />        row.getCell(5).setCellValue(thisWeekOrderNumber);//本周预约数<br />        row.getCell(7).setCellValue(thisWeekVisitsNumber);//本周出游数

    row = sheet.getRow(9);<br />        row.getCell(5).setCellValue(thisMonthOrderNumber);//本月预约数<br />        row.getCell(7).setCellValue(thisMonthVisitsNumber);//本月出游数

    **int** rowNum = 12;<br />        **for**(Map map : hotSetmeal){//热门套餐<br />            String name = (String) map.get("name");<br />            Long setmeal_count = (Long) map.get("setmeal_count");<br />            BigDecimal proportion = (BigDecimal) map.get("proportion");<br />            row = sheet.getRow(rowNum ++);<br />            row.getCell(4).setCellValue(name);//套餐名称<br />            row.getCell(5).setCellValue(setmeal_count);//预约数量<br />            row.getCell(6).setCellValue(proportion.doubleValue());//占比<br />        }

    //通过输出流进行文件下载<br />        ServletOutputStream out = response.getOutputStream();<br />     //获取下载文件的类型<br />        String mimeType = servletContext.getMimeType(realPath);<br />        // 设置下载的数据类型（excel类型）<br />        response.setContentType(mimeType);<br />        // 设置下载形式(通过附件的形式下载)<br />        response.setHeader("content-Disposition", "attachment;filename=report_business.xlsx");<br />        workbook.write(out);

    out.flush();<br />        out.close();<br />        workbook.close();

    **return** **null**;<br />    }**catch** (Exception e){<br />        **return** **new** **Result**(**false**, MessageConstant.GET_BUSINESS_REPORT_FAIL,**null**);<br />    }<br />}

【小结】

如果发现导出Excel有些复杂, 一般先把Excel制作一个模版. 把模版通过POI读取到内存里面. 获得数据, 动态的给模版里面填充数据, 再响应(Response)文件