[EasyExcel][IO]报表+Excel二进制文件流下载

Author：唐宋丶
发布时间：October 12, 2022
421 views
No comments
8655 words
Categories：工具类开发工具插件

## 前言

大数据需要风控测的已审核数据进行模型训练，以xlsx的文件形式给到，考虑到首次全量历史有15w且后续也要频繁给到数据，且没有前端支持。因此决定用[EasyExcel](https://easyexcel.opensource.alibaba.com/docs/current/)生成文件再下载的形式做成通用模板，没想到踩了一堆坑。

## 引入依赖

项目结构如下，实体类需要用到注解，因此需要在model层引入依赖

```java
|client
|--dao
|--client
|----model

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.1.1</version>
</dependency>
```

## Excel报表生成

**报表字段**

```java
@Data
public class FastScreenReportVo {

@ExcelProperty("用户名")
    private String accountUuid;

@ExcelProperty("内容")
    private String content;

@ExcelProperty("上下文内容")
    private String context;
}
```

```java
/**
 * 快筛报表任务
 */
@Component
public class FastScreenReportTask {

private final Logger logger = LoggerFactory.getLogger(getClass());

@Autowired
    private FastScreenDao fastScreenDao;
    @Autowired
    private FastScreenHitDao fastScreenHitDao;

private final static Integer SEARCH_FREQUENCY = 200;
    private final static String REPORT_PATH = "/usr/local/";

@Scheduled(cron = "0 50 17 12 10 ? ")
    public void fastScreenReport() {
        logger.info("快筛报表,开始执行");

// 线上20w不到，只取三个字段，全都拉到内存跑一次就够了
        Map<Long, String> modelMap = initModelMap();
        Map<String, List<FastScreenReportVo>> sheetMap = initSheetMap();
        int count = fastScreenDao.count();
        long start = 1;
        long end = start + SEARCH_FREQUENCY - 1;

logger.info("快筛报表,count={}", count);
        do {
            Map<Long, String> idStatusMap = fastScreenDao.getIdStatusMap(start, end);

start = end + 1;
            end = start + SEARCH_FREQUENCY - 1;
            if (idStatusMap.isEmpty()) {
                continue;
            }

List<FastScreenHit> hitList = fastScreenHitDao.listByIdSet(idStatusMap.keySet())
                    .stream()
                    .filter(e -> modelMap.containsKey(e.getModelId()))
                    .collect(Collectors.toList());
            for (FastScreenHit hit : hitList) {
                FastScreenReportVo vo = new FastScreenReportVo();
                BeanUtils.copyProperties(hit, vo);
                sheetMap.get(modelMap.get(hit.getModelId()) + "-" + idStatusMap.get(hit.getBizId())).add(vo);
            }
        } while (start < count);

String fileName = REPORT_PATH + "快筛报表_" + DateUtil.format(new Date(), "yyyyMMdd") + ".xlsx";
        ExcelWriter excelWriter = EasyExcel.write(fileName, FastScreenReportVo.class).build();
        int sheetIndex = 0;
        for (Map.Entry<String, List<FastScreenReportVo>> map : sheetMap.entrySet()) {
            WriteSheet writeSheet = EasyExcel
                    .writerSheet(sheetIndex++, map.getKey())
                    .build();
            excelWriter.write(map.getValue(), writeSheet);
        }
        excelWriter.finish();

logger.info("快筛报表,结束执行");
    }

public static Map<Long, String> initModelMap() {
        Map<Long, String> modelMap = Maps.newLinkedHashMap();
        modelMap.put(13L, "数字组合");
        modelMap.put(14L, "中文数字组合");
        modelMap.put(15L, "表情数字组合");
        modelMap.put(17L, "金融广告");
        modelMap.put(18L, "通用广告");
        modelMap.put(19L, "色情广告");
        modelMap.put(20L, "网赚兼职广告");
        modelMap.put(26L, "杀猪盘");
        modelMap.put(27L, "裸聊诈骗");
        modelMap.put(28L, "仙人跳");
        modelMap.put(29L, "充电宝诈骗");
        modelMap.put(30L, "广告挖人");
        modelMap.put(32L, "刷单诈骗");
        modelMap.put(33L, "网络陪聊");
        modelMap.put(36L, "裸聊诈骗（正则匹配）");
        modelMap.put(139L, "网络乞丐");
        modelMap.put(140L, "引导三方（正则匹配）");
        return modelMap;
    }

public static Map<String, List<FastScreenReportVo>> initSheetMap() {
        Map<Long, String> modelMap = initModelMap();
        Map<String, List<FastScreenReportVo>> sheetMap = Maps.newLinkedHashMap();
        for (String desc : modelMap.values()) {
            sheetMap.put(desc + "-处罚", Lists.newArrayList());
            sheetMap.put(desc + "-忽略", Lists.newArrayList());
        }
        return sheetMap;
    }
}
```

由于每个业务的报表逻辑和字段都不相同，这块可以不做抽离，可以考虑各自业务的报表都做成一个**异步接口**，配置在在后台系统设置中，也方便后续前端接入日常报表。
生成Excel的部分可以抽离出来做成工具类，存放在同一路径下。

**注意点：**

1. 引入EasyExcel就是为了实现后端快速的完成整个流程（获取数据->生成Excel->存储磁盘->磁盘读取->二进制文件流直接转发），节省了OSS文件系统存储的引入、统一报表队列的回调复杂写法、前端的筛选报表（日常可以用，历史太大了返回给前端处理会出现SOA超时）
   对应的牺牲点就是需要跟三方接口回调一样配置一个API网关系统（运维添加）和网关API接口（开发添加）
2. 由于新系统都是采用 `jar`包的方式，因此不能存放到项目中，而老系统或者本地都是war包的形式，因此可以直接放在资源目录 `resources`底下（获取的时候不要从  `target`获取，否则需要修改pom文件避免压缩Excel导致文件损坏）
3. `/usr/local`每次重新部署项目都会清空，不需要维护删除，而 `/data/html/`存放项目jar包的则不会
4. 基础的Excel写入看官方文档就够用了，多个Sheet写入时记得带上 `ExcelWriter.finish()`否则数据没法刷到Excel（官方文档示例漏了这步）
   如果遇到空指针后字体报错可以参考GitHub官网上的Issue解决
5. 本次历史数据主表9k、子20w条，主表一次拿200（可以再调大一点），命中数据量10w条3个字段，考虑到内存占用不大因此直接拉到内存中存储，最终xlsx大小5.5M，在Mac上秒开，总耗时6s
   若是百万数据量且字段较多可以考虑批量多次追加写入，EasyExcel支持追加（更新）

## Excel报表下载

### 错误示范

**官方下载示例**

```java
@GetMapping("download")
public void download(HttpServletResponse response) throws IOException {
    // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    response.setCharacterEncoding("utf-8");
    // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
    String fileName = URLEncoder.encode("测试", "UTF-8").replaceAll("\\+", "%20");
    response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");

EasyExcel.write(response.getOutputStream(), DownloadData.class).sheet("模板").doWrite(data());
}
```

EasyExcel的**下载**方法仅支持对实体类数据进行 `doWrite()`，无法根据已生成的Excel创建 `InputStream`直接写入到 `HttpServletResponse`的 `OutputStream`中，这种模式只适合小数据量的报表，生产上跑大量数据报表网关直接就给你超时报错了
强行满足只能通过读操作获取到实体类数据，而**读操作**是通过读取监听（每条、全部），进行触发回调接口。但是报表都生成了再读出来返回未免有点太蠢了- -

**缓冲流文件传输**

```java
/**
 * 下载导入excel模板
 * @throws IOException
 */
@RequestMapping(value = "downModel")
public void download( ) throws IOException {
    ServletRequestAttributes requestAttributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
    HttpServletResponse response = requestAttributes.getResponse();

String filename = "导入模板.xlsx";

// 设置信息给客户端不解析
    String type = new MimetypesFileTypeMap().getContentType(filename);
    // 设置contenttype，即告诉客户端所发送的数据属于什么类型
    response.setHeader("Content-type",type);
    // 设置编码
    String hehe = new String(filename.getBytes("utf-8"), "iso-8859-1");
    // 设置扩展头，当Content-Type 的类型为要下载的类型时 , 这个信息头会告诉浏览器这个文件的名字和类型。
    response.setHeader("Content-Disposition", "attachment;filename=" + hehe);
    FileUtil.download(filename, response);
}

public static void download(String filename, HttpServletResponse res) throws IOException {
    // 发送给客户端的数据
    OutputStream outputStream = res.getOutputStream();
    byte[] buff = new byte[1024];
    BufferedInputStream bis = null;
    // 读取filename
    bis = new BufferedInputStream(new FileInputStream(new File("./file/" + filename)));
    int i = bis.read(buff);
    while (i != -1) {
        outputStream.write(buff, 0, buff.length);
        outputStream.flush();
        i = bis.read(buff);
    }

bis.close();
    outputStream.close();
}
```

其它写法忘了可以看原先的[IO](http://www.tangsong.fun/index.php/IO.html)，试了几种方式都是乱码or文件损坏，设置了各种编码UTF-8、IOS、GBK等也无效，断定判断大概率是Response的编码设置失败或网关解析的时候用了其它编码方式

### 正确示范

```java
@Controller
@RequestMapping(value = "/api", params = "service=report")
public class ReportController {

private final Logger logger = LoggerFactory.getLogger(getClass());

@RequestMapping(params = "method=fastScreen")
    private ResponseEntity<byte[]> fastScreen(@RequestParam("fileName") String fileName) throws IOException {
        String pathUrl = "/usr/local/" + fileName + ".xlsx";
        File file = new File(pathUrl);

HttpHeaders httpHeaders = new HttpHeaders();
        httpHeaders.set("Content-Disposition", "attchment; filename=" + URLEncoder.encode(pathUrl.substring(pathUrl.lastIndexOf("/")), StandardCharsets.UTF_8.name()));
        httpHeaders.setContentType(MediaType.APPLICATION_OCTET_STREAM);
        return ResponseEntity
                .ok()
                .headers(httpHeaders)
                .contentLength(file.length())
                .body(FileUtils.readFileToByteArray(file));
    }
}
```

**注意点：**

1. 需要把 `@RestController`改为 `@Controller`返回 `ResponseEntity<byte[]>`对象，同时参数要把框架上的 `RequestParam`改为传统的 `@RequestParam("xxx")`方式
2. `Content-Type=application/octet-stream`一般和 `Content-Disposition=attchment; filename=xxx.xxx`一起使用，告诉浏览器这个是二进制文件需并触发下载行为
3. apach的 `FileUtils.readFileToByteArray(file)`会将文件转为FileInputStream再转为字节数组，最大为maxInt（约2G），并集成了自动关闭
4. 由于网关转发默认为SOA处理，所以需要配置API接口并且方式为**直接转发**对外暴露，通过 `https://gw-w.jiaoliuqu.com/system/v1/service/method?env=1&param=xxx`访问，`env=1`用来标记灰度

Last modification：October 13, 2022

喵ฅฅ

[EasyExcel][IO]报表+Excel二进制文件流下载

唐宋丶 • 2022 年 10 月 12 日

## 前言

## 引入依赖

项目结构如下，实体类需要用到注解，因此需要在model层引入依赖

```java
|client
|--dao
|--client
|----model

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.1.1</version>
</dependency>
```

## Excel报表生成

**报表字段**

```java
@Data
public class FastScreenReportVo {

@ExcelProperty("用户名")
    private String accountUuid;

@ExcelProperty("内容")
    private String content;

@ExcelProperty("上下文内容")
    private String context;
}
```

```java
/**
 * 快筛报表任务
 */
@Component
public class FastScreenReportTask {

private final Logger logger = LoggerFactory.getLogger(getClass());

@Autowired
    private FastScreenDao fastScreenDao;
    @Autowired
    private FastScreenHitDao fastScreenHitDao;

private final static Integer SEARCH_FREQUENCY = 200;
    private final static String REPORT_PATH = "/usr/local/";

@Scheduled(cron = "0 50 17 12 10 ? ")
    public void fastScreenReport() {
        logger.info("快筛报表,开始执行");

logger.info("快筛报表,count={}", count);
        do {
            Map<Long, String> idStatusMap = fastScreenDao.getIdStatusMap(start, end);

start = end + 1;
            end = start + SEARCH_FREQUENCY - 1;
            if (idStatusMap.isEmpty()) {
                continue;
            }

logger.info("快筛报表,结束执行");
    }

**注意点：**

## Excel报表下载

### 错误示范

**官方下载示例**

EasyExcel.write(response.getOutputStream(), DownloadData.class).sheet("模板").doWrite(data());
}
```

**缓冲流文件传输**

String filename = "导入模板.xlsx";

bis.close();
    outputStream.close();
}
```

### 正确示范

```java
@Controller
@RequestMapping(value = "/api", params = "service=report")
public class ReportController {

private final Logger logger = LoggerFactory.getLogger(getClass());

**注意点：**

Leave a Comment Cancel reply

[EasyExcel][IO]报表+Excel二进制文件流下载