简单快速上手JAVA操作Excel

文章目录

前言一、POI及EasyExcel是什么？

1.Apache POI2.easyExcel 二、使用步骤

1.Excel基本写操作2.大数据量的写入

大文件写HSSF大文件写XSSF大文件写SXSSF 3.Excel基本读取及注意4.读取不同类型的数据(难点)5.计算公式(了解)6.EasyExcel使用

前言

在开发后台管理系统中的系统用户管理模块，涉及到了该功能的实现，所以就把学习的内容做了个笔记，方便以后翻阅ε≡٩(๑>₃<)۶ 一心向学

一、POI及EasyExcel是什么？

常用信息：

1、将用户信息导出为excel表格（导出大量数据)
2、将Excel表中的信息录入到网站数据库（习题上传) 大大的减轻网站的录用量！
开发中经常会设计到excel的处理，如导出Excel，导入Excel到数据库中!
操作Excel目前比较流行的就是Apache POI和阿里巴巴的easyExcel！

1.Apache POI

Apache POl官网: https://poi.apache.org/

什么是Apache POI?
Apache POI是一种流行的API，允许程序员使用Java程序创建，修改和显示MS Office文件。它是由Apache Software Foundation开发和分发的开源库，用于使用Java程序设计或修改Microsoft Office文件。它包含将用户输入数据或文件解码为MS Office文档的类和方法。

Apache POI的组件
Apache POI包含用于处理MS Office的所有OLE2复合文档的类和方法。该API的组件列表如下。

POIFS - 该组件是所有其他POI元素的基本因素。它用于显式读取不同的文件。- HSSF - 用于读取和写入MS-Excel文件的 xls 格式。(03版本最大行数65536)- XSSF - 用于MS-Excel的 xlsx 文件格式。(07版本行数不限制)HPSF - 用于提取MS-Office文件的属性集。- HWPF - 用于读写MS-Word的 doc 扩展文件。XWPF - 用于读写MS-Word的 docx 扩展文件。- HSLF - 用于阅读，创建和编辑PowerPoint演示文稿。- HDGF - 它包含 MS-Visio 二进制文件的类和方法。HPBF - 用于读写 MS-Publisher 文件。 2.easyExcel

easyExcel官网地址: https://github.com/alibaba/easyexcel

EasyExcel是阿里巴巴开源的—个excel处理框架，以使用简单、节省内存著称。
EasyExcel能大大减少占用内存的主要原因是在解析Excel时没有将文件数据一次性全部载到内行中，而是从磁盘上一行行读取数据，逐个解析。

下图是EasyExcel和POI在解析Excel时的对比图(该图来着官网：文件解压文件读取通过文件形式)：

官方文档: https://www.yuque.com/easyexcel/doc/easyexcel

二、使用步骤 1.Excel基本写操作

使用idea，创建一个maven工程，添加依赖：


    
    
        org.apache.poi
        poi
        3.9
    

    
    
        org.apache.poi
        poi-ooxml
        3.9
    

    
    
        joda-time
        joda-time
        2.10.1
    

    
    
        junit
        junit
        4.12

简单了解excel的结构：

如何将这三个对象抽象出来呢？

工作薄工作表行列

03版本的测试代码(.xls)：

String PATH = "C:\Users\A\Desktop\";

@Test
public void testWrite03() throws IOException {
    // 1、创建一个工作薄
    HSSFWorkbook workbook = new HSSFWorkbook();
    // 2、创建一个工作表
    Sheet sheet = workbook.createSheet("一个爱运动的程序员上班打卡表");
    // 3、创建一个行 （1,1）
    Row row1 = sheet.createRow(0);
    // 4、创建一个单元格
    Cell cell11 = row1.createCell(0);
    cell11.setCellValue("今天上班人数");
    // (1,2)
    Cell cell12 = row1.createCell(1);
    cell12.setCellValue(22);

    // 第二行（2,1）
    Row row2 = sheet.createRow(1);
    Cell cell21 = row2.createCell(0);
    cell21.setCellValue("统计时间");
    // (2,2)
    Cell cell22 = row2.createCell(1);
    String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
    cell22.setCellValue(time);

    // 生成一张表（IO 流） 03版本就是使用xls结尾
    FileOutputStream fileOutputStream = new FileOutputStream(PATH + "一个爱运动的程序员上班打卡表" + ".xls");

    // 输出
    workbook.write(fileOutputStream);

    // 关闭流
    fileOutputStream.close();

    System.out.println("一个爱运动的程序员上班打卡表03 生成完毕");
}

