Java使用POI實現(xiàn)導(dǎo)出Excel的方法詳解
一、前景
在項目開發(fā)中往往需要使用到Excel的導(dǎo)入和導(dǎo)出，導(dǎo)入就是從Excel中導(dǎo)入到DB中，而導(dǎo)出就是從DB中查詢數(shù)據(jù)然后使用POI寫到Excel上。
操作Excel目前比較流行的就是Apache POI和阿里巴巴的easyExcel !
廢話不多說，開始擼起來！??！
二、概念
POI官網(wǎng)：https://poi.apache.org/
POI官網(wǎng)API：https://poi.apache.org/components/spreadsheet/index.html
POI的Javadocs文檔：https://poi.apache.org/apidocs/index.html
百度百科介紹：https://baike.baidu.com/item/Apache%20POI/4242784?fr=aladdin
2.1. 簡介
POI不僅僅可以操作Excel，他的定位是操作Microsoft Office讀和寫，Microsoft Office其中包含了很多常用的辦公文件，例如：Excel、ppt、word、Visio等等…
結(jié)構(gòu)：
- HSSF－ 提供讀寫Microsoft Excel XLS格式檔案的功能。
- XSSF－ 提供讀寫Microsoft Excel OOXML XLSX格式檔案的功能。
- HWPF－ 提供讀寫Word(97-2003) 的 Java 組件，XWPF是 POI 支持 Word 2007+ 的 Java組件，提供簡單文件的讀寫功能；
- HSLF－ 提供讀寫Microsoft PowerPoint格式檔案的功能。
- HDGF － 提供讀Microsoft Visio格式檔案的功能。
- HPBF － 提供讀Microsoft Publisher格式檔案的功能。
- HSMF－ 提供讀Microsoft Outlook格式檔案的功能。
截止目前最新的版本是5.2.3（Sep 17, 2022），現(xiàn)在還在不斷的完善
2.2.Excel版本和相關(guān)對象
Excel有兩個版本：
- 2003版本和2007版本存在兼容性的問題！03最多只有65536行！07版本最多有1048576行！
- 2003版本的文件名后綴是.xls
- 2007版本的文件后綴名是.xlsx
相關(guān)對象：工作簿、工作表、行、列 對應(yīng)的POI當(dāng)中的對象是Workbook、Sheet、Row、Cell
03最多只有65536行，如下所示：
在poi當(dāng)中往往會說超過65535行會報錯，原因是poi當(dāng)中0代表的是第一行！
07最多只有1048576行，如下所示：
2.3.WorkBook
首先我們知道POI中我們最熟悉的莫過于WorkBook這樣一個接口，WorkBook代表的就是我們上面所提到的工作簿，WorkBook有如下三個實現(xiàn)類。明確一點，這三個都是WorkBook的實現(xiàn)類，所以用法上基本上是一致的！
HSSFWorkbook: 這個實現(xiàn)類是我們早期使用最多的對象，它可以操作Excel2003以前（包含2003）的所有Excel版本。在2003以前Excel的版本后綴還是.xls
缺點： 最多只能導(dǎo)出 65535行，也就是導(dǎo)出的數(shù)據(jù)函數(shù)超過這個數(shù)據(jù)就會報錯;
優(yōu)點： 一般不會報內(nèi)存溢出。（因為數(shù)據(jù)量還不到7w所以內(nèi)存一般都夠用，首先你得明確知道這種方式是將數(shù)據(jù)先讀取到內(nèi)存中，然后再操作）
XSSFWorkbook： 這個實現(xiàn)類現(xiàn)在在很多公司都可以發(fā)現(xiàn)還在使用，它是操作的Excel2003–Excel2007之間的版本，Excel的擴展名是.xlsx
優(yōu)點： 這種形式的出現(xiàn)是為了突破HSSFWorkbook的65535行局限，是為了針對Excel2007版本的1048576行，16384列，最多可以導(dǎo)出104w條數(shù)據(jù)；
缺點： 伴隨的問題來了，雖然導(dǎo)出數(shù)據(jù)行數(shù)增加了好多倍，但是隨之而來的內(nèi)存溢出問題也成了噩夢。因為你所創(chuàng)建的book，Sheet，row，cell等在寫入到Excel之前，都是存放在內(nèi)存中的（這還沒有算Excel的一些樣式格式等等），可想而知，內(nèi)存不溢出就有點不科學(xué)了?。?！
SXSSFWorkbook : 這個實現(xiàn)類是POI3.8之后的版本才有的,它可以操作Excel2007以后的所有版本Excel,擴展名是.xlsx
優(yōu)點： 這種方式不會一般不會出現(xiàn)內(nèi)存溢出（它使用了硬盤來換取內(nèi)存空間，也就是當(dāng)內(nèi)存中數(shù)據(jù)達到一定程度這些數(shù)據(jù)會被持久化到硬盤中存儲起來，而內(nèi)存中存的都是最新的數(shù)據(jù)），并且支持大型Excel文件的創(chuàng)建（存儲百萬條數(shù)據(jù)綽綽有余）。
缺點：
- 既然一部分數(shù)據(jù)持久化到了硬盤中，且不能被查看和訪問那么就會導(dǎo)致，在同一時間點我們只能訪問一定數(shù)量的數(shù)據(jù)，也就是內(nèi)存中存儲的數(shù)據(jù);
- sheet.clone()方法將不再支持,還是因為持久化的原因;
- 不再支持對公式的求值，還是因為持久化的原因，在硬盤中的數(shù)據(jù)沒法讀取到內(nèi)存中進行計算；
- 在使用模板方式下載數(shù)據(jù)的時候，不能改動表頭，還是因為持久化的問題，寫到了硬盤里就不能改變了；
經(jīng)過了解也知道了這三種Workbook的優(yōu)點和缺點，那么具體使用哪種方式還是需要看情況的：
- 當(dāng)我們經(jīng)常導(dǎo)入導(dǎo)出的數(shù)據(jù)不超過7w的情況下，可以使用HSSFWorkbook或者XSSFWorkbook都行；
- 當(dāng)數(shù)據(jù)量超過7w并且導(dǎo)出的Excel中不牽扯對Excel的樣式，公式，格式等操作的情況下，推薦使用SXSSFWorkbook;
- 當(dāng)數(shù)據(jù)量超過7w，并且我們需要操做Excel中的表頭，樣式，公式等，這時候我們可以使用XSSFWorkbook配合進行分批查詢，分批寫入Excel的方式來做；
2.4.POI依賴
hutool是一個工具合集，使用poi實際上只需要引入poi-ooxml就可以。因為poi-ooxml里面已經(jīng)引入了poi和poi-ooxml-schemas的依賴。
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>4.1.2</version> </dependency> <dependency> <groupId>cn.hutool</groupId> <artifactId>hutool-all</artifactId> <version>5.8.8</version> </dependency>
三、POI - 寫
workbook常用API:
createSheet()：創(chuàng)建Excel工作表 返回類型為HSSFSheeet
setSheetName()：設(shè)置Excel工作表的名稱，語法結(jié)構(gòu)如下
public void setSheetName(int sheetIx,String name)
3.1.代碼示例
（1）HSSFWorkbook
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.io.FileUtil;
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 java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
public class ExcelWriterTest03 {
public static void main(String[] args) throws IOException {
String path = "D:\\poi\\";
// 1.創(chuàng)建一個工作簿。03
Workbook workbook = new HSSFWorkbook();
// 2.創(chuàng)建一個工作表
Sheet sheet = workbook.createSheet("統(tǒng)計表");
// 3.創(chuàng)建行。第一行
Row row = sheet.createRow(0);
// 4.創(chuàng)建列。
// (1,1) 第一行第一列的單元格
Cell cell = row.createCell(0);
cell.setCellValue("我們都一樣");
// (1,2) 第一行第二列的單元格
Cell cell2 = row.createCell(1);
cell2.setCellValue(666);
// 第二行。(1,0)
Row row1 = sheet.createRow(1);
//（2,1）第二行第一列的單元格
Cell cell1 = row1.createCell(0);
cell1.setCellValue(DateUtil.now());
// 判斷文件是否存在，不存在就創(chuàng)建
if (FileUtil.isEmpty(new File(path))) {
FileUtil.mkdir(path);
}
// 5.生成一張表。03版本的工作簿是以.xls結(jié)尾
FileOutputStream fileOutputStream = new FileOutputStream(path + "03.xls");
// 輸出
workbook.write(fileOutputStream);
// 6.關(guān)閉流
fileOutputStream.close();
System.out.println("03表生成成功！");
}
}
生成的Excel如下：
最多65536行，而poi是以0為第一行，所以這里只能寫65535，類似于數(shù)組以0代表第一個元素一樣，一旦超過65535就會報以下異常：
注意：
- 假如路徑下已經(jīng)存在Excel文件，再次生成他會直接覆蓋該文件。
- 使用HSSFWorkbook也可以使用xlsx結(jié)尾，正常也是可以打開的，但是超過65535同樣會報錯
（2）XSSFWorkbook
他是可以超過65535行的并且不會報錯，并且他兼容.xls、.xlsx兩種格式都是可以的。這里需要注意一下，即時是使用的.xls，只要使用的是XSSFWorkbook，超過65535行同樣也不會報錯！
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.io.FileUtil;
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 java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
public class ExcelWriterTest03 {
public static void main(String[] args) throws IOException {
String path = "D:\\poi\\";
// 1.創(chuàng)建一個工作簿。03
Workbook workbook = new XSSFWorkbook(); // 07和03版本只有對象不同，其他操作一樣
// 2.創(chuàng)建一個工作表
Sheet sheet = workbook.createSheet("統(tǒng)計表");
// 3.創(chuàng)建行。第一行
Row row = sheet.createRow(0);
// 4.創(chuàng)建列。
// (1,1) 第一行第一列的單元格
Cell cell = row.createCell(0);
cell.setCellValue("我們都一樣");
// (1,2) 第一行第二列的單元格
Cell cell2 = row.createCell(1);
cell2.setCellValue(666);
// 第65537行。(65537,0)
Row row1 = sheet.createRow(65536);
//（2,1）第二行第一列的單元格
Cell cell1 = row1.createCell(0);
cell1.setCellValue(DateUtil.now());
// 判斷文件是否存在，不存在就創(chuàng)建
if (FileUtil.isEmpty(new File(path))) {
FileUtil.mkdir(path);
}
// 5.生成一張表。03版本的工作簿是以.xls結(jié)尾
FileOutputStream fileOutputStream = new FileOutputStream(path + "03.xls");
// 輸出
workbook.write(fileOutputStream);
// 6.關(guān)閉流
fileOutputStream.close();
System.out.println("03表生成成功！");
}
}
（3）SXSSFWorkbook
SXSSFWorkbook同XSSFWorkbook使用方法一樣！也是不受行數(shù)限制！只不過他是硬盤換時間，在大數(shù)據(jù)量的情況下，會將內(nèi)存當(dāng)中的數(shù)據(jù)寫到臨時文件當(dāng)中，這樣達到釋放內(nèi)存，因此占用內(nèi)存較小，然后速度要比XSSFWorkbook快！
使用SXSSFWorkbook wb = new SXSSFWorkbook(100)創(chuàng)建的工作簿在讀取數(shù)據(jù)時，會根據(jù)所傳入的閾值（此處是100，默認也是100）。當(dāng)內(nèi)存中的對象達到這個閾值時，生成一個臨時文件，以臨時文件進行存儲，來實現(xiàn)分段讀取與寫入。舉個例子：假如寫入1-10行數(shù)據(jù)，然后設(shè)置的閥值是2，那么會將1-8行的數(shù)據(jù)寫到硬盤，9和10行的寫到內(nèi)存。類似于一個隊列先進先出的規(guī)則！
//當(dāng)為-1的時候表示 將會把所有的行刷新到臨時文件 Workbook workbook = new SXSSFWorkbook(-1); //當(dāng)為100的時候表示 將會把超過100行的數(shù)據(jù)刷新到臨時文件 Workbook workbook = new SXSSFWorkbook(100); //表示手動刷新所有數(shù)據(jù)到臨時文件的方式 ，可指定參數(shù) 行數(shù) ((SXSSFSheet) sheet).flushRows()；
這里需要注意的是，當(dāng)每次刷新到臨時文件。內(nèi)存中的數(shù)據(jù)就不存在了，因此避免了OOM。有些小伙伴可能會犯還去拿行數(shù)，或者操作行數(shù)據(jù)的問題。這些數(shù)據(jù)已經(jīng)被刷新到臨時文件，內(nèi)存中已經(jīng)不存在了。所以就拿不到了。（拋異常）
SXSSF在把內(nèi)存數(shù)據(jù)刷新到硬盤時，是把每個SHEET生成一個臨時文件，這個臨時文件可能會很大，有可以會達到G級別，如果文件的過大對你來說是一個問題，你可以使用wb.setCompressTempFiles(true);方法讓SXSSF來進行壓縮，當(dāng)然性能也會有一定的影響。
默認的臨時文件存放目錄：
- windows下：AppData\Local\Temp\poifiles文件夾下，生成一個叫poi-sxssf-sheet**************的文件
- Linux系統(tǒng)下：會在/tmp/poifiles文件下生成該臨時文件
代碼示例： 這里我故意設(shè)置了為5000 Workbook workbook = new SXSSFWorkbook(5000);，然后在workbook.createSheet這個地方打斷點，當(dāng)執(zhí)行完的時候臨時文件已經(jīng)創(chuàng)建了！當(dāng)執(zhí)行完for循環(huán)后，臨時文件已經(jīng)存在內(nèi)容了，執(zhí)行write之后會將所有內(nèi)容都寫入臨時文件，沒有執(zhí)行write之前，會將超過閥值的數(shù)據(jù)提前寫入臨時文件當(dāng)中，關(guān)于這一點大家可以自行測試！
public class ExcelWriterTest03BigData {
public static void main(String[] args) throws IOException {
// 開始時間
long start = System.currentTimeMillis();
String path = "D:\\poi\\";
// 1.創(chuàng)建一個工作簿。03
Workbook workbook = new SXSSFWorkbook(5000);
// 2.創(chuàng)建一個工作表
Sheet sheet = workbook.createSheet("統(tǒng)計表");
// 3.創(chuàng)建行。
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(rowNum + "," + cellNum);
}
}
// 5.生成一張表。03版本的工作簿是以.xlsx結(jié)尾
FileOutputStream fileOutputStream = new FileOutputStream(path + "07BigDataUpGrade.xlsx");
// 輸出
workbook.write(fileOutputStream);
// 6.關(guān)閉流
fileOutputStream.close();
// 7.清除臨時文件
((SXSSFWorkbook) workbook).dispose();
System.out.println("07大數(shù)據(jù)量表優(yōu)化后生成成功！");
// 結(jié)束時間
long end = System.currentTimeMillis();
System.out.println("用時：" + ((end - start) / 1000) + "秒");
}
}
通過以下會發(fā)現(xiàn)，他是寫到了xml當(dāng)中。然后又通過讀取xml當(dāng)中的內(nèi)容轉(zhuǎn)換到我們設(shè)置的Excel文件當(dāng)中。寫到Excel是個耗時的操作，于是先寫到硬盤將內(nèi)存釋放，然后這樣就是所謂的硬盤換內(nèi)存。
這是執(zhí)行完write方法之后文件的大?。?/p>
通過以下配置就可以實現(xiàn)臨時文件的自定義配置。再有就是記住臨時文件的清理。自帶api就有實現(xiàn)((SXSSFWorkbook) workbook).dispose(); 清理臨時緩存文件。因為我用的是父類所以強轉(zhuǎn)了。
@Component
public class ExcelConfig {
private final static Logger logger = LoggerFactory.getLogger(ExcelConfig.class);
@Value("${application.tmp.path}")
private String applicationTmpPath;
/**
* 設(shè)置使用SXSSFWorkbook對象導(dǎo)出excel報表時，TempFile使用的臨時目錄，代替{java.io.tmpdir}
*/
@PostConstruct
public void setExcelSXSSFWorkbookTmpPath() {
String excelSXSSFWorkbookTmpPath = applicationTmpPath + "/poifiles";
File dir = new File(excelSXSSFWorkbookTmpPath);
if (!dir.exists()) {
dir.mkdirs();
}
TempFile.setTempFileCreationStrategy(new TempFile.DefaultTempFileCreationStrategy(dir));
logger.info("setExcelSXSSFWorkbookTmpPath={}", excelSXSSFWorkbookTmpPath);
}
}
3.2. 性能對比
（1）HSSFWorkbook
優(yōu)點：過程中寫入緩存，不操作磁盤，最后一次性寫入磁盤，速度快。
缺點：最多只能處理65536行，否則會拋出異常。
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 java.io.FileOutputStream;
import java.io.IOException;
public class ExcelWriterTest03BigData {
public static void main(String[] args) throws IOException {
// 開始時間
long start = System.currentTimeMillis();
String path = "D:\\poi\\";
// 1.創(chuàng)建一個工作簿。03
Workbook workbook = new HSSFWorkbook();
// 2.創(chuàng)建一個工作表
Sheet sheet = workbook.createSheet("統(tǒng)計表");
// 3.創(chuàng)建行。
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+","+cellNum);
}
}
// 5.生成一張表。03版本的工作簿是以.xls結(jié)尾
FileOutputStream fileOutputStream = new FileOutputStream(path + "03BigData.xls");
// 輸出
workbook.write(fileOutputStream);
// 6.關(guān)閉流
fileOutputStream.close();
System.out.println("03大數(shù)據(jù)量表生成成功！");
// 結(jié)束時間
long end = System.currentTimeMillis();
System.out.println("用時："+((end-start)/1000)+"秒");
}
}
（2）XSSFWorkbook
直接使用以上示例來測試即可，然后將Workbook 換成XSSFWorkbook
缺點：寫數(shù)據(jù)時速度非常慢，非常耗內(nèi)存，也會發(fā)生內(nèi)存溢出，如100萬條。
優(yōu)點：可以寫較大的數(shù)據(jù)量，如20萬條。
（3）SXSSFWorkbook
注意：
- 過程中產(chǎn)生臨時文件，需要清理臨時文件。
- 默認由100條記錄被保存在內(nèi)存中，如果超過這數(shù)量，則最前面的數(shù)據(jù)被寫入臨時件。
- 如果想自定義內(nèi)存中數(shù)據(jù)的數(shù)量，可以使用new SXSSFWorkbook(數(shù)量)
3.3. 測試rowAccessWindowSize
對于不一樣的rowAccessWindowSize值，進行耗時測試。
例子：生成三個SHEET，每一個SHEET有 200000 行記錄，共60萬行記錄flex
- rowAccessWindowSize：1的時候執(zhí)行是30s
- rowAccessWindowSize：100的時候執(zhí)行是34s
- rowAccessWindowSize：200的時候執(zhí)行是51s
- rowAccessWindowSize：5000的時候執(zhí)行是326s
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.util.CellReference;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import java.io.FileOutputStream;
import java.io.IOException;
public class ClassSXSSFWorkBookUtil {
public static void main(String[] args) throws IOException {
long curr_time = System.currentTimeMillis();
// 內(nèi)存中緩存記錄行數(shù)
int rowAccess = 100;
SXSSFWorkbook wb = new SXSSFWorkbook(rowAccess);
// 生成3個SHEET
int sheetNum = 3;
for (int i = 0; i < sheetNum; i++) {
Sheet sh = wb.createSheet();
// 每一個SHEET有 200000 ROW
for (int rowNum = 0; rowNum < 200000; rowNum++) {
Row row = sh.createRow(rowNum);
//每行有10個CELL
for (int cellnum = 0; cellnum < 10; cellnum++) {
Cell cell = row.createCell(cellnum);
String address = new CellReference(cell).formatAsString();
cell.setCellValue(address);
}
// 每當(dāng)行數(shù)達到設(shè)置的值就刷新數(shù)據(jù)到硬盤,以清理內(nèi)存，這塊本質(zhì)上其實不加這個poi在達到閥值也會向臨時文件寫數(shù)據(jù)，
// 假如導(dǎo)出60w數(shù)據(jù)3個sheet，加上手動刷新是34s，然后不加是40s，所以在一定程度上來講手動刷新要快一點
if (rowNum % rowAccess == 0) {
((SXSSFSheet) sh).flushRows();
}
}
}
FileOutputStream os = new FileOutputStream("D:\\poi\\biggrid.xlsx");
wb.write(os);
os.close();
System.out.println("耗時(秒):" + (System.currentTimeMillis() - curr_time) / 1000);
}
}
這個測試出來的結(jié)果跟電腦配置有很大關(guān)系，實際開發(fā)當(dāng)中，可以采取這種方式然后看看設(shè)置多少比較快，然后進行優(yōu)化！
3.4. 導(dǎo)出Excel樣式設(shè)置
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import java.io.FileOutputStream;
import java.io.IOException;
public class Test {
public static void main(String[] args) throws IOException {
//創(chuàng)建HSSFWorkbook對象
HSSFWorkbook wb = new HSSFWorkbook();
//建立sheet對象
HSSFSheet sheet = wb.createSheet("成績表");
// 設(shè)置列寬
sheet.setColumnWidth(0, 25 * 256);
sheet.setColumnWidth(1, 25 * 256);
sheet.setColumnWidth(2, 25 * 256);
sheet.setColumnWidth(3, 25 * 256);
sheet.setColumnWidth(4, 25 * 256);
// 記住一點設(shè)置單元格樣式相關(guān)的都是CellStyle來控制的,設(shè)置完之后只需set給單元格即可：cell.setCellStyle(cellStyle);
// 合并單元格后居中
CellStyle cellStyle = wb.createCellStyle();
// 垂直居中
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setAlignment(HorizontalAlignment.CENTER);
// 設(shè)置字體
Font font = wb.createFont();
font.setFontName("宋體");
font.setFontHeightInPoints((short) 16);
font.setItalic(false);
font.setStrikeout(false);
cellStyle.setFont(font);
// 設(shè)置背景色
cellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
// 設(shè)置邊框（一般標題不設(shè)置邊框，是標題下的所有表格設(shè)置邊框）
cellStyle.setBorderBottom(BorderStyle.THIN); //下邊框
cellStyle.setBorderLeft(BorderStyle.THIN);//左邊框
cellStyle.setBorderTop(BorderStyle.THIN);//上邊框
cellStyle.setBorderRight(BorderStyle.THIN);//右邊框
//在sheet里創(chuàng)建第一行，參數(shù)為行索引
HSSFRow row1 = sheet.createRow(0);
// 合并單元格：參數(shù)1：行號 參數(shù)2：起始列號 參數(shù)3：行號 參數(shù)4：終止列號
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 4));
// 創(chuàng)建單元格
HSSFCell cell = row1.createCell(0);
cell.setCellStyle(cellStyle);
//設(shè)置單元格內(nèi)容
cell.setCellValue("學(xué)生成績表");
//在sheet里創(chuàng)建第二行
HSSFRow row2 = sheet.createRow(1);
//創(chuàng)建單元格并設(shè)置單元格內(nèi)容
row2.createCell(0).setCellValue("姓名");
row2.createCell(1).setCellValue("班級");
row2.createCell(2).setCellValue("語文成績");
row2.createCell(3).setCellValue("數(shù)學(xué)成績");
row2.createCell(4).setCellValue("英語成績");
//在sheet里創(chuàng)建第三行
HSSFRow row3 = sheet.createRow(2);
row3.createCell(0).setCellValue("小明");
row3.createCell(1).setCellValue("1班");
row3.createCell(2).setCellValue(80);
row3.createCell(3).setCellValue(75);
row3.createCell(4).setCellValue(88);
//在sheet里創(chuàng)建第四行
HSSFRow row4 = sheet.createRow(3);
row4.createCell(0).setCellValue("小紅");
row4.createCell(1).setCellValue("1班");
row4.createCell(2).setCellValue(82);
row4.createCell(3).setCellValue(70);
row4.createCell(4).setCellValue(90);
FileOutputStream fileOutputStream = new FileOutputStream("D:\\poi\\04.xlsx");
wb.write(fileOutputStream);
fileOutputStream.close();
}
}
四、POI - 讀
當(dāng)你企圖使用SXSSFWorkbook去加載一個已存在的Excel模板時，首先你應(yīng)該用XSSFWorkbook去獲取它 ，以下列舉了常用的四種獲取XSSFWorkbook的方式。
XSSFWorkbook(java.io.File file) XSSFWorkbook(java.io.InputStream is) XSSFWorkbook(OPCPackage pkg) XSSFWorkbook(java.lang.String path)
4.1.代碼示例
使用SXSSFWorkbook寫的文檔，必須使用SXSSFWorkbook來讀，否則報錯！同樣HSSFWorkbook寫入也必須用HSSFWorkbook讀取！當(dāng)然SXSSFWorkbook是不能用來讀取的！
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 java.io.FileInputStream;
import java.io.IOException;
public class ExcelReadTest03 {
public static void main(String[] args) throws IOException {
String path = "D:\\poi\\";
FileInputStream fileInputStream = new FileInputStream(path + "03.xlsx");
// 1.創(chuàng)建一個工作簿。使得excel能操作的，這邊他也能操作。
// Workbook workbook = new HSSFWorkbook(fileInputStream);
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());// 數(shù)字類型
// 5.關(guān)閉流。
fileInputStream.close();
}
}
4.2.讀取不同的數(shù)據(jù)類型
這里重點會用到CellType枚舉類，就是獲取當(dāng)前單元格的類型，CellType cellType = cell.getCellType();，舊版本poi直接獲取的是int值，int cellType = cell.getCellType();，這塊還是有一定的區(qū)別的，但是枚舉都是用的這個類。
- _NONE(-1), // none類型
- NUMERIC(0), // 數(shù)值類型
- STRING(1), // 字符串類型
- FORMULA(2), // 公式類型
- BLANK(3), // 空格類型
- BOOLEAN(4), // 布爾類型
- ERROR(5); // 錯誤
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileInputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.Date;
public class ExcelReadTestType {
public static void main(String[] args) throws IOException {
String path = "D:\\poi\\";
// 1.獲取文件流
FileInputStream fileInputStream = new FileInputStream(path + "會員消費商品明細表.xls");
// 2.創(chuàng)建一個工作簿。使用excel能操作的這邊他也可以操作。
Workbook workbook = new HSSFWorkbook(fileInputStream);
// Workbook workbook = new XSSFWorkbook(fileInputStream);
// 3.獲取第一張表。
Sheet sheet = workbook.getSheetAt(0);
// 4.獲取標題內(nèi)容。
Row rowTitle = sheet.getRow(0);
if (rowTitle != null) {
// 獲取一行有多少列
int cellCount = rowTitle.getPhysicalNumberOfCells();
// 循環(huán)遍歷，獲取每一個標題名稱
for (int cellNum = 0; cellNum < cellCount; cellNum++) {
Cell cell = rowTitle.getCell(cellNum);
if (cell != null) {
System.out.print(cell.getStringCellValue() + "|");
}
}
System.out.println();
}
// 5.獲取表中的記錄
// 獲取有多少行記錄
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++) {
// 獲得單元格
Cell cell = rowData.getCell(cellNum);
// 匹配列的數(shù)據(jù)類型
String cellValueByCell = getCellValueByCell(cell);
System.out.println(cellValueByCell);
}
}
System.out.println("----");
}
fileInputStream.close();
}
//獲取單元格各類型值，返回字符串類型
public static String getCellValueByCell(Cell cell) {
//判斷是否為null或空串
if (cell == null || cell.toString().trim().equals("")) {
return "";
}
String cellValue = "";
CellType cellType = cell.getCellType();
switch (cellType) {
// 數(shù)字
case NUMERIC:
short format = cell.getCellStyle().getDataFormat();
if (DateUtil.isCellDateFormatted(cell)) {
SimpleDateFormat sdf = null;
//System.out.println("cell.getCellStyle().getDataFormat()="+cell.getCellStyle().getDataFormat());
if (format == 20 || format == 32) {
sdf = new SimpleDateFormat("HH:mm");
} else if (format == 14 || format == 31 || format == 57 || format == 58) {
// 處理自定義日期格式：m月d日(通過判斷單元格的格式id解決，id的值是58)
sdf = new SimpleDateFormat("yyyy-MM-dd");
double value = cell.getNumericCellValue();
Date date = org.apache.poi.ss.usermodel.DateUtil
.getJavaDate(value);
cellValue = sdf.format(date);
} else {
// 日期
sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
}
try {
// 日期
cellValue = sdf.format(cell.getDateCellValue());
} catch (Exception e) {
try {
throw new Exception("exception on get date data !".concat(e.toString()));
} catch (Exception e1) {
e1.printStackTrace();
}
} finally {
sdf = null;
}
} else {
BigDecimal bd = new BigDecimal(cell.getNumericCellValue());
// 數(shù)值 這種用BigDecimal包裝再獲取plainString，可以防止獲取到科學(xué)計數(shù)值
cellValue = bd.toPlainString();
}
break;
// 字符串
case STRING:
cellValue = cell.getStringCellValue();
break;
// Boolean
case BOOLEAN:
cellValue = cell.getBooleanCellValue() + "";
break;
// 公式
case FORMULA:
cellValue = cell.getCellFormula();
break;
// 空值
case BLANK:
cellValue = "";
break;
// 故障
case ERROR:
cellValue = "ERROR VALUE";
break;
default:
cellValue = "UNKNOW VALUE";
break;
}
return cellValue;
}
}
getPhysicalNumberOfRows()獲取的是物理行數(shù)，也就是不包括空行（隔行）的情況。getLastRowNum()獲取的是最后一行的編號（編號從0開始）
注意：日常中我們進行POI讀取導(dǎo)入EXCEL表格操作時，一定要保證工作薄干凈，即有效數(shù)據(jù)區(qū)域外的單元格千萬不要動。不然可能會出現(xiàn)，明明Excel有兩條數(shù)據(jù)，但是讀出來好多空格內(nèi)容，往往就是我們不小心動了別的單元格導(dǎo)致，然后肉眼還看不出來，但是getPhysicalNumberOfRows獲取行數(shù)就會有好幾行空格內(nèi)容！
4.3.讀取公式
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import java.io.FileInputStream;
import java.io.IOException;
public class GS {
public static void main(String[] args) throws IOException {
String path = "D:\\poi\\";
FileInputStream fileInputStream = new FileInputStream(path + "計算公式.xls");
// 1.創(chuàng)建一個工作簿。使得excel能操作的，這邊他也能操作。
Workbook workbook = new HSSFWorkbook(fileInputStream);
// 2.得到表。
Sheet sheet = workbook.getSheetAt(0);
Row row = sheet.getRow(4);
Cell cell = row.getCell(0);
// 拿到計算公式
FormulaEvaluator formulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);
// 輸出單元格內(nèi)容
CellType cellType = cell.getCellType();
switch (cellType) {
case FORMULA:
String cellFormula = cell.getCellFormula();
System.out.println(cellFormula);
// 計算
CellValue evaluate = formulaEvaluator.evaluate(cell);
String cellValue = evaluate.formatAsString();
System.out.println(cellValue);
break;
}
}
}
五、POI - 遇到的坑
為什么模板中的數(shù)據(jù)獲取不到？
根據(jù)我對SXSSFWorkbook的了解，它只會加載一部分數(shù)據(jù)到內(nèi)存，其余的數(shù)據(jù)全部持久化到本次磁盤。
但是當(dāng)你噼里啪啦對SXSSFWorkbook進行了一頓操作時，你會忽然發(fā)現(xiàn)為什么SXSSFSheet.getRow(0) = null???
這是因為這些記錄存在于硬盤當(dāng)中！
以上就是Java使用POI實現(xiàn)導(dǎo)出Excel的方法詳解的詳細內(nèi)容，更多關(guān)于Java POI導(dǎo)出Excel的資料請關(guān)注腳本之家其它相關(guān)文章！
- Java利用POI實現(xiàn)導(dǎo)入導(dǎo)出Excel表格示例代碼
- java poi導(dǎo)出圖片到excel示例代碼
- Java使用poi組件導(dǎo)出Excel格式數(shù)據(jù)
- Java使用POI導(dǎo)出大數(shù)據(jù)量Excel的方法
- Java中利用POI優(yōu)雅的導(dǎo)出Excel文件詳解
- Java Web使用POI導(dǎo)出Excel的方法詳解
- Java中用POI實現(xiàn)將數(shù)據(jù)導(dǎo)出到Excel
- java poi導(dǎo)出excel時如何設(shè)置手動換行
- Java使用Poi導(dǎo)出Excel表格方法實例
相關(guān)文章
關(guān)于SpringBoot改動后0.03秒啟動的問題
這篇文章主要介紹了SpringBoot改動后0.03秒啟動,本文結(jié)合示例代碼給大家講解的非常詳細，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值，需要的朋友可以參考下2022-12-12
這篇文章主要介紹了Java集合之LinkedHashSet類詳解,LinkedHashSet 是 Java 中的一個集合類,它是 HashSet 的子類,并實現(xiàn)了 Set 接口,與 HashSet 不同的是,LinkedHashSet 保留了元素插入的順序,并且具有 HashSet 的快速查找特性,需要的朋友可以參考下2023-09-09
Spring和SpringMVC父子容器關(guān)系初窺(小結(jié))
這篇文章主要介紹了Spring和SpringMVC父子容器關(guān)系初窺(小結(jié))，小編覺得挺不錯的，現(xiàn)在分享給大家，也給大家做個參考。一起跟隨小編過來看看吧2018-01-01
這篇文章主要介紹了mybatis輸出SQL格式化方式，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2021-11-11
目前幾乎所有的語言都支持信號量機制,Java也不例外.Java中提供了Semaphore并發(fā)工具類來支持信號量機制,下面我們就來了解Java實現(xiàn)的信號量機制，文中有非常詳細的介紹,需要的朋友可以參考下2021-06-06
小編最近接手一個項目，涉及到天貓精靈的語音功能，今天小編通過本文給大家分享下springboot項目接入天貓精靈語音功能的詳細過程及實例代碼，感興趣的朋友跟隨小編一起看看吧2021-12-12
歸并排序是建立在歸并操作上的一種有效的排序算法。該算法是采用分治法（Divide and Conquer）的一個非常典型的應(yīng)用。本文將通過圖片詳解插入排序的原理及實現(xiàn)，需要的可以參考一下2022-08-08
SpringBoot發(fā)送異步郵件流程與實現(xiàn)詳解
這篇文章主要介紹了SpringBoot發(fā)送異步郵件流程與實現(xiàn)詳解,Servlet階段郵件發(fā)送非常的復(fù)雜,如果現(xiàn)代化的Java開發(fā)是那個樣子該有多糟糕,現(xiàn)在SpringBoot中集成好了郵件發(fā)送的東西,而且操作十分簡單容易上手,需要的朋友可以參考下2024-01-01
這篇文章主要介紹了Nacos動態(tài)配置管理機制方式,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2024-07-07
這篇文章主要為大家詳細介紹了Java中集合關(guān)系圖及常見操作，解析Java中的集合類型的繼承關(guān)系圖，具有一定的參考價值，感興趣的小伙伴們可以參考一下2017-11-11