导出导入开发场景-阿里云开发者社区

一、excel导出导入

1、框架

关于excel的操作，使用较多的框架或者工具有Apache POI、EasyPoi、EasyExcel 三个

1、Apache POI是Apache旗下的产品，api太多了

2、EasyExcel是阿里出的一款产品

文档地址：https://easyexcel.opensource.alibaba.com/

3、EasyPoi

地址：EasyPoi教程_V1.0

备用地址：http://www.wupaas.com/

2、EasyPoi使用

2.1、导入依赖

<dependency>
  <groupId>cn.afterturn</groupId>
  <artifactId>easypoi-spring-boot-starter</artifactId>
  <version>4.1.0</version>
</dependency>

或者以下依赖

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

2.2、导出示例

官网说明了Excel的导入导出是Easypoi的核心功能,基本也是围绕这个打造的,主要分为三种方式的处理

注解方式,注解变种方式
模板方式
Html方式

其中模板和Html目前只支持导出,因为支持Map.class其实导入应该是怎样都支持的

模板标签语法

看一个常见的到处模板--专项支出用款申请书

代码如何写？这里介绍四种使用示例

方式1、使用定义好的模板

定义好的excel模板放在项目resource下

使用模板将Map<String, Object>数据导出为excel示例(需要模板)

// 模板导出---Map组装数据
// 注:.xls的模板可以导出.xls文件，也可以导出xlsx的文件;同样的, .xlsx的模板可以导出.xls文件，也可以导出xlsx的文件;
@Test
public void templateExportExcelByMap() throws IOException {
  // 加载模板
  TemplateExportParams params = new TemplateExportParams("templates/templateMap.xls");
  Map<String, Object> map = new HashMap<>(16);
  map.put("title", "全亚洲,最帅气人员名单");
  map.put("date", "2018-12-05");
  map.put("interviewer", "JustryDeng");
  List<Map<String, Object>> list = new ArrayList<>(16);
  Map<String, Object> tempMap;
  for (int i = 0; i < 5; i++) {
    tempMap = new HashMap<>();
    tempMap.put("name", "邓沙利文");
    tempMap.put("gender", new Random().nextInt(2) == 0 ? "男" : "女");
    tempMap.put("age", new Random().nextInt(90) + 11);
    tempMap.put("hobby", "活的，女的！！！");
    tempMap.put("handsomeValue", "100分(满分100分)");
    tempMap.put("motto", "之所以只帅到了全亚洲，是因为其他地方审美不同！");
    list.add(tempMap);
  }
  map.put("dataList", list);
  // 生成workbook 并导出
  Workbook workbook = ExcelExportUtil.exportExcel(params, map);
  File savefile = new File("E:/temp/easypoi");
  if (!savefile.exists()) {
    boolean result = savefile.mkdirs();
    System.out.println("目录不存在,进行创建,创建" + (result ? "成功!" : "失败！"));
  }
  FileOutputStream fos = new FileOutputStream("E:/temp/easypoi/采访结果.xls");
  workbook.write(fos);
  fos.close();
}

使用模板将Object数据导出为excel示例(需要模板)

// 模板导出---对象组装数据
// 注:实际上仍然是"模板导出---Map组装数据",不过这里借助了工具类，将对象先转换为了Map<String, Object>
// 注:.xls的模板可以导出.xls文件，也可以导出xlsx的文件;同样的, .xlsx的模板可以导出.xls文件，也可以导出xlsx的文件;
@Test
public void templateExportExcelByObject() throws IOException, IllegalAccessException {
  // 加载模板
  TemplateExportParams params = new TemplateExportParams("templates/templateObject.xlsx");
  // 组装数据
  InterviewResult interviewResult = new InterviewResult();
  interviewResult.setTitle("全亚洲最帅人员名单");
  interviewResult.setInterviewer("邓沙利文");
  interviewResult.setDate("2018-12-05");
  List<HandsomeBoy> list = new ArrayList<>(8);
  interviewResult.setList(list);
  HandsomeBoy handsomeBoy;
  for (int i = 0; i < 5; i++) {
    handsomeBoy = new HandsomeBoy();
    handsomeBoy.setAge(20 + i);
    handsomeBoy.setGender(i % 2 == 0 ? "女" : "男");
    handsomeBoy.setHandsomeValue(95 + i + "(满分100分)");
    handsomeBoy.setHobby("女。。。。");
    handsomeBoy.setMotto("我是一只小小小小鸟~");
    handsomeBoy.setName("JustryDeng");
    list.add(handsomeBoy);
  }
  // 生成workbook 并导出
  Workbook workbook = ExcelExportUtil.exportExcel(params, objectToMap(interviewResult));
  File savefile = new File("E:/temp/easypoi");
  if (!savefile.exists()) {
    boolean result = savefile.mkdirs();
    System.out.println("目录不存在,进行创建,创建" + (result ? "成功!" : "失败！"));
  }
  FileOutputStream fos = new FileOutputStream("E:/temp/easypoi/采访结果.xlsx");
  workbook.write(fos);
  fos.close();
}

