POI和EasyExcel

最新推荐文章于 2023-10-08 11:46:51 发布

油锅里的猪

最新推荐文章于 2023-10-08 11:46:51 发布

阅读量664

点赞数 2

本文链接：https://blog.csdn.net/qq_42956376/article/details/108553120

版权

在开发中会经常设计到excel的处理，比如导出Excel或这导入Excel到数据库中。

操作Excel目前比较流行的是POI(Apache)和easyExcel(阿里巴巴)

一：Apache POI

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

POI提供API给java程序，使用java程序对Office的读和写的功能。

POI提供了些基本对象：

比如：

* HSSF - 对Excel的读写功能。

* XSSF - 对Excel OOXML的读写功能。

* HWPF - 对Word的读写功能。

* HSLF - 对PowerPoint的读写功能。

* HDGF - 对Vislo的读写功能。

比较原生，存在一个严重问题耗内存，容易产生OMM。

内存问题：POI读。比如100W数据,POI会先加载到内存中，在写入文件，如果说你内存小的话，会出现内存溢出。

二：Alibaba easyExcel

官方：https://github.com/alibaba/easyexcel

EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目，几乎没有OMM。

内存问题：EasyExcel写入时，比如写100W,那么它会一行一行的写入。

三：准备

首先我们需要一个03版本和07版本的Excel表。

03版本和07版的区别：

* 后缀名不同： 03版为xls，07版为xlsx

* 最大行数不同：03版本它最大到65536行，07版没有限制。

版本的不一样，操作工具也不一样，那么在项目中依赖也是不同的。

    <!--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>
        <!--日期转换工具-->
        <dependency>
            <groupId>joda-time</groupId>
            <artifactId>joda-time</artifactId>
            <version>2.10.1</version>
        </dependency>
        <!--test-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>

在java中的有一句话，万物皆对象，那么Excel表我们可以它作为对象去看待。

Excel表我们可以把分为四个对象：

工作薄对象，

工作表对象，

行对象，

列对象。

有了这个清晰的对象结构，那么我们通过java代码进行Excel表操作的话，可以按照4步创建对象。

第一步：创建工作薄对象

第二步：创建工作表对象

第三步：创建行对象

第四步：创建列对象

四：代码实现

4.1： POI实现

那么创建Excel工作薄对象，poi提供了一个接口WorkBook，这个接口的实现类有3个。

HSSFWorkbook：创建03版本的Excel。

SXSSFWorkbook：创建07版本的Excel。

SXSSWorkBook：可以理解为增强版的07版的Excel(比XSSF速度快)。

数据批量导入：

大文件写HSSF(03版本)

缺点：只能处理65536行，超出抛异常

优点：过程中写入缓存，不操作磁盘，最后一次性全部写入到磁盘中，速度快。

大文件写XSSF(07版本)

缺点：写入数据时的速度非常慢,比较耗内存，也会发生内存溢出，比如100万条数据

优点：可以写较大数据量，如20万条

大文件写SXSSF(07加强版)

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

注意：过程中会产生临时文件，需要清理临时文件

默认由100条记录被保存在内存中，如果超过这数量，则最前面数据写入临时文件（需要清除临时文件

           ((SXSSFWorkbook)workbook).dispose();)

如果想自定义内存数据的数量,可以使用new SXSSFWorkbook(数量)

POI使用03版本写和读的代码实现

public class ExcelWriteTest {
    
    //文件路径
    String PATH = "D:\\Ideaworkspace2019\\java-poiAndEasyExcel\\Java-PoiAndEasyExcel\\";

    @Test
    public void TestWriteExcel03() throws Exception {
       //第一步：创建一个工作薄
       Workbook workbook = new HSSFWorkbook();
       //第二步：创建一个工作表
       Sheet sheet = workbook.createSheet("03版本的Excel统计表");
       //第三步：创建一个行 0表示索引也就是代表第一行
       Row row = sheet.createRow(0);
       //第四步：创建一个列 0表示索引也就是代表第一列
       Cell cell = row.createCell(0);
       //为第一列写入值
       cell.setCellValue("今日新增用户");
       //创建第二列，并且为第二列写入值
       Cell cell1 = row.createCell(1);
       cell1.setCellValue(999);
       //当然还可以创建第二行,并且创建两列写入值
       Row row1 = sheet.createRow(1);
       Cell cell2 = row1.createCell(0);
       Cell cell3 = row1.createCell(1);
       cell2.setCellValue("统计时间");
       String date = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
       cell3.setCellValue(date);
       //生成Excel表，会使用到IO流
       FileOutputStream fileOutputStream = new FileOutputStream(PATH + "03版本的Excel统计表.xls");
       //将相应的文件写入存盘
       workbook.write(fileOutputStream);
       //关闭
       fileOutputStream.close();
       System.out.println("生成03版Excel表成功");

    }
}

 @Test
    public void TestReadExcel03() throws Exception {
        //文件流
        FileInputStream fileInputStream = new FileInputStream(PATH + "03版本的Excel统计表.xls");
        //创建一个工作薄
        Workbook workbook = new HSSFWorkbook(fileInputStream);
        //获取工作表
        Sheet sheetAt = workbook.getSheetAt(0);
        //获取行
        Row row = sheetAt.getRow(0);
        //获取列(注意：读取的是String类型的值)
        Cell cell = row.getCell(0);
        System.out.println(cell.getStringCellValue());
        //获取第二列(注意：第二列是数值类型)
        Cell cell1 = row.getCell(1);
        System.out.println(cell1.getNumericCellValue());
        fileInputStream.close();
    }

