• java操作office表格(POI与easyExcelg)

    文章目录

    1.POI与easyExcelg概述

    概述

    开发中经常会设计到excel的处理,如导出excel,导入excel到数据库中的功能!
    而目前操作Excel目前比较主流的技术有Apache的POI和阿里巴巴的easyExcel技术

    常用场景

    1. 将用户信息,部门信息,等信息导出为excel表格(导出数据)
    2. 将Excel表格信息录入到网站数据库(习题上传…)大大减轻网站录入量！

    Apache PIO

    Apache POI官网地址为:https://poi.apache.org/
    在这里插入图片描述
    在这里插入图片描述

    easyExcelg

    easyExcelg官网地址:https://github.com/alibaba/easyexcel
    在这里插入图片描述
    EasyExcel是阿里巴巴开源的一个excel处理框架,以使用简单节省内存著称。
    EasyExcel能大大减少占用内存的主要原因是在解析Excel时没有将文件数据一次性全部加载到内存中,而是从磁盘上一行一行读取数据,逐个进行解析.
    内存问题:POI=100W先加载到内存OOM,在写入文件
    下图是EasyExcel与POI在解析Excel文档时的对比图:
    在这里插入图片描述

    2.POI-Excel写入

    创建项目

    1. 创建一个普通的Maven项目
    2. 引入pom依赖如下
     <dependencies>

    
    <dependency>
        <groupId>org.apache.poigroupId>
        <artifactId>poiartifactId>
        <version>3.9version>
    dependency>

    
    <dependency>
        <groupId>org.apache.poigroupId>
        <artifactId>poi-ooxmlartifactId>
        <version>5.2.2version>
    dependency>


    
    <dependency>
        <groupId>joda-timegroupId>
        <artifactId>joda-timeartifactId>
        <version>2.10.1version>
    dependency>

    
    <dependency>
        <groupId>junitgroupId>
        <artifactId>junitartifactId>
        <version>4.13.2version>
    dependency>

dependencies>

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32

    版本介绍

    03(xls)与07(xlsx)版本的写,就是对象不同，方法都是一样的!
    需要注意:2003版本和2007版本存在兼容性问题!03最多只有65535行!
    在这里插入图片描述
    步骤为:

    1. 工作簿
    2. 工作表
    3. 行
    4. 列

    基本写入操作

    工作薄的接口为Workbook这个接口下有三个实现类为:

    1. HSSFWorkbook(03版本的xls)对象
    2. XSSFWorkbook(07版本的xlsx)对象
    3. SXSSFWorkbook(升级版增加读写效率)对象
      这里除了工作薄的对象不一样其他的操作基本一只代码如:
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.joda.time.DateTime;

import java.io.FileOutputStream;
import java.io.IOException;

public class Test {
    public static void main(String[] args) throws IOException {
        //1.创建一个工作簿
        Workbook hssfWorkbook = new HSSFWorkbook(); //03版本的对象
        //2.创建一个工作表
        Sheet testTable = hssfWorkbook.createSheet("小步测试的表");
        //3.创建一个行
        Row row = testTable.createRow(0); //表示创建第0行
        //4.创建一个单元格
        Cell cell = row.createCell(0); //表示创建第0行的第0个单元格
        cell.setCellValue("测试数值为:"); //向单元格设值
        Cell cell1 = row.createCell(1);//表示创建第0行的第1个单元格
        cell1.setCellValue(666666);
        Cell cell2 = row.createCell(2);//表示创建第0行第2个单元格
        cell2.setCellValue("时间为:");
        Cell cell3 = row.createCell(3);//表示创建第0行第3个单元格
        cell3.setCellValue(new DateTime().toString("yyyy-MM-dd HH:mm:ss"));

        //生成一张表
        FileOutputStream fileOutputStream = new FileOutputStream("crawl/test.xls");//这里为需要输出的文件地址
        hssfWorkbook.write(fileOutputStream);
        fileOutputStream.close();
    }
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34

    注意对象的区别所生成的后缀名需要对应!

    大文件写HSSF

    缺点:最多只能处理65536行,否则会抛出异常

    Invalid row number (65536) outside allowable range (0..65535)

    • 1

    优点:过程中写入缓存,不操作磁盘,最后一次性写入,速度块

    03(xls)测试如:

