From cd78e3c1f7ae98f686c98322547f489de51fa8a7 Mon Sep 17 00:00:00 2001
From: chenjiahe <763432473@qq.com>
Date: 星期一, 04 九月 2023 15:34:38 +0800
Subject: [PATCH] 新版excel
---
src/main/java/com/hx/util/ExcelUtil.java | 350 ++++++++++++++++++++++++++++-----------------------------
1 files changed, 172 insertions(+), 178 deletions(-)
diff --git a/src/main/java/com/hx/util/ExcelUtil.java b/src/main/java/com/hx/util/ExcelUtil.java
index 07abd29..8418213 100644
--- a/src/main/java/com/hx/util/ExcelUtil.java
+++ b/src/main/java/com/hx/util/ExcelUtil.java
@@ -1,14 +1,15 @@
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.*;
-import org.apache.poi.ooxml.POIXMLDocument;
-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.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 javax.imageio.ImageIO;
@@ -678,9 +679,6 @@
return null;
}
-
-
-
/**璇诲彇excel鏂囦欢锛屽吋瀹�2003鍜�2007
* 閫氳繃娴佽鍙朎xcel鏂囦欢
* @return
@@ -782,27 +780,27 @@
* @return
* @throws Exception
*/
- public static List<List<String>> getExcelDataCompatibleCheckEmpty(File file,boolean header) throws Exception {
+ public static List<List<String>> getExcelDataCompatible(InputStream inputStream,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)));
+ 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琛ㄧず宸ヤ綔琛ㄧ殑鎬绘暟
- //涓嶄负绌�
- boolean isNotEmpty = false;
for(int s=0;s<book.getNumberOfSheets();s++) {
- Sheet hssfsheet = book.getSheetAt(s);
+ hssfsheet = book.getSheetAt(s);
int col = 0;
// 閬嶅巻璇ヨ鎵�鏈夌殑琛�,j琛ㄧず琛屾暟 getPhysicalNumberOfRows琛岀殑鎬绘暟 鍘婚櫎鏍囬
for (int j = 0; j < hssfsheet.getPhysicalNumberOfRows(); j++) {
- Row hssfrow = hssfsheet.getRow(j);
+ hssfrow = hssfsheet.getRow(j);
if(hssfrow!=null){
if(j == 0) {
col = hssfrow.getPhysicalNumberOfCells();
@@ -812,26 +810,26 @@
}
}
// 鍗曡鏁版嵁
- List<String> arrayString = new ArrayList<>();
+ arrayString = new ArrayList<>();
for (int i = 0; i < col; i++) {
- Cell cell = hssfrow.getCell(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()) {
- short format = cell.getCellStyle().getDataFormat();
+ 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");
+ 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)) {
- Date d = cell.getDateCellValue();
+ d = cell.getDateCellValue();
//DateFormat formater = new SimpleDateFormat("yyyy骞碝M鏈坉d鏃� HH鏃秏m鍒唖s绉�");
- DateFormat formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
+ formater = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
arrayString.add(formater.format(d));
//arrayString[i] = formater.format(d);
} else {
@@ -842,25 +840,21 @@
arrayString.add(cell.getCellFormula());
//arrayString[i] =cell.getCellFormula();
}else if(CellType.NUMERIC== cell.getCellType()){
- HSSFDataFormatter dataFormatter = new HSSFDataFormatter();
+ dataFormatter = new HSSFDataFormatter();
arrayString.add(dataFormatter.formatCellValue(cell));
//arrayString[i] =dataFormatter.formatCellValue(cell);
}
}
}
- isNotEmpty = true;
} else if(cell.getCellType() == CellType.BLANK){
arrayString.add("");
//arrayString[i] = "";
} else { // 濡傛灉EXCEL琛ㄦ牸涓殑鏁版嵁绫诲瀷涓哄瓧绗︿覆鍨�
arrayString.add(cell.getStringCellValue().trim());
//arrayString[i] = cell.getStringCellValue().trim();
- isNotEmpty = true;
}
}
- if (isNotEmpty){
- list.add(arrayString);
- }
+ list.add(arrayString);
}
}
}
@@ -871,135 +865,144 @@
return null;
}
-
- /**璇诲彇excel鏂囦欢锛屽吋瀹�2003鍜�2007
- * 閫氳繃娴佽鍙朎xcel鏂囦欢
- * @return
- * @throws Exception
+ /**
+ * 鏂扮増璇诲彇Excel锛屽彧鏀寔2007浠ヤ笂鐗堟湰锛屼篃灏辨槸xslx鏍煎紡
+ * 鏀寔澶ф暟鎹噺
+ * @param file 鏂囦欢
+ * @return 鏁版嵁
*/
- 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() == CellType.NUMERIC) {
- // arrayString[i] = new Double(cell.getNumericCellValue()).toString();
- if (CellType.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(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()){
- HSSFDataFormatter dataFormatter = new HSSFDataFormatter();
- arrayString.add(dataFormatter.formatCellValue(cell));
- //arrayString[i] =dataFormatter.formatCellValue(cell);
- }
- }
- }
- isNotEmpty = true;
- } else if(cell.getCellType() == CellType.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();
+ public static List<List<String>> readExcelData(File file){
+ if(!isExcel(file)){
+ throw new TipsException("璇蜂笂浼爀xcel鐨勬枃浠舵牸寮忥紒");
}
- return null;
+ List<List<String>> listData = new ArrayList<>();
+ try{
+ //rowCacheSize 缂撳瓨鍒板唴瀛樹腑鐨勮鏁�(榛樿鏄�10)
+ //bufferSize 璇诲彇璧勬簮鏃讹紝缂撳瓨鍒板唴瀛樼殑瀛楄妭澶у皬(榛樿鏄�1024)
+ //open InputStream鎴栬�匵LSX鏍煎紡鐨凢ile(蹇呴』)
+ Workbook book = StreamingReader.builder()
+ .rowCacheSize(100)
+ .bufferSize(10240)
+ .open(new FileInputStream(file));
+ listData =readhandle(book);
+ }catch (Exception e){
+ e.printStackTrace();
+ throw new RuntimeException(e.getMessage());
+ }
+ return listData;
}
+ /**
+ * 鏂扮増璇诲彇Excel锛屽彧鏀寔2007浠ヤ笂鐗堟湰锛屼篃灏辨槸xslx鏍煎紡
+ * 鏀寔澶ф暟鎹噺
+ * @param file 鏂囦欢
+ * @return 鏁版嵁
+ */
+ public static List<List<String>> readExcelData(MultipartFile file){
+ if(!isExcel(file)){
+ throw new TipsException("璇蜂笂浼爀xcel鐨勬枃浠舵牸寮忥紒");
+ }
+ List<List<String>> listData = new ArrayList<>();
+ try{
+ //rowCacheSize 缂撳瓨鍒板唴瀛樹腑鐨勮鏁�(榛樿鏄�10)
+ //bufferSize 璇诲彇璧勬簮鏃讹紝缂撳瓨鍒板唴瀛樼殑瀛楄妭澶у皬(榛樿鏄�1024)
+ //open InputStream鎴栬�匵LSX鏍煎紡鐨凢ile(蹇呴』)
+ Workbook book = StreamingReader.builder()
+ .rowCacheSize(100)
+ .bufferSize(10240)
+ .open(new BufferedInputStream(file.getInputStream()));
- /*private static String getCellVal(Cell cell) {
- if (null == cell) {
- return "";
+ listData =readhandle(book);
+ }catch (Exception e){
+ e.printStackTrace();
+ throw new RuntimeException(e.getMessage());
}
- switch (cell.getCellType()) {
- // 鏁板瓧
- case CellType.NUMERIC:
- // 鏃ユ湡鏍煎紡鐨勫鐞�
- if (HSSFDateUtil.isCellDateFormatted(cell)) {
- SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
- return sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue())).toString();
+ 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;
+ //閬嶅巻鎵�鏈夌殑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());
+ }
}
- return String.valueOf(cell.getNumericCellValue());
- // 瀛楃涓�
- case CellType.STRING:
- return cell.getStringCellValue();
- // 鍏紡
- case CellType.FORMULA:
- return cell.getCellFormula();
- // 绌虹櫧
- case CellType.BLANK:
- return "";
- case CellType.BOOLEAN:
- return cell.getBooleanCellValue() + "";
- // 閿欒绫诲瀷
- case CellType.ERROR:
- return cell.getErrorCellValue() + "";
- default:
- break;
+ if (isData) {
+ listData.add(arrayString);
+ }
+ }
}
- return "";
- }*/
+ return listData;
+ }
+
/**
* p鍒ゆ柇鏄惁excel鏂囦欢
@@ -1007,16 +1010,27 @@
* @return
*/
public static boolean isExcel(MultipartFile file){
- String fileName = file.getOriginalFilename();
+ return isExcel(file.getOriginalFilename());
+ }
+
+
+ /**
+ * p鍒ゆ柇鏄惁excel鏂囦欢
+ * @param file
+ * @return
+ */
+ public static boolean isExcel(File file){
+ return isExcel(file.getName());
+ }
+
+
+ /**鍒ゆ柇鏂囦欢鏍煎紡鏄笉鏄痚xcel*/
+ public static boolean isExcel(String fileName){
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");
@@ -1093,26 +1107,6 @@
}
return file;
- }
- public static void main(String[] args) throws Exception {
- //File file = new File("E:\\360\\a701aae8-9d4b-49f1-8999-1456fa53ef03 (1).xlsx");
- File file = new File("E:\\360\\tttt (1).xls");
- //Workbook wk = create(new FileInputStream(file));
- Workbook wk = StreamingReader.builder()
- .rowCacheSize(100) // 缂撳瓨鍒板唴瀛樹腑鐨勮鏁�(榛樿鏄�10)
- .bufferSize(4096) // 璇诲彇璧勬簮鏃讹紝缂撳瓨鍒板唴瀛樼殑瀛楄妭澶у皬(榛樿鏄�1024)
- .open(new FileInputStream(file)); // InputStream鎴栬�匵LSX鏍煎紡鐨凢ile(蹇呴』)
-
- Sheet sheet = wk.getSheetAt(0);
- //閬嶅巻鎵�鏈夌殑琛�
- for (Row row : sheet) {
- System.out.println("寮�濮嬮亶鍘嗙" + row.getRowNum() + "琛屾暟鎹細");
- //閬嶅巻鎵�鏈夌殑鍒�
- for (Cell cell : row) {
- System.out.print(cell.getStringCellValue() + " ");
- }
- System.out.println(" ");
- }
}
}
\ No newline at end of file
--
Gitblit v1.8.0