package com.product.org.admin.util;
|
|
import java.text.SimpleDateFormat;
|
import java.util.ArrayList;
|
import java.util.HashMap;
|
import java.util.List;
|
import java.util.Map;
|
import java.util.regex.Matcher;
|
import java.util.regex.Pattern;
|
|
import org.apache.poi.ss.usermodel.*;
|
|
import com.alibaba.fastjson.JSONObject;
|
import com.product.admin.util.SystemParamReplace;
|
import com.product.common.lang.StringUtils;
|
import com.product.core.cache.DataPoolCacheImpl;
|
import com.product.core.entity.DataTableEntity;
|
import com.product.core.entity.FieldSetEntity;
|
import com.product.core.exception.BaseException;
|
import com.product.core.spring.context.SpringMVCContextHolder;
|
import com.product.org.admin.config.CmnConst;
|
import com.product.org.admin.config.SystemCode;
|
|
/**
|
*
|
* Copyright LX-BASE
|
* @Title: LX-BASE-
|
* @Project: ImportDataUtil
|
* @Date: 2021-01-12 17:47
|
* @Author: 杜洪波
|
* @Description:
|
*/
|
public class ImportDataUtil {
|
|
/**
|
* 导入特殊字段处理参照（例：dept_uuid）
|
* @param fieldName
|
* @param tableName
|
* @return
|
*/
|
public static String fieldSpecialJude(String fieldName,String tableName) {
|
if (CmnConst.PRODUCT_SYS_JOB_POSTS.equals(tableName) && CmnConst.DEPT_UUID.equals(fieldName)) { //岗位等级表
|
return "data-import-dept";
|
}else if (CmnConst.PRODUCT_SYS_STAFFS.equals(tableName) && CmnConst.DEPT_UUID.equals(fieldName)) { //员工信息表
|
return "data-import-dept";
|
|
}else {
|
return null;
|
}
|
}
|
|
|
/**
|
* 根据字段名获取字段索引
|
* @param fieldNames
|
* @param fieldNmae
|
* @return
|
*/
|
public static int getIndexByFieldName(List<String>fieldNames,String fieldNmae) {
|
for (int i = 0; i < fieldNames.size(); i++) {
|
if (fieldNmae.equals(fieldNames.get(i))) {
|
return i;
|
}
|
}
|
return 999;
|
}
|
|
|
/**
|
* 根据列数获取数据集合
|
* @param listBaseData
|
* @param col
|
* @param jsonErrorInfo
|
*/
|
public static JSONObject getDataByCol(List<List<Object>>listBaseData,int col, JSONObject jsonErrorInfo) {
|
|
JSONObject jsonData = new JSONObject();
|
|
for (int i = 0; i < listBaseData.size(); i++) {
|
String dataValue=listBaseData.get(i).get(col).toString();
|
if (StringUtils.isEmpty(jsonData.get(dataValue))) {
|
if (dataValue.isEmpty()) {
|
jsonData.put("", i);
|
}else {
|
jsonData.put(dataValue, i);
|
}
|
}else {
|
jsonErrorInfo.put(i+","+col, SystemParamReplace.paramEXCELError(ImportDataUtil.errorlanguage(SystemCode.SYSTEM_DATA_IMPORT_FIELD_UNIQUE_ERROR.getValue()), col, i, dataValue));
|
}
|
}
|
return jsonData;
|
}
|
|
|
/**
|
* 读取EXCEL数据
|
* @param dataSheet EXCEL数据
|
* @param startCol 起始列
|
* @param endCol 结束列
|
* @return
|
*/
|
public static List<List<Object>> readSheetData(Sheet dataSheet,int startCol,int endCol){
|
//创建数据储存集合 行<列<值>>
|
List<List<Object>> baseData=new ArrayList<>();
|
|
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");//日期格式化
|
|
for (int i = 2; i <= dataSheet.getLastRowNum(); i++) {
|
Row row=dataSheet.getRow(i);
|
List<Object> rowBaseData=new ArrayList<>();
|
int isNUll=0;//空值单元格
|
for (int j = startCol; j <= endCol; j++) {
|
Cell cell=row.getCell(j);
|
if (cell!=null) {
|
int cellType=cell.getCellType();
|
if (cellType==Cell.CELL_TYPE_NUMERIC && DateUtil.isCellDateFormatted(cell)) {
|
rowBaseData.add(sdf.format(cell.getDateCellValue()));
|
}else if (cellType!=Cell.CELL_TYPE_BLANK) {
|
cell.setCellType(Cell.CELL_TYPE_STRING);
|
rowBaseData.add(cell.getStringCellValue());
|
}else {
|
rowBaseData.add(null);
|
isNUll++;
|
}
|
}else {
|
rowBaseData.add(null);
|
isNUll++;
|
}
|
|
}
|
//判断空值单元格个数是否与列数相等
|
if(isNUll==(endCol-startCol+1)) {
|
break;
|
}
|
baseData.add(rowBaseData);
|
}
|
return baseData;
|
}
|
|
|
/**
|
* 国际化封装
|
* @param currentField 当前字段
|
* @param language_code 语言代码
|
* @param languageValue 语言内容
|
* @return
|
* @throws BaseException
|
*/
|
public static FieldSetEntity parseInternational(String currentField,String language_code,String languageValue) throws BaseException {
|
FieldSetEntity fseLanguage=new FieldSetEntity();
|
fseLanguage.setTableName(currentField);
|
fseLanguage.setValue(language_code, languageValue);
|
return fseLanguage;
|
}
|
|
|
/**
|
* 获取异常信息国际化
|
* @param code
|
* @return
|
*/
|
public static String errorlanguage(String code) throws BaseException{
|
String language_code=SpringMVCContextHolder.getCurrentUser().getDefault_language();
|
if (StringUtils.isEmpty(language_code)) {
|
language_code="zh-CN";
|
}
|
String message=DataPoolCacheImpl.getInstance().getErrorLanguageInternation(code, language_code);
|
return message;
|
}
|
|
|
/**
|
* 组织机构分级
|
* @param dtBaseData
|
* @return
|
* @throws BaseException
|
*/
|
public static List<List<FieldSetEntity>> orgLevelGrades(DataTableEntity dtBaseData) throws BaseException {
|
|
List<List<FieldSetEntity>> array =new ArrayList<>();
|
for (int i = 0; i < dtBaseData.getRows(); i++) {
|
FieldSetEntity fseBaseData=dtBaseData.getData().get(i);
|
int grade=0;
|
//获取上级组织机构全称
|
String org_level_code_parent=fseBaseData.getString("org_level_code_parent");
|
if (!StringUtils.isEmpty(org_level_code_parent)) {
|
grade = org_level_code_parent.split(">").length;
|
}
|
// 如果数据集中没有对应该等级的下标则往里面新增
|
while(array.size()-1<grade){
|
array.add(new ArrayList<>());
|
}
|
array.get(grade).add(fseBaseData);
|
}
|
return array;
|
}
|
|
|
/**
|
* 获取过滤条件参数
|
* @param filter 高级参照过滤条件
|
* @return
|
*/
|
public static Map<String,String> getFilterPara(String filter) {
|
// 按指定模式在字符串查找
|
String pattern = "~\\w+~";
|
// 创建 Pattern 对象
|
Pattern r = Pattern.compile(pattern);
|
// 现在创建 matcher 对象
|
Matcher m = r.matcher(filter);
|
Map<String,String> v = new HashMap<>();
|
if (!StringUtils.isEmpty(filter)) {
|
while (m.find()) {
|
String c = m.group();
|
v.put(c.replace("~", ""), c);
|
}
|
}
|
return v;
|
}
|
|
|
/**
|
* 导入表名验证
|
* @param dataMap 导入数据信息
|
* @param tableNames 表名
|
* @throws BaseException
|
*/
|
public static void tableNameValidation(Map<String, DataTableEntity>dataMap,String tableNames) throws BaseException {
|
String[] tableName=tableNames.split(",");
|
for (int i = 0; i < tableName.length; i++) {
|
if (dataMap.get(tableName[i])==null) {
|
throw new BaseException(SystemCode.SYSTEM_IMPORT_TEBLENAME_ERROR.getValue(), SystemCode.SYSTEM_IMPORT_TEBLENAME_ERROR.getText());
|
}
|
}
|
}
|
}
|