     public static void testWrite03BigData() throws IOException {
        long  startTime = System.currentTimeMillis();
        //1.创建薄
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
        //2.创建表
        HSSFSheet sheet = hssfWorkbook.createSheet("小步的测试表");
        //3.写入数据
        for (int i = 0; i < 65536; i++) {
            HSSFRow row = sheet.createRow(i);
            for (int j = 0; j < 10; j++) {
                HSSFCell cell = row.createCell(j);
                cell.setCellValue(i+":"+j);
            }
        }
        long endTime = System.currentTimeMillis();
        FileOutputStream fileOutputStream = new FileOutputStream("crawl/test.xls");
        hssfWorkbook.write(fileOutputStream);
        System.out.println("创建时间为:"+(endTime-startTime)+"毫秒");
    }

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19

    07(xlsx)测试如:

    07则没有65536行的限制

        public static void testWrite07BigData() throws IOException {
        long  startTime = System.currentTimeMillis();
        //1.创建薄
        Workbook hssfWorkbook = new XSSFWorkbook();
        //2.创建表
        Sheet sheet = hssfWorkbook.createSheet("小步的测试表");
        //3.写入数据
        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(i+":"+j);
            }
        }
        long endTime = System.currentTimeMillis();
        FileOutputStream fileOutputStream = new FileOutputStream("crawl/test.xlsx");
        hssfWorkbook.write(fileOutputStream);
        System.out.println("创建时间为:"+(endTime-startTime)+"毫秒");
    }

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19

    进行更快的写入测试如:

    使用SXSSFWorkbook类进行写入如

        public static void testWrite07BigData() throws IOException {
        long  startTime = System.currentTimeMillis();
        //1.创建薄
        Workbook hssfWorkbook = new SXSSFWorkbook();
        //2.创建表
        Sheet sheet = hssfWorkbook.createSheet("小步的测试表");
        //3.写入数据
        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(i+":"+j);
            }
        }
        long endTime = System.currentTimeMillis();
        FileOutputStream fileOutputStream = new FileOutputStream("crawl/test.xlsx");
        //清除临时文件
        ((SXSSFWorkbook)hssfWorkbook).dispose();
        hssfWorkbook.write(fileOutputStream);
        System.out.println("创建时间为:"+(endTime-startTime)+"毫秒");
    }

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21

    3.POI-Excel读取

    如果需要读取都大致相同,需要与之前一样注意一下03与07版的对象不一就行了如:

    基本读取

      public static void ReaderData() throws IOException {
        FileInputStream fileInputStream = new FileInputStream("crawl/testt.xlsx"); //读取的文件
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(fileInputStream);
        HSSFSheet test = hssfWorkbook.getSheetAt(0);
        HSSFRow row = test.getRow(0);
        HSSFCell cell = row.getCell(0);
        //读取值的时候需要注意类型否则会失败
        System.out.println(cell.getStringCellValue());
        //如:无法从文本单元格中获取布尔值
        System.out.println(cell.getBooleanCellValue());
    }

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11

    注意:读取值的时候需要注意类型否则会失败

    不同类型读取

    public static void ReaderData() throws IOException {
        FileInputStream fileInputStream = new FileInputStream("crawl/test.xls");
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(fileInputStream);
        HSSFSheet sheet = hssfWorkbook.getSheetAt(0); //获取第零个工作簿
        int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();//获取这个工作簿有多少行数
        for (int i = 0; i < physicalNumberOfRows; i++) {
            HSSFRow row = sheet.getRow(i);//获取每一行
            int physicalNumberOfCells = row.getPhysicalNumberOfCells();//获取每一行有多少单元格
            for (int i1 = 0; i1 < physicalNumberOfCells; i1++) {
                HSSFCell cell = row.getCell(i1);//获取单元格
                System.out.println(cell.getCellType());
                //判断单元格的类型值
                switch (cell.getCellType()){
                    case Cell.CELL_TYPE_NUMERIC:
                        //数字类型则需要判断是不是日期
                        if (HSSFDateUtil.isCellDateFormatted(cell)){
                            System.out.println(cell.getDateCellValue());
                        }else {
                            System.out.println(cell.getNumericCellValue());
                        }
                        break;
                    case Cell.CELL_TYPE_STRING:
                    case Cell.CELL_TYPE_BLANK:
                        //空白类型和字符串类型
                        System.out.println(cell.getStringCellValue());
                        break;
                    case Cell.CELL_TYPE_BOOLEAN:
                        //布尔类型
                        System.out.println(cell.getBooleanCellValue());
                        break;
                    case Cell.CELL_TYPE_ERROR:
                        //布尔类型
                        System.out.println(cell.getErrorCellValue());
                        break;
                }
            }
        }
    }

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38

    计算公式(了解即可)

    FileInputStream fileInputStream = new FileInputStream("crawl/test.xls");
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(fileInputStream);
        HSSFSheet sheet = hssfWorkbook.getSheetAt(0);

        HSSFRow row = sheet.getRow(4);
        HSSFCell cell = row.getCell(0);

        //拿到计算公司
        FormulaEvaluator hssfFormulaEvaluator = new HSSFFormulaEvaluator(hssfWorkbook);
        //输出单元格的内容
        int cellType = cell.getCellType();
        switch (cellType){
            case Cell.CELL_TYPE_FORMULA:
                //如果为公式则
                String cellFormula = cell.getCellFormula(); //信息
                System.out.println(cellFormula);
                //计算
                CellValue evaluate = hssfFormulaEvaluator.evaluate(cell);
                String s = evaluate.formatAsString();
                System.out.println(s);
                break;
        }

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22

    4.EasyExcel操作

    导入依赖

            
        <dependency>
            <groupId>com.alibabagroupId>
            <artifactId>easyexcelartifactId>
            <version>3.1.1version>
        dependency>

        <dependency>
            <groupId>junitgroupId>
            <artifactId>junitartifactId>
            <version>4.13.2version>
        dependency>

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12

    写入测试

    实体类:DemoData.java

    import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

