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/HostingFieldServiceImpl.java | 322 ++++++++++++++++++++++++++++++++++++++++++++++++++++-
1 files changed, 313 insertions(+), 9 deletions(-)
diff --git a/src/main/java/kr/wisestone/owl/service/impl/HostingFieldServiceImpl.java b/src/main/java/kr/wisestone/owl/service/impl/HostingFieldServiceImpl.java
index 9ae3495..38ad53c 100644
--- a/src/main/java/kr/wisestone/owl/service/impl/HostingFieldServiceImpl.java
+++ b/src/main/java/kr/wisestone/owl/service/impl/HostingFieldServiceImpl.java
@@ -6,35 +6,31 @@
import kr.wisestone.owl.constant.MsgConstants;
import kr.wisestone.owl.domain.CompanyField;
import kr.wisestone.owl.domain.HostingField;
-import kr.wisestone.owl.domain.IspField;
import kr.wisestone.owl.exception.OwlRuntimeException;
import kr.wisestone.owl.mapper.HostingFieldMapper;
import kr.wisestone.owl.repository.HostingFieldRepository;
import kr.wisestone.owl.service.CompanyFieldService;
import kr.wisestone.owl.service.HostingFieldService;
import kr.wisestone.owl.service.WorkspaceService;
+import kr.wisestone.owl.util.CommonUtil;
import kr.wisestone.owl.util.ConvertUtil;
-import kr.wisestone.owl.vo.HostingFieldVo;
-import kr.wisestone.owl.vo.ExportExcelAttrVo;
-import kr.wisestone.owl.vo.ExportExcelVo;
-import kr.wisestone.owl.vo.ResPage;
+import kr.wisestone.owl.vo.*;
import kr.wisestone.owl.web.condition.HostingFieldCondition;
import kr.wisestone.owl.web.form.HostingFieldForm;
import kr.wisestone.owl.web.view.ExcelView;
import org.apache.commons.lang3.StringUtils;
+import org.apache.poi.ss.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.ui.Model;
+import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.servlet.ModelAndView;
import javax.servlet.http.HttpServletRequest;
-import java.util.Arrays;
-import java.util.HashMap;
-import java.util.List;
-import java.util.Map;
+import java.util.*;
@Service
public class HostingFieldServiceImpl extends AbstractServiceImpl<HostingField, Long, JpaRepository<HostingField, Long>> implements HostingFieldService {
@@ -62,9 +58,13 @@
return this.hostingFieldRepository;
}
+ private static final int EXCEL_IMPORT_MAX_ROWS = 10000; // excel import �젣�븳
+
// Hosting 異붽�
@Override
public HostingField add(HostingFieldForm HostingFieldForm) {
+ // �샇�뒪�똿紐� �쑀�슚�꽦 泥댄겕
+ //this.verifyTitle(title, null);
if (HostingFieldForm.getTelList() != null && HostingFieldForm.getTelList().size() > 0) {
String tels = HostingFieldForm.getTelList().toString();
if (tels.contains("[")) {
@@ -122,6 +122,8 @@
// Hosting �젙濡쒕�� �닔�젙�븳�떎.
@Override
public void modify(HostingFieldForm HostingFieldForm) {
+ // �샇�뒪�똿紐� �쑀�슚�꽦 泥댄겕
+ //this.verifyTitle(title, null);
if (HostingFieldForm.getTelList() != null && HostingFieldForm.getTelList().size() > 0) {
String tels = HostingFieldForm.getTelList().toString();
if (tels.contains("[")) {
@@ -202,6 +204,308 @@
return new ModelAndView(this.excelView);
}
+ // �샇�뒪�똿 Import �슜 �뿊�� �뀥�뵆由� �떎�슫濡쒕뱶
+ @Override
+ @Transactional
+ public ModelAndView downloadExcelTemplate(HttpServletRequest request, Model model) {
+
+ ExportExcelVo excelInfo = new ExportExcelVo();
+ excelInfo.setHideCount(true);
+ excelInfo.setFileName(this.messageAccessor.message("common.registerExcelHostingField")); // �뿊��濡� �샇�뒪�똿 �벑濡앺븯湲�
+ excelInfo.addAttrInfos(new ExportExcelAttrVo("id", this.messageAccessor.message("Hosting.HostingName"), 20, ExportExcelAttrVo.ALIGN_CENTER)); // �샇�뒪�똿紐�
+ excelInfo.addAttrInfos(new ExportExcelAttrVo("id", this.messageAccessor.message("Hosting.HostingCode"), 10, ExportExcelAttrVo.ALIGN_CENTER)); // 肄붾뱶
+ excelInfo.addAttrInfos(new ExportExcelAttrVo("id", this.messageAccessor.message("Hosting.HostingManager"), 10, ExportExcelAttrVo.ALIGN_CENTER)); // �떞�떦�옄
+ excelInfo.addAttrInfos(new ExportExcelAttrVo("id", this.messageAccessor.message("Hosting.HostingTel"), 10, ExportExcelAttrVo.ALIGN_CENTER)); // �뿰�씫泥�
+ excelInfo.addAttrInfos(new ExportExcelAttrVo("id", this.messageAccessor.message("Hosting.HostingEmail"), 10, ExportExcelAttrVo.ALIGN_CENTER)); // �씠硫붿씪
+ excelInfo.addAttrInfos(new ExportExcelAttrVo("id", this.messageAccessor.message("Hosting.HostingUrl"), 10, ExportExcelAttrVo.ALIGN_CENTER)); // url
+ excelInfo.addAttrInfos(new ExportExcelAttrVo("id", this.messageAccessor.message("Hosting.HostingMemo"), 40, ExportExcelAttrVo.ALIGN_CENTER)); // 鍮꾧퀬
+
+ // �뿊���뿉 �꽔�쓣 �뜲�씠�꽣 - HostingFieldVo �뜲�씠�꽣瑜� �뿊���뿉�꽌 �몴�떆�븷 �닔 �엳�뒗 �뜲�씠�꽣濡� 蹂�寃쏀븳�떎.
+ excelInfo.setDatas(Lists.newArrayList(new HostingFieldVo()));
+
+ model.addAttribute(Constants.EXCEL, excelInfo);
+ return new ModelAndView(this.excelView);
+ }
+
+ // �뾽濡쒕뱶 �뙆�씪 �솗�옣�옄 泥댄겕
+ private void verifyMultipartFileExtension(MultipartFile multipartFile) {
+ multipartFile.getOriginalFilename();
+
+ int pos = multipartFile.getOriginalFilename().lastIndexOf(".");
+ String ext = multipartFile.getOriginalFilename().substring(pos + 1);
+
+ if (!ext.equals("xlsx")) {
+ throw new OwlRuntimeException(this.messageAccessor.getMessage(MsgConstants.EXCEL_NOT_EXTENSION));
+ }
+ }
+
+ // �뿊�� import 濡� �샇�뒪�똿�쓣 �벑濡앺븳�떎.
+ @Override
+ @Transactional
+ public void importExcel(MultipartFile multipartFile) throws Exception {
+ // �궗�슜�븯怨� �엳�뒗 �뾽臾� 怨듦컙�씠 �솢�꽦 �긽�깭�씤吏� �솗�씤�븳�떎. �궗�슜 怨듦컙�뿉�꽌 濡쒓렇�씤�븳 �궗�슜�옄媛� 鍮꾪솢�꽦�씤吏� �솗�씤�븳�떎.
+ this.workspaceService.checkUseWorkspace();
+
+ if (multipartFile != null) {
+ // �뾽濡쒕뱶 �뙆�씪 �솗�옣�옄 泥댄겕
+ this.verifyMultipartFileExtension(multipartFile);
+
+ List<HostingFieldForm> hostingFieldForms = Lists.newArrayList();
+ List<String> headers = Lists.newArrayList();
+
+ Workbook workbook;
+
+ workbook = WorkbookFactory.create(multipartFile.getInputStream());
+ Sheet sheet = workbook.getSheetAt(0);
+ int lastRowNum = sheet.getLastRowNum() + 1;
+
+ // 2嫄� - �젣紐�, �뿤�뜑 - �꽦�뒫�쓣 �쐞�빐 理쒕� 1留뚭굔�쑝濡� �젣�븳
+ if (lastRowNum > (EXCEL_IMPORT_MAX_ROWS + 2)) {
+ throw new OwlRuntimeException(this.messageAccessor.getMessage(MsgConstants.EXCEL_IMPORT_MAX_ROWS_OVER));
+ }
+
+ for (int rowIndex = 0; rowIndex < lastRowNum; rowIndex++) {
+ // 0踰덉� �뿤�뜑�뒗 臾댁떆�븳�떎.
+ Row row = sheet.getRow(rowIndex);
+ // �뿤�뜑 �젙蹂대�� 異붿텧�븳�떎
+ if (rowIndex == 1) {
+ for (int cellIndex = 0; cellIndex < row.getLastCellNum(); cellIndex++) {
+ Cell cell = row.getCell(cellIndex);
+
+ if (cell == null) {
+ throw new OwlRuntimeException(this.messageAccessor.getMessage(MsgConstants.EXCEL_EMPTY_CELL));
+ }
+
+ // �뿊�� import �뜲�씠�꽣�뿉�꽌 cell 媛믪쓣 臾몄옄�뿴濡� 蹂��솚�븳�떎.
+ String cellValue = CommonUtil.convertExcelStringToCell(cell);
+
+ if (StringUtils.isEmpty(cellValue)) {
+ throw new OwlRuntimeException(this.messageAccessor.getMessage(MsgConstants.EXCEL_HEADER_EMPTY_CELL));
+ }
+
+ headers.add(cellValue);
+ }
+ }
+
+ // 1踰� �뿤�뜑遺��꽣 �뜲�씠�꽣 �쁺�뿭
+ if (rowIndex > 1) {
+ // �샇�뒪�똿�쑝濡� �벑濡앺븯湲� �쐞�빐 newHostingFieldForm �뿉 �뜲�씠�꽣瑜� �뀑�똿�븳�떎.
+ HostingFieldForm newHostingFieldForm = this.setHostingFieldFormToExcelField(row, (rowIndex + 1), headers);
+
+ hostingFieldForms.add(newHostingFieldForm);
+ }
+ }
+
+ if (hostingFieldForms.size() < 1) {
+ return;
+ }
+
+ for (HostingFieldForm saveHostingFieldForm : hostingFieldForms) {
+ HostingField hostingField = new HostingField();
+ ConvertUtil.copyProperties(saveHostingFieldForm, hostingField);
+
+ hostingField = this.hostingFieldRepository.saveAndFlush(hostingField);
+
+ saveHostingFieldForm.setId(hostingField.getId());
+ }
+ }
+ }
+
+ /**
+ * 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 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;
+ }
+ }
+ return true;
+ }
+
+ /**
+ * �쟾�솕踰덊샇 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));
+ }
+ }
+
+ // �뿊�� �븘�뱶�뿉 �엳�뒗 �젙蹂대�� HOSTING form �쑝濡� �삷湲대떎.
+ private HostingFieldForm setHostingFieldFormToExcelField(Row row, int rowIndex, List<String> headers) {
+ HostingFieldForm hostingFieldForm = new HostingFieldForm();
+ hostingFieldForm.setRegisterId(this.webAppUtil.getLoginId());
+
+ 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:
+ // �샇�뒪�똿紐�
+ if (isNull) {
+ throw new OwlRuntimeException(
+ this.messageAccessor.getMessage(MsgConstants.EXCEL_IMPORT_HOSTING_NAME_IS_NULL, rowIndex));
+ }
+ this.setHostingFormName(cellStr, hostingFieldForm);
+ break;
+
+ case 1:
+ // 肄붾뱶
+ if (isNull) {
+ throw new OwlRuntimeException(
+ this.messageAccessor.getMessage(MsgConstants.EXCEL_IMPORT_HOSTING_CODE_IS_NULL, rowIndex));
+ }
+ this.setHostingFormCode(cellStr, hostingFieldForm);
+ break;
+
+ case 2:
+ // �떞�떦�옄
+ this.setHostingFormManager(cellStr, hostingFieldForm, isNull);
+ break;
+
+ case 3:
+ // �뿰�씫泥�
+ telTypeCheck(cell, rowIndex);
+ this.setHostingFormTel(cellStr, hostingFieldForm, isNull);
+ break;
+
+ case 4:
+ // �씠硫붿씪
+ this.setHostingFormEmail(cellStr, hostingFieldForm, isNull);
+ break;
+
+ case 5:
+ // url
+ this.setHostingFormUrl(cellStr, hostingFieldForm, isNull);
+ break;
+
+ case 6:
+ // 鍮꾧퀬
+ this.setHostingFormMemo(cellStr, hostingFieldForm, isNull);
+ }
+ }
+
+ return hostingFieldForm;
+ }
+
+ private void setHostingFormMemo(String memo, HostingFieldForm hostingFieldForm, boolean isNull) {
+ if (!isNull) {
+ hostingFieldForm.setMemo(memo);
+ }
+ }
+
+ private void setHostingFormUrl(String url, HostingFieldForm hostingFieldForm, boolean isNull) {
+ if (!isNull) {
+ if (url.contains(" ")) {
+ url = url.replace(" ", "");
+ }
+ hostingFieldForm.setUrl(url);
+ }
+ }
+
+ private void setHostingFormEmail(String email, HostingFieldForm hostingFieldForm, boolean isNull) {
+ if (!isNull) {
+ if (email.contains(" ")) {
+ email = email.replace(" ", "");
+ }
+ hostingFieldForm.setEmail(email);
+ }
+ }
+
+ private void setHostingFormTel(String tel, HostingFieldForm hostingFieldForm, boolean isNull) {
+ if (!isNull) {
+ if (tel.contains("-")) {
+ tel = tel.replace("-", "");
+ }
+ if (tel.contains(" ")) {
+ tel = tel.replace(" ", "");
+ }
+ hostingFieldForm.setTel(tel);
+ }
+ }
+
+ private void setHostingFormManager(String manager, HostingFieldForm hostingFieldForm, boolean isNull) {
+ if (!isNull) {
+ hostingFieldForm.setManager(manager);
+ }
+ }
+
+ private void setHostingFormCode(String code, HostingFieldForm hostingFieldForm) {
+ //肄붾뱶 �쑀�슚�꽦 泥댄겕
+ hostingFieldForm.setCode(code);
+ }
+
+ private void setHostingFormName(String title, HostingFieldForm hostingFieldForm) {
+ // �샇�뒪�똿紐� �쑀�슚�꽦 泥댄겕
+ //this.verifyTitle(title, null);
+ hostingFieldForm.setName(title);
+ }
+
+ // �샇�뒪�똿紐� �쑀�슚�꽦 泥댄겕
+ private void verifyTitle(String title, Long id) {
+ List<HostingField> hostingFields = new ArrayList<>();
+
+ // �샇�뒪�똿紐� 以묐났 泥댄겕
+ if (id != null) { //�닔�젙 �씪 寃쎌슦
+ hostingFields = this.hostingFieldRepository.findByNameAndIdNot(title, id);
+ } else { // 異붽� �씪 寃쎌슦
+ hostingFields = this.hostingFieldRepository.findByName(title);
+ }
+ if (hostingFields != null && hostingFields.size() > 0) {
+ throw new OwlRuntimeException(
+ this.messageAccessor.getMessage(MsgConstants.HOSTING_NAME_ALREADY_IN_USE));
+ }
+
+ // �샇�뒪�똿紐� 鍮덇컪 泥댄겕
+ if (StringUtils.isEmpty(title)) {
+ throw new OwlRuntimeException(
+ this.messageAccessor.getMessage(MsgConstants.HOSTING_NO_TITLE));
+ }
+
+ // �샇�뒪�똿紐� 湲몄씠 泥댄겕
+ if (title.length() > 300) {
+ throw new OwlRuntimeException(
+ this.messageAccessor.getMessage(MsgConstants.HOSTING_NAME_MAX_LENGTH_OUT));
+ }
+ }
+
// 寃��깋 寃곌낵瑜� HostingVo 濡� 蹂��솚�븳�떎.
private List<HostingFieldVo> convertHostingVoToMap(List<Map<String, Object>> results, Long totalHostingCount, Pageable pageable, Map<String, Object> resJsonData) {
List<HostingFieldVo> hostingFieldVos = Lists.newArrayList();
--
Gitblit v1.8.0