进阶篇 - 19. 操作Excel文件 - 《后端技术知识库》

19.1 概述
19.2 解析上传的Excel文件
19.3 封装数据为Excel后下载
19.4 填充Excel模板
19.5 更复杂的操作

19.1 概述

虽然Apache poi是目前使用最多的用来做Excel解析的框架，但这个框架并不那么完美。大部分使用 POI 都是使用他的userModel模式。userModel的好处是上手容易使用简单，随便拷贝个代码跑一下，剩下就是写业务转换了，虽然转换也要写上百行代码，相对比较好理解。然而 userModel 模式最大的问题是在于非常大的内存消耗，一个几兆的文件解析要用掉上百兆的内存。现在很多应用采用这种模式，之所以还正常在跑一定是并发不大，并发上来后一定会OOM或者频繁的full gc。

POI还有SAX模式，但它相对比较复杂：excel有03和07两种版本，两个版本数据存储方式截然不同，sax解析方式也各不一样，他的学习成本和使用成本都很高。由于代码复杂，后续维护成本巨大。

为此我们选择更简单、高效、节省内存的开源项目Ali EasyExcel在后台操作Excel文件。

下面是需要添加的依赖

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.0.4</version>
        </dependency>

本章的代码在EasyExcel官方示例的基础上做了简单修改。更多的内容可去查阅官方文档。

19.2 解析上传的Excel文件

19.2.1 设计目标

本节的目标是在后端解析用户上传过来的如下图格式的Excel文件

把每行数据转换成一个数据对象，然后保存在数据库中。

19.2.2 数据对象定义

package com.longser.union.cloud.data.model;
import lombok.Data;
import java.util.Date;
@Data
public class ExcelDemoData {
    private String string;
    private Date date;
    private Double doubleData;
}

19.2.3 最简单的解析方法

下面是自 EasyExcel 3.0 之后的一种不需要监听器的简单方法

package com.longser.union.cloud.controller;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.read.listener.PageReadListener;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.longser.union.cloud.data.model.ExcelDemoData;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import java.io.IOException;
import java.util.concurrent.atomic.AtomicInteger;
@RestController
@RequestMapping("/api/excel")
public class ExcelController {
    @PostMapping("/read/simple")
    public String readSimple(MultipartFile file) {
        ObjectMapper objectMapper = new ObjectMapper();
        // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
        // 这里每次会读取3000条数据 然后返回过来 直接调用使用数据就行
        AtomicInteger rows = new AtomicInteger();
        try {
            EasyExcel.read(file.getInputStream(), ExcelDemoData.class,
                    new PageReadListener<ExcelDemoData>(dataList -> {
                rows.set(dataList.size());
                for (ExcelDemoData excelDemoData : dataList) {
                    try {
                        System.out.printf("读取到一条数据{}%s%n", objectMapper.writeValueAsString(excelDemoData));
                    } catch (JsonProcessingException e) {
                        e.printStackTrace();
                    }
                }
            })).sheet().doRead();
            return "处理了 " + rows.toString() + " 条数据";
        } catch (IOException e) {
            e.printStackTrace();
            return "出错了";
        }
    }
}

19.2.4 使用监听器的的解析方法

使用监听器解析的好处是可以批量写入数据以节省资源。

1. 模拟保存的DAO类

下面类的方法被调用来模拟保存数据（在本例中它什么都不做）

package com.longser.union.cloud.data.mapper;

import com.longser.union.cloud.data.model.ExcelDemoData;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public class ExcelDemoDataDAO {

    public void save(List<ExcelDemoData> list) {
        // 如果是mybatis,尽量别直接调用多次insert,自己写一个mapper里面新增一个方法batchInsert,所有数据一次性插入
    }
}

2. 解析过程监听器

下面的类监听数据解析过程，数据累积到一定数量之后再执行保存操作

package com.longser.union.cloud.service;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.util.ListUtils;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.longser.union.cloud.data.mapper.ExcelDemoDataDAO;
import com.longser.union.cloud.data.model.ExcelDemoData;
import lombok.SneakyThrows;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.List;

public class ExcelDataUploadListener implements ReadListener<ExcelDemoData> {
    private static final Logger logger =
            LoggerFactory.getLogger(ExcelDataUploadListener.class);
    /**
     * 每隔5条存储数据库，实际使用中可以100条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 5;
    /**
     * 缓存的数据
     */
    private List<ExcelDemoData> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
    /**
     * 假设这个是一个DAO，当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。
     */
    private final ExcelDemoDataDAO excelDemoDataDAO;

    public ExcelDataUploadListener() {
        // 这里是demo，所以随便new一个。实际使用如果到了spring,请使用下面的有参构造函数
        excelDemoDataDAO = new ExcelDemoDataDAO();
    }

    /**
     * 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
     */
    public ExcelDataUploadListener(ExcelDemoDataDAO excelDemoDataDAO) {
        this.excelDemoDataDAO = excelDemoDataDAO;
    }

