Java操作Excel的示例詳解
更新時間：2023年01月10日 10:11:49 作者：糊涂涂是個小盆友
在平時可以使用IO流對Excle進行操作，但是現(xiàn)在使用更加方便的第三方組件來實現(xiàn)。本文就來和大家聊聊Java如何通過第三方組件實現(xiàn)操作Excel，需要的可以參考一下
java操作Excel數(shù)據(jù)
在 平時 可以使用IO流對Excle進行操作
但是現(xiàn)在使用更加方便的第三方組件來實現(xiàn)
使用場景
1、將用戶信息導出為Excel表格，導入數(shù)據(jù)
2、將Excel表中的數(shù)據(jù)錄入到網(wǎng)站數(shù)據(jù)庫 （習題上傳） 減輕網(wǎng)站的錄入量
3、開發(fā)中經(jīng)常會設計到Excel的處理，導入Excel到數(shù)據(jù)庫中
目前最流行的是 Apache POI以及阿里巴巴easyExcel
excel 03 和 07的區(qū)別
HSSF 對應excel中的03版本 該版本要求excel中最多只能寫65536行
后綴名為 03.xls
XSSF 對應excel中的07版本 該版本對于行數(shù)沒有要求
后綴名為 07.xlsx
POI
Apache提供的，會比較麻煩，比較原生
開放源碼函式庫，POI提供API給java程序對Office格式檔案讀和寫的功能
但是存在內(nèi)存問題 => POI將數(shù)據(jù)會先寫入內(nèi)存中，一旦寫入的內(nèi)容過多時會產(chǎn)生OOM，也叫做內(nèi)存溢出
easyExcel
https://github.com/alibaba/easyexcel
對POI進行了一些優(yōu)化，可以使開發(fā)者更加簡單，讀和寫代碼只需要1行
存在時間的問題 => easyExcel在寫數(shù)據(jù)時是一行一行往磁盤中寫，所以解決了POI的內(nèi)存問題，但是帶來了時間問題
解析excel表中的對象
由于java中萬物皆對象，所以需要先觀察一張excel表中有哪些對象~
1、工作簿
2、工作表
3、行
4、列 => 單元格
POI使用步驟
第一步：創(chuàng)建Maven項目
第二步：導入依賴
<dependencies>
<!-- 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>
<scope>test</scope>
</dependency>
</dependencies>
POI 寫數(shù)據(jù)
第一步：基本文件的寫入
private String PATH = "E:\\JavaCode\\Maven\\excel-demo\\src";
03版本測試
@Test
public void test03() throws Exception {
// 1 創(chuàng)建工作簿
Workbook workbook = new HSSFWorkbook();
// 2 創(chuàng)建工作表
Sheet sheet1 = workbook.createSheet("表1");
// 3 創(chuàng)建行 下標從0開始 第一行
Row row1 = sheet1.createRow(0);
// 4 創(chuàng)建單元格 (1,1)
Cell cell1 = row1.createCell(0);
// 5 往第一個單元格填入數(shù)據(jù)
cell1.setCellValue("今日新加入");
// 6 創(chuàng)建第二個單元格(1,2)
Cell cell2 = row1.createCell(1);
cell2.setCellValue("統(tǒng)計時間");
// 創(chuàng)建第二行
Row row2 = sheet1.createRow(1);
// (2,1)
Cell cell3 = row2.createCell(0);
cell3.setCellValue("11000");
// (2,2)
Cell cell4 = row2.createCell(1);
cell4.setCellValue(new DateTime().toString("yyyy-MM-dd hh:mm:ss"));
// 生成表的IO流 03
FileOutputStream fos = new FileOutputStream(PATH + "03版本excel.xls");
// 將工作簿寫入
workbook.write(fos);
// 釋放流
fos.close();
System.out.println("創(chuàng)建成功");
}07版本測試
@Test
public void test07() throws Exception {
// 1 創(chuàng)建工作簿
Workbook workbook = new SXSSFWorkbook(); //todo
// 2 創(chuàng)建工作表
Sheet sheet1 = workbook.createSheet("表1");
// 3 創(chuàng)建行 下標從0開始 第一行
Row row1 = sheet1.createRow(0);
// 4 創(chuàng)建單元格 (1,1)
Cell cell1 = row1.createCell(0);
// 5 往第一個單元格填入數(shù)據(jù)
cell1.setCellValue("今日新加入");
// 6 創(chuàng)建第二個單元格(1,2)
Cell cell2 = row1.createCell(1);
cell2.setCellValue("統(tǒng)計時間");
// 創(chuàng)建第二行
Row row2 = sheet1.createRow(1);
// (2,1)
Cell cell3 = row2.createCell(0);
cell3.setCellValue("11000");
// (2,2)
Cell cell4 = row2.createCell(1);
cell4.setCellValue(new DateTime().toString("yyyy-MM-dd hh:mm:ss"));
// 生成表的IO流 03
FileOutputStream fos = new FileOutputStream(PATH + "07版本excel.xlsx");
// 將工作簿寫入
workbook.write(fos);
// 釋放流
fos.close();
System.out.println("創(chuàng)建成功");
}
第二步：大數(shù)據(jù)寫入
HSSF 寫入
缺點：最多只能處理65536行數(shù)據(jù)，否則會拋出異常 java.lang.IllegalArgumentException
優(yōu)點：過程中寫入緩存，不操作磁盤，最后一次性寫入磁盤，速度快
// 03 版本 多數(shù)據(jù)寫入
@Test
public void test03BigData() throws Exception {
long start = System.currentTimeMillis();
Workbook workbook = new HSSFWorkbook();
Sheet s1 = workbook.createSheet("表1");
for (int row = 0; row < 65536; row++) {
Row rows = s1.createRow(row);
for (int cell = 0; cell < 10; cell++) {
Cell cells = rows.createCell(cell);
cells.setCellValue(cell);
}
}
FileOutputStream fos = new FileOutputStream(PATH + "03BigData.xls");
workbook.write(fos);
fos.close();
long end = System.currentTimeMillis();
System.out.println("總耗時:" + (double) (end - start) / 1000 + "秒");
}
XSSF 寫入
缺點：寫數(shù)據(jù)使速度非常慢，非常耗費內(nèi)存，也會發(fā)生內(nèi)存溢出 ，如寫100萬條數(shù)據(jù)
優(yōu)點：可以寫比HSSF大的數(shù)據(jù)量，如20萬條數(shù)據(jù)
// 07 版本 低性能 XSSF
@Test
public void test07BigData() throws Exception {
long start = System.currentTimeMillis();
Workbook workbook = new XSSFWorkbook();
Sheet s1 = workbook.createSheet("表1");
for (int row = 0; row < 65536; row++) {
Row rows = s1.createRow(row);
for (int cell = 0; cell < 10; cell++) {
Cell cells = rows.createCell(cell);
cells.setCellValue(cell);
}
}
FileOutputStream fos = new FileOutputStream(PATH + "07BigData-XSSF.xlsx");
workbook.write(fos);
fos.close();
long end = System.currentTimeMillis();
System.out.println("總耗時:" + (double) (end - start) / 1000 + "秒");
}
SXSSF 寫入
優(yōu)點：可以寫非常大的數(shù)據(jù)量，如100萬條，寫的速度也非?？欤加酶俚膬?nèi)存
SXSSFWorkbook-來至官方的解釋∶實現(xiàn)"BigGridDemo"策略的流式XSSFWorkbook版本。這允許寫入非常大的文件而不會耗盡內(nèi)存，因為任何時候只有可配置的行部分被保存在內(nèi)存中。
請注意，仍然可能會消耗大量內(nèi)存，這些內(nèi)存基于您正在使用的功能，例如合并區(qū)域，注.…….然只存儲在內(nèi)存中，因此如果廣泛使用，可能需要大量內(nèi)存。 可以使用jprofile來監(jiān)控
注意
過程中會產(chǎn)生臨時文件，需要清理臨時文件
默認由100條記錄被保存到內(nèi)存中，如果超過這個數(shù)量，則最前面的數(shù)據(jù)就被寫入臨時文件，如果想自定義內(nèi)存中數(shù)據(jù)的數(shù)量，可以使用 new SXSSFWorkbook（數(shù)量）
// 07 版本 高性能 SXSS 性能優(yōu)化
@Test
public void test07BigData2() throws Exception {
long start = System.currentTimeMillis();
Workbook workbook = new SXSSFWorkbook();
Sheet s1 = workbook.createSheet("表1");
for (int row = 0; row < 65536; row++) {
Row rows = s1.createRow(row);
for (int cell = 0; cell < 10; cell++) {
Cell cells = rows.createCell(cell);
cells.setCellValue(cell);
}
}
FileOutputStream fos = new FileOutputStream(PATH + "07BigData-SXSS.xlsx");
workbook.write(fos);
fos.close();
// todo 清除臨時文件 需要強轉類型
((SXSSFWorkbook) workbook).dispose();
long end = System.currentTimeMillis();
System.out.println("總耗時:" + (double) (end - start) / 1000 + "秒");
}
POI 讀數(shù)據(jù)
在讀取excel表格中單元格中的數(shù)據(jù)的時候，需要注意一點的是:單元格中的數(shù)據(jù)可以有String類型、Number類型、Date類型等，所以需要通過Switch-case來進行判斷獲取，否則會報錯
private String PATH = "E:\\JavaCode\\Maven\\excel-demo\\src";
不用清除臨時文件
HSSF 讀數(shù)據(jù)
@Test
public void Read03() throws Exception {
// 0、得到文件輸入流
FileInputStream fis = new FileInputStream(PATH + "03版本excel.xls");
// 1、工作簿
Workbook workbook = new HSSFWorkbook(fis);
// 2.得到表 可以根據(jù)索引也可以根據(jù)表的名稱
Sheet sheet = workbook.getSheetAt(0);
// 3、得到行
Row row = sheet.getRow(1);
// 4、得到列 鎖定單元格
Cell cell = row.getCell(0);
// 5、根據(jù)類型得到單元格中的內(nèi)容
String value = cell.getStringCellValue();
System.out.println(value);
}XSSF 讀數(shù)據(jù)
@Test
public void Read07() throws Exception {
// 0、得到文件輸入流
FileInputStream fis = new FileInputStream(PATH + "07版本excel.xls");
// 1、工作簿
Workbook workbook = new XSSFWorkbook(fis);
// 2.得到表 可以根據(jù)索引也可以根據(jù)表的名稱
Sheet sheet = workbook.getSheetAt(0);
// 3、得到行
Row row = sheet.getRow(1);
// 4、得到列 鎖定單元格
Cell cell = row.getCell(0);
// 5、根據(jù)類型得到單元格中的內(nèi)容
String value = cell.getStringCellValue();
System.out.println(value);
}循環(huán)讀取多個不同類型的數(shù)據(jù)
// 讀取多個
@Test
public void Read03teset02() throws Exception {
String path = PATH + "人員.xlsx";
getData(path);
}
public static void getData(String path){
// 0、得到文件輸入流
FileInputStream fis = null;
try {
fis = new FileInputStream(path);
Workbook workbook = new XSSFWorkbook(fis);
Sheet sheet = workbook.getSheetAt(0);
// 1.拿到第一行所有為String類型的數(shù)據(jù)
Row row = sheet.getRow(0);
if (row != null) {
int cellCount = row.getPhysicalNumberOfCells(); // 該行總共的單元格數(shù)
for (int cellNum = 0; cellNum < cellCount; cellNum++) {
Cell cell = row.getCell(cellNum);
if (cell != null) {
String cellTitle = cell.getStringCellValue();
System.out.print(cellTitle + "|");
}
}
System.out.println("");
}
// 2.拿到剩下行數(shù)中的數(shù)據(jù)
int rowCount = sheet.getPhysicalNumberOfRows(); // 總行數(shù)
for (int rowNum = 1; rowNum < rowCount; rowNum++) {
Row rowData = sheet.getRow(rowNum);
int cellCount = rowData.getPhysicalNumberOfCells(); //總單元格數(shù)
String cellValue = "";
for (int cellNum = 0; cellNum < cellCount; cellNum++) {
Cell cell = rowData.getCell(cellNum);
if (cell != null) {
// 判斷類型
int cellType = cell.getCellType();
switch (cellType) {
case Cell.CELL_TYPE_STRING: // todo 字符串
cellValue = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_NUMERIC: // todo 數(shù)值 || 日期
if (HSSFDateUtil.isCellDateFormatted(cell)){
// 日期
Date date= cell.getDateCellValue();
cellValue = new DateTime().toString("yyyy-MM-dd hh:mm:ss");
}else{
// 數(shù)字 防止數(shù)字過長
cell.setCellType(XSSFCell.CELL_TYPE_STRING);
cellValue = cell.toString();
}
break;
case Cell.CELL_TYPE_BOOLEAN: // todo 布爾
boolean boolean_value = cell.getBooleanCellValue();
cellValue = String.valueOf(boolean_value);
break;
case Cell.CELL_TYPE_BLANK: // todo 為空
System.out.print("[BLANK]");
break;
case Cell.CELL_TYPE_ERROR: // todo 數(shù)據(jù)類型錯誤
System.out.print("[數(shù)據(jù)類型錯誤]");
break;
}
System.out.print(cellValue+"|");
}
}
System.out.println("");
}
} catch (IOException e) {
e.printStackTrace();
}finally {
// 3 釋放資源
try {
fis.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}計算公式
在excel表格中存在著很多個公式 如：sum、排序、求平均值等。這個時候就需要進行判斷。了解即可，需要時可以再看
@Test
public void test1() throws Exception {
FileInputStream fis = new FileInputStream("E:\\JavaCode\\Maven\\excel-demo\\公式.xls");
Workbook workbook = new HSSFWorkbook(fis);
Sheet sheet = workbook.getSheetAt(0);
Row row = sheet.getRow(2);
Cell cell = row.getCell(0);
// 得到表中的計算公式
FormulaEvaluator FormulaEvaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);
// 得到單元格的內(nèi)容
int cellType = cell.getCellType();
switch (cellType){
case Cell.CELL_TYPE_FORMULA: // 公式
// 拿到公式
String cellFormula = cell.getCellFormula();
System.out.println(cellFormula);
//計算
CellValue evaluate = FormulaEvaluator.evaluate(cell);
System.out.println("evaluate---"+evaluate); // org.apache.poi.ss.usermodel.CellValue [700.0]
String value = evaluate.formatAsString(); // 將數(shù)據(jù)格式化為字符串
System.out.println(value);
break;
}
}
easyExcel讀寫數(shù)據(jù)
根據(jù)實體類自動生成表
第一步：導入依賴
該依賴中自帶了很多種依賴，如lombok、spring-boot等，需要我們在引入依賴時將自己已經(jīng)導入的依賴刪除，不然會報依賴沖突的錯誤
<dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>3.1.4</version> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.18.8</version> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>fastjson</artifactId> <version>1.2.75</version> </dependency>
寫數(shù)據(jù)
第二步：創(chuàng)建excel表對應的實體類
@Data
public class DemoData {
@ExcelProperty("字符串標題")
private String string;
@ExcelProperty("日期標題")
private Date date;
@ExcelProperty("數(shù)字標題")
private Double aDouble;
// 忽略該字段
@ExcelIgnore
private String ignore;
}第三步：編寫設置數(shù)據(jù)的方法
使用集合list來寫入excel
public List easyTest1(){
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.setADouble(0.12);
list.add(data);
}
return list;
}第四步：編寫測試類
使用鏈式編寫的方式
write(文件路徑，excel表對應的java類)
sheet(設置表名)
doWrite(數(shù)據(jù))
@Test
public void test1(){
String fileName ="E:\\JavaCode\\Maven\\excel-demo\\easyEasyData.xlsx";
EasyExcel.write(fileName,DemoData.class).sheet("表1").doWrite(data());
}讀數(shù)據(jù)
1、每執(zhí)行一條excel表中的數(shù)據(jù)都會執(zhí)行一次監(jiān)聽文件中的invoke方法，所以如果需要修改可以修改invoke方法中的內(nèi)容
2、DemoDataListener 不能被spring管理，要每次讀取excel都要new,然后里面用到spring可以構造方法傳進去
第二步：準備一個對應excel表中字段的類
與寫操作中使用同一個類
@Data
public class DemoData {
@ExcelProperty("字符串標題")
private String string;
@ExcelProperty("日期標題")
private Date date;
@ExcelProperty("數(shù)字標題")
private Double aDouble;
// 忽略該字段
@ExcelIgnore
private String ignore;
}第三步：創(chuàng)建數(shù)據(jù)層 Mapper || Dao
public class DemoDAO {
public void save(List<DemoData> list) {
// 如果是mybatis,盡量別直接調(diào)用多次insert,自己寫一個mapper里面新增一個方法batchInsert,所有數(shù)據(jù)一次性插入
}
}第四步：創(chuàng)建監(jiān)聽器
package excel.readEasy;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.metadata.CellExtra;
import com.alibaba.excel.metadata.data.ReadCellData;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.util.ListUtils;
import com.alibaba.fastjson.JSON;
import excel.easy.DemoData;
import lombok.extern.slf4j.Slf4j;
import java.util.List;
import java.util.Map;
// 有個很重要的點 DemoDataListener 不能被spring管理，要每次讀取excel都要new,然后里面用到spring可以構造方法傳進去
@Slf4j
public class DemoDataListener implements ReadListener<DemoData> {
//每隔5條存儲數(shù)據(jù)庫，實際使用中可以100條，然后清理list ，方便內(nèi)存回收
private static final int BATCH_COUNT = 100;
//緩存的數(shù)據(jù)
private List<DemoData> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
//假設這個是一個DAO，當然有業(yè)務邏輯這個也可以是一個service。當然如果不用存儲這個對象沒用
private DemoDAO demoDAO;
// 這里是demo，所以隨便new一個。實際使用如果到了spring,請使用下面的有參構造函數(shù)
public DemoDataListener() {
demoDAO = new DemoDAO();
}
//如果使用了spring,請使用這個構造方法。每次創(chuàng)建Listener的時候需要把spring管理的類傳進來
public DemoDataListener(DemoDAO demoDAO) {
this.demoDAO = demoDAO;
}
@Override
public void onException(Exception exception, AnalysisContext context) throws Exception {
}
@Override
public void invokeHead(Map<Integer, ReadCellData<?>> headMap, AnalysisContext context) {
System.out.println("111");
}
//這個每一條數(shù)據(jù)解析都會來調(diào)用
@Override
public void invoke(DemoData data, AnalysisContext analysisContext) {
System.out.println("2222");
System.out.println(JSON.toJSONString(data));
// log.info("解析到一條數(shù)據(jù):{}", JSON.toJSONString(data));
cachedDataList.add(data);
// 達到BATCH_COUNT了，需要去存儲一次數(shù)據(jù)庫，防止數(shù)據(jù)幾萬條數(shù)據(jù)在內(nèi)存，容易OOM
if (cachedDataList.size() >= BATCH_COUNT) {
saveData();
// 存儲完成清理 list
cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
}
}
@Override
public void extra(CellExtra extra, AnalysisContext context) {
}
//所有數(shù)據(jù)解析完成了 都會來調(diào)用
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
saveData();
log.info("所有數(shù)據(jù)解析完成！");
}
@Override
public boolean hasNext(AnalysisContext context) {
return false;
}
/**
* 加上存儲數(shù)據(jù)庫
*/
private void saveData() {
log.info("{}條數(shù)據(jù)，開始存儲數(shù)據(jù)庫！", cachedDataList.size());
demoDAO.save(cachedDataList);
log.info("存儲數(shù)據(jù)庫成功！");
}
}第五步：測試
@Test
public void test3() {
String fileName = "E:\\JavaCode\\Maven\\excel-demo\\easyEasyData.xlsx";
EasyExcel.read(fileName, DemoData.class, new PageReadListener<DemoData>(dataList -> {
for (DemoData demoData : dataList) {
System.out.println(JSON.toJSONString(demoData));
}
})).sheet().doRead();
}以上就是Java操作Excel的示例詳解的詳細內(nèi)容，更多關于Java操作Excel的資料請關注腳本之家其它相關文章！
相關文章
利用java監(jiān)聽器實現(xiàn)在線人數(shù)統(tǒng)計
過去使用ASP和ASP.NET兩種編程的時候，都寫過在線人數(shù)統(tǒng)計能，實現(xiàn)功能挺簡單的！今天使用java來實現(xiàn)在線人數(shù)統(tǒng)計有點另類，是通過Java監(jiān)聽器實現(xiàn)的，需要的朋友可以參考下2015-09-09
java多線程join()方法的作用和實現(xiàn)原理解析(應用場景)
join方法主要是用于將當前線程掛起，等待其他線程結束后在執(zhí)行當前線程，本文通過應用場景分析代碼示例講解java多線程join()方法的作用和實現(xiàn)原理，感興趣的朋友一起看看吧2021-07-07
這篇文章主要介紹了Java中的LinkedHashMap詳解,LinkedHashMap繼承自HashMap,它的多種操作都是建立在HashMap操作的基礎上的,同HashMap不同的是,LinkedHashMap維護了一個Entry的雙向鏈表,保證了插入的Entry中的順序,需要的朋友可以參考下2023-09-09
Java求兩個正整數(shù)的最大公約數(shù)和最小公倍數(shù)
這篇文章主要介紹了輸入兩個正整數(shù)m和n，求其最大公約數(shù)和最小公倍數(shù),需要的朋友可以參考下2017-02-02
在Spring框架中,.properties 文件通常用于存儲配置信息,如數(shù)據(jù)庫連接、服務地址、應用參數(shù)等,本文給大家介紹了Spring配置和使用Properties文件的詳細步驟,需要的朋友可以參考下2024-05-05
cos是O'Rrilly公司開發(fā)的一款用于HTTP上傳文件的OpenSource組件。下面通過本文給大家分享使用COS實現(xiàn)文件上傳功能，感興趣的朋友一起看看吧2017-08-08
JDBC+GUI實現(xiàn)簡單學生管理系統(tǒng)
這篇文章主要為大家詳細介紹了JDBC+GUI實現(xiàn)簡單學生管理系統(tǒng)，具有一定的參考價值，感興趣的小伙伴們可以參考一下2019-02-02
Springboot中攔截GET請求獲取請求參數(shù)驗證合法性核心方法
這篇文章主要介紹了Springboot中攔截GET請求獲取請求參數(shù)驗證合法性,在Springboot中創(chuàng)建攔截器攔截所有GET類型請求，獲取請求參數(shù)驗證內(nèi)容合法性防止SQL注入，這種方法適用攔截get類型請求，需要的朋友可以參考下2023-08-08
詳解spring security 配置多個AuthenticationProvider
這篇文章主要介紹了詳解spring security 配置多個AuthenticationProvider ,小編覺得挺不錯的，現(xiàn)在分享給大家，也給大家做個參考。一起跟隨小編過來看看吧2017-05-05
這篇文章主要介紹了java 歸并排序的實例詳解的相關資料,需要的朋友可以參考下2017-07-07