Apache POI及easyExcel读取及写入excel文件

目录

1.excel

2.使用场景

3.Apache POI

4.easyExcel

5.总结

1.excel

excel分为两版，03版和07版。

03版的后缀为xls，最大有65536行。

07版的后缀为xlsx，最大行数没有限制。

2.使用场景

将用户信息导出到excel表格中。

将excel中的数据读取到数据库中。

3.Apache POI

(1)说明

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

HSSF – 提供读写MicrostExcel格式档案的功能（excel 03）。
XSSF – 提供读写MicrosofExcel OOXML各式档案的功能（excel 07）。
HWPF -提供读写Microsoft Word洛式档案的功能。
HSLF – 提供读写Microsof PowerPoint式档案的功能
HDGF – 提供读写Microsoft Visio各式档案的功能

使用起来比较麻烦，而且数据量大时会出现OOM异常。

使用poi读取不同格式的excel文件的使用jar包是不同的。如下：

        <!--        xls-03--&gt;
        <dependency&gt;
            <groupId&gt;org.apache.poi</groupId&gt;
            <artifactId&gt;poi</artifactId>
            <version>4.1.2</version>
        </dependency>
        <!--        xls-07-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>

(2)读取说明

将excel分为4个对象，excel文件是一个工作簿；excel中有好多sheet，这个是工作表；表中有行，行中有列。使用apache poi读取或写入文件，就是先获取工作簿，然后获取工作表，再获取某行，最后获取行中某列的内容。

写入示例：

注意：03和07版excel的对象的区别和文件后缀。

读取示例：

   @Test
    public void read03() throws IOException {

        FileInputStream fileInputStream = new FileInputStream(path + "apache-03.xls");

        Workbook workbook = new HSSFWorkbook(fileInputStream);

        Sheet sheet = workbook.getSheetAt(0);

        Row row = sheet.getRow(2);

        Cell cell = row.getCell(1);

        System.out.println(cell.getCellType());

        double value = cell.getNumericCellValue();

        System.out.println(value);

        if(fileInputStream != null) fileInputStream.close();

    }

    @Test
    public void read07() throws IOException {

        FileInputStream fileInputStream = new FileInputStream(path + "apache-07.xlsx");

        Workbook workbook = new XSSFWorkbook(fileInputStream);

        Sheet sheet = workbook.getSheetAt(0);

        Row row = sheet.getRow(1);

        Cell cell = row.getCell(1);

        System.out.println(cell.getCellType());

        String value = cell.getStringCellValue();

        System.out.println(value);

        if(fileInputStream != null) fileInputStream.close();

    }

注意：

读取时注意单元格的类型，按照类型进行使用不同的方法进行读取，使用的方法和类型不一致时会报错 

读取类型示例：

@Test
    public void cellTypeTest() throws IOException {
        FileInputStream fileInputStream = new FileInputStream(path + "apache-03.xls");

        Workbook workbook = new HSSFWorkbook(fileInputStream);

        Sheet sheet = workbook.getSheetAt(0);

        Row row = sheet.getRow(1);
        if (row != null) {
            int cellNum = row.getPhysicalNumberOfCells();
            for (int i = 0; i < cellNum; i++) {
                Cell cell = row.getCell(i);
                if (cell != null) {
                    System.out.println(cell.getStringCellValue());
                }
            }
        }

        int rowNum = sheet.getPhysicalNumberOfRows();
        for (int i = 1; i < rowNum; i++) {
            Row row1 = sheet.getRow(i);
            if (row1 != null) {
                int cells = row1.getPhysicalNumberOfCells();
                for (int j = 0; j < cells; j++) {
                    Cell cell = row1.getCell(j);
                    if (cell == null) {
                        continue;
                    }
                    String value = "";
                    CellType cellType = cell.getCellType();
                    switch (cellType) {
                        case STRING:
                            System.out.println("String类型");
                            value = cell.getStringCellValue();
                            break;
                        case BLANK:
                            System.out.println("类型为空");
                            break;
                        case BOOLEAN:
                            System.out.println("布尔类型");
                            value = String.valueOf(cell.getBooleanCellValue());
                            break;
                        case NUMERIC:
                            if (DateUtil.isCellDateFormatted(cell)) {
                                System.out.println("日期类型时");
                                Date dateCellValue = cell.getDateCellValue();
                                value = new DateTime(dateCellValue).toString("yyyy-MM-dd");
                            } else {
                                System.out.println("数值格式时");
                                // 有时候数值过长会出现问题
                                value = String.valueOf(cell.getNumericCellValue());
                            }
                            break;
                        case ERROR:
                            System.out.println("数据类型错误");
                            break;
                        default:
                    }
                }
            }
        }
        if(fileInputStream != null) fileInputStream.close();
    }

 读取公式示例：

 @Test
    public void testFormula() throws IOException {
        String path = "C:\Users\DELL\Desktop\新建 XLS 工作表.xls";
        FileInputStream fileInputStream = new FileInputStream(path);

        Workbook workbook = new HSSFWorkbook(fileInputStream);

        Sheet sheet = workbook.getSheetAt(0);

        Row row = sheet.getRow(3);
        Cell cell = row.getCell(0);
        CellType cellType = cell.getCellType();
        switch (cellType) {
            case FORMULA:
                String cellFormula = cell.getCellFormula();
                System.out.println("公式:" + cellFormula);

                FormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);
                CellValue evaluate = formulaEvaluator.evaluate(cell);
                String value = evaluate.formatAsString();
                System.out.println(value);
        }
    }

 根据类型判断是公式时，获取工作簿的计算对象，调用计算方法获取计算结果。