方式2、直接将List<Map<String, Object>>数据导出为excel示例(无需模板)

/**
 * 直接导出(无需模板) 注:此方式存在一些不足之处，在对性能、excel要求比较严格时不推荐使用
 * 
 * @throws IOException
 */
@Test
public void directExportExcel() throws IOException {
  // Map作为每一行的数据容器，List作为行的容器
  List<Map<String, Object>> rowDataList = new ArrayList<>();
  // 每个ExcelExportEntity存放Map行数据的key
  List<ExcelExportEntity> keyList = new ArrayList<>();
  Map<String, Object> aRowMap;
  final int COMMON_KEY_INDEX = 10;
  for (int i = 0; i < 5; i++) {
    // 一个Map对应一行数据（如果需要导出多行数据，那么需要多个Map）
    aRowMap = new HashMap<>(16);
    for (int j = 0; j < COMMON_KEY_INDEX; j++) {
      String key = j + "";
      aRowMap.put(key, "坐标(" + i + "," + j + ")");
    }
    rowDataList.add(aRowMap);
    // 同一列对应的cell,在从Map里面取值时，会共用同一个key
    // 因此ExcelExportEntity的个数要保持和列数做多的行 的map.size()大小一致
    if (i == 0) {
      ExcelExportEntity excelExportEntity;
      for (int j = 0; j < COMMON_KEY_INDEX; j++) {
        excelExportEntity = new ExcelExportEntity();
        excelExportEntity.setKey(j + "");
        // 设置cell宽
        excelExportEntity.setWidth(15D);
        // 设置cell是否自动换行
        excelExportEntity.setWrap(true);
        keyList.add(excelExportEntity);
      }
    }
  }
  // excel总体设置
  ExportParams exportParams = new ExportParams();
  // 不需要标题
  exportParams.setCreateHeadRows(false);
  // 指定sheet名字
  exportParams.setSheetName("直接导出数据测试");
  // 生成workbook 并导出
  Workbook workbook = ExcelExportUtil.exportExcel(exportParams, keyList, rowDataList);
  File savefile = new File("E:\\temp\\easypoi");
  if (!savefile.exists()) {
    boolean result = savefile.mkdirs();
    System.out.println("目录不存在，创建" + result);
  }
  FileOutputStream fos = new FileOutputStream("E:\\temp\\easypoi\\坐标.xls");
  workbook.write(fos);
  fos.close();
}

方式3：通过注解，直接将Object(集合,需要在模型的属性添加@excel注解)数据导出为excel示例(无需模板)

/**
 * 对象---直接导出(无需模板) 注:如果模型 的父类的属性也有@Excel注解，那么导出excel时，会连该模型的父类的属性也一会儿导出
 * 
 * @throws IOException
 */
@Test
public void directExportExcelByObject() throws IOException {
  List<Student> list = new ArrayList<>(16);
  Student student;
  Random random = new Random();
  for (int i = 0; i < 10; i++) {
    student = new Student(i + "", "name" + i, random.nextInt(2), random.nextInt(100), new Date(),
        "className" + i);
    student.setSchoolName("学校名称" + i);
    student.setSchoolAddress("学校地址" + i);
    list.add(student);
  }
  ExportParams exportParams = new ExportParams();
  exportParams.setSheetName("我是sheet名字");
  // 生成workbook 并导出
  Workbook workbook = ExcelExportUtil.exportExcel(exportParams, Student.class, list);
  File savefile = new File("E:/temp/easypoi");
  if (!savefile.exists()) {
    boolean result = savefile.mkdirs();
    System.out.println("目录不存在，创建" + result);
  }
  FileOutputStream fos = new FileOutputStream("E:/temp/easypoi/学生.xls");
  workbook.write(fos);
  fos.close();
}

