POI和EasyExcel读写Excel表格

  • POI 一次性把数据读到内存中,所以可能会造成内存溢出
  • EasyExcel 是读一行写一行,很难造成内存溢出

相关依赖

<!--xls-->
<dependency>
     <groupId>org.apache.poi</groupId>
     <artifactId>poi</artifactId>
     <version>4.1.2</version>
</dependency>
<!--xlsx-->
<dependency>
     <groupId>org.apache.poi</groupId>
     <artifactId>poi-ooxml</artifactId>
     <version>4.1.2</version>
</dependency>

Excel表格构造

在这里插入图片描述

工作簿里面有工作表,工作表里面有行和列,行和列就能定位到具体的单元格


Excel表格写操作

1. HSSFWorkbook类是EXcel.xls的操作类

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

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


public class ExcelWriteTest {

    // 操作Excel后缀为xls的文件
    @Test
    public void testWriteXLS() throws IOException {
        // 1.创建一个工作簿
        Workbook workbook = new HSSFWorkbook();
        // 2.创建一个工作表
        Sheet sheet1 = workbook.createSheet("学分绩统计表");
        // 3.创建一个行,0行就是第一行
        Row row1 = sheet1.createRow(0);
        // 4.创建此行的第一个单元格
        Cell cell1 = row1.createCell(0);
        // 5.为此单元格写一个值
        cell1.setCellValue(100);

        // 此时上面设置好的工作簿还在内存,要写到硬盘里面,用文件输出字节流
        FileOutputStream fos = new FileOutputStream("D:\\Excel表格\\学分统计工作簿.xls");

        workbook.write(fos);

        fos.close();

        System.out.println("表格生成完毕");

    }

}

2. XSSFWorkbook类是EXcel.xlsx的操作类

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


public class ExcelWriteTest {

    // 操作Excel后缀为xlsx的文件
    @Test
    public void testWriteXLSX() throws IOException {
        // 1.创建一个工作簿
        Workbook workbook = new XSSFWorkbook();
        // 2.创建一个工作表
        Sheet sheet1 = workbook.createSheet("学分绩统计表");
        // 3.创建一个行,0行就是第一行
        Row row1 = sheet1.createRow(0);
        // 4.创建此行的第一个单元格
        Cell cell1 = row1.createCell(0);
        // 5.为此单元格写一个值
        cell1.setCellValue(100);

        // 此时上面设置好的工作簿还在内存,要写到硬盘里面,用文件输出字节流
        FileOutputStream fos = new FileOutputStream("D:\\Excel表格\\学分统计工作簿.xlsx");

        workbook.write(fos);

        fos.close();

        System.out.println("表格生成完毕");

    }

}

这就是面向接口编程,代码基本不用变

3. SXSSFWorkbook类是xlsx超大量数据操作类

package POI;

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.streaming.SXSSFWorkbook;
import org.junit.Test;

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


public class ExcelWriteBigDataTest {

    @Test
    public void testSXSSFWorkbook() throws IOException {
        Workbook workbook = new SXSSFWorkbook();
        Sheet sheet1 = workbook.createSheet();
        // 10w行记录,一行记录10列
        for(int rowNum=0;rowNum<100000;rowNum++){
            Row row = sheet1.createRow(rowNum);
            for(int cellNum=0;cellNum<10;cellNum++){
                Cell cell = row.createCell(cellNum);
                cell.setCellValue(cellNum);
            }
        }
        FileOutputStream fos = new FileOutputStream("D:\\Excel表格\\学分统计工作簿1.xlsx");
        workbook.write(fos);
        fos.close();
        
        // 清楚临时文件
        ((SXSSFWorkbook) workbook).dispose();

        System.out.println("创建完毕");
    }
}
  • 过程中会产生临时文件,需要清理临时文件
  • 默认由100条记录被保存在内存中,如果超过这数量,则最前面的数据被写入临时文件
  • 如果想自定义内存中数据的数量,可以使用new SXSSFWorkbook(数量)

Excel表格读操作

简单读取

package POI;


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

public class ExcelReadTest {

    @Test
    public void readTest() throws IOException {

        // 把Excel表格转换成数据流
        FileInputStream fis = new FileInputStream("D:\\Excel表格\\学分统计工作簿1.xlsx");

        // 把数据流转化成jvm内存中的虚拟工作簿(此时虚拟的等同于硬盘中的)
        Workbook workbook = new XSSFWorkbook(fis);
        // 获取工作簿中下标为0的表,也就是第一个表
        Sheet sheet1 = workbook.getSheetAt(0);
        // 获取表中第一行
        Row row1 = sheet1.getRow(0);
        // 获取该行的第一个单元格
        Cell cell1 = row1.getCell(0);
        // 获取该单元格的值(numeric型)要注意表中的值的判断
        System.out.println(cell1.getNumericCellValue());

        fis.close();

    }

}

Excel表格不同数据类型值的读取

在这里插入图片描述

package POI;


import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;
import org.junit.Test;

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

