企微群机器人推送文本+Excel

例如，企微群定时推送：内容+表格

群机器人：学校基本信息：

[一个记录学校基本信息的excel表]

 excel表简单类似：

准备肯定先企微群创建一个机器人，获取到群webhook备用;

简易步骤比较简单，先分别获取老师、学生、职工的list信息->再把列表转化为excel表->推送消息

 一、推送工具类;（isNotBank之类的判空方法，可以自行重写）

import lombok.extern.slf4j.Slf4j;
import okhttp3.*;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import java.io.File;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.concurrent.TimeUnit;

/**
 * @author yrl
 * @date 2023/7/5 15:29
 */
@Slf4j
public class WxPushUtil {

    /**
     * 企业微信机器人api地址与key；webhook 截取
     */
    private final static String WECHAT_API_URL = "https://qyapi.weixin.qq.com/cgi-bin/webhook/";
    private final static String WECHAT_API_KEY = "xxxxx-xxx-xxx-xxxxxxxxxxxxxxxxx";


    /**
     * 学生列表数据转化为 excel
     *
     * @param wb       Workbook
     * @param student  学生
     * @param dest     首栏提示 默认可空
     * @param pageName 底部分页表名称
     */
    public static boolean studentFileBuild(HSSFWorkbook wb, List<student> student, String dest, String pageName) {
        //
        if (student == null || student.size() < 0) {
            return false;
        }
        //表头字段，与对象一致
        String[] titleArray = new String[]{"学生姓名", "年龄", "性别", "班级"};
        String[] fieldArray = new String[]{"studentName", "age", "sex", "class"};
        List<Map<String, Object>> list = new ArrayList<>();

        for (Student s : student) {
            //转成map
            Map<String, Object> map = BeanUtils.beanToMap(s);
            list.add(map);
        }

        if (list.size() > 0) {
            //数据组合 到HSSFWorkbook
            ExcelUtil.buildExcel(list, wb, pageName, titleArray, fieldArray, dest);
            return true;
        }
        return false;
    }

    /**
     * 老师 数据转化为excel构建
     *
     * @param wb       Workbook
     * @param teacher  老师
     * @param dest     首栏提示 默认可空
     * @param pageName 底部分页表名称
     */
    public static boolean teacherFileBuild(HSSFWorkbook wb, List<Teacher> teacher, String dest, String pageName) {
        //
        if (teacher == null || teacher.size() < 0) {
            return false;
        }
        //表头字段，与对象一致
        String[] titleArray = new String[]{"工号", "姓名", "性别", "职位"};
        String[] fieldArray = new String[]{"number", "name", "sex", "lev"};
        List<Map<String, Object>> list = new ArrayList<>();

        for (Teacher s : teacher) {
            //转成map
            Map<String, Object> map = BeanUtils.beanToMap(s);
            list.add(map);
        }

        if (list.size() > 0) {
            //数据组合 到HSSFWorkbook
            ExcelUtil.buildExcel(list, wb, pageName, titleArray, fieldArray, dest);
            return true;
        }
        return false;
    }

