java百万级别数据导入导出如何优化?【篇章一】

Kyle0432

已于 2024-04-17 15:43:06 修改

阅读量2.7k

点赞数 26

分类专栏：技术解决方案专栏文章标签： java 开发语言 spring boot jvm mybatis maven log4j

于 2024-02-25 16:15:16 首次发布

本文链接：https://blog.csdn.net/qq_43345619/article/details/136281776

版权

技术解决方案专栏专栏收录该内容

9 篇文章 0 订阅

订阅专栏

一、背景
大家在日常的开发中，excel导入导出是非常常见的需求，大部分公司因为业务量比较小，操作的数据量都是几千、几万，这个级别的数据量，我们采用apache的poi很轻松可以搞定，但如果遇到大型互联网公司动不动就百万级别的用户体量，poi就会出现各种各样的问题，比较常见的现象就是内存OOM或者导出耗时特别久无响应，很显然，POI在百万级别的数据量处理，已经不合适了。

二、痛点
1、导入百万级别数据(excel)到DB，有什么痛点呢？
1）一次加载百万级别数据到内存，发生OOM？
2）导入时百万级别数据存储到DB如果循环一条条插入，数据库链接不够用？

2、导出百万级别数据(DB)到excel，有什么痛点？
1）一次查询百万级别数据到内存，mybatis查询超时报错？
2）一次加载百万级别数据到内存，发生OOM？
3）百万级别数据导出到Excel，写到一个Sheet中，能打开，不卡爆？
4）百万级别数据导出到Excel中一行一行写入Excel，频繁io操作，CPU飙高？

3、业务操作高峰期，后台管理员频繁操作导入导出excel，这个时候系统很容易就会因为内存溢出挂掉了。
那我们如何解决上面的痛点呢？那有没有什么好的解决方案能够有效的支撑百万级别的数据导入导出，而且系统的CPU、内存等核心指标不会出现明显波动呢？

三、解决方案
1、选用合适的excel开源处理框架,例如阿里的easyexcel
2、百万级别导入方案
. 逐行解析excel数据，分批读取excel数据放到list，list达到一定数据量持久化到DB，同时清空list列表数据
. 采用mybatis的foreach批量保存、mybatis plus批量saveBatch方法保存、spring jdbcTemplate，不过记得配置连接池参数要加上rewriteBatchedStatements=true
3、百万级别导出方案
. 分批分页查询db数据到内存，比如一次查询5000条，分20次分批查询出来
. 每次加载指定大小的数据到内存，比如每次5000条，5000条批量写入到excel以后，清空当前list，释放内存
. 分多个sheet写入，每个sheet比如放20w，100百万数据放到5个sheet
. 每次从数据库批量读取指定大小的数据写入到excel，比如每次5000条
4、解决高峰期导入导出资源瓶颈
. 从产品侧设计的时候，引导产品，将导入导出功能设计成异步，尽量不要同步等待，虽然牺牲了一点体验，但是长期看对系统的稳定非常重要。
（为了产品的体验，可以提供一个统一excel导入导出历史记录功能给产品或运营同学，支持查看对应的下载进度，可追溯）
. 前端导入导出按钮控制操作的频率，比如10分钟内不能频繁导入导出。
. 后端导入导出采用MQ异步排队处理，防止把内存写爆，CPU打满。
. 采用动态配置开关控制导入导出的数量，通过压测预估系统处理数据的阈值默认配置一个，导入导出超过阈值需要产品或运营发邮件申请，开发修改配置，
如果业务确实有超大数据量要求，超过阈值太多，引导产品或运营分批导入导出解决。

四、代码案例

controller层

package com.litian.dancechar.examples.excel.controller;

import cn.hutool.core.convert.Convert;
import com.litian.dancechar.examples.excel.dao.entity.StudentDO;
import com.litian.dancechar.examples.excel.dto.StudentReqDTO;
import com.litian.dancechar.examples.excel.util.StudentExcelExport;
import com.litian.dancechar.examples.excel.util.StudentExcelImport;
import com.litian.dancechar.examples.excel.service.StudentService;
import com.litian.dancechar.framework.common.base.RespResult;
import com.litian.dancechar.framework.common.base.RespResultCode;
import com.litian.dancechar.framework.excel.util.ExcelUtil;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.ArrayList;
import java.util.List;

