Java 操作 Excel

Java 操作 Excel

还记得IO里说过一句话吗？一切皆字节，excel的xls当然也可以用IO流读写。

但相当的麻烦，所以需要借助第三方接口、组件快速操作Excel！

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

常用场景

1. 将用户信息导出为excel表格（导出数据）
2. 将Excel表中的信息录入到网站数据库（数据上传）

POI

什么是POI ？

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

原生的源码库使用起来会比较麻烦，于是出现了easyExcel。

POI官网

实例测试

1. 首先创建一个空项目

   

2. 在项目结构（快捷键Ctrl + Shift + Alt + s）中选择合适的SDK

   

3. 在Modules模块添加POI项目

   

   

   下一步完成项目创建！

4. 在pom.xml中导入依赖

   <!-- 导入依赖-->
       <dependencies>
           <!--xls(03)-->
           <dependency>
               <groupId>org.apache.poi</groupId>
               <artifactId>poi</artifactId>
               <version>3.9</version>
           </dependency>
           <!--xls(07)-->
           <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>
       </dependencies>
   

   

   

   导入依赖时会显示很多红色错误，稍等几分钟等待IDEA自动下载依赖后就不报错了。

5. 编写相关代码

POI-Excel写数据

package com.poi;

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.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;

public class ExcelWriteTest {

    @Test
    public void testWirte01() throws IOException {
        // 1.创建工作簿 HSSF是03版本
        Workbook workbook = new HSSFWorkbook();
        // 2.通过工作薄 -> 创建工作表
        Sheet sheet = workbook.createSheet("成绩表");
        // 3.通过工作表 -> 创建行
        Row row1 = sheet.createRow(0);
        // 4.通过行 -> 创建单元格（A1） 并填充数据
        Cell cell = row1.createCell(0);
        cell.setCellValue("张三");

        Cell cell2 = row1.createCell(1);
        cell2.setCellValue(99);

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


        // 保存文件
        File file = new File(".\成绩表.xls");
        FileOutputStream fos = null;
        try{
            fos = new FileOutputStream(file);
            workbook.write(fos);
            System.out.println("完成");

        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } finally {
            if (fos != null) {
                fos.close();
            }
        }

    }


    @Test
    public void testWirte02(){
        // 1.创建工作簿 XSSF是07版本
        Workbook workbook = new XSSFWorkbook();
        // 2.通过工作薄 -> 创建工作表
        Sheet sheet = workbook.createSheet("成绩表");
        // 3.通过工作表 -> 创建行
        Row row1 = sheet.createRow(0);
        // 4.通过行 -> 创建单元格（A1） 并填充数据
        Cell cell = row1.createCell(0);
        cell.setCellValue("张三");

        Cell cell2 = row1.createCell(1);
        cell2.setCellValue(99);

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


        // 保存文件
        File file = new File(".\成绩表2.xlsx");
        FileOutputStream fos = null;
        try{
            fos = new FileOutputStream(file);
            workbook.write(fos);
            System.out.println("完成");

        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (fos != null) {
                try {
                    fos.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }

    }
}

面向接口编程的好处， testWirte02只将HSSFWorkbook()修改成XSSFWorkbook() 其他的大量代码相同。

还有最后保存文件时，注意两个版本的文件后缀是不一样的

• 2003版 后缀 .xls 最大65536行
• 2007版 后缀 .xlsx 无上限

数据批量导入

大文件使用HSSF (03)

• 缺点：最多只能处理65536行，否则抛出异常
• 优点：速度快！过程写入缓存，不操作硬盘，最后一次性写入磁盘

@Test
public void testWirte01() throws IOException {
    // 开始时间
    long s = System.currentTimeMillis();
    // 1.创建工作簿 HSSF是03版本
    Workbook workbook = new HSSFWorkbook();
    // 2.通过工作薄 -> 创建工作表
    Sheet sheet = workbook.createSheet("测试表");
    // 3.大量数据写入 03版一旦rowNum超过65536就会报错
    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+1);
        }
    }

    // 保存文件
    File file = new File(".\03版填满.xls");
    FileOutputStream fos = null;
    try{
        fos = new FileOutputStream(file);
        workbook.write(fos);
        System.out.println("完成");

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } finally {
        if (fos != null) {
            fos.close();
        }
    }
    long e = System.currentTimeMillis();
    System.out.println((double)(e-s)/1000);
}

大文件使用XSSF (07)

• 缺点：非常慢！而且耗内存！也存在内存溢出，如上百万条
• 优点：可以写超过65536行的数据量，如20万条

