Java操作Excel

1. POI

常用场景：

将用户信息导出到Excel
将Excel表中的信息录入到网站数据库

Apache POI是用Java编写的免费开源的跨平台的Java API，Apache POI提供API给Java程序对Microsoft Office格式档案读和写的功能。

HSSF －提供读写Microsoft Excel XLS格式档案的功能。

XSSF －提供读写Microsoft Excel OOXML XLSX格式档案的功能。

HWPF －提供读写Microsoft WordDOC格式档案的功能。

HSLF－提供读写Microsoft PowerPoint格式档案的功能。

HDGF －提供读Microsoft Visio格式档案的功能。

HPBF －提供读Microsoft Publisher格式档案的功能。

HSMF －提供读Microsoft Outlook格式档案的功能。

1.1 POI写

创建普通Maven项目
导入依赖


    
    
        org.apache.poi
        poi
        4.1.2
    
    
    
        org.apache.poi
        poi-ooxml
        4.1.2
    
    
    
        joda-time
        joda-time
        2.10.10
    
    
        junit
        junit
        4.13.2

对象：

工作簿
工作表
行
列

普通文件

package com.hua;

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

public class ExcelWrite {

    private String path = "D:\java代码\Excel\POI";

   @Test
    public void testWrite03() throws Exception {
       // 1. 创建工作簿
       Workbook workbook = new HSSFWorkbook();
       // 2. 工作表
       Sheet sheet = workbook.createSheet("学生成绩表");
       // 3. 创建一行

       // 第一行
       Row row1 = sheet.createRow(0);
       // 4. 创建单元格
       Cell cell11 = row1.createCell(0);
       cell11.setCellValue("学号");
       Cell cell12 = row1.createCell(1);
       cell12.setCellValue("姓名");
       Cell cell13 = row1.createCell(2);
       cell13.setCellValue("入学时间");

       // 第二行
       Row row2 = sheet.createRow(1);
       Cell cell21 = row2.createCell(0);
       cell21.setCellValue("2022001");
       Cell cell22 = row2.createCell(1);
       cell22.setCellValue("张三");
       Cell cell23 = row2.createCell(2);
       String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
       cell23.setCellValue(time);


       // 生成表  03版本使用xls结尾
       FileOutputStream fos = new FileOutputStream(path + "学生信息统计表.xls");
       workbook.write(fos);
       fos.close();
       System.out.println("学生信息统计表.xls 已生成");
   }
   @Test
   public void testWrite07() throws Exception{
      // 1. 创建工作簿
      Workbook workbook = new XSSFWorkbook();
      // 2. 工作表
      Sheet sheet = workbook.createSheet("学生成绩表");
      // 3. 创建一行

      // 第一行
      Row row1 = sheet.createRow(0);
      // 4. 创建单元格
      Cell cell11 = row1.createCell(0);
      cell11.setCellValue("学号");
      Cell cell12 = row1.createCell(1);
      cell12.setCellValue("姓名");
      Cell cell13 = row1.createCell(2);
      cell13.setCellValue("入学时间");

      // 第二行
      Row row2 = sheet.createRow(1);
      Cell cell21 = row2.createCell(0);
      cell21.setCellValue("2022001");
      Cell cell22 = row2.createCell(1);
      cell22.setCellValue("张三");
      Cell cell23 = row2.createCell(2);
      String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
      cell23.setCellValue(time);


      // 生成表  07版本使用xlsx结尾
      FileOutputStream fos = new FileOutputStream(path + "学生信息统计表.xlsx");
      workbook.write(fos);
      fos.close();
      System.out.println("学生信息统计表.xlsx 已生成");
   }
}

03和07版本就是使用的对象不同，03是HSSFWorkbook，07是XSSFWorkbook，另外就是生成文件的后缀名不同，03是xls，07是xlsx

大文件

大文件写入HSSF

缺点：只能写入65536行，超出会抛出异常

优点：过程写入缓存，不操作磁盘，最后一次性写入磁盘，速度快

@Test
public void testWrite03BigData() throws Exception{
    long start = System.currentTimeMillis();
    Workbook workbook = new HSSFWorkbook();
    Sheet sheet = workbook.createSheet("学生成绩表");
    Row row1 = sheet.createRow(0);
    Cell cell11 = row1.createCell(0);
    cell11.setCellValue("学号");
    Cell cell12 = row1.createCell(1);
    cell12.setCellValue("姓名");
    for (int i = 1; i < 65535; i++) {
        Row row = sheet.createRow(i);
        for (int j = 0; j < 2; j++) {
            Cell cell = row.createCell(j);
            if(j==0){
                cell.setCellValue("2020"+i);
            }else{
                cell.setCellValue("张三"+j);
            }
        }
    }
    FileOutputStream fos = new FileOutputStream(path + "信息表.xls");
    workbook.write(fos);
    fos.close();
    long end = System.currentTimeMillis();

    System.out.println("总耗时"+(double)(end-start)/1000+"秒");
}
// 耗时1.054s

