Java使用poi實現(xiàn)excel的導入操作指南

更新時間：2023年06月28日 11:23:53 作者：小就是我

使用Apache Poi是一種流行且廣泛使用的方式，可以幫助開發(fā)人員直接從Java代碼中讀取、寫入和處理Excel文件，因此在這篇文章我們將著重介紹如何實現(xiàn)excel的導入，感興趣的朋友可以跟著小編一起來學習

創(chuàng)建項目測試

1 創(chuàng)建springBoot項目

2 pom導入相關(guān)依賴

 <!--導入依賴jar包-->
 <!--xls(03)-->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.9</version>
</dependency>
 <!--xlsx(07)-->
<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>
 <!--單元測試-->
<dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.12</version>
</dependency>

3 本地創(chuàng)建兩個excel 分別是03版和07版

首先03版,它最多有65536行

但是07版的,是沒有限制的

他們對應的后綴也是不一樣的,03版本的xls,07版本的是xslx,這意味著操作它們的工具類也不相同.03版本用的是poi,07版用的則是poi-ooxml

4 Java的宗旨就是萬物皆對象,我們也要把excel當成我們的一個對象去處理

1 工作薄

首先我們打開的excel就是一個大對象,也叫工作簿,它包括以下內(nèi)容

2 工作表

每一個sheet也是我們excel對象的屬性,也叫工作表,我們肯定是現(xiàn)有工作簿才會有工作表,而且會默認自帶sheet,也可以根據(jù)我們的需要自行添加sheet工作表

3 行

excel中有很多行,每一行也是我們excel對象的屬性(橫的叫行,豎的叫列)

4 列

excel中有很多列,每一列也是我們excel對象的屬性(橫的叫行,豎的叫列)

5 單元格

一行一列有很多個單元格,每一個單元格也是我們excel對象的屬性

創(chuàng)建我們的測試類,創(chuàng)建Workbook對象,按著ctrl點進去發(fā)現(xiàn)它是一個接口

點擊箭頭可以看到它的三個實現(xiàn)類

定義工作簿,工作表,行列,單元格,和我們手動創(chuàng)建excel是一樣的操作,只不過是用代碼來實現(xiàn)

03版本excel IO操作寫的全部代碼如下

package com.wyh.Test;
import org.apache.poi.hssf.usermodel.HSSFSheet;
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.joda.time.DateTime;
import org.junit.Test;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.util.Date;
 /**
 *  @program:  JavaExecl
 *  @description:  寫excel 03版本
 *  @author:  魏一鶴
 *  @createDate:  2021-12-12 10:42
 **/
public class ExcelWrite03 {
    //全局路徑,供我們操作使用方便
  static String path= "D:\Tools\JavaWorkSpace\JavaExecl\" ;
    @Test
    public  void ExcelWrite03() throws Exception {
        //1創(chuàng)建一個工作簿
 Workbook workbook=new HSSFWorkbook();
        //2創(chuàng)建一個工作表 工作簿里面包含工作表,所以創(chuàng)建工作表要通過工作簿創(chuàng)建
 //默認的工作表是沒有名字的,需要我們手動賦值,和我們在excel中更改sheet工作表的名稱是一樣的 操作
 Sheet sheet=workbook.createSheet( "用戶表" );
        //3創(chuàng)建行 行也是在我們的表中存在的,所以需要用到表來創(chuàng)建
 //默認從0開始 也就是第一行
 Row row1 = sheet.createRow(0);
        //創(chuàng)建單元格 第一行的第一個數(shù)據(jù) 用坐標表示為(1,1)
 Cell cell11 = row1.createCell(0);
        //創(chuàng)建單元格 第一行的第二個數(shù)據(jù) 用坐標表示為(1,2)
 Cell cell12 = row1.createCell(1);
        //給單元格賦值
 cell11.setCellValue( "姓名" );
        cell12.setCellValue( "魏一鶴" );
        //創(chuàng)建第二行
 Row row2=sheet.createRow(1);
        //創(chuàng)建第二行的第一列
 Cell cell21 = row2.createCell(0);
        Cell cell22 = row2.createCell(1);
        //給單元格賦值（2.1）
 cell21.setCellValue( "出生日期" );
        //創(chuàng)建時間并且格式化
 String s = new DateTime().toString( "yyyy-MM-dd HH:mm:ss" );
        //給單元格賦值（2.2）
 cell22.setCellValue(s);
        //生成一張表 其實就是IO流操作 03版本就是使用xls文件結(jié)尾
 FileOutputStream fileOutputStream = new FileOutputStream(path + "用戶測試03.xls" );
        //輸出工作簿
 workbook.write(fileOutputStream);
        //關(guān)閉流
 fileOutputStream.close();
        System.out.println( "用戶測試03.xls生成完畢" );
    }
}