POI使用07版本写和读的代码实现(07版本和03版本基本是一样的，只是创建工作薄的对象不同而已)

  @Test
    public void TestWriteExcel07() throws Exception {
        //第一步：创建一个工作薄
        Workbook workbook = new XSSFWorkbook();
        //第二步：创建一个工作表
        Sheet sheet = workbook.createSheet("07版本的Excel统计表");
        //第三步：创建一个行 0表示索引也就是代表第一行
        Row row = sheet.createRow(0);
        //第四步：创建一个列 0表示索引也就是代表第一列
        Cell cell = row.createCell(0);
        //为第一列写入值
        cell.setCellValue("今日新增用户");
        //创建第二列，并且为第二列写入值
        Cell cell1 = row.createCell(1);
        cell1.setCellValue(999);
        //当然还可以创建第二行,并且创建两列写入值
        Row row1 = sheet.createRow(1);
        Cell cell2 = row1.createCell(0);
        Cell cell3 = row1.createCell(1);
        cell2.setCellValue("统计时间");
        String date = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
        cell3.setCellValue(date);
        //生成Excel表，会使用到IO流
        FileOutputStream fileOutputStream = new FileOutputStream(PATH + "07版本的Excel统计表.xlsx");
        //将相应的文件写入存盘
        workbook.write(fileOutputStream);
        //关闭
        fileOutputStream.close();
        System.out.println("生成07版Excel表成功");

    }

    @Test
    public void TestReadExcel07() throws Exception {
        //文件流
        FileInputStream fileInputStream = new FileInputStream(PATH + "07版本的Excel统计表.xlsx");
        //创建一个工作薄
        Workbook workbook = new XSSFWorkbook(fileInputStream);
        //获取工作表
        Sheet sheetAt = workbook.getSheetAt(0);
        //获取行
        Row row = sheetAt.getRow(0);
        //获取列(注意：读取的是String类型的值)
        Cell cell = row.getCell(0);
        System.out.println(cell.getStringCellValue());
        //获取第二列(注意：第二列是数值类型)
        Cell cell1 = row.getCell(1);
        System.out.println(cell1.getNumericCellValue());
        fileInputStream.close();
    }

注意：最麻烦的是在读取Excel表时的每列值的类型是不同的，一个列可能是String,也可能是date，或者是double。

   @Test
    public void TestCellType() throws Exception {
        //获取文件流
        FileInputStream fileInputStream = new FileInputStream(PATH + "明细表.xls");
        Workbook workbook = new HSSFWorkbook(fileInputStream);
        Sheet sheetAt = workbook.getSheetAt(0);
        //获取标题行
        Row rowTitle = sheetAt.getRow(0);
        if (rowTitle!=null){
            //getPhysicalNumberOfCells这个方法是获取一行列的数量
            int cellCount = rowTitle.getPhysicalNumberOfCells();
            //循环每一列
            for (int cellNum = 0; cellNum < cellCount ; cellNum++) {
                //获取每一列
                Cell cell = rowTitle.getCell(cellNum);
                if (cell!= null){
                    //获取每一列的类型
                    int cellType = cell.getCellType();
                    String stringCellValue = cell.getStringCellValue();
                    System.out.print(stringCellValue + "|");
                }
            }
            System.out.println();
        }
        //获取表中列表的数据
        int rowsCount = sheetAt.getPhysicalNumberOfRows();
        for (int rowNum = 1; rowNum < rowsCount; rowNum++){
             //每一行
             Row row = sheetAt.getRow(rowNum);
             if (row!=null){
                 //读取每一行中的列数
                 int cellsCount = row.getPhysicalNumberOfCells();
                 //循环列数
                 for (int cellNum = 0; cellNum < cellsCount ; cellNum++) {
                     //获取每一列
                     Cell cell = row.getCell(cellNum);
                     //匹配列的数据类型
                     if (cell!=null){
                         //获取列的类型
                         //可以考虑抽出来封装成一个工具类方法，
                         int cellType = cell.getCellType();
                         String cellValue= "";
                         switch (cellType){
                             case HSSFCell.CELL_TYPE_STRING: //字符串
                                 System.out.print("【String】");
                                 cellValue = cell.getStringCellValue();
                                 break;
                             case HSSFCell.CELL_TYPE_BOOLEAN: //布尔
                                  System.out.print("【Boolean】");
                                  cellValue = String.valueOf(cell.getBooleanCellValue());
                                  break;
                             case HSSFCell.CELL_TYPE_BLANK: //空
                                  System.out.print("【blank】");
                                  break;
                             case HSSFCell.CELL_TYPE_NUMERIC: //数字(日期，普通数字)
                                  System.out.print("【numeric】");
                                  //判断是否是日期，HSSFDateUtil是poi的工具类
                                  if (HSSFDateUtil.isCellDateFormatted(cell)){
                                      System.out.print("【日期】");
                                      Date dateCellValue = cell.getDateCellValue();
                                      cellValue = new DateTime(dateCellValue).toString("yyyy-MM-dd");
                                  }else {
                                      //普通的数字,防止数字过长
                                      System.out.println("【转成字符串输出】");
                                      cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                                      cellValue = cell.toString();
                                  }
                                  break;
                             case HSSFCell.CELL_TYPE_ERROR: //错误
                                 System.out.println("【数据类型错误】");
                                 break;
                         }
                         System.out.println(cellValue);
                     }
                 }
             }
        }
        //关闭流
        fileInputStream.close();
    }

