controller
@Autowired
ImportDataService importDataService;
/**
* 导入数据
*/
@AvoidRepeatSubmit
@WiteLog("导入数据")
@ApiOperation("导入数据")
@PostMapping(value = "/importSetExcel", consumes = MediaType.MULTIPART_FORM_DATA_VALUE)
public R importExcel(@RequestParam("file") MultipartFile file,
@RequestParam("orgId") Long orgId,
@RequestParam("a0103") String a0103
) {
return RUtil.success(importDataService.importExcel(file, orgId, a0103));
}
service
List<Map<String, Object>> importExcel(MultipartFile file, Long orgId, String a0103);
serviceImlp
@Autowired
A01Service a01Service;
@Autowired
A08Service a08Service;
/**
* 导入Excel文件
*
* @param file
* @param orgId
* @param a0103
* @return
*/
@Override
public List<Map<String, Object>> importExcel(MultipartFile file, Long orgId, String a0103) {
Workbook workBook;
try {
workBook = WorkbookFactory.create(file.getInputStream());
} catch (Exception e) {
throw new SystemException("创建workBook失败{}", e.getMessage());
}
List<Map<String, Object>> repeatPersons = importData(workBook, orgId, a0103);
return repeatPersons;
}
/**
* 导入excel数据
*
* @param workbook
*/
@Transactional
public List<Map<String, Object>> importData(Workbook workbook, Long orgId, String a0103) {
List<A01> a01s = new ArrayList<>();
List<A08> a08s = new ArrayList<>();
List<A01> needA01s = new ArrayList<>();
//重复人员(不需要保存)
List<String> repeatA0184 = new ArrayList<>();
//不重复人员(需要保存)
List<String> needSaveA0184 = new ArrayList<>();
List<Map<String, Object>> repeatPersons = new ArrayList<>();
Map<Integer, String> sheetMap = getSheetMap(workbook);
int sheetSize = sheetMap.keySet().size();
for (int i = 0; i < sheetSize; i++) {
String sheetName = sheetMap.get(i);
if (StringUtils.isEmpty(sheetName)) {
continue;
}
if ("a01".equals(sheetName)) {
a01s = getA01s(workbook.getSheetAt(i), orgId);
List<String> mainDataA0184s = a01s.stream().map(x -> x.getA0184()).collect(Collectors.toList());
repeatPersons = a01Service.selectAllByA0184AndA0103(mainDataA0184s, a0103);
repeatPersons.forEach(x -> repeatA0184.add(StringUtil.doString(x, "a0184")));
needA01s = a01s.stream().filter(x -> !repeatA0184.contains(x.getA0184())).collect(Collectors.toList());
needSaveA0184 = needA01s.stream().map(y -> y.getA0184()).collect(Collectors.toList());
continue;
}
if ("a08".equals(sheetName)) {
a08s = getChildren(workbook.getSheetAt(i), A08.class, needSaveA0184, needA01s);
continue;
}
}
a01Service.batchAdd(needA01s);
log.info("保存a01实体{}", needA01s);
a08Service.batchAdd(a08s);
log.info("保存a08实体{}", a08s);
return repeatPersons;
}
/**
* 获取人员基本信息 （主表）
*
* @param sheet
* @param orgId
*/
public List<A01> getA01s(Sheet sheet, Long orgId) {
List<A01> a01s = new ArrayList<>();
int rowNum = sheet.getPhysicalNumberOfRows();
Map<Integer, String> headMap = getHeadMap(sheet.getRow(0));
for (int i = 1; i < rowNum; i++) {
Row row = sheet.getRow(i);
//判断是否为空行
boolean blankRow = isBlankRow(row);
if (blankRow) {
continue;
}
Map<String, A01> map = rowTransferToEntity(row, headMap, A01.class);
Set<String> keys = map.keySet();
A01 a01 = map.get(keys.toArray()[0]);
a01.setKeyId(SnowflakeIdHelper.getId());
a01.setA0157(orgId);
a01.setDataType(0);
log.info("正在转换A01实体....");
a01s.add(a01);
}
return a01s;
}
/**
* 获取表头索引Map
*
* @param row 行
* @return
*/
public Map<Integer, String> getHeadMap(Row row) {
if (row == null || isBlankRow(row)) {
return null;
}
Map<Integer, String> headMap = new HashMap<>();
for (int i = 0; i < row.getLastCellNum(); i++) {
Cell cell = row.getCell(i);
String head = subStringHead(dealCellByString(cell));
if (StringUtils.isNoneBlank(head)) {
headMap.put(i, head);
}
}
return headMap;
}
/**
* 获取sheet页Map
*
* @param workbook 工作簿
* @return
*/
public Map<Integer, String> getSheetMap(Workbook workbook) {
int sheetNum = workbook.getNumberOfSheets();
Map<Integer, String> sheetMap = new HashMap<>();
for (int i = 0; i < sheetNum; i++) {
Sheet sheet = workbook.getSheetAt(i);
String sheetName = sheet.getSheetName();
sheetName = StringUtils.substringAfter(sheetName, ".");
if (StringUtils.isNotEmpty(sheetName)) {
sheetMap.put(i, sheetName);
}
}
return sheetMap;
}
/**
* 行数据转实体
*
* @param row 行
* @param headMap 表头
* @param clazz 要转的实体类
* @param <T>
* @return map集合，K：身份证号码（a0184） V:实体对象
*/
public <T> Map<String, T> rowTransferToEntity(Row row, Map<Integer, String> headMap, Class clazz) {
Map<String, T> map = new HashMap<>();
T bean = null;
String a0184 = "";
try {
bean = (T) clazz.newInstance();
} catch (Exception e) {
log.error("创建实体对象失败{}", e.getMessage());
throw new RuntimeException();
}
int size = headMap.keySet().size();
for (int i = 0; i < size; i++) {
String fieldName = headMap.get(i);
Cell cell = row.getCell(i);
Field field;
if (fieldName.contains("CN")) {
String value = "";
fieldName = StringUtils.substringBefore(fieldName, ".");
value = subStringBy$(dealCellByString(cell));
field = getField(clazz, fieldName);
if (field == null) {
continue;
}
try {
field.setAccessible(true);
field.set(bean, value);
} catch (IllegalAccessException e) {
log.error("设置属性值失败{}", e.getMessage());
} finally {
continue;
}
}
if ("a0157".equals(fieldName)) {
fieldName = fieldName + "_CN";
}
if ("a0418".equals(fieldName)) {
fieldName = fieldName + "_CN";
}
if ("a0184".equals(fieldName)) {
a0184 = dealCellByString(cell);
}
field = getField(clazz, fieldName);
if (field == null) {
continue;
}
field.setAccessible(true);
setFieldValue(bean, field, cell);
}
map.put(a0184, bean);
return map;
}
/**
* 给指定对象的属性赋值
*
* @param obj 对象
* @param field 属性
* @param cell 数据（单元格数据）
*/
public void setFieldValue(Object obj, Field field, Cell cell) {
try {
if (field.getType() == int.class || field.getType() == Integer.class) {
Integer value = dealCellByInteger(cell);
field.set(obj, value);
} else if (field.getType() == String.class) {
String value = dealCellByString(cell);
field.set(obj, value);
} else if (field.getType() == LocalDate.class) {
LocalDate value = dealCellByLocalDate(cell);
field.set(obj, value);
} else {
BigDecimal value = dealCellByBigDecimal(cell);
field.set(obj, value);
}
} catch (IllegalAccessException e) {
log.error("设置属性值失败{}", e.getMessage());
}
}
/**
* 获取类的属性
*
* @param clazz 类
* @param fieldName 属性名
* @return
*/
public Field getField(Class clazz, String fieldName) {
Field field = null;
try {
field = clazz.getDeclaredField(fieldName);
} catch (NoSuchFieldException e) {
log.error("没有获取到对象属性{}", fieldName);
}
return field;
}
/**
* 获取子集集合
*
* @param sheet 表
* @param clazz 类
* @param needSaveA0184 需要保存的主表身份证集合
* @param a01s 需要保存的主表信息
* @param <T>
* @return 子集集合
*/
public <T> List<T> getChildren(Sheet sheet, Class clazz, List<String> needSaveA0184, List<A01> a01s) {
int rowNum = sheet.getPhysicalNumberOfRows();
List<T> children = new ArrayList<>();
Map<Integer, String> headMap = getHeadMap(sheet.getRow(0));
for (int i = 1; i < rowNum; i++) {
Row row = sheet.getRow(i);
//判断是否为空行
boolean blankRow = isBlankRow(row);
if (blankRow) {
continue;
}
Map<String, T> map = rowTransferToEntity(row, headMap, clazz);
Set<String> keys = map.keySet();
String a0184 = (String) keys.toArray()[0];
T child = map.get(a0184);
Class superclass = clazz.getSuperclass();
Field keyId = getField(superclass, "keyId");
Field id = getField(superclass, "id");
Long id1 = SnowflakeIdHelper.getId();
for (A01 a01 : a01s) {
if (a01.getA0184().equals(a0184)) {
try {
keyId.setAccessible(true);
keyId.set(child, a01.getKeyId());
id.setAccessible(true);
id.set(child, id1);
} catch (IllegalAccessException e) {
log.error("子集设置keyId失败{}", e.getMessage());
}
}
}
boolean contains = needSaveA0184.contains(a0184);
if (contains) {
children.add(child);
}
}
return children;
}
/**
* 判断行是否为空 （目前是根据行的第一列是否为空进行判断，后续还要进行修改）
*
* @param row 行
* @return
*/
public boolean isBlankRow(Row row) {
Cell cell = row.getCell(0);
if (cell != null && cell.getCellType() != CellType.BLANK)
return false;
return true;
// for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
// Cell cell = row.getCell(c);
// if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK)
// return false;
// }
// return true;
}
/**
* 截取字符串 以（$符号截取）
*
* @param str 字符串
* @return
*/
public String subStringBy$(String str) {
String str1 = "";
String str2 = "";
if (StringUtil.isEmpty(str)) {
return str2;
}
str1 = str.substring(0, str.indexOf("$"));
str2 = str.substring(str1.length() + 1).trim();
return str2;
}
/**
* 解决表头或者sheet页字符串
*
* @param str
* @return
*/
public String subStringHead(String str) {
String str1 = "";
if (StringUtils.isEmpty(str)) {
return str1;
}
str1 = StringUtils.substringAfter(str, ".");
return str1;
}
/**
* 处理单元格为Integer类型的数据
*
* @param cell
* @return
*/
public Integer dealCellByInteger(Cell cell) {
if (cell == null) {
return null;
}
String str = cell.toString();
String num = str.substring(0, str.length() - 2);
return Integer.parseInt(num);
}
/**
* 处理单元格为BigDecimal类型的数据
*
* @param cell
* @return
*/
public BigDecimal dealCellByBigDecimal(Cell cell) {
if (cell == null) {
return null;
}
String str = cell.toString();
if (StringUtils.isEmpty(str)) {
return null;
}
BigDecimal bd = new BigDecimal(str);
return bd;
}
/**
* 处理单元格为String类型的数据
*
* @param cell
* @return
*/
public String dealCellByString(Cell cell) {
String str = "";
if (cell == null) {
return str;
}
str = cell.toString();
return str;
}
/**
* 处理单元格为LocalDate类型的数据
*
* @param cell
* @return
*/
public LocalDate dealCellByLocalDate(Cell cell) {
if (cell == null) {
return null;
}
String str = doGetCellValue(cell);
if (StringUtil.isEmpty(str)) {
return null;
}
LocalDate localDate = DateHelper.parseLocalDate(str, DateHelper.DAFAULT_DATE_FORMAT);
return localDate;
}
public String doGetCellValue(Cell cell) {
String cellStr = "";
if (cell == null) {
return cellStr;
}
try {
switch (cell.getCellType()) {
case NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
Date date = cell.getDateCellValue();
cellStr = DateFormatUtils.format(date, "yyyy-MM-dd");
} else {
Double numericCellValue = cell.getNumericCellValue();
DecimalFormat df = new DecimalFormat("0");
cellStr = df.format(numericCellValue);
}
break;
case FORMULA:
cellStr = cell.getCellFormula();
break;
case STRING:
cellStr = cell.getStringCellValue() == null ? "" : cell.getStringCellValue();
break;
}
} catch (Exception e) {
e.printStackTrace();
}
return cellStr;
}
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