07版本excel IO操作寫的全部代碼如下

package com.wyh.Test;
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.FileOutputStream;
 /**
 *  @program:  JavaExecl
 *  @description:  07版本excel寫操作
 *  @author:  魏一鶴
 *  @createDate:  2021-12-12 23:58
 **/
public class ExcelWrite07 {
    //全局路徑,供我們操作使用方便
  static String path= "D:\Tools\JavaWorkSpace\JavaExecl\" ;
    @Test
    public  void ExcelWrite07() throws Exception {
        //07版和03最大的差別就是使用的工具不一樣 03是HSSF 07是XSSF
 //其他代碼無需改動
 //1創(chuàng)建一個工作簿
 Workbook workbook=new XSSFWorkbook();
        //2創(chuàng)建一個工作表 工作簿里面包含工作表,所以創(chuàng)建工作表要通過工作簿創(chuàng)建
 //默認的工作表是沒有名字的,需要我們手動賦值,和我們在excel中更改sheet工作表的名稱是一樣的 操作
 Sheet sheet=workbook.createSheet( "用戶表" );
        //3創(chuàng)建行 行也是在我們的表中存在的,所以需要用到表來創(chuàng)建
 //默認從0開始 也就是第一行
 Row row1 = sheet.createRow(0);
        //創(chuàng)建單元格 第一行的第一個數(shù)據(jù) 用坐標表示為(1,1)
 Cell cell11 = row1.createCell(0);
        //創(chuàng)建單元格 第一行的第二個數(shù)據(jù) 用坐標表示為(1,2)
 Cell cell12 = row1.createCell(1);
        //給單元格賦值
 cell11.setCellValue( "今日學習" );
        cell12.setCellValue( "api和easyExcl導出導入excel" );
        //創(chuàng)建第二行
 Row row2=sheet.createRow(1);
        //創(chuàng)建第二行的第一列
 Cell cell21 = row2.createCell(0);
        Cell cell22 = row2.createCell(1);
        //給單元格賦值（2.1）
 cell21.setCellValue( "學習日期" );
        //創(chuàng)建時間并且格式化
 String s = new DateTime().toString( "yyyy-MM-dd HH:mm:ss" );
        //給單元格賦值（2.2）
 cell22.setCellValue(s);
        //生成一張表 其實就是IO流操作 07版本就是使用xlsx文件結(jié)尾
 FileOutputStream fileOutputStream = new FileOutputStream(path + "用戶測試07.xlsx" );
        //輸出工作簿
 workbook.write(fileOutputStream);
        //關(guān)閉流
 fileOutputStream.close();
        System.out.println( "用戶測試07.xls生成完畢" );
    }
}

運行后發(fā)現(xiàn),會在項目本地生成我們定義的excel,打開查看

03版和07版的區(qū)別如下：

1 03版本有最大長度現(xiàn)在 07版本沒有

2 03版本后綴xls 07版本后綴xlsx

3 03版本使用的工具是HSSF,07版本使用的是XSSF

5 大數(shù)據(jù)繞導入導出(批量)

真實開發(fā)中,大多數(shù)就是大數(shù)據(jù)批量導入或者導出excel

大文件寫HSSF

缺點:最多只能處理65536行,否則會報內(nèi)存溢出異常

優(yōu)點:過程中寫入緩存,不操作磁盤,最后一次性寫入磁盤,速度快

大文件寫XSSF

缺點:寫數(shù)據(jù)時速度非常慢,非常消耗內(nèi)存,也會發(fā)生內(nèi)存溢出,比如100萬條

優(yōu)點:可以寫較大的數(shù)據(jù)量,比如20萬條