实战

模型

 
 
 
import cn.afterturn.easypoi.excel.annotation.Excel;
 
import java.io.Serializable;
 
public class ReamQuotaMonthBo implements Serializable {
    /**
     * Id
     *
     * @mbggenerated
     */
    @Excel(name = "ID")
    private Integer id;
 
    /**
     * 用户id
     *
     * @mbggenerated
     */
    @Excel(name = "用户id")
    private Integer userId;
 
    /**
     * 用户姓名
     */
    @Excel(name = "姓名")
    private String userName;
 
 
    /**
     * 年月
     *
     * @mbggenerated
     */
    @Excel(name = "年月")
    private String dt;
 
    /**
     * 当月总报销额度
     *
     * @mbggenerated
     */
    @Excel(name = "总额度")
    private Double totalAmount;
 
    /**
     * 当月剩余报销额度
     *
     * @mbggenerated
     */
    @Excel(name = "剩余额度")
    private Double leftAmount;
 
 
 
    /**
     * Id
     * @return
     *
     * @mbggenerated
     */
    public Integer getId() {
        return id;
    }
 
    /**
     * Id
     * @param id
     *
     * @mbggenerated
     */
    public void setId(Integer id) {
        this.id = id;
    }
 
    /**
     * 用户id
     * @return
     *
     * @mbggenerated
     */
    public Integer getUserId() {
        return userId;
    }
 
    /**
     * 用户id
     * @param userId
     *
     * @mbggenerated
     */
    public void setUserId(Integer userId) {
        this.userId = userId;
    }
 
    /**
     * 年月
     * @return
     *
     * @mbggenerated
     */
    public String getDt() {
        return dt;
    }
 
    /**
     * 年月
     * @param dt
     *
     * @mbggenerated
     */
    public void setDt(String dt) {
        this.dt = dt == null ? null : dt.trim();
    }
 
    /**
     * 当月总报销额度
     * @return
     *
     * @mbggenerated
     */
    public Double getTotalAmount() {
        return totalAmount;
    }
 
    /**
     * 当月总报销额度
     * @param totalAmount
     *
     * @mbggenerated
     */
    public void setTotalAmount(Double totalAmount) {
        this.totalAmount = totalAmount;
    }
 
    /**
     * 当月剩余报销额度
     * @return
     *
     * @mbggenerated
     */
    public Double getLeftAmount() {
        return leftAmount;
    }
 
    /**
     * 当月剩余报销额度
     * @param leftAmount
     *
     * @mbggenerated
     */
    public void setLeftAmount(Double leftAmount) {
        this.leftAmount = leftAmount;
    }
 
    public String getUserName() {
        return userName;
    }
 
    public void setUserName(String userName) {
        this.userName = userName;
    }
 
    @Override
    public String toString() {
        return "ReamQuotaMonthBo{" +
                "id=" + id +
                ", userId=" + userId +
                ", userName='" + userName + '\'' +
                ", dt='" + dt + '\'' +
                ", totalAmount=" + totalAmount +
                ", leftAmount=" + leftAmount +
                '}';
    }
}

直接导出

 @RequestMapping(value="/exportReimQuotaMonthList", method = RequestMethod.GET)
    public void exportReimQuotaMonthList(Integer year, String month, HttpServletResponse response) throws Exception {
        if(month!=null && !month.equals("")){
            month =year+"-"+month;
        }
        log.info("查询参数分别是：year="+year+"month="+month);
        List<ReamQuotaMonthBo> reimQuotaMonthBos = reimQuotaMonthService.selectAllByCon(year,month);
        // 设置响应输出的头类型
        response.setHeader("content-Type", "application/vnd.ms-excel");
        // 设计导出文件的名称，尽量不要中文
        String fileName = new String("excel.xls".getBytes(), "ISO-8859-1");
        response.setHeader("Content-Disposition","attachment;filename="+fileName);
        //输出流。
        ServletOutputStream out = response.getOutputStream();
        // 创建参数对象（用来设定excel得sheet的内容等信息）
        ExportParams params = new ExportParams() ; //sheet
        // title的参数为ExportParams类型，目前仅仅在ExportParams中设置了sheetName
        params.setSheetName("导出");
        Workbook workbook = ExcelExportUtil.exportExcel(params, ReamQuotaMonthBo.class,reimQuotaMonthBos) ;
        workbook.write(out);
    }