/**
 * 学生业务处理
 *
 * @author kyle0432
 * @date 2024/02/25 12:26
 */
@Api(tags = "学生相关api")
@RestController
@Slf4j
@RequestMapping(value = "/student/")
public class StudentController {
    @Resource
    private StudentService studentService;

    @Resource
    private StudentExcelExport studentExcelExport;

    @ApiOperation(value = "从excel读取百万数据导入到db", notes = "从excel读取百万数据导入到db")
    @PostMapping("importBigData")
    public RespResult<Boolean> importData(@RequestParam("file") MultipartFile multipartFile, HttpServletRequest request){
        try{
            long start = System.currentTimeMillis();
            Integer sheetNo = Convert.toInt(request.getParameter("sheetNo"), 0);
            ExcelUtil.readExcel(multipartFile.getInputStream(), StudentDO.class,
                                new StudentExcelImport(studentService), sheetNo);
            log.info("本次导入100w数据，总耗时:{}ms", (System.currentTimeMillis() -start));
            return RespResult.success(true);
        } catch (Exception e){
            log.error(e.getMessage(), e);
            return RespResult.error(RespResultCode.IO_EXCEPTION);
        }
    }
    @ApiOperation(value = "导出百万数据到excel", notes = "导出百万数据到excel")
    @GetMapping("exportBigData")
    public void exportData(HttpServletResponse response,
                           @RequestParam(required = false) String no,
                           @RequestParam(required = false) String name){
        StudentReqDTO reqDTO = new StudentReqDTO();
        reqDTO.setNo(no);
        reqDTO.setName(name);
        studentExcelExport.exportWithBigData(response, "学生列表", reqDTO);
    }

    @ApiOperation(value = "导出小数据量数据到excel", notes = "导出小数据量数据到excel")
    @GetMapping("exportSmallData")
    public void exportSmallData(HttpServletResponse response,
                           @RequestParam(required = false) String no,
                           @RequestParam(required = false) String name){
        StudentReqDTO reqDTO = new StudentReqDTO();
        reqDTO.setNo(no);
        reqDTO.setName(name);
        studentExcelExport.exportWithSmallData(response, "学生列表", reqDTO);
    }

    /**
     * 循环设置要添加的数据，最终封装到list集合中
     */
    private static List<StudentDO> getData(){
        List<StudentDO> demoData = new ArrayList<>();
        for (int i = 0; i < 1000000; i++) {
            StudentDO data = new StudentDO();
            data.setNo("100"+i);
            data.setName("Eric" + i);
            demoData.add(data);
        }
        return demoData;
    }

    /*
    public static void main(String[] args) {
        //实现excel写的操作
        //1、设置写入文件夹地址和excel文件名称
        String fileName = "/Users/guohg/project/dancechar/write.xlsx";
        //2、调用EasyExcel里面方法实现写的操作
        //write两个参数：参数1：文件路径名称   参数2：参数实体类class
        EasyExcel.write(fileName, StudentDO.class).sheet("学生列表").doWrite(getData());
    }
     */
}

service层

package com.litian.dancechar.examples.excel.service;

import cn.hutool.core.convert.Convert;
import cn.hutool.core.util.StrUtil;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.litian.dancechar.examples.excel.dao.entity.StudentDO;
import com.litian.dancechar.examples.excel.dao.inf.StudentDao;
import com.litian.dancechar.framework.common.base.RespResult;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.util.List;
import java.util.Map;


/**
 * 学生服务
 *
 * @author kyle0432
 * @date 2024/02/25 12:30
 */
@Service
@Slf4j
@Transactional(rollbackFor = Exception.class)
public class StudentService extends ServiceImpl<StudentDao, StudentDO> {

    @Resource
    private StudentDao studentDao;

    public List<StudentDO> listPage(Map<String, Object> queryCondition, Integer pageNo, Integer pageSize){
        String no = Convert.toStr(queryCondition.get("no"));
        String name = Convert.toStr(queryCondition.get("name"));
        return studentDao.findByPage(no, name, (pageNo-1)*pageSize, pageSize);
    }