@Test
public void testWirte02() throws IOException {
    // 开始时间
    long s = System.currentTimeMillis();
    // 1.创建工作簿 HSSF是03版本
    Workbook workbook = new XSSFWorkbook();
    // 2.通过工作薄 -> 创建工作表
    Sheet sheet = workbook.createSheet("测试表");
    // 3.大量数据写入 XSSF 07版不限制行数
    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+1);
        }
    }

    // 保存文件
    File file = new File(".\07版填满.xlsx");
    FileOutputStream fos = null;
    try{
        fos = new FileOutputStream(file);
        workbook.write(fos);
        System.out.println("完成");

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } finally {
        if (fos != null) {
            fos.close();
        }
    }
    long e = System.currentTimeMillis();
    System.out.println((double)(e-s)/1000);
}

XSSF 优化接口 SXSSF

@Test
public void testWirte03() throws IOException {
    // 开始时间
    long s = System.currentTimeMillis();
    // 1.创建工作簿 HSSF是03版本
    Workbook workbook = new SXSSFWorkbook();
    // 2.通过工作薄 -> 创建工作表
    Sheet sheet = workbook.createSheet("测试表");
    // 3.大量数据写入 XSSF 07版不限制行数
    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+1);
        }
    }

    // 保存文件
    File file = new File(".\07版填满加强.xlsx");
    FileOutputStream fos = null;
    try{
        fos = new FileOutputStream(file);
        workbook.write(fos);
        System.out.println("完成");

    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } finally {
        if (fos != null) {
            fos.close();
        }
    }
    // 清理临时文件
    ((SXSSFWorkbook) workbook).dispose();
    long e = System.currentTimeMillis();
    System.out.println((double)(e-s)/1000);
}

SXSSF又快又可以写入大量数据

POI-Excel读数据

package com.poi;

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.junit.Test;

import java.io.FileInputStream;

public class ExcelReadTest {
    @Test
    public void testRead03() throws Exception {
        // 获取文件流
        FileInputStream fis = new FileInputStream("成绩表.xls");
        // 创建工作簿 03版本
        Workbook workbook = new HSSFWorkbook(fis);
        // 获取工作表
        Sheet sheet = workbook.getSheetAt(0);
        // 获取行
        Row row = sheet.getRow(0);
        // 获取单元格
        Cell cell = row.getCell(0);
        // 获取单元格的内容
        String value = cell.getStringCellValue();
        System.out.println(value);
        fis.close();
    }

    @Test
    public void testRead07() throws Exception {
        // 获取文件流
        FileInputStream fis = new FileInputStream("成绩表2.xlsx");
        // 创建工作簿 07版本
        Workbook workbook = new XSSFWorkbook(fis);
        // 获取工作表
        Sheet sheet = workbook.getSheetAt(0);
        // 获取行
        Row row = sheet.getRow(0);
        // 获取单元格
        Cell cell = row.getCell(0);
        // 获取单元格的内容
        String value = cell.getStringCellValue();
        System.out.println(value);
        fis.close();
    }
}

注意

test.xls

学号	姓名	课程	分数
17003009	张三	高数	95
17003008	李四	计网	90
17003007	王五	大英	75
			260

获取单元格的公式（了解）

上表格中分数汇总的260是=SUM(D2:D4) 得到的

@Test
public void testFormula() throws Exception {
    // 首先拿到文件
    FileInputStream fis = new FileInputStream(".\test.xls");
    // 获取工作牌
    Workbook workbook = new HSSFWorkbook(fis);
    // 获取工作表
    Sheet sheet = workbook.getSheetAt(0);
    // 定位单元格
    Row row = sheet.getRow(4);
    Cell cell = row.getCell(3);

    // 直接获取单元格的值
    double cellValue = cell.getNumericCellValue();
    System.out.println("单元格的值：" + cellValue);

    // 创建公式类实例
    FormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);

    //输出单元格公式以内容
    int cellType = cell.getCellType();
    switch (cellType) {
        case CELL_TYPE_FORMULA:
            // 拿到计算公式
            String formula = cell.getCellFormula();
            System.out.println("单元格的公式：" + formula);
            // 统计计算器计算单元格的值
            CellValue value = formulaEvaluator.evaluate(cell);
            // 打印的结果
            String s = value.formatAsString();
            System.out.println("公式算来的值：" + s);
            break;
        default:
            break;
    }

    fis.close();
}

运行结果

单元格的值：260.0
单元格的公式：SUM(D2:D4)
公式算来的值：260.0

封装成的工具类

package com.poi;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.joda.time.DateTime;
import org.junit.Test;

import java.io.FileInputStream;
import java.util.Date;

