1、需求
每个月月初需要对各部门的项目预算、进度、金额使用情况进行统计,统计完成后将报表通过邮件发送到指定的领导邮箱,项目基于SpringBoot实现,那就找SpringBoot发邮件的组件吧
2、找解决办法
通过在bing.cn搜索SpringBoot发邮件带附件,很容易就找到很多前辈们分享的方案
3、实现
<!-- Mail --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-mail</artifactId> </dependency>
在Service中依赖注入
@Resource private JavaMailSender javaMailSender;
package com.quality.centre.base.alert.pojo.entity;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.io.Serializable;
/**
* @Description: 发送邮件的实体类
*/
@Data
@NoArgsConstructor
@AllArgsConstructor
public class ToEmail implements Serializable {
/**
* 邮件接收方,可多人
*/
private String[] tos;
/**
* 邮件主题
*/
private String subject;
/**
* 邮件内容
*/
private String content;
/**
* 告警颜色
*/
private String alertColor;
/**
* 描述
*/
private String remark;
}
通过生产临时文件的方式发送,需要注意发送完成后删除临时文件,否则会产生很多垃圾文件
@Override
public R<?> sendEmailFile(ToEmail toEmail, File file) throws MessagingException {
MimeMessage message = javaMailSender.createMimeMessage();
MimeMessageHelper mimeMessageHelper = new MimeMessageHelper(message,true);
mimeMessageHelper.setFrom(emailUsername);
mimeMessageHelper.setTo(toEmail.getTos());
mimeMessageHelper.setSubject(toEmail.getSubject());
mimeMessageHelper.setText(toEmail.getContent(),true);
LocalDateTime now = LocalDateTime.now();
try {
if (file != null && file.exists()) {
FileSystemResource fsr = new FileSystemResource(file);
String filename = fsr.getFilename();
//添加附件
mimeMessageHelper.addAttachment(filename, fsr);
}
javaMailSender.send(message);
// 记录发送成功的日志
} catch (MailException e) {
log.error("邮件发送异常:{}",e.getMessage());
e.printStackTrace();
}
return R.success();
}
通过字节数组流的方式发送,好处时不会产生临时文件,没有产生垃圾文件的烦恼
// fileName,
ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
IoUtil.write(outputStream,false,ExcelExportUtils.getExcelBytes(fileName, summaryList, TransClassUtil.getTransClassBean(ReportSupplierRatingSummary.class)));
// 封装发邮件的参数
ToEmail toEmail = new ToEmail();
if (StrUtil.isEmpty(targetEmail)) {
toEmail.setTos(emails);
} else {
toEmail.setTos(targetEmail.split(","));
}
toEmail.setAlertColor(configuration.getAlertColor());
toEmail.setContent(content);
toEmail.setSubject(emailSubject);
try {
sendEmailFile(toEmail,fileName,outputStream);
} catch (Exception e) {
e.printStackTrace();
log.error(e.getMessage());
}
@SneakyThrows
@Override
public R<?> sendEmailFile(ToEmail toEmail, String filename, ByteArrayOutputStream outputStream) throws MessagingException {
MimeMessage message = javaMailSender.createMimeMessage();
MimeMessageHelper mimeMessageHelper = new MimeMessageHelper(message,true);
mimeMessageHelper.setFrom(emailUsername);
mimeMessageHelper.setTo(toEmail.getTos());
mimeMessageHelper.setSubject(toEmail.getSubject());
mimeMessageHelper.setText(toEmail.getContent(),true);
LocalDateTime now = LocalDateTime.now();
try {
if (outputStream != null) {
mimeMessageHelper.addAttachment(filename, ()-> new ByteArrayInputStream(outputStream.toByteArray()));
}
javaMailSender.send(message);
// 记录发送成功日志
} catch (MailException e) {
log.error("邮件发送异常:{}",e.getMessage());
e.printStackTrace();
}
return R.success();
}
/**
* 通过对象模板导出Excel
*/
public static < T > byte[] getExcelBytes( String fileName, List< T > list, Class< ? > clazz ) {
return getExcelBytes( fileName, list, clazz, true, null );
}
/**
* 通过对象模板导出Excel
*/
public static < T > byte[] getExcelBytes( String fileName, List< T > list, Class< ? > clazz, boolean distinct, List< ExcelHeader > excelHeaders ) {
// 如果 list 为空,则导出模板数据
if ( CollectionUtil.isEmpty( list ) ) {
return getTemplateBytes( fileName, clazz, true, distinct, excelHeaders );
}
clazz = list.get( 0 ).getClass();
// 获取表头字段
List< ExcelField > excelFields = getExcelFields( clazz, distinct, excelHeaders );
ExcelStyleDefinition styleDefinition = new ExcelStyleDefinition();
styleDefinition.setExcelStyle( clazz.getAnnotation( ExcelStyle.class ) );
List< Object > headNames = new ArrayList<>();
for ( int i = 0; i < excelFields.size(); i++ ) {
ExcelField each = excelFields.get( i );
styleDefinition.getHeaderStyleMap().put( i, each.getExcelHeaderStyle() );
styleDefinition.getRowStyleMap().put( i, each.getExcelRowStyle() );
styleDefinition.getHexColorMap().put( i, each.isHexColor() );
styleDefinition.getRequireMap().put( i, each.isRequire() );
headNames.add( each.getName() );
}
List< List< Object > > sheetData = getSheetData( excelFields, list );
sheetData.add( 0, headNames );
// 导出数据
ExcelDefinition excelDefinition = new ExcelDefinition();
excelDefinition.setStyleDefinition( styleDefinition );
Map< String, List< List< Object > > > sheetMap = new HashMap<>();
sheetMap.put( fileName, sheetData );
excelDefinition.setSheetMap( sheetMap );
excelDefinition.setFileName( fileName );
return exportExcel( excelDefinition );
}
@SneakyThrows
private static byte[] exportExcel( ExcelDefinition excelDefinition ) {
XSSFWorkbook book = ( XSSFWorkbook ) WorkbookFactory.create( true );
Set< Map.Entry< String, List< List< Object > > > > entries = excelDefinition.getSheetMap().entrySet();
for ( Map.Entry< String, List< List< Object > > > entry : entries ) {
List< List< Object > > sheetDataList = entry.getValue();
XSSFSheet sheet = book.createSheet( entry.getKey() );
ExcelStyleDefinition styleDefinition = excelDefinition.getStyleDefinition();
renderSheetStyle( book, sheet, styleDefinition.getExcelStyle() );
int rowLength = sheetDataList.size();
int columnLength = sheetDataList.get( 0 ).size();
int[][] mergeArray = new int[rowLength][columnLength];
ExcelStyle excelStyle = styleDefinition.getExcelStyle();
Map< Integer, XSSFCellStyle > cellStyleMap = new HashMap<>();
Map< String, XSSFCellStyle > hexColorCellStyleMap = new HashMap<>();
boolean init = true;
for ( int i = 0; i < sheetDataList.size(); i++ ) {
Row row = sheet.createRow( i );
List< Object > rowList = sheetDataList.get( i );
for ( int j = 0; j < rowList.size(); j++ ) {
Object o = rowList.get( j );
int v = 0;
Cell cell = row.createCell( j );
if ( i == 0 ) {
ExcelHeaderStyle headerStyle = styleDefinition.getHeaderStyleMap().get( j );
v = setCellValue( cell, o, renderRowStyle( book, excelStyle, headerStyle, null, false, styleDefinition.getRequireMap().get( j ) ) );
int width = 15;
if ( ObjectUtil.isNotNull( headerStyle ) ) {
width = headerStyle.width() == 0 ? ( ObjectUtil.isNotNull( excelStyle ) ? excelStyle.width() : width ) : headerStyle.width();
} else if ( ObjectUtil.isNotNull( excelStyle ) ) {
width = excelStyle.width();
}
sheet.setColumnWidth( j, width * 256 );
} else {
Boolean hexColor = styleDefinition.getHexColorMap().get( j );
if ( Optional.ofNullable( hexColor ).orElse( false ) ) {
if ( o != null ){
String hexColorStr = o.toString();
final int finalJ = j;
XSSFCellStyle style = hexColorCellStyleMap.computeIfAbsent( hexColorStr, color -> renderRowStyle( book, excelStyle, styleDefinition.getRowStyleMap().get( finalJ ), color, true, null ) );
v = setCellValue( cell, o, style );
}
} else {
if ( init && !cellStyleMap.containsKey( j ) ) {
cellStyleMap.put( j, renderRowStyle( book, excelStyle, styleDefinition.getRowStyleMap().get( j ), o, hexColor, null ) );
}
v = setCellValue( cell, o, cellStyleMap.get( j ) );
}
}
mergeArray[i][j] = v;
}
if ( i > 0 ) {
init = false;
}
}
mergeCells( sheet, mergeArray );
}
// 写数据
return getBytes( book );
}
private static void mergeCells( Sheet sheet, int[][] mergeArray ) {
for ( int x = 0; x < mergeArray.length; x++ ) {
int[] arr = mergeArray[x];
boolean merge = false;
int y1 = 0;
int y2 = 0;
for ( int y = 0; y < arr.length; y++ ) {
int value = arr[y];
if ( value == CELL_COLUMN_MERGE ) {
if ( !merge ) {
y1 = y;
}
y2 = y;
merge = true;
} else {
merge = false;
if ( y1 > 0 ) {
sheet.addMergedRegion( new CellRangeAddress( x, x, ( y1 - 1 ), y2 ) );
}
y1 = 0;
y2 = 0;
}
}
if ( y1 > 0 ) {
sheet.addMergedRegion( new CellRangeAddress( x, x, ( y1 - 1 ), y2 ) );
}
}
int xLen = mergeArray.length;
int yLen = mergeArray[0].length;
for ( int y = 0; y < yLen; y++ ) {
boolean merge = false;
int x1 = 0;
int x2 = 0;
for ( int x = 0; x < xLen; x++ ) {
int value = mergeArray[x][y];
if ( value == CELL_ROW_MERGE ) {
if ( !merge ) {
x1 = x;
}
x2 = x;
merge = true;
} else {
merge = false;
if ( x1 > 0 ) {
sheet.addMergedRegion( new CellRangeAddress( ( x1 - 1 ), x2, y, y ) );
}
x1 = 0;
x2 = 0;
}
}
if ( x1 > 0 ) {
sheet.addMergedRegion( new CellRangeAddress( ( x1 - 1 ), x2, y, y ) );
}
}
}
private static int setCellValue( Cell cell, Object o, XSSFCellStyle style ) {
cell.setCellStyle( style );
if ( ObjectUtil.isNull( o ) ) {
cell.setCellType( CellType.STRING );
cell.setCellValue( "" );
return CELL_OTHER;
}
// 是否为字符串
if ( o instanceof String || isNumeric( o ) ) {
String s = o.toString();
// 当数字类型长度超过8位时,改为字符串类型显示(Excel数字超过一定长度会显示为科学计数法)
if ( isNumeric( o ) && s.length() < 8 ) {
cell.setCellType( CellType.NUMERIC );
cell.setCellValue( Double.parseDouble( s ) );
return CELL_OTHER;
} else {
cell.setCellType( CellType.STRING );
cell.setCellValue( s );
}
if ( s.equals( ROW_MERGE ) ) {
return CELL_ROW_MERGE;
} else if ( s.equals( COLUMN_MERGE ) ) {
return CELL_COLUMN_MERGE;
} else {
return CELL_OTHER;
}
}
if ( o instanceof Boolean ) {
cell.setCellType( CellType.BOOLEAN );
cell.setCellValue( ( Boolean ) o );
return CELL_OTHER;
}
if ( o instanceof BigDecimal ) {
cell.setCellType( CellType.NUMERIC );
cell.setCellValue( ( ( BigDecimal ) o ).setScale( 3, RoundingMode.HALF_UP ).doubleValue() );
return CELL_OTHER;
}
if ( o instanceof Date ) {
cell.setCellType( CellType.STRING );
cell.setCellValue( DateUtil.format( ( Date ) o, DatePattern.NORM_DATE_PATTERN ) );
return CELL_OTHER;
}
if ( o instanceof LocalDate ) {
cell.setCellType( CellType.STRING );
cell.setCellValue( LocalDateTimeUtil.format( ( LocalDate ) o, DatePattern.NORM_DATE_PATTERN ) );
return CELL_OTHER;
}
if ( o instanceof LocalDateTime ) {
cell.setCellType( CellType.STRING );
cell.setCellValue( LocalDateTimeUtil.format( ( LocalDateTime ) o, DatePattern.NORM_DATETIME_PATTERN ) );
return CELL_OTHER;
}
cell.setCellType( CellType.STRING );
cell.setCellValue( o.toString() );
return CELL_OTHER;
}
private static XSSFCellStyle renderRowStyle( XSSFWorkbook workbook, ExcelStyle excelStyle, Annotation style, Object o, Boolean hexColor, Boolean require ) {
if ( ObjectUtil.isNull( workbook ) ) {
return null;
}
XSSFFont font = workbook.createFont();
XSSFCellStyle cellStyle = workbook.createCellStyle();
boolean styleNotNull = ObjectUtil.isNotNull( excelStyle );
if ( styleNotNull ) {
font.setBold( excelStyle.bold() );
cellStyle.setWrapText( excelStyle.wrap() );
}
if ( ObjectUtil.isNotNull( style ) ) {
ExcelRowStyle rowStyle;
if ( style instanceof ExcelRowStyle ) {
rowStyle = ( ExcelRowStyle ) style;
} else {
ExcelHeaderStyle headerStyle = ( ExcelHeaderStyle ) style;
rowStyle = headerStyle.rowStyle();
}
int bold = rowStyle.bold();
font.setBold( bold == 0 ? ( styleNotNull && excelStyle.bold() ) : bold > 0 );
font.setColor( rowStyle.color().index );
int wrap = rowStyle.wrap();
cellStyle.setWrapText( wrap == 0 ? ( styleNotNull && excelStyle.wrap() ) : wrap > 0 );
cellStyle.setAlignment( rowStyle.horizontalAlign() );
cellStyle.setVerticalAlignment( rowStyle.verticalAlign() );
cellStyle.setFillForegroundColor( rowStyle.backgroundColor().index );
cellStyle.setFillPattern( FillPatternType.SOLID_FOREGROUND );
} else {
cellStyle.setAlignment( HorizontalAlignment.CENTER );
cellStyle.setVerticalAlignment( VerticalAlignment.CENTER );
}
if ( require != null && require ) {
XSSFColor fontColor = new XSSFColor();
fontColor.setARGBHex( "FF0000" );
font.setColor( fontColor );
}
cellStyle.setFont( font );
if ( styleNotNull && excelStyle.border() ) {
cellStyle.setBorderTop( BorderStyle.THIN );
cellStyle.setTopBorderColor( IndexedColors.BLACK.index );
cellStyle.setBorderRight( BorderStyle.THIN );
cellStyle.setRightBorderColor( IndexedColors.BLACK.index );
cellStyle.setBorderBottom( BorderStyle.THIN );
cellStyle.setBottomBorderColor( IndexedColors.BLACK.index );
cellStyle.setBorderLeft( BorderStyle.THIN );
cellStyle.setLeftBorderColor( IndexedColors.BLACK.index );
}
if ( hexColor != null && hexColor && o != null ) {
String hexColorStr = o.toString();
XSSFColor xssfColor = new XSSFColor();
if ( hexColorStr.startsWith( "#" ) ) {
hexColorStr = hexColorStr.substring( 1 );
}
if ( hexColorStr.length() == 6 || hexColorStr.length() == 8 ) {
xssfColor.setARGBHex( hexColorStr );
cellStyle.setFillForegroundColor( xssfColor );
cellStyle.setFillBackgroundColor( xssfColor );
cellStyle.setFillPattern( FillPatternType.SOLID_FOREGROUND );
font.setColor( xssfColor );
}
}
return cellStyle;
}
@SneakyThrows
private static byte[] getBytes( XSSFWorkbook book ) {
ByteArrayOutputStream bos = new ByteArrayOutputStream();
book.write( bos );
return bos.toByteArray();
}
生成excel部分可以按照自己的想法实现,上面缺少部分自定义注解实现部分,照着poi或者easyexcel的示例都可以将表中读取的内容写入excel