使用poi操作excel详解

  • 1、POI工具介绍
  • 2、POI可操作的文件类型
  • 3、POI所需依赖
  • 4、xls和xlsx的区别
  • 5、POI Excel 写 03(xls)和07(xlsx)版本方式
  • 6、HSSF和XSSF写大文件的区别
  • 6.1、使用HSSF写大文件
  • 6.2、使用XSSF写大文件
  • 6.3、使用SXSS写大文件


1、POI工具介绍

1.1、POI 是用Java编写的免费开源的跨平台的 Java API，Apache POI提供API给Java程式对Microsoft Office格式档案读和写的功能。
1.2、主要是运用其中读取和输出excel的功能。
1.3、POI官网地址：
https://poi.apache.org/components/index.html

2、POI可操作的文件类型

TemplateExportParams 最大行数_excel

3、POI所需依赖

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

4、xls和xlsx的区别

①xls是excel03版本 xlsx是excel07版本
②最大的区别是行列数不同
xls最大支持65536行、256列
xlsx最大支持1048576行、16384列
③
poi 操作xls
poi-ooml操作xlsx

5、POI Excel 写 03(xls)和07(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.text.SimpleDateFormat;
import java.util.Date;

/**
 * @author yw
 * @since 2022/11/20
 */
public class ExcelWrite {

    public static final String DEFAULT_DATE_FORMAT = "yyyy-MM-dd HH:mm:ss";

    String PATH = "D:\\excel\\";

    /**
     * 写工作簿 03版本
     */
    @Test
    public void Write03() throws Exception {
        //1.创建一个工作簿
        Workbook workbook = new HSSFWorkbook();
        //2.创建 一个工作表
        Sheet sheet = workbook.createSheet("工人表");
        //3.创建一行
        Row row1 = sheet.createRow(0);
        //4.创建一个单元格
        //(1,1)
        Cell cell1 = row1.createCell(0);
        cell1.setCellValue("工号");
        //(1,2)
        Cell cell2 = row1.createCell(1);
        cell2.setCellValue("日期");

        //创建第二行
        Row row2 = sheet.createRow(1);
        //(2,1)
        Cell cell21 = row2.createCell(0);
        cell21.setCellValue(001);
        //(2,2)
        Cell cell22 = row2.createCell(1);
        SimpleDateFormat sdf = new SimpleDateFormat(DEFAULT_DATE_FORMAT);
        String datetime = sdf.format(new Date());
        cell22.setCellValue(datetime);

        //生成一张表(IO流)，03版本就是使用xls结尾
        FileOutputStream fos = new FileOutputStream(PATH + "工人表03.xls");
        //输出
        workbook.write(fos);
        //关闭流
        fos.close();
        System.out.println("文件生成完毕");
    }

    /**
     * 写工作簿 07版本
     */
    @Test
    public void Write07() throws Exception {
        //1.创建一个工作簿
        Workbook workbook = new XSSFWorkbook();
        //2.创建 一个工作表
        Sheet sheet = workbook.createSheet("工人表");
        //3.创建一行
        Row row1 = sheet.createRow(0);
        //4.创建一个单元格
        //(1,1)
        Cell cell1 = row1.createCell(0);
        cell1.setCellValue("工号");
        //(1,2)
        Cell cell2 = row1.createCell(1);
        cell2.setCellValue("日期");

        //创建第二行
        Row row2 = sheet.createRow(1);
        //(2,1)
        Cell cell21 = row2.createCell(0);
        cell21.setCellValue(001);
        //(2,2)
        Cell cell22 = row2.createCell(1);
        SimpleDateFormat sdf = new SimpleDateFormat(DEFAULT_DATE_FORMAT);
        String datetime = sdf.format(new Date());
        cell22.setCellValue(datetime);

        //生成一张表(IO流)，03版本就是使用xlsx结尾
        FileOutputStream fos = new FileOutputStream(PATH + "工人表07.xlsx");
        //输出
        workbook.write(fos);
        //关闭流
        fos.close();
        System.out.println("文件生成完毕");
    }
}

6、HSSF和XSSF写大文件的区别

6.1、使用HSSF写大文件

@Test
    public void Write03BigData() throws Exception{
        //时间
        long begin = System.currentTimeMillis();
        //1.创建一个工作簿
        Workbook workbook = new HSSFWorkbook();
        //2.创建一个表
        Sheet sheet = workbook.createSheet("sheet1");
        //写入数据
        for (int rowNum = 0;rowNum<65536;rowNum++){
            //3.创建行
            Row row = sheet.createRow(rowNum);
            for (int CellNum = 0;CellNum<10;CellNum++){
                Cell cell = row.createCell(CellNum);
                cell.setCellValue(CellNum);
            }
        }
        System.out.println("over");
        //获取io流
        FileOutputStream fos = new FileOutputStream(PATH+"Write03BigData.xlsx");
        //生成一张表
        workbook.write(fos);
        fos.close();
        long end = System.currentTimeMillis();
        System.out.println("耗时："+(end-begin));
    }

TemplateExportParams 最大行数_excel_02

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

缺点：但是只能写入65535条数据，超过就会报异常

TemplateExportParams 最大行数_microsoft_03

6.2、使用XSSF写大文件

@Test
    public void Write07BigData() throws Exception{
        //时间
        long begin = System.currentTimeMillis();
        //1.创建一个工作簿
        Workbook workbook = new XSSFWorkbook();
        //2.创建一个表
        Sheet sheet = workbook.createSheet("sheet1");
        //写入数据
        for (int rowNum = 0;rowNum<1000000;rowNum++){
            //3.创建行
            Row row = sheet.createRow(rowNum);
            for (int CellNum = 0;CellNum<10;CellNum++){
                Cell cell = row.createCell(CellNum);
                cell.setCellValue(CellNum);
            }
        }
        System.out.println("over");
        //获取io流
        FileOutputStream fos = new FileOutputStream(PATH+"Write07BigData.xlsx");
        //生成一张表
        workbook.write(fos);
        fos.close();
        long end = System.currentTimeMillis();
        System.out.println("耗时："+(end-begin));
    }

TemplateExportParams 最大行数_System_04

优点：可以写较大数据量（超过655135条），如10万条
缺点：写数据时，速度非常慢，非常消耗内存，也会容易发生内存溢出，如100万条的时候。

6.3、使用SXSS写大文件

@Test
    public void Write07BigDataS() throws Exception{
        //时间
        long begin = System.currentTimeMillis();
        //1.创建一个工作簿
        Workbook workbook = new SXSSFWorkbook(200);
        //2.创建一个表
        Sheet sheet = workbook.createSheet("sheet1");
        //写入数据
        for (int rowNum = 0;rowNum<1000000;rowNum++){
            //3.创建行
            Row row = sheet.createRow(rowNum);
            for (int CellNum = 0;CellNum<10;CellNum++){
                Cell cell = row.createCell(CellNum);
                cell.setCellValue(CellNum);
            }
        }
        System.out.println("over");
        //获取io流
        FileOutputStream fos = new FileOutputStream(PATH+"Write07BigDataS.xlsx");
        //生成一张表
        workbook.write(fos);
        fos.close();
        //清除临时文件
        ((SXSSFWorkbook) workbook).dispose();
        long end = System.currentTimeMillis();
        System.out.println("耗时："+(end-begin));
    }

TemplateExportParams 最大行数_microsoft_05


优点：可以写超大数据量的，比如100万条，写数据速度更快，占用更少内存

注意：

1、过程中会产生临时文件，结束时需要注意清理

2、默认100条记录会保存在内存中，如果超过这个数据，则最前面的数据会被写入磁盘中

3、如果想自定义内存中的数量，则可以new SXSSFWorkbook(自定义数量)

SXSSFWorkbook-来至官方的解释︰实现"BigGridDemo"策略的流式XSSFWorkbook版本。这允许写入非常大的文件而不会耗尽内存，因为任何时候只有可配置的行部分被保存在内存中。