附：对象转换为Map<String, Object>的工具类

/**
 * 对象转换为Map<String, Object>的工具类
 * 
 * @param obj 要转换的对象
 * @return
 * @throws IllegalAccessException
 */
private static Map<String, Object> objectToMap(Object obj) throws IllegalAccessException {
  Map<String, Object> map = new HashMap<>(16);
  Class<?> clazz = obj.getClass();
  for (Field field : clazz.getDeclaredFields()) {
    field.setAccessible(true);
    String fieldName = field.getName();
    Object value = field.get(obj);
    map.put(fieldName, value);
  }
  return map;
}

注意:注：

通过模板导只能以xls为结尾,xlsx为结尾时不能正常打开。

不用模板xls,xlsx都能正常打开,通过ExcelType设置,ExcelType.HSSF:xls ExcelType.XSSF:xlsx。

3、百万级数据导出导入

3.1、概述

其实想到数据的导入导出,理所当然的会想到apache的poi技术,以及Excel的版本问题。

既然要做导入导出,那么我们就先来大致看一下传统poi技术的版本以及优缺点对比吧!

首先我们知道POI中我们最熟悉的莫过于WorkBook这样一个接口，我们的POI版本也在更新的同时对这个几口的实现类做了更新：

HSSFWorkbook :

这个实现类是我们早期使用最多的对象，它可以操作Excel2003以前（包含2003）的所有Excel版本。在2003以前Excel的版本后缀还是.xls

它是POI版本中最常用的方式，不过:

它的缺点是最多只能导出 65535行，也就是导出的数据函数超过这个数据就会报错;
它的优点是不会报内存溢出。（因为数据量还不到7w所以内存一般都够用，首先你得明确知道这种方式是将数据先读取到内存中，然后再操作）
XSSFWorkbook :

这个实现类现在在很多公司都可以发现还在使用，它是操作的Excel2003--Excel2007之间的版本，Excel的扩展名是.xlsx

优点：这种形式的出现是为了突破HSSFWorkbook的65535行局限，是为了针对Excel2007版本的1048576行，16384列，最多可以导出104w条数据；
缺点：伴随的问题来了，虽然导出数据行数增加了好多倍，但是随之而来的内存溢出问题也成了噩梦。因为你所创建的book，Sheet，row，cell等在写入到Excel之前，都是存放在内存中的（这还没有算Excel的一些样式格式等等），可想而知，内存不溢出就有点不科学了！！！
SXSSFWorkbook :

这个实现类是POI3.8之后的版本才有的,它可以操作Excel2007以后的所有版本Excel,扩展名是.xlsx

大致知道了我们在导入导出操作的时候会用到这样三个实现类以及他们可以操作的Excel版本和后缀之后,我们就要从优缺点分析他们了

从POI 3.8版本开始，提供了一种基于XSSF的低内存占用的SXSSF方式：

优点：

这种方式不会一般不会出现内存溢出（它使用了硬盘来换取内存空间，
也就是当内存中数据达到一定程度这些数据会被持久化到硬盘中存储起来，而内存中存的都是最新的数据），
并且支持大型Excel文件的创建（存储百万条数据绰绰有余）。

缺点：

既然一部分数据持久化到了硬盘中，且不能被查看和访问那么就会导致，
在同一时间点我们只能访问一定数量的数据，也就是内存中存储的数据;
sheet.clone()方法将不再支持,还是因为持久化的原因;
不再支持对公式的求值，还是因为持久化的原因，在硬盘中的数据没法读取到内存中进行计算；
在使用模板方式下载数据的时候，不能改动表头，还是因为持久化的问题，写到了硬盘里就不能改变了；

经过了解也知道了这三种Workbook的优点和缺点，那么具体使用哪种方式还是需要看情况的：

我一般会根据这样几种情况做分析选择：

1、当我们经常导入导出的数据不超过7w的情况下，可以使用 HSSFWorkbook 或者 XSSFWorkbook都行；

2、当数据量查过7w并且导出的Excel中不牵扯对Excel的样式，公式，格式等操作的情况下，推荐使用SXSSFWorkbook;