    /**
     * 发送文件消息，需要先将文件上传到企业微信临时素材，再根据获取的media_id调用群机器人
     *
     * @param file      需要发送的文件
     * @param text      发送附件的同时需要发送文字内容
     * @param phoneList 需要@的群成员手机号
     */
    public static void sendFileMsg(File file, String text, List<String> phoneList) {
        // 构造RequestBody对象，用来携带要提交的数据；需要指定MediaType，用于描述请求/响应 body 的内容类型
        MediaType contentType = MediaType.parse("application/form-data; boundary");
        RequestBody body = RequestBody.create(contentType, file);
        RequestBody requestBody = new MultipartBody.Builder()
                .setType(MultipartBody.FORM)
                .addFormDataPart("file", file.getName(), body)
                .build();

        try {
            //获取推送的群id，如果多个，推送多个
            // 上传到临时素材
            String mediaUrl = WECHAT_API_URL + "upload_media?type=file&key=" + WECHAT_API_KEY;
            log.debug("将文件{}上传到临时素材：{}", file.getName(), mediaUrl);
            String respMsg = okHttp(requestBody, mediaUrl);
            log.debug("将文件{}上传到临时素材：{}, {}", file.getName(), mediaUrl, respMsg);

            // 获取临时素材id
            JSONObject result = JSONUtil.parseObj(respMsg);
            String mediaId = result.getStr("media_id");

            if (U.isNotBlank(text)) {
                JSONObject textJson = new JSONObject();
                textJson.set("content", text);
                JSONObject reqBody = new JSONObject();
                //先推文本
                reqBody.set("msgtype", "text");
                reqBody.set("text", textJson);
                //"mentioned_mobile_list":["13800001111","@all"]
                if (A.isNotEmpty(phoneList)) {
                    System.out.println("推送的手机号：" + phoneList);
                    reqBody.set("mentioned_mobile_list", phoneList);
                }

                // 调用群机器人发送消息
                callWeChatBot(reqBody.toString(), WECHAT_API_KEY);
            }

            JSONObject fileJson = new JSONObject();
            fileJson.set("media_id", mediaId);
            JSONObject reqBody = new JSONObject();
            //再推文件
            reqBody.set("msgtype", "file");
            reqBody.set("file", fileJson);
            reqBody.set("safe", 0);

            // 再调用群机器人发送文件消息
            callWeChatBot(reqBody.toString(), WECHAT_API_KEY);
        } catch (Exception e) {
            log.error("文件推送异常:{}", file.getName(), e);
        }
    }


    /**
     * @param body 携带需要提交的数据（后续迁移）
     * @param url  请求地址
     * @throws Exception 异常
     */
    private static String okHttp(RequestBody body, String url) throws Exception {
        // 构造和配置OkHttpClient
        OkHttpClient client = new OkHttpClient.Builder()
                // 设置连接超时时间
                .connectTimeout(10, TimeUnit.SECONDS)
                // 设置读取超时时间
                .readTimeout(20, TimeUnit.SECONDS)
                .build();

        // 构造Request对象
        Request request = new Request.Builder()
                .url(url)
                .post(body)
                // 响应消息不缓存
                .addHeader("cache-control", "no-cache")
                .build();

        // 构建Call对象，通过Call对象的execute()方法提交异步请求
        Response response = client.newCall(request).execute();

        // 请求结果处理
        assert response.body() != null;
        byte[] datas = response.body().bytes();
        return new String(datas);
    }

    /**
     * 调用群机器人
     *
     * @param reqBody 接口请求参数
     * @throws Exception 异常
     */
    private static void callWeChatBot(String reqBody, String key) throws Exception {
        log.debug("调用群机器人：{}", reqBody);
        // 构造RequestBody对象，用来携带要提交的数据；需要指定MediaType，用于描述请求/响应 body 的内容类型
        okhttp3.MediaType contentType = okhttp3.MediaType.parse("application/json; charset=utf-8");
        RequestBody body = RequestBody.create(contentType, reqBody);

        String botUrl = WECHAT_API_URL + "send?key=" + key;
        // 调用群机器人
        String respMsg = okHttp(body, botUrl);
        JSONObject result = JSONUtil.parseObj(respMsg);

        if ("0".equals(result.getStr("errcode"))) {
            log.debug("调用群机器人发送消息成功, reqBody: {}, respMsg : {}", reqBody, respMsg);
        } else {
            log.error("调用群机器人发送消息失败, reqBody: {}, respMsg : {}", reqBody, respMsg);
        }
    }
}

二、EXCEL工具类（list转化等）有些方法可能用不到

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFCell;
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.multipart.MultipartFile;

import java.io.IOException;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;

/**
 * Excel工具类
 */
public class ExcelUtil<T> {

    public Class<T> clazz;

    public ExcelUtil(Class<T> clazz) {
        this.clazz = clazz;
    }


    /**
     * 读取Excel内容转为List
     */
    public List<T> importExcel(MultipartFile file) throws Exception {
        return importExcel(StringUtils.EMPTY, file);
    }