(3)写入说明

使用HSSF：会写入缓存，不操作磁盘，最后一次性写入磁盘，速度快。

使用XSSF：写数据时非常慢，非常耗内存，数据量大时，会发生内存溢出问题。

使用SXSSF：可以写非常大的数据量，如百万级别的数据，写数据速度快，占用更少的内存。

写入过程中会生成临时文件，需要手动清理临时文件，默认是100条数据保存在内存中，超过100条，则最前面的100条数据会写入到临时文件中，如果想自定义存放在内存中数据的数量，创建时可以传入件数的参数。如果写入的excel结构比较复杂，例如合并单元格，注释等，广泛使用也可能消耗大量内存。内存问题可以使用Jprofile进行监控。

写入示例：

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.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;
import org.junit.Test;

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

/**
 * @Author linab
 * @Date 2023/10/29 20:26
 * @Version 1.0
 */
public class ApacheTest {

    private String path = "E:\learn\excel\excel-poi\src\main\resources\";

    @Test
    public void test03() throws IOException {
        Workbook workbook = new HSSFWorkbook();

        Sheet sheet = workbook.createSheet("03");

        Row row = sheet.createRow(0);

        Cell cell = row.createCell(0);
        cell.setCellValue("姓名");

        Cell cell1 = row.createCell(1);
        cell1.setCellValue("小李");


        Row row1 = sheet.createRow(1);

        Cell cell2 = row1.createCell(0);
        cell2.setCellValue("日期");

        Cell cell3 = row1.createCell(1);
        String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
        cell3.setCellValue(time);

        FileOutputStream fileOutputStream = new FileOutputStream(path + "apache-03.xls");

        workbook.write(fileOutputStream);

        if(fileOutputStream != null) fileOutputStream.close();
        if(workbook != null) workbook.close();

        System.out.println("文件生成成功");
    }

    @Test
    public void test07() throws IOException {
        Workbook workbook = new XSSFWorkbook();

        Sheet sheet = workbook.createSheet("07");

        Row row = sheet.createRow(0);

        Cell cell = row.createCell(0);
        cell.setCellValue("姓名");

        Cell cell1 = row.createCell(1);
        cell1.setCellValue("小李");


        Row row1 = sheet.createRow(1);

        Cell cell2 = row1.createCell(0);
        cell2.setCellValue("日期");

        Cell cell3 = row1.createCell(1);
        String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
        cell3.setCellValue(time);

        FileOutputStream fileOutputStream = new FileOutputStream(path + "apache-07.xlsx");

        workbook.write(fileOutputStream);

        if(fileOutputStream != null) fileOutputStream.close();
        if(workbook != null) workbook.close();

        System.out.println("文件生成成功");
    }
}

写入速度示例：

    @Test
    public void testBigHssf() throws IOException {
        Instant start = Instant.now();
        HSSFWorkbook workbook = new HSSFWorkbook();

        HSSFSheet sheet = workbook.createSheet("hssf");

        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(j);
            }
        }

        FileOutputStream fileOutputStream = new FileOutputStream(path + "bigHssf.xls");

        workbook.write(fileOutputStream);

        if (fileOutputStream != null) fileOutputStream.close();
        if (workbook != null) workbook.close();
        Instant end = Instant.now();
        System.out.println(Duration.between(start, end).toMillis());
    }

    @Test
    public void testBigXssf() throws IOException {
        Instant start = Instant.now();
        Workbook workbook = new XSSFWorkbook();

        Sheet sheet = workbook.createSheet("hssf");

        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 fileOutputStream = new FileOutputStream(path + "bigXssf.xlsx");

        workbook.write(fileOutputStream);

        if (fileOutputStream != null) fileOutputStream.close();
        if (workbook != null) workbook.close();
        Instant end = Instant.now();
        System.out.println(Duration.between(start, end).toMillis());
    }

    @Test
    public void testBigSxssf() throws IOException {
        Instant start = Instant.now();
        Workbook workbook = new SXSSFWorkbook();

        Sheet sheet = workbook.createSheet("hssf");

        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 fileOutputStream = new FileOutputStream(path + "bigsXssf.xlsx");

        workbook.write(fileOutputStream);

        if (fileOutputStream != null) fileOutputStream.close();
        ((SXSSFWorkbook) workbook).dispose();
        if (workbook != null) workbook.close();
        Instant end = Instant.now();
        System.out.println(Duration.between(start, end).toMillis());
    }

4.easyExcel

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

EasyExcel是阿里巴巴开源的一个excel处理框架，以使用简单，节省内存著称。

节省内存的主要原因是在解析excel时没有将文件数据一次性全部加载到内存中，而是从磁盘上一行行读取数据，逐个解析。

读写excel只需要一行代码就可以完成。

详细的参照官网：读Excel | Easy Excel

5.总结

使用easyExcel让操作excel更加方便，如果数据量过大，不想一直存放在内存中，可以读取一些，处理一些，获取登录到数据库中。但有时要确保原子性，还要要放在内存中，所有数据校验都通过之后再存储到数据库中。

代码007普通

打赏 收藏 海报 链接