From a227dd7df7f84a5cfb7218042a6f844991dcfb96 Mon Sep 17 00:00:00 2001
From: 이민희 <mhlee@maprex.co.kr>
Date: 수, 09 2월 2022 14:53:41 +0900
Subject: [PATCH] - isp, 호스팅 엑셀 import 기능 추가
---
src/main/java/kr/wisestone/owl/service/impl/CompanyFieldServiceImpl.java | 209 +++++++++++++++++++++++++--------------------------
1 files changed, 102 insertions(+), 107 deletions(-)
diff --git a/src/main/java/kr/wisestone/owl/service/impl/CompanyFieldServiceImpl.java b/src/main/java/kr/wisestone/owl/service/impl/CompanyFieldServiceImpl.java
index f0b018b..32d6003 100644
--- a/src/main/java/kr/wisestone/owl/service/impl/CompanyFieldServiceImpl.java
+++ b/src/main/java/kr/wisestone/owl/service/impl/CompanyFieldServiceImpl.java
@@ -355,7 +355,7 @@
}
}
- // �뿊�� import 濡� �씠�뒋瑜� �벑濡앺븳�떎.
+ // �뿊�� import 濡� �뾽泥대�� �벑濡앺븳�떎.
@Override
@Transactional
public void importExcel(MultipartFile multipartFile) throws Exception {
@@ -440,38 +440,53 @@
}
/**
+ * cell String�쑝濡� 蹂��솚 �븿�닔
+ * @param cell Cell
+ * @param isNull boolean
+ * @return String
+ */
+ private String stringToCell (Cell cell, boolean isNull) {
+ String cellStr = "";
+ if (!isNull) {
+ cellStr = CommonUtil.convertExcelStringToCell(cell);
+ // 怨듬갚 �젣嫄�
+ cell.setCellValue(cellStr.trim());
+ } else {
+ cell.setCellValue(cellStr);
+ }
+ return cellStr;
+ }
+
+ /**
* cell NULL 泥댄겕 �븿�닔
* @param cell Cell
* @return boolean
*/
- private Boolean cellNullCheck (Cell cell, int rowIndex) {
- boolean result = false;
-
- // 臾몄옄�삎�떇�씤吏� 泥댄겕
- if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK && cell.getCellType() != cell.CELL_TYPE_STRING) {
- throw new OwlRuntimeException(
- this.messageAccessor.getMessage(MsgConstants.EXCEL_IMPORT_NOT_STRING_TYPE, rowIndex));
+ private Boolean cellNullCheck (Cell cell) {
+ int cellType = cell.getCellType();
+ if (cellType < Cell.CELL_TYPE_BLANK) {
+ if (cellType == Cell.CELL_TYPE_STRING) {
+ if (cell.getStringCellValue() != null && !cell.getStringCellValue().equals("")) {
+ return false;
+ }
+ } else {
+ return false;
+ }
}
-
- // 怨듬갚 �젣嫄�
- if (cell != null && cell.getCellType() == cell.CELL_TYPE_STRING && cell.getStringCellValue() != null) {
- cell.setCellValue(cell.getStringCellValue().trim());
- }
-
- if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK && cell.getCellType() == cell.CELL_TYPE_STRING
- && cell.getStringCellValue() != null && !cell.getStringCellValue().equals("")) {
- result = true;
- }
-
- return result;
+ return true;
}
- /*private void telTypeCheck (Cell cell, int rowIndex) {
+ /**
+ * �쟾�솕踰덊샇 CellType 泥댄겕 �븿�닔
+ * @param cell Cell
+ * @param rowIndex int
+ */
+ private void telTypeCheck (Cell cell, int rowIndex) {
if (cell != null && cell.getCellType() != cell.CELL_TYPE_STRING) {
throw new OwlRuntimeException(
this.messageAccessor.getMessage(MsgConstants.EXCEL_IMPORT_TEL_NOT_STRING_TYPE, rowIndex));
}
- }*/
+ }
// �뿊�� �븘�뱶�뿉 �엳�뒗 �젙蹂대�� �뾽泥� form �쑝濡� �삷湲대떎.
private CompanyFieldForm setCompanyFieldFormToExcelField(Row row, int rowIndex, Map<String, IspField> ispFieldMaps, Map<String, HostingField> hostingFieldMaps,
@@ -483,121 +498,111 @@
for (int cellIndex = 0; cellIndex < headers.size(); cellIndex++) {
Cell cell = row.getCell(cellIndex);
+
+ String cellStr = "";
+ boolean isNull = true;
+
+ if (cell != null) {
+ isNull = cellNullCheck(cell);
+ cellStr = stringToCell(cell, isNull); //cell�쓣 String�쑝濡� 蹂��솚
+ }
+
switch (cellIndex) {
case 0:
// �뾽泥대챸
- this.setCompanyFormName(cell, companyFieldForm, rowIndex);
+ if (isNull) {
+ throw new OwlRuntimeException(
+ this.messageAccessor.getMessage(MsgConstants.EXCEL_IMPORT_COMPANY_NAME_IS_NULL, rowIndex));
+ }
+ this.setCompanyFormName(cellStr, companyFieldForm);
break;
case 1:
// url
- if (cellNullCheck(cell, rowIndex)) {
- this.setCompanyFormUrl(cell, companyFieldForm, rowIndex);
- }
+ this.setCompanyFormUrl(cellStr, companyFieldForm, isNull);
break;
case 2:
// isp紐�
- if (cellNullCheck(cell, rowIndex)) {
- this.setCompanyFormIspName(cell, ispFieldMaps, companyFieldForm, rowIndex);
- }
+ this.setCompanyFormIspName(cellStr, ispFieldMaps, companyFieldForm, rowIndex, isNull);
break;
case 3:
// �샇�뒪�똿紐�
- if (cellNullCheck(cell, rowIndex)) {
- this.setCompanyFormHostingName(cell, hostingFieldMaps, companyFieldForm, rowIndex);
- }
+ this.setCompanyFormHostingName(cellStr, hostingFieldMaps, companyFieldForm, rowIndex, isNull);
break;
case 4:
// �뿰�씫泥�
- if (cellNullCheck(cell, rowIndex)) {
- //this.telTypeCheck(cell, rowIndex);
- this.setCompanyFormTel(cell, companyFieldForm, rowIndex);
- }
+ telTypeCheck(cell, rowIndex);
+ this.setCompanyFormTel(cellStr, companyFieldForm, isNull);
break;
case 5:
// �씠硫붿씪
- if (cellNullCheck(cell, rowIndex)) {
- this.setCompanyFormEmail(cell, companyFieldForm, rowIndex);
- }
+ this.setCompanyFormEmail(cellStr, companyFieldForm, isNull);
break;
case 6:
// �떞�떦�옄
- if (cellNullCheck(cell, rowIndex)) {
- this.setCompanyFormManager(cell, companyFieldForm, rowIndex);
- }
+ this.setCompanyFormManager(cellStr, companyFieldForm, isNull);
break;
case 7:
// 湲곗뾽援щ텇
- if (cellNullCheck(cell, rowIndex)) {
- this.setCompanyFormCompanyType(cell, companyTypeMaps, companyFieldForm, rowIndex);
- }
+ this.setCompanyFormCompanyType(cellStr, companyTypeMaps, companyFieldForm, rowIndex, isNull);
break;
case 8:
// �뾽醫�(��遺꾨쪟)
- if (cellNullCheck(cell, rowIndex)) {
- this.setCompanyFormParentSector(cell, parentSectorMaps, companyFieldForm, rowIndex);
- }
+ this.setCompanyFormParentSector(cellStr, parentSectorMaps, companyFieldForm, rowIndex, isNull);
break;
case 9:
// �뾽醫�(以묐텇瑜�)
- if (cellNullCheck(cell, rowIndex)) {
- this.setCompanyFormChildSector(cell, childSectorMaps, companyFieldForm, rowIndex);
- }
+ this.setCompanyFormChildSector(cellStr, childSectorMaps, companyFieldForm, rowIndex, isNull);
break;
case 10:
// 吏��뿭
- if (cellNullCheck(cell, rowIndex)) {
- this.setCompanyFormRegion(cell, regionMaps, companyFieldForm, rowIndex);
- }
+ this.setCompanyFormRegion(cellStr, regionMaps, companyFieldForm, rowIndex, isNull);
break;
case 11:
// �긽�깭
- if (cellNullCheck(cell, rowIndex)) {
- this.setCompanyFormStatus(cell, statusMaps, companyFieldForm, rowIndex);
- }
+ this.setCompanyFormStatus(cellStr, statusMaps, companyFieldForm, isNull);
break;
case 12:
// 鍮꾧퀬
- if (cellNullCheck(cell, rowIndex)) {
- this.setCompanyFormMemo(cell, companyFieldForm, rowIndex);
- }
- break;
+ this.setCompanyFormMemo(cellStr, companyFieldForm, isNull);
}
}
return companyFieldForm;
}
- private void setCompanyFormMemo(Cell cell, CompanyFieldForm companyFieldForm, int rowIndex) {
- companyFieldForm.setMemo(CommonUtil.convertExcelStringToCell(cell));
+ private void setCompanyFormMemo(String cellStr, CompanyFieldForm companyFieldForm, boolean isNull) {
+ if (!isNull) {
+ companyFieldForm.setMemo(cellStr);
+ }
}
- private void setCompanyFormStatus(Cell cell, Map<String, Map<String, Object>> statusMaps, CompanyFieldForm companyFieldForm, int rowIndex) {
- if (cell != null) {
- Map<String, Object> statusMap = statusMaps.get(CommonUtil.convertExcelStringToCell(cell));
+ private void setCompanyFormStatus(String cellStr, Map<String, Map<String, Object>> statusMaps, CompanyFieldForm companyFieldForm, boolean isNull) {
+ if (!isNull) {
+ Map<String, Object> statusMap = statusMaps.get(cellStr);
if (MapUtil.getLong(statusMap, "id") != null) {
companyFieldForm.setStatusId(MapUtil.getLong(statusMap, "id"));
} else {
companyFieldForm.setStatusId(120L);
}
- companyFieldForm.setStatusName(CommonUtil.convertExcelStringToCell(cell));
+ companyFieldForm.setStatusName(cellStr);
}
}
- private void setCompanyFormRegion(Cell cell, Map<String, Map<String, Object>> regionMaps, CompanyFieldForm companyFieldForm, int rowIndex) {
- if (cell != null) {
- Map<String, Object> regionMap = regionMaps.get(CommonUtil.convertExcelStringToCell(cell));
+ private void setCompanyFormRegion(String cellStr, Map<String, Map<String, Object>> regionMaps, CompanyFieldForm companyFieldForm, int rowIndex, boolean isNull) {
+ if (!isNull) {
+ Map<String, Object> regionMap = regionMaps.get(cellStr);
if (regionMap == null) {
throw new OwlRuntimeException(
this.messageAccessor.getMessage(MsgConstants.EXCEL_IMPORT_REGION_NOT_EXIST, rowIndex));
@@ -606,9 +611,9 @@
}
}
- private void setCompanyFormChildSector(Cell cell, Map<String, Map<String, Object>> childSectorMaps, CompanyFieldForm companyFieldForm, int rowIndex) {
- if (cell != null) {
- Map<String, Object> childSectorMap = childSectorMaps.get(CommonUtil.convertExcelStringToCell(cell));
+ private void setCompanyFormChildSector(String cellStr, Map<String, Map<String, Object>> childSectorMaps, CompanyFieldForm companyFieldForm, int rowIndex, boolean isNull) {
+ if (!isNull) {
+ Map<String, Object> childSectorMap = childSectorMaps.get(cellStr);
// ��遺꾨쪟 �뾾�씠 以묐텇瑜섎쭔 �엯�젰�뻽�쓣寃쎌슦
if (companyFieldForm.getParentSectorId() == null) {
@@ -626,9 +631,9 @@
}
}
- private void setCompanyFormParentSector(Cell cell, Map<String, Map<String, Object>> parentSectorMaps, CompanyFieldForm companyFieldForm, int rowIndex) {
- if (cell != null) {
- Map<String, Object> parentSectorMap = parentSectorMaps.get(CommonUtil.convertExcelStringToCell(cell));
+ private void setCompanyFormParentSector(String cellStr, Map<String, Map<String, Object>> parentSectorMaps, CompanyFieldForm companyFieldForm, int rowIndex, boolean isNull) {
+ if (!isNull) {
+ Map<String, Object> parentSectorMap = parentSectorMaps.get(cellStr);
if (parentSectorMap == null) {
throw new OwlRuntimeException(
this.messageAccessor.getMessage(MsgConstants.EXCEL_IMPORT_PARENT_SECTOR_NOT_EXIST, rowIndex));
@@ -637,9 +642,9 @@
}
}
- private void setCompanyFormCompanyType(Cell cell, Map<String, Map<String, Object>> companyTypeMaps, CompanyFieldForm companyFieldForm, int rowIndex) {
- if (cell != null) {
- Map<String, Object> companyTypeMap = companyTypeMaps.get(CommonUtil.convertExcelStringToCell(cell));
+ private void setCompanyFormCompanyType(String cellStr, Map<String, Map<String, Object>> companyTypeMaps, CompanyFieldForm companyFieldForm, int rowIndex, boolean isNull) {
+ if (!isNull) {
+ Map<String, Object> companyTypeMap = companyTypeMaps.get(cellStr);
if (companyTypeMap == null) {
throw new OwlRuntimeException(
this.messageAccessor.getMessage(MsgConstants.EXCEL_IMPORT_COMPANY_TYPE_NOT_EXIST, rowIndex));
@@ -648,25 +653,23 @@
}
}
- private void setCompanyFormManager(Cell cell, CompanyFieldForm companyFieldForm, int rowIndex) {
- if (cell != null) {
- companyFieldForm.setManager(CommonUtil.convertExcelStringToCell(cell));
+ private void setCompanyFormManager(String manager, CompanyFieldForm companyFieldForm, boolean isNull) {
+ if (!isNull) {
+ companyFieldForm.setManager(manager);
}
}
- private void setCompanyFormEmail(Cell cell, CompanyFieldForm companyFieldForm, int rowIndex) {
- if (cell != null) {
- String email = CommonUtil.convertExcelStringToCell(cell);
- if (CommonUtil.convertExcelStringToCell(cell).contains(" ")) {
- email = CommonUtil.convertExcelStringToCell(cell).replace(" ", "");
+ private void setCompanyFormEmail(String email, CompanyFieldForm companyFieldForm, boolean isNull) {
+ if (!isNull) {
+ if (email.contains(" ")) {
+ email = email.replace(" ", "");
}
companyFieldForm.setEmail(email);
}
}
- private void setCompanyFormTel(Cell cell, CompanyFieldForm companyFieldForm, int rowIndex) {
- if (cell != null) {
- String tel = CommonUtil.convertExcelStringToCell(cell);
+ private void setCompanyFormTel(String tel, CompanyFieldForm companyFieldForm, boolean isNull) {
+ if (!isNull) {
if (tel.contains("-")) {
tel = tel.replace("-", "");
}
@@ -677,9 +680,9 @@
}
}
- private void setCompanyFormHostingName(Cell cell, Map<String, HostingField> hostingFieldMaps, CompanyFieldForm companyFieldForm, int rowIndex) {
- if (cell != null) {
- HostingField hostingField = hostingFieldMaps.get(CommonUtil.convertExcelStringToCell(cell));
+ private void setCompanyFormHostingName(String cellStr, Map<String, HostingField> hostingFieldMaps, CompanyFieldForm companyFieldForm, int rowIndex, boolean isNull) {
+ if (!isNull) {
+ HostingField hostingField = hostingFieldMaps.get(cellStr);
if (hostingField == null) {
throw new OwlRuntimeException(
this.messageAccessor.getMessage(MsgConstants.EXCEL_IMPORT_HOSTING_NOT_EXIST, rowIndex));
@@ -688,9 +691,9 @@
}
}
- private void setCompanyFormIspName(Cell cell, Map<String, IspField> ispFieldMaps, CompanyFieldForm companyFieldForm, int rowIndex) {
- if (cell != null) {
- IspField ispField = ispFieldMaps.get(CommonUtil.convertExcelStringToCell(cell));
+ private void setCompanyFormIspName(String cellStr, Map<String, IspField> ispFieldMaps, CompanyFieldForm companyFieldForm, int rowIndex, boolean isNull) {
+ if (!isNull) {
+ IspField ispField = ispFieldMaps.get(cellStr);
if (ispField == null) {
throw new OwlRuntimeException(
this.messageAccessor.getMessage(MsgConstants.EXCEL_IMPORT_ISP_NOT_EXIST, rowIndex));
@@ -699,22 +702,14 @@
}
}
- private void setCompanyFormName(Cell cell, CompanyFieldForm companyFieldForm, int rowIndex) {
- if (!cellNullCheck(cell, rowIndex)) {
- throw new OwlRuntimeException(
- this.messageAccessor.getMessage(MsgConstants.EXCEL_IMPORT_COMPANY_NAME_IS_NULL, rowIndex));
- }
-
- String title = CommonUtil.convertExcelStringToCell(cell);
-
+ private void setCompanyFormName(String title, CompanyFieldForm companyFieldForm) {
// �뾽泥대챸 �쑀�슚�꽦 泥댄겕
this.verifyTitle(title, null);
companyFieldForm.setName(title);
}
- private void setCompanyFormUrl(Cell cell, CompanyFieldForm companyFieldForm, int rowIndex) {
- if (cell != null) {
- String url = CommonUtil.convertExcelStringToCell(cell);
+ private void setCompanyFormUrl(String url, CompanyFieldForm companyFieldForm, boolean isNull) {
+ if (!isNull) {
if (url.contains(" ")) {
url = url.replace(" ", "");
}
--
Gitblit v1.8.0