前言：本案例下载的是
.xlsx格式模板文件
模板文件位置
模板文件的样式
java代码
核心方法：exportBusinessReport()
package com.xxx.controller;
import com.alibaba.dubbo.config.annotation.Reference;
import com.xxx.constant.MessageConstant;
import com.xxx.entity.Result;
import com.xxx.service.MemberService;
import com.xxx.service.ReportService;
import com.xxx.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.io.FileInputStream;
import java.io.FileOutputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;
@RestController
@RequestMapping("/report")
public class ReportController {
@Reference
private MemberService memberService;
@Reference
private SetmealService setmealService;
@Reference
private ReportService reportService;
// 导出运营数据报表
@RequestMapping(value = "/exportBusinessReport")
public Result exportBusinessReport(HttpServletRequest request, HttpServletResponse response){
try {
// 远程调用报表服务获取报表数据
Map<String, Object> result = reportService.getBusinessReportData();
//取出返回结果数据，准备将报表数据写入到Excel文件中
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模板文件绝对路径
System.out.println("获取真实路径：" + request.getSession().getServletContext().getRealPath("template"));
String temlateRealPath = request.getSession().getServletContext().getRealPath("template") + File.separator+"report_template.xlsx";
//读取模板文件创建Excel表格对象
XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(new File(temlateRealPath)));
XSSFSheet sheet = workbook.getSheetAt(0);
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());//占比
}
// 通过输出流进行文件下载
ServletOutputStream out = response.getOutputStream();
// 下载的数据类型
response.setContentType("application/vnd.ms-excel");
// 设置下载形式
response.setHeader("content-Disposition", "attachment;filename=report.xlsx");
workbook.write(out);
out.flush();
out.close();
workbook.close();
return null;
} catch (Exception e) {
e.printStackTrace();
return new Result(false, MessageConstant.GET_BUSINESS_REPORT_FAIL,null);
}
}
// 运营数据统计
@RequestMapping(value = "/getBusinessReportData")
public Result getBusinessReportData(){
try {
Map<String,Object> map = reportService.getBusinessReportData();
// 成功
return new Result(true, MessageConstant.GET_BUSINESS_REPORT_SUCCESS,map);
} catch (Exception e) {
e.printStackTrace();
return new Result(false, MessageConstant.GET_BUSINESS_REPORT_FAIL);
}
}
// 预约套餐人数
@RequestMapping(value = "/getSetmealReport")
public Result getSetmealReport(){
// 获取预约套餐人数
List<Map<String,Object>> list = setmealService.getSetmealReport();
// 套餐名
List<String> setmealNames = new ArrayList<>();
for (Map<String, Object> setMealMap : list) {
setmealNames.add((String) setMealMap.get("name"));
}
Map<String,Object> map = new HashMap<>();
map.put("setmealCount",list);
map.put("setmealNames",setmealNames);
return new Result(true,MessageConstant.QUERY_SETMEALLIST_SUCCESS,map);
}
// 会员数量统计
@RequestMapping(value = "/getMemberReport")
public Result getMemberReport(){
// ① 创建一个日历对象，根据这个日历对象获取所有的月份
Calendar calendar = Calendar.getInstance();
calendar.add(Calendar.MONTH,-12);
List<String> months = new ArrayList<>();
for (int i = 0; i < 12; i++) {
calendar.add(Calendar.MONTH,1);
months.add(new SimpleDateFormat("yyyy-MM").format(calendar.getTime()));
}
// ② 查询会员数量
List<Integer> memberCount = memberService.findMemberCountByMonth(months);
// ③ 创建一个 map ,存储 months 和 memberCount
Map<String,Object> map = new HashMap<>();
map.put("months",months);
map.put("memberCount",memberCount);
return new Result(true, MessageConstant.GET_MEMBER_NUMBER_REPORT_SUCCESS,map);
}
}