    /**
     * 这个每一条数据解析都会来调用
     *
     * @param excelDemoData    one row value. Is is same as {@link AnalysisContext#readRowHolder()}
     */
    @SneakyThrows
    @Override
    public void invoke(ExcelDemoData excelDemoData, AnalysisContext context) {
        ObjectMapper objectMapper = new ObjectMapper();

        logger.info("解析到一条数据:{}", objectMapper.writeValueAsString(excelDemoData));
        cachedDataList.add(excelDemoData);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (cachedDataList.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
        }
    }

    /**
     * 所有数据解析完成了 都会来调用
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();
        logger.info("所有数据解析完成！");
    }

    /**
     * 加上存储数据库
     */
    private void saveData() {
        if(cachedDataList.size() > 0) {
            logger.info("{}条数据，开始存储数据库！", cachedDataList.size());
            excelDemoDataDAO.save(cachedDataList);
            logger.info("存储数据库成功！");
        }
    }
}

3. 使用监听器解析数据

下面是使用监听器解析数据的方法

    @PostMapping("/read/listener")
    public  RestfulResult<String> upload(MultipartFile file)  {
        // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
        try {
            EasyExcel.read(file.getInputStream(), ExcelDemoData.class, new ExcelDataUploadListener())
                    .sheet()
                    .doRead();
            return RestfulResult.success("上传解析成功");
        } catch (IOException e) {
            e.printStackTrace();

            return RestfulResult.fail(500,"IOException");
        }
    }

将设计目标中描述的Excel文件用Postman提交给后端，在Postman应该得到如下的结果

在IDEA控制台窗口中应该可以看到如下信息。

19.3 封装数据为Excel后下载

19.3.1 设计目标

本节的目标是在后端生成一个如下的图的Excel供下载

19.3.2 数据对象定义

下面是后端用来准备存入Excel文件中的数据的对象定义

package com.longser.union.cloud.data.model;

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

import java.util.Date;

@Data
public class ExcelDemoDataDownload {
    @ExcelProperty("字符串标题")
    private String string;
    @ExcelProperty("日期标题")
    private Date date;
    @ExcelProperty("数字标题")
    private Double doubleData;
}

19.3.3 生成模拟源数据

把下面的方法放在ExcelController类中用来生成模拟的数据

    private List<ExcelDemoDataDownload> getDataList() {
        double Pi = 3.14159;
        double Euler = 2.71828;
        List<ExcelDemoDataDownload> list = new ArrayList<>();

        for (int i = 0; i < 10; i++) {
            ExcelDemoDataDownload data = new ExcelDemoDataDownload();
            data.setString("字符串" + i);
            data.setDate(new Date());
            if(Math.floorMod(i, 2) == 0) {
                data.setDoubleData(Pi);
            } else {
                data.setDoubleData(Euler);
            }
            list.add(data);
        }

        return list;
    }

19.3.4 存入Excel后下载

接下来我们把模拟数据保存到Excel后给前端下载保存。

方法一

  @GetMapping("/download/type1")
  public void download(HttpServletResponse response)  {
      response.setCharacterEncoding("utf-8");
      response.setContentType("application/vnd.ms-excel");

      try {
          String fileName = URLEncoder.encode("测试", "UTF-8").replaceAll("\\+", "%20");
          response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");

          EasyExcel.write(response.getOutputStream(), ExcelDemoDataDownload.class)
                  .sheet("数据")
                  .doWrite(getDataList());
      } catch (Exception e) {
          e.printStackTrace();
      }
  }

方法二

  @GetMapping("/download/type2")
  public void getHelloExcel(HttpServletResponse response) {
      response.setHeader("content-Type", "application/vnd.ms-excel");
      response.setHeader("Content-Disposition", "attachment;filename=data.xlsx");

      ExcelWriter excelWriter = null;
      try {
          WriteSheet writeSheet = EasyExcel.writerSheet("数据").build();

          excelWriter = EasyExcel.write(response.getOutputStream(), ExcelDemoDataDownload.class)
                  .build()
                  .write(getDataList(), writeSheet);
      } catch (IOException e) {
          e.printStackTrace();
      } finally {
          // 千万别忘记finish 会帮忙关闭流
          if (excelWriter != null) {
              excelWriter.finish();
          }
      }
  }

在浏览器的地址栏分别输入 http://localhost:8088/api/excel/download/type1 和 http://localhost:8088/api/excel/download/type2，可下载得到生成好的Excel文件。

19.3.5 发生异常后友好返回