import static org.apache.poi.ss.usermodel.Cell.*;

public class ExcelPOIUtil {
    @Test
    public void testCellType() throws Exception {
        // 获取文件流 改造工具类是把下面这行写到方法参数，通过传路径实现读表
        FileInputStream fis = new FileInputStream("test.xls");
        // 创建工作簿
        Workbook workbook = new HSSFWorkbook(fis);
        // 获取工作表
        Sheet sheet = workbook.getSheetAt(0);
        // 读取第一行 属性字段
        Row rowTitle = sheet.getRow(0);
        if (rowTitle != null) {
            int cells = rowTitle.getPhysicalNumberOfCells();
            for (int i = 0; i < cells; i++) {
                // 获取单元格
                Cell cell = rowTitle.getCell(i);
                if (cell != null) {
                    int type = cell.getCellType();
                    String s = cell.getStringCellValue();
                    System.out.print(s + "	");
                }
            }
        }
        System.out.println();
        int rows = sheet.getPhysicalNumberOfRows();
        for (int i = 1; i < rows; i++) {
            Row row = sheet.getRow(i);
            if (row != null) {
                int cells = row.getPhysicalNumberOfCells();
                for (int j = 0; j < cells; j++) {
                    System.out.print("（" + (char) ('A' + i) + "" + (j + 1) + ") ");
                    Cell cell = row.getCell(j);
                    // 匹配类型！！难点
                    if (cell != null) {
                        // 首先获取单元格数据类型，但是以数字表示
                        int type = cell.getCellType();
                        System.out.print(type);
                        String value = "";
                        // 需要switch匹配相应的类型
                        switch (type) {
                            case CELL_TYPE_STRING:
                                System.out.print("【String】");
                                value = cell.getStringCellValue();
                                break;
                            case CELL_TYPE_BOOLEAN:
                                System.out.print("【Boolean】");
                                value = String.valueOf(cell.getBooleanCellValue());
                                break;
                            case CELL_TYPE_NUMERIC:
                                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                    System.out.print("【日期】");
                                    Date data = cell.getDateCellValue();
                                    value = new DateTime(data).toString("yyyy-HH-mm HH:mm:ss");

                                } else {
                                    System.out.print("【数字串】");
                                    cell.setCellType(CELL_TYPE_STRING);
                                    value = cell.toString();

                                }
                                break;

                            case CELL_TYPE_ERROR:
                                System.out.print("【Error】");
                                break;
                            case CELL_TYPE_BLANK:
                                System.out.print("【Blank】");
                                break;
                            default:
                                break;
                        }
                        System.out.println(value);
                    }
                }

            }
        }

        fis.close();
    }
}

运行结果

学号	姓名	课程	分数	
（B1) 0【数字串】17003009
（B2) 1【String】张三
（B3) 1【String】高数
（B4) 0【数字串】95
（C1) 0【数字串】17003008
（C2) 1【String】李四
（C3) 1【String】计网
（C4) 0【数字串】90
（D1) 0【数字串】17003007
（D2) 1【String】王五
（D3) 1【String】大英
（D4) 0【数字串】75
（E1) 1【String】 
（E2) 1【String】 
（E3) 3【Blank】
（E4) 2【Formula】公式=SUM(D2:D4) 结果：0.0

双手挠头，为什么获取公式单独测试时能够准确计算出公式的结果260，将该模块添加到工具类之后，结果又除了问题。

查了好多资料没找到原因，去问问狂老师，等后续解惑之后再上来更新。

easyExcel

easyExcel是什么？

快速、简单避免OOM的java处理Excel工具 。

Java解析、生成Excel比较有名的框架有Apache poi、jxl。

但他们都存在一个严重的问题就是非常的耗内存，poi有一套SAX模式的API可以一定程度的解决一些内存溢出的问题，

但POI还是有一些缺陷，比如07版Excel解压缩以及解压后存储都是在内存中完成的，内存消耗依然很大。

easyexcel重写了poi对07版Excel的解析，能够原本一个3M的excel用POI sax依然需要100M左右内存降低到几M，

并且再大的excel不会出现内存溢出，03版依赖POI的sax模式。在上层做了模型转换的封装，让使用者更加简单方便

easyExcel官网

easyExcel官方文档

首先从easyExcel项目上拷贝pom.xml的依赖

<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.3</version>

小结-学习方式

了解面向对象的思想，学会面向接口编程。

理解使用测试API

视频教程Bilibili：遇见狂神说 关注公众号：狂神说
作业：后期把easyExcel官方文档的API都测试一下，再补充上面easyExcel知识。