    public List<StudentDO> findList(Map<String, Object> queryCondition){
        LambdaQueryWrapper<StudentDO> lambdaQueryWrapper = Wrappers.lambdaQuery();
        String no = Convert.toStr(queryCondition.get("no"));
        if(StrUtil.isNotEmpty(no)){
            lambdaQueryWrapper.eq(StudentDO::getNo, no);
        }
        String name = Convert.toStr(queryCondition.get("name"));
        if(StrUtil.isNotEmpty(name)){
            lambdaQueryWrapper.eq(StudentDO::getName, name);
        }
        lambdaQueryWrapper.orderByDesc(StudentDO::getId);
        return studentDao.selectList(lambdaQueryWrapper);
    }

    public Integer getTotalCount(){
        return this.count();
    }

    /**
     * 功能：批量插入数据
     */
    public RespResult<Boolean> saveStuListWithBatch(List<StudentDO> studentList) {
        this.saveBatch(studentList);
        return RespResult.success(true);
    }
}

dao层

package com.litian.dancechar.examples.excel.dao.inf;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.litian.dancechar.examples.excel.dao.entity.StudentDO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

import java.util.List;


/**
 * 学生Dao
 *
 * @author kyle0432
 * @date 2024/02/25 12:35
 */
@Mapper
public interface StudentDao extends BaseMapper<StudentDO> {

    List<StudentDO> findByPage(@Param("no")String no, @Param("name")String name,
                               @Param("pageBegin") Integer pageBegin, @Param("pageSize") Integer pageSize);
}

entity层

package com.litian.dancechar.examples.excel.dao.entity;

import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
import com.alibaba.excel.annotation.ExcelProperty;
import com.baomidou.mybatisplus.annotation.TableName;
import com.litian.dancechar.framework.common.mybatis.BaseDO;
import lombok.Data;
import lombok.EqualsAndHashCode;

import java.io.Serializable;

/**
 * 学生DO
 *
 * @author kyle0432
 * @date 2024/02/25 12:48
 */
@Data
@TableName("t_student")
@EqualsAndHashCode(callSuper = false)
@ExcelIgnoreUnannotated
public class StudentDO extends BaseDO implements Serializable {
    private static final long serialVersionUID = 1L;

    /**
     * 主键
     */
    private String id;

    /**
     * 工号
     */
    @ExcelProperty("学生编号")
    private String no;

    /**
     * 姓名
     */
    @ExcelProperty("学生姓名")
    private String name;
}

dto层

package com.litian.dancechar.examples.excel.dto;

import com.litian.dancechar.framework.common.base.BasePage;
import lombok.Data;
import lombok.EqualsAndHashCode;

import java.io.Serializable;
import java.util.List;

/**
 * 学生请求对象
 *
 * @author kyle0432
 * @date 2024/02/25 13:18
 */
@Data
@EqualsAndHashCode(callSuper = false)
public class StudentReqDTO extends BasePage implements Serializable {
    private static final long serialVersionUID = 1L;

    private String id;

    private List<String> ids;

    /**
     * 工号
     */
    private String no;

    /**
     * 姓名
     */
    private String name;
}




package com.litian.dancechar.examples.excel.dto;

import com.litian.dancechar.framework.common.base.BaseRespDTO;
import lombok.Data;
import lombok.EqualsAndHashCode;

import java.io.Serializable;

/**
 * 学生返回對象
 *
 * @author kyle0432
 * @date 2024/02/25 13:20
 */
@Data
@EqualsAndHashCode(callSuper = false)
public class StudentRespDTO extends BaseRespDTO implements Serializable {
    private static final long serialVersionUID = 1L;

    private String id;

    /**
     * 工号
     */
    private String no;

    /**
     * 姓名
     */
    private String name;

}

import

package com.litian.dancechar.examples.excel.util;

import com.litian.dancechar.examples.excel.dao.entity.StudentDO;
import com.litian.dancechar.examples.excel.service.StudentService;
import com.litian.dancechar.framework.excel.core.AbstractExcelImport;

import java.util.List;

/**
 * 学生excel百万数据导入监听
 *
 * @author kyle0432
 * @date 2024/02/25 13:30
 */
public class StudentExcelImport extends AbstractExcelImport<StudentDO> {
    private StudentService studentService;

    public StudentExcelImport(){}

