SpringBoot整合easyPOI

SpringBoot整合easyPOI 实现导出

• maven依赖

<!--easy poi start-->
		<dependency>
			<groupId>cn.afterturn</groupId>
			<artifactId>easypoi-base</artifactId>
			<version>3.2.0</version>
		</dependency>
		<dependency>
			<groupId>cn.afterturn</groupId>
			<artifactId>easypoi-web</artifactId>
			<version>3.2.0</version>
		</dependency>
		<dependency>
			<groupId>cn.afterturn</groupId>
			<artifactId>easypoi-annotation</artifactId>
			<version>3.2.0</version>
		</dependency>
<!--easy poi end-->

• Excel模板 （能与map自由转换，会更顺手）
• map转换工具类
• EasyPoi 官方教程

/**
 * 自查排名导出 Excel 模板
 * By CHENYB Date 2020-15-19
 */
@ExcelTarget( "poiTemplateOfProject" )
public class PoiTemplateOfRankingQuery implements Serializable {

    @Excel( name = "项目/单位名称",height = 7,width = 30,isImportField = "true_st")
    private String companyName;

    @Excel( name = "分析年份",height = 7,width = 12,isImportField = "true_st")
    private String yearStamp;

    @Excel( name = "总分",height = 7,width = 12,isImportField = "true_st")
    private Integer totalScore;

    @Excel( name = "信息系统（提交完整度得分）",height = 7,width = 12,isImportField = "true_st")
    private Integer normalCommitPercentScore;

    @Excel( name = "信息系统（提交内容得分）",height = 7,width = 12,isImportField = "true_st")
    private Integer normalCheckValueScore;

    @Excel( name = "信息系统（提交及时性得分）",height = 7,width = 12,isImportField = "true_st")
    private Integer normalCommitOntimeScore;

    @Excel( name = "工控系统（提交完整度得分）",height = 7,width = 12,isImportField = "true_st")
    private Integer icsCommitPercentScore;

    @Excel( name = "工控系统（提交内容得分）",height = 7,width = 12,isImportField = "true_st")
    private Integer icsCheckValueScore;

    @Excel( name = "工控系统（提交及时性得分）",height = 7,width = 12,isImportField = "true_st")
    private Integer icsCommitOntimeScore;

    @Excel( name = "远程高危漏洞（小计）",height = 7,width = 12,isImportField = "true_st")
    private Integer normalLoopholeScore;


    @Excel( name = "工控远程检查（小计）",height = 7,width = 12,isImportField = "true_st")
    private Integer icsLoopholeScore;


    public String getCompanyName() {
        return StringUtils.isBlank( companyName ) ? "-" : companyName;
    }

    public void setCompanyName(String companyName) {
        this.companyName = companyName;
    }

    public String getYearStamp() {
        return StringUtils.isBlank( yearStamp ) ? "-" : yearStamp;
    }

    public void setYearStamp(String yearStamp) {
        this.yearStamp = yearStamp;
    }

    public String getTotalScore() {
        return totalScore != null ? String.valueOf( totalScore ) : "-";
    }

    public void setTotalScore(Integer totalScore) {
        this.totalScore = totalScore;
    }


    public String getNormalCommitPercentScore() {
        return normalCommitPercentScore != null ? String.valueOf( normalCommitPercentScore ) : "-";
    }

    public void setNormalCommitPercentScore(Integer normalCommitPercentScore) {
        this.normalCommitPercentScore = normalCommitPercentScore;
    }

    public String getNormalCheckValueScore() {
        return normalCheckValueScore != null ? String.valueOf(normalCheckValueScore) : "-";
    }

    public void setNormalCheckValueScore(Integer normalCheckValueScore) {
        this.normalCheckValueScore = normalCheckValueScore;
    }

    public String getNormalCommitOntimeScore() {
        return normalCommitOntimeScore != null ? String.valueOf(normalCommitOntimeScore) : "-";
    }

    public void setNormalCommitOntimeScore(Integer normalCommitOntimeScore) {
        this.normalCommitOntimeScore = normalCommitOntimeScore;
    }


    public String getIcsCommitPercentScore() {
        return icsCommitPercentScore != null ? String.valueOf( icsCommitPercentScore ) : "-";
    }

    public void setIcsCommitPercentScore(Integer icsCommitPercentScore) {
        this.icsCommitPercentScore = icsCommitPercentScore;
    }

