From 756acbd7d971fbeebe224bdedfdd1a3e0a195dc2 Mon Sep 17 00:00:00 2001
From: chenjiahe <763432473@qq.com>
Date: 星期一, 18 九月 2023 15:10:36 +0800
Subject: [PATCH] 新版excel优化
---
src/main/java/com/hx/util/ExcelUtil.java | 297 +++++++++++++++++++++++++++++++++++++---------------------
1 files changed, 188 insertions(+), 109 deletions(-)
diff --git a/src/main/java/com/hx/util/ExcelUtil.java b/src/main/java/com/hx/util/ExcelUtil.java
index 463d446..57bc636 100644
--- a/src/main/java/com/hx/util/ExcelUtil.java
+++ b/src/main/java/com/hx/util/ExcelUtil.java
@@ -1,6 +1,5 @@
package com.hx.util;
-import com.alibaba.fastjson.JSON;
import com.hx.exception.TipsException;
import com.monitorjbl.xlsx.StreamingReader;
import org.apache.poi.hssf.usermodel.*;
@@ -679,9 +678,6 @@
return null;
}
-
-
-
/**璇诲彇excel鏂囦欢锛屽吋瀹�2003鍜�2007
* 閫氳繃娴佽鍙朎xcel鏂囦欢
* @return
@@ -778,9 +774,99 @@
return null;
}
+ /**璇诲彇excel鏂囦欢锛屽吋瀹�2003鍜�2007
+ * 閫氳繃娴佽鍙朎xcel鏂囦欢
+ * @return
+ * @throws Exception
+ */
+ public static List<List<String>> getExcelDataCompatible(InputStream inputStream,boolean header) throws Exception {
+ try {
+ // 缁撴灉闆�
+ List<List<String>> list = new ArrayList<>();
+ Workbook book = create(new BufferedInputStream(inputStream));
+
+ Sheet hssfsheet;
+ Row hssfrow;
+ List<String> arrayString;
+ Cell cell;
+ short format;
+ Date d;
+ DateFormat formater;
+ HSSFDataFormatter dataFormatter;
+ // 閬嶅巻璇ヨ〃鏍间腑鎵�鏈夌殑宸ヤ綔琛紝i琛ㄧず宸ヤ綔琛ㄧ殑鏁伴噺 getNumberOfSheets琛ㄧず宸ヤ綔琛ㄧ殑鎬绘暟
+ for(int s=0;s<book.getNumberOfSheets();s++) {
+ hssfsheet = book.getSheetAt(s);
+ int col = 0;
+ // 閬嶅巻璇ヨ鎵�鏈夌殑琛�,j琛ㄧず琛屾暟 getPhysicalNumberOfRows琛岀殑鎬绘暟 鍘婚櫎鏍囬
+ for (int j = 0; j < hssfsheet.getPhysicalNumberOfRows(); j++) {
+ hssfrow = hssfsheet.getRow(j);
+ if(hssfrow!=null){
+ if(j == 0) {
+ col = hssfrow.getPhysicalNumberOfCells();
+ if(!header) {
+ //涓嶅寘鎷〃澶�
+ continue;
+ }
+ }
+ // 鍗曡鏁版嵁
+ arrayString = new ArrayList<>();
+ for (int i = 0; i < col; i++) {
+ cell = hssfrow.getCell(i);
+ if (cell == null) {
+ arrayString.add("");
+ } else if (cell.getCellType() == CellType.NUMERIC) {
+ // arrayString[i] = new Double(cell.getNumericCellValue()).toString();
+ if (CellType.NUMERIC == cell.getCellType()) {
+ format = cell.getCellStyle().getDataFormat();
+ if(format == 14 || format == 31 || format == 57 || format == 58){
+ //鏃ユ湡锛堜腑鏂囨椂闂存牸寮忕殑锛�
+ d = cell.getDateCellValue();
+ 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)) {
+ d = cell.getDateCellValue();
+ //DateFormat formater = new SimpleDateFormat("yyyy骞碝M鏈坉d鏃� HH鏃秏m鍒唖s绉�");
+ formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
+ arrayString.add(formater.format(d));
+ //arrayString[i] = formater.format(d);
+ } else {
+ if(CellType.STRING == cell.getCellType()){
+ arrayString.add(cell.getStringCellValue());
+ //arrayString[i] =cell.getStringCellValue();
+ }else if(CellType.FORMULA==cell.getCellType()){
+ arrayString.add(cell.getCellFormula());
+ //arrayString[i] =cell.getCellFormula();
+ }else if(CellType.NUMERIC== cell.getCellType()){
+ dataFormatter = new HSSFDataFormatter();
+ arrayString.add(dataFormatter.formatCellValue(cell));
+ //arrayString[i] =dataFormatter.formatCellValue(cell);
+ }
+ }
+ }
+ } else if(cell.getCellType() == CellType.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锛屽彧鏀寔2007浠ヤ笂鐗堟湰锛屼篃灏辨槸xslx鏍煎紡
+ * 鏀寔澶ф暟鎹噺
* @param file 鏂囦欢
* @return 鏁版嵁
*/
@@ -795,60 +881,9 @@
//open InputStream鎴栬�匵LSX鏍煎紡鐨凢ile(蹇呴』)
Workbook book = StreamingReader.builder()
.rowCacheSize(100)
- .bufferSize(4096)
+ .bufferSize(10240)
.open(new FileInputStream(file));
-
- //鏄惁瀛樺湪鏁版嵁
- boolean isData;
- List<String> arrayString;
- short format;
- Date d;
- DateFormat formater;
- Sheet sheet;
- //閬嶅巻鎵�鏈夌殑sheet
- for(int i=0;i<book.getNumberOfSheets();i++){
- sheet = book.getSheetAt(i);
- //閬嶅巻鎵�鏈夌殑琛�
- for (Row row : sheet) {
- isData = false;
- arrayString = new ArrayList<>();
- //閬嶅巻鎵�鏈夌殑鍒�
- for (Cell cell : row) {
- if (cell == null) {
- arrayString.add("");
- } else if (cell.getCellType() == CellType.NUMERIC) {
- isData = true;
- format = cell.getCellStyle().getDataFormat();
- if(format == 14 || format == 31 || format == 57 || format == 58){
- //鏃ユ湡锛堜腑鏂囨椂闂存牸寮忕殑锛�
- d = cell.getDateCellValue();
- formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
- arrayString.add(formater.format(d));
- }else if (HSSFDateUtil.isCellDateFormatted(cell)) {
- d = cell.getDateCellValue();
- formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
- arrayString.add(formater.format(d));
- } else {
- if(CellType.STRING == cell.getCellType()){
- arrayString.add(cell.getStringCellValue());
- }else if(CellType.FORMULA==cell.getCellType()){
- arrayString.add(cell.getCellFormula());
- }else{
- arrayString.add(cell.getStringCellValue().trim());
- }
- }
- } else if(cell.getCellType() == CellType.BLANK){
- arrayString.add("");
- } else { // 濡傛灉EXCEL琛ㄦ牸涓殑鏁版嵁绫诲瀷涓哄瓧绗︿覆鍨�
- isData = true;
- arrayString.add(cell.getStringCellValue().trim());
- }
- }
- if(isData){
- listData.add(arrayString);
- }
- }
- }
+ listData =readhandle(book);
}catch (Exception e){
e.printStackTrace();
throw new RuntimeException(e.getMessage());
@@ -858,6 +893,7 @@
/**
* 鏂扮増璇诲彇Excel锛屽彧鏀寔2007浠ヤ笂鐗堟湰锛屼篃灏辨槸xslx鏍煎紡
+ * 鏀寔澶ф暟鎹噺
* @param file 鏂囦欢
* @return 鏁版嵁
*/
@@ -872,60 +908,10 @@
//open InputStream鎴栬�匵LSX鏍煎紡鐨凢ile(蹇呴』)
Workbook book = StreamingReader.builder()
.rowCacheSize(100)
- .bufferSize(4096)
+ .bufferSize(10240)
.open(new BufferedInputStream(file.getInputStream()));
- //鏄惁瀛樺湪鏁版嵁
- boolean isData;
- List<String> arrayString;
- short format;
- Date d;
- DateFormat formater;
- Sheet sheet;
- //閬嶅巻鎵�鏈夌殑sheet
- for(int i=0;i<book.getNumberOfSheets();i++){
- sheet = book.getSheetAt(i);
- //閬嶅巻鎵�鏈夌殑琛�
- for (Row row : sheet) {
- isData = false;
- arrayString = new ArrayList<>();
- //閬嶅巻鎵�鏈夌殑鍒�
- for (Cell cell : row) {
- if (cell == null) {
- arrayString.add("");
- } else if (cell.getCellType() == CellType.NUMERIC) {
- isData = true;
- format = cell.getCellStyle().getDataFormat();
- if(format == 14 || format == 31 || format == 57 || format == 58){
- //鏃ユ湡锛堜腑鏂囨椂闂存牸寮忕殑锛�
- d = cell.getDateCellValue();
- formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
- arrayString.add(formater.format(d));
- }else if (HSSFDateUtil.isCellDateFormatted(cell)) {
- d = cell.getDateCellValue();
- formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
- arrayString.add(formater.format(d));
- } else {
- if(CellType.STRING == cell.getCellType()){
- arrayString.add(cell.getStringCellValue());
- }else if(CellType.FORMULA==cell.getCellType()){
- arrayString.add(cell.getCellFormula());
- }else{
- arrayString.add(cell.getStringCellValue().trim());
- }
- }
- } else if(cell.getCellType() == CellType.BLANK){
- arrayString.add("");
- } else { // 濡傛灉EXCEL琛ㄦ牸涓殑鏁版嵁绫诲瀷涓哄瓧绗︿覆鍨�
- isData = true;
- arrayString.add(cell.getStringCellValue().trim());
- }
- }
- if(isData){
- listData.add(arrayString);
- }
- }
- }
+ listData =readhandle(book);
}catch (Exception e){
e.printStackTrace();
throw new RuntimeException(e.getMessage());
@@ -933,6 +919,99 @@
return listData;
}
+ /**
+ * 鏂扮増璇诲彇Excel锛屽彧鏀寔2007浠ヤ笂鐗堟湰锛屼篃灏辨槸xslx鏍煎紡
+ * 鏀寔澶ф暟鎹噺
+ * @param file 鏂囦欢
+ * @return 鏁版嵁
+ */
+ public static List<List<String>> readExcelData(InputStream file){
+ List<List<String>> listData = new ArrayList<>();
+ try{
+ //rowCacheSize 缂撳瓨鍒板唴瀛樹腑鐨勮鏁�(榛樿鏄�10)
+ //bufferSize 璇诲彇璧勬簮鏃讹紝缂撳瓨鍒板唴瀛樼殑瀛楄妭澶у皬(榛樿鏄�1024)
+ //open InputStream鎴栬�匵LSX鏍煎紡鐨凢ile(蹇呴』)
+ Workbook book = StreamingReader.builder()
+ .rowCacheSize(100)
+ .bufferSize(10240)
+ .open(file);
+
+ listData =readhandle(book);
+ }catch (Exception e){
+ e.printStackTrace();
+ throw new RuntimeException(e.getMessage());
+ }
+ return listData;
+ }
+
+ /**澶勭悊鏁版嵁*/
+ public static List<List<String>> readhandle(Workbook book){
+ List<List<String>> listData = new ArrayList<>();
+
+ //鏄惁瀛樺湪鏁版嵁
+ boolean isData;
+ List<String> arrayString;
+ short format;
+ Date d;
+ DateFormat formater;
+ Sheet sheet;
+ Cell cell;
+ //閬嶅巻鎵�鏈夌殑sheet
+ for(int i=0;i<book.getNumberOfSheets();i++) {
+ sheet = book.getSheetAt(i);
+ //鍒楁暟
+ Integer arrange = null;
+ //閬嶅巻鎵�鏈夌殑琛�
+ for (Row row : sheet) {
+ if(row == null){
+ continue;
+ }
+ isData = false;
+ arrayString = new ArrayList<>();
+ if(arrange == null){
+ arrange = row.getPhysicalNumberOfCells();
+ }
+ //閬嶅巻鎵�鏈夌殑鍒�
+ for (int j = 0;j<arrange;j++) {
+ cell = row.getCell(j);
+ if (cell == null) {
+ arrayString.add("");
+ } else if (cell.getCellType() == CellType.NUMERIC) {
+ isData = true;
+ format = cell.getCellStyle().getDataFormat();
+ if (format == 14 || format == 31 || format == 57 || format == 58) {
+ //鏃ユ湡锛堜腑鏂囨椂闂存牸寮忕殑锛�
+ d = cell.getDateCellValue();
+ formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
+ arrayString.add(formater.format(d));
+ } else if (HSSFDateUtil.isCellDateFormatted(cell)) {
+ d = cell.getDateCellValue();
+ formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
+ arrayString.add(formater.format(d));
+ } else {
+ if (CellType.STRING == cell.getCellType()) {
+ arrayString.add(cell.getStringCellValue());
+ } else if (CellType.FORMULA == cell.getCellType()) {
+ arrayString.add(cell.getCellFormula());
+ } else {
+ arrayString.add(cell.getStringCellValue().trim());
+ }
+ }
+ } else if (cell.getCellType() == CellType.BLANK) {
+ arrayString.add("");
+ } else { // 濡傛灉EXCEL琛ㄦ牸涓殑鏁版嵁绫诲瀷涓哄瓧绗︿覆鍨�
+ isData = true;
+ arrayString.add(cell.getStringCellValue().trim());
+ }
+ }
+ if (isData) {
+ listData.add(arrayString);
+ }
+ }
+ }
+ return listData;
+ }
+
/**
* p鍒ゆ柇鏄惁excel鏂囦欢
--
Gitblit v1.8.0