    /**
     * 读取Excel内容转为List
     */
    public List<T> importExcel(String sheetName, MultipartFile file) throws Exception {
        U.assertException(U.isBlank(file), "上传的文件为空");
        //获取文件名
        String fileName = file.getOriginalFilename();
        U.assertException(!(fileName.endsWith(".xls") || fileName.endsWith(".xlsx")), "请选择Excel文件上传");
        //
        List<T> list = new ArrayList<>();
        Workbook workbook = WorkbookFactory.create(file.getInputStream());
        Sheet sheet;
        if (StringUtils.isNotEmpty(sheetName)) {
            // 如果指定sheet名,则取指定sheet中的内容.
            sheet = workbook.getSheet(sheetName);
        } else {
            // 如果传入的sheet名不存在则默认指向第1个sheet.
            sheet = workbook.getSheetAt(0);
        }
        if (sheet == null) {
            throw new IOException("文件sheet不存在");
        }
        //获取数据
        int rows = sheet.getLastRowNum();
        if (rows == 0) {
            U.assertException("当前工作簿没数据");
        }
        //行号从0开始，所以需要+1
        rows = rows + 1;
        // 默认序号
        int serialNum = 0;
        // 有数据时才处理 得到类的所有field.
        Field[] allFields = clazz.getDeclaredFields();
        //用于校验是否存在某些字段
        Map<String, Field> fieldNameMap = new HashMap<>();
        // 定义一个map用于存放列的序号和field.
        Map<Integer, Field> fieldsMap = new HashMap<>();
        for (Field field : allFields) {
            // 设置类的私有字段属性可访问.
            field.setAccessible(true);
            fieldsMap.put(++serialNum, field);
            fieldNameMap.put(field.getName(), field);
        }
        //
        DateTimeFormatter dtf = DateTimeFormatter.ofPattern(DateFormatType.YYYY_MM_DD_HH_MM_SS.getValue());
        DateTimeFormatter df = DateTimeFormatter.ofPattern(DateFormatType.YYYY_MM_DD.getValue());
        SimpleDateFormat sdf = new SimpleDateFormat(DateFormatType.YYYY_MM_DD_HH_MM_SS.getValue());
        //
        Row row = null;
        Cell cell = null;
        try {
            // 从第2行开始取数据,默认第一行是表头.
            for (int i = 1; i < rows; i++) {
                row = sheet.getRow(i);
                if (row == null) {
                    continue;
                }
                T entity = clazz.newInstance();
                for (int j = 0; j < serialNum; j++) {
                    cell = row.getCell(j);
                    if (cell == null) {
                        continue;
                    }

                    // 从map中得到对应列的field.
                    Field field = fieldsMap.get(j + 1);
                    // 取得类型,并根据对象类型设置值.
                    Class<?> fieldType = field.getType();

                    String c;
                    //excel数字精度问题：如果是数字类型,转化
                    if (cell.getCellTypeEnum().equals(CellType.NUMERIC) && fieldType != Date.class) {
                        NumberFormat numberFormat = NumberFormat.getNumberInstance();
                        double d = cell.getNumericCellValue();
                        // 关键在这里！
                        c = numberFormat.format(d);
                    } else {
                        // 先设置Cell的类型，然后就可以把纯数字作为String类型读进来了（这里数字类型 c可能存在精度问题）
                        cell.setCellType(CellType.STRING);
                        c = cell.getStringCellValue();
                    }

                    if (StringUtils.isEmpty(c)) {
                        continue;
                    }
                    if (String.class == fieldType) {
                        field.set(entity, c);
                    } else if ((Integer.TYPE == fieldType) || (Integer.class == fieldType)) {
                        field.set(entity, Integer.parseInt(c));
                    } else if ((Long.TYPE == fieldType) || (Long.class == fieldType)) {
                        field.set(entity, Long.valueOf(c));
                    } else if ((Float.TYPE == fieldType) || (Float.class == fieldType)) {
                        field.set(entity, Float.valueOf(c));
                    } else if ((Short.TYPE == fieldType) || (Short.class == fieldType)) {
                        field.set(entity, Short.valueOf(c));
                    } else if ((Double.TYPE == fieldType) || (Double.class == fieldType)) {
                        field.set(entity, Double.valueOf(c));
                    } else if (Character.TYPE == fieldType) {
                        if (c.length() > 0) {
                            field.set(entity, c.charAt(0));
                        }
                    } else if (Date.class == fieldType) {
                        if (cell.getCellTypeEnum() == CellType.NUMERIC) {
                            cell.setCellValue(sdf.format(cell.getNumericCellValue()));
                            c = sdf.format(cell.getNumericCellValue());
                        } else {
                            c = cell.getStringCellValue();
                        }
                    } else if (BigDecimal.class == fieldType) {
                        try {
                            field.set(entity, NumberUtil.toBigDecimal(c));
                        } catch (Exception ex) {
                            U.assertException("数值类型转换失败，请检查单元格值是否符合规范");
                        }
                    } else if (LocalDateTime.class == fieldType) {
                        field.set(entity, LocalDateTime.parse(excelDoubleToDate(c, DateFormatType.YYYY_MM_DD_HH_MM_SS), dtf));
                    } else if (LocalDate.class == fieldType) {
                        field.set(entity, LocalDate.parse(excelDoubleToDate(c, DateFormatType.YYYY_MM_DD), df));
                    }
                }
                if (BeanUtil.isNotEmpty(entity)) {
                    //判断是否有行号字段，有的话赋值
                    if (fieldNameMap.containsKey("rowNum")) {
                        //记录一下行数
                        fieldNameMap.get("rowNum").set(entity, row.getRowNum() + 1);
                    }
                    list.add(entity);
                }
            }
        } catch (Exception ex) {
            ex.printStackTrace();
            U.assertException(StrUtil.format("解析第{}行，第{}列时出错，错误原因：{}",
                    row == null ? "null" : row.getRowNum() + 1,
                    cell == null ? "null" : cell.getColumnIndex() + 1,
                    ex.getMessage()));
        }
        return list;
    }

