EasyExcel和POI具体使用及对比

easyExcel和POI使用教程及性能对比

山羊夫子

8178人浏览 · 2022-06-22 17:29:54

山羊夫子 · 2022-06-22 17:29:54 发布

1.简介
操作execl最常用的就是easyExcel和POI
场景：
(1)数据导出写到excel
(2)读excel文件内容到数据库
普及知识点：读写速度：cpu>内存>磁盘

2.POI使用
官网：https://poi.apache.org/components/index.html
(1).可操作文件类型：
在这里插入图片描述
(2)导入依赖：

<!--xls(03)-->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.2</version>
</dependency>
<!--xlsx(07)-->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.2</version>
</dependency>

* 03版的Excel最多可以放入65536条数据，但是新版的是没有条数限制的
* 03版的后缀是xls，07版是xlsx

(3)创建步骤
工作蒲–工作表—行—列（单元格）
在这里插入图片描述
(4)读写案例：
①.写：

03版大数据写HSSF：
缺点：但是只能写入65535条数据
优点：过程中写入缓存，不操作磁盘，最后再一次性导入磁盘，速度快

long startTime = System.currentTimeMillis();

HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet();

for (int i = 0; i < 65536; i++) {
    Row row = sheet.createRow(i);
    for (int j = 0; j < 10; j++) {
        Cell cell = row.createCell(j);
        cell.setCellValue(j);
    }
}

FileOutputStream outputStream = new FileOutputStream(PATH + "/大数据03.xls");
workbook.write(outputStream);
System.out.println("03版表格生成成功");
outputStream.close();

long endTime = System.currentTimeMillis();

System.out.println("03版消耗时间：" + (endTime - startTime));

07版大数据写XSSF：
缺点：写数据时速度非常慢，非常耗内存，容易内存溢出(OOM)，如100万。
优点：可以写较大数据量，如20万。

ong startTime = System.currentTimeMillis();

XSSFWorkbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet();
List<Object> datas = ArrayList<>();
for (int i = 0; i < datas.size(); i++) {
    Row row = sheet.createRow(i);
    for (int j = 0; j < 10; j++) {
        Cell cell = row.createCell(j);
        cell.setCellValue(j);
    }
}

FileOutputStream outputStream = new FileOutputStream(PATH + "/大数据07.xlsx");
workbook.write(outputStream);
System.out.println("07版表格生成成功");
outputStream.close();

long endTime = System.currentTimeMillis();

System.out.println("07版消耗时间：" + (endTime - startTime));

快速版SXSSF:
优点：可以写非常大量的数据，如100万条甚至更多，写数据速度快，占用内存更少。
注意:
会产生临时文件，需要清理临时文件
默认先写100条记录保存在内存中，超过数量最前面的数据被写入临时文件，使用new SXSSFWorkbook(数量)可以自定义

long startTime = System.currentTimeMillis();

SXSSFWorkbook workbook = new SXSSFWorkbook();
Sheet sheet = workbook.createSheet();
List<Object> datas = ArrayList<>();
for (int i = 0; i < datas.size(); i++) {
    Row row = sheet.createRow(i);
    for (int j = 0; j < 10; j++) {
        Cell cell = row.createCell(j);
        cell.setCellValue(j);
    }
}

FileOutputStream outputStream = new FileOutputStream(PATH + "/大数据07Super.xlsx");
workbook.write(outputStream);

// 清除临时文件
workbook.dispose();

System.out.println("07Super版表格生成成功");
outputStream.close();
long endTime = System.currentTimeMillis();
System.out.println("07Super版消耗时间：" + (endTime - startTime));

②读：

//获取文件流
FileInputStream inputStream = new FileInputStream(PATH + "godfrey日常统计表03.xls");

//1.创建工作簿,使用excel能操作的这边都看看操作
Workbook workbook = new HSSFWorkbook(inputStream);
//2.得到表
Sheet sheet = workbook.getSheetAt(0);
//3.得到行
Row row = sheet.getRow(0);
//4.得到列
Cell cell = row.getCell(0);

//getStringCellValue获取字符串类型
System.out.println(cell.getStringCellValue());
inputStream.close();

3.EasyExcel使用
官网：https://alibaba-easyexcel.github.io/docs/current/api/
(1)导入依赖：

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

(2)创建步骤：
在这里插入图片描述

(3)读写案例：
①写：
step1：写入数据

