From 08573af6ddab154c52a5741de059d88a74206500 Mon Sep 17 00:00:00 2001
From: ChenJiaHe <763432473@qq.com>
Date: 星期五, 27 八月 2021 15:55:56 +0800
Subject: [PATCH] Merge remote-tracking branch 'origin/master'

---
 src/main/java/com/hx/util/ExcelUtil.java        |  141 ++++++++++++++++++++++++++++++++++++++++++++--
 src/main/java/com/hx/util/RegValidatorUtil.java |   14 ++++
 2 files changed, 148 insertions(+), 7 deletions(-)

diff --git a/src/main/java/com/hx/util/ExcelUtil.java b/src/main/java/com/hx/util/ExcelUtil.java
index 88fe7f1..5e9c4d6 100644
--- a/src/main/java/com/hx/util/ExcelUtil.java
+++ b/src/main/java/com/hx/util/ExcelUtil.java
@@ -1,10 +1,5 @@
 package com.hx.util;
 
-import java.io.*;
-import java.text.DateFormat;
-import java.text.SimpleDateFormat;
-import java.util.*;
-
 import com.hx.exception.TipsException;
 import org.apache.poi.POIXMLDocument;
 import org.apache.poi.hssf.usermodel.*;
@@ -14,8 +9,19 @@
 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.xssf.usermodel.XSSFCell;
+import org.apache.poi.xssf.usermodel.XSSFRow;
+import org.apache.poi.xssf.usermodel.XSSFSheet;
 import org.apache.poi.xssf.usermodel.XSSFWorkbook;
 import org.springframework.web.multipart.MultipartFile;
