poi操作excel

一、Excel导入导出的应用场景

1、数据导入：减轻录入工作量

2、数据导出：统计信息归档

3、数据传输：异构系统之间数据传输

二、POI简介（Apache POI）

1、什么是POI

Apache POI是Apache软件基金会的开放源码函式库，POI提供API给Java程序对Microsoft Office格式档案读和写的功能。

• HSSF － 提供读写Microsoft Excel格式档案的功能。（.xls）

• XSSF － 提供读写Microsoft Excel OOXML格式档案的功能。（.xlsx）

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

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

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

三、创建项目

1、创建一个普通的maven项目

项目名：excel-poi

2、pom中引入xml相关依赖

<dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.9</version>
    </dependency>
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>3.9</version>
    </dependency>
    <!--日期格式化工具-->
    <dependency>
        <groupId>joda-time</groupId>
        <artifactId>joda-time</artifactId>
        <version>2.5</version>
    </dependency>
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.12</version>
    </dependency>

四.xls写-03和07的区别

1、03

//测试poi操作excel
public class PoiDemoTest  {

    //1.向03版本excel写入操作
    @Test
    public void testWirte03() throws Exception{
        //1.创建workbook
        Workbook workbook = new HSSFWorkbook();
        //2.根据workbook创建sheet
        Sheet sheet = workbook.createSheet("会员列表");
        //3.根据sheet创建row
        Row row = sheet.createRow(0);
        //4.根据行创建cell
        Cell cell = row.createCell(0);
        //5.向cell设置值
        cell.setCellValue("lucy");
        //6.使用输出流写入到文件中
        OutputStream out = new FileOutputStream("D:\\project_liu\\prac\\111\\01.xls");
        //把workbook内容通过输出流写入到文件中
        workbook.write(out);
        //关闭流
        out.close();
    }
}

结果:

2 、07

@Test
    public void testWirte07() throws Exception{
        //1.创建workbook
        Workbook workbook = new XSSFWorkbook();
        //2.根据workbook创建sheet
        Sheet sheet = workbook.createSheet("会员列表");
        //3.根据sheet创建row
        Row row = sheet.createRow(0);
        //4.根据行创建cell
        Cell cell = row.createCell(0);
        //5.向cell设置值
        cell.setCellValue("lucy07");
        //6.使用输出流写入到文件中
        OutputStream out = new FileOutputStream("D:\\project_liu\\prac\\111\\0107.xlsx");
        //把workbook内容通过输出流写入到文件中
        workbook.write(out);
        //关闭流
        out.close();
    }

结果:

五.xls读-03和07

1、读取03版本

@Test
    public void readExcel003 () throws Exception{
        //1.获取读取文件的输入流
        InputStream in = new FileInputStream("D:\\project_liu\\prac\\111\\01.xls");
        //2.创建workbook,需要把输入流传递进去
        Workbook workbook = new HSSFWorkbook(in);
        //3.根据workbook获取sheet
        Sheet sheet = workbook.getSheetAt(0);
        //4.根据sheet获取行
        Row row = sheet.getRow(0);
        //5.根据行获取cell
        Cell cell = row.getCell(0);
        //6.获取cell里面的值
        String value = cell.getStringCellValue();
        System.out.println(value);
        in.close();
    }

结果:

2、读取07版本

@Test
    public void readExcel007 () throws Exception{
        //1.获取读取文件的输入流
        InputStream in = new FileInputStream("D:\\project_liu\\prac\\111\\0107.xlsx");
        //2.创建workbook,需要把输入流传递进去
        Workbook workbook = new XSSFWorkbook(in);
        //3.根据workbook获取sheet
        Sheet sheet = workbook.getSheetAt(0);
        //4.根据sheet获取行
        Row row = sheet.getRow(0);
        //5.根据行获取cell
        Cell cell = row.getCell(0);
        //6.获取cell里面的值
        String value = cell.getStringCellValue();
        System.out.println(value);
        in.close();
    }
}

结果:

六、大文件写-HSSF、XSSF和SXSSF

1、使用HSSF

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

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

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

 //操作大数据量文件
    @Test
    public void testBigData03 () throws  Exception{
        //创建workbook
        Workbook workbook = new HSSFWorkbook();
        //创建sheet
        Sheet sheet = workbook.createSheet();
        //循环多次
        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(j);
            }
        }
        OutputStream out = new FileOutputStream("D:\\project_liu\\prac\\111\\big01.xls");
        workbook.write(out);
    }

2、使用XSSF

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

优点：可以写较大的数据量，如20万条

@Test
public void testWrite07BigData() throws IOException {

    //记录开始时间

    long begin = System.currentTimeMillis();

    //创建一个XSSFWorkbook

    Workbook workbook = new XSSFWorkbook();

    FileOutputStream out = new FileOutputStream("d:/excel-poi/test-write07-bigdata.xlsx");


}

3、使用SXSSF

优点：可以写非常大的数据量，如100万条甚至更多条，写数据速度快，占用更少的内存

注意：

过程中会产生临时文件，需要清理临时文件(C:\Users\helen\AppData\Local\Temp)

默认由100条记录被保存在内存中，如果查过这数量，则最前面的数据被写入临时文件

如果想自定义内存中数据的数量，可以使用new SXSSFWorkbook(数量)

@Test
public void testWrite07BigDataFast() throws IOException {

    //记录开始时间

    long begin = System.currentTimeMillis();

    //创建一个SXSSFWorkbook

    Workbook workbook = new SXSSFWorkbook();

   ......

    FileOutputStream out = new FileOutputStream("d:/excel-poi/test-write07-bigdata-fast.xlsx");

    workbook.write(out);

    // 操作结束，关闭文件

    out.close();

    //清除临时文件

    ((SXSSFWorkbook)workbook).dispose();

    //记录结束时间

    long end = System.currentTimeMillis();

    System.out.println((double)(end - begin)/1000);

}

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

请注意，仍然可能会消耗大量内存，这些内存基于您正在使用的功能，例如合并区域，注释…仍然只存储在内存中，因此如果广泛使用，可能需要大量内存。