    public StudentExcelImport(StudentService studentService){
        this.studentService = studentService;
    }

    @Override
    protected  void doSaveData(List<StudentDO> data) {
        studentService.saveStuListWithBatch(data);
    }
}

export

package com.litian.dancechar.examples.excel.util;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.collection.CollUtil;
import com.litian.dancechar.examples.excel.dao.entity.StudentDO;
import com.litian.dancechar.examples.excel.dto.StudentReqDTO;
import com.litian.dancechar.examples.excel.service.StudentService;
import com.litian.dancechar.framework.excel.core.AbstractExcelExport;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.util.*;

/**
 * 学生数据导入excel监听(支持百万数据导入导出)
 *
 * @author kyle0432
 * @date 2024/02/25 14:30
 */
@Component
public class StudentExcelExport extends AbstractExcelExport<StudentDO> {
    @Resource
    private StudentService studentService;

    /**
     * 百万数据导出excel
     */
    public void exportWithBigData(HttpServletResponse response, String fileName, StudentReqDTO reqDTO){
        List<List<String>> head = new ArrayList<List<String>>();
        head.add(Collections.singletonList("学生编号"));
        head.add(Collections.singletonList("学生姓名"));
        this.exportWithBigData(response, fileName, head, BeanUtil.beanToMap(reqDTO));
    }

    /**
     * 小数据量数据导出excel
     */
    public void exportWithSmallData(HttpServletResponse response, String fileName, StudentReqDTO reqDTO){
        List<List<String>> head = new ArrayList<List<String>>();
        head.add(Collections.singletonList("学生编号"));
        head.add(Collections.singletonList("学生姓名"));
        this.exportSmallData(response, fileName, head, studentService.findList(BeanUtil.beanToMap(reqDTO)));
    }

    @Override
    protected Integer dataTotalCount() {
        return studentService.getTotalCount();
    }

    @Override
    protected Integer eachSheetTotalCount() {
        return 500000;
    }

    @Override
    protected Integer eachTimesWriteSheetTotalCount() {
        return 5000;
    }

    @Override
    protected void buildDataList(List<List<String>> resultList, Map<String, Object> queryCondition,
                                 Integer pageNo, Integer pageSize) {
        List<StudentDO> studentList = studentService.listPage(queryCondition, pageNo, pageSize);
        if(CollUtil.isNotEmpty(studentList)){
            studentList.forEach(item -> {
                resultList.add(Arrays.asList(item.getNo(), item.getName()));
            });
        }
    }
}

core

AbstractExcelExport:

package com.litian.dancechar.framework.excel.core;

import cn.hutool.core.util.IdUtil;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.WriteTable;
import com.alibaba.excel.write.metadata.WriteWorkbook;
import com.litian.dancechar.framework.excel.util.ExcelUtil;
import lombok.extern.slf4j.Slf4j;

import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.Map;

/**
 * excel数据导出监听抽象类(采用模版模式, 支持百万数据处理方式)
 *
 * @author kyle0432
 * @date 2024/02/25 13:26
 */
@Slf4j
public abstract class AbstractExcelExport<T>  {

    /**
     * 导出小数据量(百万以下)
     */
    protected void exportSmallData(HttpServletResponse response, String fileName, List<List<String>> head, Collection<T> data){
        OutputStream outputStream = null;
        try {
            long startTime = System.currentTimeMillis();
            outputStream = response.getOutputStream();
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码
            String sheetName = fileName;
            fileName = URLEncoder.encode(fileName+ IdUtil.objectId(), "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName
                    + ExcelTypeEnum.XLSX.getValue());
            ExcelUtil.write(outputStream, sheetName, head, data);
            log.info("导出所用时间:{}", (System.currentTimeMillis() - startTime) / 1000 + "秒");
        } catch (Exception e){
            log.error(e.getMessage(), e);
        }finally {
            if (outputStream != null) {
                try {
                    outputStream.close();
                } catch (Exception e) {
                    log.error(e.getMessage(),e);
                }
            }
        }
    }