    //解析Excel日期格式
    public static String excelDoubleToDate(String strDate, DateFormatType dateFormatType) {
        try {
            SimpleDateFormat sdf = new SimpleDateFormat(dateFormatType.getValue());
            Date tDate = doubleToDate(Double.parseDouble(strDate));
            return sdf.format(tDate);
        } catch (Exception e) {
            //e.printStackTrace();
            return strDate;
        }
    }

    //解析Excel日期格式
    public static Date doubleToDate(Double dVal) {
        Date tDate = new Date();
        long localOffset = tDate.getTimezoneOffset() * 60000; //系统时区偏移 1900/1/1 到 1970/1/1 的 25569 天
        tDate.setTime((long) ((dVal - 25569) * 24 * 3600 * 1000 + localOffset));
        return tDate;
    }


    /**
     * 常规字体-微软雅黑-12号
     */
    public static HSSFFont getFont(HSSFWorkbook wb) {
        HSSFFont font = wb.createFont();
        font.setFontHeightInPoints((short) 12);//设置字体大小
        font.setFontName("微软雅黑");
        return font;
    }

    /**
     * 白色字体-微软雅黑-12号
     */
    public static HSSFFont getWhiteFont(HSSFWorkbook wb) {
        HSSFFont font = wb.createFont();
        font.setFontHeightInPoints((short) 12);//设置字体大小
        font.setFontName("微软雅黑");
        font.setColor(HSSFColor.HSSFColorPredefined.WHITE.getIndex());
        font.setBold(true);
        return font;
    }

    /**
     * 标题字体-微软雅黑-12号-加粗
     */
    public static HSSFFont getTitleFont(HSSFWorkbook wb) {
        HSSFFont font = getFont(wb);
        font.setBold(true);
        return font;
    }

