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 |  878 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++-
 1 files changed, 850 insertions(+), 28 deletions(-)

diff --git a/src/main/java/com/hx/util/ExcelUtil.java b/src/main/java/com/hx/util/ExcelUtil.java
index c549a08..d5c0f2a 100644
--- a/src/main/java/com/hx/util/ExcelUtil.java
+++ b/src/main/java/com/hx/util/ExcelUtil.java
@@ -1,24 +1,28 @@
 package com.hx.util;
 
-import java.io.File;
-import java.io.FileInputStream;
-import java.io.FileOutputStream;
+import com.hx.exception.TipsException;
+import org.apache.poi.POIXMLDocument;
+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.*;
+import org.apache.poi.xssf.streaming.SXSSFWorkbook;
+import org.apache.poi.xssf.usermodel.*;
+import org.springframework.web.multipart.MultipartFile;
+
+import javax.imageio.ImageIO;
+import java.awt.image.BufferedImage;
+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;
 
-import com.hx.exception.TipsException;
-import org.apache.poi.hssf.usermodel.*;
-import org.apache.poi.ss.usermodel.Cell;
-import org.springframework.web.multipart.MultipartFile;
-
 
 /**
- * 
+ *
  * @author hjr
  */
 public final class ExcelUtil {
@@ -37,8 +41,8 @@
 	 * @throws Exception
 	 */
 	public static String createExcel(String outPath, String excelName,
-			String[] headList, String[] fieldList,
-			List<Map<String, Object>> dataList) throws Exception {
+									 String[] headList, String[] fieldList,
+									 List<Map<String, Object>> dataList) throws Exception {
 
 		String filePath = null;
 		// 鍒涘缓鏂扮殑Excel 宸ヤ綔绨�
@@ -51,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);
 			// 鍦ㄥ崟鍏冩牸涓緭鍏ヤ竴浜涘唴瀹�
@@ -62,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);
 					// 鍦ㄥ崟鍏冩牸涓緭鍏ヤ竴浜涘唴瀹�
@@ -80,7 +87,7 @@
 		}
 
 		// 鏂板缓涓�杈撳嚭鏂囦欢娴�
-		File file = SimpleTool.createFile(outPath,excelName);
+		File file = SimpleTool.createFile(outPath, excelName);
 		FileOutputStream fOut = new FileOutputStream(file);
 		// 鎶婄浉搴旂殑Excel 宸ヤ綔绨垮瓨鐩�
 		workbook.write(fOut);
@@ -106,8 +113,8 @@
 	 * @throws Exception
 	 */
 	public static File createExcel(String[] headList, String[] fieldList,
-									 List<Map<String, Object>> dataList) throws Exception {
-		File file = File.createTempFile("temp", ".xls");
+								   List<Map<String, Object>> dataList) throws Exception {
+		File file = File.createTempFile("temp", ".xlsx");
 		try{
 			// 鍒涘缓鏂扮殑Excel 宸ヤ綔绨�
 			HSSFWorkbook workbook = new HSSFWorkbook();
@@ -119,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);
 				// 鍦ㄥ崟鍏冩牸涓緭鍏ヤ竴浜涘唴瀹�
@@ -130,18 +137,199 @@
 			}
 			// ===============================================================
 			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);
 						// 鍦ㄥ崟鍏冩牸涓緭鍏ヤ竴浜涘唴瀹�
 						cell.setCellValue(objToString(dataMap.get(fieldList[i])));