生成的效果：

07版本的测试代码(.xlsx):

@Test
public void testWrite07() throws IOException {
    // 1、创建一个工作薄(与03版本的区别在此处：一个HSSF, 一个XSSF)
    XSSFWorkbook workbook = new XSSFWorkbook();
    // 2、创建一个工作表
    Sheet sheet = workbook.createSheet("一个爱运动的程序员上班打卡表");
    // 3、创建一个行 （1,1）
    Row row1 = sheet.createRow(0);
    // 4、创建一个单元格
    Cell cell11 = row1.createCell(0);
    cell11.setCellValue("今天上班人数");
    // (1,2)
    Cell cell12 = row1.createCell(1);
    cell12.setCellValue(22);

    // 第二行（2,1）
    Row row2 = sheet.createRow(1);
    Cell cell21 = row2.createCell(0);
    cell21.setCellValue("统计时间");
    // (2,2)
    Cell cell22 = row2.createCell(1);
    String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
    cell22.setCellValue(time);

    // 生成一张表（IO 流） 03版本就是使用xls结尾 07版本就是使用xlsx结尾
    FileOutputStream fileOutputStream = new FileOutputStream(PATH + "一个爱运动的程序员上班打卡表07" + ".xlsx");

    // 输出
    workbook.write(fileOutputStream);

    // 关闭流
    fileOutputStream.close();

    System.out.println("一个爱运动的程序员上班打卡表07 生成完毕");
}

生成的效果图：

注意对象的一个区别，文件的后缀。

2.大数据量的写入

数据批量导入

大文件写HSSF

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

java.lang.I1legalArgumentException: Invalid row number (65536) outside allowable range (0…65535)

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

03版本的代码测试：

@Test
public void testWrite03BigData() throws IOException {
    // 时间
    long begin = System.currentTimeMillis();

    // 创建一个薄
    Workbook workbook = new HSSFWorkbook();
    // 创建表
    Sheet sheet = workbook.createSheet();
    // 写入数据
    for (int rowNum = 0; rowNum < 65536; rowNum++) {
        Row row = sheet.createRow(rowNum);
        for (int cellNum = 0; cellNum < 10; cellNum++) {
            Cell cell = row.createCell(cellNum);
            cell.setCellValue(cellNum);
        }
    }
    System.out.println("over");
    FileOutputStream fileOutputStream = new FileOutputStream(PATH + "testWrite03BigData.xls");
    workbook.write(fileOutputStream);
    fileOutputStream.close();
    long end = System.currentTimeMillis();
    System.out.println((double) (end - begin) / 1000);
}

运行效果：

当我们超出最大行数时，出现的报错信息：

大文件写XSSF

缺点:写数据时速度非常慢，非常耗内存，也会发生内存溢出，如100万条
优点:可以写较大的数据量，如20万条

07版本写入10万条数据测试：

@Test
public void testWrite07BigData() throws IOException {
    // 时间
    long begin = System.currentTimeMillis();

    // 创建一个薄
    Workbook workbook = new XSSFWorkbook();
    // 创建表
    Sheet sheet = workbook.createSheet();
    // 写入数据
    for (int rowNum = 0; rowNum < 100000; rowNum++) {
        Row row = sheet.createRow(rowNum);
        for (int cellNum = 0; cellNum < 10; cellNum++) {
            Cell cell = row.createCell(cellNum);
            cell.setCellValue(cellNum);
        }
    }
    System.out.println("over");
    FileOutputStream fileOutputStream = new FileOutputStream(PATH + "testWrite07BigData.xlsx");
    workbook.write(fileOutputStream);
    fileOutputStream.close();
    long end = System.currentTimeMillis();
    System.out.println((double) (end - begin) / 1000);
}

运行效果：

相信大家看到运行结果会发现这运行结果也太慢了吧，所以下面将在记录一个加速的

大文件写SXSSF

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

我们下面任然是写入10万行数据查看：

