From 30e858fa504b268b9b436afca0a1259cf6e8c488 Mon Sep 17 00:00:00 2001
From: MrShi <1878285526@qq.com>
Date: 星期二, 19 八月 2025 11:01:40 +0800
Subject: [PATCH] 优化
---
server/src/main/java/com/doumee/core/annotation/excel/ExcelImporter.java | 504 ++++++++++++++++++++++++++++++++++++++-----------------
1 files changed, 343 insertions(+), 161 deletions(-)
diff --git a/server/src/main/java/com/doumee/core/annotation/excel/ExcelImporter.java b/server/src/main/java/com/doumee/core/annotation/excel/ExcelImporter.java
index ea4ee4d..f8f067c 100644
--- a/server/src/main/java/com/doumee/core/annotation/excel/ExcelImporter.java
+++ b/server/src/main/java/com/doumee/core/annotation/excel/ExcelImporter.java
@@ -1,212 +1,394 @@
package com.doumee.core.annotation.excel;
-import com.doumee.core.constants.ResponseStatus;
-import com.doumee.core.exception.BusinessException;
-import lombok.AllArgsConstructor;
-import lombok.Data;
import org.apache.commons.lang3.StringUtils;
+import org.apache.poi.hssf.usermodel.HSSFWorkbook;
+import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
-import org.springframework.core.annotation.AnnotationConfigurationException;
+import org.slf4j.Logger;
+import org.slf4j.LoggerFactory;
+import org.springframework.web.multipart.MultipartFile;
+import java.io.File;
+import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
-import java.lang.reflect.Type;
-import java.util.ArrayList;
-import java.util.List;
-import java.util.Map;
-import java.util.TreeMap;
+import java.util.*;
/**
- * Excel瀵煎叆瀹炵幇
- * @author dm
- * @since 2025/03/31 16:44
+ * 瀵煎叆Excel鏂囦欢锛堟敮鎸佲�淴LS鈥濆拰鈥淴LSX鈥濇牸寮忥級
*/
-public class ExcelImporter<T> {
+public class ExcelImporter {
- private Class<T> modelClass;
-
- private ExcelImporter () {}
+ private static Logger log = LoggerFactory.getLogger(ExcelImporter.class);
/**
- * 鏋勯�燛xcelImporter瀵硅薄
- *
- * @param modelClass 瀹炰綋Class瀵硅薄
- * @return ExcelImporter瀹炰緥
+ * 宸ヤ綔钖勫璞�
*/
- public static <T> ExcelImporter<T> build(Class<T> modelClass) {
- ExcelImporter<T> excelImporter = new ExcelImporter<>();
- excelImporter.modelClass = modelClass;
- return excelImporter;
+ private Workbook wb;
+
+ /**
+ * 宸ヤ綔琛ㄥ璞�
+ */
+ private Sheet sheet;
+
+ /**
+ * 鏍囬琛屽彿
+ */
+ private int headerNum;
+ /**
+ * 鏍囬琛屽彿
+ */
+ private CellType changeType;
+
+ /**
+ * 鏋勯�犲嚱鏁�
+ * @param--path 瀵煎叆鏂囦欢锛岃鍙栫涓�涓伐浣滆〃
+ * @param headerNum 鏍囬琛屽彿锛屾暟鎹鍙�=鏍囬琛屽彿+1
+ * @throws InvalidFormatException
+ * @throws IOException
+ */
+ public ExcelImporter(String fileName, int headerNum)
+ throws InvalidFormatException, IOException {
+ this(new File(fileName), headerNum);
}
/**
- * 瀵煎叆鏁版嵁
- *
- * @param is 杈撳叆娴�
- * @param callback 鍥炶皟
- * @param sync 鏄惁鍚屾宸插瓨鍦ㄦ暟鎹�
- * @return 瀵煎叆鎴愬姛鏁�
+ * 鏋勯�犲嚱鏁�
+ * @param--path 瀵煎叆鏂囦欢瀵硅薄锛岃鍙栫涓�涓伐浣滆〃
+ * @param headerNum 鏍囬琛屽彿锛屾暟鎹鍙�=鏍囬琛屽彿+1 (eg: 1)
+ * @throws InvalidFormatException
+ * @throws IOException
*/
- public int importData (InputStream is, ExcelImportCallback<T> callback, boolean sync) {
- return this.importData(is, 0, callback, sync);
+ public ExcelImporter(File file, int headerNum)
+ throws InvalidFormatException, IOException {
+ this(file, headerNum, 0);
}
/**
- * 瀵煎叆鏁版嵁
- *
- * @param is 杈撳叆娴�
- * @param sheetIndex sheet鍧愭爣
- * @param callback 鍥炶皟
- * @param sync 鏄惁鍚屾宸插瓨鍦ㄦ暟鎹�
- * @return 瀵煎叆鎴愬姛鏁�
+ * 鏋勯�犲嚱鏁�
+ * @param--path 瀵煎叆鏂囦欢
+ * @param headerNum 鏍囬琛屽彿锛屾暟鎹鍙�=鏍囬琛屽彿+1
+ * @param sheetIndex 宸ヤ綔琛ㄧ紪鍙�
+ * @throws InvalidFormatException
+ * @throws IOException
*/
- public int importData (InputStream is, int sheetIndex, ExcelImportCallback<T> callback, boolean sync) {
- try {
- Workbook workbook = new XSSFWorkbook(is);
- Sheet sheet = workbook.getSheetAt(sheetIndex);
- // 鑾峰彇鍒椾俊鎭�
- List<ColumnInfo> columns = this.getColumns();
- List<T> data = new ArrayList<>();
- // 寰幆鑾峰彇excel琛岃褰�
- for (int i = 1; i <= sheet.getLastRowNum(); i++) {
- // 鏋勯�犳暟鎹疄渚嬪璞�
- T instance = modelClass.newInstance();
- Row row = sheet.getRow(i);
- // 寰幆鑾峰彇鍗曞厓鏍间俊鎭�
- for (int columnIndex = 0; columnIndex < columns.size(); columnIndex++) {
- Cell cell = row.getCell(columnIndex);
- if (cell == null) {
- continue;
- }
- if (StringUtils.isBlank(cell.toString())) {
- continue;
- }
- ColumnInfo columnInfo = columns.get(columnIndex);
- if (columnInfo == null) {
- break;
- }
- // 鍐欏叆瀵硅薄灞炴��
- columnInfo.getField().setAccessible(Boolean.TRUE);
- columnInfo.getField().set(instance, this.getCellValue(cell, columnInfo));
- columnInfo.getField().setAccessible(Boolean.FALSE);
- }
- // 濡傛灉鏄┖琛屽垯缁撴潫琛岃鍙�
- if (this.isEmptyRow(instance)) {
- break;
- }
- data.add(instance);
- }
- // 鎵ц鍥炶皟鍑芥暟
- return callback.callback(data, sync);
- } catch (Exception e) {
- throw new BusinessException(ResponseStatus.IMPORT_EXCEL_ERROR, e);
- } finally {
- if (is != null) {
- try {
- is.close();
- } catch (IOException e) {
- e.printStackTrace();
- }
- }
+ public ExcelImporter(String fileName, int headerNum, int sheetIndex)
+ throws InvalidFormatException, IOException {
+ this(new File(fileName), headerNum, sheetIndex);
+ }
+
+ /**
+ * 鏋勯�犲嚱鏁�
+ * @param--path 瀵煎叆鏂囦欢瀵硅薄
+ * @param headerNum 鏍囬琛屽彿锛屾暟鎹鍙�=鏍囬琛屽彿+1
+ * @param sheetIndex 宸ヤ綔琛ㄧ紪鍙�
+ * @throws InvalidFormatException
+ * @throws IOException
+ */
+ public ExcelImporter(File file, int headerNum, int sheetIndex)
+ throws InvalidFormatException, IOException {
+ this(file.getName(), new FileInputStream(file), headerNum, sheetIndex);
+ }
+
+ /**
+ * 鏋勯�犲嚱鏁� (閲嶈)
+ * @param--file 瀵煎叆鏂囦欢瀵硅薄
+ * @param headerNum 鏍囬琛屽彿锛屾暟鎹鍙�=鏍囬琛屽彿+1
+ * @param sheetIndex 宸ヤ綔琛ㄧ紪鍙�
+ * @throws InvalidFormatException
+ * @throws IOException
+ */
+ public ExcelImporter(MultipartFile multipartFile, int headerNum, int sheetIndex)
+ throws InvalidFormatException, IOException {
+ this(multipartFile.getOriginalFilename(), multipartFile.getInputStream(), headerNum, sheetIndex);
+ }
+ /**
+ * 鏋勯�犲嚱鏁� (閲嶈)
+ * @param--file 瀵煎叆鏂囦欢瀵硅薄
+ * @param headerNum 鏍囬琛屽彿锛屾暟鎹鍙�=鏍囬琛屽彿+1
+ * @param sheetIndex 宸ヤ綔琛ㄧ紪鍙�
+ * @throws InvalidFormatException
+ * @throws IOException
+ */
+ public ExcelImporter(MultipartFile multipartFile, int headerNum, int sheetIndex, CellType cellType)
+ throws InvalidFormatException, IOException {
+ this(multipartFile.getOriginalFilename(), multipartFile.getInputStream(), headerNum, sheetIndex,cellType);
+ }
+
+ /**
+ * 鏋勯�犲嚱鏁� 锛堬級
+ * @param--path 瀵煎叆鏂囦欢瀵硅薄
+ * @param headerNum 鏍囬琛屽彿锛屾暟鎹鍙�=鏍囬琛屽彿+1
+ * @param sheetIndex 宸ヤ綔琛ㄧ紪鍙� (eg: 0)
+ * @throws InvalidFormatException
+ * @throws IOException
+ */
+ public ExcelImporter(String fileName, InputStream in, int headerNum, int sheetIndex)
+ throws InvalidFormatException, IOException {
+ if (StringUtils.isBlank(fileName)){
+ throw new RuntimeException("瀵煎叆鏂囨。涓虹┖!");
+ }else if(fileName.toLowerCase().endsWith("xls")){
+ this.wb = new HSSFWorkbook(in);
+ }else if(fileName.toLowerCase().endsWith("xlsx")){
+ this.wb = new XSSFWorkbook(in);
+ }else{
+ throw new RuntimeException("鏂囨。鏍煎紡涓嶆纭�!");
}
+ if (this.wb.getNumberOfSheets()<sheetIndex){
+ throw new RuntimeException("鏂囨。涓病鏈夊伐浣滆〃!");
+ }
+ this.sheet = this.wb.getSheetAt(sheetIndex);
+ this.headerNum = headerNum;
+ log.debug("Initialize success.");
+ }
+ public ExcelImporter(String fileName, InputStream in, int headerNum, int sheetIndex,CellType cellType)
+ throws InvalidFormatException, IOException {
+ if (StringUtils.isBlank(fileName)){
+ throw new RuntimeException("瀵煎叆鏂囨。涓虹┖!");
+ }else if(fileName.toLowerCase().endsWith("xls")){
+ this.wb = new HSSFWorkbook(in);
+ }else if(fileName.toLowerCase().endsWith("xlsx")){
+ this.wb = new XSSFWorkbook(in);
+ }else{
+ throw new RuntimeException("鏂囨。鏍煎紡涓嶆纭�!");
+ }
+ if (this.wb.getNumberOfSheets()<sheetIndex){
+ throw new RuntimeException("鏂囨。涓病鏈夊伐浣滆〃!");
+ }
+ this.sheet = this.wb.getSheetAt(sheetIndex);
+ this.headerNum = headerNum;
+ this.changeType = cellType;
+ log.debug("Initialize success.");
+ }
+
+
+
+ /**
+ * 鑾峰彇琛屽璞�
+ * @param rownum
+ * @return
+ */
+ public Row getRow(int rownum){
+ return this.sheet.getRow(rownum);
}
/**
- * 鑾峰彇Cell鍊�
- *
- * @param cell 鍗曞厓鏍煎璞�
- * @param columnInfo 鍒楅厤缃�
+ * 鑾峰彇鏁版嵁琛屽彿
+ * @return
+ */
+ public int getDataRowNum(){
+ return headerNum+1;
+ }
+
+ /**
+ * 鑾峰彇鏈�鍚庝竴涓暟鎹鍙�
+ * @return
+ */
+ public int getLastDataRowNum(){
+ return this.sheet.getLastRowNum()+headerNum;
+ }
+
+ /**
+ * 鑾峰彇鏈�鍚庝竴涓垪鍙�
+ * @return
+ */
+ public int getLastCellNum(){
+ return this.getRow(headerNum).getLastCellNum();
+ }
+
+ /**
+ * 鑾峰彇鍗曞厓鏍煎��
+ * @param row 鑾峰彇鐨勮
+ * @param column 鑾峰彇鍗曞厓鏍煎垪鍙�
* @return 鍗曞厓鏍煎��
*/
- private Object getCellValue (Cell cell, ColumnInfo columnInfo) {
- CellType cellType = cell.getCellType();
- Type fieldType = columnInfo.getField().getGenericType();
- Object value = null;
- if (fieldType.getTypeName().equals("java.util.Date")) {
- value = cell.getDateCellValue();
- } else if (cellType.equals(CellType.NUMERIC)) {
- value = cell.getNumericCellValue();
- } else if (cellType.equals(CellType.STRING)) {
- value = cell.getStringCellValue();
- } else if (cellType.equals(CellType.BOOLEAN)) {
- value = cell.getBooleanCellValue();
- } else if (cellType.equals(CellType.BLANK)) {
- value = "";
- } else if (cellType.equals(CellType.ERROR)) {
- value = cell.getErrorCellValue();
- }
- // 璋冪敤杞崲鍣�
- if (!columnInfo.columnConfig.converter().equals(ExcelDataConverterAdapter.class)) {
- try {
- Object instance = columnInfo.columnConfig.converter().newInstance();
- Method convertMethod = columnInfo.columnConfig.converter().getMethod("convert", Object[].class);
- List<Object> args = new ArrayList<>();
- args.add(value);
- for (String arg : columnInfo.columnConfig.args()) {
- args.add(arg);
+ public Object getCellValue(Row row, int column){
+ Object val = "";
+ try{
+ Cell cell = row.getCell(column);
+ if (cell != null){
+ if (cell.getCellType() == CellType.NUMERIC){
+ cell.setCellType(CellType.STRING); // 纭繚鍗曞厓鏍肩被鍨嬩负瀛楃涓�
+ val = cell.getStringCellValue();
+ }else if (cell.getCellType() == CellType.STRING){
+ val = cell.getStringCellValue();
+ }else if (cell.getCellType() == CellType.FORMULA){
+ val = cell.getCellFormula();
+ }else if (cell.getCellType() == CellType.BOOLEAN){
+ val = cell.getBooleanCellValue();
+ }else if (cell.getCellType() == CellType.ERROR){
+ val = cell.getErrorCellValue();
}
- value = convertMethod.invoke(instance, new Object[]{args.toArray()});
- } catch (Exception e) {
- throw new IllegalStateException("EVA: can not convert data by " + columnInfo.columnConfig.converter(), e);
}
+ }catch (Exception e) {
+ return val;
}
- return value;
+ return val;
}
/**
- * 鑾峰彇鍒楅泦鍚�
+ * 鑾峰彇瀵煎叆鏁版嵁鍒楄〃
+ * @param cls 瀵煎叆瀵硅薄绫诲瀷
+ * @param groups 瀵煎叆鍒嗙粍 鍙负绌�
*/
- private List<ColumnInfo> getColumns () {
- Map<Integer, ColumnInfo> sortedFields = new TreeMap<>();
- Field[] fields = modelClass.getDeclaredFields();
- int index = 0;
- for (Field field : fields) {
- ExcelImportColumn excelColumn = field.getAnnotation(ExcelImportColumn.class);
- if (excelColumn == null) {
+ public <E> List<E> getDataList(Class<E> cls, int... groups) throws InstantiationException, IllegalAccessException{
+ List<Object[]> annotationList = new ArrayList<>();
+ // Get annotation field
+ Field[] fs = cls.getDeclaredFields();
+ for (Field f : fs){
+ ExcelColumn ef = f.getAnnotation(ExcelColumn.class);
+ if (ef != null && (ef.type()==0 || ef.type()==2)){
+ if (groups!=null && groups.length>0){
+ boolean inGroup = false;
+ for (int g : groups){
+ if (inGroup){
+ break;
+ }
+ for (int efg : ef.groups()){
+ if (g == efg){
+ inGroup = true;
+ annotationList.add(new Object[]{ef, f});
+ break;
+ }
+ }
+ }
+ }else{
+ annotationList.add(new Object[]{ef, f});
+ }
+ }
+ }
+ // Get annotation method
+ Method[] ms = cls.getDeclaredMethods();
+ for (Method m : ms){
+ ExcelColumn ef = m.getAnnotation(ExcelColumn.class);
+ if (ef != null && (ef.type()==0 || ef.type()==2)){
+ if (groups!=null && groups.length>0){
+ boolean inGroup = false;
+ for (int g : groups){
+ if (inGroup){
+ break;
+ }
+ for (int efg : ef.groups()){
+ if (g == efg){
+ inGroup = true;
+ annotationList.add(new Object[]{ef, m});
+ break;
+ }
+ }
+ }
+ }else{
+ annotationList.add(new Object[]{ef, m});
+ }
+ }
+ }
+ // Field sorting
+ Collections.sort(annotationList, (o1, o2) -> {
+ int index1 =((ExcelColumn)o1[0]).index(), index2=((ExcelColumn)o2[0]).index();
+ if(index1 == -1){
+ index1 = annotationList.size()+99999;
+ }
+ if(index2 == -1){
+ index2 = annotationList.size()+99999;
+ }
+ return new Integer(index1).compareTo(new Integer(index2));
+ });
+ //log.debug("Import column count:"+annotationList.size());
+ // Get excel data
+ List<E> dataList = new ArrayList<>();
+ System.out.println("璧峰鏁版嵁琛�:"+getDataRowNum());
+ System.out.println("缁撴潫鏁版嵁琛�:"+getLastDataRowNum());
+ for (int i = this.getDataRowNum(); i <= this.getLastDataRowNum(); i++) {
+ E e = (E)cls.newInstance();
+ int column = 0;
+ Row row = this.getRow(i);
+ if (Objects.isNull(row)){
continue;
}
- if (sortedFields.get(excelColumn.index()) != null) {
- throw new AnnotationConfigurationException("EVA: Excel column contains the same index.");
+ StringBuilder sb = new StringBuilder();
+ for (Object[] os : annotationList){
+ Object val = this.getCellValue(row, column++);
+ if (val != null){
+ ExcelColumn ef = (ExcelColumn)os[0];
+ // Get param type and type cast
+ Class<?> valType = Class.class;
+ if (os[1] instanceof Field){
+ valType = ((Field)os[1]).getType();
+ }else if(os[1] instanceof Method){
+ Method method = ((Method)os[1]);
+ if ("get".equals(method.getName().substring(0, 3))){
+ valType = method.getReturnType();
+ }else if("set".equals(method.getName().substring(0, 3))){
+ valType = ((Method)os[1]).getParameterTypes()[0];
+ }
+ }
+ //log.debug("Import value type: ["+i+","+column+"] " + valType);
+ try {
+ if (valType == String.class){
+ String s = String.valueOf(val.toString());
+ if(StringUtils.endsWith(s, ".0")){
+ val = StringUtils.substringBefore(s, ".0");
+ }else{
+ val = String.valueOf(val.toString()).trim();
+ }
+ }
+ else if (valType == Integer.class){
+ val = Double.valueOf(val.toString()).intValue();
+ }else if (valType == Long.class){
+ val = Double.valueOf(val.toString()).longValue();
+ }else if (valType == Double.class){
+ val = Double.valueOf(val.toString());
+ }else if (valType == Float.class){
+ val = Float.valueOf(val.toString());
+ }else if (valType == Date.class){
+ val = DateUtil.getJavaDate((Double)val);
+ }else{
+ if (ef.fieldType() != Class.class){
+ val = ef.fieldType().getMethod("getValue", String.class).invoke(null, val.toString());
+ }else{
+ val = Class.forName(this.getClass().getName().replaceAll(this.getClass().getSimpleName(),
+ "fieldtype."+valType.getSimpleName()+"Type")).getMethod("getValue", String.class).invoke(null, val.toString());
+ }
+ }
+ } catch (Exception ex) {
+ log.info("Get cell value ["+i+","+column+"] error: " + ex.toString());
+ val = null;
+ }
+ // set entity value
+ if (os[1] instanceof Field){
+ Reflections.invokeSetter(e, ((Field)os[1]).getName(), val);
+ }else if (os[1] instanceof Method){
+ String mthodName = ((Method)os[1]).getName();
+ if ("get".equals(mthodName.substring(0, 3))){
+ mthodName = "set"+StringUtils.substringAfter(mthodName, "get");
+ }
+ Reflections.invokeMethod(e, mthodName, new Class[] {valType}, new Object[] {val});
+ }
+ }
+ sb.append(val+", ");
}
- sortedFields.put(excelColumn.index() == -1 ? index : excelColumn.index(), new ColumnInfo(excelColumn, field));
- index++;
+ dataList.add(e);
+ log.debug("Read success: ["+i+"] "+sb.toString());
}
- return new ArrayList<>(sortedFields.values());
+ return dataList;
}
/**
- * 鍒ゆ柇鏄惁涓虹┖琛�
- *
- * @param row 琛屽璞�
- * @return Boolean
+ * 鍏抽棴娴� 娓呯悊涓存椂鏂囦欢
*/
- private boolean isEmptyRow(Object row) throws IllegalAccessException{
- Field[] fields = row.getClass().getDeclaredFields();
- for (Field field : fields) {
- field.setAccessible(Boolean.TRUE);
- if (field.get(row) != null) {
- field.setAccessible(Boolean.FALSE);
- return Boolean.FALSE;
+ public void dispose(){
+ try {
+ if(wb!=null){
+ wb.close();
}
- field.setAccessible(Boolean.FALSE);
+ } catch (IOException e) {
+ e.printStackTrace();
+ throw new RuntimeException(e);
}
- return Boolean.TRUE;
+
}
- /**
- * 鍒椾俊鎭�
- */
- @Data
- @AllArgsConstructor
- private static class ColumnInfo {
- // 鍒楅厤缃�
- private ExcelImportColumn columnConfig;
-
- // 瀛楁淇℃伅
- private Field field;
- }
-}
+}
\ No newline at end of file
--
Gitblit v1.9.3