03版本HSSF循環(huán)導入65536行數(shù)據(jù)(03版本最大行就是65536)

03版本HSSF循環(huán)插入65536條

package com.wyh.Test;
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.junit.Test;
import java.io.FileOutputStream;
 /**
 *  @program:  JavaExecl
 *  @description:  大數(shù)據(jù)量寫03版本
 *  @author:  魏一鶴
 *  @createDate:  2021-12-14 23:31
 **/
public class BigDateExcelWrite03 {
    //全局路徑,供我們操作使用方便
  static String path = "D:\Tools\JavaWorkSpace\JavaExecl\" ;
    @Test
    public void BigDateExcelWrite03() throws Exception {
        //開始時間 用于計算時間差
  long beginTime = System.currentTimeMillis();
        //創(chuàng)建工作簿  03版本使用HSSF
 Workbook workbook = new HSSFWorkbook();
        //創(chuàng)建工作表 這里就不給它命令了 按照默認的來
 Sheet sheet = workbook.createSheet();
        //寫入數(shù)據(jù) 循環(huán)插入65536行數(shù)據(jù),03版的HSSF最多只能插入65536行
  for (int rowNum = 0; rowNum < 65536; rowNum++) {
            //循環(huán)創(chuàng)建行
 Row row = sheet.createRow(rowNum);
            for(int cellNum=0;cellNum<10;cellNum++){
                //循環(huán)插入列
 Cell cell = row.createCell(cellNum);
                //循環(huán)設(shè)置值
 cell.setCellValue(cellNum);
            }
        }
        System.out.println( "生成excel表完畢" );
        //03版本的后綴是xls
 //開啟文件流
 FileOutputStream fileOutputStream = new FileOutputStream(path + "BigDateExcelWrite03.xlsx" );
        //開始寫excel
 workbook.write(fileOutputStream);
        //關(guān)閉流
 fileOutputStream.close();
        //結(jié)束時間
  long endTime = System.currentTimeMillis();
        //輸出花費的時間
 System.out.println( "花費的時間:" +(double)(endTime - beginTime)/1000);
    }
}

運行發(fā)現(xiàn)excel已經(jīng)創(chuàng)建成功,速度也非常的快

打開查看

已知03版本xls最多存65536行,那么如果我們循環(huán)插入65537行會怎么樣呢? 保留源代碼,循環(huán)最大值設(shè)置為65537

再次運行發(fā)現(xiàn)會報錯

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

07版本XSSF循環(huán)插入65536條

把后綴改為xlsx,把HSSF緩存XSSF即可

package com.wyh.Test;
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;
 /**
 *  @program:  JavaExecl
 *  @description:  大數(shù)據(jù)量寫03版本
 *  @author:  魏一鶴
 *  @createDate:  2021-12-14 23:31
 **/
public class BigDateExcelWrite07 {
    //全局路徑,供我們操作使用方便
  static String path = "D:\Tools\JavaWorkSpace\JavaExecl\" ;
    @Test
    public void BigDateExcelWrite07() throws Exception {
        //開始時間 用于計算時間差
  long beginTime = System.currentTimeMillis();
        //創(chuàng)建工作簿 07版本的使用XSSF
 Workbook workbook = new XSSFWorkbook();
        //創(chuàng)建工作表 這里就不給它命令了 按照默認的來
 Sheet sheet = workbook.createSheet();
        //寫入數(shù)據(jù) 循環(huán)插入65536行數(shù)據(jù),03版的HSSF最多只能插入65536行
  for (int rowNum = 0; rowNum < 65536; rowNum++) {
            //循環(huán)創(chuàng)建行
 Row row = sheet.createRow(rowNum);
            for(int cellNum=0;cellNum<10;cellNum++){
                //循環(huán)插入列
 Cell cell = row.createCell(cellNum);
                //循環(huán)設(shè)置值
 cell.setCellValue(cellNum);
            }
        }
        System.out.println( "生成excel表完畢" );
        //037版本的后綴是xlsx
 //開啟文件流
 FileOutputStream fileOutputStream = new FileOutputStream(path + "BigDateExcelWrite07.xlsx" );
        //開始寫excel
 workbook.write(fileOutputStream);
        //關(guān)閉流
 fileOutputStream.close();
        //結(jié)束時間
  long endTime = System.currentTimeMillis();
        //輸出花費的時間
 System.out.println( "花費的時間:" +(double)(endTime - beginTime)/1000);
    }
}

