管理系统功能实例-CSDN博客

本文链接：https://blog.csdn.net/WNC32475/article/details/134752929

设计一个能够通过多线程导出表格数据到指定可多人的邮箱，配置不一样，学习思想就行。
前端做好页面发送请求到后端，后端接收请求开启线程，并返回成功响应，前端接收回调，可做延时也可做提示，后端查找数据用二维数组装载，然后导出时定义表头后填充数据，最后发送邮箱，还有就是定时发布任务到指定邮箱。

前端

@layout("/common/_container.html"){
<div class="row">
    <div class="col-sm-12">
        <div class="ibox float-e-margins">
            <div class="ibox-title">
                <h5>币包售出情况</h5>
            </div>
            <div class="ibox-content">
                <div class="row row-lg">
                    <div class="col-sm-12">
                        <div class="row" style="margin-top: 5px">
                            <div class="col-sm-1">
                                <select id="timeQuickSelect" style="border: 1px solid #cccccc;outline: none;background-color: white;height: 34px">
                                    <option value="" selected>时间快捷选择</option>
                                    <option value="thisweek">本周</option>
                                    <option value="lastweek">上周</option>
                                    <option value="thismonth">本月</option>
                                    <option value="lastmonth">上月</option>
                                </select>
                            </div>
                            <div class="col-sm-2">
                                <#TimeCon id="datebeginTime" name="开始时间" isTime="false" pattern="YYYY-MM-DD" />
                            </div>
                            <div class="col-sm-2">
                                <#TimeCon id="dateendTime" name="结束时间" isTime="false" pattern="YYYY-MM-DD" />
                            </div>
                            <div class="col-sm-4">
                                <#NameCon id="mailboxs" name="邮箱（“，”隔开）" />
                            </div>
                        </div>
                        <div class="row" style="margin-top: 5px;">
                            <div class="col-sm-12" style="margin-top: 10px;">
                                <#button name="导出到邮箱" icon="fa-file-excel-o" clickFun="currencyPackSale.exportmailbox()" space="true"/>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>
</div>
<script src="${ctxPath}/static/modular/admin/currencyPackSale/currencyPackSale.js?111111111114"></script>
@}

前端js

/**
 * 图表页面初始化
 */
var currencyPackSale = {};

//查询快捷时间选择
$("#timeQuickSelect").change(function(){
    let timeStr = $("#timeQuickSelect").val();
    let datebeginTime = "";
    let dateendTime = "";
    if(timeStr=="thisweek"){
        datebeginTime = getWeekStartDate();
        dateendTime = getWeekEndDate();
    }else if(timeStr=="lastweek"){
        datebeginTime = getLastWeekStartDate();
        dateendTime = getLastWeekEndDate();
    }else if(timeStr=="thismonth"){
        datebeginTime = getMonthStartDate();
        dateendTime = getMonthEndDate();
    }else if(timeStr=="lastmonth"){
        datebeginTime = getLastMonthStartDate();
        dateendTime = getLastMonthEndDate();
    }
    $("#datebeginTime").val(datebeginTime);
    $("#dateendTime").val(dateendTime);
});

/**
 * 导出到邮箱
 */
currencyPackSale.exportmailbox = function () {
    let Param = {};
    if (Myhelp.isNull($("#datebeginTime").val())){
        Jay.info( "请选择开始时间!")
        return false;
    }
    if (Myhelp.isNull($("#dateendTime").val())){
        Jay.info( "请选择结束时间!")
        return false;
    }
    if (Myhelp.isNull($("#mailboxs").val())){
        Jay.info( "请填写发送邮箱!")
        return false;
    }
    Param['datebeginTime'] = $("#datebeginTime").val()
    Param['dateendTime'] = $("#dateendTime").val()
    Param['mailboxs'] = $("#mailboxs").val()
//此处是封装的ajax请求
    let ajax = new $axjson(Jay.ctxPath+"/currencyPackSale/exportmailbox","application/json;charset=UTF-8",function (data){
        if(data.key == 1){
            Jay.success("发送成功，稍后会发送至邮箱");
        }else {
            Jay.error(data.msg);
        }
    })
    ajax.set(Param)
    ajax.start()
}

前端效果

后端Controller

package com.hanlong.guns.modular.admin.controller;

import com.hanlong.guns.core.base.controller.BaseController;
import com.hanlong.guns.core.util.ToolUtil;
import com.hanlong.guns.modular.admin.service.ICurrencyPackSaleService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import java.util.HashMap;
import java.util.Map;

/**
 * @author af
 * @description 币包售出情况
 * @date 2023-11-27
 */
@Controller
@RequestMapping("/currencyPackSale")
public class currencyPackSaleController extends BaseController {