public class TypeReadTest {
    @Test
    public void Test() throws IOException {

        FileInputStream fis = new FileInputStream("D:\\Excel表格\\订单.xlsx");

        Workbook workbook = new XSSFWorkbook(fis);

        Sheet sheet1 = workbook.getSheetAt(0);

        // 获取第一行
        Row rowTitle = sheet1.getRow(0);
        if(rowTitle!=null){
            // 获取第一行的列数量,getPhysicalNumberOfCells()获取行的列数量,也就是此行有多少个单元格
            int cellCount = rowTitle.getPhysicalNumberOfCells();
            for (int cellNum = 0; cellNum < cellCount; cellNum++){
                Cell cell = rowTitle.getCell(cellNum);
                if(cell!=null){
                    // 获取每一个单元格中的标题
                    String cellValue = cell.getStringCellValue();
                    System.out.print(cellValue + "|");
                }
            }
        }
        System.out.println();

        // 获取表中的数据内容
        // getPhysicalNumberOfRows() 获取表中的行数量
        int rowCount = sheet1.getPhysicalNumberOfRows();
        for(int rowNum = 1;rowNum < rowCount;rowNum++){
            // 循环获取0行到最后一行
            Row row = sheet1.getRow(rowNum);
            if(row!=null){
                // 获取到此行的单元格数量
                int cellCount = rowTitle.getPhysicalNumberOfCells();
                for (int cellNum=0;cellNum < cellCount; cellNum++){
                    // 循环获取每一个单元格
                    Cell cell = row.getCell(cellNum);
                    // 匹配单元格的数据类型
                    if(cell!=null){
                        CellType cellType = cell.getCellType();
                        // 最终统一转化为字符串类型进行输出
                        String cellValue = "";

                        switch (cellType){
                            case STRING:
                                System.out.print("字符串:");
                                cellValue = cell.getStringCellValue();
                                break;
                            case BOOLEAN:
                                System.out.print("布尔:");
                                cellValue =String.valueOf(cell.getBooleanCellValue());
                                break;
                            case BLANK:
                                System.out.print("空");
                                break;
                            case NUMERIC:
                                // NUMERIC:数值型包括日期和数字,要进行处理
                                if(DateUtil.isCellDateFormatted(cell)){
                                    System.out.print("日期:");
                                    Date date = cell.getDateCellValue();
                                    cellValue = new DateTime(date).toString("yyyy-MM-dd");
                                }else {
                                    // 不是日期格式防止数字过长
                                    System.out.print("数字:");
                                    cell.setCellType(CellType.STRING);
                                    cellValue = cell.toString();
                                }
                                break;
                        }
                        // 对捕获到相应类型的值进行处理,这里只是输出
                        System.out.println(cellValue);
                    }
                }
                System.out.println("-------------------");
            }
        }
        fis.close();
    }
}

需要注意判断cell非空是为了防止空指针异常,null和blank不是同一个意思,这里对空cell过滤掉后依旧要对blank进行处理


EasyExcel的使用

Dome.xlsx

在这里插入图片描述

EasyExcel写

1.格式类
package POI.pojo;


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;
}
2.写入类
package POI;


import POI.pojo.DemoData;
import com.alibaba.excel.EasyExcel;
import org.junit.Test;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class EasyExcelTest {

    private final static String PATH = "D:\\Excel表格\\";

	// 数据
    private List<DemoData> data() {
        List<DemoData> list = new ArrayList<DemoData>();
        // 十行数据,也可以当作十个实例化对象
        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 + "Demo.xlsx";
        // 这里 需要指定写用哪个class去写,然后写到第一个sheet,名字为模板 然后文件流会自动关闭
        EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data());

    }

}

EasyExcel
.write(fileName, DemoData.class) 创建fileName表格按照DemoData.class格式输出
.sheet(“模板”) 创建名为模板的表
.doWrite(data()) 写入数据

EasyExcel读

往往我们读取硬盘上的表格调用DAO层往数据库里面写

1.格式类
package POI.pojo;

import lombok.Data;
import java.util.Date;

@Data
public class DemoData {
    private String string;
    private Date date;
    private Double doubleData;
}
2.监听器
package POI;


import POI.pojo.DemoData;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;

import java.util.ArrayList;
import java.util.List;

// 有个很重要的点 DemoDataListener 不能被spring管理,要每次读取excel都要new,然后里面用到spring可以构造方法传进去
public class DemoDataListener extends AnalysisEventListener<DemoData> {
    /**
     * 每隔5条存储数据库,实际使用中可以3000条,然后清理list ,方便内存回收
     */
    private static final int BATCH_COUNT = 5;
    List<DemoData> list = new ArrayList<DemoData>();
    /**
     * 假设这个是一个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) {
        System.out.println(JSON.toJSONString(data));
        list.add(data);
        // 达到BATCH_COUNT了,需要去存储一次数据库,防止数据几万条数据在内存,容易OOM
        if (list.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            list.clear();
        }
    }
    /**
     * 所有数据解析完成了 都会来调用
     *
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 这里也要保存数据,确保最后遗留的数据也存储到数据库
        saveData();
    }
    /**
     * 加上存储数据库
     */
    private void saveData() {
        demoDAO.save(list);
    }
}
3.读类
package POI;

import POI.pojo.DemoData;
import com.alibaba.excel.EasyExcel;
import org.junit.Test;


public class EasyReadTest {

    private final static String PATH = "D:\\Excel表格\\";

    /**
     * 最简单的读
     * <p>1. 创建excel对应的实体对象 参照{@link DemoData}
     * <p>2. 由于默认一行行的读取excel,所以需要创建excel一行一行的回调监听器,参照{@link DemoDataListener}
     * <p>3. 直接读即可
     */
    @Test
    public void simpleRead() {
        // 写法1:
        String fileName = PATH + "demo.xlsx";
        // 这里 需要指定读用哪个class去读,然后读取第一个sheet 文件流会自动关闭
        EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead();

    }

}

具体参考阿里巴巴文档

  • 1
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 6
    评论
评论 6
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值