public class ExcelUtil {
    public static OutputStream getOutputStream(String fileName, HttpServletResponse response)
            throws Exception{
        try{
            fileName = URLEncoder.encode(fileName,"utf-8");
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            //此处指定了文件类型为xls，如果是xlsx的，请自行替换修改
            response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".xls");
            response.setHeader("Pragma", "public");
            response.setHeader("Cache-Control", "no-store");
            response.addHeader("Cache-Control", "max-age=0");
            return response.getOutputStream();
        } catch (IOException e){
            throw new Exception("导出文件失败！");
        }
    }
    public static void writeExcel(HttpServletResponse response, List<? extends BaseRowModel> list, String fileName,
                                  String sheetName, Class clazz) throws Exception {
        ExcelWriter writer = new ExcelWriter(getOutputStream(fileName, response), ExcelTypeEnum.XLS);
        Sheet sheet = new Sheet(1, 0, clazz);
        sheet.setSheetName(sheetName);
        writer.write(list, sheet);
        writer.finish();
    }
}

step2:Controller调用

@RequestMapping(value = "/file/testExcelDownload")
    public void testExcelDownload(HttpServletRequest request,HttpServletResponse response){
        //以下信息从数据库中查出
        List<ExcelInfo> excelInfos = new ArrayList<>();
        ExcelInfo info1 = new ExcelInfo();
        ExcelInfo info2 = new ExcelInfo();
        excelInfos.add(info1);
        excelInfos.add(info2);
        info1.setIssuerName("name1");
        info1.setRiskLevel("level1");
        info2.setIssuerName("name1");
        info2.setRiskLevel("level1");
        try {
            String fileName = "excelInfo";
            String sheetName = "sheet1";
            ExcelUtil.writeExcel(response, excelInfos, fileName, sheetName, ExcelInfo.class);
        } catch(Exception e){
            log.error("模板下载失败",e);
        }
    }

②读：
step1:创建用户信息类

@Data
public class UserInfo extends BaseRowModel {
    @ExcelProperty(index = 0)
    private String name;
    @ExcelProperty(index = 1)
    private int age;
    @ExcelProperty(index = 2)
    private String address;
}

step2:创建AnalysisEventListener子类

/**
 * 每解析一行会回调invoke()方法。
 * 整个excel解析结束会执行doAfterAllAnalysed()方法
 */
//有个很重要的点   不能被spring管理,要每次读取excel都要new。
//这边就会有一个问题：如果UserInfoDataListener中需要用到Spring中的主键怎么办？
public class UserInfoDataListener extends AnalysisEventListener<UserInfo> {
    Logger logger = LoggerFactory.getLogger(UserInfoDataListener.class);
    //每次读取100条数据就进行保存操作
    private static final int BATCH_COUNT = 100;
    //由于每次读都是新new UserInfoDataListener的，所以这个list不会存在线程安全问题
    List<UserInfo> list = new ArrayList<>();
    //这个组件是Spring中的组件，这边推荐两种方法注入这个组件
    //第一种就是提供一个UserInfoDataListener的构造方法，这个方法提供一个参数是UserInfoDataListener类型
    //另外一种方法就是将 UserInfoDataListener 这个类定义成 UserService 实现类的内部类（推荐这种方式）
    //private UserService userService;
    @Override
    public void invoke(UserInfo data, AnalysisContext analysisContext) {
        logger.info("解析到一条数据:{}", JSON.toJSONString(data));
        list.add(data);
        if (list.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            list.clear();
        }
    }
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();
        logger.info("所有数据解析完成！");
    }
    private void saveData() {
        logger.info("{}条数据，开始存储数据库！", list.size());
        //保存数据
        //userService.save(list);
        logger.info("存储数据库成功！");
    }
}

step3:读取excel

public class EasyExcelDemo {
    public static void main(String[] args) throws Exception {
        InputStream fis = new FileInputStream("D:\\UserInfo.xlsx");
        AnalysisEventListener listener = new UserInfoDataListener();
        ExcelReader excelReader = EasyExcel.read(fis, UserInfo.class, listener).build();
        ReadSheet readSheet = EasyExcel.readSheet(0).build();
        ReadSheet readSheet2 = EasyExcel.readSheet(1).build();
        excelReader.read(readSheet);
        // 这里千万别忘记关闭，读的时候会创建临时文件，到时磁盘会崩的
        excelReader.finish();
    }
}

4.POI与EasyExcel对比
在这里插入图片描述

POI存在的问题：非常的消耗内存；
EasyExcel 遇到再大的excel都不会出现内存溢出的问题，能够将一个原本3M的excel文件，POI来操作将会占用内存100M，使用EasyExcel降低到几KB，使用起来更加简单。
poi读 1、创建xsshworkbook/hssfworkbook (inputstream in)
2、读取sheet
3、拿到当前sheet所有行row
4、通过当前行去拿到对应的单元格的值。
而easyExcel能大大减少占用内存的主要原因是在解析Excel时没有将文件数据一次性全部加载到内存中，而是从磁盘上一行行读取数据，逐个解析。