@Test
public void testWrite07BigDataS() throws IOException {
    // 时间
    long begin = System.currentTimeMillis();
    // 创建一个薄
    Workbook workbook = new SXSSFWorkbook();
    // 创建表
    Sheet sheet = workbook.createSheet();
    // 写入数据
    for (int rowNum = 0; rowNum < 100000; rowNum++) {
        Row row = sheet.createRow(rowNum);
        for (int cellNum = 0; cellNum < 10; cellNum++) {
            Cell cell = row.createCell(cellNum);
            cell.setCellValue(cellNum);
        }
    }
    System.out.println("over");
    FileOutputStream fileOutputStream = new FileOutputStream(PATH + "testWrite07BigDataS.xlsx");
    workbook.write(fileOutputStream);
    fileOutputStream.close();
    // 清除临时文件
    ((SXSSFWorkbook) workbook).dispose();
    long end = System.currentTimeMillis();
    System.out.println((double) (end - begin) / 1000);
}

查看运行效果：

同样的数据量，写入的时间差别很大。

SXSSFWorkbook-来至官方的解释：实现"BigGridDemo"策略的流式XSSFWorkbook版本。这允许写入非常大的文件而不会耗尽内存，因为任何时候只有可配置的行部分被保存在内存中。

请注意，仍然可能会消耗大量内存，这些内存基于您正在使用的功能，例如合并区域，注释；’任然只存储在内存中，因此如果广泛使用，可能需要大量内存。

当遇到这样的问题时，再使用POI的时候，内存问题Jprofile来进行监控。

3.Excel基本读取及注意

03版本的读取，测试用例：

@Test
public void testRead03() throws IOException {
    // 获取文件流
    FileInputStream fileInputStream = new FileInputStream(PATH + "一个爱运动的程序员上班打卡表03.xls");
    // 1、创建一个工作薄
    Workbook workbook = new HSSFWorkbook(fileInputStream);
    // 2、得到表
    Sheet sheetAt = workbook.getSheetAt(0);
    // 3、得到行
    Row row = sheetAt.getRow(0);
    // 4、得到列
    Cell cell1 = row.getCell(0);
    // 获取字符串的类型
    System.out.println(cell1.getStringCellValue());

    Cell cell2 = row.getCell(1);
    // 获取数值类型
    System.out.println(cell2.getNumericCellValue());
    fileInputStream.close();
}

测试效果：

而07版本的读取差别不大, 具体如下：

4.读取不同类型的数据(难点)

我使用的数据：

代码测试：

@Test
public void testCellType() throws IOException {
    // 获取文件流
    FileInputStream fileInputStream = new FileInputStream(PATH + "JAVA操作.xls");
    // 创建一个工作薄
    Workbook workbook = new HSSFWorkbook(fileInputStream);
    Sheet sheet = workbook.getSheetAt(0);
    // 获取标题内容
    Row row = sheet.getRow(0);
    if (row != null) {
        // 获取列数
        int cellCount = row.getPhysicalNumberOfCells();
        for (int cellNum = 0; cellNum < cellCount; cellNum++) {
            Cell cell = row.getCell(cellNum);
            if (cell != null) {
                int cellType = cell.getCellType();
                // 获取标题
                String cellValue = cell.getStringCellValue();
                System.out.printf(cellValue + "|");
            }
        }
        System.out.println();
    }
    // 获取表中的内容
    int rowCount = sheet.getPhysicalNumberOfRows();
    for (int rowNum = 1; rowNum < rowCount; rowNum++) {
        Row rowData = sheet.getRow(rowNum);
        if (rowData != null) {
            // 读取到
            int cellCount = row.getPhysicalNumberOfCells();
            for (int cellNum = 0; cellNum < cellCount; cellNum++) {
                System.out.printf("[" + (rowNum + 1) + "-" + (cellNum + 1) + "]");
                Cell cell = rowData.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】");
                            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                System.out.print("【日期】");
                                Date date = cell.getDateCellValue();
                                cellValue = new DateTime(date).toString("yyyy-MM-dd");
                            } else {
                                // 不是日期格式，防止数字过长
                                System.out.print("【转换为字符串输出】");
                                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                                cellValue = cell.toString();
                            }
                            break;
                        // 数据类型错误
                        case HSSFCell.CELL_TYPE_ERROR:
                            System.out.print("【数据类型错误】");
                            break;
                    }
                    System.out.println(cellValue);
                }
            }
        }
    }
    fileInputStream.close();
}

