【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-->
View Code

 

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);
        }
    }

}
View Code

 

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);
            }
        }
    }
}
View Code

 

 

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!"));
    }
}
View Code

 

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!"));
    }

}
View Code

 

 

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);
    }
}
View Code

 

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);
}
View Code

 

 

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());
                }
            });
        }

    }
}
View Code

 

 

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);
    }
}
View Code

 

 

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);
}
View Code

 

 

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;

}
View Code

 

 

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;



}
View Code

 

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;

}
View Code

 

 

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);
}
View Code

 

<?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>
View Code

 

 

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;

}
View Code

 

 

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;


}
View Code

 

 

 

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);
            }
        }
    }
}
View Code

 


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);
    }
}
View Code

 

 

 

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);
        }
    }

}
View Code

 

 

 

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) {

    }
}
View Code

 

 

 

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;
    }



}
View Code

 

 

 

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;
    }

}
View 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);
    }
}
View Code

 

 

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;
        }
    }
}
View Code

 

 

序列号工具 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;
    }
}
View Code

 

 

 

 

 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
View Code

 

 

 

请求实时下载:

http://localhost:9666/xlsx/downloadAndUpload/rtDownload

 

 

 

生成报表结果展示:

 

posted @ 2021-05-28 18:21  Angel挤一挤  阅读(436)  评论(0编辑  收藏  举报