From d57fc1094625792b20d5ec713bcf9dfb1901ed68 Mon Sep 17 00:00:00 2001
From: fwq <582742538@qq.com>
Date: 星期五, 01 九月 2023 10:17:15 +0800
Subject: [PATCH] 增加Excel解析不为空的校验
---
src/main/java/com/hx/util/ExcelUtil.java | 254 +++++++++++++++++++++++++++++++++++++++++++++-----
1 files changed, 227 insertions(+), 27 deletions(-)
diff --git a/src/main/java/com/hx/util/ExcelUtil.java b/src/main/java/com/hx/util/ExcelUtil.java
index 320ecb7..d5c0f2a 100644
--- a/src/main/java/com/hx/util/ExcelUtil.java
+++ b/src/main/java/com/hx/util/ExcelUtil.java
@@ -5,10 +5,8 @@
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
-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.ss.usermodel.*;
+import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.web.multipart.MultipartFile;
@@ -57,10 +55,11 @@
// 鍦ㄧ储寮�0鐨勪綅缃垱寤鸿锛堟渶椤剁鐨勮锛�
HSSFRow row = sheet.createRow(0);
// ===============================================================
- for (int i = 0; i < headList.length; i++) {
+ HSSFCell cell;
+ for (int i = 0; i < headList.length; i++) {
// 鍦ㄧ储寮�0鐨勪綅缃垱寤哄崟鍏冩牸锛堝乏涓婄锛�
- HSSFCell cell = row.createCell(i);
+ cell = row.createCell(i);
// 瀹氫箟鍗曞厓鏍间负瀛楃涓茬被鍨�
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
// 鍦ㄥ崟鍏冩牸涓緭鍏ヤ竴浜涘唴瀹�
@@ -68,14 +67,16 @@
}
// ===============================================================
if (dataList != null) {
+ HSSFRow row_value;
+ Map<String, Object> dataMap;
for (int n = 0; n < dataList.size(); n++) {
// 鍦ㄧ储寮�1鐨勪綅缃垱寤鸿
- HSSFRow row_value = sheet.createRow(n + 1);
- Map<String, Object> dataMap = dataList.get(n);
+ row_value = sheet.createRow(n + 1);
+ dataMap = dataList.get(n);
// ===============================================================
for (int i = 0; i < fieldList.length; i++) {
// 鍦ㄧ储寮�0鐨勪綅缃垱寤哄崟鍏冩牸锛堝乏涓婄锛�
- HSSFCell cell = row_value.createCell(i);
+ cell = row_value.createCell(i);
// 瀹氫箟鍗曞厓鏍间负瀛楃涓茬被鍨�
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
// 鍦ㄥ崟鍏冩牸涓緭鍏ヤ竴浜涘唴瀹�
@@ -125,10 +126,10 @@
// 鍦ㄧ储寮�0鐨勪綅缃垱寤鸿锛堟渶椤剁鐨勮锛�
HSSFRow row = sheet.createRow(0);
// ===============================================================
+ HSSFCell cell;
for (int i = 0; i < headList.length; i++) {
-
// 鍦ㄧ储寮�0鐨勪綅缃垱寤哄崟鍏冩牸锛堝乏涓婄锛�
- HSSFCell cell = row.createCell(i);
+ cell = row.createCell(i);
// 瀹氫箟鍗曞厓鏍间负瀛楃涓茬被鍨�
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
// 鍦ㄥ崟鍏冩牸涓緭鍏ヤ竴浜涘唴瀹�
@@ -136,14 +137,16 @@
}
// ===============================================================
if (dataList != null) {
+ HSSFRow row_value;
+ Map<String, Object> dataMap;
for (int n = 0; n < dataList.size(); n++) {
// 鍦ㄧ储寮�1鐨勪綅缃垱寤鸿
- HSSFRow row_value = sheet.createRow(n + 1);
- Map<String, Object> dataMap = dataList.get(n);
+ row_value = sheet.createRow(n + 1);
+ dataMap = dataList.get(n);
// ===============================================================
for (int i = 0; i < fieldList.length; i++) {
// 鍦ㄧ储寮�0鐨勪綅缃垱寤哄崟鍏冩牸锛堝乏涓婄锛�
- HSSFCell cell = row_value.createCell(i);
+ cell = row_value.createCell(i);
// 瀹氫箟鍗曞厓鏍间负瀛楃涓茬被鍨�
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
// 鍦ㄥ崟鍏冩牸涓緭鍏ヤ竴浜涘唴瀹�
@@ -204,12 +207,13 @@
// 鍦ㄧ储寮�0鐨勪綅缃垱寤鸿锛堟渶椤剁鐨勮锛�
HSSFRow row = sheet.createRow(0);
// ===============================================================
+ HSSFCell cell;
for (int i = 0; i < headList.length; i++) {
//楂樺害
row.setHeight(height.shortValue());
sheet.setColumnWidth(i,width);
// 鍦ㄧ储寮�0鐨勪綅缃垱寤哄崟鍏冩牸锛堝乏涓婄锛�
- HSSFCell cell = row.createCell(i);
+ cell = row.createCell(i);
// 瀹氫箟鍗曞厓鏍间负瀛楃涓茬被鍨�
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
// 鍦ㄥ崟鍏冩牸涓緭鍏ヤ竴浜涘唴瀹�
@@ -218,16 +222,18 @@
}
// ===============================================================
if (dataList != null) {
+ HSSFRow row_value;
+ Map<String, Object> dataMap;
for (int n = 0; n < dataList.size(); n++) {
// 鍦ㄧ储寮�1鐨勪綅缃垱寤鸿
- HSSFRow row_value = sheet.createRow(n + 1);
+ row_value = sheet.createRow(n + 1);
row_value.setHeight(height.shortValue());
- Map<String, Object> dataMap = dataList.get(n);
+ dataMap = dataList.get(n);
// ===============================================================
for (int i = 0; i < fieldList.length; i++) {
// 鍦ㄧ储寮�0鐨勪綅缃垱寤哄崟鍏冩牸锛堝乏涓婄锛�
sheet.setColumnWidth(i,width);
- HSSFCell cell = row_value.createCell(i);
+ cell = row_value.createCell(i);
// 瀹氫箟鍗曞厓鏍间负瀛楃涓茬被鍨�
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
// 鍦ㄥ崟鍏冩牸涓緭鍏ヤ竴浜涘唴瀹�
@@ -276,11 +282,11 @@
width = 5000;
}
// 鍒涘缓鏂扮殑Excel 宸ヤ綔绨�
- XSSFWorkbook workbook = new XSSFWorkbook();
-
+ //XSSFWorkbook workbook = new XSSFWorkbook();
+ SXSSFWorkbook workbook = new SXSSFWorkbook(1000);
//鍚堝苟鐨勫崟鍏冩牸鏍峰紡
- XSSFCellStyle boderStyle = workbook.createCellStyle();
+ CellStyle boderStyle = workbook.createCellStyle();
//鍨傜洿灞呬腑
boderStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
boderStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 鍒涘缓涓�涓眳涓牸寮�
@@ -288,16 +294,17 @@
// 鍦‥xcel宸ヤ綔绨夸腑寤轰竴宸ヤ綔琛紝鍏跺悕涓虹己鐪佸��
// 濡傝鏂板缓涓�鍚嶄负"鏁堢泭鎸囨爣"鐨勫伐浣滆〃锛屽叾璇彞涓猴細
// HSSFSheet sheet = workbook.createSheet("鏁堢泭鎸囨爣");
- XSSFSheet sheet = workbook.createSheet();
+ Sheet sheet = workbook.createSheet();
// 鍦ㄧ储寮�0鐨勪綅缃垱寤鸿锛堟渶椤剁鐨勮锛�
- XSSFRow row = sheet.createRow(0);
+ Row row = sheet.createRow(0);
// ===============================================================
+ Cell cell;
for (int i = 0; i < headList.length; i++) {
//楂樺害
row.setHeight(height.shortValue());
sheet.setColumnWidth(i,width);
// 鍦ㄧ储寮�0鐨勪綅缃垱寤哄崟鍏冩牸锛堝乏涓婄锛�
- XSSFCell cell = row.createCell(i);
+ cell = row.createCell(i);
// 瀹氫箟鍗曞厓鏍间负瀛楃涓茬被鍨�
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
// 鍦ㄥ崟鍏冩牸涓緭鍏ヤ竴浜涘唴瀹�
@@ -306,16 +313,18 @@
}
// ===============================================================
if (dataList != null) {
+ Row row_value;
+ Map<String, Object> dataMap;
for (int n = 0; n < dataList.size(); n++) {
// 鍦ㄧ储寮�1鐨勪綅缃垱寤鸿
- XSSFRow row_value = sheet.createRow(n + 1);
+ row_value = sheet.createRow(n + 1);
row_value.setHeight(height.shortValue());
- Map<String, Object> dataMap = dataList.get(n);
+ dataMap = dataList.get(n);
// ===============================================================
for (int i = 0; i < fieldList.length; i++) {
// 鍦ㄧ储寮�0鐨勪綅缃垱寤哄崟鍏冩牸锛堝乏涓婄锛�
sheet.setColumnWidth(i,width);
- XSSFCell cell = row_value.createCell(i);
+ cell = row_value.createCell(i);
// 瀹氫箟鍗曞厓鏍间负瀛楃涓茬被鍨�
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
// 鍦ㄥ崟鍏冩牸涓緭鍏ヤ竴浜涘唴瀹�
@@ -759,6 +768,197 @@
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;
+ // 閬嶅巻璇ヨ鎵�鏈夌殑琛�,j琛ㄧず琛屾暟 getPhysicalNumberOfRows琛岀殑鎬绘暟 鍘婚櫎鏍囬
+ for (int j = 0; j < hssfsheet.getPhysicalNumberOfRows(); j++) {
+ 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() == 0) {
+ // arrayString[i] = new Double(cell.getNumericCellValue()).toString();
+ if (HSSFCell.CELL_TYPE_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(HSSFCell.CELL_TYPE_STRING == cell.getCellType()){
+ arrayString.add(cell.getStringCellValue());
+ //arrayString[i] =cell.getStringCellValue();
+ }else if(HSSFCell.CELL_TYPE_FORMULA==cell.getCellType()){
+ arrayString.add(cell.getCellFormula());
+ //arrayString[i] =cell.getCellFormula();
+ }else if(HSSFCell.CELL_TYPE_NUMERIC== cell.getCellType()){
+ HSSFDataFormatter dataFormatter = new HSSFDataFormatter();
+ arrayString.add(dataFormatter.formatCellValue(cell));
+ //arrayString[i] =dataFormatter.formatCellValue(cell);
+ }
+ }
+ }
+ isNotEmpty = true;
+ } else if(cell.getCellType() == Cell.CELL_TYPE_BLANK){
+ arrayString.add("");
+ //arrayString[i] = "";
+ } else { // 濡傛灉EXCEL琛ㄦ牸涓殑鏁版嵁绫诲瀷涓哄瓧绗︿覆鍨�
+ arrayString.add(cell.getStringCellValue().trim());
+ //arrayString[i] = cell.getStringCellValue().trim();
+ isNotEmpty = true;
+ }
+ }
+ if (isNotEmpty){
+ 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(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() == 0) {
+ // arrayString[i] = new Double(cell.getNumericCellValue()).toString();
+ if (HSSFCell.CELL_TYPE_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(HSSFCell.CELL_TYPE_STRING == cell.getCellType()){
+ arrayString.add(cell.getStringCellValue());
+ //arrayString[i] =cell.getStringCellValue();
+ }else if(HSSFCell.CELL_TYPE_FORMULA==cell.getCellType()){
+ arrayString.add(cell.getCellFormula());
+ //arrayString[i] =cell.getCellFormula();
+ }else if(HSSFCell.CELL_TYPE_NUMERIC== cell.getCellType()){
+ HSSFDataFormatter dataFormatter = new HSSFDataFormatter();
+ arrayString.add(dataFormatter.formatCellValue(cell));
+ //arrayString[i] =dataFormatter.formatCellValue(cell);
+ }
+ }
+ }
+ isNotEmpty = true;
+ } else if(cell.getCellType() == Cell.CELL_TYPE_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 "";
--
Gitblit v1.8.0