雖然也運行成功,但是可以明顯感覺到速度不如03版HSSF,但是可以存更多的數(shù)據(jù)

打開excel查看發(fā)現(xiàn)數(shù)據(jù)到了65536停并沒有結(jié)束,說明07版本XSSF上限不是65536,是可以存儲更多的,可以寫更多的數(shù)據(jù)

如果我們正在查看同一個文件,但是又進行其他操作,就會出現(xiàn)以下錯誤,我們把我們正在查看的文件關(guān)閉讓它運行,等運行結(jié)束后再次打開即可

java.io.FileNotFoundException: D:\Tools\JavaWorkSpace\JavaExecl\BigDateExcelWrite07.xlsx (另一個程序正在使用此文件，進程無法訪問。)

07版本XSSF導入100000條數(shù)據(jù),把循環(huán)數(shù)改為100000即可

既然XSSF可以存這么多數(shù)據(jù),但是速度比較慢,有沒有方法可以優(yōu)化效率呢(緩存,這個問題也可以叫做如何給poi加速

它就是Workbook借口三個實現(xiàn)類之一的SXSSFWorkbook,其他的兩個我們上面都有操作過

這時候需要用到我們的SXSSF

優(yōu)點:可以寫非常大的數(shù)據(jù)量.如100萬條甚至更多,寫速度非?？?占用更少的

注意

1 過程中會產(chǎn)生臨時文件,需要清理臨時文件

2 默認由100條記錄被保存在內(nèi)存中,如果超過這數(shù)量,則最前面的數(shù)據(jù)被寫入臨時文件,當然緩存數(shù)量也可以自定義

3 如果自定義內(nèi)存中數(shù)據(jù)的數(shù)量,可以使用new SXSSFWorkbook(數(shù)量)

SXSSF循環(huán)插入100000條數(shù)據(jù)

package com.wyh.Test;
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.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;
import java.io.FileOutputStream;
 /**
 *  @program:  JavaExecl
 *  @description:  大數(shù)據(jù)量寫03版本
 *  @author:  魏一鶴
 *  @createDate:  2021-12-14 23:31
 **/
public class BigDateExcelWrite07Super {
    //全局路徑,供我們操作使用方便
  static String path = "D:\Tools\JavaWorkSpace\JavaExecl\" ;
    @Test
    public void BigDateExcelWrite07Super() throws Exception {
        //開始時間 用于計算時間差
  long beginTime = System.currentTimeMillis();
        //創(chuàng)建工作簿 07版本的使用XSSF
 Workbook workbook = new SXSSFWorkbook();
        //創(chuàng)建工作表 這里就不給它命令了 按照默認的來
 Sheet sheet = workbook.createSheet();
        //寫入數(shù)據(jù) 循環(huán)插入65536行數(shù)據(jù),03版的HSSF最多只能插入65536行
  for (int rowNum = 0; rowNum < 100000; rowNum++) {
            //循環(huán)創(chuàng)建行
 Row row = sheet.createRow(rowNum);
            for(int cellNum=0;cellNum<10;cellNum++){
                //循環(huán)插入列
 Cell cell = row.createCell(cellNum);
                //循環(huán)設(shè)置值
 cell.setCellValue(cellNum);
            }
        }
        System.out.println( "生成excel表完畢" );
        //037版本的后綴是xlsx
 //開啟文件流
 FileOutputStream fileOutputStream = new FileOutputStream(path + "BigDateExcelWrite07Super.xlsx" );
        //開始寫excel
 workbook.write(fileOutputStream);
        //關(guān)閉流
 fileOutputStream.close();
        //由于SXSSF會產(chǎn)生臨時文件,這里我們需要清除下臨時文件
 ((SXSSFWorkbook) workbook).dispose();
        //結(jié)束時間
  long endTime = System.currentTimeMillis();
        //輸出花費的時間
 System.out.println( "花費的時間:" +(double)(endTime - beginTime)/1000);
    }
}

運行發(fā)現(xiàn),excel也正常生成了,但是它(SXSSF)的速度比XSSF快的多

查看我們的super