From 9020811b517b3a417231e0558d740804ca6fb5b2 Mon Sep 17 00:00:00 2001
From: fwq <582742538@qq.com>
Date: 星期一, 23 十月 2023 19:55:00 +0800
Subject: [PATCH] 解析Excel问题
---
src/main/java/com/hx/util/ExcelUtil.java | 72 ++++++++++++++++++++++-------------
1 files changed, 45 insertions(+), 27 deletions(-)
diff --git a/src/main/java/com/hx/util/ExcelUtil.java b/src/main/java/com/hx/util/ExcelUtil.java
index 8418213..cb92bab 100644
--- a/src/main/java/com/hx/util/ExcelUtil.java
+++ b/src/main/java/com/hx/util/ExcelUtil.java
@@ -1,9 +1,9 @@
package com.hx.util;
-import com.alibaba.fastjson.JSON;
import com.hx.exception.TipsException;
import com.monitorjbl.xlsx.StreamingReader;
import org.apache.poi.hssf.usermodel.*;
+import org.apache.poi.openxml4j.exceptions.OLE2NotOfficeXmlFileException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
@@ -885,7 +885,10 @@
.bufferSize(10240)
.open(new FileInputStream(file));
listData =readhandle(book);
- }catch (Exception e){
+ }catch (OLE2NotOfficeXmlFileException ex){
+ ex.printStackTrace();
+ throw new RuntimeException("excel鐗堟湰涓嶄负2007鍙婁互涓�");
+ } catch (Exception e){
e.printStackTrace();
throw new RuntimeException(e.getMessage());
}
@@ -913,7 +916,10 @@
.open(new BufferedInputStream(file.getInputStream()));
listData =readhandle(book);
- }catch (Exception e){
+ }catch (OLE2NotOfficeXmlFileException ex){
+ ex.printStackTrace();
+ throw new RuntimeException("excel鐗堟湰涓嶄负2007鍙婁互涓�");
+ } catch (Exception e){
e.printStackTrace();
throw new RuntimeException(e.getMessage());
}
@@ -956,43 +962,55 @@
Date d;
DateFormat formater;
Sheet sheet;
+ Cell cell;
//閬嶅巻鎵�鏈夌殑sheet
for(int i=0;i<book.getNumberOfSheets();i++) {
sheet = book.getSheetAt(i);
+ //鍒楁暟
+ Integer arrange = null;
//閬嶅巻鎵�鏈夌殑琛�
for (Row row : sheet) {
+ if(row == null){
+ continue;
+ }
isData = false;
arrayString = new ArrayList<>();
+ if(arrange == null){
+ arrange = row.getPhysicalNumberOfCells();
+ }
//閬嶅巻鎵�鏈夌殑鍒�
- for (Cell cell : row) {
+ for (int j = 0;j<arrange;j++) {
+ cell = row.getCell(j);
if (cell == null) {
arrayString.add("");
- } else if (cell.getCellType() == CellType.NUMERIC) {
- isData = true;
- format = cell.getCellStyle().getDataFormat();
- if (format == 14 || format == 31 || format == 57 || format == 58) {
- //鏃ユ湡锛堜腑鏂囨椂闂存牸寮忕殑锛�
- d = cell.getDateCellValue();
- formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
- arrayString.add(formater.format(d));
- } else if (HSSFDateUtil.isCellDateFormatted(cell)) {
- d = cell.getDateCellValue();
- formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
- arrayString.add(formater.format(d));
- } else {
- if (CellType.STRING == cell.getCellType()) {
- arrayString.add(cell.getStringCellValue());
- } else if (CellType.FORMULA == cell.getCellType()) {
- arrayString.add(cell.getCellFormula());
+ }else{
+ if (cell.getCellType() == CellType.NUMERIC) {
+ isData = true;
+ format = cell.getCellStyle().getDataFormat();
+ if (format == 14 || format == 31 || format == 57 || format == 58) {
+ //鏃ユ湡锛堜腑鏂囨椂闂存牸寮忕殑锛�
+ d = cell.getDateCellValue();
+ formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
+ arrayString.add(formater.format(d));
+ } else if (HSSFDateUtil.isCellDateFormatted(cell)) {
+ d = cell.getDateCellValue();
+ formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
+ arrayString.add(formater.format(d));
} else {
- arrayString.add(cell.getStringCellValue().trim());
+ if (CellType.STRING == cell.getCellType()) {
+ arrayString.add(cell.getStringCellValue());
+ } else if (CellType.FORMULA == cell.getCellType()) {
+ arrayString.add(cell.getCellFormula());
+ } else {
+ arrayString.add(cell.getStringCellValue().trim());
+ }
}
+ } else if (cell.getCellType() == CellType.BLANK) {
+ arrayString.add("");
+ } else { // 濡傛灉EXCEL琛ㄦ牸涓殑鏁版嵁绫诲瀷涓哄瓧绗︿覆鍨�
+ isData = true;
+ arrayString.add(cell.getStringCellValue().trim());
}
- } else if (cell.getCellType() == CellType.BLANK) {
- arrayString.add("");
- } else { // 濡傛灉EXCEL琛ㄦ牸涓殑鏁版嵁绫诲瀷涓哄瓧绗︿覆鍨�
- isData = true;
- arrayString.add(cell.getStringCellValue().trim());
}
}
if (isData) {
--
Gitblit v1.8.0