使用POI实现excel表格导入导出

使用POI实现excel表格导入导出

Scroll Down

使用POI实现excel表格导入导出

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

1、数据导入:减轻录入工作量
2、数据导出:统计信息归档3、数据传输:异构系统之间数据传输为了非工作人员和开发者之间数据交互!

二、POI简介(Apache POI)

什么是POI

  • Apache POI是Apache软件基金会的开放源码函式库,POI提供API给Java程序对Microsoft Office格式档案读和写的功能。
  • HSSFWorkBook - 提供读写Microsoft Excel格式档案的功能。(.xls)-03
  • XSSFWorkBook - 提供读写Microsoft Excel OOXML格式档案的功能。(.xlsx)-07
  • HWPF - 提供读写Microsoft Word格式档案的功能。
  • HSLF - 提供读写Microsoft PowerPoint格式档案的功能。
  • HDGF - 提供读写Microsoft Visio格式档案的功能。

官网

三、使用范例

(1)创建一个普通的maven项目

名字叫:guan_poi

(2)在pom.xml中导入依赖

<dependencies>
        <!--xls-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.9</version>
        </dependency>
        <!--xlsx-->
        <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.10.1</version>
        </dependency>
        <!--test-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
    </dependencies>

(3)新建WriteTest类

/**
     * 写入excl表格03版本
     *
     */
    @Test
    public void Write03() throws Exception {

        //1、创建工作表

        Workbook workbook = new HSSFWorkbook();
        //2、在工作表中创建sheet
        Sheet sheet = workbook.createSheet("会员管理");

        //3、在sheet中创建行数
        //索引从0开始
        Row row = sheet.createRow(0);
        //4、在行数中创建列数
        Cell cell1 = row.createCell(0);
        //5、在对应的单元格中设置数据
        cell1.setCellValue("人数");

        Cell cell2 = row.createCell(1);
        cell2.setCellValue(12345);

        //6、写入磁盘,文件的位置
        FileOutputStream outputStream = new FileOutputStream("D:/guan.xls");
        workbook.write(outputStream);
        //7、关闭流
        outputStream.close();

    }

    /**
     * 写入excl表格07版本
     *
     */
    @Test
    public void Write07() throws Exception {

        //1、创建工作表

        Workbook workbook = new XSSFWorkbook();
        //2、在工作表中创建sheet
        Sheet sheet = workbook.createSheet("会员管理");

        //3、在sheet中创建行数
        //索引从0开始
        Row row = sheet.createRow(0);
        //4、在行数中创建列数
        Cell cell1 = row.createCell(0);
        //5、在对应的单元格中设置数据
        cell1.setCellValue("人数");

        Cell cell2 = row.createCell(1);
        cell2.setCellValue(12345);

        //6、写入磁盘,文件的位置
        FileOutputStream outputStream = new FileOutputStream("D:/guan.xlsx");
        workbook.write(outputStream);
        //7、关闭流
        outputStream.close();

    }

四、大数据量写入时

(1)使用HSSF

  • 缺点:最多只能处理65536行,否则会抛出异常java.lang.IllegalArgumentException: Invalid row number (65536) outside allowable range (0..65535)
  • 优点:过程中写入缓存,不操作磁盘,最后一次性写入磁盘,速度快

(2)使用XSSF

  • 缺点:写数据时速度非常慢,非常耗内存,也会发生内存溢出,如100万条
  • 优点:可以写较大的数据量,如20万条