    /**
     * 常规字体-微软雅黑-12号-红色
     */
    public static HSSFFont getRedFont(HSSFWorkbook wb) {
        HSSFFont font = getFont(wb);
        font.setColor(Font.COLOR_RED);
        return font;
    }

    /**
     * 单元格样式-自动换行-全边框-左右垂直居中
     */
    public static CellStyle getCellStyle(HSSFWorkbook wb, HSSFFont font) {
        CellStyle style = wb.createCellStyle();//新建样式对象
        style.setWrapText(true);//设置自动换行
        style.setAlignment(HorizontalAlignment.CENTER);//居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);//居中
        style.setBorderBottom(BorderStyle.THIN);//下边框
        style.setBorderLeft(BorderStyle.THIN);//左边框
        style.setBorderRight(BorderStyle.THIN);//右边框
        style.setBorderTop(BorderStyle.THIN);//上边框
        style.setFont(font);
        return style;
    }

    /**
     * 单元格样式-自动换行-全边框-左右垂直居中-背景颜色
     */
    public static CellStyle getCellStyle(HSSFWorkbook wb, HSSFFont font, short bg) {
        CellStyle style = getCellStyle(wb, font);
        //设置背景色
        style.setFillForegroundColor(bg);
        //设置填充样式
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        return style;
    }


    /**
     * 设置下拉框(注意单元格内的字符不能超过255)
     *
     * @param sheet    指定sheet页
     * @param values   下拉框的值
     * @param firstRow 起始行号
     * @param lastRow  起始行号
     * @param firstCol 起始列号
     * @param lastCol  终止列号
     */
    public static void setDropDownBox(HSSFSheet sheet, String[] values, Integer firstRow, Integer lastRow, Integer firstCol, Integer lastCol) {
        // 加载下拉列表内容
        DVConstraint constraint = DVConstraint.createExplicitListConstraint(values);
        // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
        CellRangeAddressList regions = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
        // 数据有效性对象
        HSSFDataValidation data_validation_list = new HSSFDataValidation(regions, constraint);
        sheet.addValidationData(data_validation_list);
    }


    /**
     * sheet合并, 合并后删除数据源sheet
     *
     * @param workbook
     * @param sourceSheet 数据源
     * @param targetSheet 合并到
     * @return
     * @throws IOException
     */
    public static void mergeSheet(XSSFWorkbook workbook, int sourceSheet, int targetSheet) throws IOException {
        final XSSFSheet target = workbook.getSheetAt(targetSheet);
        final XSSFSheet source = workbook.getSheetAt(sourceSheet);
        int lastRowNum = target.getLastRowNum();

        final List<CellRangeAddress> mergedRegions = source.getMergedRegions();

        //处理合并后的mergedRegion
        for (CellRangeAddress mergedRegion : mergedRegions) {
            mergedRegion.setFirstRow(mergedRegion.getFirstRow() + lastRowNum + 1);
            mergedRegion.setLastRow(mergedRegion.getLastRow() + lastRowNum + 1);

        }

        //逐行复制
        for (int i = 0; i <= source.getLastRowNum(); i++) {
            final XSSFRow row = source.getRow(i);
            if (row == null) {
                target.createRow(++lastRowNum);
                continue;
            }
            final Iterator<Cell> cellIterator = row.cellIterator();
            final XSSFRow s1Row = target.createRow(++lastRowNum);
            while (cellIterator.hasNext()) {
                final Cell next = cellIterator.next();
                final CellStyle cellStyle = next.getCellStyle();
                final XSSFCell cell = s1Row.createCell(next.getColumnIndex());
                final CellType cellType = next.getCellType();
                cell.setCellStyle(cellStyle);

                switch (cellType) {
                    case STRING:
                        cell.setCellValue(next.getStringCellValue());
                        break;
                    case FORMULA:
                        cell.setCellFormula(next.getCellFormula());
                        continue;
                    case NUMERIC:
                        cell.setCellValue(next.getNumericCellValue());
                        break;
                    case BOOLEAN:
                        cell.setCellValue(next.getBooleanCellValue());
                }
                cell.setCellType(cellType);
            }
        }

        for (CellRangeAddress mergedRegion : mergedRegions) {
            target.addMergedRegion(mergedRegion);
        }
        //移除sourceSheet
        workbook.removeSheetAt(sourceSheet);
    }