测试运行的效果：

注意转换的类型，以上的方法日后可以拿来用做一个工具类。

5.计算公式(了解)

测试用的数据：

测试代码：

@Test
public void testFormula() throws IOException {
    // 获取文件流
    FileInputStream fileInputStream = new FileInputStream(PATH + "JAVA操作.xls");
    // 创建一个工作薄
    Workbook workbook = new HSSFWorkbook(fileInputStream);
    Sheet sheet = workbook.getSheetAt(0);
    Row row = sheet.getRow(11);
    Cell cell = row.getCell(0);

    // 拿到计算公式 eval
    Formulaevaluator Formulaevaluator = new HSSFFormulaevaluator((HSSFWorkbook) workbook);
    // 输出单元格的内容
    int cellType = cell.getCellType();
    switch (cellType) {
        // 公式
        case Cell.CELL_TYPE_FORMULA:
            String formula = cell.getCellFormula();
            System.out.println(formula);

            // 计算
            CellValue evaluate = Formulaevaluator.evaluate(cell);
            String cellValue = evaluate.formatAsString();
            System.out.println(cellValue);
            break;
    }
}

6.EasyExcel使用

官方文档: https://www.yuque.com/easyexcel/doc/easyexcel
这个工具的使用，只需要看着官方文档操作就可以啦，需要啥就查啥，下面便简单的操作一下：

下面便简单的根据文档的写操作一下：
首先导入依赖：


    com.alibaba
    easyexcel
    2.2.0-beta2

为了方便实体类的操作，也引入lombok依赖：


    org.projectlombok
    lombok
    1.18.22

文档的实体类demo：

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

一个简单的写：

public class EasyTest {
    String PATH = "C:\Users\A\Desktop\";

    private List data() {
        List list = new ArrayList();
        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 + "EasyTest.xlsx";
        // 这里需要制定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流自动关闭
        // write (fileName, 格式类)
        // sheet (表明)
        // doWrite (数据)
        EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data());
    }
}

运行看看效果：

报了一个这样的错误，查了下度娘，说是不是依赖包冲突就是包缺失，查了下pom，发现一开始的POI的依赖没有注释，因为在EasyExcel的依赖包中已经存POI的依赖，所以引起了依赖包的冲突错误。所以解决办法就是把之前的POI的依赖注释掉，在运行：

运行成功，上面便是运行成功生成的excel。
居然简单的写操作完成了，那读的操作当然就不能少啦

因为在文档的测试中引入了fastjson的依赖包，所以我们也得引入一下：


    com.alibaba
    fastjson
    1.2.75

引入监听器的类：

@Slf4j
public class DemoDataListener extends AnalysisEventListener {

    
    private static final int BATCH_COUNT = 100;
    
    private List cachedDataList = new ArrayList(BATCH_COUNT);
    
    private DemoDAO demoDAO;

    public DemoDataListener() {
        // 这里是demo，所以随便new一个。实际使用如果到了spring,请使用下面的有参构造函数
        demoDAO = new DemoDAO();
    }

    
    public DemoDataListener(DemoDAO demoDAO) {
        this.demoDAO = demoDAO;
    }


    @Override
    public void invoke(DemoData data, AnalysisContext context) {
        System.out.println(JSON.toJSonString(data));
        cachedDataList.add(data);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (cachedDataList.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            cachedDataList.clear();
        }
    }

    
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();
        log.info("所有数据解析完成！");
    }

    
    private void saveData() {
        log.info("{}条数据，开始存储数据库！", cachedDataList.size());
        demoDAO.save(cachedDataList);
        log.info("存储数据库成功！");
    }
}

启动测试方法：

@Test
public void simpleRead() {
    String fileName = PATH + "EasyTest.xlsx";
    // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
    // 这里每次会读取3000条数据 然后返回过来 直接调用使用数据就行
    EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead();
}

运行看结果：

而至于持久化操作，大家可以查看一下官方文档。
固定套路：
1、写入：固定类格式进行写入
2、读取：根据监听器设置的规则进行读取

大家有兴趣或有开发需要，可以看看文档的API，功能特别丰富。

资料来源于：B站狂神，大家有兴趣可以看看https://www.bilibili.com/video/BV1Ua4y1x7BK?p=1

简单快速上手JAVA操作Excel

Java相关栏目本月热门文章