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

布满荆棘的人生 2024-02-20 10:09 101阅读 0赞

**目录**

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包是不同的。如下：

<dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
                <version>4.1.2</version>
            </dependency>
            
            <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][Excel _ Easy Excel]

#### 5.总结 ####

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

[Excel _ Easy Excel]: https://easyexcel.opensource.alibaba.com/docs/current/quickstart/read