    private String PREFIX = "/admin/currencyPackSale/";

    /**
     * 跳转首页
     */
    @RequestMapping("")
    public String index() {
        return PREFIX + "currencyPackSale.html";
    }


    @Autowired
    private ICurrencyPackSaleService currencyPackSaleService;


    /**
     * 导出到邮箱
     */
    @RequestMapping(value = "/exportmailbox")
    @ResponseBody
    public Object exportmailbox(@RequestBody Map<String,Object> map) {
        Map<String, Object> returnmap = new HashMap<>();
        if (ToolUtil.isEmpty(map.get("datebeginTime")) || ToolUtil.isEmpty(map.get("dateendTime"))) {
            returnmap.put("key", -1);
            returnmap.put("msg", "发送失败！请选择发送时间");
        }
        if (ToolUtil.isEmpty(map.get("mailboxs"))) {
            returnmap.put("key", -1);
            returnmap.put("msg", "发送失败！发送邮箱错误");
        }
        new Thread(() -> {
            currencyPackSaleService.exportmailbox(map);
        }).start();
        returnmap.put("key", 1);
        return returnmap;
    }
}

service

package com.hanlong.guns.modular.admin.service;

import java.util.Map;

public interface ICurrencyPackSaleService {

    Boolean exportmailbox(Map<String, Object> map);

}

serviceImpl

package com.hanlong.guns.modular.admin.service.impl;

import com.hanlong.guns.common.constant.DatasourceEnum;
import com.hanlong.guns.core.db.Tbdelivery;
import com.hanlong.guns.core.mutidatasource.annotion.DataSource;
import com.hanlong.guns.core.util.DatetimeUtil;
import com.hanlong.guns.core.util.ToolUtil;
import com.hanlong.guns.modular.admin.model.util.MailVo;
import com.hanlong.guns.modular.admin.service.EmailService;
import com.hanlong.guns.modular.admin.service.ICurrencyPackSaleService;
import com.hanlong.guns.modular.admin.service.ITbconsumerecordService;
import com.hanlong.guns.modular.admin.service.ITbpayorderService;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.mongodb.core.MongoTemplate;
import org.springframework.data.mongodb.core.query.Criteria;
import org.springframework.data.mongodb.core.query.Query;
import org.springframework.stereotype.Service;

import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

@Service
public class CurrencyPackSaleServiceImpl implements ICurrencyPackSaleService {

    @Autowired
    private ITbconsumerecordService tbconsumerecordService;

    @Autowired
    private ITbpayorderService tbpayorderService;

    @Autowired
    private MongoTemplate mongoTemplate;

    @Autowired
    private TbdeliveryServiceImpl tbdeliveryService;

    @Autowired
    private EmailService emailService;