/**
     *写入大数据量
     *
     * 考虑性能
     * 03版
     */
    @Test
    public void WriteData03() throws Exception {

        Long begin = System.currentTimeMillis();

        //1、创建工作表
        Workbook workbook = new HSSFWorkbook();
        //2、在工作表中创建sheet
        Sheet sheet = workbook.createSheet("会员管理");

        //3、在sheet中创建行数
        //索引从0开始
        for (int i = 0; i < 65536; i++) {
            Row row = sheet.createRow(i);
            //4、在行数中创建列数
            Cell cell1 = row.createCell(0);
            //5、在对应的单元格中设置数据
            cell1.setCellValue("人数"+i);
        }


        //6、写入磁盘,文件的位置
        FileOutputStream outputStream = new FileOutputStream("D:/guan-03.xls");
        workbook.write(outputStream);

        Long end = System.currentTimeMillis();
        System.err.println((double) (end-begin)/1000);

        //7、关闭流
        outputStream.close();
    }

    /**
     *写入大数据量
     *
     * 考虑性能
     * 07版
     */
    @Test
    public void WriteData07() throws Exception {

        Long begin = System.currentTimeMillis();

        //1、创建工作表
        Workbook workbook = new XSSFWorkbook();
        //2、在工作表中创建sheet
        Sheet sheet = workbook.createSheet("会员管理");

        //3、在sheet中创建行数
        //索引从0开始
        //07版本可以大于65536条数据
        for (int i = 0; i < 65537; i++) {
            Row row = sheet.createRow(i);
            //4、在行数中创建列数
            Cell cell1 = row.createCell(0);
            //5、在对应的单元格中设置数据
            cell1.setCellValue("人数"+i);
        }


        //6、写入磁盘,文件的位置
        FileOutputStream outputStream = new FileOutputStream("D:/guan-07.xlsx");
        workbook.write(outputStream);

        Long end = System.currentTimeMillis();
        System.err.println((double) (end-begin)/1000);

        //7、关闭流
        outputStream.close();
    }

(3)使用SXSSF

  • 优点:可以写非常大的数据量,如100万条甚至更多条,写数据速度快,占用更少的内存
  • 问题:过程中会产生临时文件,需要清理临时文件(C:\Users\helen\AppData\Local\Temp)默认由100条记录被保存在内存中,如果查过这数量,则最前面的数据被写入临时文件如果想自定义内存中数据的数量,可以使用new SXSSFWorkbook(数量)。
  • 实际上SXSSF使用了内存来临时存储输出,当达到一定的数据量时一起写入磁盘,这种延迟写的策略大大减少了多余的IO操作,这也是SXSSF能够在写入大数据量的同时保证速度的原因。
@Test
    public void SXWriteData07() throws Exception {

        Long begin = System.currentTimeMillis();

        //1、创建工作表
        Workbook workbook = new SXSSFWorkbook();
        //2、在工作表中创建sheet
        Sheet sheet = workbook.createSheet("会员管理");

        //3、在sheet中创建行数
        //索引从0开始
        //07版本可以大于65536条数据
        for (int i = 0; i < 100000; i++) {
            Row row = sheet.createRow(i);
            //4、在行数中创建列数
            Cell cell1 = row.createCell(0);
            //5、在对应的单元格中设置数据
            cell1.setCellValue("人数"+i);
        }


        //6、写入磁盘,文件的位置
        FileOutputStream outputStream = new FileOutputStream("D:/guanSXSSF-07.xlsx");
        workbook.write(outputStream);

        Long end = System.currentTimeMillis();
        System.err.println((double) (end-begin)/1000);

        //7、关闭流
        outputStream.close();

        //8、清理内存
        ((SXSSFWorkbook)workbook).dispose();
    }

五、导入EXCEL

新建ReadTest类

/**
 * 读取EXECL表格内容
 * @throws IOException
 */
@Test
public void Read03() throws IOException {

    //1、获取文件路径,获取流
    FileInputStream inputStream = new FileInputStream("D:/guan.xls");
    //2、根据流创建Workbook

    Workbook workbook = new HSSFWorkbook(inputStream);
    //3、获取工作表中的sheet
    Sheet sheet = workbook.getSheetAt(0);
    //4、根据sheet获取一行
    Row row = sheet.getRow(0);
    //5、获取列
    Cell cell = row.getCell(0);
    //6、根据列获取列中的数据
    String value = cell.getStringCellValue();
    System.err.println(value);
    //7、关闭流
    inputStream.close();
}


/**
 * 读取EXECL表格内容
 * @throws IOException
 */
@Test
public void Read07() throws IOException {

    //1、获取文件路径,获取流
    FileInputStream inputStream = new FileInputStream("D:/guan.xlsx");
    //2、根据流创建Workbook

    Workbook workbook = new XSSFWorkbook(inputStream);
    //3、获取工作表中的sheet
    Sheet sheet = workbook.getSheetAt(0);
    //4、根据sheet获取一行
    Row row = sheet.getRow(0);
    //5、获取列
    Cell cell1 = row.getCell(0);
    Cell cell2 = row.getCell(1);
    //6、根据列获取列中的数据
    double value2 = cell2.getNumericCellValue();
    String value1 = cell1.getStringCellValue();
    System.err.println(value1+"--------"+value2);
    //7、关闭流
    inputStream.close();
}