例如，企微群定时推送：内容+表格
群机器人：学校基本信息：
[一个记录学校基本信息的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));
}
}
}
}
三、实际业务方法文章来源：https://www.toymoban.com/news/detail-805926.html
/**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);
}
}
}文章来源地址https://www.toymoban.com/news/detail-805926.html
到了这里，关于企微群机器人推送文本+Excel的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