    /**
     * 计算出数据发送至邮箱mailboxs
     */
    @Override
    @DataSource(name = DatasourceEnum.DATA_SOURCE_BIZ)
    public Boolean exportmailbox(Map<String, Object> map ) {
        //发送的邮箱
        String mailboxs = map.get("mailboxs").toString();
        String datebeginTime = map.get("datebeginTime").toString();
        String dateendTime = map.get("dateendTime").toString();

        //装载数据
        /** 准备时间范围 */
        List<String> dayBetween = DatetimeUtil.getDayBetween(datebeginTime, dateendTime);

        //TODO 这是mongodb查询浏览人数的数据
        List<Map<String, Object>> browseData = new ArrayList<>();
        for (String today : dayBetween) {
            Map<String, Object> browsemap = new HashMap<>();
            //mongo数据
            Criteria criteria = new Criteria();
            criteria.and("f3").is("scanIndex");
            criteria.and("f2").is(today);
//            criteria.and("f2").gte(today + " 00:00:00").lte(today + " 23:59:59");
            Query query = new Query(criteria);
            List<Map> analyzes = mongoTemplate.find(query, Map.class,"tbanalyze");
            if (ToolUtil.isEmpty(analyzes)) {
                browsemap.put("profit", 0);
                browsemap.put("rent", 0);
                browseData.add(browsemap);
                continue;
            }
            List<String> positionIds = analyzes.stream().map(a -> a.get("f0").toString()).collect(Collectors.toList());
            List<Tbdelivery> deliveryList = tbdeliveryService.selectBizNameByBizCode((ArrayList<String>) positionIds);
            List<Map> profitList = new ArrayList<>();
            List<Map> rentList = new ArrayList<>();
            if (ToolUtil.isEmpty(deliveryList)) {
                browsemap.put("profit", 0);
                browsemap.put("rent", 0);
                browseData.add(browsemap);
                continue;
            }
            for (Tbdelivery d : deliveryList) {
                analyzes.forEach(a -> {
                    if (a.get("f0").toString().equals(d.getBizCode()) && ToolUtil.isNotEmpty(d.getBizContractModel()) && "分润".equals(d.getBizContractModel())) {
                        profitList.add(a);
                    }else if (a.get("f0").toString().equals(d.getBizCode()) && ToolUtil.isNotEmpty(d.getBizContractModel()) && "租金".equals(d.getBizContractModel())){
                        rentList.add(a);
                    }
                });
            }
            Integer profit = 0;
            for (Map map1 : profitList) {
                profit += Integer.valueOf(map1.get("f1").toString());
            }
            Integer rent = 0;
            for (Map map1 : rentList) {
                rent += Integer.valueOf(map1.get("f1").toString());
            }
            browsemap.put("profit",profit);
            browsemap.put("rent", rent);
            browseData.add(browsemap);
        }
        Map<String, Object> adddata = new HashMap<>();
        adddata.put("date", dayBetween.get(0) + "-" + dayBetween.get(dayBetween.size() - 1));

        //TODO 这是分润的数据
        String[][] arrprofit = new String[dayBetween.size() + 1][17];//装载分润数据的二维数组
        int j = 0;//用于天数对应上来自mongodb的浏览数据
        double profit800 = 0; double profit990 = 0; double profit1000 = 0; double profit1280 = 0; double profit1800 = 0; double profittotal = 0; double profitbrowse = 0;//统计分润数据总结那一列
        for (int i = 0; i < dayBetween.size(); i++) {
            //查一天的分润数据
            List<Map<String, Object>> onebuypackagedata = tbconsumerecordService.selectBuyPackageData(dayBetween.get(i),"分润");
            //装载时间
            Date parse = DatetimeUtil.parse(dayBetween.get(i));
            String now = new SimpleDateFormat("yyyy年MM月dd日").format(parse);//类型转换
            arrprofit[i][0] =now;//第一列装载时间
            int add800 = 0; int add990 = 0; int add1000 = 0; int add1280 = 0; int add1800 = 0;//装载一天的不同类型的售出数量
            for (Map<String, Object> one : onebuypackagedata) {//收集数据
                if (Integer.valueOf(one.get("costCurrency").toString())==800) add800 +=Integer.valueOf(one.get("countNumber").toString());
                if (Integer.valueOf(one.get("costCurrency").toString())==990) add990 +=Integer.valueOf(one.get("countNumber").toString());
                if (Integer.valueOf(one.get("costCurrency").toString())==1000)add1000+=Integer.valueOf(one.get("countNumber").toString());
                if (Integer.valueOf(one.get("costCurrency").toString())==1280)add1280+=Integer.valueOf(one.get("countNumber").toString());
                if (Integer.valueOf(one.get("costCurrency").toString())==1800)add1800+=Integer.valueOf(one.get("countNumber").toString());
            }
            arrprofit[i][2]=String.valueOf(add800); profit800 += add800;//第三列800，并累加上800数据
            arrprofit[i][4]=String.valueOf(add990); profit990 += add990;//第五列990
            arrprofit[i][6]=String.valueOf(add1000); profit1000 += add1000;//第七列1000
            arrprofit[i][8]=String.valueOf(add1280); profit1280 += add1280;//第九列1280
            arrprofit[i][10]=String.valueOf(add1800);profit1800 += add1800;//第十一列1800

            double total = add800 + add990 + add1000 + add1280 + add1800;
            arrprofit[i][1]=String.valueOf((int) total);  profittotal += total;//第二列总笔数

            DecimalFormat decimalFormat = new DecimalFormat("0.00%");
            arrprofit[i][3]=decimalFormat.format(total != 0 ? add800 / total : 0.0);//第四列
            arrprofit[i][5]=decimalFormat.format(total != 0 ? add990 / total :  0.0);//第六列
            arrprofit[i][7]=decimalFormat.format(total != 0 ? add1000 / total :0.0);
            arrprofit[i][9]=decimalFormat.format(total != 0 ? add1280 / total : 0.0);
            arrprofit[i][11] = decimalFormat.format(total != 0 ? add1800 / total : 0.0);

            double revenue = add800 * 8 + add990 * 9.9 + add1000 * 10 + add1280 * 12.8 + add1800 * 18;
            arrprofit[i][12] = String.valueOf(String.format("%.2f", revenue));//营收
            arrprofit[i][13] = String.valueOf(String.format("%.2f", revenue != 0 && total != 0 ? revenue / total : 0)); //单价
            int browse = ToolUtil.isNotEmpty(browseData) ? ToolUtil.isNotEmpty(browseData.get(j).get("profit")) ? Integer.valueOf(browseData.get(j).get("profit").toString()) : 0 : 0;
            profitbrowse += browse * 1.0;
            j++;
            arrprofit[i][14] = String.valueOf(browse);//页面浏览人数
            double payment = total != 0 && browse != 0 ? total / browse : 0.0;
            arrprofit[i][15]=String.valueOf(decimalFormat.format(payment)); //付款意向
            arrprofit[i][16] = String.format("%.2f", browse != 0 ? revenue / browse * 10000 : 0); //万人收益
        }
        /**  汇总分润列的数据*/
        arrprofit[dayBetween.size()][0] = "汇总";
        arrprofit[dayBetween.size()][1] = String.valueOf(profittotal);

        arrprofit[dayBetween.size()][2]=String.valueOf(profit800);
        arrprofit[dayBetween.size()][4]=String.valueOf(profit990);
        arrprofit[dayBetween.size()][6]=String.valueOf(profit1000);
        arrprofit[dayBetween.size()][8]=String.valueOf(profit1280);
        arrprofit[dayBetween.size()][10]=String.valueOf(profit1800);

        DecimalFormat decimalFormat1 = new DecimalFormat("0.00%");
        arrprofit[dayBetween.size()][3]=decimalFormat1.format(profittotal != 0 ?profit800 / profittotal : 0);
        arrprofit[dayBetween.size()][5]=decimalFormat1.format(profittotal != 0 ?profit990 / profittotal :  0);
        arrprofit[dayBetween.size()][7]=decimalFormat1.format(profittotal != 0 ? profit1000 / profittotal :0);
        arrprofit[dayBetween.size()][9]=decimalFormat1.format(profittotal != 0 ? profit1280 / profittotal : 0);
        arrprofit[dayBetween.size()][11]=decimalFormat1.format(profittotal != 0 ? profit1800 / profittotal : 0);

        double profitrevenue = profit800 * 8 + profit990 * 9.9 + profit1000 * 10 + profit1280 * 12.8 + profit1800 * 18;
        arrprofit[dayBetween.size()][12] = String.valueOf(String.format("%.2f", profitrevenue));
        arrprofit[dayBetween.size()][13] = String.valueOf(String.format("%.2f", profittotal != 0 ? profitrevenue / profittotal : 0));
        arrprofit[dayBetween.size()][14] = String.valueOf(profitbrowse);
        arrprofit[dayBetween.size()][15] = decimalFormat1.format(profitbrowse != 0 ? profittotal / profitbrowse : 0);
        arrprofit[dayBetween.size()][16] = String.format("%.2f", profitbrowse != 0 ? profitrevenue / profitbrowse * 10000 : 0);

        //TODO 这是租金的数据
        String[][] arrRent = new String[dayBetween.size() + 1][17];
        double rent800 = 0; double rent990 = 0; double rent1000 = 0; double rent1280 = 0; double rent1800 = 0; double renttotal = 0; double rentbrowse = 0;
        int n = 0;
        for (int i = 0; i < dayBetween.size(); i++) {
            //装载时间
            Date parse = DatetimeUtil.parse(dayBetween.get(i));
            String now = new SimpleDateFormat("yyyy年MM月dd日").format(parse);
            arrRent[i][0] = now;
            //查询租金的990、1000的数据，来自tbpayorder表
            int add990 = 0; int add1000 = 0;
            List<Map<String, Object>> divideonepayorderdata = tbpayorderService.selectgiftdata(dayBetween.get(i));
            for (Map<String, Object> one : divideonepayorderdata) {
                if (Integer.valueOf(one.get("costCurrency").toString())==990) add990 +=Integer.valueOf(one.get("countNumber").toString());
                if (Integer.valueOf(one.get("costCurrency").toString())==1000)add1000+=Integer.valueOf(one.get("countNumber").toString());
            }
            arrRent[i][4]=String.valueOf(add990); rent990 += add990;
            arrRent[i][6]=String.valueOf(add1000); rent1000 += add1000;

            //获取MySQL数据(租金)
            List<Map<String, Object>> divideonebuypackagedata = tbconsumerecordService.selectBuyPackageData(dayBetween.get(i), "租金");
            //装载数据
            int add800 = 0;  int add1280 = 0; int add1800 = 0;
            for (Map<String, Object> one : divideonebuypackagedata) {
                if (Integer.valueOf(one.get("costCurrency").toString())==800) add800 +=Integer.valueOf(one.get("countNumber").toString());
                if (Integer.valueOf(one.get("costCurrency").toString())==1280)add1280+=Integer.valueOf(one.get("countNumber").toString());
                if (Integer.valueOf(one.get("costCurrency").toString())==1800)add1800+=Integer.valueOf(one.get("countNumber").toString());
            }
           arrRent[i][2]=String.valueOf(add800); rent800 += add800;
           arrRent[i][8]=String.valueOf(add1280); rent1280 += add1280;
           arrRent[i][10]=String.valueOf(add1800);rent1800 += add1800;

            double total = add800 + add990 + add1000 + add1280 + add1800;
            arrRent[i][1]=String.valueOf((int) total);  renttotal += total;//总笔数

            DecimalFormat decimalFormat = new DecimalFormat("0.00%");
            arrRent[i][3]=decimalFormat.format(total != 0 ? add800 / total : 0.0);
            arrRent[i][5]=decimalFormat.format(total != 0 ? add990 / total :  0.0);
            arrRent[i][7]=decimalFormat.format(total != 0 ? add1000 / total :0.0);
            arrRent[i][9]=decimalFormat.format(total != 0 ? add1280 / total : 0.0);
            arrRent[i][11] = decimalFormat.format(total != 0 ? add1800 / total : 0.0);

            double revenue = add800 * 8 + add990 * 9.9 + add1000 * 10 + add1280 * 12.8 + add1800 * 18;
            arrRent[i][12] = String.valueOf(String.format("%.2f", revenue));//营收
            arrRent[i][13] = String.valueOf(String.format("%.2f", revenue != 0 && total != 0 ? revenue / total : 0)); //单价
            int browse = ToolUtil.isNotEmpty(browseData) ? ToolUtil.isNotEmpty(browseData.get(n).get("rent")) ? Integer.valueOf(browseData.get(n).get("rent").toString()) : 0 : 0;
            rentbrowse += browse * 1.0;
            n++;
            arrRent[i][14] = String.valueOf(browse);//页面浏览人数
            double payment = total != 0 && browse != 0 ? total / browse : 0.0;
            arrRent[i][15]=String.valueOf(decimalFormat.format(payment)); //付款意向
            arrRent[i][16] = String.format("%.2f", browse != 0 ? revenue / browse * 10000 : 0); //万人收益
        }
        /**  汇总租金列的数据*/
        arrRent[dayBetween.size()][0] = "汇总";
        arrRent[dayBetween.size()][1] = String.valueOf(renttotal);

        arrRent[dayBetween.size()][2]=String.valueOf(rent800);
        arrRent[dayBetween.size()][4]=String.valueOf(rent990);
        arrRent[dayBetween.size()][6]=String.valueOf(rent1000);
        arrRent[dayBetween.size()][8]=String.valueOf(rent1280);
        arrRent[dayBetween.size()][10]=String.valueOf(rent1800);

        DecimalFormat decimalFormat2 = new DecimalFormat("0.00%");
        arrRent[dayBetween.size()][3] = decimalFormat2.format( renttotal != 0 ? rent800 / renttotal : 0);
        arrRent[dayBetween.size()][5]=decimalFormat2.format(renttotal != 0 ? rent990 / renttotal :  0);
        arrRent[dayBetween.size()][7]=decimalFormat2.format(renttotal != 0 ? rent1000 / renttotal :0);
        arrRent[dayBetween.size()][9]=decimalFormat2.format(renttotal != 0 ? rent1280 / renttotal : 0);
        arrRent[dayBetween.size()][11]=decimalFormat2.format(renttotal != 0 ?rent1800 / renttotal : 0);

        double rentrevenue = rent800 * 8 + rent990 * 9.9 + rent1000 * 10 + rent1280 * 12.8 +rent1800 * 18;
        arrRent[dayBetween.size()][12] = String.valueOf(String.format("%.2f", rentrevenue));
        arrRent[dayBetween.size()][13] = String.valueOf(String.format("%.2f", renttotal != 0 ? rentrevenue / renttotal : 0));
        arrRent[dayBetween.size()][14] = String.valueOf(rentbrowse);
        arrRent[dayBetween.size()][15] = decimalFormat2.format(rentbrowse != 0 ? renttotal / rentbrowse : 0);
        arrRent[dayBetween.size()][16] = String.format("%.2f", rentbrowse != 0 ? rentrevenue / rentbrowse * 10000 : 0);

        //总结表单中的那一列数据
        String[] add = new String[17];
        add[0] = dayBetween.get(0) + "-" + dayBetween.get(dayBetween.size() - 1);
        double addtotal = profittotal + renttotal;
        add[1] = String.valueOf(profittotal + renttotal);

        add[2] = String.valueOf(profit800 + rent800);
        add[4] = String.valueOf(profit990 + rent990);
        add[6] = String.valueOf(profit1000 + rent1000);
        add[8] = String.valueOf(profit1280 + rent1280);
        add[10] = String.valueOf(profit1800 + rent1800);

        add[3] = decimalFormat2.format(addtotal != 0 ? (profit800 + rent800) / addtotal : 0);
        add[5] = decimalFormat2.format(addtotal != 0 ? (profit990 + rent990) / addtotal : 0);
        add[7] = decimalFormat2.format(addtotal != 0 ? (profit1000 + rent1000) / addtotal : 0);
        add[9] = decimalFormat2.format(addtotal != 0 ? (profit1280 + rent1280) / addtotal : 0);
        add[11] =decimalFormat2.format(addtotal != 0 ? (profit1800 + rent1800) / addtotal : 0);

        double addrentrevenue = (profit800 + rent800) * 8 + (profit990 + rent990) * 9.9 + (profit1000 + rent1000) * 10 + (profit1280 + rent1280) * 12.8 +(profit1800 + rent1800) * 18;
        add[12] = String.valueOf(String.format("%.2f", addrentrevenue));
        add[13] = String.valueOf(String.format("%.2f", addtotal !=    0 ? addrentrevenue / addtotal : 0));
        add[14] = String.valueOf(profitbrowse + rentbrowse);
        add[15] = decimalFormat2.format((profitbrowse + rentbrowse) != 0 ? addtotal / (profitbrowse + rentbrowse) : 0);
        add[16] = String.format("%.2f", (profitbrowse + rentbrowse) != 0 ? addrentrevenue / (profitbrowse + rentbrowse) * 10000 : 0);

        Workbook workBook5 = emailService.getWorkBook5(dayBetween,arrprofit, arrRent, add);
        if (workBook5!=null){
            MailVo mailVo = new MailVo();
            mailVo.setTableName("币包售出情况");
            mailVo.setAddress(mailboxs);
            String[] header = null;
            mailVo.setHeader(header);
            List<Map<String, Object>> arrayList = new ArrayList<>();
            mailVo.setData(arrayList);
            mailVo.setHtmlFlag(false);
            mailVo.setContent("币包售出情况");
            mailVo.setExcelName("币包售出情况"+ ".xls");
            mailVo.setTitle("币包售出情况");
            mailVo.setWorkbook(workBook5);
            emailService.sendMailByExcelStream(mailVo.getTitle(),mailVo.getContent(),mailVo.getHtmlFlag(),
                    mailVo.getAddress(), mailVo.getCsr(),mailVo.getExcelName(), mailVo.getWorkbook());
        }
        return null;
    }
}