import java.util.Date;

@Data
public class DemoData {

    @ExcelProperty("字符串标题")
    private String string;

    @ExcelProperty("日期标题")
    private Date date;

    @ExcelProperty("数字标题")
    private Double doubleData;

    //忽略这个字段
    @ExcelIgnore
    private String ignore;
}


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23

    写入测试:

    package com.test;

import com.alibaba.excel.EasyExcel;
import org.junit.Test;

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

public class EasyTest {

    //获取数据
    public List<DemoData> getData(){
       List<DemoData> demoData = new ArrayList<>();
        for (int i = 0; i <10 ; i++) {
            DemoData demoData1 = new DemoData();
            demoData1.setString("字符串"+i);
            demoData1.setDate(new Date());
            demoData1.setDoubleData(3.1415);
            demoData.add(demoData1);
        }
        return demoData;
    }

    //测试方法
    @Test
    public void testEasy(){
        String fileName="test/test.xlsx";//写入的地址
        EasyExcel.write(fileName,DemoData.class).sheet("test").doWrite(getData());
    }

}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32

    读取测试

    如果需要读取exel需要进行编写一个监听器如:

    // 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
@Slf4j
public class DemoDataListener implements ReadListener<DemoData> {

    /**
     * 每隔5条存储数据库，实际使用中可以100条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 100;
    /**
     * 缓存的数据
     */
    private List<DemoData> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
    /**
     * 假设这个是一个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) {
        log.info("解析到一条数据:{}", JSON.toJSONString(data));
        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() {
        log.info("{}条数据，开始存储数据库！", cachedDataList.size());
        demoDAO.save(cachedDataList);
        log.info("存储数据库成功！");
    }
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70

    读取:

        //测试方法
    @Test
    public void testEasy(){
        String fileName="test/test.xlsx";//写入的地址
        EasyExcel.read(fileName,DemoData.class,new DemoDataListener()).sheet("test");
    }

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    详细文档可查看:https://www.yuque.com/easyexcel/doc/read

  • 相关阅读:
    二、JavaScript
    IDEA快捷键大全
    【Java 进阶篇】HTML DOM样式控制详解
    Selenium--多表单frame切换
    这才是 SpringBoot 统一登录鉴权、异常处理、数据格式的正确打开姿势
    数据结构——二叉树1
    C语言：通讯录联系(内存存储)
    黑五必备！跨境电商广告投放终极指南！
    TiDB 集群报警规则
    配置Mysql与注册登录模块
  • 原文地址：https://blog.csdn.net/abc98526/article/details/125983287