一、常用场景

1.将用户信息导出为excel表格
2.将excel的信息导入到数据库，减轻大量网站录入量开发经常使用到excel导入导出

1.1、Apache POI官网：

Apache POI项目的任务是创建和维护Java API，以基于Office Open
XML标准（OOXML）和Microsoft的OLE 2复合文档格式（OLE2）来处理各种文件格式。简而言之，您可以使用Java读写MS
Excel文件。此外，您可以使用Java读写MS Word和MS PowerPoint文件。Apache POI是您的Java Excel解决方案（适用于Excel 97-2008）。

1.2、easyexcel官网：

https://github.com/alibaba/easyexcel

二、POI使用

注意：xls03和xls07的区别（excel版本）：数据量的大小 03的只有65536行

03版的excel 只有65536行
07版excel 无限制

共通代码

创建maven项目
导入pom依赖

<dependencies>
  <dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter</artifactId>
  </dependency>
  <!--    xml(03)-->
  <dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.2</version>
  </dependency>
  <!--    xmlx(07)-->
  <dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.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.12</version>
  </dependency>
  <dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
    <scope>test</scope>
  </dependency>
  <dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <scope>test</scope>
  </dependency>
</dependencies>

2.1、写文件使用步骤

少量数据写入

03版本使用

@Test
    @SneakyThrows
    public void testExcelWrite(){
    // 创建一个工作簿
    HSSFWorkbook workbook = new HSSFWorkbook();
    // 创建一个工作表
    HSSFSheet sheet = workbook.createSheet("测试工作表01");
    // 创建一行数据（1，1）
    Row row1 = sheet.createRow(0);
    // 创建一个单员格
    Cell cell = row1.createCell(0);
    // 写入单元格数据
    cell.setCellValue("测试单元格");
    Cell cell12 = row1.createCell(1);
    cell12.setCellValue("123");
    // 创建第二行数据
    Row row2 = sheet.createRow(1);
    Cell cell1 = row2.createCell(0);
    cell1.setCellValue("时间");
    Cell cell2 = row2.createCell(1);
    cell2.setCellValue(new DateTime().toString("yyyy-MM-dd HH:mm:ss"));
    // 创建文件流
    FileOutputStream fileOutputStream = new FileOutputStream(PATH + "/测试写入excel数据.xls");
    // 工作簿文件流写入
    workbook.write(fileOutputStream);
    // 关闭流
    fileOutputStream.close();
    log.info("文件生成完毕！");
}

输出结果

07版本使用

  @Test
  @SneakyThrows
  public void test07ExcelWrite(){
    // 创建一个工作簿
    XSSFWorkbook workbook = new XSSFWorkbook();
    // 创建一个工作表
    XSSFSheet sheet = workbook.createSheet("测试工作表01");
    // 创建一行数据（1，1）
    Row row1 = sheet.createRow(0);
    // 创建一个单员格
    Cell cell = row1.createCell(0);
    // 写入单元格数据
    cell.setCellValue("测试单元格");
    Cell cell12 = row1.createCell(1);
    cell12.setCellValue("123");
    // 创建第二行数据
    Row row2 = sheet.createRow(1);
    Cell cell1 = row2.createCell(0);
    cell1.setCellValue("时间");
    Cell cell2 = row2.createCell(1);
    cell2.setCellValue(new DateTime().toString("yyyy-MM-dd HH:mm:ss"));
    // 创建文件流
    FileOutputStream fileOutputStream = new FileOutputStream(PATH + "/测试写入excel数据.xlsx");
    // 工作簿文件流写入
    workbook.write(fileOutputStream);
    // 关闭流
    fileOutputStream.close();
    log.info("文件生成完毕！");
  }

输出结果
生成的两个文件对比

区别

大文件写入HSSF

缺点：最多只能处理65536行否则会抛出异常
优点：过程中写入缓存，最后一次写入磁盘，速度快

大文件写入XSSF

缺点：写数据速度非常慢，消耗内存，也会内存溢出入100万条
优点：可以写较大的数据量

HSSF写入数据量条数超过65536 就会报错

java.lang.IllegalArgumentException: Invalid row number (65536) outside allowable range (0..65535)

批量数据写入