大文件写入XSSF

缺点：写数据速度非常慢，耗内存，会发生内存溢出如100w

优点：可以写较大的数据量如20w

@Test
public void testWrite07BigData() throws Exception{
    long start = System.currentTimeMillis();
    Workbook workbook = new XSSFWorkbook();
    Sheet sheet = workbook.createSheet("学生成绩表");
    Row row1 = sheet.createRow(0);
    Cell cell11 = row1.createCell(0);
    cell11.setCellValue("学号");
    Cell cell12 = row1.createCell(1);
    cell12.setCellValue("姓名");
    for (int i = 1; i < 1000000; i++) {
        Row row = sheet.createRow(i);
        for (int j = 0; j < 2; j++) {
            Cell cell = row.createCell(j);
            if(j==0){
                cell.setCellValue("2020"+i);
            }else{
                cell.setCellValue("张三"+j);
            }
        }
    }
    FileOutputStream fos = new FileOutputStream(path + "信息表.xlsx");
    workbook.write(fos);
    fos.close();
    long end = System.currentTimeMillis();

    System.out.println("总耗时"+(double)(end-start)/1000+"秒");
}
// 写入100w条数据  耗时47s
// 写入65536条数据 耗时4.269s

大文件写入SXSSF

优点：可以写非常大量的数据，如100w以上，写速度非常快，耗内存少

但是过程中会产生临时文件，需要清除，默认100条数据保存在内存中，超过这个数量最前面的数据被写入临时文件，如果想设定内存中数据条数使用 new SXSSFWorkbook(数量)

SXSSFWorkbook 对象不同
需要清除临时文件 ((SXSSFWorkbook)workbook).dispose();
文件后缀还是.xlsx

@Test
public void testWrite07BigDataQuick() throws Exception{
    long start = System.currentTimeMillis();
    Workbook workbook = new SXSSFWorkbook();
    Sheet sheet = workbook.createSheet("学生成绩表");
    Row row1 = sheet.createRow(0);
    Cell cell11 = row1.createCell(0);
    cell11.setCellValue("学号");
    Cell cell12 = row1.createCell(1);
    cell12.setCellValue("姓名");
    for (int i = 1; i < 1000000; i++) {
        Row row = sheet.createRow(i);
        for (int j = 0; j < 2; j++) {
            Cell cell = row.createCell(j);
            if(j==0){
                cell.setCellValue("2020"+i);
            }else{
                cell.setCellValue("张三"+j);
            }
        }
    }
    FileOutputStream fos = new FileOutputStream(path + "信息表.xlsx");
    workbook.write(fos);
    // 清除临时文件
    ((SXSSFWorkbook)workbook).dispose();
    fos.close();
    long end = System.currentTimeMillis();

    System.out.println("总耗时"+(double)(end-start)/1000+"秒");
}
// 写入100w条数据  耗时7.555s

1.2 POI读

public class ExcelRead {
    private String path = "D:\java代码\Excel";

    @Test
    public void testRead03() throws Exception{  // 03版
        // 获取文件流
        FileInputStream fis = new FileInputStream(path + "\POI学生信息统计表.xls");

        Workbook workbook = new HSSFWorkbook(fis);
        // 得到表
        Sheet sheet = workbook.getSheetAt(0);
        // 得到行
        Row row = sheet.getRow(0);
        // 得到列
        Cell cell = row.getCell(0);
        // 获取单元格内容
        // 注意获取的类型   getNumericCellValue();
        String cellValue = cell.getStringCellValue();
        System.out.println(cellValue);
    }

    @Test
    public void testRead07() throws Exception{  // 07版
        // 获取文件流
        FileInputStream fis = new FileInputStream(path + "\POI信息表.xlsx");

        Workbook workbook = new XSSFWorkbook(fis);
        // 得到表
        Sheet sheet = workbook.getSheetAt(0);
        // 得到行
        Row row = sheet.getRow(0);
        // 得到列
        Cell cell = row.getCell(0);
        // 获取单元格内容
        // 注意获取的类型   getNumericCellValue();
        String cellValue = cell.getStringCellValue();
        System.out.println(cellValue);
    }
}

读不同的数据类型

