文章首發(fā)于我的個(gè)人博客裂垦，歡迎訪問(wèn)：https://blog.itzhouq.cn/poi
代碼地址：https://github.com/itzhouq/POIAndEasyExcelDemo
POI和EasyExcel 的使用筆記
我們經(jīng)常需要將項(xiàng)目中的表格數(shù)據(jù)或者文檔數(shù)據(jù)進(jìn)行導(dǎo)入或者導(dǎo)出操作，這個(gè)如果自己從零開(kāi)始做還比較麻煩。比如我之前就職的公司都是自己做的組件，但是很不好用，BUG 太多。關(guān)于表格導(dǎo)入導(dǎo)出崭添，市面上比較知名的開(kāi)源就是 Apache 的POI 和 阿里巴巴的 EasyExcel了。EasyExcel 也是對(duì) POI 的改進(jìn)和封裝叛氨， 更加好用呼渣。下面通過(guò)一些 demo 學(xué)習(xí)如何使用這兩個(gè)開(kāi)源組件。這兩個(gè)組件都不難寞埠，多看文檔就能會(huì)屁置，尤其是 EasyExcel 的文檔非常詳細(xì)。這篇博客主要自己在寫(xiě) demo 的時(shí)候整理的筆記畸裳，方便以后使用的時(shí)候查閱缰犁。如果能幫到你那就更好了淳地。
常用信息
1怖糊、將用戶(hù)的信息導(dǎo)出為 excel 表格帅容。
2、將 Excel 表中的信息錄入到網(wǎng)站數(shù)據(jù)庫(kù)伍伤。
開(kāi)發(fā)中經(jīng)常會(huì)涉及到 excel 的 處理并徘，如導(dǎo)出 Excel ，導(dǎo)入 Excel 到數(shù)據(jù)庫(kù)中扰魂。
操作 Excel 目前比較流行的就是 Apache POI 和阿里巴巴的 EasyExcel麦乞。
Apache POI
Apache POI 官網(wǎng)： http://poi.apache.org/index.html 。
百度百科的解釋： Apache POI是Apache軟件基金會(huì)的開(kāi)放源碼函式庫(kù)劝评，POI提供API給Java程序?qū)icrosoft Office格式檔案讀和寫(xiě)的功能姐直。
結(jié)構(gòu)：
HSSF － 提供讀寫(xiě)[Microsoft Excel](https://baike.baidu.com/item/Microsoft Excel)格式檔案的功能。excel 2003 版本
XSSF － 提供讀寫(xiě)Microsoft Excel OOXML格式檔案的功能蒋畜。excel 207 版本
HWPF － 提供讀寫(xiě)[Microsoft Word](https://baike.baidu.com/item/Microsoft Word)格式檔案的功能声畏。
HSLF － 提供讀寫(xiě)Microsoft PowerPoint格式檔案的功能。
HDGF － 提供讀寫(xiě)[Microsoft Visio](https://baike.baidu.com/item/Microsoft Visio)格式檔案的功能姻成。
EasyExcel
GitHub 地址： https://github.com/alibaba/easyexcel
EasyExcel 官網(wǎng)： https://www.yuque.com/easyexcel/doc/easyexcel
EasyExcel 是阿里巴巴開(kāi)源的一個(gè) excel處理框架插龄，以使用簡(jiǎn)單、節(jié)省內(nèi)存著稱(chēng)科展。
EasyExcel 能大大減少內(nèi)存占用的主要原因是在解析 Excel 時(shí)沒(méi)有將文件數(shù)據(jù)一次性全部加載到內(nèi)存中均牢，而是從磁盤(pán)上一行行讀取數(shù)據(jù)，逐個(gè)解析才睹。
下面是 EasyExcel 和 POI 在解析Excel 時(shí)的對(duì)比圖徘跪。
[圖片上傳失敗...(image-4732b1-1587700594308)]
1、POI-Excel 寫(xiě)
創(chuàng)建項(xiàng)目
1琅攘、建立一個(gè)空項(xiàng)目真椿，創(chuàng)建普通的 Module 。
2乎澄、引入依賴(lài)：
<dependencies>
<!-- xls03 -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<!-- xlsx07 -->
<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>
03 | 07 版本的寫(xiě)操作突硝，就是對(duì)象不一樣，方法都是一樣的置济。
需要注意：2003 版本和 2007 版本存在兼容性問(wèn)題解恰， 03 版本最多只有 65535 行。
03 版本：
package cn.itzhouq;
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.joda.time.DateTime;
import org.junit.Test;
import java.io.FileOutputStream;
public class ExcelWriteTest {
String PATH = "F:\\workspaces\\workspaces_idea\\POIAndEasyExcel\\itzhouq-poi";
@Test
public void testWrite03 () throws Exception {
// 1. 創(chuàng)建一個(gè)工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
// 2. 創(chuàng)建一個(gè)工作表
HSSFSheet sheet = workbook.createSheet("人員統(tǒng)計(jì)表");
// 3. 創(chuàng)建一個(gè)行 (1, 1)
Row row1 = sheet.createRow(0);
// 4. 創(chuàng)建一個(gè)單元格
Cell cell11 = row1.createCell(0);
cell11.setCellValue("今日新增人員");
Cell cell12 = row1.createCell(1);
cell12.setCellValue(666);
// 第二行
Row row2 = sheet.createRow(1);
Cell cell21 = row2.createCell(0); // （2,1 ）
cell21.setCellValue("統(tǒng)計(jì)時(shí)間");
Cell cell22 = row2.createCell(1);
String time = new DateTime().toString("yyyy-MM-dd HH:mm:ss");
cell22.setCellValue(time);
// 生成一張表 03版本的就是使用 xls 結(jié)尾
FileOutputStream fileOutputStream = new FileOutputStream(PATH + "/人員統(tǒng)計(jì)表03.xls");
workbook.write(fileOutputStream);
// 關(guān)閉流
fileOutputStream.close();
System.out.println("人員統(tǒng)計(jì)表03.xls 生成完畢");
}
}
大文件寫(xiě) HSSF
缺點(diǎn)：最多只能處理 65536 行浙于，否則會(huì)拋出異常
java.lang.IllegalArgumentException: Invalid row number (65536) outside allowable range (0..65535)
優(yōu)點(diǎn)：過(guò)程中寫(xiě)入緩存护盈，不操作磁盤(pán)，最后一次性寫(xiě)入磁盤(pán)羞酗，速度快腐宋。
@Test
public void testWrite03BigData () throws IOException {
// 時(shí)間
long begin = System.currentTimeMillis();
// 創(chuàng)建一個(gè)工作薄
Workbook workbook = new HSSFWorkbook();
// 創(chuàng)建表
Sheet sheet = workbook.createSheet();
// 寫(xiě)入數(shù)據(jù)
for (int rowNum = 0; rowNum < 65535; rowNum++) {
Row row = sheet.createRow(rowNum);
for (int cellNum = 0; cellNum < 10; cellNum++) {
Cell cell = row.createCell(cellNum);
cell.setCellValue(cellNum);
}
}
System.out.println("over");
FileOutputStream fileOutputStream = new FileOutputStream(PATH + "/testWrite03BigData.xls");
workbook.write(fileOutputStream);
fileOutputStream.close();
long end = System.currentTimeMillis();
System.out.println((double) (end - begin) / 1000); // 1.295
}
大文件寫(xiě) XSSF
缺點(diǎn)：寫(xiě)數(shù)據(jù)時(shí)速度非常慢，非常耗內(nèi)存，也會(huì)發(fā)生內(nèi)存溢出胸竞，如 100 萬(wàn)條數(shù)據(jù)欺嗤。
優(yōu)點(diǎn)：可以寫(xiě)較大的數(shù)據(jù)量，如 20 萬(wàn)條卫枝。
@Test
public void testWrite07BigData () throws IOException {
// 時(shí)間
long begin = System.currentTimeMillis();
// 創(chuàng)建一個(gè)工作薄
Workbook workbook = new XSSFWorkbook();
// 創(chuàng)建表
Sheet sheet = workbook.createSheet();
// 寫(xiě)入數(shù)據(jù)
for (int rowNum = 0; rowNum < 65537; rowNum++) {
Row row = sheet.createRow(rowNum);
for (int cellNum = 0; cellNum < 10; cellNum++) {
Cell cell = row.createCell(cellNum);
cell.setCellValue(cellNum);
}
}
System.out.println("over");
FileOutputStream fileOutputStream = new FileOutputStream(PATH + "/testWrite07BigData.xlsx");
workbook.write(fileOutputStream);
fileOutputStream.close();
long end = System.currentTimeMillis();
System.out.println((double) (end - begin) / 1000); // 7.39
}
大文件寫(xiě) SXSSF
優(yōu)點(diǎn)： 可以寫(xiě)非常大的數(shù)據(jù)量煎饼，如 100 萬(wàn)條甚至更多，寫(xiě)數(shù)據(jù)速度快校赤，占用更少的內(nèi)存吆玖。
注意：
過(guò)程總會(huì)產(chǎn)生臨時(shí)文件，需要清理臨時(shí)文件马篮。默認(rèn)由 100 條記錄被保存在內(nèi)存中沾乘，則最前面的數(shù)據(jù)被寫(xiě)入臨時(shí)文件。如果想要自定義內(nèi)存中數(shù)據(jù)的數(shù)量浑测，可以使用 new SXSSFWorkbook (數(shù)量)意鲸。
@Test
public void testWrite07BigDataS () throws IOException {
// 時(shí)間
long begin = System.currentTimeMillis();
// 創(chuàng)建一個(gè)工作薄
Workbook workbook = new SXSSFWorkbook();
// 創(chuàng)建表
Sheet sheet = workbook.createSheet();
// 寫(xiě)入數(shù)據(jù)
for (int rowNum = 0; rowNum < 65537; rowNum++) {
Row row = sheet.createRow(rowNum);
for (int cellNum = 0; cellNum < 10; cellNum++) {
Cell cell = row.createCell(cellNum);
cell.setCellValue(cellNum);
}
}
System.out.println("over");
FileOutputStream fileOutputStream = new FileOutputStream(PATH + "/testWrite07BigDataS.xlsx");
workbook.write(fileOutputStream);
fileOutputStream.close();
// 清除臨時(shí)文件
((SXSSFWorkbook) workbook).dispose();
long end = System.currentTimeMillis();
System.out.println((double) (end - begin) / 1000); // 1.859
}
SXSSFWorkbook 來(lái)自官方的解釋?zhuān)簩?shí)現(xiàn)“BigGridDemo” 策略的流式 SXSSFWorkbook 版本。這允許寫(xiě)入非常大的文件而不會(huì)耗盡內(nèi)存尽爆，因?yàn)槿魏螘r(shí)候只有可配置的行部分被保存在內(nèi)存中怎顾。
請(qǐng)注意，仍然可能會(huì)消耗大量?jī)?nèi)存漱贱，這些內(nèi)存基于你正在使用的功能槐雾，例如合并區(qū)域，注釋幅狮。募强。。仍然只存儲(chǔ)在內(nèi)存中崇摄，因此如果廣泛使用擎值，可能需要大量?jī)?nèi)存。
2逐抑、POI-Excel 讀
03 | 07 版本的讀操作
03 版本
String PATH = "F:\\workspaces\\workspaces_idea\\POIAndEasyExcel\\itzhouq-poi";
@Test
public void testRead03() throws IOException {
// 獲取文件流
FileInputStream fileInputStream = new FileInputStream(PATH + "/人員統(tǒng)計(jì)表03.xls");
// 1. 創(chuàng)建一個(gè)工作簿鸠儿，使用excel能操作的，代碼都能操作
Workbook workbook = new HSSFWorkbook(fileInputStream);
// 2. 得到表
Sheet sheet = workbook.getSheetAt(0);
// 3. 得到行
Row row = sheet.getRow(0);
// 4. 得到列
Cell cell = row.getCell(0);
System.out.println(cell.getStringCellValue()); // 今日新增人員
Cell cell1 = row.getCell(1);
System.out.println(cell1.getNumericCellValue()); // 666.0
fileInputStream.close();
}
07 版本
@Test
public void testRead07() throws IOException {
// 獲取文件流
FileInputStream fileInputStream = new FileInputStream(PATH + "/人員統(tǒng)計(jì)表07.xlsx");
// 1. 創(chuàng)建一個(gè)工作簿厕氨，使用excel能操作的进每，代碼都能操作
Workbook workbook = new XSSFWorkbook(fileInputStream);
// 2. 得到表
Sheet sheet = workbook.getSheetAt(0);
// 3. 得到行
Row row = sheet.getRow(0);
// 4. 得到列
Cell cell = row.getCell(0);
System.out.println(cell.getStringCellValue()); // 今日新增人員
Cell cell1 = row.getCell(1);
System.out.println(cell1.getNumericCellValue()); // 666.0
fileInputStream.close();
}
注意獲取值的類(lèi)型。
讀取不同的數(shù)據(jù)類(lèi)型（最麻煩的點(diǎn)）
@Test
public void testCellType() throws IOException {
// 獲取文件流
FileInputStream fileInputStream = new FileInputStream(PATH + "/明細(xì)表.xls");
// 創(chuàng)建一個(gè)工作簿
Workbook workbook = new HSSFWorkbook(fileInputStream);
Sheet sheet = workbook.getSheetAt(0);
// 獲取標(biāo)題內(nèi)容
Row rowTitle = sheet.getRow(0);
if (rowTitle != null) {
// 重點(diǎn)
int rowCount = rowTitle.getPhysicalNumberOfCells(); // 獲取列的數(shù)量
for (int cellNum = 0; cellNum < rowCount; cellNum++) {
Cell cell = rowTitle.getCell(cellNum);
if (cell != null) {
int cellType = cell.getCellType();
String cellValue = cell.getStringCellValue();
System.out.print(cellValue + " | ");
}
}
System.out.println();
}
// 獲取表中內(nèi)容
int rowCount = sheet.getPhysicalNumberOfRows();
for (int rowNum = 1; rowNum < rowCount; rowNum++) {
Row rowData = sheet.getRow(rowNum);
if (rowData != null) {
// 讀取列
int cellCount = rowTitle.getPhysicalNumberOfCells();
for (int cellNum = 0; cellNum < cellCount; cellNum++) {
System.out.print("[" + (rowNum + 1) + "-" + (cellNum + 1) + "]");
Cell cell = rowData.getCell(cellNum);
// 匹配列的數(shù)據(jù)類(lèi)型
if (cell != null) {
int cellType = cell.getCellType();
String cellValue = "";
switch (cellType) {
case HSSFCell.CELL_TYPE_STRING: // 字符串
System.out.print("【String】");
cellValue = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_BOOLEAN: // 布爾
System.out.print("【BOOLEAN】");
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_BLANK: // 空
System.out.print("【BLANK】");
break;
case HSSFCell.CELL_TYPE_NUMERIC: // 數(shù)字
System.out.print("【UMERIC】");
if (HSSFDateUtil.isCellDateFormatted(cell)) { // 日期
System.out.print("【日期】");
Date date = cell.getDateCellValue();
cellValue = new DateTime(date).toString();
} else {
// 不是日期格式命斧，防止數(shù)字過(guò)長(zhǎng)
System.out.print("【裝換為字符串輸出】");
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cellValue = cell.toString();
}
break;
case HSSFCell.CELL_TYPE_ERROR:
System.out.print("【數(shù)據(jù)類(lèi)型錯(cuò)誤】");
break;
}
System.out.println(cellValue);
}
}
}
}
fileInputStream.close();
}
結(jié)果：
注意類(lèi)型轉(zhuǎn)換問(wèn)題田晚。可以將上面的方法提取成工具類(lèi)国葬。
計(jì)算公式(了解)
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.junit.Test;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
/**
* 計(jì)算公式
*/
public class Formula {
String PATH = "F:\\workspaces\\workspaces_idea\\POIAndEasyExcel\\itzhouq-poi"
@Test
public void testForMula() throws IOException {
FileInputStream fileInputStream = new FileInputStream(PATH + "/公式.xls");
Workbook workbook = new HSSFWorkbook(fileInputStream);
Sheet sheet = workbook.getSheetAt(0);
Row row = sheet.getRow(4);
Cell cell = row.getCell(0);
// 拿到計(jì)算公式
FormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);
// 輸出單元格的內(nèi)容
int cellType = cell.getCellType();
switch (cellType) {
case (Cell.CELL_TYPE_FORMULA): // 公式
String formula = cell.getCellFormula();
System.out.println(formula); // SUM(A2:A4)
// 計(jì)算
CellValue evaluate = formulaEvaluator.evaluate(cell);
String cellValue = evaluate.formatAsString();
System.out.println(cellValue); // 1188.0
break;
}
}
}
3贤徒、EsayExcel 操作
官方文檔很詳細(xì)芹壕，可以根據(jù)文檔快速入門(mén) https://www.yuque.com/easyexcel/doc/easyexcel 。
導(dǎo)入依賴(lài)
<dependencies>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.0-beta2</version>
</dependency>
<!-- 日期格式化工具 -->
<dependency>
<groupId>joda-time</groupId>
<artifactId>joda-time</artifactId>
<version>2.10.1</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.10</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>compile</scope>
</dependency>
</dependencies>
寫(xiě)入測(cè)試
根據(jù)官方文檔的測(cè)試代碼： https://www.yuque.com/easyexcel/doc/write
1接奈、DemoData.java
package cn.itzhouq.easyexcel;
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
import java.util.Date;
@Data
public class DemoData {
@ExcelProperty("字符串標(biāo)題")
private String string;
@ExcelProperty("日期標(biāo)題")
private Date date;
@ExcelProperty("數(shù)字標(biāo)題")
private Double doubleData;
/**
* 忽略這個(gè)字段
*/
@ExcelIgnore
private String ignore;
}
2踢涌、測(cè)試寫(xiě)入數(shù)據(jù)
package cn.itzhouq.easyexcel;
import com.alibaba.excel.EasyExcel;
import org.junit.Test;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
public class EasyExcelTest {
private List<DemoData> data() {
List<DemoData> list = new ArrayList<DemoData>();
for (int i = 0; i < 10; i++) {
DemoData data = new DemoData();
data.setString("字符串" + i);
data.setDate(new Date());
data.setDoubleData(0.56);
list.add(data);
}
return list;
}
// 根據(jù)list 寫(xiě)入 Excel
/**
* 最簡(jiǎn)單的寫(xiě)
* <p>1. 創(chuàng)建excel對(duì)應(yīng)的實(shí)體對(duì)象 參照{(diào)@link DemoData}
* <p>2. 直接寫(xiě)即可
*/
@Test
public void simpleWrite() {
String PATH = "F:\\workspaces\\workspaces_idea\\POIAndEasyExcel\\EasyExcel\\";
String fileName =PATH + System.currentTimeMillis() + ".xlsx";
// 這里 需要指定寫(xiě)用哪個(gè)class去寫(xiě)，然后寫(xiě)到第一個(gè)sheet鲫趁，名字為模板 然后文件流會(huì)自動(dòng)關(guān)閉
// 如果這里想使用03 則 傳入excelType參數(shù)即可
EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data());
}
}
最終結(jié)果