    /**
     * 导出大数据量(百万级别)
     */
    protected void exportWithBigData(HttpServletResponse response, String fileName, List<List<String>> head,
                                  Map<String, Object> queryCondition) {
        // 总的记录数
        Integer totalCount = dataTotalCount();
        // 每一个Sheet存放的数据
        Integer sheetDataRows = eachSheetTotalCount();
        // 每次写入的数据量20w
        Integer writeDataRows = eachTimesWriteSheetTotalCount();
        if(totalCount < sheetDataRows){
            sheetDataRows = totalCount;
        }
        if(sheetDataRows < writeDataRows){
            writeDataRows = sheetDataRows;
        }
        doExport(response, fileName, head, queryCondition, totalCount, sheetDataRows, writeDataRows);
    }

    /**
     * 计算导出数据的总数
     */
    protected abstract Integer dataTotalCount();

    /**
     * 每一个sheet存放的数据总数
     */
    protected abstract Integer eachSheetTotalCount();

    /**
     * 每次写入sheet的总数
     */
    protected abstract Integer eachTimesWriteSheetTotalCount();

    protected abstract void buildDataList(List<List<String>> resultList, Map<String, Object> queryCondition,
                                          Integer pageNo, Integer pageSize);

    private void doExport(HttpServletResponse response, String fileName, List<List<String>> head,
                          Map<String, Object> queryCondition, Integer totalCount, Integer sheetDataRows,
                          Integer writeDataRows){
        OutputStream outputStream = null;
        try {
            long startTime = System.currentTimeMillis();
            outputStream = response.getOutputStream();
            WriteWorkbook writeWorkbook = new WriteWorkbook();
            writeWorkbook.setOutputStream(outputStream);
            writeWorkbook.setExcelType(ExcelTypeEnum.XLSX);
            ExcelWriter writer = new ExcelWriter(writeWorkbook);
            WriteTable table = new WriteTable();
            table.setHead(head);
            // 计算需要的Sheet数量
            int sheetNum = totalCount % sheetDataRows == 0 ? (totalCount / sheetDataRows) : (totalCount / sheetDataRows + 1);
            // 计算一般情况下每一个Sheet需要写入的次数(一般情况不包含最后一个sheet,因为最后一个sheet不确定会写入多少条数据)
            int oneSheetWriteCount = totalCount > sheetDataRows ? sheetDataRows / writeDataRows :
                    totalCount % writeDataRows > 0 ? totalCount / writeDataRows + 1 : totalCount / writeDataRows;
            // 计算最后一个sheet需要写入的次数
            int lastSheetWriteCount = totalCount % sheetDataRows == 0 ? oneSheetWriteCount :
                    (totalCount % sheetDataRows % writeDataRows == 0 ? (totalCount / sheetDataRows / writeDataRows) :
                            (totalCount / sheetDataRows / writeDataRows + 1));
            // 分批查询分次写入
            List<List<String>> dataList = new ArrayList<>();
            for (int i = 0; i < sheetNum; i++) {
                // 创建Sheet
                WriteSheet sheet = new WriteSheet();
                sheet.setSheetNo(i);
                sheet.setSheetName(sheetNum == 1 ? fileName : fileName + i);
                // 循环写入次数: j的自增条件是当不是最后一个Sheet的时候写入次数为正常的每个Sheet写入的次数,如果是最后一个就需要使用计算的次数lastSheetWriteCount
                for (int j = 0; j < (i != sheetNum - 1 || i==0 ? oneSheetWriteCount : lastSheetWriteCount); j++) {
                    // 集合复用,便于GC清理
                    dataList.clear();
                    buildDataList(dataList, queryCondition, j + 1 + oneSheetWriteCount * i, writeDataRows);
                    // 写数据
                    writer.write(dataList, sheet, table);
                }
            }
            response.setHeader("Content-Disposition", "attachment;filename="
                    + new String((fileName+ IdUtil.objectId()).getBytes("gb2312"),
                    "ISO-8859-1") + ".xlsx");
            response.setContentType("multipart/form-data");
            response.setCharacterEncoding("utf-8");
            writer.finish();
            outputStream.flush();
            log.info("导出所用时间:{}", (System.currentTimeMillis() - startTime) / 1000 + "秒");
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        } finally {
            if (outputStream != null) {
                try {
                    outputStream.close();
                } catch (Exception e) {
                    log.error(e.getMessage(),e);
                }
            }
        }
    }
}

AbstractExcelImport:

package com.litian.dancechar.framework.excel.core;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.litian.dancechar.framework.excel.constants.ExcelConstants;
import lombok.extern.slf4j.Slf4j;

import java.util.ArrayList;
import java.util.List;

/**
 * excel数据导入监听抽象类(采用模版模式, 百万数据处理方式)
 *
 * @author kyle0432
 * @date 2024/02/25 15:26
 */
@Slf4j
public abstract class AbstractExcelImport<T> extends AnalysisEventListener<T> {
    /**
     * 自定义用于暂时存储data。
     */
    private List<T> dataList = new ArrayList<>();

    @Override
    public void invoke(T object, AnalysisContext context) {
        //数据存储
        dataList.add(object);
        //百万数据处理方式，达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (dataList.size() >= ExcelConstants.SAVE_DB_MAX_SIZE) {
            saveData();
            log.info("数据量达到{}条,保存内存数据到db", ExcelConstants.SAVE_DB_MAX_SIZE);
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        log.info("解析excel数据完成！！！");
        saveData();
        log.info("保存内存数据到db");
    }

    /**
     * 保存数据到 DB
     */
    private void saveData() {
        if (dataList.size() > 0) {
            doSaveData(dataList);
            dataList.clear();
        }
    }
    protected abstract void doSaveData(List<T> data);
}

constants

package com.litian.dancechar.framework.excel.constants;

/**
 * excel常量类
 *
 * @author kyle0432
 * @date 2024/02/25 13:26
 */
public class ExcelConstants {

    /**
     * 保存到DB最大数量
     */
    public static final int SAVE_DB_MAX_SIZE = 100000;
}

util:导入导出工具类

package com.litian.dancechar.framework.excel.util;

import com.alibaba.excel.EasyExcel;
import com.litian.dancechar.framework.excel.core.AbstractExcelImport;
import lombok.extern.slf4j.Slf4j;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.Collection;
import java.util.List;


@Slf4j
public class ExcelUtil {

    /**
     * 从Excel中指定sheet读取文件
     */
    public static <T> void readExcel(final InputStream inputStream, Class<?> clazz,
                                     AbstractExcelImport<T> listener, Integer sheetNo) {
        if(sheetNo != null){
            EasyExcel.read(inputStream, clazz, listener).sheet(sheetNo).doRead();
        }
    }

    /**
     * 从Excel中读取文件(第一个sheet)
     */
    public static <T> void readExcel(final InputStream inputStream, Class<?> clazz, AbstractExcelImport<T> listener) {
        EasyExcel.read(inputStream, clazz, listener).sheet().doRead();
    }

    /**
     * 写入到指定excel文件
     */
    public static <T> void write(String fileName, String sheetName, Class<T> head, Collection<T> datas){
        EasyExcel.write(fileName).head(head).sheet(sheetName).doWrite(datas);
    }

    /**
     * 写入到指定excel的sheet文件
     */
    public static <T> void write(OutputStream outputStream, String sheetName, Class<T> head, Collection<T> data){
        EasyExcel.write(outputStream).head(head).sheet(sheetName).doWrite(data);
    }

    /**
     * 写入到指定excel的sheet文件
     */
    public static <T> void write(OutputStream outputStream, Integer sheetNo, String sheetName, Class<T> head, Collection<T> data){
        EasyExcel.write(outputStream).head(head).sheet(sheetNo).sheetName(sheetName).doWrite(data);
    }

    /**
     * 写入到指定excel文件
     */
    public static <T> void write(String fileName, String sheetName, List<List<String>> head, Collection<T> datas){
        EasyExcel.write(fileName).head(head).sheet(sheetName).doWrite(datas);
    }

    /**
     * 写入到指定excel的sheet文件
     */
    public static <T> void write(OutputStream outputStream, String sheetName, List<List<String>> head, Collection<T> data){
        EasyExcel.write(outputStream).head(head).sheet(sheetName).doWrite(data);
    }

    /**
     * 写入到指定excel的sheet文件
     */
    public static <T> void write(OutputStream outputStream, Integer sheetNo, String sheetName, List<List<String>> head,
                                 Collection<T> data){
        EasyExcel.write(outputStream).head(head).sheet(sheetNo).sheetName(sheetName).doWrite(data);
    }
}