@Test
public void testReadDataType() throws Exception{
    // 获取文件流
    FileInputStream fis = new FileInputStream(path + "\订单表.xlsx");

    Workbook workbook = new XSSFWorkbook(fis);
    Sheet sheet = workbook.getSheetAt(0);
    // 获取标题内容
    Row rowTitle = sheet.getRow(0);
    if(rowTitle!=null){
        int numberOfCells = rowTitle.getPhysicalNumberOfCells();
        for (int i = 0; i < numberOfCells; i++) {
            Cell cell = rowTitle.getCell(i);
            if(cell!=null){
                CellType cellType = cell.getCellType();
                String cellValue = cell.getStringCellValue();
                System.out.print(cellValue+" | ");
            }
        }
        System.out.println();
    }
    // 获取表中内容
    int numberOfRows = sheet.getPhysicalNumberOfRows();
    for (int i = 1; i < numberOfRows; i++) {
        Row rowData = sheet.getRow(i);
        if(rowData!=null){
            int numberOfCells = rowData.getPhysicalNumberOfCells();
            for (int j = 0; j < numberOfCells; j++) {
                Cell cell = rowData.getCell(j);
                // 匹配列的数据类型
                if(cell!=null){
                    CellType cellType = cell.getCellType();
                    String cellValue = "";
                    switch (cellType){
                        case STRING:  // 字符串
                            System.out.print("[String]");
                            cellValue = cell.getStringCellValue();
                            break;
                        case BOOLEAN: // 布尔类型
                            System.out.print("[boolean]");
                            cellValue = String.valueOf(cell.getBooleanCellValue());
                        case BLANK:   // 空
                            break;
                        case NUMERIC: // 数值
                            if (HSSFDateUtil.isCellDateFormatted(cell)){
                                // 日期
                                System.out.print("[date]");
                                Date dateCellValue = cell.getDateCellValue();
                                cellValue = new DateTime(dateCellValue).toString("yyyy-MM-dd");
                            }else {
                                // 数字
                                System.out.print("[number]");
                                cell.setCellType(CellType.STRING);
                                cellValue = cell.toString();
                            }
                            break;
                        case ERROR: // 数据类型错误
                            break;
                    }
                    System.out.print(cellValue+" | ");
                }
            }
            System.out.println();
        }
    }
    fis.close();
}

1.3 计算公式

@Test
public void testFormula() throws  Exception{
    FileInputStream fis = new FileInputStream(path + "\公式.xlsx");
    Workbook workbook = new XSSFWorkbook(fis);
    Sheet sheet = workbook.getSheetAt(0);
    Row row = sheet.getRow(4);
    Cell cell = row.getCell(0);

    // 拿到计算公式
    FormulaEvaluator formulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
    // 拿到单元格内容
    CellType cellType = cell.getCellType();
    switch (cellType){
        case FORMULA:
            String cellFormula = cell.getCellFormula(); // 得到公式
            System.out.println(cellFormula); 

            // 计算
            CellValue evaluate = formulaEvaluator.evaluate(cell);
            System.out.println(evaluate.formatAsString());
            break;
    }
    fis.close();
}

// SUM(A2:A4)
// 1500.0

2. EasyExcel

阿里巴巴开源的一个Excel处理框架，简单实用，节省内存

EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel。

github地址：https://github.com/alibaba/easyexcel

官网文档：https://easyexcel.opensource.alibaba.com/

导入依赖


    com.alibaba
    easyexcel
    2.2.0-beta2

实体类

@Data
public class DemoData {
    @ExcelProperty("字符串标题")
    private String string;
    @ExcelProperty("日期标题")
    private Date date;
    @ExcelProperty("数字标题")
    private Double doubleData;
    
    @ExcelIgnore
    private String ignore;
}

public class test {

    private String path = "D:\java代码\Excel\";

    private List data() {
        List 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() {
        String fileName =  path+"easyExcel.xlsx";
        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data());
    }
}

package com.hua.easy;

import com.hua.pojo.DemoData;

import java.util.List;


public class DemoDAO {

    public void save(List list) {
        // 如果是mybatis,尽量别直接调用多次insert,自己写一个mapper里面新增一个方法batchInsert,所有数据一次性插入
    }
}

package com.hua.easy;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import com.hua.pojo.DemoData;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

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

// 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
public class DemoDataListener extends AnalysisEventListener {
    private static final Logger LOGGER = LoggerFactory.getLogger(DemoDataListener.class);
    
    private static final int BATCH_COUNT = 5;
    List list = new ArrayList();
    
    private DemoDAO demoDAO;

    public DemoDataListener() {
        // 这里是demo，所以随便new一个。实际使用如果到了spring,请使用下面的有参构造函数
        demoDAO = new DemoDAO();
    }

    
    public DemoDataListener(DemoDAO demoDAO) {
        this.demoDAO = demoDAO;
    }

    
    @Override
    public void invoke(DemoData data, AnalysisContext context) {
        System.out.println(JSON.toJSONString(data));
        LOGGER.info("解析到一条数据:{}", JSON.toJSONString(data));
        list.add(data);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (list.size() >= BATCH_COUNT) {
            saveData(); // 持久化
            // 存储完成清理 list
            list.clear();
        }
    }

    
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();
        LOGGER.info("所有数据解析完成！");
    }

    
    private void saveData() {
        LOGGER.info("{}条数据，开始存储数据库！", list.size());
        demoDAO.save(list);
        LOGGER.info("存储数据库成功！");
    }
}

@Test
public void simpleRead() {
    String fileName = path+"easyExcel.xlsx";
    EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead();
}

步骤：

根据固定类进行写入
根据监听类设置规则进行读

Java操作Excel

Java相关栏目本月热门文章