+
+import java.io.*;
+import java.text.DateFormat;
+import java.text.SimpleDateFormat;
+import java.util.ArrayList;
+import java.util.Date;
+import java.util.List;
+import java.util.Map;
 
 
 /**
@@ -81,7 +87,7 @@
 		}
 
 		// 鏂板缓涓�杈撳嚭鏂囦欢娴�
-		File file = SimpleTool.createFile(outPath,excelName);
+		File file = SimpleTool.createFile(outPath, excelName);
 		FileOutputStream fOut = new FileOutputStream(file);
 		// 鎶婄浉搴旂殑Excel 宸ヤ綔绨垮瓨鐩�
 		workbook.write(fOut);
@@ -265,6 +271,30 @@
 	}
 
 	/**
+	 * 璇诲彇Excel鏁版嵁
+	 * @param file
+	 * @param header
+	 * @return
+	 * @throws Exception
+	 */
+	public static List<List<String>> readExcelData(MultipartFile file, boolean header) throws Exception {
+
+		String fileName = file.getOriginalFilename();
+		if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
+			throw new TipsException("涓婁紶鏂囦欢鏍煎紡涓嶆纭�");
+		}
+
+		//鍒ゆ柇涓嶅悓鏍煎紡澶勭悊鏂规硶涓嶅悓
+		if(fileName.matches("^.+\\.(?i)(xls)$")){
+			//xls鏍煎紡浣跨敤HSSF
+			return readExcelByeFileData(file, header);
+		}else{
+			//xlsx鏍煎紡浣跨敤XSSF
+			return readExcelByeFileDataToXSSF(file, header);
+		}
+	}
+
+	/**
 	 * 璇诲彇 Excel鏂囦欢鍐呭
 	 *
 	 * @param file
@@ -340,7 +370,104 @@
 							arrayString.add("");
 							//arrayString[i] = "";
 						} else { // 濡傛灉EXCEL琛ㄦ牸涓殑鏁版嵁绫诲瀷涓哄瓧绗︿覆鍨�
-							arrayString.add(cell.getStringCellValue().trim());
+							if(cell.getCellType() != Cell.CELL_TYPE_BOOLEAN){
+								arrayString.add(cell.getStringCellValue().trim());
+							}else{
+								arrayString.add(cell.getBooleanCellValue() ? "TRUE" : "FALSE");
+							}
+							//arrayString[i] = cell.getStringCellValue().trim();
+						}
+					}
+					list.add(arrayString);
+				}
+			}
+		}
+		return list;
+	}
+
+	/**
+	 * 璇诲彇 Excel鏂囦欢鍐呭
+	 *
+	 * @param file
+	 * @param header 鏄惁鍖呮嫭琛ㄥご
+	 * @return
+	 * @throws Exception
+	 */
+	public static List<List<String>> readExcelByeFileDataToXSSF(MultipartFile file, boolean header) throws Exception {
+
+		String fileName = file.getOriginalFilename();
+		if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
+			throw new TipsException("涓婁紶鏂囦欢鏍煎紡涓嶆纭�");
+		}
+
+		// 缁撴灉闆�
+		List<List<String>> list = new ArrayList<>();
+
+		XSSFWorkbook xssfWorkbook = new XSSFWorkbook(file.getInputStream());
+
+		// 閬嶅巻璇ヨ〃鏍间腑鎵�鏈夌殑宸ヤ綔琛紝i琛ㄧず宸ヤ綔琛ㄧ殑鏁伴噺 getNumberOfSheets琛ㄧず宸ヤ綔琛ㄧ殑鎬绘暟
+		for(int s=0;s<xssfWorkbook.getNumberOfSheets();s++) {
+			XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(s);
+			int col = 0;
+			// 閬嶅巻璇ヨ鎵�鏈夌殑琛�,j琛ㄧず琛屾暟 getPhysicalNumberOfRows琛岀殑鎬绘暟 鍘婚櫎鏍囬
+			for (int j = 0; j < xssfSheet.getPhysicalNumberOfRows(); j++) {
+				XSSFRow xssfrow = xssfSheet.getRow(j);
+				if(xssfrow!=null){
+					if(j == 0) {
+						col = xssfrow.getPhysicalNumberOfCells();
+						if(!header) {
+							//涓嶅寘鎷〃澶�
+							continue;
+						}
+					}
+					// 鍗曡鏁版嵁
+					List<String> arrayString = new ArrayList<>();
+					for (int i = 0; i < col; i++) {
+						XSSFCell cell = xssfrow.getCell(i);
+						if (cell == null) {
+							arrayString.add("");
+						} else if (cell.getCellType() == 0) {
+							// arrayString[i] = new Double(cell.getNumericCellValue()).toString();
+							if (XSSFCell.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(XSSFCell.CELL_TYPE_STRING == cell.getCellType()){
+										arrayString.add(cell.getStringCellValue());
+										//arrayString[i] =cell.getStringCellValue();
+									}else if(XSSFCell.CELL_TYPE_FORMULA==cell.getCellType()){
+										arrayString.add(cell.getCellFormula());
+										//arrayString[i] =cell.getCellFormula();
+									}else if(XSSFCell.CELL_TYPE_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){
+							arrayString.add("");
+							//arrayString[i] = "";
+						} else { // 濡傛灉EXCEL琛ㄦ牸涓殑鏁版嵁绫诲瀷涓哄瓧绗︿覆鍨�
+							if(cell.getCellType() != Cell.CELL_TYPE_BOOLEAN){
+								arrayString.add(cell.getStringCellValue().trim());
+							}else{
+								arrayString.add(cell.getBooleanCellValue() ? "TRUE" : "FALSE");
+							}
+
+
 							//arrayString[i] = cell.getStringCellValue().trim();
 						}
 					}
diff --git a/src/main/java/com/hx/util/RegValidatorUtil.java b/src/main/java/com/hx/util/RegValidatorUtil.java
index 4c89a09..d3d68a2 100644
--- a/src/main/java/com/hx/util/RegValidatorUtil.java
+++ b/src/main/java/com/hx/util/RegValidatorUtil.java
@@ -240,6 +240,20 @@
 	}
 
 	/**
+	 * 鍒ゆ柇瀛楃涓叉槸涓嶆槸double鍨�
+	 * @param str
+	 * @return
+	 */
+	public static boolean isNumeric(String str){
+		Pattern pattern = Pattern.compile("[0-9]+[.]{0,1}[0-9]*[dD]{0,1}");
+		Matcher isNum = pattern.matcher(str);
+		if( !isNum.matches() ){
+			return false;
+		}
+		return true;
+	}
+
+	/**
 	* @param regex
 	* 姝e垯琛ㄨ揪寮忓瓧绗︿覆
 	* @param str

--
Gitblit v1.8.0