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>

    <!-- xls(03) -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.9</version>
    </dependency>

    <!-- xlsx(07) -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>5.2.2</version>
    </dependency>


    <!-- 日期格式化工具 -->
    <dependency>
        <groupId>joda-time</groupId>
        <artifactId>joda-time</artifactId>
        <version>2.10.1</version>
    </dependency>

    <!-- 测试依赖 -->
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.13.2</version>
    </dependency>

</dependencies>

版本介绍

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

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

大文件写HSSF

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

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

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

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)+"毫秒");
    }

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)+"毫秒");
    }

进行更快的写入测试如:

使用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)+"毫秒");
    }

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

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

不同类型读取

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;
                }
            }
        }
    }

计算公式(了解即可)

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;
        }

4.EasyExcel操作

导入依赖

        <!--easyExcel-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.1.1</version>
        </dependency>

        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.13.2</version>
        </dependency>

写入测试

实体类: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;
}

写入测试:

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

}

读取测试

如果需要读取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("存储数据库成功！");
    }
}

读取:

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

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