03版本批量数据写入

  /**
   * 测试大数据写入
   */
  @Test
  @SneakyThrows
  public void test03BigDataExcelWrite(){
    long strTime = System.currentTimeMillis();
    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet sheet = workbook.createSheet();
    for (int rowNum = 0; rowNum < 65536; rowNum++) {
      HSSFRow row = sheet.createRow(rowNum);
      for (int celNum = 0; celNum < 10; celNum++) {
        HSSFCell cell = row.createCell(celNum);
        cell.setCellValue(celNum);
      }
    }
    FileOutputStream fileOutputStream = new FileOutputStream(PATH + "/批量写入03版本.xls");
    workbook.write(fileOutputStream);
    fileOutputStream.close();
    long endTime = System.currentTimeMillis();
    log.info("使用时间为： {}",((double)(endTime-strTime)/1000));
  }
// 日志输出
// 15:37:37.055 [main] INFO com.yang.excelpoi.ExcelPoiApplicationTests - 使用时间为： 1.28

输入结果

07版本批量数据写入

  /**
   * 测试大数据写入
   */
  @Test
  @SneakyThrows
  public void test07BigDataExcelWrite(){
    long strTime = System.currentTimeMillis();
    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet sheet = workbook.createSheet();
    for (int rowNum = 0; rowNum < 65536; rowNum++) {
      XSSFRow row = sheet.createRow(rowNum);
      for (int celNum = 0; celNum < 10; celNum++) {
        XSSFCell cell = row.createCell(celNum);
        cell.setCellValue(celNum);
      }
    }
    FileOutputStream fileOutputStream = new FileOutputStream(PATH + "/批量写入07版本.xls");
    workbook.write(fileOutputStream);
    fileOutputStream.close();
    long endTime = System.currentTimeMillis();
    log.info("使用时间为： {}",((double)(endTime-strTime)/1000));
  }
// 日志输出
// 15:38:48.011 [main] INFO com.yang.excelpoi.ExcelPoiApplicationTests - 使用时间为： 5.255

输出

区别

XSSFWorkbook耗时较长，但插入的数据可以很多

解决XSSFWorkbook耗时较长问题（SXSSFWorkbook）

升级版07 加速运行时间但会产生临时文件，我们可以手动清除临时文件

  /**
   * 测试SXSSFWorkbook
   * 测试大数据写入
   */
  @Test
  @SneakyThrows
  public void test07BigDataExcelWriteS(){
    long strTime = System.currentTimeMillis();
    SXSSFWorkbook workbook = new SXSSFWorkbook();
    SXSSFSheet sheet = workbook.createSheet();
    for (int rowNum = 0; rowNum < 65536; rowNum++) {
      SXSSFRow row = sheet.createRow(rowNum);
      for (int celNum = 0; celNum < 10; celNum++) {
        SXSSFCell cell = row.createCell(celNum);
        cell.setCellValue(celNum);
      }
    }
    FileOutputStream fileOutputStream = new FileOutputStream(PATH + "/批量写入07S版本.xls");
    workbook.write(fileOutputStream);
    fileOutputStream.close();
    // 清除临时文件
    workbook.dispose();
    long endTime = System.currentTimeMillis();
    log.info("使用时间为： {}",((double)(endTime-strTime)/1000));
  }
// 日志输出
// 15:44:47.122 [main] INFO com.yang.excelpoi.ExcelPoiApplicationTests - 使用时间为： 2.255

输出

2.2、读文件步骤

03Excel测试读数据

    public static final String PATH = "/Users/yang/IdeaProjects/SoftLeaderGy/test-demo/test-demo/excel-poi/";
    /**
     * 03Excel测试读数据
     */
    @Test
    @SneakyThrows
    public void excel03ReadTest(){
        FileInputStream fileInputStream = new FileInputStream(PATH + "测试写入excel数据.xls");
        HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream);
        HSSFSheet sheetAt = workbook.getSheetAt(0);
        HSSFRow row = sheetAt.getRow(0);
        HSSFCell cell = row.getCell(0);
        System.out.println(cell.getStringCellValue());
    }
// 输出 ： 测试单元格