+					}
+					// ===============================================================
+				}
+			}
+
+			// 鏂板缓涓�杈撳嚭鏂囦欢娴�
+			FileOutputStream fOut = new FileOutputStream(file);
+			// 鎶婄浉搴旂殑Excel 宸ヤ綔绨垮瓨鐩�
+			workbook.write(fOut);
+			fOut.flush();
+			// 鎿嶄綔缁撴潫锛屽叧闂枃浠�
+			fOut.close();
+		}catch (Exception e){
+
+		}finally {
+			file.deleteOnExit();
+		}
+		return file;
+	}
+
+	/**鐢熸垚涓存椂鏂囦欢
+	 * @param headList
+	 *        Excel鏂囦欢Head鏍囬闆嗗悎
+	 * @param fieldList
+	 *        Excel鏂囦欢Field鏍囬闆嗗悎 鏍规嵁field鏉ュ鎵句綅缃～鍏呰〃鏍�
+	 * @param dataList
+	 *        Excel鏂囦欢鏁版嵁鍐呭閮ㄥ垎
+	 * @throws Exception
+	 */
+	public static File createExcel(String[] headList, String[] fieldList, List<Map<String, Object>> dataList
+			,Integer height,Integer width) throws Exception {
+		File file = File.createTempFile("temp", ".xls");
+		try{
+
+			if(height == null){
+				height = 450;
+			}
+			if(width == null){
+				width = 5000;
+			}
+			// 鍒涘缓鏂扮殑Excel 宸ヤ綔绨�
+			HSSFWorkbook workbook = new HSSFWorkbook();
+
+			//鍚堝苟鐨勫崟鍏冩牸鏍峰紡
+			HSSFCellStyle boderStyle = workbook.createCellStyle();
+			//鍨傜洿灞呬腑
+			boderStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
+			boderStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 鍒涘缓涓�涓眳涓牸寮�
+
+			// 鍦‥xcel宸ヤ綔绨夸腑寤轰竴宸ヤ綔琛紝鍏跺悕涓虹己鐪佸��
+			// 濡傝鏂板缓涓�鍚嶄负"鏁堢泭鎸囨爣"鐨勫伐浣滆〃锛屽叾璇彞涓猴細
+			// HSSFSheet sheet = workbook.createSheet("鏁堢泭鎸囨爣");
+			HSSFSheet sheet = workbook.createSheet();
+			// 鍦ㄧ储寮�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鐨勪綅缃垱寤哄崟鍏冩牸锛堝乏涓婄锛�
+				cell = row.createCell(i);
+				// 瀹氫箟鍗曞厓鏍间负瀛楃涓茬被鍨�
+				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
+				// 鍦ㄥ崟鍏冩牸涓緭鍏ヤ竴浜涘唴瀹�
+				cell.setCellValue(headList[i]);
+				cell.setCellStyle(boderStyle);
+			}
+			// ===============================================================
+			if (dataList != null) {
+				HSSFRow row_value;
+				Map<String, Object> dataMap;
+				for (int n = 0; n < dataList.size(); n++) {
+					// 鍦ㄧ储寮�1鐨勪綅缃垱寤鸿
+					row_value = sheet.createRow(n + 1);
+					row_value.setHeight(height.shortValue());
+					dataMap = dataList.get(n);
+					// ===============================================================
+					for (int i = 0; i < fieldList.length; i++) {
+						// 鍦ㄧ储寮�0鐨勪綅缃垱寤哄崟鍏冩牸锛堝乏涓婄锛�
+						sheet.setColumnWidth(i,width);
+						cell = row_value.createCell(i);
+						// 瀹氫箟鍗曞厓鏍间负瀛楃涓茬被鍨�
+						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
+						// 鍦ㄥ崟鍏冩牸涓緭鍏ヤ竴浜涘唴瀹�
+						cell.setCellValue(objToString(dataMap.get(fieldList[i])));
+						cell.setCellStyle(boderStyle);
+					}
+					// ===============================================================
+				}
+			}
+
+			// 鏂板缓涓�杈撳嚭鏂囦欢娴�
+			FileOutputStream fOut = new FileOutputStream(file);
+			// 鎶婄浉搴旂殑Excel 宸ヤ綔绨垮瓨鐩�
+			workbook.write(fOut);
+			fOut.flush();
+			// 鎿嶄綔缁撴潫锛屽叧闂枃浠�
+			fOut.close();
+		}catch (Exception e){
+
+		}finally {
+			file.deleteOnExit();
+		}
+		return file;
+	}
+
+	/**鏃犻檺鍒惰鏁扮敓鎴恊cxel锛岀敓鎴愪复鏃舵枃浠�
+	 * @param headList
+	 *        Excel鏂囦欢Head鏍囬闆嗗悎
+	 * @param fieldList
+	 *        Excel鏂囦欢Field鏍囬闆嗗悎 鏍规嵁field鏉ュ鎵句綅缃～鍏呰〃鏍�
+	 * @param dataList
+	 *        Excel鏂囦欢鏁版嵁鍐呭閮ㄥ垎
+	 * @param height  鍗曞厓鏍奸珮搴︼紝榛樿450
+	 * @param width  鍗曞厓鏍煎搴︼紝榛樿5000
+	 * @throws Exception
+	 */
+	public static File createXSSExcel(String[] headList, String[] fieldList, List<Map<String, Object>> dataList
+			,Integer height,Integer width) throws Exception {
+		File file = File.createTempFile("temp", ".xls");
+		try{
+
+			if(height == null){
+				height = 450;
+			}
+			if(width == null){
+				width = 5000;
+			}
+			// 鍒涘缓鏂扮殑Excel 宸ヤ綔绨�
+			//XSSFWorkbook workbook = new XSSFWorkbook();
+			SXSSFWorkbook workbook = new SXSSFWorkbook(1000);
+
+			//鍚堝苟鐨勫崟鍏冩牸鏍峰紡
+			CellStyle boderStyle = workbook.createCellStyle();
+			//鍨傜洿灞呬腑
+			boderStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
+			boderStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 鍒涘缓涓�涓眳涓牸寮�
+
+			// 鍦‥xcel宸ヤ綔绨夸腑寤轰竴宸ヤ綔琛紝鍏跺悕涓虹己鐪佸��
+			// 濡傝鏂板缓涓�鍚嶄负"鏁堢泭鎸囨爣"鐨勫伐浣滆〃锛屽叾璇彞涓猴細
+			// HSSFSheet sheet = workbook.createSheet("鏁堢泭鎸囨爣");
+			Sheet sheet = workbook.createSheet();
+			// 鍦ㄧ储寮�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鐨勪綅缃垱寤哄崟鍏冩牸锛堝乏涓婄锛�
+				cell = row.createCell(i);
+				// 瀹氫箟鍗曞厓鏍间负瀛楃涓茬被鍨�
+				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
+				// 鍦ㄥ崟鍏冩牸涓緭鍏ヤ竴浜涘唴瀹�
+				cell.setCellValue(headList[i]);
+				cell.setCellStyle(boderStyle);
+			}
+			// ===============================================================
+			if (dataList != null) {
+				Row row_value;
+				Map<String, Object> dataMap;
+				for (int n = 0; n < dataList.size(); n++) {
+					// 鍦ㄧ储寮�1鐨勪綅缃垱寤鸿
+					row_value = sheet.createRow(n + 1);
+					row_value.setHeight(height.shortValue());
+					dataMap = dataList.get(n);
+					// ===============================================================
+					for (int i = 0; i < fieldList.length; i++) {
+						// 鍦ㄧ储寮�0鐨勪綅缃垱寤哄崟鍏冩牸锛堝乏涓婄锛�
+						sheet.setColumnWidth(i,width);
+						cell = row_value.createCell(i);
+						// 瀹氫箟鍗曞厓鏍间负瀛楃涓茬被鍨�
+						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
+						// 鍦ㄥ崟鍏冩牸涓緭鍏ヤ竴浜涘唴瀹�
+						cell.setCellValue(objToString(dataMap.get(fieldList[i])));
+						cell.setCellStyle(boderStyle);
 					}
 					// ===============================================================
 				}
@@ -171,10 +359,34 @@
 				return (String) obj;
 			} else if (obj instanceof Date) {
 				return null;// DateUtil.dateToString((Date)
-							// obj,DateUtil.DATESTYLE_SHORT_EX);
+				// obj,DateUtil.DATESTYLE_SHORT_EX);
 			} else {
 				return obj.toString();
 			}
+		}
+	}
+
+	/**
+	 * 璇诲彇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);
 		}
 	}
 
@@ -254,7 +466,11 @@
 							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();
 						}
 					}
@@ -265,4 +481,610 @@
 		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();
+						}
+					}
+					list.add(arrayString);
+				}
+			}
+		}
+		return list;
+	}
+
+
+	/**鍒ゆ柇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瑙ｆ瀽涓嶄簡");
+	}
+
+	/**璇诲彇excel鏂囦欢锛屽吋瀹�2003鍜�2007
+	 * 閫氳繃娴佽鍙朎xcel鏂囦欢
+	 * @return
+	 * @throws Exception
+	 */
+	public static List<List<String>> getExcelDataCompatible(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琛ㄧず宸ヤ綔琛ㄧ殑鎬绘暟
+			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);
+										}
+									}
+								}
+							} else if(cell.getCellType() == Cell.CELL_TYPE_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>> getExcelDataCompatible(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琛ㄧず宸ヤ綔琛ㄧ殑鎬绘暟
+			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);
+										}
+									}
+								}
+							} else if(cell.getCellType() == Cell.CELL_TYPE_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;
+				// 閬嶅巻璇ヨ鎵�鏈夌殑琛�,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 "";
+		}
+		switch (cell.getCellType()) {
+			// 鏁板瓧
+			case HSSFCell.CELL_TYPE_NUMERIC:
+				// 鏃ユ湡鏍煎紡鐨勫鐞�
+				if (HSSFDateUtil.isCellDateFormatted(cell)) {
+					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
+					return sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())).toString();
+				}
+				return String.valueOf(cell.getNumericCellValue());
+			// 瀛楃涓�
+			case HSSFCell.CELL_TYPE_STRING:
+				return cell.getStringCellValue();
+			// 鍏紡
+			case HSSFCell.CELL_TYPE_FORMULA:
+				return cell.getCellFormula();
+			// 绌虹櫧
+			case HSSFCell.CELL_TYPE_BLANK:
+				return "";
+			case HSSFCell.CELL_TYPE_BOOLEAN:
+				return cell.getBooleanCellValue() + "";
+			// 閿欒绫诲瀷
+			case HSSFCell.CELL_TYPE_ERROR:
+				return cell.getErrorCellValue() + "";
+			default:
+				break;
+		}
+		return "";
+	}
+
+	/**
+	 * p鍒ゆ柇鏄惁excel鏂囦欢
+	 * @param file
+	 * @return
+	 */
+	public static boolean isExcel(MultipartFile file){
+		String fileName = file.getOriginalFilename();
+		if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
+			return false;
+		}
+		return true;
+	}
+
+
+
+
+
+	public static File createExcelByImg(String[] headList, String[] fieldList, List<Map<String, Object>> dataList, Integer height, Integer width) throws Exception {
+		File file = File.createTempFile("temp", ".xls");
+		FileOutputStream fileOut = null;
+		BufferedImage bufferImg = null;
+
+		try {
+			ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
+			if (height == null) {
+				height = 450;
+			}
+
+			if (width == null) {
+				width = 1000;
+			}
+
+			HSSFWorkbook workbook = new HSSFWorkbook();
+			HSSFCellStyle boderStyle = workbook.createCellStyle();
+			boderStyle.setVerticalAlignment((short)1);
+			boderStyle.setAlignment((short)2);
+			HSSFSheet sheet = workbook.createSheet();
+			HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
+			HSSFRow row = sheet.createRow(0);
+
+			HSSFCell anchor;
+			for(int i = 0; i < headList.length; ++i) {
+				row.setHeight(height.shortValue());
+				sheet.setColumnWidth(i, width);
+				anchor = row.createCell(i);
+				anchor.setCellType(1);
+				anchor.setCellValue(headList[i]);
+				anchor.setCellStyle(boderStyle);
+			}
+
+			HSSFRow row_value = null;
+			anchor = null;
+			HSSFCell cell = null;
+			if (dataList != null) {
+				for(int n = 0; n < dataList.size(); ++n) {
+					row_value = sheet.createRow(n + 1);
+					row_value.setHeight(height.shortValue());
+					Map<String, Object> dataMap = (Map)dataList.get(n);
+
+					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;
+							if (file2 == null) {
+								cell.setCellValue("");
+							} else {
+								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);
+								patriarch.createPicture(anchor1, workbook.addPicture(byteArrayOut.toByteArray(), 5));
+							}
+						} else {
+							cell.setCellValue(objToString(dataMap.get(fieldList[i])));
+							cell.setCellStyle(boderStyle);
+						}
+					}
+				}
+			}
+
+			FileOutputStream fOut = new FileOutputStream(file);
+			workbook.write(fOut);
+			fOut.flush();
+			fOut.close();
+		} catch (Exception var25) {
+			var25.printStackTrace();
+		} finally {
+			file.deleteOnExit();
+		}
+
+		return file;
+	}
+}
\ No newline at end of file

--
Gitblit v1.8.0