easyPOI导出

文档：http://doc.wupaas.com/docs/easypoi/easypoi-1c0u6ksp2r091

1.依赖

        <!-- easypoi的支持 -->
        <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>

2.实体类

注解： @Excel(name = "字典id")

name值即为excel标题行

3.controller

@GetMapping("/export/test/{id}")
public ResponseEntity<byte[]> exportProtocol(@PathVariable("id") Integer id, HttpServletRequest request, HttpServletResponse response){
    return testService.export(id,request,response);
}

4.service

public ResponseEntity<byte[]> exportProtocol(Integer protocolId, HttpServletRequest request, HttpServletResponse response) {
    //准备参数集合
    List<A> datas1 = new ArrayList<>();
    List<B> datas3 = new ArrayList<>();
    List<Map<String,Object>> sheetsList = new ArrayList<>();
    //sheet1
    sheetsList.add(makeSheet("sheet页名称1", A.class, datas1));
    //sheet2
    sheetsList.add(makeSheet("sheet页名称2", B.class, datas2));
    //工具类获取Workbook对象
    Workbook workbook = ExcelExportUtil.exportExcel(sheetsList, ExcelType.HSSF);
    ByteArrayOutputStream out = new ByteArrayOutputStream();
    try {
        workbook.write(out);
        String fileName = DownloadUtil.processFileName("测试文件导出-文件名.xls",request,response);
        return DownloadUtil.downloadFile(out,fileName);
    }catch (Exception e){
        e.printStackTrace();
    }
    return null;
}

    /**
     * 封装sheet
     * @param sheetName sheet名称
     * @param pojoClass 实体类
     * @param datas 数据
     * @param <T>
     * @return
     */
    public static <T> Map<String, Object> makeSheet(String sheetName, Class<T> pojoClass, List<T> datas) {
        ExportParams sheet = new ExportParams();
        sheet.setSheetName(sheetName);
        Map<String, Object> map = new HashMap<>();
        map.put("title", sheet);
        map.put("entity", pojoClass);
        map.put("data", datas);
        return map;
    }

5.工具类

package com.test.utils;

import lombok.SneakyThrows;
import org.apache.commons.lang.StringUtils;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.util.FileCopyUtils;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;

/**
 * Mvc 导出文件
 */
public class DownloadUtil {
    private DownloadUtil() {}

    @SneakyThrows
    public static ResponseEntity<byte[]> downloadFile(File file, String fileName) {
        HttpHeaders headers = new HttpHeaders();
        //通知浏览器以attachment（下载方式）打开
        headers.setContentDispositionFormData("attachment",
                new String(fileName.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1));
        //application/octet-stream ： 二进制流数据（最常见的文件下载）。
        headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
        return new ResponseEntity<>(FileCopyUtils.copyToByteArray(file),
                headers, HttpStatus.CREATED);
    }

    @SneakyThrows
    public static ResponseEntity<byte[]> downloadFile(ByteArrayOutputStream out, String fileName) {
        HttpHeaders headers = new HttpHeaders();
        //通知浏览器以attachment（下载方式）打开
        headers.setContentDispositionFormData("attachment",
                new String(fileName.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1));
        //application/octet-stream ： 二进制流数据（最常见的文件下载）。
        headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
        return new ResponseEntity<>(out.toByteArray(),
                headers, HttpStatus.CREATED);
    }


    public static String processFileName(String fileName, HttpServletRequest request, HttpServletResponse response) {
        String userAgent = request.getHeader("USER-AGENT");
        try {
            if (StringUtils.contains(userAgent, "MSIE")) {
                //IE浏览器
                fileName = URLEncoder.encode(fileName, "UTF8");
            } else if (StringUtils.contains(userAgent, "Mozilla")) {
                //google,火狐浏览器
                fileName = new String(fileName.getBytes(), "ISO8859-1");
            } else if(StringUtils.contains(fileName,"Postman")){
                fileName = new String(fileName.getBytes(),"ISO8859-1");
            } else {
                fileName = URLEncoder.encode(fileName, "UTF8");
                //其他浏览器
            }
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        return fileName;
    }
}

6.数据脱敏

数据脱敏分为两种方法,一种是简单的,在注解或者ExcelExportEntity设置属性既可以
规则如下

• 规则1: 采用保留头和尾的方式,中间数据加星号

如:  身份证  6_4 则保留 370101********1234
手机号   3_4 则保留 131****1234

• 规则2: 采用确定隐藏字段的进行隐藏,优先保留头

如: 姓名   1,3 表示最大隐藏3位,最小一位
           李 -->  *
           李三 --> 李*
           张全蛋  --> 张*蛋
           李张全蛋 --> 李**蛋
           尼古拉斯.李张全蛋 -> 尼古拉***张全蛋

• 规则3: 特殊符号后保留

如: 邮箱    1~@ 表示只保留第一位和@之后的字段
        afterturn@wupaas.com -> a********@wupaas.com

复杂版本请使用接口 {@link cn.afterturn.easypoi.handler.inter.IExcelDataHandler}
即在数据接口自己处理脱敏规则,系统不单独处理

常用脱敏规则如下,在注解使用比较简单
@Excel(name = "姓名", desensitizationRule = "1,6")
private String name;
@Excel(name = "身份证", desensitizationRule = "6_4")
private String card;
@Excel(name = "手机号", desensitizationRule = "3_4")
private String phone;
@Excel(name = "邮箱", desensitizationRule = "3~@")
private String email;

得到的效果：