    public String getIcsCheckValueScore() {
        return icsCheckValueScore != null ? String.valueOf( icsCheckValueScore ) : "-";
    }

    public void setIcsCheckValueScore(Integer icsCheckValueScore) {
        this.icsCheckValueScore = icsCheckValueScore;
    }

    public String getIcsCommitOntimeScore() {
        return icsCommitOntimeScore != null ? String.valueOf(icsCommitOntimeScore) : "-";
    }

    public void setIcsCommitOntimeScore(Integer icsCommitOntimeScore) {
        this.icsCommitOntimeScore = icsCommitOntimeScore;
    }

    public String getNormalLoopholeScore() {
        return normalLoopholeScore != null ? String.valueOf(normalLoopholeScore) : "-";
    }

    public void setNormalLoopholeScore(Integer normalLoopholeScore) {
        this.normalLoopholeScore = normalLoopholeScore;
    }


    public String getIcsLoopholeScore() {
        return icsLoopholeScore != null ? String.valueOf(icsLoopholeScore) : "-";
    }

    public void setIcsLoopholeScore(Integer icsLoopholeScore) {
        this.icsLoopholeScore = icsLoopholeScore;
    }

•  封装为Workbook对象

List<PoiTemplateOfRankingQuery> ptofs = new ArrayList<>();
/**
将数据填充到实体类，并装进 ptofs 集合
**/

//PoiTemplateOfRankingQuery 实体类 ， ptofs 数据集合（实体类集合）
Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("标题名称","sheet名称"),
                PoiTemplateOfRankingQuery.class, ptofs);

•  方法一、Workbook直接实现页面下载，返回void即可

ServletOutputStream outputStream = response.getOutputStream();
        try {
            response.setContentType("application/octet-stream");
            response.setHeader("Content-Disposition", "attachment;filename="+URLEncoder.encode("文件.后缀", "UTF-8"));
            wb.write(outputStream);
        } catch (IOException e) {
            logger.error("[monitor][IO][表单功能]", e);
        }finally {
            outputStream.close();
        }

• 方法二、需要临时文件以便随时使用，Workbook在本地生成临时文件

//filePath 文件绝对路径
String filePath = FILE_DIR + File.separator + new Date( ).getTime()+"."+"xls";

FileOutputStream fos = new FileOutputStream( filePath );
        try {
            wb.write( fos );
            result = filePath;
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            fos.close();//记得关流
        }


//然后再将临时生成的文件下载下来就好， 记得写临时文件清理机制

• 直接返回ResponseEntity<byte[]>对象完成页面下载
• 文件名乱码处理
• 文件流处理工具类

//Controller 下载方法， 记得请求方式为GET
public ResponseEntity<byte[]> downloadSelfCheckRanking(
            HttpServletRequest request,
            HttpServletResponse response,
            @ApiParam(required = true, name = "userId", value = "userId") @RequestParam(name = "userId", required = true) String userId,
            @ApiParam(required = true, name = "filePath", value = "自查排名临时文件路径") @RequestParam(name = "filePath", required = true) String filePath
    ){

        if (userId.length() != 32)
            throw new MyException( ExceptionEnum.EXCEPTION_PARAMETER );

        String fileName = LocalDate.now().toString()+"-自查排名表格.xls";
        try {
            HttpHeaders headers = new HttpHeaders();
            headers.setContentDispositionFormData("attachment", encodeFileName(request,fileName));
            headers.setContentType( MediaType.APPLICATION_OCTET_STREAM);
            return new ResponseEntity<byte[]>(File2byte( filePath ), headers, HttpStatus.OK);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

//文件转换为byte[]流的方法
private static byte[] File2byte(String filePath) {
        byte[] buffer = null;
        try {
            File file = new File(filePath);
            FileInputStream fis = new FileInputStream(file);
            ByteArrayOutputStream bos = new ByteArrayOutputStream();
            byte[] b = new byte[1024];
            int n;
            while ((n = fis.read(b)) != -1) {
                bos.write(b, 0, n);
            }
            fis.close();
            bos.close();
            buffer = bos.toByteArray();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return buffer;
    }

//谷歌浏览器 处理文件名乱码问题
private String encodeFileName(HttpServletRequest request, String fileName) {
       
        String result = fileName;
        try {
            result = java.net.URLEncoder.encode(fileName, "UTF8");
        } catch (UnsupportedEncodingException e) {
        }
        return result;
    }

• 下载效果

 

随笔记录,方便学习

2020-05-27