07Excel测试读数据

    /**
     * 07Excel测试读数据
     */
    @Test
    @SneakyThrows
    public void excel07ReadTest(){
        FileInputStream fileInputStream = new FileInputStream(PATH + "测试写入excel数据.xlsx");
        XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream);
        XSSFSheet sheetAt = workbook.getSheetAt(0);
        XSSFRow row = sheetAt.getRow(0);
        XSSFCell cell = row.getCell(1);
        System.out.println(cell.getStringCellValue());
    }
// 输出： 123qwe

注意点：数据类型

如果excel里的数据与代码中获取的类型不匹配就会报错

java.lang.IllegalStateException: Cannot get a STRING value from a NUMERIC cell

例如

excel中的是数字类型
代码中使用获取字符串的方法获取
就会报错：java.lang.IllegalStateException: Cannot get a STRING value from a NUMERIC cell

日期类型：

getDateCellValue() 日期
getStringCellValue() 字符串
getBooleanCellValue()布尔类型

07读取excel不同数据类型数据

    /**
     * 07Excel测试读不同数据类型数据
     */
    @Test
    @SneakyThrows
    public void testCellType(){
        FileInputStream fileInputStream = new FileInputStream(PATH + "测试写入excel数据.xlsx");
        XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream);
        XSSFSheet sheetAt = workbook.getSheetAt(0);
        XSSFRow rowTitle = sheetAt.getRow(0);
        int cellNum = rowTitle.getPhysicalNumberOfCells();
        for (int i = 0; i < cellNum; i++) {
            XSSFCell cell = rowTitle.getCell(i);
            if (cell != null) {
                CellType cellType = cell.getCellType();
                String stringCellValue = cell.getStringCellValue();
                System.out.print(stringCellValue + " | ");
            }
        }
        System.out.println();
        // 读数据
        // 获取excel行数
        int physicalNumberOfRows = sheetAt.getPhysicalNumberOfRows();
        for (int rowNum = 1; rowNum < physicalNumberOfRows; rowNum++) {
            XSSFRow rowData = sheetAt.getRow(rowNum);
            if (rowData != null) {
                int cellCount = rowData.getPhysicalNumberOfCells();
                for (int i = 0; i < cellCount; i++) {
                    XSSFCell cell = rowData.getCell(i);
                    if (cell != null) {
                        String cellValue="";
                        CellType cellType = cell.getCellType();
                        switch (cellType){
                            case STRING:cellValue = cell.getStringCellValue();
                                break;//string类型
                            case BOOLEAN:cellValue = String.valueOf(cell.getBooleanCellValue());
                                break;//布尔类型
                            case NUMERIC:
                                if (HSSFDateUtil.isCellDateFormatted(cell)){
                                    Date dateCellValue = cell.getDateCellValue();
                                    cellValue = new DateTime().toString("yyyy-MM-dd");
                                    break;
                                } else {
                                    // 数字以字符串的形式进行输出
                                    cell.setCellType(CellType.STRING);
                                    cellValue = cell.getStringCellValue();
                                    break;
                                }
                        }
                        System.out.print(cellValue + " | ");
                    }
                }
            }
        }
        fileInputStream.close();
    }

计算公式

代码

//计算公式
    @Test
    public void test04() throws IOException {
        FileInputStream fileInputStream = new FileInputStream("H:\\CSDN\\POI\\统计表07.xlsx");
        Workbook workbook = new XSSFWorkbook(fileInputStream);
        Sheet sheet = workbook.getSheetAt(0);//获取第1个工作表
        Row row = sheet.getRow(4);//得到一行
        Cell cell = row.getCell(0);
        //拿到计算公式
        FormulaEvaluator formulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
        //拿到单元格内容
        int cellType = cell.getCellType();
        switch (cellType){
            case Cell.CELL_TYPE_FORMULA://公式
                String cellFormula = cell.getCellFormula();
                System.out.println(cellFormula);//输出公式
                //计算
                CellValue evaluate = formulaEvaluator.evaluate(cell);
                String cellValue = evaluate.formatAsString();
                System.out.println(cellValue);
        }
        fileInputStream.close();
    }

输出

三、easyexcel使用

导入依赖

<!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
<dependency>
  <groupId>com.alibaba</groupId>
  <artifactId>easyexcel</artifactId>
  <version>2.2.6</version>
</dependency>

3.1、简单的写

