这周的需求是:统计出昨天的数据,然后写到Excel里面发邮件,想到每次都要用jwt或者最原始的poi写代码,就很麻烦,于是我用了以前学到的EasyPoi,然后封装成了工具类。
首先是导入依赖:
<!-- EasyPoi -->
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>4.4.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>4.4.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>4.4.0</version>
</dependency>
我以前的博客说过一次,要使用EasyPoi,首先要创建一个实体类BO ,然后利用纯注解的方式进行开发,下面是实体类:
import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import lombok.Data;
import java.io.Serializable;
/**
* @ClassName OperationDataReport
* @Author 王弈程
* @Date 2022/2/15 16:44
**/
@ExcelTarget("运营数据日报")
@Data
public class OperationDataReportBO implements Serializable {
@Excel(name = "当前有效活动总量",orderNum = "1",width = 20,needMerge = true)
private long currentTotalActive;
@Excel(name = "当前有效资源提供方总量",orderNum = "2",width = 20,needMerge = true)
private long currentTotalActiveResourceProvides;
@Excel(name = "当前有效资源使用方总量",orderNum = "3",width = 20,needMerge = true)
private long currentTotalActiveResourceUse;
@Excel(name = "昨日资源同步领取总量",orderNum = "4",width = 20,needMerge = true)
private long yesterdayResourcesSynchronReceiveTotal;
@Excel(name = "昨日资源异步领取总量",orderNum = "5",width = 20,needMerge = true)
private long yesterdayResourcesAsynchronReceiveTotal;
@Excel(name = "昨日资源领取总量",orderNum = "6",width = 20,needMerge = true)
private long yesterdayResourcesReceiveTotal;
@Excel(name = "昨日资源同步领取号码总量",orderNum = "7",width = 20,needMerge = true)
private long yesterdayResourcesSynchronReceivePhoneTotal;
@Excel(name = "昨日资源异步领取号码总量",orderNum = "8",width = 20,needMerge = true)
private long yesterdayResourcesAsynchronReceivePhoneTotal;
@Excel(name = "昨日资源领取号码总量",orderNum = "9",width = 20,needMerge = true)
private long yesterdayResourcesReceivePhoneTotal;
@Excel(name = "昨日新增库存总量",orderNum = "10",width = 20,needMerge = true)
private long yesterdayResourcesAddTotal;
@Excel(name = "昨日发送库存预警短信条目数",orderNum = "11",width = 20,needMerge = true)
private long yesterdaySendAlertSMSTotal;
}
因为应用场景不同,因此用法也不一样,我在这里给大家模拟一下,接下来是业务层接口类:
import lombok.extern.ohaotian.HTServiceAPI;
import java.util.Date;
/**
* @ClassName OperationDataReportService
* @Author 王弈程
* @Date 2022/2/15 15:47
**/
@HTServiceAPI
public interface OperationDataReportService {
void getYesterdayOperationData(Date date) throws Exception;
}
实现类:
import com.unicom.resinteractive.api.bo.OperationDataReportBO;
import com.unicom.resinteractive.api.bo.SendEmailBO;
import com.unicom.resinteractive.api.service.OperationDataReportService;
import com.unicom.resinteractive.dao.OperationDataReportMapper;
import com.unicom.resinteractive.service.SendEmailService;
import com.unicom.resinteractive.util.DateUtils;
import com.unicom.resinteractive.util.EasyPoiUtil;
import lombok.extern.ohaotian.HTServiceImpl;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
/**
* @ClassName OperationDataReportServiceImpl
* @Author 王弈程
* @Date 2022/2/15 15:48
**/
@HTServiceImpl
@Slf4j
public class OperationDataReportServiceImpl implements OperationDataReportService {
@Value("${jd_ip}")
private String ip;
@Value("${jd_port}")
private String port;
@Value("${jd_username}")
private String username;
@Value("${jd_password}")
private String password;
@Value("${rb_path}")
private String uploadPath;
@Value("${rb_local_path}")
private String localPath;
@Autowired
private OperationDataReportMapper operationDataReportMapper;
@Value("${rb.email.toUsers.operationDateReport}")
private String Users;
@Autowired
private SendEmailService sendEmailService;
@Override
public void getYesterdayOperationData(Date date) throws Exception {
log.info("进入到OperationDataReportServiceImpl");
//获取昨天的时间 yyyy-mm-dd
final String yesterday = DateUtils.addDateDay2(date, -1);
String startTime = yesterday + " 00:00:00";
log.info("查询条件:开始时间"+startTime);
String endTime = yesterday + " 23:59:59";
log.info("查询条件:结束时间"+endTime);
OperationDataReportBO operationDataReportBO = new OperationDataReportBO();
//获取数据
//当前有效活动总量
long countCurrentTotalActive = operationDataReportMapper.countCurrentTotalActive();
operationDataReportBO.setCurrentTotalActive(countCurrentTotalActive);
log.info("当前有效活动总量:"+countCurrentTotalActive);
//当前有效资源提供方总量
long countCurrentTotalActiveResourceProvides = operationDataReportMapper.countCurrentTotalActiveResourceProvides();
operationDataReportBO.setCurrentTotalActiveResourceProvides(countCurrentTotalActiveResourceProvides);
log.info("当前有效资源提供方总量:"+countCurrentTotalActiveResourceProvides);
//当前有效资源使用方总量
long countCurrentTotalActiveResourceUse = operationDataReportMapper.countCurrentTotalActiveResourceUse();
operationDataReportBO.setCurrentTotalActiveResourceUse(countCurrentTotalActiveResourceUse);
log.info("当前有效资源使用方总量:"+countCurrentTotalActiveResourceUse);
//昨日资源同步领取总量
long countYesterdayResourcesSynchronReceiveTotal = operationDataReportMapper.countYesterdayResourcesSynchronReceiveTotal(startTime, endTime);
operationDataReportBO.setYesterdayResourcesSynchronReceiveTotal(countYesterdayResourcesSynchronReceiveTotal);
log.info("昨日资源同步领取总量:"+countYesterdayResourcesSynchronReceiveTotal);
//昨日资源异步领取总量
long countYesterdayResourcesAsynchronReceiveTotal = operationDataReportMapper.countYesterdayResourcesAsynchronReceiveTotal(startTime, endTime);
operationDataReportBO.setYesterdayResourcesAsynchronReceiveTotal(countYesterdayResourcesAsynchronReceiveTotal);
log.info("昨日资源异步领取总量:"+countYesterdayResourcesAsynchronReceiveTotal);
//昨日资源领取总量(同步+异步)
long countYesterdayResourcesReceiveTotal = operationDataReportMapper.countYesterdayResourcesReceiveTotal(startTime, endTime);
operationDataReportBO.setYesterdayResourcesReceiveTotal(countYesterdayResourcesReceiveTotal);
log.info("昨日资源领取总量(同步+异步):"+countYesterdayResourcesReceiveTotal);
//昨日资源同步领取号码总量
long countYesterdayResourcesSynchronReceivePhoneTotal = operationDataReportMapper.countYesterdayResourcesSynchronReceivePhoneTotal(startTime, endTime);
operationDataReportBO.setYesterdayResourcesSynchronReceivePhoneTotal(countYesterdayResourcesSynchronReceivePhoneTotal);
log.info("昨日资源同步领取号码总量:"+countYesterdayResourcesSynchronReceivePhoneTotal);
//昨日资源异步领取号码总量
long countYesterdayResourcesAsynchronReceivePhoneTotal = operationDataReportMapper.countYesterdayResourcesAsynchronReceivePhoneTotal(startTime, endTime);
operationDataReportBO.setYesterdayResourcesAsynchronReceivePhoneTotal(countYesterdayResourcesAsynchronReceivePhoneTotal);
log.info("昨日资源异步领取号码总量:"+countYesterdayResourcesAsynchronReceivePhoneTotal);
//昨日资源领取号码总量(同步+异步)
long countYesterdayResourcesReceivePhoneTotal = operationDataReportMapper.countYesterdayResourcesReceivePhoneTotal(startTime, endTime);
operationDataReportBO.setYesterdayResourcesReceivePhoneTotal(countYesterdayResourcesReceivePhoneTotal);
log.info("昨日资源领取号码总量(同步+异步):"+countYesterdayResourcesReceivePhoneTotal);
//昨日发送库存预警短信条目数
long countYesterdaySendAlertSMSTotal = operationDataReportMapper.countYesterdaySendAlertSMSTotal(startTime, endTime);
operationDataReportBO.setYesterdaySendAlertSMSTotal(countYesterdaySendAlertSMSTotal);
log.info("昨日发送库存预警短信条目数:"+countYesterdaySendAlertSMSTotal);
//昨日新增库存总量
//1.首先查询t_jackpot更新后的奖品总量
long countYesterdayUpdateTotal = operationDataReportMapper.countYesterdayUpdateTotal(startTime, endTime);
log.info("t_jackpot更新后的奖品总量:"+countYesterdayUpdateTotal);
//2.查询t_jackpot昨日更新的奖品
List<String> yesterdayUpdateJackpots = operationDataReportMapper.selectYesterdayUpdateJackpots(startTime, endTime);
log.info("t_jackpot昨日更新的奖品:"+yesterdayUpdateJackpots);
//3.查询t_jackpot_operation_log更新之前的数量
Long countOperationYesterdayData = operationDataReportMapper.countOperationYesterdayData(startTime, endTime, yesterdayUpdateJackpots);
log.info("t_jackpot_operation_log更新之前的数量:"+countOperationYesterdayData);
if (countOperationYesterdayData == null){
countOperationYesterdayData = 0L;
}
//4.做差求出昨日更新量
long yesterdayTotalUpdate = countYesterdayUpdateTotal - countOperationYesterdayData;
log.info("做差求出昨日更新量:"+yesterdayTotalUpdate);
operationDataReportBO.setYesterdayResourcesAddTotal(yesterdayTotalUpdate);
List<OperationDataReportBO> list = new ArrayList<>();
list.add(operationDataReportBO);
//Excel文件名字
String yyyyMMdd = DateUtils.dateToString(date);
String fileName = yesterday + "运营数据报表.xls";
//Excel第一行大标题
String title = "运营数据报表-" + yyyyMMdd;
//Excel中sheet页名字
String sheetName = "运营数据报表-" + yyyyMMdd;
//创建Excel,并上传到FTP
EasyPoiUtil.exportExcel(list,
title,
sheetName,
fileName,
OperationDataReportBO.class,
localPath,
username,
password,
uploadPath,
ip,
port,
true,
false);
//发送邮件
//Excel保存路径
String path = localPath+ "/" + fileName;
Calendar cal = Calendar.getInstance();
cal.add(Calendar.DATE,-1);
String time = new SimpleDateFormat("yyyy-MM-dd ").format(cal.getTime());
SendEmailBO sendEmailBO = new SendEmailBO();
ArrayList<String> tos = new ArrayList<>();
String Emailtitle = "【资源对接平台运营数据日报】-----" + DateUtils.dateToString(new Date());
String Emailcontent = "各位领导同事好:\n附件是资源对接平台运营数据日报" + time + "(实际产生数据日期)数据,请查收。";
for (String s : Users.split(",")) {
if (StringUtils.isBlank(s)){
log.info("遍历后的发送邮件对象为空,停止发送");
return;
}
log.info("遍历后的发送邮件对象:{}",s);
tos.add(s);
sendEmailBO.setToUsers(tos);
sendEmailBO.setToTitle(Emailtitle);
sendEmailBO.setToContent(Emailcontent);
sendEmailBO.setFilePath(new String[]{path});
}
sendEmailService.sendEmail(sendEmailBO);
}
}
接下来就是生成Excel的工具类:
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import com.jcraft.jsch.SftpException;
import jxl.write.WriteException;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Value;
import java.io.*;
import java.util.Collection;
import java.util.Date;
/**
* @ClassName EasyPoiUtil
* @Author 王弈程
* @Date 2022/2/15 16:23
**/
@Slf4j
public class EasyPoiUtil {
public static void exportExcel(Collection<?> data, //Excel数据
String title, //Excel第一行大标题
String sheetName, //Excel中sheet页名字
String fileName, //Excel文件名字
Class<?> pojoClass, //Excel数据的BO实体类
String localPath, //Excel保存的本地路径
String username, //FTP用户名
String password, //FTP密码
String uploadPath, //FTP上传路径
String ip, //FTP的ip地址
String port, //FTP的端口号
boolean isCreateHeader, //是否需要创建题头
boolean isNeedSignature ) throws Exception, Exception { //是否需要签名
log.info("进入到EasyPoiUtil");
// 写到服务器上
String path = localPath+ "/" + fileName;
File file1 = new File(localPath);
if(!file1.exists()){
file1.mkdirs();
}
// Io流
File name = new File(path);
try {
ExportParams exportParams = new ExportParams(title, sheetName);
exportParams.setCreateHeadRows(isCreateHeader);
exportParams.setStyle(KscExcelStyle.class);
Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, data);
//如果需页底要有签名,则需要合并单元格并为其设置样式和签名模板内容
if (isNeedSignature) {
Sheet sheet = workbook.getSheet(sheetName);
int lastRowNum = sheet.getLastRowNum();
short lastCellNum = sheet.getRow(2).getLastCellNum();
CellRangeAddress rangeAddress = new CellRangeAddress(lastRowNum+1 , lastRowNum+2 , 0, lastCellNum-1);
sheet.addMergedRegion(rangeAddress);
Cell signatureCell = sheet.createRow(lastRowNum + 1).createCell(0);
CellStyle signatureCellStyle = workbook.createCellStyle();
signatureCellStyle.setAlignment(HorizontalAlignment.RIGHT);
signatureCell.setCellStyle(signatureCellStyle);
signatureCell.setCellValue("签名: 日期: ");
}
//将Excel写入指定的位置
FileOutputStream outputStream = new FileOutputStream(path);
workbook.write(outputStream);
outputStream.close();
workbook.close();
log.info("开始上传文件到sftp");
SFTPUtils sftp = new SFTPUtils(username, password, ip, Integer.parseInt(port));
sftp.login();
File file = new File(localPath+ "/" + fileName);
InputStream is = new FileInputStream(file);
log.info("上传路径:{};文件名:{};", uploadPath, fileName);
sftp.upload("/",uploadPath, fileName, is);
sftp.logout();
log.info("上传文件到sftp完成");
} catch (IOException e) {
e.printStackTrace();
} catch (SftpException e) {
e.printStackTrace();
}
}
}
接下来是样式类,如果想换一个样式,直接写一个新的样式类,然后换成新的这个就可以了
import cn.afterturn.easypoi.excel.export.styler.ExcelExportStylerDefaultImpl;
import cn.afterturn.easypoi.excel.export.styler.IExcelExportStyler;
import org.apache.poi.ss.usermodel.*;
/**
* @ClassName KscExcelStyle
* @Author 王弈程
* @Date 2022/2/15 16:30
*/
public class KscExcelStyle extends ExcelExportStylerDefaultImpl implements IExcelExportStyler {
public KscExcelStyle(Workbook workbook) {
super(workbook);
}
/**
* 覆盖此方法实现自定义HeaderStyle
* @param i
* @return
*/
@Override
public CellStyle getHeaderStyle(short i) {
CellStyle style = getBaseCellStyle(workbook);
style.setFont(getFont(workbook, (short) 12, true));
return style;
/**
* 覆盖此方法实现自定义TitleStyle
* @param i
* @return
*/
}
@Override
public CellStyle getTitleStyle(short i) {
CellStyle style = getBaseCellStyle(workbook);
style.setFont(getFont(workbook, (short) 11, false));
//背景色
style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
return style;
}
private CellStyle getBaseCellStyle(Workbook workbook) {
CellStyle style = workbook.createCellStyle();
//下边框
style.setBorderBottom(BorderStyle.THIN);
//左边框
style.setBorderLeft(BorderStyle.THIN);
//上边框
style.setBorderTop(BorderStyle.THIN);
//右边框
style.setBorderRight(BorderStyle.THIN);
//水平居中
style.setAlignment(HorizontalAlignment.CENTER);
//上下居中
style.setVerticalAlignment(VerticalAlignment.CENTER);
//设置自动换行
style.setWrapText(true);
return style;
}
/**
* 字体样式
*
* @param size 字体大小
* @param isBold 是否加粗
* @return
*/
private Font getFont(Workbook workbook, short size, boolean isBold) {
Font font = workbook.createFont();
//字体样式
font.setFontName("宋体");
//是否加粗
font.setBold(isBold);
//字体大小
font.setFontHeightInPoints(size);
return font;
}
/**
* 数据行样式
*
* @param parity 可以用来表示奇偶行
* @param entity 数据内容
* @return 样式
*/
/*
@Override
public CellStyle getStyles(boolean parity, ExcelExportEntity entity) {
CellStyle style = getBaseCellStyle(workbook);
style.setFont(getFont(workbook, (short) 11, false));
style.setDataFormat(STRING_FORMAT);
return style;
}
*/
/**
* 获取样式方法
*
* @param dataRow 数据行
* @param obj 对象
* @param data 数据
*/
/*
@Override
public CellStyle getStyles(Cell cell, int dataRow, ExcelExportEntity entity, Object obj, Object data) {
return getStyles(true, entity);
}
*/
}