【easyexcel】1.实时下载导出报表
实时下载导出报表
====================================================================================
1.修改总览
1.pom.xml
<easyexcel.version>2.2.7</easyexcel.version>
<poi.version>3.17</poi.version>
<!-- 阿里巴巴easyexcel start-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>${easyexcel.version}</version>
<exclusions>
<exclusion>
<groupId>cglib</groupId>
<artifactId>cglib</artifactId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>cglib</groupId>
<artifactId>cglib</artifactId>
<version>2.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>${poi.version}</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>${poi.version}</version>
</dependency>
<!-- 阿里巴巴easyexcel end-->
2.入口controller
package com.sxd.swapping.downloadAndUpload.xlsxDownloadAndUpload.controller;
import com.alibaba.fastjson.JSON;
import com.sxd.swapping.downloadAndUpload.xlsxDownloadAndUpload.dto.ReportDTO;
import com.sxd.swapping.downloadAndUpload.xlsxDownloadAndUpload.enums.ReportEnum;
import com.sxd.swapping.downloadAndUpload.xlsxDownloadAndUpload.exception.ReportException;
import com.sxd.swapping.downloadAndUpload.xlsxDownloadAndUpload.query.DUQuery;
import com.sxd.swapping.downloadAndUpload.xlsxDownloadAndUpload.service.ReportService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.validation.annotation.Validated;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import javax.validation.Valid;
import java.io.File;
import java.io.FileInputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
@Slf4j
@Validated
@RestController
@RequestMapping("/xlsx/downloadAndUpload")
public class XlsxDownloadAndUploadController {
@Resource
private ReportService reportService;
/**
* 实时下载 xlsx文件
* 将根据查询条件实时查询到的数据 通过 xlsx文件导出
* @param query 导出查询条件
* @return
*/
@RequestMapping(value = "/rtDownload", method = {RequestMethod.GET})
public void rtDownload(HttpServletResponse response, @Valid DUQuery query) {
OutputStream outputStream = null;
FileInputStream inputStream =null;
File file = null;
try {
ReportDTO dto = new ReportDTO();
dto.setReportEnum(ReportEnum.RT_REPORT);
dto.setRequestParams(JSON.toJSONString(query));
file = reportService.generateReport(dto);
response.setContentType("mutipart/form-data");
response.setCharacterEncoding("utf-8");
response.setHeader("Content-disposition","attachment;filename="+ URLEncoder.encode(file.getName(), "utf-8"));
outputStream = response.getOutputStream();
inputStream = new FileInputStream(file);
byte[] b = new byte[1024];
int length = 0;
while ((length = inputStream.read(b)) != -1) {
outputStream.write(b, 0, length);
}
outputStream.flush();
} catch (Exception e) {
log.error("导出业务报表发生错误:", e);
throw new ReportException("导出业务报表发生错误！");
}finally{
reportService.close(outputStream,inputStream);
reportService.delFile(file);
}
}
}
3.报表ReportService
package com.sxd.swapping.downloadAndUpload.xlsxDownloadAndUpload.service.impl;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.sxd.swapping.downloadAndUpload.xlsxDownloadAndUpload.context.ReportGenerateContext;
import com.sxd.swapping.downloadAndUpload.xlsxDownloadAndUpload.dto.ReportDTO;
import com.sxd.swapping.downloadAndUpload.xlsxDownloadAndUpload.enums.ReportEnum;
import com.sxd.swapping.downloadAndUpload.xlsxDownloadAndUpload.exception.ReportException;
import com.sxd.swapping.downloadAndUpload.xlsxDownloadAndUpload.handler.AbstractReportHandler;
import com.sxd.swapping.downloadAndUpload.xlsxDownloadAndUpload.service.ReportService;
import com.sxd.swapping.downloadAndUpload.xlsxDownloadAndUpload.utils.XlsxCellWidthUtil;
import com.sxd.swapping.downloadAndUpload.xlsxDownloadAndUpload.utils.XlsxCellWriteUtil;
import com.sxd.swapping.downloadAndUpload.xlsxDownloadAndUpload.utils.XlsxHeadUtil;
import com.sxd.swapping.utils.serialNum.SerialNumHelper;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.Properties;
/**
* 业务报表Service
*
* 报表导出的 核心逻辑在这里
*
* 1.边读边写 解决FullGC问题
* 2.集成多种业务报表 不同的报表类型可以在ReportEnum中以不同枚举定义 定义入参class 出参class 报表名称 等关键信息
* 3.单元格支持超过15位长的纯数字串不会显示成E+问题
* 4.generateReport() 生成临时File,支持【实时导出】 和 【上传远程服务器得到可直接下载的URL 暂未实现】
*/
@Slf4j
@Service
public class ReportServiceImpl implements ReportService {
@Resource
private ReportGenerateContext reportGenerateContext;
/**
* 生成报表临时文件
* @param reportDTO
* @return
*/
@Override
public File generateReport(ReportDTO reportDTO) {
ReportEnum reportEnum = reportDTO.getReportEnum();
if (reportEnum == null) {
throw new ReportException("报表类型必传！");
}
if (StringUtils.isBlank(reportDTO.getRequestParams())) {
throw new ReportException("报表入参数据必传！");
}
//1.获取临时文件路径[需定义自定义报表枚举]
String filePath = getFilePath(reportEnum.getTempFileName());
//2.初始化xlsx文件对象 [需定义自定义表头工具]
ExcelWriter excelWriter = EasyExcel
.write(filePath, reportEnum.getReportClass())
// .registerWriteHandler(new XlsxCellFontUtil()) //默认情况不用显式设置，除非业务有需求
// .registerWriteHandler(XlsxCellStyleUtil.getCellStyleStrategy()) //默认情况不用显式设置，除非业务有需求
.registerWriteHandler(new XlsxCellWidthUtil()) //一般情况下可以不用设置，除非表头没正常生成
.registerWriteHandler(new XlsxCellWriteUtil()) //如果导出数据中，有超过15位的数字串，则需要设置
.head(XlsxHeadUtil.getHeadByReportEnum(reportEnum)) //一般情况可以不用设置，除非表头没正常生成
.build();
WriteSheet writeSheet = EasyExcel.writerSheet(reportEnum.getDesc()).build();
reportDTO.setExcelWriter(excelWriter);
reportDTO.setWriteSheet(writeSheet);
//3.获取报表handler完成xlsx文件生成[需定义自定义handler并加入上下文]
AbstractReportHandler reportHandler = reportGenerateContext.getReportHandler(reportEnum);
reportHandler.generateReport(reportDTO);
//4.关闭easyxlsx写入器
excelWriter.finish();
return new File(filePath);
}
/**
* 根据文件名获取文件路径
* @param tempFileName
* @return
*/
@Override
public String getFilePath(String tempFileName) {
Properties properties = System.getProperties();
String path = properties.getProperty("user.dir");
if (properties.getProperty("os.name").toLowerCase().contains("win")) {
path += "\\";
} else {
path += "/";
}
path += String.format(tempFileName, SerialNumHelper.generateRecordId());
log.info("DownloadServiceImpl#getFilePath={}" , path);
return path;
}
/**
* 删除临时文件
* @param file
*/
@Override
public void delFile(File file) {
if (file != null && file.exists()) {
try {
file.delete();
}catch (Exception e) {
log.error("删除临时文件失败：", e);
}
}
}
/**
* 关闭资源
* @param outputStream
* @param inputStream
*/
@Override
public void close(OutputStream outputStream, InputStream inputStream) {
closeOutStream(outputStream);
cloaseInputStream(inputStream);
}
@Override
public void closeOutStream(OutputStream outputStream) {
if(outputStream !=null){
try {
outputStream.close();
} catch (IOException e) {
log.error("关闭输出流失败：", e);
}
}
}
@Override
public void cloaseInputStream(InputStream inputStream) {
if(inputStream != null ){
try {
inputStream.close();
} catch (IOException e) {
log.error("关闭输入流失败：", e);
}
}
}
}
4.报表枚举ReportEnum
package com.sxd.swapping.downloadAndUpload.xlsxDownloadAndUpload.enums;
import com.fasterxml.jackson.annotation.JsonCreator;
import com.sxd.swapping.downloadAndUpload.xlsxDownloadAndUpload.query.DUQuery;
import com.sxd.swapping.downloadAndUpload.xlsxDownloadAndUpload.vo.RTReportVO;
import lombok.AllArgsConstructor;
import lombok.Getter;
import java.util.Arrays;
import java.util.Objects;
@AllArgsConstructor
@Getter
public enum ReportEnum {
/** 枚举项 */
RT_REPORT(1, "实时导出报表", RTReportVO.class, DUQuery.class,"实时导出报表-%s.xlsx"),
;
/**值*/
private Integer value;
/**报表描述*/
private String desc;
/**导出模板*/
private Class reportClass;
/**业务入参*/
private Class requestParams;
/**报表文件名称*/
private String tempFileName;
@JsonCreator
public static ReportEnum valueOf(Integer value) {
return Arrays.stream(ReportEnum.values())
.filter(e -> Objects.equals(e.value, value)).findFirst()
.orElseThrow(() -> new RuntimeException("ReportEnum value=" + value + " not exists!"));
}
}
5.业务枚举DUStatusEnum
package com.sxd.swapping.downloadAndUpload.xlsxDownloadAndUpload.enums;
import com.fasterxml.jackson.annotation.JsonCreator;
import lombok.AllArgsConstructor;
import lombok.Getter;
import java.util.Arrays;
import java.util.Objects;
@AllArgsConstructor
@Getter
public enum DUStatusEnum {
WAIT(0,"等待处理"),
SUCCESS(1,"成功"),
FAIL(-1, "失败")
;
/**值*/
private Integer value;
/**报表描述*/
private String desc;
@JsonCreator
public static DUStatusEnum valueOf(Integer value) {
return Arrays.stream(DUStatusEnum.values())
.filter(e -> Objects.equals(e.value, value)).findFirst()
.orElseThrow(() -> new RuntimeException("DUStatusEnum value=" + value + " not exists!"));
}
}
6.报表上下文ReportGenerateContext
package com.sxd.swapping.downloadAndUpload.xlsxDownloadAndUpload.context;
import com.sxd.swapping.downloadAndUpload.xlsxDownloadAndUpload.enums.ReportEnum;
import com.sxd.swapping.downloadAndUpload.xlsxDownloadAndUpload.handler.AbstractReportHandler;
import com.sxd.swapping.downloadAndUpload.xlsxDownloadAndUpload.handler.RTReportHandler;
import org.springframework.beans.BeansException;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.stereotype.Component;
import java.util.HashMap;
import java.util.Map;
/**
* 报表生成上下文
*/
@Component
public class ReportGenerateContext implements ApplicationContextAware {
private Map<ReportEnum, AbstractReportHandler> reportHandlerMap;
@Override
public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
if (reportHandlerMap == null) {
reportHandlerMap = new HashMap<>();
reportHandlerMap.put(ReportEnum.RT_REPORT,applicationContext.getBean(RTReportHandler.class));
}
}
public AbstractReportHandler getReportHandler(ReportEnum reportEnum){
return reportHandlerMap.get(reportEnum);
}
}
7.抽象报表处理器 AbstractReportHandler
package com.sxd.swapping.downloadAndUpload.xlsxDownloadAndUpload.handler;
import com.sxd.swapping.downloadAndUpload.xlsxDownloadAndUpload.dto.ReportDTO;
/**
* 抽象的报表处理器
* 各种报表的 自定义的报表处理逻辑 均实现该抽象接口
*
*/
public abstract class AbstractReportHandler {
public abstract void generateReport(ReportDTO reportDTO);
}
8.实时报表处理器（上面的实现子类）RTReportHandler
package com.sxd.swapping.downloadAndUpload.xlsxDownloadAndUpload.handler;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.fastjson.JSON;
import com.sxd.swapping.downloadAndUpload.xlsxDownloadAndUpload.assemble.DUDB2VOAssemble;
import com.sxd.swapping.mybatis.pojo.DownloadUpload;
import com.sxd.swapping.downloadAndUpload.xlsxDownloadAndUpload.dto.ReportDTO;
import com.sxd.swapping.downloadAndUpload.xlsxDownloadAndUpload.enums.DUStatusEnum;
import com.sxd.swapping.downloadAndUpload.xlsxDownloadAndUpload.enums.ReportEnum;
import com.sxd.swapping.downloadAndUpload.xlsxDownloadAndUpload.exception.ReportException;
import com.sxd.swapping.downloadAndUpload.xlsxDownloadAndUpload.query.DUQuery;
import com.sxd.swapping.downloadAndUpload.xlsxDownloadAndUpload.service.DUDBService;
import com.sxd.swapping.downloadAndUpload.xlsxDownloadAndUpload.vo.RTReportVO;
import com.sxd.swapping.utils.dateTime.DateTimeHelper;
import org.apache.commons.collections4.CollectionUtils;
import org.springframework.stereotype.Component;
import javax.annotation.Resource;
import java.util.List;
import java.util.stream.Collectors;
/**
* 实时报表的 处理器
* 【每一种报表 是一个单独实现的 处理器】
* 【所以新增加一种报表 就去单独实现 各自的处理器即可】
*/
@Component
public class RTReportHandler extends AbstractReportHandler{
@Resource
private DUDBService dudbService;
@Resource
private DUDB2VOAssemble dudb2VOAssemble;
@Override
public void generateReport(ReportDTO reportDTO) {
ExcelWriter excelWriter = reportDTO.getExcelWriter();
WriteSheet writeSheet = reportDTO.getWriteSheet();
if (excelWriter == null){
throw new ReportException("easyexcel写入器不能为空");
}
if (writeSheet == null) {
throw new ReportException("sheet写入器不能为空");
}
//1.初始化入参
String requestParams = reportDTO.getRequestParams();
ReportEnum reportEnum = reportDTO.getReportEnum();
DUQuery duQuery = (DUQuery) JSON.parseObject(requestParams, reportEnum.getRequestParams());
//2.初次查询统计总数
int total = dudbService.count(duQuery);
int totalPage = total/duQuery.getPageSize() + 1;
//3.分页分批次边读边写 【防止超大数据量下FullGC】
for (int i = 1; i <= totalPage; i++) {
duQuery.setStart((duQuery.getCurrentPage()-1) * duQuery.getPageSize());
List<DownloadUpload> dbResult = dudbService.query(duQuery);
if (CollectionUtils.isNotEmpty(dbResult)) {
List<RTReportVO> reportDTOs = dbResult.stream().map(dudb2VOAssemble::from).collect(Collectors.toList());
transferDataDesc(reportDTOs);
excelWriter.write(reportDTOs,writeSheet);
clearOneBatch(reportDTOs, dbResult);
}
}
}
private void clearOneBatch(List<RTReportVO> reportVOS, List<DownloadUpload> dbResult){
if (reportVOS != null) {
reportVOS.clear();
}
if (dbResult != null ) {
dbResult.clear();
}
}
/**
* 将数据转化为描述性文字
* @param reportVOS
*/
private void transferDataDesc(List<RTReportVO> reportVOS){
if (CollectionUtils.isNotEmpty(reportVOS)) {
reportVOS.forEach(i-> {
if (i.getDuId() != null) {
i.setDuIdDesc(String.valueOf(i.getDuId()));
}
if (i.getCreateTime() != null) {
i.setCreateTimeDesc(DateTimeHelper.getDateTimeStr(i.getCreateTime()));
}
if (i.getDuStatus() != null) {
i.setDuStatusDesc(DUStatusEnum.valueOf(i.getDuStatus()).getDesc());
}
});
}
}
}
9.业务DUDBService
package com.sxd.swapping.downloadAndUpload.xlsxDownloadAndUpload.service.impl;
import com.sxd.swapping.mybatis.pojo.DownloadUpload;
import com.sxd.swapping.mybatis.dao.DUMapper;
import com.sxd.swapping.downloadAndUpload.xlsxDownloadAndUpload.query.DUQuery;
import com.sxd.swapping.downloadAndUpload.xlsxDownloadAndUpload.service.DUDBService;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
import java.util.List;
@Service
public class DUDBServiceImpl implements DUDBService {
@Resource
private DUMapper duMapper;
/**
* 假设 从第一页开始查询 一直查询到所有数据
*
* @param query
* @return
*/
@Override
public List<DownloadUpload> query(DUQuery query) {
return duMapper.queryDU(query);
}
@Override
public int count(DUQuery duQuery) {
return duMapper.countDU(duQuery);
}
}
10.mapstruct Spring支持的克隆转化 DUDB2VOAssemble
package com.sxd.swapping.downloadAndUpload.xlsxDownloadAndUpload.assemble;
import com.sxd.swapping.mybatis.pojo.DownloadUpload;
import com.sxd.swapping.downloadAndUpload.xlsxDownloadAndUpload.vo.RTReportVO;
import org.mapstruct.Mapper;
/**
* mapstruct
* Spring支持的克隆
*/
@Mapper(componentModel = "spring")
public interface DUDB2VOAssemble {
RTReportVO from(DownloadUpload downloadUpload);
}
11.导出报表的过程数据ReportDTO
package com.sxd.swapping.downloadAndUpload.xlsxDownloadAndUpload.dto;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.sxd.swapping.downloadAndUpload.xlsxDownloadAndUpload.enums.ReportEnum;
import lombok.Data;
/**
* 导出报表的 过程数据
*/
@Data
public class ReportDTO {
/**报告类型枚举*/
private ReportEnum reportEnum;
/**序列化入参*/
private String requestParams;
/**easyexcel写入器 所有报表通用*/
private ExcelWriter excelWriter;
/**sheet写入器 所有报表通用*/
private WriteSheet writeSheet;
}
12.业务查询DUQuery 和 PageQuery
package com.sxd.swapping.downloadAndUpload.xlsxDownloadAndUpload.query;
import lombok.Data;
/**
* 分页查询
*/
@Data
public abstract class PageQuery {
private Integer currentPage = 1;
private Integer pageSize = 10;
private Integer start;
}
package com.sxd.swapping.downloadAndUpload.xlsxDownloadAndUpload.query;
import lombok.Data;
import javax.validation.constraints.NotNull;
/**
* 上传下载业务的 查询条件
* DownloadAndUploadQuery
*/
@Data
public class DUQuery extends PageQuery{
/**主键ID*/
// @NotNull(message = "业务ID不能为空")
private Long duId;
/**状态*/
private Integer duStatus;
}
13.业务数据DUMapper
package com.sxd.swapping.mybatis.dao;
import com.sxd.swapping.mybatis.pojo.DownloadUpload;
import com.sxd.swapping.downloadAndUpload.xlsxDownloadAndUpload.query.DUQuery;
import org.apache.ibatis.annotations.Mapper;
import java.util.List;
@Mapper
public interface DUMapper {
List<DownloadUpload> queryDU(DUQuery duQuery);
int countDU(DUQuery duQuery);
}
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.sxd.swapping.mybatis.dao.DUMapper">
<select id="queryDU" parameterType="com.sxd.swapping.downloadAndUpload.xlsxDownloadAndUpload.query.DUQuery"
resultType="com.sxd.swapping.mybatis.pojo.DownloadUpload">
select
*
from
download_upload du
<where>
<if test="duId != null">
AND du.du_id = #{duId}
</if>
<if test="duStatus != null">
AND du.du_status = #{duStatus}
</if>
</where>
<if test="start != null and pageSize != null">
LIMIT #{start}, #{pageSize}
</if>
</select>
<select id="countDU" parameterType="com.sxd.swapping.downloadAndUpload.xlsxDownloadAndUpload.query.DUQuery"
resultType="java.lang.Integer">
select
count(id)
from
download_upload du
<where>
<if test="duId != null">
AND du.du_id = #{duId}
</if>
<if test="duStatus != null">
AND du.du_status = #{duStatus}
</if>
</where>
</select>
</mapper>
14.POJO DownloadUpload
package com.sxd.swapping.mybatis.pojo;
import lombok.Data;
import java.time.LocalDateTime;
/**
* 数据库表的POJO
*/
@Data
public class DownloadUpload {
/**
* DB 自增ID
*/
private Long id;
/**
* 业务ID
*/
private Long duId;
/**
* 业务名称
*/
private String duName;
/**
* 业务状态 枚举值
*/
private Integer duStatus;
/**
* 业务时间
*/
private LocalDateTime createTime;
}
15.业务视图(即报表预设列对象) RTReportVO
即导出的报表 应该导出那些列，列名是什么，哪些列忽略，在此设置
package com.sxd.swapping.downloadAndUpload.xlsxDownloadAndUpload.vo;
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import lombok.Data;
import java.time.LocalDateTime;
@Data
@ColumnWidth(25)
public class RTReportVO {
/**
* 业务ID
*/
@ExcelIgnore
private Long duId;
/**
* 业务ID
*/
@ExcelProperty(value = "业务ID",index = 0)
private String duIdDesc;
/**
* 业务名称
*/
@ExcelProperty(value = "业务名称",index = 1)
private String duName;
/**
* 业务状态 枚举值
*/
@ExcelIgnore
private Integer duStatus;
/**
* 业务状态 枚举描述
*/
@ExcelProperty(value = "业务状态",index = 2)
private String duStatusDesc;
/**
* 业务时间 DB字段
*/
@ExcelIgnore
private LocalDateTime createTime;
/**
* 业务时间 展示字段
*/
@ExcelProperty(value = "业务时间",index = 3)
private String createTimeDesc;
}
16.easy-excel工具类
xlsx单元格字体样式工具类 XlsxCellFontUtil
package com.sxd.swapping.downloadAndUpload.xlsxDownloadAndUpload.utils;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.AbstractCellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.sxd.swapping.downloadAndUpload.xlsxDownloadAndUpload.enums.DUStatusEnum;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import java.util.List;
/**
* 设置单元格字体样式
*
* 默认情况下 不用显式声明该Util 除非业务上有特殊需求
*/
public class XlsxCellFontUtil extends AbstractCellWriteHandler {
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
int columnIndex = cell.getColumnIndex();
// 列索引是2的时候是状态栏列
// 这里处理逻辑即: 如果状态是成功，则设置字体为绿色 【这里仅针对RTReport】
if (columnIndex == 2) {
String stringCellValue = cell.getStringCellValue();
if (DUStatusEnum.SUCCESS.getDesc().equalsIgnoreCase(stringCellValue)) {
Workbook workbook = writeSheetHolder.getSheet().getWorkbook();
CellStyle cellStyle = workbook.createCellStyle();
Font font = workbook.createFont();
font.setFontName("微软雅黑");
// 这里设置字体高度，需要看Excel的高度乘以20，比如：如果要想在Excel看到的高度是11，那么这里设置为220
font.setFontHeight((short) 220);
font.setColor(HSSFColor.HSSFColorPredefined.GREEN.getIndex());
cellStyle.setFont(font);
// 设置字体对齐方式
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setAlignment(HorizontalAlignment.LEFT);
cell.setCellStyle(cellStyle);
}
}
}
}
xlsx单元格样式处理器 XlsxCellStyleUtil
package com.sxd.swapping.downloadAndUpload.xlsxDownloadAndUpload.utils;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
/**
* xlsx单元格样式处理器
*
* 默认不用显式设置 除非业务有特殊需求
*/
public class XlsxCellStyleUtil {
private XlsxCellStyleUtil() {
throw new IllegalArgumentException();
}
public static HorizontalCellStyleStrategy getCellStyleStrategy() {
/*******自定义列标题和内容的样式******/
// 头的策略
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
// 背景设置为红色
headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
WriteFont headWriteFont = new WriteFont();
// 设置标题字体大小
headWriteFont.setFontHeightInPoints((short) 11);
// 设置标题字体
headWriteFont.setFontName("微软雅黑");
// 设置标题字体是否加粗
headWriteFont.setBold(true);
headWriteCellStyle.setWriteFont(headWriteFont);
// 设置是否自动换行
headWriteCellStyle.setWrapped(true);
// 内容的策略
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
// 设置内容字体
WriteFont contentWriteFont = new WriteFont();
// 字体大小
contentWriteFont.setFontName("微软雅黑");
// 设置标题字体大小
contentWriteFont.setFontHeightInPoints((short) 11);
contentWriteCellStyle.setWriteFont(contentWriteFont);
//设置 自动换行
contentWriteCellStyle.setWrapped(false);
//设置 垂直居中
contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//设置 水平居左
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
// 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
}
}
xlsx单元格宽度工具类 XlsxCellWidthUtil
package com.sxd.swapping.downloadAndUpload.xlsxDownloadAndUpload.utils;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import org.apache.poi.ss.usermodel.Cell;
import java.util.List;
/**
* xlsx单元格宽度工具类
*
* 一般情况下，可不一不用显式的重写该 方法，但 可能遇上 导出xlsx文件没有表头的情况，为解决这种异常，就需要显式去重写该方法。
*/
public class XlsxCellWidthUtil extends AbstractColumnWidthStyleStrategy {
@Override
protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
if (isHead) {
int columnWidth = 25;
writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
}
}
}
xlsx单元格格式工具类 XlsxCellWriteUtil
package com.sxd.swapping.downloadAndUpload.xlsxDownloadAndUpload.utils;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.ss.usermodel.*;
import java.util.List;
/**
* xlsx设置单元格格式为 文本格式工具类
*
* 第一步：xlsx 设置单元格格式为 文本格式
* 但是数字超过15位以上的数字串，依旧会显示为E+ 解决该问题，仅设置单元格格式为 文本格式还不能完全解决，
* 第二步：需要 将 数值类型的字段 Long类型字段 按照String 返回，去写入单元格，即可解决E+问题。 例如：ActivitySyncRecordDTO 类中的actId是Long类型超过15位的数字串，写出应该用actIdDesc为String类型。
*
* 【备选方案，未测试：需要配合在数字串前加上英文单引号' ，即可解决】
*
*/
public class XlsxCellWriteUtil implements CellWriteHandler {
@Override
public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
}
@Override
public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
//设置单元格格式为文本
Workbook workbook = writeSheetHolder.getSheet().getWorkbook();
CellStyle cellStyle = workbook.createCellStyle();
DataFormat dataFormat = workbook.createDataFormat();
cellStyle.setDataFormat(dataFormat.getFormat("@"));
cell.setCellStyle(cellStyle);
}
@Override
public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
}
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
}
}
xlsx设置表头工具类 XlsxHeadUtil
package com.sxd.swapping.downloadAndUpload.xlsxDownloadAndUpload.utils;
import com.sxd.swapping.downloadAndUpload.xlsxDownloadAndUpload.enums.ReportEnum;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
/**
* xlsx设置表头工具类
*
* 一般情况下 不需要显示设置该表头工具， easeexcel的注解标注了导出表结构一般会正常生成表头，例如RTReportVO类
* 如果发生导出的xlsx表头不见了，处理该异常则可以通过显式设置该表头 完成xlsx表头的正常生成
*
*/
public class XlsxHeadUtil {
public static List<List<String>> getHeadByReportEnum(ReportEnum reportEnum) {
List<List<String>> heads = new ArrayList<>();
switch (reportEnum) {
case RT_REPORT:
heads = generateRTReportHead();
break;
default:
break;
}
return heads;
}
private static List<List<String>> generateRTReportHead() {
List<List<String>> heads = new ArrayList<>();
heads.add(Collections.singletonList("业务ID"));
heads.add(Collections.singletonList("业务名称"));
heads.add(Collections.singletonList("业务状态"));
heads.add(Collections.singletonList("业务时间"));
return heads;
}
}
17.报表自定义异常 ReportException
package com.sxd.swapping.downloadAndUpload.xlsxDownloadAndUpload.exception;
import com.sxd.swapping.downloadAndUpload.xlsxDownloadAndUpload.result.DUResult;
/**
* 报表自定义业务报警
*/
public class ReportException extends RuntimeException {
private final String code;
public ReportException(String message) {
super(message);
this.code = DUResult.ERROR_CODE;
}
public ReportException(String code, String message) {
super(message);
this.code = code;
}
}
18.Controller公共响应体DUResult
package com.sxd.swapping.downloadAndUpload.xlsxDownloadAndUpload.result;
import lombok.Data;
/**
* DownloadAndUpload 公共返回对象
*/
@Data
public class DUResult {
public static final String SUCCESS_CODE = "0000";
public static final String ERROR_CODE = "9999";
/**
* 状态码
*/
private String code;
/**
* 状态码描述
*/
private String msg;
/**
* 响应结果
*/
private Object data;
private DUResult(String code, String msg) {
this(code, msg, null);
}
private DUResult(String code, String msg, Object data) {
this.code = code;
this.msg = msg;
this.data = data;
}
public static DUResult build(String code, String message) {
return new DUResult(code, message);
}
public static DUResult build(String code, String message, Object data) {
return new DUResult(code, message, data);
}
public static DUResult success() {
return build(SUCCESS_CODE, "处理成功");
}
public static DUResult success(String code, String message) {
return build(code, message);
}
public static DUResult success(Object data) {
return build(SUCCESS_CODE, "处理成功", data);
}
public static DUResult error() {
return build(ERROR_CODE, "处理失败");
}
public static DUResult error(String message) {
return error(ERROR_CODE, message);
}
public static DUResult error(String code, String message) {
return build(code, message);
}
}
19.工具类
时间格式工具 DateTimeHelper
package com.sxd.swapping.utils.dateTime;
import java.time.DateTimeException;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
public class DateTimeHelper {
public static final String PATTERN_1 = "yyyy-MM-dd HH:mm:ss";
public static final String PATTERN_2 = "yyyyMMddHHmmssSSS";
/**
* 时间类型 转 字符串
* @param localDateTime
* @return
*/
public static String getDateTimeStr(LocalDateTime localDateTime){
if (localDateTime == null) {
return null;
}
try {
DateTimeFormatter format = DateTimeFormatter.ofPattern(PATTERN_1);
return localDateTime.format(format);
} catch (DateTimeException ex) {
ex.printStackTrace();
return null;
}
}
}
序列号工具 SerialNumHelper
package com.sxd.swapping.utils.serialNum;
import com.sxd.swapping.utils.dateTime.DateTimeHelper;
import com.xiaoleilu.hutool.date.DateUtil;
import java.util.Date;
public class SerialNumHelper {
public synchronized static String generateRecordId() {
String daystr = DateUtil.format(new Date(), DateTimeHelper.PATTERN_2);
//TODO redis自增
return daystr;
}
}
20.application.properties配置文件
server.port=9666
spring.jackson.time-zone=GMT+8
#spring boot2.0 限制文件上传大小【spring boot默认1MB】
spring.servlet.multipart.max-file-size=90MB
spring.servlet.multipart.max-request-size=100MB
#datasource
spring.datasource.continue-on-error=false
#=========本地===========
spring.datasource.url=jdbc:mysql://localhost:3306/swapping?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8
#=======虚拟机==========
#spring.datasource.url=jdbc:mysql://192.168.92.130:3306/swapping?useSSL=false&useUnicode=true&characterEncoding=UTF-8
spring.datasource.username=root
spring.datasource.password=mynewpassword123
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
#druid 下面为连接池的补充设置，应用到上面所有数据源中
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
# 初始化大小，最小，最大
spring.datasource.initialSize=5
spring.datasource.minIdle=5
spring.datasource.maxActive=20
# 配置获取连接等待超时的时间
spring.datasource.maxWait=60000
# 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
spring.datasource.timeBetweenEvictionRunsMillis=60000
# 配置一个连接在池中最小生存的时间，单位是毫秒
spring.datasource.minEvictableIdleTimeMillis=300000
spring.datasource.validationQuery=SELECT 'x'
spring.datasource.testWhileIdle=true
spring.datasource.testOnBorrow=false
spring.datasource.testOnReturn=false
# 打开PSCache，并且指定每个连接上PSCache的大小
spring.datasource.poolPreparedStatements=true
spring.datasource.maxPoolPreparedStatementPerConnectionSize=20
spring.datasource.maxOpenPreparedStatements=20
# 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
spring.datasource.filters=stat,wall,log4j
# 通过connectProperties属性来打开mergeSql功能；慢SQL记录
spring.datasource.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
# 合并多个DruidDataSource的监控数据
#spring.datasource.useGlobalDataSourceStat=true
#mybatis相关配置 参考地址：https://mybatis.org/mybatis-3/zh/index.html
#mybatis映射文件的位置
mybatis.mapper-locations=classpath:mapper/*.xml
#mybatis指定entity位置
mybatis.type-aliases-package=com.sxd.swapping.mybatis.pojo
#mybatis展示sql语句执行
logging.level.com.sxd.swapping=debug
#允许 JDBC 支持自动生成主键，需要数据库驱动支持。如果设置为 true，将强制使用自动生成主键。尽管一些数据库驱动不支持此特性，但仍可正常工作
mybatis.configuration.use-generated-keys=true
#是否开启驼峰命名自动映射，即从经典数据库列名 A_COLUMN 映射到经典 Java 属性名 aColumn
mybatis.configuration.map-underscore-to-camel-case=true
#pagehelper mybatis分页插件
pagehelper.helperDialect=mysql
pagehelper.reasonable=true
pagehelper.supportMethodsArguments=true
pagehelper.params=count=countSql
pagehelper.returnPageInfo=check
#jpa相关配置
spring.jpa.database=mysql
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=update
spring.jpa.database-platform=org.hibernate.dialect.MySQL55Dialect
#redis配置
# Redis数据库索引（默认为0）
spring.redis.database=0
# Redis服务器地址
#======本地=======
spring.redis.host=localhost
#=======虚拟机=======
#spring.redis.host=192.168.92.130
# Redis服务器连接端口
spring.redis.port=6379
# Redis服务器连接密码（默认为空）
spring.redis.password=398023
# 连接池最大连接数（使用负值表示没有限制）
spring.redis.jedis.pool.max-active=8
# 连接池最大阻塞等待时间（使用负值表示没有限制）
spring.redis.jedi.pool.max-wait=-1
# 连接池中的最大空闲连接
spring.redis.jedi.pool.max-idle=8
# 连接池中的最小空闲连接
spring.redis.jedi.pool.min-idle=0
# 连接超时时间（毫秒）
spring.redis.jedi.timeout=0
#elasticsearch相关配置
#es的cluster集群名称可以查看服务器安装的集群名称 curl http://192.168.92.130:9200 获取到集群名称
spring.data.elasticsearch.cluster-name=docker-cluster
#注意端口为9300 9300 是 Java 客户端的端口,支持集群之间的通信。9200 是支持 Restful HTTP 的接口
spring.data.elasticsearch.cluster-nodes=192.168.92.130:9300
#logback对接logstash的日志配置文件
logging.config=classpath:logback-spring.xml
#线程池配置
thread.pool.core.size=10
thread.pool.max.size=10
thread.pool.queue.capacity=10000
thread.pool.alive.seconds=1000
请求实时下载：
http://localhost:9666/xlsx/downloadAndUpload/rtDownload
生成报表结果展示：