上文的访问数据库

<!--    List<Map<String, Object>> selectgiftdata(@Param("datebeginTime")String datebeginTime,@Param("dateendTime") String dateendTime);-->
    <select id="selectgiftdata" resultType="java.util.Map">
        SELECT
            o.pay_price AS costCurrency,
            COUNT( 1 ) AS countNumber
        FROM
        tbpay_order o
        WHERE
            o.order_status >= 200
        AND o.pay_time >= #{datebeginTime}
        AND o.pay_time &lt;= #{dateendTime}
        AND o.pay_price IN ( 1000, 990 )
        GROUP BY
            o.pay_price
    </select>

表格设计接口


    /**
     * 币包售出情况专用
     * @param arrprofit 分润数据
     * @param arrRent 租金数据
     * @return
     */
    Workbook getWorkBook5(List<String> dayBetween,String[][] arrprofit, String[][] arrRent, String[] add);

表格实现


    /**
     * 币包售出情况
     * @param arrprofit 分润数据
     * @param arrRent 租金数据
     * @return
     */
    @Override
    public Workbook getWorkBook5(List<String> dayBetween,String[][] arrprofit, String[][] arrRent, String[] add) {
        HSSFWorkbook wb = null;
        try {
            // 第一步，创建一个webbook，对应一个Excel文件
            wb = new HSSFWorkbook();
            // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
            HSSFSheet sheet = wb.createSheet("币包售出情况");
//            sheet.autoSizeColumn(1, true);
            HSSFCellStyle style = wb.createCellStyle();
            // 创建一个居中格式
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//左右居中
            style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
            style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
            style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
            style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
            style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框

            HSSFFont font = wb.createFont();
            font.setFontHeightInPoints((short) 12);
            font.setColor(HSSFColor.BLACK.index);
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//加粗
            font.setFontName("等线");
            style.setFont(font);
            //基本的设置：边框、居中、字号、加粗、行宽、列高
            //一共有5种不同的样式：正文、数字、橙底、红字

            //红字
            HSSFCellStyle style1 = wb.createCellStyle();
            style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);//左右居中
            style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
            style1.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
            style1.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
            style1.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
            style1.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框

            HSSFFont font1 = wb.createFont();
            font1.setFontHeightInPoints((short) 12);
            font1.setColor(HSSFColor.RED.index);
            font1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//加粗
            font1.setFontName("等线");
            style1.setFont(font1);

            //橙底
            HSSFCellStyle style2 = wb.createCellStyle();
            style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);//左右居中
            style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
            style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
            style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
            style2.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
            style2.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
            style2.setFillForegroundColor(IndexedColors.LIGHT_ORANGE.getIndex());// 设置背景色

            HSSFFont font2 = wb.createFont();
            font2.setFontHeightInPoints((short) 12);
            font2.setColor(HSSFColor.BLACK.index);
            font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//加粗
            font2.setFontName("等线");
            style2.setFont(font2);

            int setrow = 0;
            HSSFCell cell = null;
            /** 每一个小表的表头 */
            sheet.addMergedRegion(new CellRangeAddress(setrow, setrow, 0, 11));
            sheet.addMergedRegion(new CellRangeAddress(setrow, setrow + 1, 12, 12));
            sheet.addMergedRegion(new CellRangeAddress(setrow, setrow + 1, 13, 13));
            sheet.addMergedRegion(new CellRangeAddress(setrow, setrow + 1, 14, 14));
            sheet.addMergedRegion(new CellRangeAddress(setrow, setrow + 1, 15, 15));
            sheet.addMergedRegion(new CellRangeAddress(setrow, setrow + 1, 16, 16));
            sheet.addMergedRegion(new CellRangeAddress(setrow + 1, setrow + 1, 2, 3));
            sheet.addMergedRegion(new CellRangeAddress(setrow + 1, setrow + 1, 4, 5));
            sheet.addMergedRegion(new CellRangeAddress(setrow + 1, setrow + 1, 6, 7));
            sheet.addMergedRegion(new CellRangeAddress(setrow + 1, setrow + 1, 8, 9));
            sheet.addMergedRegion(new CellRangeAddress(setrow + 1, setrow + 1, 10, 11));
            style.setWrapText(true);

            HSSFRow row_title = sheet.createRow(setrow++);
            cell = row_title.createCell(0);
            cell.setCellValue("币包售出情况统计" + dayBetween.get(0) + "-" + dayBetween.get(dayBetween.size() - 1) + "总计租金");
            cell.setCellStyle(style);
            for (int v = 1; v < 12; v++) {
                cell = row_title.createCell(v);
                cell.setCellStyle(style);
            }
            String[] hard = {"营收", "单价", "页面浏览人数", "付款意向", "每万人收益"};
            for (int i = 12; i < 17; i++) {
                cell = row_title.createCell(i);
                cell.setCellValue(hard[i - 12]);
                cell.setCellStyle(style);
            }


            String[] bi = {"日期","总笔数","8元5币","","9.9元10币","","10元10币","","12.8元16币","","18元28币",""};
            HSSFRow row_title2 = sheet.createRow(setrow++);
            for (int i = 0; i < 12; i++) {
                cell = row_title2.createCell(i);
                if (i > 1 && i % 2 == 0) {
                    cell.setCellValue(bi[i]);
                }
                if (i < 2) {
                    cell.setCellValue(bi[i]);
                }
                cell.setCellStyle(style);
            }
            //数据展示
            for (int i = 0; i < arrprofit.length; i++) {
                HSSFRow row_content = sheet.createRow(setrow++);
                for (int j = 0; j < arrprofit[i].length; j++) {
                    HSSFCell cell1 = row_content.createCell(j);
                    if (j == 13) {
                        cell.setCellStyle(style1);
                    } else if (j == 16) {
                        cell.setCellStyle(style2);
                    } else {
                        cell.setCellStyle(style);
                    }
                    cell1.setCellValue(arrprofit[i][j]);
                }
            }

            sheet.createRow(setrow++);
            sheet.addMergedRegion(new CellRangeAddress(setrow, setrow, 0, 11));
            sheet.addMergedRegion(new CellRangeAddress(setrow, setrow + 1, 12, 12));
            sheet.addMergedRegion(new CellRangeAddress(setrow, setrow + 1, 13, 13));
            sheet.addMergedRegion(new CellRangeAddress(setrow, setrow + 1, 14, 14));
            sheet.addMergedRegion(new CellRangeAddress(setrow, setrow + 1, 15, 15));
            sheet.addMergedRegion(new CellRangeAddress(setrow, setrow + 1, 16, 16));
            sheet.addMergedRegion(new CellRangeAddress(setrow + 1, setrow + 1, 2, 3));
            sheet.addMergedRegion(new CellRangeAddress(setrow + 1, setrow + 1, 4, 5));
            sheet.addMergedRegion(new CellRangeAddress(setrow + 1, setrow + 1, 6, 7));
            sheet.addMergedRegion(new CellRangeAddress(setrow + 1, setrow + 1, 8, 9));
            sheet.addMergedRegion(new CellRangeAddress(setrow + 1, setrow + 1, 10, 11));
            style.setWrapText(true);

            HSSFRow row_title5 = sheet.createRow(setrow++);
            cell = row_title5.createCell(0);
            cell.setCellValue("币包售出情况统计" + dayBetween.get(0) + "-" + dayBetween.get(dayBetween.size() - 1) + "总计分润");
            cell.setCellStyle(style);
            for (int v = 1; v < 11; v++) {
                cell = row_title5.createCell(v);
                cell.setCellStyle(style);
            }
            for (int i = 12; i < 17; i++) {
                cell = row_title5.createCell(i);
                cell.setCellValue(hard[i - 12]);
                cell.setCellStyle(style);
            }

            HSSFRow row_title6 = sheet.createRow(setrow++);
            for (int i = 0; i < 12; i++) {
                cell = row_title6.createCell(i);
                if (i > 1 && i % 2 == 0) {
                    cell.setCellValue(bi[i]);
                }
                if (i < 2) {
                    cell.setCellValue(bi[i]);
                }
                cell.setCellStyle(style);
            }

            for (int i = 0; i < arrRent.length; i++) {
                HSSFRow row_content = sheet.createRow(setrow++);
                for (int j = 0; j < arrRent[i].length; j++) {
                    HSSFCell cell1 = row_content.createCell(j);
                    if (j == 13) {
                        cell.setCellStyle(style1);
                    } else if (j == 16) {
                        cell.setCellStyle(style2);
                    } else {
                        cell.setCellStyle(style);
                    }
                    cell1.setCellValue(arrRent[i][j]);
                }
            }
            sheet.createRow(setrow++);
            sheet.addMergedRegion(new CellRangeAddress(setrow, setrow, 0, 11));
            sheet.addMergedRegion(new CellRangeAddress(setrow, setrow + 1, 12, 12));
            sheet.addMergedRegion(new CellRangeAddress(setrow, setrow + 1, 13, 13));
            sheet.addMergedRegion(new CellRangeAddress(setrow, setrow + 1, 14, 14));
            sheet.addMergedRegion(new CellRangeAddress(setrow, setrow + 1, 15, 15));
            sheet.addMergedRegion(new CellRangeAddress(setrow, setrow + 1, 16, 16));
            sheet.addMergedRegion(new CellRangeAddress(setrow + 1, setrow + 1, 2, 3));
            sheet.addMergedRegion(new CellRangeAddress(setrow + 1, setrow + 1, 4, 5));
            sheet.addMergedRegion(new CellRangeAddress(setrow + 1, setrow + 1, 6, 7));
            sheet.addMergedRegion(new CellRangeAddress(setrow + 1, setrow + 1, 8, 9));
            sheet.addMergedRegion(new CellRangeAddress(setrow + 1, setrow + 1, 10, 11));
            style.setWrapText(true);

            HSSFRow row_title3 = sheet.createRow(setrow++);
            cell = row_title3.createCell(0);
            cell.setCellValue("币包售出情况统计" + dayBetween.get(0) + "-" + dayBetween.get(dayBetween.size() - 1) + "总计分润+租金");
            cell.setCellStyle(style);
            for (int v = 1; v < 11; v++) {
                cell = row_title3.createCell(v);
                cell.setCellStyle(style);
            }
            for (int i = 12; i < 17; i++) {
                cell = row_title3.createCell(i);
                cell.setCellValue(hard[i - 12]);
                cell.setCellStyle(style);
            }

            HSSFRow row_title4 = sheet.createRow(setrow++);
            for (int i = 0; i < 12; i++) {
                cell = row_title4.createCell(i);
                if (i > 1 && i % 2 == 0) {
                    cell.setCellValue(bi[i]);
                }
                if (i < 2) {
                    cell.setCellValue(bi[i]);
                }
                cell.setCellStyle(style);
            }

            HSSFRow row_content = sheet.createRow(setrow);
            for (int i = 0; i < add.length; i++) {
                HSSFCell cell1 = row_content.createCell(i);
                cell.setCellStyle(i == 13 ? style1 : style);
                cell1.setCellValue(add[i]);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return wb;
    }

定时任务



    /**
     * 每天7点定时发布昨天币包售出数据
     */
    @Scheduled(cron = "0 0 7 * * ? ")
    public void exportmailbox(){
        Map<String, Object> map = new HashMap<>();
        String time = DatetimeUtil.formatDate(DateUtils.addDays(new Date(), -1));
        map.put("datebeginTime", time);
        map.put("dateendTime", time);
        map.put("mailboxs", "XXXXXXXXX@qq.com");
        new Thread(() -> {
            currencyPackSaleService.exportmailbox(map);
        }).start();

    }

结果