public class TestEasyExcel {
    public static final String PATH = "/Users/yang/IdeaProjects/SoftLeaderGy/test-demo/test-demo/excel-poi/";
    private List<DemoData> data() {
        List<DemoData> list = new ArrayList<>();
        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 + "easyexcel.xlsx";
        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        // 如果这里想使用03 则 传入excelType参数即可
        // write(文件路径，格式类)
        // sheet(工作表名)
        // doWrite(数据)
        EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data());
    }
}

3.2、简单的读

编写监听器

package com.yang.excelpoi.easyexcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.CellExtra;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.fastjson.JSON;
import com.yang.excelpoi.easyexcel.dao.DemoDAO;
import com.yang.excelpoi.easyexcel.dto.DemoData;
import com.yang.excelpoi.easyexcel.dto.DemoReadData;
import lombok.extern.slf4j.Slf4j;
import org.slf4j.LoggerFactory;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.logging.Logger;
// 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
@Slf4j
// 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
public class DemoDataListener extends AnalysisEventListener<DemoReadData> {
    /**
     * 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 5;
    List<DemoReadData> list = new ArrayList<DemoReadData>();
    /**
     * 假设这个是一个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(DemoReadData 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();//保存数据库
        log.info("所有数据解析完成！");
    }
    /**
     * 加上存储数据库
     */
    private void saveData() {
        log.info("{}条数据，开始存储数据库！", list.size());
        demoDAO.save(list);
        log.info("存储数据库成功！");
    }
}

编写dao层可不写用来持久化操作（模拟）

/**
 * 假设这个是你的DAO存储。当然还要这个类让spring管理，当然你不用需要存储，也不需要这个类。
 **/
public class DemoDAO {
    public void save(List<DemoData> list) {
        // 如果是mybatis,尽量别直接调用多次insert,自己写一个mapper里面新增一个方法batchInsert,所有数据一次性插入
    }
}

测试读取数据

package com.yang.excelpoi.easyexcel;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.yang.excelpoi.easyexcel.dto.DemoData;
import com.yang.excelpoi.easyexcel.dto.DemoReadData;
import org.junit.Test;
/**
 * @Description:
 * @Author: Guo.Yang
 * @Date: 2022/09/20/22:01
 */
public class TestReadEasyExcel {
    public static final String PATH = "/Users/yang/IdeaProjects/SoftLeaderGy/test-demo/test-demo/excel-poi/";
    /**
     * 最简单的读
     * <p>1. 创建excel对应的实体对象 参照{@link DemoData}
     * <p>2. 由于默认一行行的读取excel，所以需要创建excel一行一行的回调监听器，参照{@link DemoDataListener}
     * <p>3. 直接读即可
     */
    @Test
    public void simpleRead() {
        // 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
        // 写法1：
        String fileName = PATH + "easyexcel.xlsx";
        // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
        EasyExcel.read(fileName, DemoReadData.class, new DemoDataListener()).sheet().doRead();
    }
}

输出

3.3、链接

Easy Excel 官网

Excel处理

一、常用场景

1.1、Apache POI官网：

1.2、easyexcel官网：

二、POI使用

共通代码

2.1、写文件使用步骤

少量数据写入

03版本使用

07版本使用

区别

批量数据写入

03版本批量数据写入

07版本批量数据写入

区别

解决XSSFWorkbook耗时较长问题（SXSSFWorkbook）

2.2、读文件步骤

03Excel测试读数据

07Excel测试读数据

注意点：数据类型

07读取excel不同数据类型数据

计算公式

三、easyexcel使用

3.1、简单的写

3.2、简单的读

3.3、链接

热门文章

最新文章

相关电子书

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

Excel处理

一、常用场景

1.1、Apache POI官网：

1.2、easyexcel官网：

二、POI使用

共通代码

2.1、写文件使用步骤

少量数据写入

03版本使用

07版本使用

区别

批量数据写入

03版本批量数据写入

07版本批量数据写入

区别

解决XSSFWorkbook耗时较长问题（SXSSFWorkbook）

2.2、读文件步骤

03Excel测试读数据

07Excel测试读数据

注意点 ：数据类型

07读取excel不同数据类型数据

计算公式

三、easyexcel使用

3.1、简单的写

3.2、简单的读

3.3、链接

热门文章

最新文章

相关电子书

注意点：数据类型