pol和easyExcel

最新推荐文章于 2023-05-24 19:28:04 发布

Heri.MG

最新推荐文章于 2023-05-24 19:28:04 发布

阅读量393

点赞数

分类专栏： java 文章标签： poi

本文链接：https://blog.csdn.net/weixin_44532692/article/details/105894613

版权

java 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

Apache POI概述

官网：https://poi.apache.org/

Apache POI 是 Apache软件基金会的开放源码函式库，POI 提供API给Java程序对Microsoft office 格式档案读和写的功能。

简单的说就是提供了操作office 文件的api

API

HSSF －提供读写[Microsoft Excel](https://baike.baidu.com/item/Microsoft Excel) XLS格式档案的功能。
XSSF －提供读写Microsoft Excel OOXML XLSX格式档案的功能。
HWPF －提供读写[Microsoft Word](https://baike.baidu.com/item/Microsoft Word) DOC格式档案的功能。
HSLF －提供读写Microsoft PowerPoint格式档案的功能。
HDGF －提供读[Microsoft Visio](https://baike.baidu.com/item/Microsoft Visio)格式档案的功能
HPBF －提供读[Microsoft Publisher](https://baike.baidu.com/item/Microsoft Publisher)格式档案的功能。
HSMF －提供读[Microsoft Outlook](https://baike.baidu.com/item/Microsoft Outlook)格式档案的功能。

EasyExcel概述

GitHub地址：https://github.com/alibaba/easyexcel

原生的api用起来特别的麻烦，而且还有可能出现一些错误，比如：内存溢出等等……

Java解析、生成Excel比较有名的框架有Apache poi、jxl。但他们都存在一个严重的问题就是非常的耗内存，poi有一套SAX模式的API可以一定程度的解决一些内存溢出的问题，但POI还是有一些缺陷，比如07版Excel解压缩以及解压后存储都是在内存中完成的，内存消耗依然很大。easyexcel重写了poi对07版Excel的解析，能够原本一个3M的excel用POI sax依然需要100M左右内存降低到几M，并且再大的excel不会出现内存溢出，03版依赖POI的sax模式。在上层做了模型转换的封装，让使用者更加简单方便。

实践测试

实现工作簿的

POI写

引入所需依赖

<!--        03版xls-->

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.9</version>
        </dependency>
<!--        07版 xlsx-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.9</version>
        </dependency>

因现在常用的有 excel 03 以及 07 版本他们是有区别的

后缀不同 03 xls 07 xlsx
03 最大行号限制65536 07 无限制6

03 版本

 public static void test03() throws Exception{
        // 1.创建工作簿
        Workbook workbook=new HSSFWorkbook();
        // 2.创建工作表
        Sheet sheet = workbook.createSheet("班级信息");
        // 3.创建行
        Row row1 =sheet.createRow(0);
        // （1，A）
        // 4.创建列
        Cell cell1A = row1.createCell(0);
        cell1A.setCellValue("编号");
        // （1，B）
        Cell cell1B = row1.createCell(1);
        cell1B.setCellValue("班级");
        // 3.创建行
        Row row2 =sheet.createRow(1);
        // （1，A）
        // 4.创建列
        // 添加数据
        Cell cell2A = row2.createCell(0);
        cell2A.setCellValue("01");
        Cell cell2B = row2.createCell(1);
        cell2B.setCellValue("WuTea");
        FileOutputStream stream = new FileOutputStream(PATH+"班级信息表03.xls");// 03 版本 后缀为 .xls
        workbook.write(stream);
        stream.close();// 关闭流
        System.out.println("【班级信息表03.xls】-创建成功！");

    }

运行结果：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3Vmj7lSE-1588421216187)(https://s1.ax1x.com/2020/05/01/JOaQJJ.png)]

07 版本

public static void test07() throws Exception{
    // 1.创建工作簿
    Workbook workbook=new XSSFWorkbook();
    // 2.创建工作表
    Sheet sheet = workbook.createSheet("班级信息");
    // 3.创建行
    Row row1 =sheet.createRow(0);
    // （1，A）
    // 4.创建列
    Cell cell1A = row1.createCell(0);
    cell1A.setCellValue("编号");
    // （1，B）
    Cell cell1B = row1.createCell(1);
    cell1B.setCellValue("班级");
    // 3.创建行
    Row row2 =sheet.createRow(1);
    // （2，A）
    // 4.创建列
    // 添加数据
    Cell cell2A = row2.createCell(0);
    cell2A.setCellValue("01");
    // （2，B）
    Cell cell2B = row2.createCell(1);
    cell2B.setCellValue("WuTea");
    FileOutputStream stream = new FileOutputStream(PATH+"班级信息表07.xlsx");// 03 版本 后缀为 .xls
    workbook.write(stream);
    stream.close();// 关闭流
    System.out.println("【班级信息表07.xls】-创建成功！");

}

运行结果

大数据量下操作

可能发生的问题

03 版本超出最大行数导致报错

Exception in thread “main” java.lang.IllegalArgumentException: Invalid row number (65536) outside allowable range (0…65535)

所以一边使用这种方式插入大数据

    public static void  test07BigData() throws Exception{
        long start = System.currentTimeMillis();
        // 1.创建工作簿
        Workbook workbook=new XSSFWorkbook();
        // 2.创建工作表
        Sheet sheet = workbook.createSheet("班级信息");
        Row row1 = sheet.createRow(0);
        Cell cell1A = row1.createCell(0);
        cell1A.setCellValue("编号");
        Cell cell1B = row1.createCell(1);
        cell1B.setCellValue("班级");
        for (int i=0;i<1000000;i++) {//模拟100万数据插入
            Row row = sheet.createRow(i+1);
            for (int j = 0; j < 2; j++) {
                Cell cell = row.createCell(j);
                if (j==1){
//                    System.out.println(j);
                    cell.setCellValue(MessageFormat.format("name{0}", j));
                }else
                cell.setCellValue(j);
            }
        }
        FileOutputStream fileOutputStream = new FileOutputStream(PATH + "测试07.xlsx");
        workbook.write(fileOutputStream);
        fileOutputStream.close();
        long end = System.currentTimeMillis();
        System.out.println("所用时间 "+(double)(end-start)/1000+"s");
    }

控制台输出：

但是看着速度有点慢

还有一个加速的api SXSSFWorkbook

 /**
     *
     */
    public static void  test07BigDatas() throws Exception{
        long start = System.currentTimeMillis();
        // 1.创建工作簿
        Workbook workbook=new SXSSFWorkbook();
        // 2.创建工作表
        Sheet sheet = workbook.createSheet("班级信息");
        Row row1 = sheet.createRow(0);
        Cell cell1A = row1.createCell(0);
        cell1A.setCellValue("编号");
        Cell cell1B = row1.createCell(1);
        cell1B.setCellValue("班级");
        for (int i=0;i<1000000;i++) {//模拟100万数据插入
            Row row = sheet.createRow(i+1);
            for (int j = 0; j < 2; j++) {
                Cell cell = row.createCell(j);
                if (j==1){
//                    System.out.println(j);
                    cell.setCellValue(MessageFormat.format("name{0}", j));
                }else
                    cell.setCellValue(j);
            }
        }
        FileOutputStream fileOutputStream = new FileOutputStream(PATH + "测试07s.xlsx");
        workbook.write(fileOutputStream);
        
        fileOutputStream.close();
        ((SXSSFWorkbook)workbook).dispose();
        long end = System.currentTimeMillis();
        System.out.println("所用时间 "+(double)(end-start)/1000+"s");
    }

控制台输出

优点：可以写非常大的数据量，如100万条甚至更多条，写数据速度快，占用更少的内存

注意：

过程中会产生临时文件，需要清理临时文件默认由100条记录被保存在内存中，如果超过这数量，则最前面的数据被写入临时文件如果想自定义内存中数据的数量，可以使用new SXSSFWorkbook ( 数量）

SXSSFWorkbook-来至官方的解释:实现"BigGridDemo"策略的流式XSSFWorkbook版本。这允许写入
非常大的文件而不会耗尽内存,因为任何时候只有可配置的行部分被保存在内存中。
请注意，仍然可能会消耗大量内存，这些内存基于您正在使用的功能,例如合并区域,注释… .仍然只存
储在内存中,因此如果广泛使用，可能需要大量内存。

POI 读

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-plMY3nFe-1588421216193)(https://s1.ax1x.com/2020/05/01/JXYZFJ.png)]

测试一：读取第一行数据

@Test
public void readTest() throws  Exception{
    FileInputStream inputStream = new FileInputStream(PATH + "班级信息.xls");
    Workbook workbook=new HSSFWorkbook(inputStream);
    Sheet sheet0 = workbook.getSheetAt(0);//通过下标 选择工作表
    // workbook.getSheet("");//通过名字
    Row row0 = sheet0.getRow(0);//获取第一行
    int cells = row0.getPhysicalNumberOfCells();//获取总列数
    System.out.println("一共有："+cells+"列");
    for (int cellNumber = 0;cellNumber <cells; cellNumber++) {
        Cell cell = row0.getCell(cellNumber);
        String stringCellValue = cell.getStringCellValue();
        System.out.print(stringCellValue+" | ");
    }
    inputStream.close();//关闭流
}

输出结果：

测试二：读取详细数据

  @Test
    public void readDetailTest() throws Exception {
        FileInputStream inputStream = new FileInputStream(PATH + "班级信息.xls");
        Workbook workbook = new HSSFWorkbook(inputStream);
        Sheet sheet0 = workbook.getSheetAt(0);//通过下标 选择工作表
        // workbook.getSheet("");//通过名字
//        Row row0 = sheet0.getRow(0);//获取第一行
        //获取行数
        int rows = sheet0.getPhysicalNumberOfRows();
        for (int rowNumber = 0; rowNumber < rows; rowNumber++) {
            Row row = sheet0.getRow(rowNumber);
            int cells = row.getPhysicalNumberOfCells();//获取总列数
//            System.out.println("一共有：" + cells + "列");
            for (int cellNumber = 0; cellNumber < cells; cellNumber++) {
                Cell cell = row.getCell(cellNumber);
                String stringCellValue = cell.getStringCellValue();
                System.out.print(stringCellValue + " | ");
            }
            System.out.println();
        }
        inputStream.close();//关闭流
    }

发现报错了

原因：没有进行判断类型，因为一开始第一行所有数据的都是 String类型，所以可以正常输出，但是后面有数字，布尔……

下面编写类型判断：

  @Test
    public void readDetailTest() throws Exception {
        FileInputStream inputStream = new FileInputStream(PATH + "班级信息.xls");
        Workbook workbook = new HSSFWorkbook(inputStream);
        Sheet sheet0 = workbook.getSheetAt(0);//通过下标 选择工作表
        // workbook.getSheet("");//通过名字
//        Row row0 = sheet0.getRow(0);//获取第一行
        //获取行数
        int rows = sheet0.getPhysicalNumberOfRows();
        for (int rowNumber = 0; rowNumber < rows; rowNumber++) {
            Row row = sheet0.getRow(rowNumber);
            int cells = row.getPhysicalNumberOfCells();//获取总列数
//            System.out.println("一共有：" + cells + "列");
            for (int cellNumber = 0; cellNumber < cells; cellNumber++) {
                Cell cell = row.getCell(cellNumber);
                String stringCellValue = getValue(cell);
                System.out.print(stringCellValue + " | ");
            }
            System.out.println();
        }
        inputStream.close();//关闭流
    }

    /**
     * 判断单元格中的数据类型
     *
     * @return 统一转成字符串格式返回
     */
    public static String getValue(Cell cell) {
        String cellValue = "";
        if (cell != null) {
            switch (cell.getCellType()) {
                case HSSFCell.CELL_TYPE_STRING:// 字符串类型
                    cellValue = cell.getStringCellValue();
                    break;
                case HSSFCell.CELL_TYPE_NUMERIC:// 数字： 日期 和 正常数字
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        Date date = cell.getDateCellValue();
                        String s = new DateTime(date).toString("yyyy-mm-dd");
                        cellValue = String.valueOf(s);
                    } else {
                        cellValue = String.valueOf(cell.getNumericCellValue());
                    }
                    break;
                case HSSFCell.CELL_TYPE_BOOLEAN:// 布尔
                    cellValue=String.valueOf(cell.getBooleanCellValue());
                    break;
                case HSSFCell.CELL_TYPE_BLANK:// 空
                    break;

            }
        }
        return cellValue;
    }

输出结果：

编号 | 姓名 | 年龄 | 出生日期 | 身高 | 是否在读|
A01 | 张三 | 21.0 | 1999-00-04 | 170com | true |
A02 | 张三 | 22.0 | 1998-00-05 | 171com | true |
A03 | 张三 | 23.0 | 1998-00-06 | 172com | true |
A04 | 张三 | 24.0 | 1998-00-07 | 173com | true |
A05 | 张三 | 25.0 | 1998-00-08 | 174com | true |
A06 | 张三 | 26.0 | 1998-00-09 | 175com | true |
A07 | 张三 | 27.0 | 1998-00-10 | 176com | true |
A08 | 张三 | 28.0 | 1998-00-11 | 177com | false |
A09 | 张三 | 29.0 | 1998-00-12 | 178com | true |
A10 | 张三 | 30.0 | 1998-00-13 | 179com | true |
A11 | 张三 | 31.0 | 1998-00-14 | 180com | false |
A12 | 张三 | 32.0 | 1998-00-15 | 181com | true |

测试三：公式读取（了解，很少用）

@Test
public void  testFormula() throws Exception {
    FileInputStream inputStream = new FileInputStream(PATH + "公式.xlsx");
    Workbook workbook = new XSSFWorkbook(inputStream);
    Sheet sheet0 = workbook.getSheetAt(0);//通过下标 选择工作表
    Row row = sheet0.getRow(5);
    Cell cell = row.getCell(0);
    XSSFFormulaEvaluator evaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
    int celltype=cell.getCellType();
    switch (celltype){
        case Cell.CELL_TYPE_FORMULA:
            String cellFormula = cell.getCellFormula();
            System.out.println(cellFormula);
            CellValue evaluate = evaluator.evaluate(cell);
            System.out.println(evaluate.formatAsString());//jieguo
            break;
    }

结果：

SUM(A1:A5)
122.0

EasyExcel 测试

最简单的写

https://www.yuque.com/easyexcel/doc/write

对象

@Data
public class DemoData {
    @ExcelProperty("字符串标题")
    private String string;
    @ExcelProperty("日期标题")
    private Date date;
    @ExcelProperty("数字标题")
    private Double doubleData;
    /**
     * 忽略这个字段
     */
    @ExcelIgnore
    private String ignore;
}

代码


 private List<DemoData> data() {
        List<DemoData> list = new ArrayList<DemoData>();
        for (int i = 0; i < 10; i++) {
            DemoData data = new DemoData();
            data.setString("字符串" + i);
            data.setDate(new Date());
            data.setDoubleData(0.56);
            list.add(data);
        }
        return list;
    }
@Test
public void simpleWrite() {

    // 写法1
    String fileName = PATH + "simpleWrite" + System.currentTimeMillis() + ".xlsx";
    // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
    // 如果这里想使用03 则 传入excelType参数即可
    EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data());
   // 写法2
	// fileName = PATH + "simpleWrite" + System.currentTimeMillis() + ".xlsx";
     // 这里 需要指定写用哪个class去写
	//ExcelWriter excelWriter = EasyExcel.write(fileName, DemoData.class).build();
    //        WriteSheet writeSheet = EasyExcel.writerSheet("模板").build();
	//        excelWriter.write(data(), writeSheet);
        // 千万别忘记finish 会帮忙关闭流
	//        excelWriter.finish();
 }

创建的结果

读

持久层

/**
 * 假设这个是你的DAO存储。当然还要这个类让spring管理，当然你不用需要存储，也不需要这个类。
 **/
public class DemoDAO {
    public void save(List<DemoData> list) {
        // 如果是mybatis,尽量别直接调用多次insert,自己写一个mapper里面新增一个方法batchInsert,所有数据一次性插入
    }
}

监听器

package cn.easyexcel;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;

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

// 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
public class DemoDataListener extends AnalysisEventListener<DemoData> {
    /**
     * 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 5;
    List<DemoData> list = new ArrayList<DemoData>();
    /**
     * 假设这个是一个DAO，当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。
     */
    private DemoDAO demoDAO;
    public DemoDataListener() {
        // 这里是demo，所以随便new一个。实际使用如果到了spring,请使用下面的有参构造函数
        demoDAO = new DemoDAO();
    }
    /**
     * 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
     *
     * @param demoDAO
     */
    public DemoDataListener(DemoDAO demoDAO) {
        this.demoDAO = demoDAO;
    }
    /**
     * 这个每一条数据解析都会来调用
     *
     * @param data
     *            one row value. Is is same as {@link AnalysisContext#readRowHolder()}
     * @param context
     */
    @Override
    public void invoke(DemoData data, AnalysisContext context) {
        list.add(data);
        System.out.println(JSON.toJSONString(data));
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (list.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            list.clear();
        }
    }
    /**
     * 所有数据解析完成了 都会来调用
     *
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();

    }
    /**
     * 加上存储数据库
     */
    private void saveData() {
        demoDAO.save(list);

    }
}

 @Test
    public void simpleRead() {
        // 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
        // 写法1：
        String fileName =PATH  +File.separator + "simpleWrite1588417799820.xlsx";
        // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
        EasyExcel.read(fileName,DemoData.class,new DemoDataListener()).sheet().doRead();
//        // 写法2：
//        fileName = PATH+ "demo" + File.separator + "demo.xlsx";
//        ExcelReader excelReader = EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).build();
//        ReadSheet readSheet = EasyExcel.readSheet(0).build();
//        excelReader.read(readSheet);
//        // 这里千万别忘记关闭，读的时候会创建临时文件，到时磁盘会崩的
//        excelReader.finish();
    }

控制台输出

{“date”:1588417800000,“doubleData”:0.56,“string”:“字符串0”}
{“date”:1588417800000,“doubleData”:0.56,“string”:“字符串1”}
{“date”:1588417800000,“doubleData”:0.56,“string”:“字符串2”}
{“date”:1588417800000,“doubleData”:0.56,“string”:“字符串3”}
{“date”:1588417800000,“doubleData”:0.56,“string”:“字符串4”}
{“date”:1588417800000,“doubleData”:0.56,“string”:“字符串5”}
{“date”:1588417800000,“doubleData”:0.56,“string”:“字符串6”}
{“date”:1588417800000,“doubleData”:0.56,“string”:“字符串7”}
{“date”:1588417800000,“doubleData”:0.56,“string”:“字符串8”}
里千万别忘记关闭，读的时候会创建临时文件，到时磁盘会崩的
// excelReader.finish();
}


控制台输出

{"date":1588417800000,"doubleData":0.56,"string":"字符串0"}
{"date":1588417800000,"doubleData":0.56,"string":"字符串1"}
{"date":1588417800000,"doubleData":0.56,"string":"字符串2"}
{"date":1588417800000,"doubleData":0.56,"string":"字符串3"}
{"date":1588417800000,"doubleData":0.56,"string":"字符串4"}
{"date":1588417800000,"doubleData":0.56,"string":"字符串5"}
{"date":1588417800000,"doubleData":0.56,"string":"字符串6"}
{"date":1588417800000,"doubleData":0.56,"string":"字符串7"}
{"date":1588417800000,"doubleData":0.56,"string":"字符串8"}
{"date":1588417800000,"doubleData":0.56,"string":"字符串9"}

Heri.MG

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
pol和easyExcel

Apache POI概述官网：https://poi.apache.org/Apache POI 是 Apache软件基金会的开放源码函式库，POI 提供API给Java程序对Microsoft office 格式档案读和写的功能。简单的说就是提供了操作office 文件的apiAPIHSSF －提供读写[Microsoft Excel](https://baike.baidu...
复制链接

扫一扫