3、当数据量查过7w，并且我们需要操做Excel中的表头，样式，公式等，这时候我们可以使用 XSSFWorkbook 配合进行分批查询，分批写入Excel的方式来做；

遇到的超百万数据的导入导出解决方案？

想要解决问题我们首先要明白自己遇到的问题是什么？

1、我遇到的数据量超级大，使用传统的POI方式来完成导入导出很明显会内存溢出，并且效率会非常低；

2、数据量大直接使用select * from tableName肯定不行，一下子查出来300w条数据肯定会很慢；

3、 300w 数据导出到Excel时肯定不能都写在一个Sheet中，这样效率会非常低；估计打开都得几分钟；

4、 300w数据导出到Excel中肯定不能一行一行的导出到Excel中。频繁IO操作绝对不行；

5、导入时300万数据存储到DB如果循环一条条插入也肯定不行；

6、导入时300w数据如果使用Mybatis的批量插入肯定不行，因为Mybatis的批量插入其实就是SQL的循环；一样很慢。

解决思路：

针对1 ：

其实问题所在就是内存溢出，我们只要使用对上面介绍的POI方式即可，主要问题就是原生的POI解决起来相当麻烦。

经过查阅资料翻看到阿里的一款POI封装工具EasyExcel，上面问题等到解决；

针对2：

不能一次性查询出全部数据，我们可以分批进行查询，只不过时多查询几次的问题，况且市面上分页插件很多。此问题好解决。

针对3：

可以将300w条数据写到不同的Sheet中，每一个Sheet写一百万即可。

针对4：

不能一行一行的写入到Excel上，我们可以将分批查询的数据分批写入到Excel中。

针对5：

导入到DB时我们可以将Excel中读取的数据存储到集合中，到了一定数量，直接批量插入到DB中。

针对6：

不能使用Mybatis的批量插入，我们可以使用JDBC的批量插入，配合事务来完成批量插入到DB。即 Excel读取分批+JDBC分批插入+事务。

3.2、300w数据的导出

首先在查询数据库层面，需要分批进行查询（我使用的是每次查询20w）
每查询一次结束，就使用EasyExcel工具将这些数据写入一次；
当一个Sheet写满了100w条数据，开始将查询的数据写入到另一个Sheet中；
如此循环直到数据全部导出到Excel完毕。

注意：

1、我们需要计算Sheet个数，以及循环写入次数。特别是最后一个Sheet的写入次数

因为你不知道最后一个Sheet选哟写入多少数据，可能是100w，也可能是25w因为我们这里的300w只是模拟数据，有可能导出的数据比300w多也可能少

2、我们需要计算写入次数，因为我们使用的分页查询，所以需要注意写入的次数。

其实查询数据库多少次就是写入多少次

代码

