From 2e06d10f39d8bbdeae8a8da99f3d94ed027df24c Mon Sep 17 00:00:00 2001
From: chenjiahe <763432473@qq.com>
Date: 星期五, 01 九月 2023 17:14:41 +0800
Subject: [PATCH] 新版excel
---
src/main/java/com/hx/util/ExcelUtil.java | 367 +++++++++++++++++++++++++++++++++++++++++----------
src/main/java/com/hx/util/SimpleTool.java | 31 ++--
pom.xml | 16 +-
3 files changed, 318 insertions(+), 96 deletions(-)
diff --git a/pom.xml b/pom.xml
index 8352058..0b64cd8 100644
--- a/pom.xml
+++ b/pom.xml
@@ -21,7 +21,7 @@
<artifactId>jaxb-api</artifactId>
</dependency>
- <dependency>
+ <!-- <dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
@@ -31,7 +31,7 @@
<groupId>dom4j</groupId>
</exclusion>
</exclusions>
- </dependency>
+ </dependency>-->
<!--鑵捐浜� cos-->
<dependency>
@@ -117,11 +117,6 @@
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
- </dependency>
- <!-- excel2003浣跨敤鐨刯ar -->
- <dependency>
- <groupId>org.apache.poi</groupId>
- <artifactId>poi</artifactId>
</dependency>
<dependency>
<groupId>redis.clients</groupId>
@@ -295,6 +290,13 @@
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-redis</artifactId>
</dependency>
+
+ <!-- https://mvnrepository.com/artifact/com.monitorjbl/xlsx-streamer -->
+ <dependency>
+ <groupId>com.monitorjbl</groupId>
+ <artifactId>xlsx-streamer</artifactId>
+ </dependency>
+
</dependencies>
<build>
diff --git a/src/main/java/com/hx/util/ExcelUtil.java b/src/main/java/com/hx/util/ExcelUtil.java
index 252ecf4..07abd29 100644
--- a/src/main/java/com/hx/util/ExcelUtil.java
+++ b/src/main/java/com/hx/util/ExcelUtil.java
@@ -1,8 +1,9 @@
package com.hx.util;
import com.hx.exception.TipsException;
-import org.apache.poi.POIXMLDocument;
+import com.monitorjbl.xlsx.StreamingReader;
import org.apache.poi.hssf.usermodel.*;
+import org.apache.poi.ooxml.POIXMLDocument;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
@@ -61,7 +62,7 @@
// 鍦ㄧ储寮�0鐨勪綅缃垱寤哄崟鍏冩牸锛堝乏涓婄锛�
cell = row.createCell(i);
// 瀹氫箟鍗曞厓鏍间负瀛楃涓茬被鍨�
- cell.setCellType(HSSFCell.CELL_TYPE_STRING);
+ //cell.setCellType(HSSFCell.CELL_TYPE_STRING);
// 鍦ㄥ崟鍏冩牸涓緭鍏ヤ竴浜涘唴瀹�
cell.setCellValue(headList[i]);
}
@@ -78,7 +79,7 @@
// 鍦ㄧ储寮�0鐨勪綅缃垱寤哄崟鍏冩牸锛堝乏涓婄锛�
cell = row_value.createCell(i);
// 瀹氫箟鍗曞厓鏍间负瀛楃涓茬被鍨�
- cell.setCellType(HSSFCell.CELL_TYPE_STRING);
+ //cell.setCellType(HSSFCell.CELL_TYPE_STRING);
// 鍦ㄥ崟鍏冩牸涓緭鍏ヤ竴浜涘唴瀹�
cell.setCellValue(objToString(dataMap.get(fieldList[i])));
}
@@ -131,7 +132,7 @@
// 鍦ㄧ储寮�0鐨勪綅缃垱寤哄崟鍏冩牸锛堝乏涓婄锛�
cell = row.createCell(i);
// 瀹氫箟鍗曞厓鏍间负瀛楃涓茬被鍨�
- cell.setCellType(HSSFCell.CELL_TYPE_STRING);
+ //cell.setCellType(HSSFCell.CELL_TYPE_STRING);
// 鍦ㄥ崟鍏冩牸涓緭鍏ヤ竴浜涘唴瀹�
cell.setCellValue(headList[i]);
}
@@ -148,7 +149,7 @@
// 鍦ㄧ储寮�0鐨勪綅缃垱寤哄崟鍏冩牸锛堝乏涓婄锛�
cell = row_value.createCell(i);
// 瀹氫箟鍗曞厓鏍间负瀛楃涓茬被鍨�
- cell.setCellType(HSSFCell.CELL_TYPE_STRING);
+ //cell.setCellType(HSSFCell.CELL_TYPE_STRING);
// 鍦ㄥ崟鍏冩牸涓緭鍏ヤ竴浜涘唴瀹�
cell.setCellValue(objToString(dataMap.get(fieldList[i])));
}
@@ -197,8 +198,8 @@
//鍚堝苟鐨勫崟鍏冩牸鏍峰紡
HSSFCellStyle boderStyle = workbook.createCellStyle();
//鍨傜洿灞呬腑
- boderStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
- boderStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 鍒涘缓涓�涓眳涓牸寮�
+ boderStyle.setVerticalAlignment(VerticalAlignment.CENTER);
+ boderStyle.setAlignment(HorizontalAlignment.CENTER); // 鍒涘缓涓�涓眳涓牸寮�
// 鍦‥xcel宸ヤ綔绨夸腑寤轰竴宸ヤ綔琛紝鍏跺悕涓虹己鐪佸��
// 濡傝鏂板缓涓�鍚嶄负"鏁堢泭鎸囨爣"鐨勫伐浣滆〃锛屽叾璇彞涓猴細
@@ -215,7 +216,7 @@
// 鍦ㄧ储寮�0鐨勪綅缃垱寤哄崟鍏冩牸锛堝乏涓婄锛�
cell = row.createCell(i);
// 瀹氫箟鍗曞厓鏍间负瀛楃涓茬被鍨�
- cell.setCellType(HSSFCell.CELL_TYPE_STRING);
+ //cell.setCellType(HSSFCell.CELL_TYPE_STRING);
// 鍦ㄥ崟鍏冩牸涓緭鍏ヤ竴浜涘唴瀹�
cell.setCellValue(headList[i]);
cell.setCellStyle(boderStyle);
@@ -235,7 +236,7 @@
sheet.setColumnWidth(i,width);
cell = row_value.createCell(i);
// 瀹氫箟鍗曞厓鏍间负瀛楃涓茬被鍨�
- cell.setCellType(HSSFCell.CELL_TYPE_STRING);
+ //cell.setCellType(HSSFCell.CELL_TYPE_STRING);
// 鍦ㄥ崟鍏冩牸涓緭鍏ヤ竴浜涘唴瀹�
cell.setCellValue(objToString(dataMap.get(fieldList[i])));
cell.setCellStyle(boderStyle);
@@ -288,8 +289,8 @@
//鍚堝苟鐨勫崟鍏冩牸鏍峰紡
CellStyle boderStyle = workbook.createCellStyle();
//鍨傜洿灞呬腑
- boderStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
- boderStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 鍒涘缓涓�涓眳涓牸寮�
+ boderStyle.setVerticalAlignment(VerticalAlignment.CENTER);
+ boderStyle.setAlignment(HorizontalAlignment.CENTER); // 鍒涘缓涓�涓眳涓牸寮�
// 鍦‥xcel宸ヤ綔绨夸腑寤轰竴宸ヤ綔琛紝鍏跺悕涓虹己鐪佸��
// 濡傝鏂板缓涓�鍚嶄负"鏁堢泭鎸囨爣"鐨勫伐浣滆〃锛屽叾璇彞涓猴細
@@ -306,7 +307,7 @@
// 鍦ㄧ储寮�0鐨勪綅缃垱寤哄崟鍏冩牸锛堝乏涓婄锛�
cell = row.createCell(i);
// 瀹氫箟鍗曞厓鏍间负瀛楃涓茬被鍨�
- cell.setCellType(HSSFCell.CELL_TYPE_STRING);
+ //cell.setCellType(HSSFCell.CELL_TYPE_STRING);
// 鍦ㄥ崟鍏冩牸涓緭鍏ヤ竴浜涘唴瀹�
cell.setCellValue(headList[i]);
cell.setCellStyle(boderStyle);
@@ -326,7 +327,7 @@
sheet.setColumnWidth(i,width);
cell = row_value.createCell(i);
// 瀹氫箟鍗曞厓鏍间负瀛楃涓茬被鍨�
- cell.setCellType(HSSFCell.CELL_TYPE_STRING);
+ //cell.setCellType(HSSFCell.CELL_TYPE_STRING);
// 鍦ㄥ崟鍏冩牸涓緭鍏ヤ竴浜涘唴瀹�
cell.setCellValue(objToString(dataMap.get(fieldList[i])));
cell.setCellStyle(boderStyle);
@@ -431,9 +432,9 @@
HSSFCell cell = hssfrow.getCell(i);
if (cell == null) {
arrayString.add("");
- } else if (cell.getCellType() == 0) {
+ } else if (cell.getCellType() == CellType.NUMERIC) {
// arrayString[i] = new Double(cell.getNumericCellValue()).toString();
- if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
+ if (CellType.NUMERIC == cell.getCellType()) {
short format = cell.getCellStyle().getDataFormat();
if(format == 14 || format == 31 || format == 57 || format == 58){
//鏃ユ湡锛堜腑鏂囨椂闂存牸寮忕殑锛�
@@ -449,24 +450,24 @@
arrayString.add(formater.format(d));
//arrayString[i] = formater.format(d);
} else {
- if(HSSFCell.CELL_TYPE_STRING == cell.getCellType()){
+ if(CellType.STRING == cell.getCellType()){
arrayString.add(cell.getStringCellValue());
//arrayString[i] =cell.getStringCellValue();
- }else if(HSSFCell.CELL_TYPE_FORMULA==cell.getCellType()){
+ }else if(CellType.FORMULA==cell.getCellType()){
arrayString.add(cell.getCellFormula());
//arrayString[i] =cell.getCellFormula();
- }else if(HSSFCell.CELL_TYPE_NUMERIC== cell.getCellType()){
+ }else if(CellType.NUMERIC== cell.getCellType()){
HSSFDataFormatter dataFormatter = new HSSFDataFormatter();
arrayString.add(dataFormatter.formatCellValue(cell));
//arrayString[i] =dataFormatter.formatCellValue(cell);
}
}
}
- } else if(cell.getCellType() == Cell.CELL_TYPE_BLANK){
+ } else if(cell.getCellType() == CellType.BLANK){
arrayString.add("");
//arrayString[i] = "";
} else { // 濡傛灉EXCEL琛ㄦ牸涓殑鏁版嵁绫诲瀷涓哄瓧绗︿覆鍨�
- if(cell.getCellType() != Cell.CELL_TYPE_BOOLEAN){
+ if(cell.getCellType() != CellType.BOOLEAN){
arrayString.add(cell.getStringCellValue().trim());
}else{
arrayString.add(cell.getBooleanCellValue() ? "TRUE" : "FALSE");
@@ -522,9 +523,9 @@
XSSFCell cell = xssfrow.getCell(i);
if (cell == null) {
arrayString.add("");
- } else if (cell.getCellType() == 0) {
+ } else if (cell.getCellType() == CellType.NUMERIC) {
// arrayString[i] = new Double(cell.getNumericCellValue()).toString();
- if (XSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
+ if (CellType.NUMERIC == cell.getCellType()) {
short format = cell.getCellStyle().getDataFormat();
if(format == 14 || format == 31 || format == 57 || format == 58){
//鏃ユ湡锛堜腑鏂囨椂闂存牸寮忕殑锛�
@@ -540,24 +541,24 @@
arrayString.add(formater.format(d));
//arrayString[i] = formater.format(d);
} else {
- if(XSSFCell.CELL_TYPE_STRING == cell.getCellType()){
+ if(CellType.STRING == cell.getCellType()){
arrayString.add(cell.getStringCellValue());
//arrayString[i] =cell.getStringCellValue();
- }else if(XSSFCell.CELL_TYPE_FORMULA==cell.getCellType()){
+ }else if(CellType.FORMULA==cell.getCellType()){
arrayString.add(cell.getCellFormula());
//arrayString[i] =cell.getCellFormula();
- }else if(XSSFCell.CELL_TYPE_NUMERIC== cell.getCellType()){
+ }else if(CellType.NUMERIC== cell.getCellType()){
HSSFDataFormatter dataFormatter = new HSSFDataFormatter();
arrayString.add(dataFormatter.formatCellValue(cell));
//arrayString[i] =dataFormatter.formatCellValue(cell);
}
}
}
- } else if(cell.getCellType() == Cell.CELL_TYPE_BLANK){
+ } else if(cell.getCellType() == CellType.BLANK){
arrayString.add("");
//arrayString[i] = "";
} else { // 濡傛灉EXCEL琛ㄦ牸涓殑鏁版嵁绫诲瀷涓哄瓧绗︿覆鍨�
- if(cell.getCellType() != Cell.CELL_TYPE_BOOLEAN){
+ if(cell.getCellType() != CellType.BOOLEAN){
arrayString.add(cell.getStringCellValue().trim());
}else{
arrayString.add(cell.getBooleanCellValue() ? "TRUE" : "FALSE");
@@ -576,17 +577,9 @@
/**鍒ゆ柇excel鐨勭増鏈�*/
- public static Workbook create(InputStream inp) throws Exception {
- if (!inp.markSupported()) {
- inp = new PushbackInputStream(inp, 8);
- }
- if (POIFSFileSystem.hasPOIFSHeader(inp)) {
- return new HSSFWorkbook(inp);
- }
- if (POIXMLDocument.hasOOXMLHeader(inp)) {
- return new XSSFWorkbook(OPCPackage.open(inp));
- }
- throw new IllegalArgumentException("浣犵殑excel鐗堟湰鐩墠poi瑙ｆ瀽涓嶄簡");
+ public static Workbook create(InputStream inp) throws IOException {
+ //杩欐牱鍐� excel 鑳藉吋瀹�03鍜�07
+ return WorkbookFactory.create(inp);
}
/**璇诲彇excel鏂囦欢锛屽吋瀹�2003鍜�2007
@@ -607,12 +600,20 @@
Workbook book = create(new BufferedInputStream(new FileInputStream(file)));
// 閬嶅巻璇ヨ〃鏍间腑鎵�鏈夌殑宸ヤ綔琛紝i琛ㄧず宸ヤ綔琛ㄧ殑鏁伴噺 getNumberOfSheets琛ㄧず宸ヤ綔琛ㄧ殑鎬绘暟
+ Sheet hssfsheet;
+ Row hssfrow;
+ List<String> arrayString;
+ Cell cell;
+ short format;
+ Date d;
+ DateFormat formater;
+ HSSFDataFormatter dataFormatter;
for(int s=0;s<book.getNumberOfSheets();s++) {
- Sheet hssfsheet = book.getSheetAt(s);
+ hssfsheet = book.getSheetAt(s);
int col = 0;
// 閬嶅巻璇ヨ鎵�鏈夌殑琛�,j琛ㄧず琛屾暟 getPhysicalNumberOfRows琛岀殑鎬绘暟 鍘婚櫎鏍囬
for (int j = 0; j < hssfsheet.getPhysicalNumberOfRows(); j++) {
- Row hssfrow = hssfsheet.getRow(j);
+ hssfrow = hssfsheet.getRow(j);
if(hssfrow!=null){
if(j == 0) {
col = hssfrow.getPhysicalNumberOfCells();
@@ -622,43 +623,43 @@
}
}
// 鍗曡鏁版嵁
- List<String> arrayString = new ArrayList<>();
+ arrayString = new ArrayList<>();
for (int i = 0; i < col; i++) {
- Cell cell = hssfrow.getCell(i);
+ cell = hssfrow.getCell(i);
if (cell == null) {
arrayString.add("");
- } else if (cell.getCellType() == 0) {
+ } else if (cell.getCellType() == CellType.NUMERIC) {
// arrayString[i] = new Double(cell.getNumericCellValue()).toString();
- if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
- short format = cell.getCellStyle().getDataFormat();
+ if (CellType.NUMERIC == cell.getCellType()) {
+ format = cell.getCellStyle().getDataFormat();
if(format == 14 || format == 31 || format == 57 || format == 58){
//鏃ユ湡锛堜腑鏂囨椂闂存牸寮忕殑锛�
- Date d = cell.getDateCellValue();
- DateFormat formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
+ d = cell.getDateCellValue();
+ formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
// DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
arrayString.add(formater.format(d));
//arrayString[i] = formater.format(d);
}else if (HSSFDateUtil.isCellDateFormatted(cell)) {
- Date d = cell.getDateCellValue();
+ d = cell.getDateCellValue();
//DateFormat formater = new SimpleDateFormat("yyyy骞碝M鏈坉d鏃� HH鏃秏m鍒唖s绉�");
- DateFormat formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
+ formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
arrayString.add(formater.format(d));
//arrayString[i] = formater.format(d);
} else {
- if(HSSFCell.CELL_TYPE_STRING == cell.getCellType()){
+ if(CellType.STRING == cell.getCellType()){
arrayString.add(cell.getStringCellValue());
//arrayString[i] =cell.getStringCellValue();
- }else if(HSSFCell.CELL_TYPE_FORMULA==cell.getCellType()){
+ }else if(CellType.FORMULA==cell.getCellType()){
arrayString.add(cell.getCellFormula());
//arrayString[i] =cell.getCellFormula();
- }else if(HSSFCell.CELL_TYPE_NUMERIC== cell.getCellType()){
- HSSFDataFormatter dataFormatter = new HSSFDataFormatter();
+ }else if(CellType.NUMERIC== cell.getCellType()){
+ dataFormatter = new HSSFDataFormatter();
arrayString.add(dataFormatter.formatCellValue(cell));
//arrayString[i] =dataFormatter.formatCellValue(cell);
}
}
}
- } else if(cell.getCellType() == Cell.CELL_TYPE_BLANK){
+ } else if(cell.getCellType() == CellType.BLANK){
arrayString.add("");
//arrayString[i] = "";
} else { // 濡傛灉EXCEL琛ㄦ牸涓殑鏁版嵁绫诲瀷涓哄瓧绗︿覆鍨�
@@ -676,6 +677,7 @@
}
return null;
}
+
@@ -696,7 +698,105 @@
List<List<String>> list = new ArrayList<>();
Workbook book = create(new BufferedInputStream(file.getInputStream()));
+ Sheet hssfsheet;
+ Row hssfrow;
+ List<String> arrayString;
+ Cell cell;
+ short format;
+ Date d;
+ DateFormat formater;
+ HSSFDataFormatter dataFormatter;
// 閬嶅巻璇ヨ〃鏍间腑鎵�鏈夌殑宸ヤ綔琛紝i琛ㄧず宸ヤ綔琛ㄧ殑鏁伴噺 getNumberOfSheets琛ㄧず宸ヤ綔琛ㄧ殑鎬绘暟
+ for(int s=0;s<book.getNumberOfSheets();s++) {
+ hssfsheet = book.getSheetAt(s);
+ int col = 0;
+ // 閬嶅巻璇ヨ鎵�鏈夌殑琛�,j琛ㄧず琛屾暟 getPhysicalNumberOfRows琛岀殑鎬绘暟 鍘婚櫎鏍囬
+ for (int j = 0; j < hssfsheet.getPhysicalNumberOfRows(); j++) {
+ hssfrow = hssfsheet.getRow(j);
+ if(hssfrow!=null){
+ if(j == 0) {
+ col = hssfrow.getPhysicalNumberOfCells();
+ if(!header) {
+ //涓嶅寘鎷〃澶�
+ continue;
+ }
+ }
+ // 鍗曡鏁版嵁
+ arrayString = new ArrayList<>();
+ for (int i = 0; i < col; i++) {
+ cell = hssfrow.getCell(i);
+ if (cell == null) {
+ arrayString.add("");
+ } else if (cell.getCellType() == CellType.NUMERIC) {
+ // arrayString[i] = new Double(cell.getNumericCellValue()).toString();
+ if (CellType.NUMERIC == cell.getCellType()) {
+ 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");
+ // DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
+ arrayString.add(formater.format(d));
+ //arrayString[i] = formater.format(d);
+ }else if (HSSFDateUtil.isCellDateFormatted(cell)) {
+ d = cell.getDateCellValue();
+ //DateFormat formater = new SimpleDateFormat("yyyy骞碝M鏈坉d鏃� HH鏃秏m鍒唖s绉�");
+ formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
+ arrayString.add(formater.format(d));
+ //arrayString[i] = formater.format(d);
+ } else {
+ if(CellType.STRING == cell.getCellType()){
+ arrayString.add(cell.getStringCellValue());
+ //arrayString[i] =cell.getStringCellValue();
+ }else if(CellType.FORMULA==cell.getCellType()){
+ arrayString.add(cell.getCellFormula());
+ //arrayString[i] =cell.getCellFormula();
+ }else if(CellType.NUMERIC== cell.getCellType()){
+ dataFormatter = new HSSFDataFormatter();
+ arrayString.add(dataFormatter.formatCellValue(cell));
+ //arrayString[i] =dataFormatter.formatCellValue(cell);
+ }
+ }
+ }
+ } else if(cell.getCellType() == CellType.BLANK){
+ arrayString.add("");
+ //arrayString[i] = "";
+ } else { // 濡傛灉EXCEL琛ㄦ牸涓殑鏁版嵁绫诲瀷涓哄瓧绗︿覆鍨�
+ arrayString.add(cell.getStringCellValue().trim());
+ //arrayString[i] = cell.getStringCellValue().trim();
+ }
+ }
+ list.add(arrayString);
+ }
+ }
+ }
+ return list;
+ } catch (Exception e) {
+ e.printStackTrace();
+ }
+ return null;
+ }
+
+ /**璇诲彇excel鏂囦欢锛屽吋瀹�2003鍜�2007
+ * 閫氳繃娴佽鍙朎xcel鏂囦欢
+ * @return
+ * @throws Exception
+ */
+ public static List<List<String>> getExcelDataCompatibleCheckEmpty(File file,boolean header) throws Exception {
+ try {
+
+ String fileName = file.getName();
+ if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
+ throw new TipsException("涓婁紶鏂囦欢鏍煎紡涓嶆纭�");
+ }
+
+ // 缁撴灉闆�
+ List<List<String>> list = new ArrayList<>();
+ Workbook book = create(new BufferedInputStream(new FileInputStream(file)));
+
+ // 閬嶅巻璇ヨ〃鏍间腑鎵�鏈夌殑宸ヤ綔琛紝i琛ㄧず宸ヤ綔琛ㄧ殑鏁伴噺 getNumberOfSheets琛ㄧず宸ヤ綔琛ㄧ殑鎬绘暟
+ //涓嶄负绌�
+ boolean isNotEmpty = false;
for(int s=0;s<book.getNumberOfSheets();s++) {
Sheet hssfsheet = book.getSheetAt(s);
int col = 0;
@@ -717,9 +817,9 @@
Cell cell = hssfrow.getCell(i);
if (cell == null) {
arrayString.add("");
- } else if (cell.getCellType() == 0) {
+ } else if (cell.getCellType() == CellType.NUMERIC) {
// arrayString[i] = new Double(cell.getNumericCellValue()).toString();
- if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
+ if (CellType.NUMERIC == cell.getCellType()) {
short format = cell.getCellStyle().getDataFormat();
if(format == 14 || format == 31 || format == 57 || format == 58){
//鏃ユ湡锛堜腑鏂囨椂闂存牸寮忕殑锛�
@@ -735,28 +835,32 @@
arrayString.add(formater.format(d));
//arrayString[i] = formater.format(d);
} else {
- if(HSSFCell.CELL_TYPE_STRING == cell.getCellType()){
+ if(CellType.STRING == cell.getCellType()){
arrayString.add(cell.getStringCellValue());
//arrayString[i] =cell.getStringCellValue();
- }else if(HSSFCell.CELL_TYPE_FORMULA==cell.getCellType()){
+ }else if(CellType.FORMULA==cell.getCellType()){
arrayString.add(cell.getCellFormula());
//arrayString[i] =cell.getCellFormula();
- }else if(HSSFCell.CELL_TYPE_NUMERIC== cell.getCellType()){
+ }else if(CellType.NUMERIC== cell.getCellType()){
HSSFDataFormatter dataFormatter = new HSSFDataFormatter();
arrayString.add(dataFormatter.formatCellValue(cell));
//arrayString[i] =dataFormatter.formatCellValue(cell);
}
}
}
- } else if(cell.getCellType() == Cell.CELL_TYPE_BLANK){
+ isNotEmpty = true;
+ } else if(cell.getCellType() == CellType.BLANK){
arrayString.add("");
//arrayString[i] = "";
} else { // 濡傛灉EXCEL琛ㄦ牸涓殑鏁版嵁绫诲瀷涓哄瓧绗︿覆鍨�
arrayString.add(cell.getStringCellValue().trim());
//arrayString[i] = cell.getStringCellValue().trim();
+ isNotEmpty = true;
}
}
- list.add(arrayString);
+ if (isNotEmpty){
+ list.add(arrayString);
+ }
}
}
}
@@ -768,13 +872,109 @@
}
- private static String getCellVal(Cell cell) {
+ /**璇诲彇excel鏂囦欢锛屽吋瀹�2003鍜�2007
+ * 閫氳繃娴佽鍙朎xcel鏂囦欢
+ * @return
+ * @throws Exception
+ */
+ public static List<List<String>> getExcelDataCompatibleCheckEmpty(MultipartFile file,boolean header) throws Exception {
+ try {
+
+ String fileName = file.getOriginalFilename();
+ if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
+ throw new TipsException("涓婁紶鏂囦欢鏍煎紡涓嶆纭�");
+ }
+
+ // 缁撴灉闆�
+ List<List<String>> list = new ArrayList<>();
+ Workbook book = create(new BufferedInputStream(file.getInputStream()));
+
+ // 閬嶅巻璇ヨ〃鏍间腑鎵�鏈夌殑宸ヤ綔琛紝i琛ㄧず宸ヤ綔琛ㄧ殑鏁伴噺 getNumberOfSheets琛ㄧず宸ヤ綔琛ㄧ殑鎬绘暟
+ //涓嶄负绌�
+ boolean isNotEmpty = false;
+ for(int s=0;s<book.getNumberOfSheets();s++) {
+ Sheet hssfsheet = book.getSheetAt(s);
+ int col = 0;
+ // 閬嶅巻璇ヨ鎵�鏈夌殑琛�,j琛ㄧず琛屾暟 getPhysicalNumberOfRows琛岀殑鎬绘暟 鍘婚櫎鏍囬
+ for (int j = 0; j < hssfsheet.getPhysicalNumberOfRows(); j++) {
+ isNotEmpty = false;
+ Row hssfrow = hssfsheet.getRow(j);
+ if(hssfrow!=null){
+ if(j == 0) {
+ col = hssfrow.getPhysicalNumberOfCells();
+ if(!header) {
+ //涓嶅寘鎷〃澶�
+ continue;
+ }
+ }
+ // 鍗曡鏁版嵁
+ List<String> arrayString = new ArrayList<>();
+ for (int i = 0; i < col; i++) {
+ Cell cell = hssfrow.getCell(i);
+ if (cell == null) {
+ arrayString.add("");
+ } else if (cell.getCellType() == CellType.NUMERIC) {
+ // arrayString[i] = new Double(cell.getNumericCellValue()).toString();
+ if (CellType.NUMERIC == cell.getCellType()) {
+ short format = cell.getCellStyle().getDataFormat();
+ if(format == 14 || format == 31 || format == 57 || format == 58){
+ //鏃ユ湡锛堜腑鏂囨椂闂存牸寮忕殑锛�
+ Date d = cell.getDateCellValue();
+ DateFormat formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
+ // DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
+ arrayString.add(formater.format(d));
+ //arrayString[i] = formater.format(d);
+ }else if (HSSFDateUtil.isCellDateFormatted(cell)) {
+ Date d = cell.getDateCellValue();
+ //DateFormat formater = new SimpleDateFormat("yyyy骞碝M鏈坉d鏃� HH鏃秏m鍒唖s绉�");
+ DateFormat formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
+ arrayString.add(formater.format(d));
+ //arrayString[i] = formater.format(d);
+ } else {
+ if(CellType.STRING == cell.getCellType()){
+ arrayString.add(cell.getStringCellValue());
+ //arrayString[i] =cell.getStringCellValue();
+ }else if(CellType.FORMULA==cell.getCellType()){
+ arrayString.add(cell.getCellFormula());
+ //arrayString[i] =cell.getCellFormula();
+ }else if(CellType.NUMERIC== cell.getCellType()){
+ HSSFDataFormatter dataFormatter = new HSSFDataFormatter();
+ arrayString.add(dataFormatter.formatCellValue(cell));
+ //arrayString[i] =dataFormatter.formatCellValue(cell);
+ }
+ }
+ }
+ isNotEmpty = true;
+ } else if(cell.getCellType() == CellType.BLANK){
+ arrayString.add("");
+ //arrayString[i] = "";
+ } else { // 濡傛灉EXCEL琛ㄦ牸涓殑鏁版嵁绫诲瀷涓哄瓧绗︿覆鍨�
+ arrayString.add(cell.getStringCellValue().trim());
+ isNotEmpty = true;
+ //arrayString[i] = cell.getStringCellValue().trim();
+ }
+ }
+ if (isNotEmpty){
+ list.add(arrayString);
+ }
+ }
+ }
+ }
+ return list;
+ } catch (Exception e) {
+ e.printStackTrace();
+ }
+ return null;
+ }
+
+
+ /*private static String getCellVal(Cell cell) {
if (null == cell) {
return "";
}
switch (cell.getCellType()) {
// 鏁板瓧
- case HSSFCell.CELL_TYPE_NUMERIC:
+ case CellType.NUMERIC:
// 鏃ユ湡鏍煎紡鐨勫鐞�
if (HSSFDateUtil.isCellDateFormatted(cell)) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
@@ -782,24 +982,24 @@
}
return String.valueOf(cell.getNumericCellValue());
// 瀛楃涓�
- case HSSFCell.CELL_TYPE_STRING:
+ case CellType.STRING:
return cell.getStringCellValue();
// 鍏紡
- case HSSFCell.CELL_TYPE_FORMULA:
+ case CellType.FORMULA:
return cell.getCellFormula();
// 绌虹櫧
- case HSSFCell.CELL_TYPE_BLANK:
+ case CellType.BLANK:
return "";
- case HSSFCell.CELL_TYPE_BOOLEAN:
+ case CellType.BOOLEAN:
return cell.getBooleanCellValue() + "";
// 閿欒绫诲瀷
- case HSSFCell.CELL_TYPE_ERROR:
+ case CellType.ERROR:
return cell.getErrorCellValue() + "";
default:
break;
}
return "";
- }
+ }*/
/**
* p鍒ゆ柇鏄惁excel鏂囦欢
@@ -835,8 +1035,8 @@
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFCellStyle boderStyle = workbook.createCellStyle();
- boderStyle.setVerticalAlignment((short)1);
- boderStyle.setAlignment((short)2);
+ boderStyle.setVerticalAlignment(VerticalAlignment.CENTER);
+ boderStyle.setAlignment(HorizontalAlignment.CENTER);
HSSFSheet sheet = workbook.createSheet();
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
HSSFRow row = sheet.createRow(0);
@@ -846,7 +1046,6 @@
row.setHeight(height.shortValue());
sheet.setColumnWidth(i, width);
anchor = row.createCell(i);
- anchor.setCellType(1);
anchor.setCellValue(headList[i]);
anchor.setCellStyle(boderStyle);
}
@@ -863,7 +1062,6 @@
for(int i = 0; i < fieldList.length; ++i) {
sheet.setColumnWidth(i, width);
cell = row_value.createCell(i);
- cell.setCellType(1);
Object value = dataMap.get(fieldList[i]);
if (value != null && "class java.io.File".equals(value.getClass().toString())) {
File file2 = (File)value;
@@ -873,7 +1071,7 @@
bufferImg = ImageIO.read(file2);
ImageIO.write(bufferImg, "jpg", byteArrayOut);
HSSFClientAnchor anchor1 = new HSSFClientAnchor(0, 0, 1023, 255, (short)i, n + 1, (short)i, n + 1);
- anchor1.setAnchorType(0);
+ anchor1.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);
patriarch.createPicture(anchor1, workbook.addPicture(byteArrayOut.toByteArray(), 5));
}
} else {
@@ -896,4 +1094,25 @@
return file;
}
+ public static void main(String[] args) throws Exception {
+ //File file = new File("E:\\360\\a701aae8-9d4b-49f1-8999-1456fa53ef03 (1).xlsx");
+ File file = new File("E:\\360\\tttt (1).xls");
+ //Workbook wk = create(new FileInputStream(file));
+ Workbook wk = StreamingReader.builder()
+ .rowCacheSize(100) // 缂撳瓨鍒板唴瀛樹腑鐨勮鏁�(榛樿鏄�10)
+ .bufferSize(4096) // 璇诲彇璧勬簮鏃讹紝缂撳瓨鍒板唴瀛樼殑瀛楄妭澶у皬(榛樿鏄�1024)
+ .open(new FileInputStream(file)); // InputStream鎴栬�匵LSX鏍煎紡鐨凢ile(蹇呴』)
+
+ Sheet sheet = wk.getSheetAt(0);
+ //閬嶅巻鎵�鏈夌殑琛�
+ for (Row row : sheet) {
+ System.out.println("寮�濮嬮亶鍘嗙" + row.getRowNum() + "琛屾暟鎹細");
+ //閬嶅巻鎵�鏈夌殑鍒�
+ for (Cell cell : row) {
+ System.out.print(cell.getStringCellValue() + " ");
+ }
+ System.out.println(" ");
+ }
+ }
+
}
\ No newline at end of file
diff --git a/src/main/java/com/hx/util/SimpleTool.java b/src/main/java/com/hx/util/SimpleTool.java
index fd0764b..eccf481 100644
--- a/src/main/java/com/hx/util/SimpleTool.java
+++ b/src/main/java/com/hx/util/SimpleTool.java
@@ -43,6 +43,7 @@
import net.sourceforge.pinyin4j.PinyinHelper;
import net.sourceforge.pinyin4j.format.HanyuPinyinOutputFormat;
import net.sourceforge.pinyin4j.format.HanyuPinyinToneType;
+import org.apache.poi.ss.usermodel.CellType;
import org.springframework.cglib.beans.BeanMap;
public class SimpleTool {
@@ -802,7 +803,7 @@
HSSFCell cell = hssfrow.getCell(i);
if (cell == null) {
arrayString[i] = "";
- } else if (cell.getCellType() == 0) {
+ } else if (cell.getCellType() == CellType.NUMERIC) {
// arrayString[i] = new Double(cell.getNumericCellValue()).toString();
short format = cell.getCellStyle().getDataFormat();
SimpleDateFormat sdf = null;
@@ -812,7 +813,7 @@
}else if (format == 20 || format == 32) {
//鏃堕棿
sdf = new SimpleDateFormat("HH:mm");
- }else if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
+ }else if (CellType.NUMERIC == cell.getCellType()) {
if (HSSFDateUtil.isCellDateFormatted(cell)) {
Date d = cell.getDateCellValue();
DateFormat formater = new SimpleDateFormat("yyyy骞�");
@@ -868,9 +869,9 @@
HSSFCell cell = hssfrow.getCell(i);
if (cell == null) {
arrayString[i] = "";
- } else if (cell.getCellType() == 0) {
+ } else if (cell.getCellType() == CellType.NUMERIC) {
// arrayString[i] = new Double(cell.getNumericCellValue()).toString();
- if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
+ if (CellType.NUMERIC == cell.getCellType()) {
short format = cell.getCellStyle().getDataFormat();
if(format == 14 || format == 31 || format == 57 || format == 58){
//鏃ユ湡锛堜腑鏂囨椂闂存牸寮忕殑锛�
@@ -884,17 +885,17 @@
// DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
arrayString[i] = formater.format(d);
} else {
- if(HSSFCell.CELL_TYPE_STRING == cell.getCellType()){
+ if(CellType.STRING == cell.getCellType()){
arrayString[i] =cell.getStringCellValue();
- }else if(HSSFCell.CELL_TYPE_FORMULA==cell.getCellType()){
+ }else if(CellType.FORMULA==cell.getCellType()){
arrayString[i] =cell.getCellFormula();
- }else if(HSSFCell.CELL_TYPE_NUMERIC== cell.getCellType()){
+ }else if(CellType.NUMERIC== cell.getCellType()){
HSSFDataFormatter dataFormatter = new HSSFDataFormatter();
arrayString[i] =dataFormatter.formatCellValue(cell);
}
}
}
- } else if(cell.getCellType() == Cell.CELL_TYPE_BLANK){
+ } else if(cell.getCellType() == CellType.BLANK){
arrayString[i] = "";
} else { // 濡傛灉EXCEL琛ㄦ牸涓殑鏁版嵁绫诲瀷涓哄瓧绗︿覆鍨�
arrayString[i] = cell.getStringCellValue().trim();
@@ -939,9 +940,9 @@
HSSFCell cell = hssfrow.getCell(i);
if (cell == null) {
arrayString[i] = "";
- } else if (cell.getCellType() == 0) {
+ } else if (cell.getCellType() == CellType.NUMERIC) {
// arrayString[i] = new Double(cell.getNumericCellValue()).toString();
- if (HSSFCell.CELL_TYPE_NUMERIC == cell.getCellType()) {
+ if (CellType.NUMERIC == cell.getCellType()) {
short format = cell.getCellStyle().getDataFormat();
if(format == 14 || format == 31 || format == 57 || format == 58){
//鏃ユ湡锛堜腑鏂囨椂闂存牸寮忕殑锛�
@@ -955,20 +956,20 @@
// DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
arrayString[i] = formater.format(d);
} else {
- if(HSSFCell.CELL_TYPE_STRING == cell.getCellType()){
+ if(CellType.STRING == cell.getCellType()){
arrayString[i] =cell.getStringCellValue();
- }else if(HSSFCell.CELL_TYPE_FORMULA==cell.getCellType()){
+ }else if(CellType.FORMULA==cell.getCellType()){
arrayString[i] =cell.getCellFormula();
- }else if(HSSFCell.CELL_TYPE_NUMERIC== cell.getCellType()){
+ }else if(CellType.NUMERIC== cell.getCellType()){
HSSFDataFormatter dataFormatter = new HSSFDataFormatter();
arrayString[i] =dataFormatter.formatCellValue(cell);
}
}
}
- } else if(cell.getCellType() == Cell.CELL_TYPE_BLANK){
+ } else if(cell.getCellType() == CellType.BLANK){
arrayString[i] = "";
} else { // 濡傛灉EXCEL琛ㄦ牸涓殑鏁版嵁绫诲瀷涓哄瓧绗︿覆鍨�
- cell.setCellType(Cell.CELL_TYPE_STRING);
+ cell.setCellType(CellType.STRING);
String name = cell.getStringCellValue().trim();
if(name.equals("-")) {
arrayString[i] = "";
--
Gitblit v1.8.0