    public static void buildExcel(List<Map<String, Object>> list, HSSFWorkbook wb, String sheetName, String[] titleArray, String[] fieldArray, String dest) {

        HSSFSheet sheet = wb.createSheet(sheetName);
        //样式
        //设置默认行高、列宽
        sheet.setDefaultRowHeight((short) 500);
        sheet.setDefaultColumnWidth(30);
        //字体
        HSSFFont font = getFont(wb);
        //标题字体
        HSSFFont titleFont = getWhiteFont(wb);
        titleFont.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        //标题样式
        CellStyle titleStyle = getCellStyle(wb, titleFont, IndexedColors.GREY_25_PERCENT.getIndex());
        //基础样式
        CellStyle style = getCellStyle(wb, font);
        style.setAlignment(HorizontalAlignment.LEFT);

        int firstRowNum = 0;
        if (StringUtils.isNotBlank(dest)) {
            HSSFFont destFont = getRedFont(wb);
            CellStyle destStyle = getCellStyle(wb, destFont);
            destStyle.setAlignment(HorizontalAlignment.LEFT);
            Row rowDest = sheet.createRow(firstRowNum);
            Cell bigDetailTitleCell = rowDest.createCell(0);
            bigDetailTitleCell.setCellStyle(destStyle);
            bigDetailTitleCell.setCellValue(dest);
            sheet.addMergedRegion(new CellRangeAddress(firstRowNum, firstRowNum, 0, fieldArray.length - 1));

            firstRowNum++;
        }

        //第一行，标题
        Row row0 = sheet.createRow(firstRowNum);
        for (int i = 0; i < titleArray.length; i++) {
            Cell cell = row0.createCell(i);
            cell.setCellStyle(titleStyle);
            cell.setCellValue(titleArray[i]);
        }
        //第2行开始填充数据
        for (int i = 0; i < list.size(); i++) {
            //从第2行开始
            Row row = sheet.createRow(i + 1 + firstRowNum);
            for (int j = 0; j < fieldArray.length; j++) {
                Cell cell = row.createCell(j);
                cell.setCellStyle(style);
                cell.setCellValue(MapUtil.getStr(list.get(i), fieldArray[j], StringUtils.EMPTY));
            }
        }
    }
}

 三、实际业务方法

  /**allInfo 所有信息 包括学生、老师、职工的数据List;phoneList 就是需要@的手机号**/
public void receiptDuePaymentReminderPushMsg(AllInfo allInfo, List<String> phoneList) {
       //创建一个HSSFWorkbook 
        HSSFWorkbook wb = new HSSFWorkbook();
        // 是否有数据需要构建excel推送
        boolean flag0 = studentFileBuild(wb, allInfo.getStudentList(), "", "学生信息");
        boolean flag1 = teacherFileBuild(wb, allInfo.getTeacherList(), "", "老师信息");
        //TODO 职工可自行补充
        boolean flag2 = zhigongFileBuild(wb, allInfo.getZhigongList(), "", "职工信息");


        //只要有一个通过就要发送
        if (!flag0 && !flag1 && !flag2) {
            log.debug("没有需要提醒的数据");
            return;
        }

         //文件名
        String fileName = LocalDate.now() + "学校信息汇总.xlsx";
        File file = new File(fileName);
        try {
            // 上传文件
            wb.write(file);

            // 推送消息：
            String text = "【学校通知】\n请查收学校信息汇总表。";
            //发送文本消息+excel文件到群
            WxPushUtil.sendFileMsg(file, text, phoneList);
        } catch (Exception e) {
            log.error("发送异常", e);
        } finally {
            if (file.exists()) {
                boolean deleteFlag = file.delete();
                log.debug("wxPushMsg delete tmp file: {}", fileName);
            }
        }

    }