From c6eec82a790b6dc9c1d74556a52cd891a2017e8c Mon Sep 17 00:00:00 2001
From: fhx <308050795@qq.com>
Date: 星期一, 19 七月 2021 15:21:29 +0800
Subject: [PATCH] 1.修改区分Excel不同格式读取数据问题
---
src/main/java/com/hx/util/ExcelUtil.java | 129 +++++++++++++++++++++++++++++++++++++++++--
1 files changed, 123 insertions(+), 6 deletions(-)
diff --git a/src/main/java/com/hx/util/ExcelUtil.java b/src/main/java/com/hx/util/ExcelUtil.java
index 88fe7f1..9bd0712 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
@@ -351,6 +381,93 @@
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 (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;
+ }
+
/**鍒ゆ柇excel鐨勭増鏈�*/
public static Workbook create(InputStream inp) throws Exception {
--
Gitblit v1.8.0