You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@inlong.apache.org by do...@apache.org on 2023/04/17 03:18:47 UTC
[inlong] branch master updated: [INLONG-7843][Manager] Creating the schema of StreamSource by importing an Excel file (#7861)
This is an automated email from the ASF dual-hosted git repository.
dockerzhang pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/inlong.git
The following commit(s) were added to refs/heads/master by this push:
new 87f252682 [INLONG-7843][Manager] Creating the schema of StreamSource by importing an Excel file (#7861)
87f252682 is described below
commit 87f252682df4e15fe1a093625ed23ae07f2e982b
Author: feat <fe...@outlook.com>
AuthorDate: Mon Apr 17 11:18:41 2023 +0800
[INLONG-7843][Manager] Creating the schema of StreamSource by importing an Excel file (#7861)
---
.../manager/common/tool/excel/ExcelTool.java | 219 ++++++++++++++++-----
.../service/stream/InlongStreamService.java | 6 +
.../service/stream/InlongStreamServiceImpl.java | 28 +++
.../web/controller/InlongStreamController.java | 10 +
4 files changed, 214 insertions(+), 49 deletions(-)
diff --git a/inlong-manager/manager-common/src/main/java/org/apache/inlong/manager/common/tool/excel/ExcelTool.java b/inlong-manager/manager-common/src/main/java/org/apache/inlong/manager/common/tool/excel/ExcelTool.java
index eb518fe7e..c88f1d584 100644
--- a/inlong-manager/manager-common/src/main/java/org/apache/inlong/manager/common/tool/excel/ExcelTool.java
+++ b/inlong-manager/manager-common/src/main/java/org/apache/inlong/manager/common/tool/excel/ExcelTool.java
@@ -30,6 +30,7 @@ import org.apache.inlong.manager.common.tool.excel.validator.ExcelCellValidator;
import org.apache.inlong.manager.common.util.Preconditions;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
+import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
@@ -48,14 +49,15 @@ import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.IOException;
+import java.io.InputStream;
import java.io.OutputStream;
import java.io.Serializable;
import java.lang.reflect.Field;
-import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.HashMap;
+import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Objects;
@@ -225,23 +227,23 @@ public class ExcelTool {
*/
private static void fillSheetContent(XSSFSheet sheet, List<String> heads, List<Map<String, String>> contents,
List<XSSFCellStyle> contentStyles) {
- Optional.ofNullable(contents)
- .ifPresent(content -> {
- int rowSize = content.size();
- for (int lineId = 0; lineId < rowSize; lineId++) {
- Map<String, String> line = contents.get(lineId);
- Row row = sheet.createRow(lineId + 1);
- int headSize = heads.size();
- for (int colId = 0; colId < headSize; colId++) {
- String title = heads.get(colId);
- String originValue = line.get(title);
- String value = StringUtils.isNotBlank(originValue) ? originValue : "";
- Cell cell = row.createCell(colId);
- cell.setCellValue(value);
- cell.setCellStyle(contentStyles.get(colId));
- }
- }
- });
+ if (CollectionUtils.isEmpty(contents)) {
+ return;
+ }
+ int rowSize = contents.size();
+ for (int lineId = 0; lineId < rowSize; lineId++) {
+ Map<String, String> line = contents.get(lineId);
+ Row row = sheet.createRow(lineId + 1);
+ int headSize = heads.size();
+ for (int colId = 0; colId < headSize; colId++) {
+ String title = heads.get(colId);
+ String originValue = line.get(title);
+ String value = StringUtils.isNotBlank(originValue) ? originValue : "";
+ Cell cell = row.createCell(colId);
+ cell.setCellValue(value);
+ cell.setCellStyle(contentStyles.get(colId));
+ }
+ }
}
private static void fillSheetHeader(XSSFRow row, List<String> heads, List<XSSFCellStyle> headerStyles) {
@@ -335,8 +337,128 @@ public class ExcelTool {
.collect(Collectors.toList());
}
+ /**
+ * Read data from an Excel file and convert it to a list of objects of the specified class.
+ *
+ * @param is The input stream of the Excel file.
+ * @param clazz The class of the objects to be converted.
+ * @param <E> The type of the objects to be converted.
+ * @return A list of objects of the specified class.
+ * @throws IOException If an I/O error occurs.
+ * @throws IllegalAccessException If the class or its nullable constructor is not accessible.
+ * @throws InstantiationException If the class that declares the underlying field is an interface or is abstract.
+ */
+ public static <E> List<E> read(InputStream is, Class<E> clazz)
+ throws IOException, IllegalAccessException, InstantiationException, NoSuchMethodException {
+ ClassMeta<E> classMeta = ClassMeta.of(clazz);
+ int fieldCount = classMeta.fieldCount();
+
+ expectTrue(fieldCount > 0, "The class contains at least one field with a @ExcelField annotation");
+ XSSFWorkbook hssfWorkbook = new XSSFWorkbook(is);
+ List<E> result = new LinkedList<>();
+ for (int sheetIndex = 0; sheetIndex < hssfWorkbook.getNumberOfSheets(); ++sheetIndex) {
+ XSSFSheet sheet = hssfWorkbook.getSheetAt(sheetIndex);
+ if (sheet != null) {
+ XSSFRow headerRow = sheet.getRow(0);
+ // According to the name of the header row, determine the column
+ for (int colIndex = 0; colIndex < fieldCount + 10 && !classMeta.matchedAll(); colIndex++) {
+ XSSFCell cell = headerRow.getCell(colIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
+ if (cell != null) {
+ classMeta.setFieldLocation(cell.getStringCellValue(), colIndex);
+ }
+ }
+ expectTrue(classMeta.matchedAll(),
+ "The first line field must be the same number of @ExcelMeta annotated fields in the class");
+
+ int lastRowNum = sheet.getLastRowNum();
+ List<E> currentResult = new ArrayList<>(lastRowNum);
+
+ for (int rowNum = 1; rowNum <= lastRowNum; ++rowNum) {
+ XSSFRow row = sheet.getRow(rowNum);
+ if (row == null) {
+ continue;
+ }
+ E instance = null;
+ boolean hasValueInRow = false;
+ for (Map.Entry<Integer, FieldMeta> entry : classMeta.positionFieldMetaMap.entrySet()) {
+ Integer colIndex = entry.getKey();
+ FieldMeta fieldMeta = entry.getValue();
+ XSSFCell cell = row.getCell(colIndex, Row.MissingCellPolicy.RETURN_BLANK_AS_NULL);
+ if (cell == null) {
+ continue;
+ }
+ hasValueInRow = true;
+ ExcelCellDataTransfer cellDataTransfer = fieldMeta.getCellDataTransfer();
+ Object value = parseCellValue(cellDataTransfer, cell);
+ if (instance == null) {
+ instance = clazz.newInstance();
+ }
+ fieldMeta.getField().setAccessible(true);
+ fieldMeta.getField().set(instance, value);
+
+ }
+ if (hasValueInRow) {
+ currentResult.add(instance);
+ }
+ }
+ result.addAll(currentResult);
+ }
+ }
+ return result;
+ }
+
+ /**
+ * Parse the cell value of a given field in the Excel sheet
+ *
+ * @param cellDataTransfer the data transfer type of the cell
+ * @param cell the cell to parse
+ * @return the parsed cell value
+ */
+ private static Object parseCellValue(ExcelCellDataTransfer cellDataTransfer, XSSFCell cell) {
+ Object o = null;
+ if (cellDataTransfer == ExcelCellDataTransfer.DATE) {
+ CellType cellTypeEnum = cell.getCellType();
+ if (cellTypeEnum == CellType.STRING) {
+ String cellValue = cell.getStringCellValue();
+ o = cellDataTransfer.parseFromText(cellValue);
+ } else if (cellTypeEnum == CellType.NUMERIC) {
+ o = cell.getDateCellValue();
+ }
+ } else {
+ String value = parseCellValue(cell);
+ o = value;
+ if (cellDataTransfer != ExcelCellDataTransfer.NONE) {
+ o = cellDataTransfer.parseFromText(value);
+ }
+ }
+ return o;
+ }
+
+ /**
+ * Parse the cell value of a given field in the Excel sheet
+ *
+ * @param cell the cell to parse
+ * @return the parsed cell value
+ */
+ private static String parseCellValue(Cell cell) {
+ String cellValue;
+ if (cell != null) {
+ cell.setCellType(CellType.STRING);
+ cellValue = cell.getStringCellValue();
+ if (!StringUtils.isEmpty(cellValue)) {
+ cellValue = cellValue.trim();
+ cellValue = cellValue.replace("\n", "");
+ cellValue = cellValue.replace("\r", "");
+ cellValue = cellValue.replace("\\", "/");
+ }
+ } else {
+ cellValue = "";
+ }
+ return cellValue;
+ }
+
@Data
- static class ClassFieldMeta implements Serializable {
+ static class FieldMeta implements Serializable {
/**
* The name of the field
@@ -361,12 +483,12 @@ public class ExcelTool {
/**
* The field object
*/
- private transient Field filed;
+ private transient Field field;
/**
* The meta of class file.
*/
- public ClassFieldMeta() {
+ public FieldMeta() {
// build meta
}
}
@@ -390,17 +512,17 @@ public class ExcelTool {
/**
* The metadata of the fields in the class.
*/
- private List<ClassFieldMeta> classFieldMetas;
+ private List<FieldMeta> classFieldMetas;
/**
* The mapping of field names to their metadata.
*/
- private Map<String, ClassFieldMeta> fieldNameMetaMap;
+ private Map<String, FieldMeta> fieldNameMetaMap;
/**
* The mapping of Excel names to their metadata.
*/
- private Map<String, ClassFieldMeta> excelNameMetaMap;
+ private Map<String, FieldMeta> excelNameMetaMap;
/**
* Whether the fields have been sorted.
@@ -410,17 +532,7 @@ public class ExcelTool {
/**
* The mapping of positions to their metadata.
*/
- private Map<Integer, ClassFieldMeta> positionFieldMetaMap;
-
- /**
- * The method to set whether the Excel data is valid.
- */
- private Method excelDataValidMethod;
-
- /**
- * The method to set the validation information of the Excel data.
- */
- private Method excelDataValidateInfoMethod;
+ private Map<Integer, FieldMeta> positionFieldMetaMap = new HashMap<>();
private ClassMeta() {
}
@@ -428,16 +540,16 @@ public class ExcelTool {
/**
* Create a ClassMeta object from a given template class.
*/
- public static <T> ClassMeta<T> of(Class<T> templateClass)
+ public static <T> ClassMeta<T> of(Class<T> clazz)
throws InstantiationException, IllegalAccessException, NoSuchMethodException {
ClassMeta<T> meta = new ClassMeta<>();
- meta.setTClass(templateClass);
- ExcelEntity excelEntity = templateClass.getAnnotation(ExcelEntity.class);
+ meta.setTClass(clazz);
+ ExcelEntity excelEntity = clazz.getAnnotation(ExcelEntity.class);
if (excelEntity != null) {
meta.name = excelEntity.name();
}
- Field[] fields = templateClass.getDeclaredFields();
+ Field[] fields = clazz.getDeclaredFields();
for (Field field : fields) {
ExcelField excelField = field.getAnnotation(ExcelField.class);
if (excelField != null) {
@@ -447,14 +559,10 @@ public class ExcelTool {
}
}
- Method excelDataValid = templateClass.getMethod("setExcelDataValid", Boolean.TYPE);
- Method excelDataValidateInfo = templateClass.getMethod("setExcelDataValidate", String.class);
- meta.setExcelDataValidMethod(excelDataValid);
- meta.setExcelDataValidateInfoMethod(excelDataValidateInfo);
return meta;
}
- private void addField(String name, String excelName, Field field, Class<?> fieldType,
+ private void addField(String fieldName, String excelName, Field field, Class<?> fieldType,
ExcelCellDataTransfer cellDataTransfer) {
if (this.classFieldMetas == null) {
this.classFieldMetas = new ArrayList<>();
@@ -468,13 +576,13 @@ public class ExcelTool {
this.fieldNameMetaMap = new HashMap<>();
}
- ClassFieldMeta fieldMeta = new ClassFieldMeta();
- fieldMeta.setName(name);
+ FieldMeta fieldMeta = new FieldMeta();
+ fieldMeta.setName(fieldName);
fieldMeta.setExcelName(excelName);
fieldMeta.setFieldType(fieldType);
fieldMeta.setCellDataTransfer(cellDataTransfer);
- fieldMeta.setFiled(field);
- this.fieldNameMetaMap.put(name, fieldMeta);
+ fieldMeta.setField(field);
+ this.fieldNameMetaMap.put(fieldName, fieldMeta);
this.excelNameMetaMap.put(excelName, fieldMeta);
this.classFieldMetas.add(fieldMeta);
}
@@ -482,9 +590,22 @@ public class ExcelTool {
/**
* Get the metadata of a field at a given position.
*/
- public ClassFieldMeta field(int position) {
+ public FieldMeta field(int position) {
return this.positionFieldMetaMap.get(position);
}
+ public int fieldCount() {
+ return classFieldMetas == null ? 0 : classFieldMetas.size();
+ }
+
+ public boolean matchedAll() {
+ return positionFieldMetaMap.size() == this.excelNameMetaMap.size();
+ }
+
+ public void setFieldLocation(String excelName, int colIndex) {
+ if (this.excelNameMetaMap.containsKey(excelName)) {
+ positionFieldMetaMap.put(colIndex, excelNameMetaMap.get(excelName));
+ }
+ }
}
}
diff --git a/inlong-manager/manager-service/src/main/java/org/apache/inlong/manager/service/stream/InlongStreamService.java b/inlong-manager/manager-service/src/main/java/org/apache/inlong/manager/service/stream/InlongStreamService.java
index b13a05206..6fd7784e2 100644
--- a/inlong-manager/manager-service/src/main/java/org/apache/inlong/manager/service/stream/InlongStreamService.java
+++ b/inlong-manager/manager-service/src/main/java/org/apache/inlong/manager/service/stream/InlongStreamService.java
@@ -26,6 +26,7 @@ import org.apache.inlong.manager.pojo.stream.InlongStreamPageRequest;
import org.apache.inlong.manager.pojo.stream.InlongStreamRequest;
import org.apache.inlong.manager.pojo.stream.StreamField;
import org.apache.inlong.manager.pojo.user.UserInfo;
+import org.springframework.web.multipart.MultipartFile;
import java.util.List;
@@ -239,4 +240,9 @@ public interface InlongStreamService {
* @return list of stream field
*/
List<StreamField> parseFields(ParseFieldRequest parseFieldRequest);
+
+ /**
+ * Converts an Excel file to a streamFields
+ */
+ List<StreamField> parseFields(MultipartFile file);
}
diff --git a/inlong-manager/manager-service/src/main/java/org/apache/inlong/manager/service/stream/InlongStreamServiceImpl.java b/inlong-manager/manager-service/src/main/java/org/apache/inlong/manager/service/stream/InlongStreamServiceImpl.java
index c24a21b6a..b9346e178 100644
--- a/inlong-manager/manager-service/src/main/java/org/apache/inlong/manager/service/stream/InlongStreamServiceImpl.java
+++ b/inlong-manager/manager-service/src/main/java/org/apache/inlong/manager/service/stream/InlongStreamServiceImpl.java
@@ -29,6 +29,7 @@ import org.apache.inlong.manager.common.enums.ErrorCodeEnum;
import org.apache.inlong.manager.common.enums.GroupStatus;
import org.apache.inlong.manager.common.enums.StreamStatus;
import org.apache.inlong.manager.common.exceptions.BusinessException;
+import org.apache.inlong.manager.common.tool.excel.ExcelTool;
import org.apache.inlong.manager.common.util.CommonBeanUtils;
import org.apache.inlong.manager.common.util.Preconditions;
import org.apache.inlong.manager.dao.entity.InlongGroupEntity;
@@ -72,6 +73,10 @@ import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.create.table.ColDataType;
import net.sf.jsqlparser.statement.create.table.ColumnDefinition;
import net.sf.jsqlparser.statement.create.table.CreateTable;
+import org.springframework.web.multipart.MultipartFile;
+
+import java.io.IOException;
+import java.io.InputStream;
import java.io.StringReader;
import java.util.ArrayList;
import java.util.Collections;
@@ -756,6 +761,29 @@ public class InlongStreamServiceImpl implements InlongStreamService {
String.format("parse stream fields error : %s", e.getMessage()));
}
}
+
+ @Override
+ public List<StreamField> parseFields(MultipartFile file) {
+ InputStream inputStream;
+ try {
+ inputStream = file.getInputStream();
+ } catch (IOException e) {
+ throw new BusinessException(ErrorCodeEnum.INVALID_PARAMETER, "Can not properly read update file");
+ }
+ List<StreamField> data = null;
+ try {
+ data = ExcelTool.read(inputStream, StreamField.class);
+ } catch (IOException | IllegalAccessException | InstantiationException | NoSuchMethodException e) {
+ throw new BusinessException(ErrorCodeEnum.INVALID_PARAMETER,
+ "Can not properly parse excel, message: " + e.getClass().getName() + ":" + e.getMessage());
+ }
+ if (CollectionUtils.isEmpty(data)) {
+ throw new BusinessException(ErrorCodeEnum.INVALID_PARAMETER,
+ "The content of uploaded Excel file is empty, please check!");
+ }
+ return data;
+ }
+
/**
* Parse fields from CSV format
* @param statement CSV statement
diff --git a/inlong-manager/manager-web/src/main/java/org/apache/inlong/manager/web/controller/InlongStreamController.java b/inlong-manager/manager-web/src/main/java/org/apache/inlong/manager/web/controller/InlongStreamController.java
index 3e41371ff..d5bca10f2 100644
--- a/inlong-manager/manager-web/src/main/java/org/apache/inlong/manager/web/controller/InlongStreamController.java
+++ b/inlong-manager/manager-web/src/main/java/org/apache/inlong/manager/web/controller/InlongStreamController.java
@@ -51,6 +51,7 @@ import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
+import org.springframework.web.multipart.MultipartFile;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
@@ -195,6 +196,15 @@ public class InlongStreamController {
return Response.success(streamService.parseFields(parseFieldRequest));
}
+ @RequestMapping(value = "/stream/parseFieldsByExcel", method = RequestMethod.POST)
+ @ApiOperation(value = "Parse inlong stream fields by update excel file", httpMethod = "POST")
+ @ApiImplicitParams({
+ @ApiImplicitParam(name = "file", value = "file object", required = true, dataType = "__FILE", dataTypeClass = MultipartFile.class, paramType = "query")
+ })
+ public Response<List<StreamField>> parseFieldsByExcel(@RequestParam(value = "file") MultipartFile file) {
+ return Response.success(streamService.parseFields(file));
+ }
+
@RequestMapping(value = "/stream/fieldsImportTemplate", method = RequestMethod.GET, produces = MediaType.APPLICATION_OCTET_STREAM_VALUE)
@ApiOperation(value = "Download fields import template", produces = MediaType.APPLICATION_OCTET_STREAM_VALUE)
public void downloadFieldsImportTemplate(HttpServletResponse response) {