4.2 使用EasyExcel实现

去github官方上拿到依赖，因为EasyExcel的依赖中包含了poi的依赖，所以我们把之前导入的poi依赖给注释掉，防止冲突.

    <dependencies>
        <!--EasyExcel-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.6</version>
        </dependency>
        <!--03版本(xls)-->
<!--        <dependency>-->
<!--            <groupId>org.apache.poi</groupId>-->
<!--            <artifactId>poi</artifactId>-->
<!--            <version>3.9</version>-->
<!--        </dependency>-->
<!--        &lt;!&ndash;07版本(xlsx)&ndash;&gt;-->
<!--        <dependency>-->
<!--            <groupId>org.apache.poi</groupId>-->
<!--            <artifactId>poi-ooxml</artifactId>-->
<!--            <version>3.9</version>-->
<!--        </dependency>-->
        <!--日期转换工具-->

EasyExcel写入实现

首先格式类，也就是你表的中标题

@Data
public class EasyExcelEntity {

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

public class TestEasyExcel {

    String PATH = "D:\\Ideaworkspace2019\\java-poiAndEasyExcel\\Java-PoiAndEasyExcel\\";

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

    /**
     * 最简单的写
     * <p>1. 创建excel对应的实体对象
     * <p>2. 直接写即可
     */
    @Test
    public void simpleWrite() {
        String fileName = PATH +  "EasyExcelTest.xlsx";
        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        // EasyExcelEntity格式类
        //sheet(表名)创建sheet
        //doWrite()写入数据
        EasyExcel.write(fileName, EasyExcelEntity.class).sheet("模板").doWrite(data());

    }

}

EasyExcel读

public class DemoDataListener extends AnalysisEventListener<EasyExcelEntity> {
    private static final Logger LOGGER = LoggerFactory.getLogger(DemoDataListener.class);
    /**
     * 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 5;
    List<EasyExcelEntity> list = new ArrayList<EasyExcelEntity>();

    private DemoDAO demoDAO;
    public DemoDataListener() {
        demoDAO = new DemoDAO();
    }

    public DemoDataListener(DemoDAO demoDAO) {
        this.demoDAO = demoDAO;
    }
    /**
     * 读取数据会执行invke方法
     *
     */
    @Override
    public void invoke(EasyExcelEntity data, AnalysisContext context) {
        System.out.println(JSON.toJSONString(data));
        list.add(data);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (list.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            list.clear();
        }
    }
    /**
     * 所有数据解析完成了 都会来调用
     *
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();
        LOGGER.info("所有数据解析完成！");
    }
    /**
     * 加上存储数据库
     */
    private void saveData() {
        LOGGER.info("{}条数据，开始存储数据库！", list.size());
        demoDAO.save(list);
        LOGGER.info("存储数据库成功！");
    }
}

    @Test
    public void simpleRead() {
        // 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
        // 写法1：
        String fileName = PATH +  "EasyExcelTest.xlsx";
        // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
        EasyExcel.read(fileName, EasyExcelEntity.class, new DemoDataListener()).sheet().doRead();
    }

油锅里的猪

关注

2
点赞
踩
4

收藏

觉得还不错? 一键收藏
打赏
0
评论
POI和EasyExcel

在开发中会经常设计到excel的处理，比如导出Excel或这导入Excel到数据库中。操作Excel目前比较流行的是POI(Apache)和easyExcel(阿里巴巴)一：Apache POI官网：https://poi.apache.org/POI提供API给java程序，使用java程序对Office的读和写的功能。POI提供了些基本对象：比如： * HSSF - 对Excel的读写功能。 * XSSF - 对E...
复制链接

扫一扫