java读取Excel文件并各方案对比

最新推荐文章于 2024-05-15 05:44:08 发布

邋遢道

最新推荐文章于 2024-05-15 05:44:08 发布

阅读量2k

点赞数 2

分类专栏： java 文章标签：面试 excel

本文链接：https://blog.csdn.net/qq_20607405/article/details/127635941

版权

java 专栏收录该内容

18 篇文章 0 订阅

订阅专栏

前言

java程序员经常会做一些报表导入的工作，比如历史数据迁移，批量数据导入等等都会需要。曾经我遇到过一场面试。面试官问我在哪些地方使用过多线程。我顺口提了一句在表格导入的时候也使用过。然后就开始鬼畜了：

1.你解决了10w数据的导入，那你有试过100w，1000w,一个亿，甚至更大吗？

2.这么多数据，多线程处理会不会重复操作？

3.一个亿的数据，网络请求能抗住吗？你的内存能抗住吗？

。。。。。

解决不了问题就解决出问题的人，我想打人

其实这些问题对我而言还算是受益匪浅，很多事情可能看起来是比较简单，但是我们从来没有考虑过如果量变引起质变，我们能不能保证我们程序的健壮性？接下来就是根据自己的经验对如何导入数据做一些简单的记录，也希望能对别人有所帮助或者启发。当然，欢迎有更好的解决方案不断学习，不断超越，数据没有上限，我们的解决方案也永远没有上限！

本文源码先给出来一下：阿里云盘分享

POI

我所了解过的读取表格的方式有jxl，和poi的方式。记得以前开始工作的时候是用的jxl方式，而且当时也有很多错误，比如excel版本需要2003 不支持2007以上（被这个bug支配过）。当然这么多年也没有再玩过了，据说是没更新了，所以就不再说明了，只对poi方式说明一下

简单说明下原理读取步骤：

1.加载文件路径、获取流

2.创建工作簿

3.取表

4.取行

5.取单元格

单线程处理

直接上代码：

