Java操作Excel表格
POI 和 easyExcel
常用資訊
1、将使用者資訊導出為excel表格(導出資料… )
2、将Excel表中的資訊錄入到網站資料庫(習題上傳… )
開發中經常會設計到excel的處理,如導出Excel ,導入Excel到資料庫中!
操作ExceI目前比較流行的就是Apache POI和阿裡巴巴的easyExcel !
Apache POI
Apache POI 官網： https://poi.apache.org/
HSSF一提供讀寫Microsoft Excel格式檔案的功能。
XSSF -提供讀寫Microsoft Excel OOXML格式檔案的功能。
HWPF -提供讀寫Microsoft Word各式檔案的功能。
HSLF -提供讀寫Microsof PowerPoint式檔案的功能。
HDGF -提供讀寫Microsoft Visio格式檔案的功能。
easyExcel
easyExcel官網：https://github.com/alibaba/easyexcel
POI-Excel寫
建立項目
1、建立一個空項目
2、導入依賴
<!--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>
<!--test-->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13</version>
<scope>test</scope>
</dependency>
03版本：
@Test
public void ExcelTset() throws IOException {
//1.建立一個工作簿
Workbook workbook = new HSSFWorkbook();
//2.建立一個工作表
Sheet sheet = workbook.createSheet("小說統計表");
//3.建立一個行
Row row1 = sheet.createRow(0);//(1,1)
//4.建立一個單元格
Cell cell11 = row1.createCell(0);
cell11.setCellValue("小說名字");
//(1,2)
Cell cell12 = row1.createCell(1);
cell12.setCellValue("遮天");
//第二行
Row row2 = sheet.createRow(1);
Cell cell21 = row2.createCell(0);
cell21.setCellValue("小說字數");
row2.createCell(1).setCellValue("200萬字");
Row row3 = sheet.createRow(2);
Cell cell31 = row3.createCell(0);
cell31.setCellValue("日期");
row3.createCell(1).setCellValue(new Date());
//生成一張表
FileOutputStream fileOutputStream = new FileOutputStream(PATH + "小說03.xls");
workbook.write(fileOutputStream);
//關閉流
fileOutputStream.close();
System.out.println("表生成完畢");
}
07版本：
@Test
public void ExcelTset2() throws IOException {
//1.建立一個工作簿
Workbook workbook = new XSSFWorkbook();
//2.建立一個工作表
Sheet sheet = workbook.createSheet("小說統計表");
//3.建立一個行
Row row1 = sheet.createRow(0);//(1,1)
//4.建立一個單元格
Cell cell11 = row1.createCell(0);
cell11.setCellValue("小說名字");
//(1,2)
Cell cell12 = row1.createCell(1);
cell12.setCellValue("遮天");
//第二行
Row row2 = sheet.createRow(1);
Cell cell21 = row2.createCell(0);
cell21.setCellValue("小說字數");
row2.createCell(1).setCellValue("200萬字");
Row row3 = sheet.createRow(2);
Cell cell31 = row3.createCell(0);
cell31.setCellValue("日期");
row3.createCell(1).setCellValue(new Date());
//生成一張表
FileOutputStream fileOutputStream = new FileOutputStream(PATH + "小說07.xlsx");
workbook.write(fileOutputStream);
//關閉流
fileOutputStream.close();
System.out.println("表生成完畢");
}
注意對象的差別，檔案字尾！
大檔案寫HSSF
@Test
public void ExcelBig() throws IOException {
//時間
long begin = System.currentTimeMillis();
Workbook workbook = new HSSFWorkbook();
Sheet sheet = workbook.createSheet();
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(cellNum);
}
}
System.out.println("Over");
FileOutputStream fileOutputStream = new FileOutputStream(PATH + "bigData03.xls");
workbook.write(fileOutputStream);
fileOutputStream.close();
long end = System.currentTimeMillis();
System.out.println((float) (end-begin)/1000);
}
缺點：最多隻能處理65536行，否則會抛出異常
優點：過程中寫入緩存,不操作磁盤,最後一次性寫入磁盤,速度快
大檔案寫XSSF
@Test
public void ExcelBig2() throws IOException {
//時間
long begin = System.currentTimeMillis();
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet();
for (int rowNum = 0; rowNum < 100000; 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 + "bigData07.xlsx");
workbook.write(fileOutputStream);
fileOutputStream.close();
long end = System.currentTimeMillis();
System.out.println((float) (end-begin)/1000);
}
缺點:寫資料時速度非常慢,非常耗記憶體,也會發生記憶體溢出,如100萬條
優點:可以寫較大的資料量,如20萬條
大檔案寫SXSSF
@Test
public void ExcelBig3S() throws IOException {
//時間
long begin = System.currentTimeMillis();
Workbook workbook = new SXSSFWorkbook();
Sheet sheet = workbook.createSheet();
for (int rowNum = 0; rowNum < 100000; 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 + "bigData07S.xlsx");
workbook.write(fileOutputStream);
fileOutputStream.close();
//清除零時文
((SXSSFWorkbook)workbook).dispose();
long end = System.currentTimeMillis();
System.out.println((float) (end-begin)/1000);
}
優點:可以寫非常大的資料量,如100萬條甚更多條,寫資料速度快，占用更少的記憶體
注意:
過程中會産生臨時檔案，需要清理臨時檔案
預設由100條記錄被儲存在記憶體中,如果超過這數量,則最前面的資料被寫入臨時檔案
如果想自定義記憶體中資料的數量,可以使用new SXSSFWorkbook (數量)
SXSSFWorkbook
來至官方的解釋:實作
BigGridDemo
政策的流式XSSFWorkbook版本。這允許寫入非常大的檔案而不會耗盡記憶體,因為任何時候隻有可配置的行部分被儲存在記憶體中。
請注意,仍然可能會消耗大量記憶體,這些記憶體基于您正在使用的功能,例如合并區域,注…仍然隻存儲在記憶體中,是以如果廣泛使用，可能需要大量記憶體.
POI-Excel讀
03版本
@Test
public void Read03Test() throws IOException {
FileInputStream inputStream = new FileInputStream(PATH + "poi_writebigData03.xls");
Workbook workbook = new HSSFWorkbook(inputStream);
//得到表
Sheet sheetAt = workbook.getSheetAt(0);
//得到行
Row row = sheetAt.getRow(0);
//得到列
Cell cell = row.getCell(1);
System.out.println(cell.getNumericCellValue());
inputStream.close();
}
07版本
@Test
public void Read07Test() throws IOException {
FileInputStream inputStream = new FileInputStream(PATH + "poi_writebigData07.xlsx");
Workbook workbook = new XSSFWorkbook(inputStream);
//得到表
Sheet sheetAt = workbook.getSheetAt(0);
//得到行
Row row = sheetAt.getRow(0);
//得到列
Cell cell = row.getCell(1);
System.out.println(cell.getNumericCellValue());
inputStream.close();
}
注意：擷取資料類型
讀取不同的資料類型
@Test
public void test3() throws IOException {
//擷取檔案流
FileInputStream inputStream = new FileInputStream("D:\\搜狗高速下載下傳\\商品資訊.xlsx");
//建立一個工作簿
Workbook workbook = new XSSFWorkbook(inputStream);
Sheet sheet = workbook.getSheetAt(0);
//擷取标題内容
Row rowTitle = sheet.getRow(0);
if (rowTitle != null){
//有多少列資訊
int cellCount = rowTitle.getPhysicalNumberOfCells();
for (int cellNum = 0; cellNum < cellCount; cellNum++) {
//擷取第一行的每一列的資訊
Cell cell = rowTitle.getCell(cellNum);
if (cell != null){
int cellType = cell.getCellType();
String stringCellValue = cell.getStringCellValue();
System.out.print(stringCellValue+" | ");
}
}
System.out.println();
}
//擷取表中的内容
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);
//比對列的資料類型
if (cell != null){
int cellType = cell.getCellType();
String cellValue="";
switch (cellType){
case XSSFCell.CELL_TYPE_STRING://字元串
System.out.println("[String]");
cellValue = cell.getStringCellValue();
break;
case XSSFCell.CELL_TYPE_BOOLEAN://布爾
System.out.println("[Boolean]");
boolean booleanCellValue = cell.getBooleanCellValue();
cellValue = String.valueOf(booleanCellValue);
break;
case XSSFCell.CELL_TYPE_BLANK://空
System.out.println("[BLANK]");
break;
case XSSFCell.CELL_TYPE_NUMERIC://數字
System.out.println("[NUMERIC]");
if (HSSFDateUtil.isCellDateFormatted(cell)){//日期
System.out.println("日期");
Date dateCellValue = cell.getDateCellValue();
cellValue = new DateTime(dateCellValue).toString("yyyy-MM-dd hh:mm:ss");
}else {
//不是日期格式，防止數字過長
System.out.println("轉化為字元串輸出");
cell.setCellType(XSSFCell.CELL_TYPE_STRING);
cellValue = cell.toString();
// double numericCellValue = cell.getNumericCellValue();
// cellValue = String.valueOf(numericCellValue);
}
break;
case XSSFCell.CELL_TYPE_ERROR://錯誤
System.out.println("[資料類型錯誤]");
break;
}
System.out.println(cellValue);
}
}
}
}
inputStream.close();
}
計算公式
@Test
public void test4() throws IOException {
FileInputStream inputStream = new FileInputStream("D:\\C槽的應用\\建立 Microsoft Excel 工作表.xlsx");
Workbook workbook = new XSSFWorkbook(inputStream);
Sheet sheet = workbook.getSheetAt(0);
Row row = sheet.getRow(5);
Cell cell = row.getCell(0);
//取出計算公式 eval
XSSFFormulaEvaluator formulaEvaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
//輸出單元格的内容
int cellType = cell.getCellType();
switch (cellType){
case Cell.CELL_TYPE_FORMULA://公式
String formula = cell.getCellFormula();
System.out.println(formula);
//計算
CellValue evaluate = formulaEvaluator.evaluate(cell);
String value = evaluate.formatAsString();
System.out.println(value);
}
inputStream.close();
}
EasyExcel操作
導入依賴
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.10</version>
</dependency>
寫入測試
/**
* 最簡單的寫
* <p>1. 建立excel對應的實體對象 參照{@link DemoData}
*/
@Test
public void simpleWrite() {
// 寫法1
String fileName = PATH+"WriteTest.xlsx";
// 這裡 需要指定寫用哪個class去寫，然後寫到第一個sheet，名字為模闆 然後檔案流會自動關閉
//write(fileName,格式類)
//sheet(表名)
//doWriter（資料）
EasyExcel.write(fileName, DemoData.class).sheet("模闆").doWrite(data());
}
讀取測試
/**
* 最簡單的讀
* <p>1. 建立excel對應的實體對象 參照{@link DemoData}
* <p>2. 由于預設一行行的讀取excel，是以需要建立excel一行一行的回調監聽器，參照{@link DemoDataListener}
* <p>3. 直接讀即可
*/
@Test
public void simpleRead() {
// 有個很重要的點 DemoDataListener 不能被spring管理，要每次讀取excel都要new,然後裡面用到spring可以構造方法傳進去
// 寫法1：
String fileName = PATH+"WriteTest.xlsx";
// 這裡 需要指定讀用哪個class去讀，然後讀取第一個sheet 檔案流會自動關閉
EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead();
}
固定套路：
1、寫入，固定類格式寫入
2、讀取，根據監聽器設定的規則進行讀取！
![Java小案例——随機數猜測随機數猜測[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)