//导出逻辑代码
public void dataExport300w(HttpServletResponse response) {
    {
        OutputStream outputStream = null;
        try {
            long startTime = System.currentTimeMillis();
            System.out.println("导出开始时间:" + startTime);
 
            outputStream = response.getOutputStream();
            ExcelWriter writer = new ExcelWriter(outputStream, ExcelTypeEnum.XLSX);
            String fileName = new String(("excel100w").getBytes(), "UTF-8");
 
            //title
            Table table = new Table(1);
            List<List<String>> titles = new ArrayList<List<String>>();
            titles.add(Arrays.asList("onlineseqid"));
            titles.add(Arrays.asList("businessid"));
            titles.add(Arrays.asList("becifno"));
            titles.add(Arrays.asList("ivisresult"));
            titles.add(Arrays.asList("createdby"));
            titles.add(Arrays.asList("createddate"));
            titles.add(Arrays.asList("updateby"));
            titles.add(Arrays.asList("updateddate"));
            titles.add(Arrays.asList("risklevel"));
            table.setHead(titles);
 
            //模拟统计查询的数据数量这里模拟100w
            int count = 3000001;
            //记录总数:实际中需要根据查询条件进行统计即可
            Integer totalCount = actResultLogMapper.findActResultLogByCondations(count);
            //每一个Sheet存放100w条数据
            Integer sheetDataRows = ExcelConstants.PER_SHEET_ROW_COUNT;
            //每次写入的数据量20w
            Integer writeDataRows = ExcelConstants.PER_WRITE_ROW_COUNT;
            //计算需要的Sheet数量
            Integer sheetNum = totalCount % sheetDataRows == 0 ? (totalCount / sheetDataRows) : (totalCount / sheetDataRows + 1);
            //计算一般情况下每一个Sheet需要写入的次数(一般情况不包含最后一个sheet,因为最后一个sheet不确定会写入多少条数据)
            Integer oneSheetWriteCount = sheetDataRows / writeDataRows;
            //计算最后一个sheet需要写入的次数
            Integer lastSheetWriteCount = totalCount % sheetDataRows == 0 ? oneSheetWriteCount : (totalCount % sheetDataRows % writeDataRows == 0 ? (totalCount / sheetDataRows / writeDataRows) : (totalCount / sheetDataRows / writeDataRows + 1));
 
            //开始分批查询分次写入
            //注意这次的循环就需要进行嵌套循环了,外层循环是Sheet数目,内层循环是写入次数
            List<List<String>> dataList = new ArrayList<>();
            for (int i = 0; i < sheetNum; i++) {
                //创建Sheet
                Sheet sheet = new Sheet(i, 0);
                sheet.setSheetName("测试Sheet1" + i);
                //循环写入次数: j的自增条件是当不是最后一个Sheet的时候写入次数为正常的每个Sheet写入的次数,如果是最后一个就需要使用计算的次数lastSheetWriteCount
                for (int j = 0; j < (i != sheetNum - 1 ? oneSheetWriteCount : lastSheetWriteCount); j++) {
                    //集合复用,便于GC清理
                    dataList.clear();
                    //分页查询一次20w
                    PageHelper.startPage(j + 1 + oneSheetWriteCount * i, writeDataRows);
                    List<ActResultLog> reslultList = actResultLogMapper.findByPage100w();
                    if (!CollectionUtils.isEmpty(reslultList)) {
                        reslultList.forEach(item -> {
                            dataList.add(Arrays.asList(item.getOnlineseqid(), item.getBusinessid(), item.getBecifno(), item.getIvisresult(), item.getCreatedby(), Calendar.getInstance().getTime().toString(), item.getUpdateby(), Calendar.getInstance().getTime().toString(), item.getRisklevel()));
                        });
                    }
                    //写数据
                    writer.write0(dataList, sheet, table);
                }
            }
 
            // 下载EXCEL
            response.setHeader("Content-Disposition", "attachment;filename=" + new String((fileName).getBytes("gb2312"), "ISO-8859-1") + ".xlsx");
            response.setContentType("multipart/form-data");
            response.setCharacterEncoding("utf-8");
            writer.finish();
            outputStream.flush();
            //导出时间结束
            long endTime = System.currentTimeMillis();
            System.out.println("导出结束时间:" + endTime + "ms");
            System.out.println("导出所用时间:" + (endTime - startTime) / 1000 + "秒");
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (outputStream != null) {
                try {
                    outputStream.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

数据库我使用的是Oracle19C在网上查阅其实在数据量不超过1亿的情况下，Mysql和Oracle的性能其实相差不大,超过1亿,Oracle的各方面优势才会明显。

所以这里可以忽略使用数据库对时间造成的影响，使用mysql一样可以完成测试，不需要单独安装Oracle。

这次测试在查询方面我使用的是rownum进行的模拟查询300w条数据，这种查询效率其实并不高，实际还有很多优化空间来加快查询速度，

如：明确查询具体字段，不要用星号，经常查询字段增加索引等尽可能提高查询效率，用时可能会更短。

300w的数据导出时间用时2分15秒，并且这是在不适用实体作为映射的情况下，如果使用实体映射不适用循环封装的话速度会更快(当然这也是在没有设置表头等其他表格样式的情况下)

综合来说速度还算可以。

在网上查了很多资料有一个博主测试使用EasyExcel导出102w数据用时105秒，具体可以看一下链接：

https://blog.csdn.net/u014299266/article/details/107790561

经过测试EasyExcel还是挺快的，并且使用起来相当方便，作者还专门提供了关流方法，不需要我们手动去关流了，也避免了我们经常忘记关流导致的一系列问题。

导出测试就到这里，对于数据量小于300W的数据可以使用在一个Sheet中进行导出。这里就不再演示。

3.3、300w数据导入

代码不重要首先还是思路

1、首先是分批读取读取Excel中的300w数据，这一点EasyExcel有自己的解决方案，我们可以参考Demo即可，只需要把它分批的参数3000调大即可。我是用的20w；（一会儿代码一看就能明白）

2、其次就是往DB里插入，怎么去插入这20w条数据，当然不能一条一条的循环，应该批量插入这20w条数据，同样也不能使用Mybatis的批量插入语，因为效率也低。可以参考下面链接【Myabtis批量插入和JDBC批量插入性能对比】

3、使用JDBC+事务的批量操作将数据插入到数据库。（分批读取+JDBC分批插入+手动事务控制）

https://www.cnblogs.com/wxw7blog/p/8706797.html

核心业务代码

// EasyExcel的读取Excel数据的API
@Test
public void import2DBFromExcel10wTest() {
    String fileName = "D:\\StudyWorkspace\\JavaWorkspace\\java_project_workspace\\idea_projects\\SpringBootProjects\\easyexcel\\exportFile\\excel300w.xlsx";
    //记录开始读取Excel时间,也是导入程序开始时间
    long startReadTime = System.currentTimeMillis();
    System.out.println("------开始读取Excel的Sheet时间(包括导入数据过程):" + startReadTime + "ms------");
    //读取所有Sheet的数据.每次读完一个Sheet就会调用这个方法
    EasyExcel.read(fileName, new EasyExceGeneralDatalListener(actResultLogService2)).doReadAll();
    long endReadTime = System.currentTimeMillis();
    System.out.println("------结束读取Excel的Sheet时间(包括导入数据过程):" + endReadTime + "ms------");
}
// 事件监听
public class EasyExceGeneralDatalListener extends AnalysisEventListener<Map<Integer, String>> {
    /**
     * 处理业务逻辑的Service,也可以是Mapper
     */
    private ActResultLogService2 actResultLogService2;
 
    /**
     * 用于存储读取的数据
     */
    private List<Map<Integer, String>> dataList = new ArrayList<Map<Integer, String>>();
 
    public EasyExceGeneralDatalListener() {
    }
    
    public EasyExceGeneralDatalListener(ActResultLogService2 actResultLogService2) {
        this.actResultLogService2 = actResultLogService2;
    }
    
    @Override
    public void invoke(Map<Integer, String> data, AnalysisContext context) {
        //数据add进入集合
        dataList.add(data);
        //size是否为100000条:这里其实就是分批.当数据等于10w的时候执行一次插入
        if (dataList.size() >= ExcelConstants.GENERAL_ONCE_SAVE_TO_DB_ROWS) {
            //存入数据库:数据小于1w条使用Mybatis的批量插入即可;
            saveData();
            //清理集合便于GC回收
            dataList.clear();
        }
    }
 
    /**
     * 保存数据到DB
     *
     * @param
     * @MethodName: saveData
     * @return: void
     */
    private void saveData() {
        actResultLogService2.import2DBFromExcel10w(dataList);
        dataList.clear();
    }
    
    /**
     * Excel中所有数据解析完毕会调用此方法
     *
     * @param: context
     * @MethodName: doAfterAllAnalysed
     * @return: void
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        saveData();
        dataList.clear();
    }
}
//JDBC工具类
public class JDBCDruidUtils {
    private static DataSource dataSource;
 
    /*
   创建数据Properties集合对象加载加载配置文件
    */
    static {
        Properties pro = new Properties();
        //加载数据库连接池对象
        try {
            //获取数据库连接池对象
            pro.load(JDBCDruidUtils.class.getClassLoader().getResourceAsStream("druid.properties"));
            dataSource = DruidDataSourceFactory.createDataSource(pro);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
 
    /*
    获取连接
     */
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }
 
 
    /**
     * 关闭conn,和 statement独对象资源
     *
     * @param connection
     * @param statement
     * @MethodName: close
     * @return: void
     */
    public static void close(Connection connection, Statement statement) {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
 
    /**
     * 关闭 conn , statement 和resultset三个对象资源
     *
     * @param connection
     * @param statement
     * @param resultSet
     * @MethodName: close
     * @return: void
     */
    public static void close(Connection connection, Statement statement, ResultSet resultSet) {
        close(connection, statement);
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
 
    /*
    获取连接池对象
     */
    public static DataSource getDataSource() {
        return dataSource;
    }
 
}
# druid.properties配置
driverClassName=oracle.jdbc.driver.OracleDriver
url=jdbc:oracle:thin:@localhost:1521:ORCL
username=mrkay
password=******
initialSize=10
maxActive=50
maxWait=60000
// Service中具体业务逻辑
 
/**
 * 测试用Excel导入超过10w条数据,经过测试发现,使用Mybatis的批量插入速度非常慢,所以这里可以使用 数据分批+JDBC分批插入+事务来继续插入速度会非常快
 *
 * @param
 * @MethodName: import2DBFromExcel10w
 * @return: java.util.Map<java.lang.String, java.lang.Object>
 */
@Override
public Map<String, Object> import2DBFromExcel10w(List<Map<Integer, String>> dataList) {
    HashMap<String, Object> result = new HashMap<>();
    //结果集中数据为0时,结束方法.进行下一次调用
    if (dataList.size() == 0) {
        result.put("empty", "0000");
        return result;
    }
    //JDBC分批插入+事务操作完成对10w数据的插入
    Connection conn = null;
    PreparedStatement ps = null;
    try {
        long startTime = System.currentTimeMillis();
        System.out.println(dataList.size() + "条,开始导入到数据库时间:" + startTime + "ms");
        conn = JDBCDruidUtils.getConnection();
        //控制事务:默认不提交
        conn.setAutoCommit(false);
        String sql = "insert into ACT_RESULT_LOG (onlineseqid,businessid,becifno,ivisresult,createdby,createddate,updateby,updateddate,risklevel) values";
        sql += "(?,?,?,?,?,?,?,?,?)";
        ps = conn.prepareStatement(sql);
        //循环结果集:这里循环不支持"烂布袋"表达式
        for (int i = 0; i < dataList.size(); i++) {
            Map<Integer, String> item = dataList.get(i);
            ps.setString(1, item.get(0));
            ps.setString(2, item.get(1));
            ps.setString(3, item.get(2));
            ps.setString(4, item.get(3));
            ps.setString(5, item.get(4));
            ps.setTimestamp(6, new Timestamp(System.currentTimeMillis()));
            ps.setString(7, item.get(6));
            ps.setTimestamp(8, new Timestamp(System.currentTimeMillis()));
            ps.setString(9, item.get(8));
            //将一组参数添加到此 PreparedStatement 对象的批处理命令中。
            ps.addBatch();
        }
        //执行批处理
        ps.executeBatch();
        //手动提交事务
        conn.commit();
        long endTime = System.currentTimeMillis();
        System.out.println(dataList.size() + "条,结束导入到数据库时间:" + endTime + "ms");
        System.out.println(dataList.size() + "条,导入用时:" + (endTime - startTime) + "ms");
        result.put("success", "1111");
    } catch (Exception e) {
        result.put("exception", "0000");
        e.printStackTrace();
    } finally {
        //关连接
        JDBCDruidUtils.close(conn, ps);
    }
    return result;
}

下面是300w数据边读边写用时间:

大致计算一下:

从开始读取到中间分批导入再到程序结束总共用时: (1623127964725-1623127873630)/1000=91.095秒

300w数据正好是分15次插入综合用时:8209毫秒也就是 8.209秒

计算可得300w数据读取时间为:91.095-8.209=82.886秒

结果显而易见:

EasyExcel分批读取300W数据只用了 82.886秒

使用JDBC分批+事务操作插入300w条数据综合只用时 8.209秒

具体我没有看网上其他人的测试情况，这东西一般也很少有人愿意测试，不过这个速度对于我当时解决公司大数据的导入和导出已经足够，当然公司的业务逻辑很复杂，数据量也比较多，表的字段也比较多，导入和导出的速度会比现在测试的要慢一点，但是也在人类能接受的范围之内。

导出导入开发场景

一、excel导出导入

1、框架

2、EasyPoi使用

2.1、导入依赖

2.2、导出示例

3、百万级数据导出导入

3.1、概述

3.2、300w数据的导出

3.3、300w数据导入

热门文章

最新文章

相关电子书

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

导出导入开发场景

一、excel导出导入

1、框架

2、EasyPoi使用

2.1、导入依赖

2.2、导出示例

3、百万级数据导出导入

3.1、概述

3.2、300w数据的导出

3.3、300w数据导入

热门文章

最新文章

相关电子书