From cac339cb773b12721bc2c9886d5ab7ed9753f245 Mon Sep 17 00:00:00 2001
From: ChenJiaHe <763432473@qq.com>
Date: 星期三, 24 二月 2021 17:34:33 +0800
Subject: [PATCH] 读取excel文件版本兼容
---
src/main/java/com/hx/util/ExcelUtil.java | 167 ++++++++++++++++++++++++++++++++++++++---
pom.xml | 5 +
hx-common.iml | 10 ++
3 files changed, 167 insertions(+), 15 deletions(-)
diff --git a/hx-common.iml b/hx-common.iml
index c68fa87..ad4fe96 100644
--- a/hx-common.iml
+++ b/hx-common.iml
@@ -19,8 +19,16 @@
</content>
<orderEntry type="inheritedJdk" />
<orderEntry type="sourceFolder" forTests="false" />
- <orderEntry type="library" name="Maven: com.qcloud:cos_api:5.6.32" level="project" />
<orderEntry type="library" name="Maven: com.qcloud:cos_api:5.6.24" level="project" />
+ <orderEntry type="library" name="Maven: javax.xml.bind:jaxb-api:2.3.1" level="project" />
+ <orderEntry type="library" name="Maven: javax.activation:javax.activation-api:1.2.0" level="project" />
+ <orderEntry type="library" name="Maven: org.apache.poi:poi-ooxml:3.9" level="project" />
+ <orderEntry type="library" name="Maven: org.apache.poi:poi-ooxml-schemas:3.9" level="project" />
+ <orderEntry type="library" name="Maven: org.apache.xmlbeans:xmlbeans:2.3.0" level="project" />
+ <orderEntry type="library" name="Maven: stax:stax-api:1.0.1" level="project" />
+ <orderEntry type="library" name="Maven: dom4j:dom4j:2.1.3" level="project" />
+ <orderEntry type="library" name="Maven: xml-apis:xml-apis:1.4.01" level="project" />
+ <orderEntry type="library" name="Maven: com.qcloud:cos_api:5.6.32" level="project" />
<orderEntry type="library" name="Maven: org.slf4j:slf4j-api:1.7.25" level="project" />
<orderEntry type="library" name="Maven: commons-codec:commons-codec:1.11" level="project" />
<orderEntry type="library" name="Maven: joda-time:joda-time:2.9.9" level="project" />
diff --git a/pom.xml b/pom.xml
index 7a164ce..cf34a6b 100644
--- a/pom.xml
+++ b/pom.xml
@@ -21,6 +21,11 @@
<artifactId>jaxb-api</artifactId>
</dependency>
+ <dependency>
+ <groupId>org.apache.poi</groupId>
+ <artifactId>poi-ooxml</artifactId>
+ <version>3.9</version>
+ </dependency>
<!--鑵捐浜� cos-->
<dependency>
diff --git a/src/main/java/com/hx/util/ExcelUtil.java b/src/main/java/com/hx/util/ExcelUtil.java
index 1191dcd..206d154 100644
--- a/src/main/java/com/hx/util/ExcelUtil.java
+++ b/src/main/java/com/hx/util/ExcelUtil.java
@@ -1,24 +1,25 @@
package com.hx.util;
-import java.io.File;
-import java.io.FileInputStream;
-import java.io.FileOutputStream;
+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 java.util.*;
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.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.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;
/**
- *
+ *
* @author hjr
*/
public final class ExcelUtil {
@@ -37,8 +38,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 宸ヤ綔绨�
@@ -106,7 +107,7 @@
* @throws Exception
*/
public static File createExcel(String[] headList, String[] fieldList,
- List<Map<String, Object>> dataList) throws Exception {
+ List<Map<String, Object>> dataList) throws Exception {
File file = File.createTempFile("temp", ".xls");
try{
// 鍒涘缓鏂扮殑Excel 宸ヤ綔绨�
@@ -256,7 +257,7 @@
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();
}
@@ -350,4 +351,142 @@
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(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<>();
+
+ Map<String, List<List<Map<Integer,String>>>> data = new HashMap<String, List<List<Map<Integer,String>>>>();
+ 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;
+ }
+
+ 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 "";
+ }
+
+}
\ No newline at end of file
--
Gitblit v1.8.0