下面是一个发生异常后更加友好返回的范例

  @GetMapping("/download/exception")
  public void getException(HttpServletResponse response) throws IOException {
      response.setCharacterEncoding("utf-8");
      response.setContentType("application/vnd.ms-excel");

      try {
          String fileName = URLEncoder.encode("测试", "UTF-8").replaceAll("\\+", "%20");
          response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");

          //下面这段是人为模拟抛出异常
          if(true) {
              throw new Exception("Exception by Author");
          }

          // 这里需要设置不关闭流
          EasyExcel.write(response.getOutputStream(), ExcelDemoDataDownload.class)
                  .autoCloseStream(Boolean.FALSE)
                  .sheet("模板")
                  .doWrite(getDataList());
      } catch (Exception e) {
          e.printStackTrace();

          response.reset();
          response.setContentType("application/json");
          response.setCharacterEncoding("utf-8");

          RestfulResult result = RestfulResult.fail(500,"下载文件失败：" + e.getMessage());
          response.getWriter().println((new ObjectMapper()).writeValueAsString(result));
      }
  }

19.4 填充Excel模板

19.4.1 设计目标

本节讨论在EasyExcel模板中填充后生成供下载的Excel文件的方法。包括简单的单条数据填充和数据列表填充两种场景。下面分别是两个模板的样式：

注意：第二个模板设置了较多的格式。

19.4.2 填充数据类

下面是填充数据类的定义 ```java package com.longser.union.cloud.data.model;

import lombok.Data;

@Data public class ExcelFillData { private String name; private double number; }

<a name="GBBUm"></a>
### 19.4.2 单数据简单填充
下面是实现简单填充Excel模板的代码示例。示例中根据type参数采用不同的编程方式来实现填充目标。当然这里是为了展示不同的写法，实际项目只需要选择一种方法即可。
```java
    @GetMapping("/fill/simple")
    public void simpleFill(@RequestParam(
            value = "type",
            defaultValue = "1",
            required = false) String type, HttpServletResponse response) throws IOException {
        response.setHeader("content-Type", "application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment;filename=fillResult.xlsx");

        // 模板注意 用{} 来表示你要用的变量 如果本来就有"{","}" 特殊字符 用"\{","\}"代替
        String templateFileName = "src/main/resources/static/st1.xlsx";

        int fillType = Integer.parseInt(type);
        //这里 会填充到第一个sheet， 然后文件流会自动关闭
        if(fillType == 1) {
            System.out.println("Fill in with method one");
            // 方案1 根据对象填充，
            ExcelFillData fillData = new ExcelFillData();
            fillData.setName("张三");
            fillData.setNumber(5.2);

            EasyExcel.write(response.getOutputStream())
                    .withTemplate(templateFileName)
                    .sheet()
                    .doFill(fillData);
        } else {
            System.out.println("Fill in with method two");
            // 方案2 根据Map填充
            Map<String, Object> map = new HashMap<String, Object>();
            map.put("name", "周润发");
            map.put("number", 5.2);
            EasyExcel.write(response.getOutputStream())
                    .withTemplate(templateFileName)
                    .sheet()
                    .doFill(map);
        }
    }

下图为下载得到的Excel文件内容

16.4.3 列表数据填充

先写一个准备数据的类

    private List<ExcelFillData> data() {
        List<ExcelFillData> list = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            ExcelFillData fillData = new ExcelFillData();
            list.add(fillData);
            fillData.setName("张" + (i+1));
            fillData.setNumber(5.2*(i+1));
        }
        return list;
    }

下面是把列表数据填充到Excel模板中的代码：

    @GetMapping("/fill/list")
    public void listFill(HttpServletResponse response) throws IOException {
        response.setHeader("content-Type", "application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment;filename=fillResult.xlsx");

        // 模板注意 用{} 来表示你要用的变量 如果本来就有"{","}" 特殊字符 用"\{","\}"代替
        // 填充list 的时候还要注意 模板中{.} 多了个点 表示list
        String templateFileName = "src/main/resources/static/st2.xlsx";

        // 方案1 一下子全部放到内存里面 并填充
        // 这里 会填充到第一个sheet， 然后文件流会自动关闭
        EasyExcel.write(response.getOutputStream())
                .withTemplate(templateFileName)
                .sheet()
                .doFill(data());
    }

下面是填充后生成Excel的结果

可见列表中新的单元格都继承了模板中设置的格式。

和简单填充近似，我们也可以换另外一种方法实现目标：

        // 方案2 分多次 填充 会使用文件缓存（省内存）
        ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream())
                .withTemplate(templateFileName)
                .build();
        WriteSheet writeSheet = EasyExcel.writerSheet().build();
        excelWriter.fill(data(), writeSheet);
        excelWriter.fill(data(), writeSheet);
        // 千万别忘记关闭流
        excelWriter.finish();

19.5 更复杂的操作

EasyExcel具有更复杂的读、写和模板填充功能，详细内容请参考下面两个链接：

EasyExcel官方文档和示例 https://www.yuque.com/easyexcel/doc/easyexcel
EasyExcel开放源码 https://github.com/alibaba/easyexcel

下面是本教程用到的两个模板文件：

版权说明：本文由北京朗思云网科技股份有限公司原创，向互联网开放全部内容但保留所有权力。