寫在前面
在Java日常開發過程中,實現Excel檔案的匯入匯出功能是一項常見的需求。
透過使用相關的Java庫,如Apache POI、EasyPoi或EasyExcel,可以輕鬆地實現Excel檔案的讀寫操作。
而這篇文章將介紹如何在Java中使用Apache POI、EasyPoi 和EasyExcel庫來進行Excel檔案的匯入和匯出操作,幫助您快速掌握這一實用的技能。
一、使用場景
下面是excel匯入匯出的幾個最常用的使用場景。
①企業管理系統:企業管理系統通常需要匯入員工資訊、客戶資訊、銷售資料等大量資料到系統中,以及匯出報表、資料分析結果等資訊。
②學校教務系統:學校教務系統可能需要匯入學生資訊、課程表、成績等資料,以及匯出學生成績單、教師工資表等資訊。
③電子商務平臺:電子商務平臺需要匯入商品資訊、訂單資料等內容,以及匯出銷售報表、庫存清單等資訊,方便管理和分析。
④ 人力資源管理系統:人力資源管理系統需要匯入員工檔案、薪資資訊等資料,以及匯出薪資條、考勤報表等資訊,方便人力資源管理和工資結算。
⑤ 醫院資訊系統:醫院資訊系統可能需要匯入患者資訊、醫療記錄等資料,以及匯出醫療報告、統計分析等資訊,方便醫護人員進行醫療服務和管理。
以上僅是一些常見的使用場景,實際上各種系統中的Excel匯入匯出功能可以根據具體需求進行定製和擴充套件
二、三個庫簡介
1、Apache POI
Apache POI是一個流行的Java庫,用於處理Microsoft Office格式檔案,包括Excel、Word和PowerPoint。它提供了豐富的API,可以建立、讀取和修改各種型別的Office文件。
官網:https://poi.apache.org/
優點:
-
功能強大:支援處理複雜的Excel檔案,包括單元格、樣式、圖表等內容。
-
穩定性高:作為一個成熟的開源專案,得到廣泛支援和持續維護。
-
靈活性:可以滿足各種定製化需求,可以實現複雜的Excel處理功能。
缺點:
- 學習曲線較陡:對於初學者來說,學習成本可能較高。
- 效能相對較低:在處理大量資料時,效能可能受到一定影響。
2、EasyPoi
easypoi功能如同名字easy,主打的功能就是容易,讓一個沒見接觸過poi的人員 就可以方便的寫出Excel匯出,Excel模板匯出,Excel匯入,Word模板匯出,透過簡單的註解和模板 語言(熟悉的表示式語法),完成以前複雜的寫法
官網: https://gitee.com/lemur/easypoi
優點:
- 簡單易用:EasyPoi 提供了簡潔的 API 介面,使得 Excel 操作更加便捷。
- 功能豐富:支援 Excel 檔案的匯入匯出、模板匯出、校驗等多種功能。
- 易於擴充套件:EasyPoi 基於 Apache POI 和 JexcelApi,可以方便地擴充套件和定製功能。
- 文件齊全:EasyPoi 提供了詳細的文件和示例,便於開發人員學習和使用。
缺點:
- 功能有限:相比於 Apache POI,EasyPoi 可能在一些高階功能上有所限制。
- 可能存在效能問題:由於封裝層的存在,EasyPoi 在處理大量資料時可能存在一定的效能損耗。
3、EasyExcel
EasyExcel是一個阿里巴巴基於Apache POI封裝的開源框架,專注於Excel檔案的讀寫操作。它提供了簡潔易用的API,簡化了Excel處理的流程。
官網:https://easyexcel.opensource.alibaba.com/
優點:
- 簡單易用:提供了簡潔的API,使用起來非常方便。
- 高效能:在處理大量資料時具有較高的效能,能夠快速匯入匯出Excel檔案。
- 支援註解:支援使用註解配置Excel檔案的匯入匯出規則,簡化了開發過程。
缺點:
- 功能相對有限:相比Apache POI,功能相對簡單,可能無法滿足某些複雜的Excel處理需求。
- 定製化能力較弱:定製化能力不如Apache POI靈活。
三 、各版本Excel 區別
不同版本的Excel在功能和格式上可能會有一些差異。所以後續在處理不同版本的excel時,會有少許不同
以下是一些常見的Excel版本之間的區別
1、Excel 97-2003(.xls)
- 最大行數為65536行,最大列數為256列。
- 支援的最大單元格格式有限。
- 不支援新的Excel特性,如條件格式、表格樣式等。
- 檔案大小限制為2GB。
2、Excel 2007及以上版本(.xlsx)
- 最大行數和列數均有較大提升,支援數百萬行數和16384列。
- 支援更多的單元格格式和樣式。
- 引入了新的功能,如條件格式、表格樣式、資料透視表等。
- 支援更多的圖表型別和圖表樣式。
- 檔案大小限制較大,最多可達16,384 x 1,048,576個單元格。
四 、Excel 基本結構介紹
Java是物件導向的操作語言,萬物皆物件。瞭解了Excel基本結構有助於我們將Excel與Java中物件關聯起來
- 工作簿(Workbook): Excel檔案以工作簿的形式存在,一個工作簿可以包含多個工作表(Sheet)
- 工作表(Sheet): 每個工作表由行(Row)和列(Column)組成,交叉形成單元格(Cell),用於儲存資料、文字、公式等內容
- 單元格(Cell): Excel中的最小單位,用於儲存資料或公式。每個單元格有一個唯一的地址,例如A1、B2等
- 行(Row)和列(Column): 行是水平方向的一組單元格,列是垂直方向的一組單元格。行用數字標識,列用字母標識
- 公式(Formula): Excel支援使用公式進行計算和資料處理。公式以等號(=)開頭,可以引用其他單元格的數值或內容進行運算
- 函式(Function): Excel提供了大量的內建函式,用於進行各種複雜的計算和資料處理,如SUM(求和)、AVERAGE(平均值)、VLOOKUP(垂直查詢)
五、Apache POI基本操作
由於Excel分為03版本和07版本,所以我們在使用的時候需要
注:
-
處理03版本excel時,主要使用
HSSFWorkbook
、HSSFSheet
、HSSFRow
、HSSFCell
等物件來操作Excel檔案; -
處理07版本及之後版本excel時,主要使用
XSSFWorkbook
、XSSFSheet
、XSSFRow
、XSSFCell
等物件來操作Excel檔案
其他操作基本是一樣的,瞭解這個之後,後續操作就很簡單了~
5.1 基本寫操作
5.1.1 03版本寫excel
① 引入依賴
<!--poi 03版本依賴-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<!--單元測試-->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13.2</version>
<scope>test</scope>
</dependency>
<!--日期-->
<dependency>
<groupId>joda-time</groupId>
<artifactId>joda-time</artifactId>
<version>2.10.10</version>
</dependency>
②例項程式碼
public class AppTest
{
String filepath="E:\\xiezhrspace\\excel-demo\\fileoutput\\";
@Test
public void poiexcel03Test() throws Exception{
//1、建立一個工作簿
Workbook workbook = new HSSFWorkbook();
//2、建立一個工作表
Sheet sheet = workbook.createSheet("第一個工作表");
//3、建立一行
//3.1 建立第一行
Row row1 = sheet.createRow(0);
//3.2 建立第二行
Row row2 = sheet.createRow(1);
//3.3 建立第三行
Row row3 = sheet.createRow(2);
//4 建立一個單元格
//4.1 建立第一行第一個單元格
Cell cell11 = row1.createCell(0);
//4.2 建立第一行第二個單元格
Cell cell12 = row1.createCell(1);
//4.3 建立第二行第一個單元格
Cell cell21 = row2.createCell(0);
//4.4 建立第二行第二個單元格
Cell cell22 = row2.createCell(1);
//4.5 建立第三行第一個單元格
Cell cell31 = row3.createCell(0);
//4.6 建立第三行第二個單元格
Cell cell32 = row3.createCell(1);
// 5 設定單元格的值
//5.1 設定第一行第一個單元格
cell11.setCellValue("個人公眾號");
//5.2 設定第一行第二個單元格
cell12.setCellValue("XiezhrSpace");
//5.3 設定第二行第一個單元格
cell21.setCellValue("個人部落格");
//5.4 設定第二行第二個單元格
cell22.setCellValue("www.xiezhr.cn");
//5.5 設定第三行第一個單元格
cell31.setCellValue("當前時間");
//5.6 設定第三行第二個單元格
String curdate = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
cell32.setCellValue(curdate);
FileOutputStream fileOutputStream = new FileOutputStream(filepath + "poiexcel03.xls");
workbook.write(fileOutputStream);
fileOutputStream.close();
workbook.close();
}
}
③ excel寫出效果
5.1.2 07版本寫excel
07 版本依賴與處理03版本的有點不一樣,程式碼基本上不變
① 所需依賴
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
② 程式碼修改
07版本操作與03版本操作基本沒什麼變化,我們只需將03版本程式碼中
new HSSFWorkbook()
修改成new XSSFWorkbook()
new FileOutputStream(filepath + "poiexcel03.xls")
修改成new FileOutputStream(filepath + "poiexcel07.xlsx")
即可
Workbook workbook = new XSSFWorkbook();
...省略
FileOutputStream fileOutputStream = new FileOutputStream(filepath + "poiexcel07.xlsx");
③ 最終效果
5.1.3 03版本批次寫excel
①程式碼
@Test
public void testBigDateExcelTest() throws Exception {
Workbook workbook = new HSSFWorkbook();
Sheet sheet = workbook.createSheet("大檔案匯出測試");
long begin = System.currentTimeMillis();
for (int rowNum = 0; rowNum <65536 ; rowNum++) {
Row row = sheet.createRow(rowNum);
for (int cellNum = 0; cellNum < 10 ; cellNum++) {
Cell cell = row.createCell(cellNum);
cell.setCellValue("("+(rowNum+1) + "," + (cellNum+1)+")");
}
}
FileOutputStream fileOutputStream = new FileOutputStream(filepath + "03版本批次寫入.xls");
workbook.write(fileOutputStream);
fileOutputStream.close();
workbook.close();
long end = System.currentTimeMillis();
System.out.println("耗時:"+(double)(end-begin)/1000+"秒");
}
②最終效果
根據記錄時間,耗時:1.663秒
5.1.4 07版本批次寫excel
① 程式碼修改
我們只需將上面
new HSSFWorkbook()
修改成new XSSFWorkbook()
new FileOutputStream(filepath + "03版本批次寫入.xls")
修改成new FileOutputStream(filepath + "07版本批次寫入.xlsx")
即可
Workbook workbook = new XSSFWorkbook();
...省略
FileOutputStream fileOutputStream = new FileOutputStream(filepath + "07版本批次寫入.xlsx");
② 最終效果
由於07及以上版本,沒有限制行數,所以在寫入資料時耗時相比較長。共耗時:10.959秒
注意: 如果03版本寫入資料行數超過65536行會報如下錯誤,而07版本的不會報錯
5.1.5 07版本批次寫入最佳化
透過上面的列子,我們可以看出來在07版本中批次寫入大資料的時候耗時比較長,這小節,我們就使用apach提供新的類來最佳化程式碼
① 程式碼
程式碼基本不用變,我們只需要將
new XSSFWorkbook()
修改為new SXSSFWorkbook()
並且最終將資料寫入過程中產生的快取檔案刪除
((SXSSFWorkbook) workbook).dispose();
@Test
public void batchWriteExcel07optTest() throws Exception {
Workbook workbook = new SXSSFWorkbook();
Sheet sheet = workbook.createSheet("大檔案匯出最佳化測試");
long begin = System.currentTimeMillis();
for (int rowNum = 0; rowNum <65536 ; rowNum++) {
Row row = sheet.createRow(rowNum);
for (int cellNum = 0; cellNum < 10 ; cellNum++) {
Cell cell = row.createCell(cellNum);
cell.setCellValue("("+(rowNum+1) + "," + (cellNum+1)+")");
}
}
FileOutputStream fileOutputStream = new FileOutputStream(filepath + "07版本批次寫入最佳化.xlsx");
workbook.write(fileOutputStream);
// 清理臨時檔案
((SXSSFWorkbook) workbook).dispose();
fileOutputStream.close();
workbook.close();
long end = System.currentTimeMillis();
System.out.println("耗時:"+(double)(end-begin)/1000+"秒");
}
② 最終效果
同樣的資料大小,耗時明顯減少了
5.2 基本讀操作
5.2.1 基本讀excel
① 03版本讀取
String filePath = "E:\\xiezhrspace\\excel-demo\\fileinput\\";
@Test
public void readExcel03Test() throws Exception{
FileInputStream fileInputStream = new FileInputStream(filePath + "poiexcel03.xls");
Workbook workbook = new HSSFWorkbook(fileInputStream);
Sheet sheet = workbook.getSheetAt(0);
//獲取第一行
Row row1 = sheet.getRow(0);
//獲取第一行的第一個單元格
Cell cell11 = row1.getCell(0);
//獲取第一行第二個單元格
Cell cell12 = row1.getCell(1);
System.out.println("第一行第一個單元格的內容是:"+cell11.getStringCellValue());
System.out.println("第一行第二個單元格的內容是:"+cell12.getStringCellValue());
//獲取第一行
Row row2 = sheet.getRow(1);
//獲取第一行的第一個單元格
Cell cell21 = row2.getCell(0);
//獲取第一行第二個單元格
Cell cell22 = row2.getCell(1);
System.out.println("第一行第一個單元格的內容是:"+cell21.getStringCellValue());
System.out.println("第一行第二個單元格的內容是:"+cell22.getStringCellValue());
}
//結果
第一行第一個單元格的內容是:個人公眾號
第一行第二個單元格的內容是:XiezhrSpace
第一行第一個單元格的內容是:個人部落格
第一行第二個單元格的內容是:www.xiezhrspace.cn
②07版本讀取
只需將
new FileInputStream(filePath + "poiexcel03.xls")
修改為new FileInputStream(filePath + "poiexcel07.xlsx")
new HSSFWorkbook(fileInputStream)
修改為new XSSFWorkbook(fileInputStream)
String filePath = "E:\\xiezhrspace\\excel-demo\\fileinput\\";
@Test
public void readExcel07Test() throws Exception{
FileInputStream fileInputStream = new FileInputStream(filePath + "poiexcel07.xlsx");
Workbook workbook = new XSSFWorkbook(fileInputStream);
Sheet sheet = workbook.getSheetAt(0);
//獲取第一行
Row row1 = sheet.getRow(0);
//獲取第一行的第一個單元格
Cell cell11 = row1.getCell(0);
//獲取第一行第二個單元格
Cell cell12 = row1.getCell(1);
System.out.println("第一行第一個單元格的內容是:"+cell11.getStringCellValue());
System.out.println("第一行第二個單元格的內容是:"+cell12.getStringCellValue());
//獲取第一行
Row row2 = sheet.getRow(1);
//獲取第一行的第一個單元格
Cell cell21 = row2.getCell(0);
//獲取第一行第二個單元格
Cell cell22 = row2.getCell(1);
System.out.println("第一行第一個單元格的內容是:"+cell21.getStringCellValue());
System.out.println("第一行第二個單元格的內容是:"+cell22.getStringCellValue());
}
//結果
第一行第一個單元格的內容是:個人公眾號
第一行第二個單元格的內容是:XiezhrSpace
第一行第一個單元格的內容是:個人部落格
第一行第二個單元格的內容是:www.xiezhr.cn
注:
如果上面獲取單元格資料的時候,取值的型別不對,即String
型別的資料透過cell22.getNumericCellValue()
獲取,則會報錯
其他型別不匹配情況類似
5.2.2 讀取不同型別資料
① 準備資料
現在有一張使用者訂單資訊表,表內容如下,表中包含了各種型別的資料
②書寫程式碼,將表格中的內容讀取出來輸出到控制檯
@Test
public void readExcel07ByTypeTest() throws Exception{
FileInputStream fileInputStream = new FileInputStream(filePath + "使用者訂單資訊表.xlsx");
//根據檔案輸入流獲取excel工作簿(Workbook)
Workbook workbook = new XSSFWorkbook(fileInputStream);
//根據工作簿獲取第一個sheet
Sheet sheet = workbook.getSheetAt(0);
//獲取表頭資訊
Row rowtitle = sheet.getRow(0);
//獲取表頭有多少列
int cells = rowtitle.getPhysicalNumberOfCells();
for (int cellNum = 0; cellNum < cells; cellNum++) {
Cell cell = rowtitle.getCell(cellNum);
String cellValue = cell.getStringCellValue();
System.out.print(cellValue + "\t");
}
//迴圈讀取資料
//獲取行數
int rows = sheet.getPhysicalNumberOfRows();
//從第二行開始讀取資料
for (int rowNum = 1; rowNum < rows; rowNum++) {
//獲取行物件
Row row = sheet.getRow(rowNum);
int ofCells = row.getPhysicalNumberOfCells();
//獲取列數
for (int cellNum = 0; cellNum < ofCells; cellNum++) {
System.out.print("第" + (rowNum + 1) + "行第" + (cellNum + 1) + "列");
//獲取單元格
Cell cell = row.getCell(cellNum);
//獲取單元格資料型別
if (cell!=null) {
CellType cellType = cell.getCellType();
//根據單元格資料型別獲取單元格資料
String cellvalue ="";
switch (cellType) {
case STRING : //判斷是否是字串型別
System.out.print("【String】");
cellvalue = cell.getStringCellValue();
break;
case NUMERIC: //判斷是否是數字型別
if (DateUtil.isCellDateFormatted(cell)) {
System.out.print("【date】");
Date date = cell.getDateCellValue();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
cellvalue = sdf.format(date);
} else {
System.out.print("【double】");
double numericCellValue = cell.getNumericCellValue();
cell.setCellType(CellType.STRING);
cellvalue = String.valueOf(numericCellValue);
}
break;
case BLANK:
System.out.print("【BLANK】");
break;
case BOOLEAN:
System.out.print("【BOOLEAN】");
boolean bool = cell.getBooleanCellValue();
cellvalue = String.valueOf(bool);
break;
case _NONE:
System.out.print("【_NONE】");
break;
case ERROR:
System.out.print("【ERROR】");
byte errorCode = cell.getErrorCellValue();
cellvalue = String.valueOf(errorCode);
break;
case FORMULA:
System.out.print("【FORMULA】");
XSSFFormulaEvaluator formulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
String formula = cell.getCellFormula();
System.out.println("formula:"+formula);
CellValue evaluate = formulaEvaluator.evaluate(cell);
cellvalue= evaluate.formatAsString();
break;
}
System.out.println(cellvalue);
}
}
}
}
六、EasyPoi 使用
Easypoi的目標不是替代poi,而是讓一個不懂匯入匯出的快速使用poi完成Excel和word的各種操作,而不是看很多api才可以完成這樣工作
Easypoi 為誰而開發?
- 不太熟悉poi的
- 不想寫太多重複太多的
- 只是簡單的匯入匯出的
- 喜歡使用模板的
6.1 所需依賴
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>4.1.0</version>
</dependency>
- 1.easypoi :父包–作用大家都懂得
- 2.easypoi-annotation :基礎註解包,作用與實體物件上,拆分後方便maven多工程的依賴管理
- 3.easypoi-base :匯入匯出的工具包,可以完成Excel匯出,匯入,Word的匯出,Excel的匯出功能
- 4.easypoi-web :耦合了spring-mvc 基於AbstractView,極大的簡化spring-mvc下的匯出功能
- 5.sax :匯入使用xercesImpl這個包(這個包可能造成奇怪的問題哈),word匯出使用poi-scratchpad,都作為可選包了
如果不使用spring mvc的便捷福利,直接引入easypoi-base 就可以了,easypoi-annotation
6.2 常用註解
EasyPoi 為了方便我們操作,實現了實體和Excel的對應,model--row,filed--col,這樣利用註解,我們就可以輕輕鬆鬆實現excel的匯入匯出
- @Excel 作用到filed上面,是對Excel一列的一個描述
- **@ExcelCollection ** 表示一個集合,主要針對一對多的匯出,比如一個老師對應多個科目,科目就可以用集合表示
- @ExcelEntity 表示一個繼續深入匯出的實體,但他沒有太多的實際意義,只是告訴系統這個物件裡面同樣有匯出的欄位
- @ExcelIgnore 和名字一樣表示這個欄位被忽略跳過這個導匯出
- @ExcelTarget 這個是作用於最外層的物件,描述這個物件的id,以便支援一個物件可以針對不同匯出做出不同處理
6.2.1 @ExcelTarget
限定一個到處實體的註解,以及一些通用設定,作用於最外面的實體
①常用屬性
屬性 | 型別 | 預設值 | 功能 |
---|---|---|---|
value |
String |
null | 定義id唯一標識,不能重複 |
height |
double |
10 | 定義單元格高度 |
fontSize |
short |
11 | 設定文字大小 |
②使用
@ExcelTarget("users")
public class User implements Serializable {
//..... 省略屬性 相關GET,SET方法
}
6.2.2 @Excel
@Excel這個是必須使用的註解,用在filed(屬性)上面,是對Excel一列的一個描述
① 常用屬性
屬性 | 型別 | 預設值 | 功能 |
---|---|---|---|
name |
String |
null | 生成Excel表格中列名 |
needMerge |
boolean |
fasle | 是否需要縱向合併單元格(用於含有list中,單個的單元格,合併list建立的多個row) |
orderNum |
String |
"0" | 指定生成Excel中列的順序,按照數字自然順序排序 |
replace |
String[] |
{} | 值得替換 匯出是{a_id,b_id} 匯入反過來 |
savePath |
String |
“upload” | 指定匯入Excel中圖片的儲存路徑 |
type |
int |
1 | 匯出型別 1 是文字 2 是圖片,3 是函式,10 是數字 預設是文字 |
width |
double |
10 | 指定匯出Excel時列的寬度 |
isImportField |
boolean |
true | 校驗欄位,看看這個欄位是不是匯入的Excel中有,如果沒有說明是錯誤的Excel,讀取失敗, |
exportFormat |
String |
"" | 匯出Excel的時間格式 |
importFormat |
String |
"" | 匯入Excel的時間格式 |
format |
String |
"" | 時間格式,相當於同時設定了exportFormat 和 importFormat |
imageType |
int |
1 | 匯出型別 1 從file讀取 2 是從資料庫中讀取 預設是檔案 同樣匯入也是一樣的 |
suffix |
String |
"" | 文字字尾,如% 90 變成90% |
isWrap |
boolean |
true | 是否換行 即支援\n |
mergeVertical |
boolean |
fasle | 縱向合併內容相同的單元格 |
②使用
@ExcelTarget("user")
public class User {
@Excel(name = "姓名",orderNum = "1",width = 20 )
private String name;
@Excel(name = "年齡",orderNum = "2",width = 20 )
private Integer age;
@Excel(name = "性別",orderNum = "3",width = 20,replace = {"男_1","女_2"})
private String sex;
@Excel(name = "生日",orderNum = "4",width = 20,format = "yyyy-MM-dd")
private Date birthday;
//...省略GET、SET方法
}
7.2.3 @ExcelEntity
來標記實體類中包含另一個實體類的欄位的註解。透過在父實體類的欄位上新增
@ExcelEntity
註解,可以將子實體類中的欄位對映到 Excel 表格中
① 常用屬性
屬性 | 型別 | 預設值 | 功能 |
---|---|---|---|
id | String | null | 定義ID |
② 使用
@ExcelTarget("user")
public class User {
//省略GET、SET方法和其他屬性
@ExcelEntity(name = "使用者基本資訊")
private UserBaseInfo userBaseInfo;
}
@ExcelTarget("userBaseInfo")
class UserBaseInfo{
@Excel(name = "手機號",orderNum = "5",width = 20)
private String phone;
@Excel(name = "郵箱",orderNum = "6",width = 20)
//省略其他欄位和方法
}
在上面的示例中, User
類中包含一個 UserBaseInfo
型別的欄位 userBaseInfo
,透過在 userBaseInfo
欄位上新增 @ExcelEntity
註解,可以將 UserBaseInfo
類中的欄位對映到 Excel 表格中,並且在 Excel 表格中會顯示為一個包含 手機號
和 郵箱
列的子表格。
6.2.4 @ExcelCollection
一對多的集合註解,來標記實體類中包含集合型別欄位的註解。
透過在父實體類的集合欄位上新增
@ExcelCollection
註解,可以將集合中的元素對映到 Excel 表格中
①常用屬性
屬性 | 型別 | 預設值 | 功能 |
---|---|---|---|
id |
String |
null | 定義ID |
name |
String |
null | 定義集合列名,支援nanm_id |
orderNum |
int |
0 | 排序,支援name_id |
type |
Class<?> |
ArrayList.class | 匯入時建立物件使用 |
② 使用
@ExcelTarget("user")
public class User {
//省略GET、SET 方法和其他屬性
@ExcelCollection(name = "商品列表",orderNum = "10")
private List<Product> products;
}
@ExcelTarget("product")
public class Product {
@Excel(name = "商品名稱" )
private String productName;
@Excel(name = "商品價格")
private double productPrice;
}
在上面的示例中, User
類中包含一個 List<Product>
型別的欄位 products
,透過在 products
欄位上新增 @ExcelCollection
註解,可以將 Product
型別的元素對映到 Excel 表格中,並且在 Excel 表格中會顯示為一個包含多個商品資訊的列表。
6.2.5 @ExcelIgnore
來標記實體類中不需要匯出到 Excel 表格的欄位的註解
① 使用
public class Product {
@Excel(name = "商品名稱")
private String name;
@Excel(name = "價格")
private double price;
@ExcelIgnore
private String description; // 不需要匯出到 Excel 表格的欄位
// 其他欄位和方法
}
在上面的示例中, Product
類中包含了 name
、 price
和 description
三個欄位。透過在 description
欄位上新增 @ExcelIgnore
註解,告訴 EasyPoi 在匯出 Excel 表格時不匯出該欄位的內容。
6.3 註解使用匯出Excel案例
6.3.1 匯出基本資料
① 建立基本物件物件
@Data
@ExcelTarget("user")
public class User {
@Excel(name = "姓名",orderNum = "1",width = 20,needMerge = true)
private String name;
@Excel(name = "年齡",orderNum = "2",width = 20,needMerge = true)
private Integer age;
@Excel(name = "性別",orderNum = "3",width = 20,replace = {"男_1","女_2"},needMerge = true)
private String sex;
@Excel(name = "生日",orderNum = "4",width = 20,format = "yyyy-MM-dd",needMerge = true)
private Date birthday;
}
②模擬測試資料
public List<User> testGetTestData(){
List<User> users = new ArrayList<User>();
for (int i = 0; i < 5; i++) {
User user = new User();
user.setName("小凡"+i);
user.setAge(i+18);
user.setSex("1");
user.setBirthday(new Date());
users.add(user);
}
return users;
}
③匯出資料
@Test
public void testExportExcel() throws Exception {
ExportParams exportParams = new ExportParams("使用者列表", "測試sheet頁");
//建立ExcelExportUtil物件,傳入引數為ExportParams物件,User類,testGetTestData()方法
Workbook workbook = ExcelExportUtil.exportExcel(exportParams, User.class, testGetTestData());
//建立FileOutputStream物件,傳入引數為檔案路徑
FileOutputStream fileOutputStream = new FileOutputStream("D:/test.xls");
//將workbook寫入到檔案輸出流中
workbook.write(fileOutputStream);
//關閉檔案輸出流
fileOutputStream.close();
//關閉workbook
workbook.close();
System.out.println("匯出成功");
}
④ 檢視excel
6.3.2 忽略某個欄位
① 新增註解
@ExcelIgnore
private Integer age;
②檢視excel
6.3.3 匯出物件中含有物件的資料
① 新增註解
@ExcelTarget("user")
public class User {
//省略GET、SET方法和其他屬性
@ExcelEntity(name = "使用者基本資訊")
private UserBaseInfo userBaseInfo;
}
② 新建UserBaseInfo類
@ExcelTarget("userBaseInfo")
class UserBaseInfo{
@Excel(name = "手機號",orderNum = "5",width = 20)
private String phone;
@Excel(name = "郵箱",orderNum = "6",width = 20)
//省略其他欄位和方法
}
③ 測試資料中新增UserBaseInfo
物件
user.setUserBaseInfo(new UserBaseInfo("15288345678","324355@qq.com","雲南省昆明市","651219","532334125689558"));
④ 匯出excel資料
6.3.4 匯出含list集合資料
①我們新建Product
物件
@ExcelTarget("product")
public class Product {
//省略GET、SET方法
@Excel(name = "商品名稱" )
private String productName;
@Excel(name = "商品價格")
private double productPrice;
}
② User
類中新增如下list集合
@ExcelCollection(name = "商品列表",orderNum = "10")
private List<Product> products;
③新增測試資料
public List<User> testGetTestData(){
List<User> users = new ArrayList<User>();
for (int i = 0; i < 5; i++) {
User user = new User();
user.setName("小凡"+i);
user.setAge(i+18);
user.setSex("1");
user.setBirthday(new Date());
user.setUserBaseInfo(new UserBaseInfo("15288345678","324355@qq.com","雲南省昆明市","651219","532334125689558"));
Product pro1 = new Product("冰箱"+i, 5000);
Product pro2 = new Product("洗衣機"+i, 3000);
Product pro3 = new Product("空調"+i, 4000);
List<Product> products = Arrays.asList(pro1, pro2, pro3);
user.setProducts(products);
users.add(user);
}
return users;
}
④ 由於是一對多關係,所以需要新增合併單元格屬性needMerge = true
@Excel(name = "姓名",orderNum = "1",width = 20,needMerge = true)
private String name;
@Excel(name = "年齡",orderNum = "2",width = 20,needMerge = true)
//其他屬性類似省略。。。
⑤匯出excel
6.3.5 匯出圖片
①User
類中定義頭像avter
欄位
@Excel(name = "頭像",orderNum = "0",width = 20,type = 2,height = 20,needMerge = true) //type的值一定要指定為2
private String avatar; //定義頭像 直接寫指定圖片路徑
② 構造測試資料
public List<User> testGetTestData(){
List<User> users = new ArrayList<User>();
for (int i = 0; i < 5; i++) {
User user = new User();
user.setName("小凡"+i);
user.setAge(i+18);
user.setSex("1");
user.setBirthday(new Date());
user.setUserBaseInfo(new UserBaseInfo("15288345678","324355@qq.com","雲南省昆明市","651219","532334125689558"));
Product pro1 = new Product("冰箱"+i, 5000);
Product pro2 = new Product("洗衣機"+i, 3000);
Product pro3 = new Product("空調"+i, 4000);
List<Product> products = Arrays.asList(pro1, pro2, pro3);
user.setAvatar("https://xiezhrspace.cn/medias/logo.png");
user.setProducts(products);
users.add(user);
}
return users;
}
③ 匯出excel
6.4 大資料匯出
當我們匯出得資料量為幾十萬到幾百萬時,一次將所有資料載入到記憶體中,會對cpu、記憶體都產生巨大壓力。
當然了,EasyPoi作者也為我們考慮了,專門提供
exportBigExcel
方法來匯出大資料
Workbook workbook1 = ExcelExportUtil.exportBigExcel(new ExportParams("使用者列表", "測試"), User.class, getUsers());
workbook1.write(outputStream);
ExcelExportUtil.closeExportBigExcel();
6.5 匯入excel
6.5.1 匯入基本資料
① 準備目標excel
② 定義基本資料物件
@ExcelTarget("student")
public class Student implements Serializable {
@Excel(name = "編號")
private String id;
@Excel(name = "姓名")
private String name;
@Excel(name = "性別", replace = {"男_1", "女_2"})
private String gender;
@Excel(name = "年齡")
private Integer age;
@Excel(name = "出生日期", importFormat = "yyyy-MM-dd")
private Date birth;
//省略GET、SET方法
}
③ 匯入程式碼
@Test
public void excelImportTest() throws Exception{
// 建立匯入引數配置
ImportParams importParams = new ImportParams();
// 設定標題行數
importParams.setTitleRows(1);
// 設定表頭行數
importParams.setHeadRows(1);
// 建立檔案輸入流
FileInputStream fileInputStream = new FileInputStream("E:\\xiezhrspace\\excel-demo\\fileinput\\學生資訊表.xlsx");
// 呼叫ExcelImportUtil的importExcel方法,傳入檔案輸入流,實體類,引數配置,返回實體類列表
List<Student> students = ExcelImportUtil.importExcel(fileInputStream, Student.class, importParams);
// 遍歷實體類列表,輸出實體類資訊
students.forEach(System.out::println);
// 關閉檔案輸入流
fileInputStream.close();
}
② 列印匯入結果
Student(id=10001, name=張三, gender=1, age=25, birth=1992-02-23)
Student(id=10002, name=李四, gender=1, age=18, birth=2006-01-01)
Student(id=10003, name=王五, gender=1, age=26, birth=1998-01-01)
Student(id=10004, name=趙六, gender=2, age=26, birth=1998-03-01)
6.5.2 匯入帶圖片資料
①excel資料
②Student
類設定圖片儲存路徑
@ExcelTarget("student")
public class Student implements Serializable {
@Excel(name = "編號")
private String id;
@Excel(name = "姓名")
private String name;
@Excel(name = "性別", replace = {"男_1", "女_2"})
private String gender;
@Excel(name = "年齡")
private Integer age;
@Excel(name = "出生日期", importFormat = "yyyy-MM-dd")
private Date birth;
@Excel(name = "頭像", type = 2, savePath = "src/main/imgs") //設定匯入圖片的儲存路徑
private String avatar;
//省略GET、SET 方法
}
③ 匯入excel
關鍵部分
importParams.setNeedSave(false);
importParams.setSaveUrl("src/main/imgs");
@Test
public void excelImportTest() throws Exception{
// 建立匯入引數配置
ImportParams importParams = new ImportParams();
// 設定標題行數
importParams.setTitleRows(1);
// 設定表頭行數
importParams.setHeadRows(1);
// 設定是否需要儲存
importParams.setNeedSave(false);
// 設定儲存路徑
importParams.setSaveUrl("src/main/imgs");
// 建立檔案輸入流
FileInputStream fileInputStream = new FileInputStream("E:\\xiezhrspace\\excel-demo\\fileinput\\學生資訊表.xlsx");
// 呼叫ExcelImportUtil的importExcel方法,傳入檔案輸入流,實體類,引數配置,返回實體類列表
List<Student> students = ExcelImportUtil.importExcel(fileInputStream, Student.class, importParams);
// 遍歷實體類列表,輸出實體類資訊
students.forEach(System.out::println);
// 關閉檔案輸入流
fileInputStream.close();
}
④ 匯入結果
Student(id=10001, name=張三, gender=1, age=25, birth=1992-02-23,avatar=src/main/imgs\pic33945659303.PNG)
Student(id=10002, name=李四, gender=1, age=18, birth=2006-01-01,avatar=src/main/imgs\pic31457305277.JPG)
Student(id=10003, name=王五, gender=1, age=26, birth=1998-01-01,avatar=src/main/imgs\pic71983821334.PNG)
Student(id=10004, name=趙六, gender=2, age=26, birth=1998-03-01,avatar=src/main/imgs\pic41577097054.PNG)
6.6 小結
以上小節為easypoi的最基本操作,如果大家還想了解更多,可以關注官方文件:http://doc.wupaas.com/docs/easypoi/
作者也給出了大量的例項:https://gitee.com/lemur/easypoi-test
八、EasyExcel使用
8.1 快速入門之寫excel
8.1.1 引入依賴
目前最新的依賴是3.3.3,我們這裡引入使用最多也最穩定的3.3.2版本
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.3.2</version>
</dependency>
8.1.2 構建資料物件
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Employee {
@ExcelProperty("員工編號")
private Integer id;
@ExcelProperty("員工姓名")
private String name;
@ExcelProperty("員工年齡")
private Integer age;
@ExcelProperty("員工薪資")
private Double salary;
@ExcelProperty("入職日期")
private Date inDate;
}
8.1.3 構建測試資料
private List<Employee> data(long count) {
List<Employee> list = ListUtils.newArrayList();
for (int i = 0; i < count; i++) {
Employee employee = new Employee();
employee.setId(i);
employee.setName("小陳"+i);
employee.setAge(18+1);
employee.setSalary(6.66+i);
employee.setInDate(new Date());
list.add(employee);
}
return list;
}
上面程式碼根據傳入count,構建通用資料,後面不會重複寫
8.1.4 寫入資料
@Test
public void testbaseWrite() {
String fileName = "E:\\xiezhrspace\\excel-demo\\fileoutput\\" + "simpleWrite" + System.currentTimeMillis() + ".xlsx";
// 這裡 需要指定寫用哪個class去寫,然後寫到第一個sheet,名字為模板 然後檔案流會自動關閉
// 如果這裡想使用03 則 傳入excelType引數即可
EasyExcel.write(fileName, Employee.class).sheet("測試模板").doWrite(data(10));
}
8.2 快速入門之讀excel
8.2.1 準備目標excel
8.2.2 構建資料物件
這裡我們沿用8.1.2 小節中建立的employee物件
8.2.3 讀取資料
注: PageReadListener 為預設easyexcel為我們寫好的監聽類,當然了,我們也可以自己定義,在後面的進階操作中
我們會說到
@Test
public void testbaseReade(){
String fileName = "E:\\xiezhrspace\\excel-demo\\fileinput\\" + "員工資訊表.xlsx";
EasyExcel.read(fileName, Employee.class, new PageReadListener<Employee>(dataList -> {
for (Employee employee : dataList) {
System.out.println(employee);
}
})).sheet().doRead();
}
8.3 進階操作
8.3.1 複雜表頭寫入
① 準備物件
@Data
public class Student {
@ExcelProperty({"學生資訊表","學生ID"})
private Integer id;
@ExcelProperty({"學生資訊表","學生姓名"})
private String name;
@ExcelProperty({ "學生資訊表", "學生年齡" })
private Integer age;
@ExcelProperty({ "學生資訊表", "生日" })
private Date birth;
}
②程式碼
public class UpEasyExcelTest {
private List<Student> data(long count) {
List<Student> list = ListUtils.newArrayList();
for (int i = 0; i < count; i++) {
Student student = new Student();
student.setId(i);
student.setName("小李" + i);
student.setAge(22+i);
student.setBirth(new Date());
list.add(student);
}
return list;
}
@Test
public void testUpWrite() {
String fileName = "E:\\xiezhrspace\\excel-demo\\fileoutput\\" + "upWrite" + System.currentTimeMillis() + ".xlsx";
// 這裡 需要指定寫用哪個class去寫,然後寫到第一個sheet,名字為模板 然後檔案流會自動關閉
// 如果這裡想使用03 則 傳入excelType引數即可
EasyExcel.write(fileName, Student.class).sheet("測試模板").doWrite(data(10));
}
}
③excel寫入示例
8.3.2 重複多次寫入(寫到單個或者多個Sheet)
① 準備物件
public class Employee {
@ExcelProperty("員工編號")
private Integer id;
@ExcelProperty("員工姓名")
private String name;
@ExcelProperty("員工年齡")
private Integer age;
@ExcelProperty("員工薪資")
private Double salary;
@ExcelProperty(value = "入職日期")
private Date inDate;
//省略GET、SET 方法
}
②程式碼
@Test
public void testmanyDateWriter(){
// 方法1: 如果寫到同一個sheet
String fileName = "E:\\xiezhrspace\\excel-demo\\fileoutput\\" + "manydataWrite" + System.currentTimeMillis() + ".xlsx";
// 這裡 需要指定寫用哪個class去寫
try (ExcelWriter excelWriter = EasyExcel.write(fileName, Employee.class).build()) {
// 這裡注意 如果同一個sheet只要建立一次
WriteSheet writeSheet = EasyExcel.writerSheet("模板").build();
// 去呼叫寫入,這裡我呼叫了五次,實際使用時根據資料庫分頁的總的頁數來
for (int i = 0; i < 5; i++) {
// 分頁去資料庫查詢資料 這裡可以去資料庫查詢每一頁的資料
List<Employee> data = data(200000);
excelWriter.write(data, writeSheet);
}
}
// 方法2: 如果寫到不同的sheet 同一個物件
fileName = "E:\\xiezhrspace\\excel-demo\\fileoutput\\" + "manydataWrite" + System.currentTimeMillis() + ".xlsx";
// 這裡 指定檔案
try (ExcelWriter excelWriter = EasyExcel.write(fileName, Employee.class).build()) {
// 去呼叫寫入,這裡我呼叫了五次,實際使用時根據資料庫分頁的總的頁數來。這裡最終會寫到5個sheet裡面
for (int i = 0; i < 5; i++) {
// 每次都要建立writeSheet 這裡注意必須指定sheetNo 而且sheetName必須不一樣
WriteSheet writeSheet = EasyExcel.writerSheet(i, "模板" + i).build();
// 分頁去資料庫查詢資料 這裡可以去資料庫查詢每一頁的資料
List<Employee> data = data(200000);
excelWriter.write(data, writeSheet);
}
}
// 方法3 如果寫到不同的sheet 不同的物件
fileName = "E:\\xiezhrspace\\excel-demo\\fileoutput\\" + "manydataWrite" + System.currentTimeMillis() + ".xlsx";
// 這裡 指定檔案
try (ExcelWriter excelWriter = EasyExcel.write(fileName).build()) {
// 去呼叫寫入,這裡我呼叫了五次,實際使用時根據資料庫分頁的總的頁數來。這裡最終會寫到5個sheet裡面
for (int i = 0; i < 5; i++) {
// 每次都要建立writeSheet 這裡注意必須指定sheetNo 而且sheetName必須不一樣。這裡注意DemoData.class 可以每次都變,我這裡為了方便 所以用的同一個class
// 實際上可以一直變
WriteSheet writeSheet = EasyExcel.writerSheet(i, "模板" + i).head(Employee.class).build();
// 分頁去資料庫查詢資料 這裡可以去資料庫查詢每一頁的資料
List<Employee> data = data(200000);
excelWriter.write(data, writeSheet);
}
}
}
③Excel示例
除了上面的常用例子外,還有更多玩法,大家可以參照官方文件,裡面寫的很詳細,這裡就不一一例舉了~
8.4 excel填充
EasyExcel提供了資料填充的功能,可以將指定的資料填充到事先設計好帶有樣式和格式的Excel模板文,這樣我們就可以製作出更加優美的excel
8.4.1 簡單的填充
① 準備模板
②準備物件
@Data
public class Student {
@ExcelProperty({"學生資訊表","學生ID"})
private Integer id;
@ExcelProperty({"學生資訊表","學生姓名"})
private String name;
@ExcelProperty({ "學生資訊表", "學生年齡" })
private Integer age;
@ExcelProperty({ "學生資訊表", "生日" })
private Date birth;
}
③填充資料
根據物件填充
@Test
public void filebyObjFillTest() {
// 模板注意 用{} 來表示你要用的變數 如果本來就有"{","}" 特殊字元 用"\{","\}"代替
String templateFileName ="E:\\xiezhrspace\\excel-demo\\fileinput\\" + "template.xlsx";
// 方案1 根據物件填充
String fileName = "E:\\xiezhrspace\\excel-demo\\fileoutput\\" + "fillWrite" + System.currentTimeMillis() + ".xlsx";
Student student = new Student();
student.setId(1);
student.setName("張三");
student.setAge(20);
student.setBirth(new Date());
// 這裡 會填充到第一個sheet, 然後檔案流會自動關閉
EasyExcel.write(fileName).withTemplate(templateFileName).sheet().doFill(student);
}
根據map填充
@Test
public void filebyMapFillTest() {
// 模板注意 用{} 來表示你要用的變數 如果本來就有"{","}" 特殊字元 用"\{","\}"代替
String templateFileName ="E:\\xiezhrspace\\excel-demo\\fileinput\\" + "template.xlsx";
// 方案2 根據Map填充
String fileName = "E:\\xiezhrspace\\excel-demo\\fileoutput\\" + "fillWrite" + System.currentTimeMillis() + ".xlsx";
// 這裡 會填充到第一個sheet, 然後檔案流會自動關閉
Map<String, Object> map = MapUtils.newHashMap();
map.put("id", "001");
map.put("name", "張三");
map.put("age", 20);
map.put("birthday", new Date());
EasyExcel.write(fileName).withTemplate(templateFileName).sheet().doFill(map);
}
④最終效果
8.4.2 填充列表資料
①準備模板
相比上一小節的模板,列表資料的模板
{id}----->{.id}
{name}--->{.name}
依此類推
②填充物件
參照上一小節
③ 準備資料
private List<Student> data(long count) {
List<Student> list = ListUtils.newArrayList();
for (int i = 0; i < count; i++) {
Student student = new Student();
student.setId(i);
student.setName("小李" + i);
student.setAge(22+i);
student.setBirth(new Date());
list.add(student);
}
return list;
}
④ 填充資料
@Test
public void listFill() {
// 模板注意 用{} 來表示你要用的變數 如果本來就有"{","}" 特殊字元 用"\{","\}"代替
// 填充list 的時候還要注意 模板中{.} 多了個點 表示list
// 如果填充list的物件是map,必須包涵所有list的key,哪怕資料為null,必須使用map.put(key,null)
String templateFileName ="E:\\xiezhrspace\\excel-demo\\fileinput\\" + "template-list.xlsx";
// 方案1 一下子全部放到記憶體裡面 並填充
String fileName = "E:\\xiezhrspace\\excel-demo\\fileoutput\\" + "fillWrite-list" + System.currentTimeMillis() + ".xlsx";
// 這裡 會填充到第一個sheet, 然後檔案流會自動關閉
EasyExcel.write(fileName).withTemplate(templateFileName).sheet().doFill(data(10));
}
⑤ 最終效果
8.4.3 複雜填充
當excel模板相對複雜時,也可以填充
① 準備複雜模板
② 準備填充物件
參照8.4.1小節
③準備資料
參照8.4.1小節
④填充資料
@Test
public void complexFill() {
// 模板注意 用{} 來表示你要用的變數 如果本來就有"{","}" 特殊字元 用"\{","\}"代替
// {} 代表普通變數 {.} 代表是list的變數
String templateFileName ="E:\\xiezhrspace\\excel-demo\\fileinput\\" + "template-hard.xlsx";
String fileName = "E:\\xiezhrspace\\excel-demo\\fileoutput\\" + "fillWrite-hard" + System.currentTimeMillis() + ".xlsx";
// 方案1
try (ExcelWriter excelWriter = EasyExcel.write(fileName).withTemplate(templateFileName).build()) {
WriteSheet writeSheet = EasyExcel.writerSheet().build();
// 這裡注意 入參用了forceNewRow 代表在寫入list的時候不管list下面有沒有空行 都會建立一行,然後下面的資料往後移動。預設 是false,會直接使用下一行,如果沒有則建立。
// forceNewRow 如果設定了true,有個缺點 就是他會把所有的資料都放到記憶體了,所以慎用
// 簡單的說 如果你的模板有list,且list不是最後一行,下面還有資料需要填充 就必須設定 forceNewRow=true 但是這個就會把所有資料放到記憶體 會很耗記憶體
// 如果資料量大 list不是最後一行 參照下一個
FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
excelWriter.fill(data(5), fillConfig, writeSheet);
excelWriter.fill(data(5), fillConfig, writeSheet);
Map<String, Object> map = MapUtils.newHashMap();
map.put("date", "2024年03月10日17:28:28");
map.put("total", 1000);
excelWriter.fill(map, writeSheet);
}
}
⑤ 最終效果
九、文章小節
文中只例舉出excel 常用操作,如果這些還不滿足你的需求,大家可以檢視官方文件。
官方文件還是非常詳細的,並且都給出了具體的demo。
另外文中例舉出的程式碼已提交到https://gitee.com/xiezhr/excel-demo.git 歡迎大家訪問檢視
本期內容到此就結束了,希望對你有所幫助,我們下期再見 (●'◡'●)