/**
     * @Author andy
     * @Description 文件读取 poi方式，并写入数据库
     * @Date 11:37 2022/10/27
     * @Param [file]
     * @return boolean
     **/
    @Override
    public boolean poiFileRead(MultipartFile file){
//        readGoodOutOf(file);  //单条新增  本地数据库不存在网络问题，所以与批量操作效率差距不明显
        try {
            XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream());//从文件流中创建工作簿
            XSSFSheet sheet = workbook.getSheetAt(0);//获取表格
            int lastRowNum = sheet.getLastRowNum();//所有行数
            int row = 0;
            while (row<=lastRowNum){
                //循环读取,每次处理1000条
                saveBatch(readGoodWhile(sheet,row,1000,lastRowNum));
                row+=1000;
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return true;
    }

/**
     * @Author andy
     * @Description //循环读取实体
     * @Date 11:41 2022/10/27
     * @Param [file]
     * @return java.util.List<com.file.entity.SupplierGoodCopy>
     **/
    @Async("myThreadPoolExecutor")
    public List<SupplierGoodCopy> readGoodWhile(XSSFSheet sheet,int begin,int lengths,int lastRowNum){
        List<SupplierGoodCopy> list = new ArrayList<>();
        try {
            //分行和列读取
            for(int row = begin; row < (begin+lengths) && row<=lastRowNum; row++) {
                //节省篇幅，没有写完出来，最后会把源码和测试结果给出来
                //读取每行，每个单元格
                SupplierGoodCopy area = new SupplierGoodCopy(                     sheet.getRow(row).getCell(0)==null?"":sheet.getRow(row).getCell(0).getStringCellValue(),
                );
                list.add(area);
            }
        } catch (Exception e){
            e.printStackTrace();
        }
        return list;
    }

先简单解释一下，大体步骤是上面的五部，先加载到内存，然后再循环读取，批量新增。这里面有一些弊端，可以思考下

看下实测的结果：5w多条数据

1.单条新增的结果

2.多条新增的结果

可以看到，单条比多条的新增慢了一些。其实这个速度并不准确。因为我本地测试的，数据库也是本地，以前我试过链接公网数据库，两个的速度差距能达到N多倍，原因是因为网络请求的缘故，如果单条新增，1000条就会有1000次网络io,而批量只需要一次io。这是第一个需要关注的点

多线程处理

package com.file.config;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.scheduling.annotation.EnableAsync;
import org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor;

import java.util.concurrent.Executor;

/**
 * 线程池定义
 */
@Configuration
@EnableAsync
public class ThreadPoolConfig {

    @Bean("myThreadPoolExecutor")
    public Executor threadPoolExecutor(){
        ThreadPoolTaskExecutor taskExecutor = new ThreadPoolTaskExecutor();
        taskExecutor.setCorePoolSize(1);//核心线程数
        taskExecutor.setMaxPoolSize(10);//最大线程数
        taskExecutor.setQueueCapacity(100);//队列大小
        taskExecutor.setKeepAliveSeconds(60);//保持存活时长
        taskExecutor.setThreadNamePrefix("threadPoolExecutor-");//名称前缀
        //下面两个是线程关闭需要注意的问题
        taskExecutor.setWaitForTasksToCompleteOnShutdown(true);//线程池关闭的时候等待所有任务都完成再继续销毁其他的Bean，默认false
        taskExecutor.setAwaitTerminationSeconds(60);//设置线程池中 任务的等待时间，如果超过这个时间还没有销毁就 强制销毁，以确保应用最后能够被关闭，而不是阻塞住。
        return taskExecutor;
    }
}

package com.file.business;

import com.alibaba.excel.util.ListUtils;
import com.file.entity.SupplierGoodCopy;
import com.file.mapper.SupplierGoodCopyMapper;
import com.file.service.SupplierGoodCopyService;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.xssf.usermodel.XSSFSheet;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @ClassName BatchInsert
 * @Description 批量新增
 * @Author andy
 * @Date 2022/10/28 9:58
 * @Version 1.0
 */
@Data
@Slf4j
public class BatchInsert implements Runnable{

    private XSSFSheet sheet;
    private int begin;
    private int lengths;
    private int lastRowNum;
    private SupplierGoodCopyService service;

    public BatchInsert(XSSFSheet sheet, int begin, int lengths, int lastRowNum, SupplierGoodCopyService service) {
        this.sheet = sheet;
        this.begin = begin;
        this.lengths = lengths;
        this.lastRowNum = lastRowNum;
        this.service = service;
    }

    @Override
    public void run() {
        log.info("启动线程处理");
        List<SupplierGoodCopy> list = new ArrayList<>();
        try {
            int i = 0;
            //分行和列读取
            for(int row = begin; row < (begin+lengths) && row<=lastRowNum; row++) {

                SupplierGoodCopy area = new SupplierGoodCopy(
                        sheet.getRow(row).getCell(0)==null?"":sheet.getRow(row).getCell(0).getStringCellValue()
                    
                );
                list.add(area);
                i++;
                if(i>=1000){
                    service.saveBatch(list);
                    list = ListUtils.newArrayListWithExpectedSize(i);
                    i=0;
                }
            }
        } catch (Exception e){
            e.printStackTrace();
        }
        service.saveBatch(list);
    }
}

    @Autowired
    Executor myThreadPoolExecutor;
/**
     * @Author andy
     * @Description 文件读取 poi方式，并写入数据库
     * @Date 11:37 2022/10/27
     * @Param [file]
     * @return boolean
     **/
    @Override
    public boolean poiFileRead(MultipartFile file){
//        readGoodOutOf(file);  //单条新增  本地数据库不存在网络问题，所以与批量操作效率差距不明显
        try {
            XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream());
            XSSFSheet sheet = workbook.getSheetAt(0);
            int lastRowNum = sheet.getLastRowNum();//所有行数
            int row = 0;
//            while (row<=lastRowNum){
//                //循环读取,每次处理1000条
//                saveBatch(readGoodWhile(sheet,row,1000,lastRowNum));
//                row+=1000;
//            }
            //多线程线程池方式操作
            while (row<=lastRowNum){
                //循环读取,每次处理1000条
                myThreadPoolExecutor.execute(new BatchInsert(sheet,row,1000,lastRowNum,this));
                row+=1000;
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return true;
    }

使用线程池，将读取的文件每1000条为一个任务，交由线程池执行，结果如下：

是不是突然感觉多线程快好多。。。。。其实不然，注意下面一个启动线程处理，实际上我controller的主线程是已经返回了，给了用户，但是实际我的新增任务，才刚由多线程开始处理，至于实际速度，受限数据库的物理新增速度，并不比单线程的快速。当然有朋友可能会考虑并发安全问题，有没有重复插入之类的，读一下代码的逻辑，自行找下吧，实际是这部分代码，并没有线程安全问题，如果有疑问，可留言沟通。

总结下poi原始读取的方式

1.读取每个单元格的的写法，我实体类有多少字段就需要把全部字段都读取出来

2.读取前是需要把文件加载到内存，如果文件过大，内存不足就会出错

3.新增速率并不快，多线程只能让接口反馈更迅速

我测试时用的是5w多条，实测后面最大的文件里面是34w的时候，我电脑内存不足，无法加载到内存，更别说读取了。

Easy Excel

那么大的文件无法读取，难道超过了多少数据量我们就没办法用java处理么？肯定不是，不同量级我们肯定要考虑不同的方案，当技术无法满足，也可以使用其他手段。接下来我们就看下阿里根据poi的基础开发出来的上层框架 easy excel

直接看代码吧：

导入依赖

<!--easyExecl-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.1.1</version>
        </dependency>

创建监听

package com.file.business;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.util.ListUtils;
import com.file.entity.SupplierGood;
import com.file.service.SupplierGoodService;
import lombok.extern.slf4j.Slf4j;

import java.util.List;

/**
 * @ClassName UploadFileListener
 * @Description easyexcel读取数据
 * @Author andy
 * @Date 2022/10/28 11:04
 * @Version 1.0
 */
@Slf4j
public class UploadFileListener implements ReadListener<SupplierGood> {
    /**
     * 500条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 500;
    /**
     * 缓存的数据
     */
    private List<SupplierGood> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
    /**
     * 一个service。当然如果不用存储这个对象没用。
     */
    private SupplierGoodService service;

    /**
     * 每次创建Listener的时候需要把spring管理的类传进来
     *
     * @param service
     */
    public UploadFileListener(SupplierGoodService service) {
        this.service = service;
    }

    /**
     * 这个每一条数据解析都会来调用
     *
     * @param data    one row value. Is is same as {@link AnalysisContext#readRowHolder()}
     * @param context
     */
    @Override
    public void invoke(SupplierGood data, AnalysisContext context) {
        cachedDataList.add(data);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (cachedDataList.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
        }
    }

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

    /**
     * 加上存储数据库
     */
    private void saveData() {
        service.saveBatch(cachedDataList);
    }
}

实体类

package com.file.entity;

import com.alibaba.excel.annotation.ExcelProperty;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import java.io.Serializable;
import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.Date;

import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Getter;
import lombok.Setter;
import lombok.experimental.Accessors;

/**
 * <p>
 * 供应商商品管理
 * </p>
 *
 * @author andy
 * @since 2022-10-25
 */
@Getter
@Setter
//@Accessors(chain = true) easy不能使用该注解
@TableName("supplier_good")
@ApiModel(value = "SupplierGood对象", description = "供应商商品管理")
public class SupplierGood implements Serializable {

    private static final long serialVersionUID = 1L;

    @ApiModelProperty("id")
    @TableId(value = "id", type = IdType.AUTO)
    private Long id;

    @ApiModelProperty("雪花id")
    @ExcelProperty(index = 0)
    private String snowId;

    @ApiModelProperty("商品编号")
    @ExcelProperty(index = 1)
    private String goodsNumber;

    @ApiModelProperty("商品id")
    @ExcelProperty(index = 2)
    private Long goodId;

    @ApiModelProperty("供应商名称")
    @ExcelProperty(index = 3)
    private String supplierName;

    @ApiModelProperty("商品别名")
    @ExcelProperty(index = 4)
    private String goodAlias;

    @ApiModelProperty("商品一级分类id")
    @ExcelProperty(index = 5)
    private Integer oneCategoryId;

    @ApiModelProperty("商品一级分类名称")
    @ExcelProperty(index = 6)
    private String oneCategoryClass;

    @ApiModelProperty("商品二级分类id")
    @ExcelProperty(index = 7)
    private Integer twoCategoryId;

    @ApiModelProperty("商品二级分类名称")
    @ExcelProperty(index = 8)
    private String twoCategoryClass;

    @ApiModelProperty("二级分类pid")
    @ExcelProperty(index = 9)
    private Long twoClasspid;

    @ApiModelProperty("商品名称")
    @ExcelProperty(index = 10)
    private String goodName;

    @ApiModelProperty("商品品牌")
    @ExcelProperty(index = 11)
    private String goodBrand;

    @ApiModelProperty("商品产地")
    @ExcelProperty(index = 12)
    private String goodPlace;

    @ApiModelProperty("商品溯源信息")
    @ExcelProperty(index = 13)
    private String goodRoot;

    @ApiModelProperty("商品溯源说明")
    @ExcelProperty(index = 14)
    private String goodRootRemark;

    @ApiModelProperty("是否上架 0下架 1上架")
    @ExcelProperty(index = 15)
    private Integer isNotShelves;

    @ApiModelProperty("创建人id")
    @ExcelProperty(index = 16)
    private String createId;

    @ApiModelProperty("供应商id")
    @ExcelProperty(index = 17)
    private String supplierId;

    @ApiModelProperty("创建时间")
    @ExcelProperty(index = 18)
    private Date createTime;

    @ApiModelProperty("修改人id")
    @ExcelProperty(index = 19)
    private Long updateId;

    @ApiModelProperty("修改时间")
    @ExcelProperty(index = 20)
    private Date updateTime;

    @ApiModelProperty("状态")
    @ExcelProperty(index = 21)
    private Integer status;

    @ApiModelProperty("审批状态（0， 未审核 1 审核通过 1 审核驳回）")
    @ExcelProperty(index = 22)
    private Integer approvalState;

    @ApiModelProperty("供应商状态（0 上架 1 下架）")
    @ExcelProperty(index = 23)
    private Integer supplierState;

    @ApiModelProperty("系统端上架状态 （0 上架 1 下架）")
    @ExcelProperty(index = 24)
    private Integer systemState;

    @ApiModelProperty("说明")
    @ExcelProperty(index = 25)
    private String remark;

    @ApiModelProperty("选择好分类和名称，自动显示编码")
    @ExcelProperty(index = 26)
    private String goodCode;

    @ApiModelProperty("单位")
    @ExcelProperty(index = 27)
    private String unit;

    @ApiModelProperty("商品价格")
    @ExcelProperty(index = 28)
    private BigDecimal goodPrice;

    @ApiModelProperty("商品规格描述")
    @ExcelProperty(index = 29)
    private String specificationsRemark;

    @ApiModelProperty("损耗率")
    @ExcelProperty(index = 30)
    private Integer loss;

    @ApiModelProperty("商品标签id")
    @ExcelProperty(index = 31)
    private Long goodLabelId;

    @ApiModelProperty("标签名称")
    @ExcelProperty(index = 32)
    private String goodLabel;

    @ApiModelProperty("税收分类编码")
    @ExcelProperty(index = 33)
    private String rateCode;

    @ApiModelProperty("税率")
    @ExcelProperty(index = 34)
    private String rate;

    @ApiModelProperty("商品主图")
    @ExcelProperty(index = 35)
    private String goodMainImage;

    @ApiModelProperty("0 未删除 1删除")
    @ExcelProperty(index = 36)
    private Integer deleted;

    @ApiModelProperty("创建人")
    @ExcelProperty(index = 37)
    private String createName;

    @ApiModelProperty("商品规格")
    @ExcelProperty(index = 38)
    private String specifications;

    @ApiModelProperty("农民信息图片")
    @ExcelProperty(index = 39)
    private String farmingImages;

    @ApiModelProperty("起订量")
    @ExcelProperty(index = 40)
    private Integer startNumber;


}

读取

@Override
    public boolean easyFileRead(MultipartFile file) throws IOException {
        EasyExcel.read(file.getInputStream(), SupplierGood.class, new UploadFileListener(this)).sheet().doRead();
        return true;
    }

搞定了，先看下效果

这是5w条数据结果

这是34w条数据结果

来对比下poi和easy excel

拿到源码的小伙伴可以看到，我在poi原始情况下读取，对实体类写了一个构造，而且在构造中做了很多处理。（本来是偷懒想直接放值，结果各种异常越来越大）而且这个情况是没有做过数据校验的，但是easy excel并没有任何去写行和列的代码

区别：

1.代码的简洁度，可读性，维护性easy更高（有很多api可自行官网必读 | Easy Excel）

2.同样的5w条数据，easy的速度更快

3.34w数据，原始poi电脑配置不够，搞不定，easy轻松处理（easy是分段读取，利用磁盘做缓存）

各位也可自行测试

总结

我暂时没有去研究过其他的读取方式，就先说这两种了。回到最开始的面试问题来说。其实可以看到，当一个技术无法满足我们的业务的时候，我们需要在原有基础上做一次包装升级，比如easy对poi做了升级，如果你觉得easy也慢了点（34w的时候）其实去读下它的源码，在基础上能不能找到更适合自己的优化呢？肯定是可以的，比如给大家个思路，它的读取是按sheet的页，有没有可能我把大量数据分成多个sheet，用多线程来读取呢？我没有试过，但是你试过没？

当然也不是说你试了就一定会比较快，还要考虑其他的问题，比如数据库新增的速度，网络传输数据的问题，能不能加缓存或者是其他的什么方式来解决。如果所有的技术方案都无法支撑量级的增长呢？技术跟不上业务的增长了呢？这个时候就不能一味的再去用极高的成本来解决极小的优化了，适当调整下业务。比如我们在网络购物的时候付款成功为啥会有个看着没有啥任何作用的倒计时跳转页面呢？这个就是当技术无法满足所有情况，由业务做出的妥协。

技术学习永无止境，开发思想不要局限。更多思考原因，找问题。我想如果你也遇到和我一样的面试题，应该能在心中有些想法了