- isp, 호스팅 엑셀 import 기능 추가

이민희

2022-02-09 a227dd7df7f84a5cfb7218042a6f844991dcfb96

- isp, 호스팅 엑셀 import 기능 추가

 src/main/java/kr/wisestone/owl/constant/MsgConstants.java

@@ -202,6 +202,12 @@
    public static final String EXCEL_IMPORT_CHILD_SECTOR_NOT_EXIST = "EXCEL_IMPORT_CHILD_SECTOR_NOT_EXIST";   //  다음 엑셀 라인에서 입력된 업종명으로 검색되는 업종(중분류)이 없습니다
    public static final String EXCEL_IMPORT_REGION_NOT_EXIST = "EXCEL_IMPORT_REGION_NOT_EXIST";   //  다음 엑셀 라인에서 입력된 지역명으로 검색되는 지역이 없습니다

    public static final String EXCEL_IMPORT_ISP_NAME_IS_NULL = "EXCEL_IMPORT_ISP_NAME_IS_NULL";   //  다음 엑셀 라인에서 ISP명이 입력되지 않았습니다.
    public static final String EXCEL_IMPORT_ISP_CODE_IS_NULL = "EXCEL_IMPORT_ISP_CODE_IS_NULL";   //  다음 엑셀 라인에서 코드가 입력되지 않았습니다.

    public static final String EXCEL_IMPORT_HOSTING_NAME_IS_NULL = "EXCEL_IMPORT_HOSTING_NAME_IS_NULL";   //  다음 엑셀 라인에서 호스팅명이 입력되지 않았습니다.
    public static final String EXCEL_IMPORT_HOSTING_CODE_IS_NULL = "EXCEL_IMPORT_HOSTING_CODE_IS_NULL";   //  다음 엑셀 라인에서 코드가 입력되지 않았습니다.

    public static final String DOWN_ISSUE_NOT_EXIST = "DOWN_ISSUE_NOT_EXIST";   //  하위 이슈 찾을수 없음

    public static final String NOTICE_NOT_EXIST = "NOTICE_NOT_EXIST";   //  공지사항이 존재하지 않습니다.
@@ -258,15 +264,22 @@
    public static final String COMPANY_USED_URL = "COMPANY_USED_URL";   // URL이 이미 사용되고 있습니다.
    public static final String COMPANY_NAME_MAX_LENGTH_OUT = "COMPANY_NAME_MAX_LENGTH_OUT";   //  업체명은 최대 300글자까지 입력할 수 있습니다.

    public static final String HOSTING_NOT_EXIST = "HOSTING_NOT_EXIST";   // 호스팅이 존재하지 않습니다.
    public static final String HOSTING_NO_TITLE = "HOSTING_NO_TITLE";   // 호스팅명이 입력되지 않았습니다.
    public static final String HOSTING_CODE_NOT_ENTER = "HOSTING_CODE_NOT_ENTER";   // 호스팅의 코드는 필수 입력 값입니다.
    public static final String HOSTING_REMOVE_NOT_SELECT = "HOSTING_REMOVE_NOT_SELECT";   // 삭제할 호스팅이 선택되지 않았습니다.
    public static final String HOSTING_NOT_EXIST = "HOSTING_NOT_EXIST";   // 호스팅이 존재하지 않습니다.
    public static final String HOSTING_NAME_MAX_LENGTH_OUT = "HOSTING_NAME_MAX_LENGTH_OUT";  // 호스팅명은 최대 300글자까지 입력할 수 있습니다.
    public static final String HOSTING_NAME_ALREADY_IN_USE = "HOSTING_NAME_ALREADY_IN_USE";   // 해당 호스팅명은 이미 등록되어 있습니다.
    public static final String HOSTING_EXIST_COMPANY = "HOSTING_EXIST_COMPANY";   // 업체에 설정되어있는 호스팅이 존재합니다.

    public static final String ISP_NOT_EXIST = "ISP_NOT_EXIST";   // ISP가 존재하지 않습니다.
    public static final String ISP_NO_TITLE = "ISP_NO_TITLE";   // ISP명이 입력되지 않았습니다.
    public static final String ISP_CODE_NOT_ENTER = "ISP_CODE_NOT_ENTER";   // ISP의 코드는 필수 입력 값입니다.
    public static final String ISP_REMOVE_NOT_SELECT = "ISP_REMOVE_NOT_SELECT";   // 삭제할 ISP가 선택되지 않았습니다.
    public static final String ISP_NOT_EXIST = "ISP_NOT_EXIST";   // ISP가 존재하지 않습니다.
    public static final String ISP_NAME_MAX_LENGTH_OUT = "ISP_NAME_MAX_LENGTH_OUT";  // ISP명은 최대 300글자까지 입력할 수 있습니다.
    public static final String ISP_NAME_ALREADY_IN_USE = "ISP_NAME_ALREADY_IN_USE";   // 해당 ISP명은 이미 등록되어 있습니다.
    public static final String ISP_EXIST_COMPANY = "ISP_EXIST_COMPANY";   // 업체에 설정되어있는 ISP가 존재합니다.

    public static final String PROJECT_NOT_INCLUDE_DEPARTMENT = "PROJECT_NOT_INCLUDE_DEPARTMENT";   // 선택한 부서 중 프로젝트에 참여하고 있지 않은 부서가 있습니다.

    public static final String EMAIL_TEMPLATE_NOT_EXIST = "EMAIL_TEMPLATE_NOT_EXIST"; //  이메일 템플릿을 찾을수 없습니다.

 src/main/java/kr/wisestone/owl/repository/HostingFieldRepository.java

@@ -6,8 +6,14 @@
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.repository.query.Param;

import java.util.List;

public interface HostingFieldRepository extends JpaRepository<HostingField, Long> {
    HostingField findByUrl(@Param("url") String url);

    HostingField findByUrlAndIdNot(@Param("url") String url, @Param("id") Long id);

    List<HostingField> findByNameAndIdNot(@Param("name") String name, @Param("id") Long id);

    List<HostingField> findByName(@Param("name") String name);
}

 src/main/java/kr/wisestone/owl/repository/IspFieldRepository.java

@@ -5,8 +5,14 @@
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.repository.query.Param;

import java.util.List;

public interface IspFieldRepository extends JpaRepository<IspField, Long> {
    IspField findByUrl(@Param("url") String url);

    IspField findByUrlAndIdNot(@Param("url") String url, @Param("id") Long id);

    List<IspField> findByNameAndIdNot(@Param("name") String name, @Param("id") Long id);

    List<IspField> findByName(@Param("name") String name);
}

 src/main/java/kr/wisestone/owl/service/HostingFieldService.java

@@ -7,6 +7,7 @@
import kr.wisestone.owl.web.form.HostingFieldForm;
import org.springframework.data.domain.Pageable;
import org.springframework.ui.Model;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.servlet.ModelAndView;

import javax.servlet.http.HttpServletRequest;
@@ -30,6 +31,10 @@

    ModelAndView downloadExcel(HttpServletRequest request, Model model);

    ModelAndView downloadExcelTemplate(HttpServletRequest request, Model model);

    void importExcel(MultipartFile multipartFile) throws Exception;

    List<HostingField> findAll();

}

 src/main/java/kr/wisestone/owl/service/IspFieldService.java

@@ -6,6 +6,7 @@
import kr.wisestone.owl.web.form.IspFieldForm;
import org.springframework.data.domain.Pageable;
import org.springframework.ui.Model;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.servlet.ModelAndView;

import javax.servlet.http.HttpServletRequest;
@@ -29,5 +30,9 @@

    ModelAndView downloadExcel(HttpServletRequest request, Model model);

    ModelAndView downloadExcelTemplate(HttpServletRequest request, Model model);

    void importExcel(MultipartFile multipartFile) throws Exception;

    List<IspField> findAll();
}

 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(" ", "");
            }

 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();

 src/main/java/kr/wisestone/owl/service/impl/IspFieldServiceImpl.java

@@ -13,28 +13,26 @@
import kr.wisestone.owl.service.CompanyFieldService;
import kr.wisestone.owl.service.IspFieldService;
import kr.wisestone.owl.service.WorkspaceService;
import kr.wisestone.owl.util.CommonUtil;
import kr.wisestone.owl.util.ConvertUtil;
import kr.wisestone.owl.vo.IspFieldVo;
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.IspFieldCondition;
import kr.wisestone.owl.web.form.CompanyFieldForm;
import kr.wisestone.owl.web.form.IspFieldForm;
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 IspFieldServiceImpl extends AbstractServiceImpl<IspField, Long, JpaRepository<IspField, Long>> implements IspFieldService {
@@ -62,9 +60,14 @@
        return this.ispFieldRepository;
    }

    private static final int EXCEL_IMPORT_MAX_ROWS = 10000; //  excel import 제한

    // Isp 추가
    @Override
    public IspField add(IspFieldForm IspFieldForm) {
        //  ISP명 중복 체크
        //this.verifyTitle(IspFieldForm.getName(), null);
        
        if (IspFieldForm.getTelList() != null && IspFieldForm.getTelList().size() > 0) {
            String tels = IspFieldForm.getTelList().toString();
            if (tels.contains("[")) {
@@ -122,6 +125,9 @@
    // Isp 정로를 수정한다.
    @Override
    public void modify(IspFieldForm IspFieldForm) {
        //  ISP명 유효성 체크
        //this.verifyTitle(IspFieldForm.getName(), IspFieldForm.getId());

        if (IspFieldForm.getTelList() != null && IspFieldForm.getTelList().size() > 0) {
            String tels = IspFieldForm.getTelList().toString();
            if (tels.contains("[")) {
@@ -204,6 +210,309 @@
        return new ModelAndView(this.excelView);
    }

    //  ISP Import 용 엑셀 템플릿 다운로드
    @Override
    @Transactional
    public ModelAndView downloadExcelTemplate(HttpServletRequest request, Model model) {

        ExportExcelVo excelInfo = new ExportExcelVo();
        excelInfo.setHideCount(true);
        excelInfo.setFileName(this.messageAccessor.message("common.registerExcelIspField")); // 엑셀로 ISP 등록하기
        excelInfo.addAttrInfos(new ExportExcelAttrVo("id", this.messageAccessor.message("isp.ispName"), 20, ExportExcelAttrVo.ALIGN_CENTER)); // ISP명
        excelInfo.addAttrInfos(new ExportExcelAttrVo("id", this.messageAccessor.message("isp.ispCode"), 10, ExportExcelAttrVo.ALIGN_CENTER)); // 코드
        excelInfo.addAttrInfos(new ExportExcelAttrVo("id", this.messageAccessor.message("isp.ispManager"), 10, ExportExcelAttrVo.ALIGN_CENTER)); // 담당자
        excelInfo.addAttrInfos(new ExportExcelAttrVo("id", this.messageAccessor.message("isp.ispTel"), 10, ExportExcelAttrVo.ALIGN_CENTER)); // 연락처
        excelInfo.addAttrInfos(new ExportExcelAttrVo("id", this.messageAccessor.message("isp.ispEmail"), 10, ExportExcelAttrVo.ALIGN_CENTER)); // 이메일
        excelInfo.addAttrInfos(new ExportExcelAttrVo("id", this.messageAccessor.message("isp.ispUrl"), 10, ExportExcelAttrVo.ALIGN_CENTER)); // url
        excelInfo.addAttrInfos(new ExportExcelAttrVo("id", this.messageAccessor.message("isp.ispMemo"), 40, ExportExcelAttrVo.ALIGN_CENTER)); // 비고

        //  엑셀에 넣을 데이터 - IspFieldVo 데이터를 엑셀에서 표시할 수 있는 데이터로 변경한다.
        excelInfo.setDatas(Lists.newArrayList(new IspFieldVo()));

        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 로 ISP를 등록한다.
    @Override
    @Transactional
    public void importExcel(MultipartFile multipartFile) throws Exception {
        //  사용하고 있는 업무 공간이 활성 상태인지 확인한다. 사용 공간에서 로그인한 사용자가 비활성인지 확인한다.
        this.workspaceService.checkUseWorkspace();

        if (multipartFile != null) {
            //  업로드 파일 확장자 체크
            this.verifyMultipartFileExtension(multipartFile);

            List<IspFieldForm> ispFieldForms = 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) {
                    //  ISP로 등록하기 위해 ispFieldForm 에 데이터를 셋팅한다.
                    IspFieldForm newIspFieldForm = this.setIspFieldFormToExcelField(row, (rowIndex + 1), headers);

                    ispFieldForms.add(newIspFieldForm);
                }
            }

            if (ispFieldForms.size() < 1) {
                return;
            }

            for (IspFieldForm saveIspFieldForm : ispFieldForms) {
                IspField ispField = new IspField();
                ConvertUtil.copyProperties(saveIspFieldForm, ispField);

                ispField = this.ispFieldRepository.saveAndFlush(ispField);

                saveIspFieldForm.setId(ispField.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));
        }
    }

    //  엑셀 필드에 있는 정보를 ISP form 으로 옮긴다.
    private IspFieldForm setIspFieldFormToExcelField(Row row, int rowIndex, List<String> headers) {
        IspFieldForm ispFieldForm = new IspFieldForm();
        ispFieldForm.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:
                    //  ISP명
                    if (isNull) {
                        throw new OwlRuntimeException(
                                this.messageAccessor.getMessage(MsgConstants.EXCEL_IMPORT_ISP_NAME_IS_NULL, rowIndex));
                    }
                    this.setIspFormName(cellStr, ispFieldForm);
                    break;

                case 1:
                    //  코드
                    if (isNull) {
                        throw new OwlRuntimeException(
                                this.messageAccessor.getMessage(MsgConstants.EXCEL_IMPORT_ISP_CODE_IS_NULL, rowIndex));
                    }
                    this.setIspFormCode(cellStr, ispFieldForm);
                    break;

                case 2:
                    // 담당자
                    this.setIspFormManager(cellStr, ispFieldForm, isNull);
                    break;

                case 3:
                    // 연락처
                    telTypeCheck(cell, rowIndex);
                    this.setIspFormTel(cellStr, ispFieldForm, rowIndex, isNull);
                    break;

                case 4:
                    // 이메일
                    this.setIspFormEmail(cellStr, ispFieldForm, isNull);
                    break;

                case 5:
                    // url
                    this.setIspFormUrl(cellStr, ispFieldForm, isNull);
                    break;

                case 6:
                    // 비고
                    this.setIspFormMemo(cellStr, ispFieldForm, isNull);
            }
        }

        return ispFieldForm;
    }

    private void setIspFormMemo(String memo, IspFieldForm ispFieldForm, boolean isNull) {
        if (!isNull) {
            ispFieldForm.setMemo(memo);
        }
    }

    private void setIspFormUrl(String url, IspFieldForm ispFieldForm, boolean isNull) {
        if (!isNull) {
            if (url.contains(" ")) {
                url = url.replace(" ", "");
            }
            ispFieldForm.setUrl(url);
        }
    }

    private void setIspFormEmail(String email, IspFieldForm ispFieldForm, boolean isNull) {
        if (!isNull) {
            if (email.contains(" ")) {
                email = email.replace(" ", "");
            }
            ispFieldForm.setEmail(email);
        }
    }

    private void setIspFormTel(String tel, IspFieldForm ispFieldForm, int rowIndex, boolean isNull) {
        if (!isNull) {
            if (tel.contains("-")) {
                tel = tel.replace("-", "");
            }
            if (tel.contains(" ")) {
                tel = tel.replace(" ", "");
            }
            ispFieldForm.setTel(tel);
        }
    }

    private void setIspFormManager(String manager, IspFieldForm ispFieldForm, boolean isNull) {
        if (!isNull) {
            ispFieldForm.setManager(manager);
        }
    }

    private void setIspFormCode(String code, IspFieldForm ispFieldForm) {
        //코드 유효성 체크
        ispFieldForm.setCode(code);
    }

    private void setIspFormName(String title, IspFieldForm ispFieldForm) {
        //  ISP명 유효성 체크
        //this.verifyTitle(title, null);
        ispFieldForm.setName(title);
    }

    //  ISP명 유효성 체크
    private void verifyTitle(String title, Long id) {
        List<IspField> ispFields = new ArrayList<>();

        //  ISP명 중복 체크

        if (id != null) { //수정 일 경우
            ispFields = this.ispFieldRepository.findByNameAndIdNot(title, id);
        } else { // 추가 일 경우
            ispFields = this.ispFieldRepository.findByName(title);
        }
        if (ispFields != null && ispFields.size() > 0) {
            throw new OwlRuntimeException(
                    this.messageAccessor.getMessage(MsgConstants.ISP_NAME_ALREADY_IN_USE));
        }

        //  ISP명 빈값 체크
        if (StringUtils.isEmpty(title)) {
            throw new OwlRuntimeException(
                    this.messageAccessor.getMessage(MsgConstants.ISP_NO_TITLE));
        }

        //  ISP명 길이 체크
        if (title.length() > 300) {
            throw new OwlRuntimeException(
                    this.messageAccessor.getMessage(MsgConstants.ISP_NAME_MAX_LENGTH_OUT));
        }
    }

    //  검색 결과를 IspVo 로 변환한다.
    private List<IspFieldVo> convertIspVoToMap(List<Map<String, Object>> results, Long totalIspCount, Pageable pageable, Map<String, Object> resJsonData) {
        List<IspFieldVo> ispFieldVos = Lists.newArrayList();
@@ -238,4 +547,6 @@
        }
        return IspField;
    }


}

 src/main/java/kr/wisestone/owl/service/impl/IssueServiceImpl.java

@@ -3126,7 +3126,7 @@
                //  1번 헤더부터 데이터 영역
                if (rowIndex > 1) {
                    //  이슈로 등록하기 위해 IssueForm 에 데이터를 셋팅한다.
                    IssueForm newIssueForm = this.setIssueFormToExcelField(row, (rowIndex + 1), priorityMaps, severityMaps, departmentMaps, customFieldMaps,
                    IssueForm newIssueForm = this.setIssueFormToExcelField(row, (rowIndex + 1), priorityMaps, severityMaps, customFieldMaps,
                            companyFieldMaps, ispFieldMaps, hostingFieldMaps, headers);
                    ConvertUtil.copyProperties(issueForm, newIssueForm);

@@ -3470,23 +3470,38 @@
    }

    /**
     * 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 체크 함수
     * 빈 값이 아닌 cell 체크
     * @param cell Cell
     * @return boolean
     */
    private Boolean cellNullCheck (Cell cell) {
        if (cell != null ) {
            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 {
        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;
@@ -3494,32 +3509,22 @@

    //  엑셀 필드에 있는 정보를 이슈 form 으로 옮긴다.
    private IssueForm setIssueFormToExcelField(Row row, int rowIndex, Map<String, Priority> priorityMaps,
                                               Map<String, Severity> severityMaps, Map<String, DepartmentVo> departmentMaps,
                                               Map<String, CustomField> customFieldMaps, Map<String, CompanyField> companyFieldMaps,
                                               Map<String, IspField> ispFieldMaps, Map<String, HostingField> hostingFieldMaps,
                                               Map<String, Severity> severityMaps, Map<String, CustomField> customFieldMaps,
                                               Map<String, CompanyField> companyFieldMaps, Map<String, IspField> ispFieldMaps, Map<String, HostingField> hostingFieldMaps,
                                               List<String> headers) throws ParseException {
        IssueForm issueForm = new IssueForm();
        issueForm.setRegisterId(this.webAppUtil.getLoginId());
        Project project = null;

        //  제목, 내용, 프로젝트 키, 이슈 타입, 우선순위, 중요도, 담당자, 시작일, 종료일, 사용자 정의 필드
        for (int cellIndex = 0; cellIndex < headers.size(); cellIndex++) {
            Cell cell = row.getCell(cellIndex);

            boolean isNull = true;
            String cellStr = "";
            boolean isNull = true;

            if (cell != null) {
                isNull = cellNullCheck(cell);

                if (!isNull) {
                    cellStr = CommonUtil.convertExcelStringToCell(cell);

                    //  공백 제거
                    cell.setCellValue(cellStr.trim());
                } else {
                    cell.setCellValue(cellStr);
                }
                cellStr = stringToCell(cell, isNull); //cell을 String으로 변환
            }

            switch (cellIndex) {

 src/main/java/kr/wisestone/owl/web/controller/HostingFieldController.java

@@ -14,6 +14,7 @@
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartHttpServletRequest;
import org.springframework.web.servlet.ModelAndView;

import javax.servlet.http.HttpServletRequest;
@@ -26,7 +27,7 @@
    @Autowired
    private HostingFieldService HostingFieldService;

    // 업체 생성
    // 호스팅 생성
    @RequestMapping(value = "/hostingField/add", method = RequestMethod.POST)
    public
    @ResponseBody
@@ -38,7 +39,7 @@
        return this.setSuccessMessage(resJsonData);
    }

    // 업체 조회
    // 호스팅 조회
    @RequestMapping(value = "/hostingField/find", method = RequestMethod.POST, produces = MediaType.APPLICATION_JSON_VALUE)
    public
    @ResponseBody
@@ -51,7 +52,7 @@
        return this.setSuccessMessage(resJsonData);
    }

    // 업체 상세 조회
    // 호스팅 상세 조회
    @RequestMapping(value = "/hostingField/detail", produces = MediaType.APPLICATION_JSON_VALUE)
    public
    @ResponseBody
@@ -62,7 +63,7 @@
        return this.setSuccessMessage(resJsonData);
    }

    // 업체 수정
    // 호스팅 수정
    @RequestMapping(value = "/hostingField/modify", produces = MediaType.APPLICATION_JSON_VALUE)
    public
    @ResponseBody
@@ -74,7 +75,7 @@
        return this.setSuccessMessage(resJsonData);
    }

    // 업체 삭제
    // 호스팅 삭제
    @RequestMapping(value = "/hostingField/remove", method = RequestMethod.POST, produces = MediaType.APPLICATION_JSON_VALUE)
    public
    @ResponseBody
@@ -98,11 +99,26 @@
        return this.setSuccessMessage(resJsonData);
    }


    // 업체 엑셀 다운로드
    // 호스팅 엑셀 다운로드
    @RequestMapping(value = "/hostingField/downloadExcel", method = RequestMethod.POST)
    public ModelAndView downloadExcel(HttpServletRequest request, Model model) {
        return this.HostingFieldService.downloadExcel(request, model);
    }

    //  호스팅 Import 용 엑셀 템플릿 다운로드
    @RequestMapping(value = "/hostingField/downloadExcelTemplate", method = RequestMethod.POST)
    public ModelAndView downloadExcelImport(HttpServletRequest request, Model model) {
        return this.HostingFieldService.downloadExcelTemplate(request, model);
    }

    //  호스팅 엑셀 등록
    @RequestMapping(value = "/hostingField/importExcel", method = RequestMethod.POST)
    public @ResponseBody Map<String, Object> importExcel(MultipartHttpServletRequest request) throws Exception {
        Map<String, Object> resJsonData = new HashMap<>();

        this.HostingFieldService.importExcel(request.getFile("file"));

        return this.setSuccessMessage(resJsonData);
    }
}


 src/main/java/kr/wisestone/owl/web/controller/IspFieldController.java

@@ -13,6 +13,7 @@
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartHttpServletRequest;
import org.springframework.web.servlet.ModelAndView;

import javax.servlet.http.HttpServletRequest;
@@ -25,7 +26,7 @@
    @Autowired
    private IspFieldService IspFieldService;

    // 업체 생성
    // ISP 생성
    @RequestMapping(value = "/ispField/add", method = RequestMethod.POST)
    public
    @ResponseBody
@@ -37,7 +38,7 @@
        return this.setSuccessMessage(resJsonData);
    }

    // 업체 조회
    // ISP 조회
    @RequestMapping(value = "/ispField/find", method = RequestMethod.POST, produces = MediaType.APPLICATION_JSON_VALUE)
    public
    @ResponseBody
@@ -50,7 +51,7 @@
        return this.setSuccessMessage(resJsonData);
    }

    // 업체 상세 조회
    // ISP 상세 조회
    @RequestMapping(value = "/ispField/detail", produces = MediaType.APPLICATION_JSON_VALUE)
    public
    @ResponseBody
@@ -61,7 +62,7 @@
        return this.setSuccessMessage(resJsonData);
    }

    // 업체 수정
    // ISP 수정
    @RequestMapping(value = "/ispField/modify", produces = MediaType.APPLICATION_JSON_VALUE)
    public
    @ResponseBody
@@ -73,7 +74,7 @@
        return this.setSuccessMessage(resJsonData);
    }

    // 업체 삭제
    // ISP 삭제
    @RequestMapping(value = "/ispField/remove", method = RequestMethod.POST, produces = MediaType.APPLICATION_JSON_VALUE)
    public
    @ResponseBody
@@ -86,11 +87,26 @@
        return this.setSuccessMessage(resJsonData);
    }


    // 업체 엑셀 다운로드
    // ISP 엑셀 다운로드
    @RequestMapping(value = "/ispField/downloadExcel", method = RequestMethod.POST)
    public ModelAndView downloadExcel(HttpServletRequest request, Model model) {
        return this.IspFieldService.downloadExcel(request, model);
    }

    //  ISP Import 용 엑셀 템플릿 다운로드
    @RequestMapping(value = "/ispField/downloadExcelTemplate", method = RequestMethod.POST)
    public ModelAndView downloadExcelImport(HttpServletRequest request, Model model) {
        return this.IspFieldService.downloadExcelTemplate(request, model);
    }

    //  ISP 엑셀 등록
    @RequestMapping(value = "/ispField/importExcel", method = RequestMethod.POST)
    public @ResponseBody Map<String, Object> importExcel(MultipartHttpServletRequest request) throws Exception {
        Map<String, Object> resJsonData = new HashMap<>();

        this.IspFieldService.importExcel(request.getFile("file"));

        return this.setSuccessMessage(resJsonData);
    }
}


 src/main/java/kr/wisestone/owl/web/form/CompanyFieldForm.java

@@ -26,7 +26,7 @@
    private String regionName;
    private String statusName;

    private Long registerId;    //  등록자 아이디 - insert batch 에서 사용
    private Long registerId;    //  등록자 아이디 - excel import 에서 사용

    private List<Long> removeIds = Lists.newArrayList();


 src/main/java/kr/wisestone/owl/web/form/HostingFieldForm.java

@@ -15,6 +15,8 @@
    private String memo;
    private List<Long> removeIds = Lists.newArrayList();

    private Long registerId;    //  등록자 아이디 - excel import 에서 사용

    public HostingFieldForm() {
    }
    public static HostingFieldForm make(Map<String, Object> params) {
@@ -81,4 +83,12 @@
    public void setUrl(String url) {
        this.url = url;
    }

    public Long getRegisterId() {
        return registerId;
    }

    public void setRegisterId(Long registerId) {
        this.registerId = registerId;
    }
}

 src/main/java/kr/wisestone/owl/web/form/IspFieldForm.java

@@ -15,6 +15,8 @@
    private String memo;
    private List<Long> removeIds = Lists.newArrayList();

    private Long registerId;    //  등록자 아이디 - excel import 에서 사용

    public IspFieldForm() {
    }
    public static IspFieldForm make(Map<String, Object> params) {
@@ -82,4 +84,12 @@
    public void setUrl(String url) {
        this.url = url;
    }

    public Long getRegisterId() {
        return registerId;
    }

    public void setRegisterId(Long registerId) {
        this.registerId = registerId;
    }
}

 src/main/webapp/WEB-INF/i18n/code_ko_KR.properties

@@ -43,6 +43,8 @@
common.inProgressProjectStatus=\uC9C4\uD589 \uC911\uC778 \uD504\uB85C\uC81D\uD2B8 \uD604\uD669
common.registerExcelIssue=\uC5D1\uC140\uB85C \uC774\uC288 \uB4F1\uB85D\uD558\uAE30
common.registerExcelCompanyField=\uC5D1\uC140\uB85C \uC5C5\uCCB4 \uB4F1\uB85D\uD558\uAE30
common.registerExcelIspField=\uC5D1\uC140\uB85C ISP \uB4F1\uB85D\uD558\uAE30
common.registerExcelHostingField=\uC5D1\uC140\uB85C \uD638\uC2A4\uD305 \uB4F1\uB85D\uD558\uAE30
common.assignedToMeIssue=\uB098\uC5D0\uAC8C \uD560\uB2F9\uB41C \uC774\uC288
common.managementRisk=\uC704\uD5D8 \uAD00\uB9AC
common.registeredByMeIssue=\uB0B4\uAC00 \uB4F1\uB85D\uD55C \uC774\uC288 \uD604\uD669

 src/main/webapp/WEB-INF/i18n/messages_ko_KR.properties

@@ -202,8 +202,14 @@
EXCEL_IMPORT_CHILD_SECTOR_NOT_EXIST=\uB2E4\uC74C \uC5D1\uC140 \uB77C\uC778\uC5D0\uC11C \uC785\uB825\uB41C \uC5C5\uC885\uBA85\uC73C\uB85C \uAC80\uC0C9\uB418\uB294 \uC5C5\uC885(\uC911\uBD84\uB958)\uC774 \uC5C6\uC2B5\uB2C8\uB2E4. \n \uB77C\uC778 \uC815\uBCF4 : {0}
EXCEL_IMPORT_REGION_NOT_EXIST=\uB2E4\uC74C \uC5D1\uC140 \uB77C\uC778\uC5D0\uC11C \uC785\uB825\uB41C \uC9C0\uC5ED\uBA85\uC73C\uB85C \uAC80\uC0C9\uB418\uB294 \uC9C0\uC5ED\uC774 \uC5C6\uC2B5\uB2C8\uB2E4. \n \uB77C\uC778 \uC815\uBCF4 : {0}
EXCEL_IMPORT_PARENT_SECTOR_IS_NULL=\uB2E4\uC74C \uC5D1\uC140 \uB77C\uC778\uC5D0\uC11C \uC5C5\uC885(\uC911\uBD84\uB958)\uB9CC \uC785\uB825 \uD588\uC2B5\uB2C8\uB2E4. \n \uC5C5\uC885(\uB300\uBD84\uB958)\uB3C4 \uC785\uB825\uC774 \uD544\uC694\uD569\uB2C8\uB2E4. \n \uB77C\uC778 \uC815\uBCF4 : {0}
EXCEL_IMPORT_TEL_NOT_STRING_TYPE=\uB2E4\uC74C \uC5D1\uC140 \uB77C\uC778\uC5D0\uC11C \uC785\uB825\uB41C \uC5F0\uB77D\uCC98\uC758 \uD615\uC2DD\uC774 \uC798\uBABB \uB418\uC5C8\uC2B5\uB2C8\uB2E4. \n \uB77C\uC778 \uC815\uBCF4 : {0}
EXCEL_IMPORT_TEL_NOT_STRING_TYPE=\uB2E4\uC74C \uC5D1\uC140 \uB77C\uC778\uC5D0\uC11C \uC785\uB825\uB41C \uC5F0\uB77D\uCC98\uC758 \uD615\uC2DD\uC774 \uC798\uBABB \uB418\uC5C8\uC2B5\uB2C8\uB2E4. \uBB38\uC790\uC5F4 \uD615\uC2DD(\uD14D\uC2A4\uD2B8)\uC73C\uB85C \uC785\uB825 \uD574\uC8FC\uC138\uC694. \n \uB77C\uC778 \uC815\uBCF4 : {0}
EXCEL_IMPORT_NOT_STRING_TYPE=\uB2E4\uC74C \uC5D1\uC140 \uB77C\uC778\uC5D0\uC11C \uC785\uB825\uB41C \uC140 \uC11C\uC2DD\uC758 \uD615\uC2DD\uC774 \uBB38\uC790 \uD615\uC2DD\uC774 \uC544\uB2D9\uB2C8\uB2E4. \n \uB77C\uC778 \uC815\uBCF4 : {0}

EXCEL_IMPORT_ISP_NAME_IS_NULL=\uB2E4\uC74C \uC5D1\uC140 \uB77C\uC778\uC5D0\uC11C ISP\uBA85\uC774 \uC785\uB825\uB418\uC9C0 \uC54A\uC558\uC2B5\uB2C8\uB2E4. \n \uB77C\uC778 \uC815\uBCF4 : {0}
EXCEL_IMPORT_ISP_CODE_IS_NULL=\uB2E4\uC74C \uC5D1\uC140 \uB77C\uC778\uC5D0\uC11C \uCF54\uB4DC\uAC00 \uC785\uB825\uB418\uC9C0 \uC54A\uC558\uC2B5\uB2C8\uB2E4. \n \uB77C\uC778 \uC815\uBCF4 : {0}

EXCEL_IMPORT_HOSTING_NAME_IS_NULL=\uB2E4\uC74C \uC5D1\uC140 \uB77C\uC778\uC5D0\uC11C ISP\uBA85\uC774 \uC785\uB825\uB418\uC9C0 \uC54A\uC558\uC2B5\uB2C8\uB2E4. \n \uB77C\uC778 \uC815\uBCF4 : {0}
EXCEL_IMPORT_HOSTING_CODE_IS_NULL=\uB2E4\uC74C \uC5D1\uC140 \uB77C\uC778\uC5D0\uC11C \uCF54\uB4DC\uAC00 \uC785\uB825\uB418\uC9C0 \uC54A\uC558\uC2B5\uB2C8\uB2E4. \n \uB77C\uC778 \uC815\uBCF4 : {0}

ISSUE_RESERVATION_VALUE_INVALID=\uC774\uC288 \uBC1C\uC0DD \uC608\uC57D\uC77C\uC774 \uC798\uBABB\uB418\uC5C8\uC2B5\uB2C8\uB2E4.
ISSUE_RESERVATION_NOT_EXIST=\uC774\uC288 \uBC1C\uC0DD \uC608\uC57D \uC815\uBCF4\uAC00 \uC874\uC7AC\uD558\uC9C0 \uC54A\uC2B5\uB2C8\uB2E4.
@@ -239,12 +245,18 @@
COMPANY_NAME_MAX_LENGTH_OUT = \uC5C5\uCCB4\uBA85\uC740 \uCD5C\uB300 300\uAE00\uC790\uAE4C\uC9C0 \uC785\uB825\uD560 \uC218 \uC788\uC2B5\uB2C8\uB2E4.
COMPANY_NAME_ALREADY_IN_USE = \uD574\uB2F9 \uC5C5\uCCB4\uBA85\uC740 \uC774\uBBF8 \uB4F1\uB85D\uB418\uC5B4 \uC788\uC2B5\uB2C8\uB2E4.

HOSTING_NOT_EXIST = \uD638\uC2A4\uD305\uC774 \uC874\uC7AC\uD558\uC9C0 \uC54A\uC2B5\uB2C8\uB2E4.
HOSTING_NO_TITLE = \uD638\uC2A4\uD305\uC774 \uC785\uB825\uB418\uC9C0 \uC54A\uC558\uC2B5\uB2C8\uB2E4.
HOSTING_CODE_NOT_ENTER = \uD638\uC2A4\uD305\uC758 \uCF54\uB4DC\uB294 \uD544\uC218 \uC785\uB825 \uAC12\uC785\uB2C8\uB2E4.
HOSTING_REMOVE_NOT_SELECT = \uC0AD\uC81C\uD560 \uD638\uC2A4\uD305\uC774 \uC120\uD0DD\uB418\uC9C0 \uC54A\uC558\uC2B5\uB2C8\uB2E4.
HOSTING_NOT_EXIST = \uD638\uC2A4\uD305\uC774 \uC874\uC7AC\uD558\uC9C0 \uC54A\uC2B5\uB2C8\uB2E4.
HOSTING_NAME_MAX_LENGTH_OUT = \uD638\uC2A4\uD305\uBA85\uC740 \uCD5C\uB300 300\uAE00\uC790\uAE4C\uC9C0 \uC785\uB825\uD560 \uC218 \uC788\uC2B5\uB2C8\uB2E4.
HOSTING_NAME_ALREADY_IN_USE=\uD574\uB2F9 \uD638\uC2A4\uD305\uBA85\uC740 \uC774\uBBF8 \uB4F1\uB85D\uB418\uC5B4 \uC788\uC2B5\uB2C8\uB2E4.
HOSTING_EXIST_COMPANY = \uC5C5\uCCB4\uC5D0 \uC124\uC815\uB418\uC5B4\uC788\uB294 \uD638\uC2A4\uD305\uC774 \uC874\uC7AC\uD569\uB2C8\uB2E4.

ISP_NOT_EXIST = ISP\uAC00 \uC874\uC7AC\uD558\uC9C0 \uC54A\uC2B5\uB2C8\uB2E4.
ISP_NO_TITLE = ISP\uBA85\uC774 \uC785\uB825\uB418\uC9C0 \uC54A\uC558\uC2B5\uB2C8\uB2E4.
ISP_CODE_NOT_ENTER = ISP\uC758 \uCF54\uB4DC\uB294 \uD544\uC218 \uC785\uB825 \uAC12\uC785\uB2C8\uB2E4.
ISP_REMOVE_NOT_SELECT = \uC0AD\uC81C\uD560 ISP\uAC00 \uC120\uD0DD\uB418\uC9C0 \uC54A\uC558\uC2B5\uB2C8\uB2E4.
ISP_NOT_EXIST = ISP\uAC00 \uC874\uC7AC\uD558\uC9C0 \uC54A\uC2B5\uB2C8\uB2E4.
ISP_NAME_MAX_LENGTH_OUT = ISP\uBA85\uC740 \uCD5C\uB300 300\uAE00\uC790\uAE4C\uC9C0 \uC785\uB825\uD560 \uC218 \uC788\uC2B5\uB2C8\uB2E4.
ISP_NAME_ALREADY_IN_USE=\uD574\uB2F9 ISP\uBA85\uC740 \uC774\uBBF8 \uB4F1\uB85D\uB418\uC5B4 \uC788\uC2B5\uB2C8\uB2E4.
ISP_EXIST_COMPANY = \uC5C5\uCCB4\uC5D0 \uC124\uC815\uB418\uC5B4\uC788\uB294 ISP\uAC00 \uC874\uC7AC\uD569\uB2C8\uB2E4.
ISP_REMOVE_NOT_SELECT = \uC0AD\uC81C\uD560 ISP\uAC00 \uC120\uD0DD\uB418\uC9C0 \uC54A\uC558\uC2B5\uB2C8\uB2E4.

 src/main/webapp/i18n/ko/global.json

@@ -1022,6 +1022,7 @@
        "ispName" : "ISP명",
        "manager" : "담당자",
        "tel" : "연락처",
        "url" : "url",
        "code" : "코드",
        "email" : "이메일",
        "memo" : "비고",
@@ -1032,17 +1033,20 @@
        "failedToDetailIspFieldModify" : "ISP 상세 정보 조회 실패",
        "pleaseEnterIspFieldName": "ISP 이름을 입력하세요.",
        "enterSpecialCharacters": "ISP 이름에는 특수 문자를 입력 할 수 없습니다.",
        "invalidCodeFormat": "코드명에는 특수 문자를 입력 할 수 없습니다."
        "invalidCodeFormat": "코드명에는 특수 문자를 입력 할 수 없습니다.",
        "registerExcelIspFields": "엑셀로 ISP 등록하기",
        "registerExcelUploadIspField": "엑셀 업로드 ISP 등록",
        "succeededIspFieldRegistration": "ISP 등록 성공"
    },
    "hostingField" : {
        "info": "호스팅정보",
        "select": "호스팅 선택",
        "add" : "호스팅생성",
        "name" : "호스팅이름",
        "manager" : "호스팅이름",
        "hostingName" : "호스팅명",
        "manager" : "담당자",
        "tel" : "연락처",
        "url" : "url",
        "code" : "코드",
        "email" : "이메일",
        "memo" : "비고",
@@ -1053,6 +1057,9 @@
        "failedToDetailHostingFieldModify" : "호스팅 상세 정보 조회 실패",
        "pleaseEnterHostingFieldName": "호스팅 이름을 입력하세요.",
        "enterSpecialCharacters": "호스팅 이름에는 특수 문자를 입력 할 수 없습니다.",
        "invalidCodeFormat": "코드명에는 특수 문자를 입력 할 수 없습니다."
        "invalidCodeFormat": "코드명에는 특수 문자를 입력 할 수 없습니다.",
        "registerExcelHostingFields": "엑셀로 호스팅 등록하기",
        "registerExcelUploadHostingField": "엑셀 업로드 호스팅 등록",
        "succeededHostingFieldRegistration": "호스팅 등록 성공"
    }
}

 src/main/webapp/scripts/app/hostingField/hostingField.js

@@ -33,7 +33,7 @@
                        loadController : ["$q", function ($q) {
                            var deferred = $q.defer();
                            require(["hostingFieldListController", 'jsTable', 'tableColumnGenerator', 'hostingFieldService', 'modalFormAutoScroll'
                                , 'hostingFieldAddController', 'hostingFieldModifyController'
                                , 'hostingFieldAddController', 'hostingFieldModifyController', 'hostingFieldImportExcelController'
                                , 'formSubmit', 'jsShortCut', 'inputRegex'], function () {
                                deferred.resolve();
                            });

 src/main/webapp/scripts/app/hostingField/hostingFieldImportExcel.controller.js

New file
@@ -0,0 +1,80 @@
/**
 * Created by wisestone on 2018-01-17.
 */
'use strict';

define([
        'app',
        'angular'
    ],
    function (app, angular) {
        app.controller('hostingFieldImportExcelController', ['$scope', '$rootScope', '$log', '$resourceProvider', '$uibModalInstance', '$injector', '$controller', '$tableProvider', 'HostingField', 'SweetAlert', '$filter', '$timeout',
            function ($scope, $rootScope, $log, $resourceProvider, $uibModalInstance, $injector, $controller, $tableProvider, HostingField, SweetAlert, $filter, $timeout) {

                $scope.fn = {
                    cancel : cancel,    //  팝업 창 닫기
                    formSubmit : formSubmit,    //  폼 전송
                    formCheck : formCheck,  //  폼 체크
                    onFileSelect : onFileSelect, //  파일 업로드
                };

                $scope.vm = {
                    form : {
                        file : "",   //  업로드하는 Excel File
                        fileName : ""
                    },
                    tab : "VIDEO"
                };

                //  파일 업로드
                function onFileSelect($files) {
                    $scope.vm.form.file = $files;

                    if ($files.length > 0) {
                        $scope.vm.form.fileName = $files[0].name;
                    }
                    else {
                        $scope.vm.form.fileName = "";
                    }
                }

                //  폼 체크
                function formCheck() {
                    if ($scope.vm.form.file.length < 1) {
                        return true;
                    }
                    return false;
                }

                //  폼 전송
                function formSubmit() {
                    $rootScope.spinner = true;

                    HostingField.importExcel({
                        method : "POST",
                        file : $scope.vm.form.file,
                        //      data 속성으로 별도의 데이터 전송
                        fileFormDataName : "file"
                    })
                    .then(function (result) {
                        if (result.data.message.status === "success") {
                            $timeout(function () {
                                SweetAlert.success($filter("translate")("hostingField.succeededHostingFieldRegistration"), result.data.message.message); // 등록 성공
                                $scope.fn.cancel();
                                $rootScope.$broadcast("getHostingFieldList");
                                $rootScope.spinner = false;
                            }, 1000);
                        }
                        else {
                            SweetAlert.error($filter("translate")("hostingField.failedToSelectHostingFieldFullList"), result.data.message.message); // 등록 실패
                            $rootScope.spinner = false;
                        }
                    });
                }

                function cancel() {
                    $rootScope.$broadcast("closeLayer");    //  팝업이 열리고 나서 js-multi, js-single 등에서 body 이벤트가 날아가는 현상 수정
                    $uibModalInstance.dismiss('cancel');
                }
            }]);
    });

 src/main/webapp/scripts/app/hostingField/hostingFieldList.controller.js

@@ -19,7 +19,8 @@
                    initSearch : initSearch, //검색 조건 초기화
                    add : add, //호스팅 생성
                    removes : removes, //호스팅 삭제
                    makeSearchConditions : makeSearchConditions //엑셀 다운로드
                    makeSearchConditions : makeSearchConditions, //엑셀 다운로드
                    importExcel : importExcel,  //  엑셀 import 기능 팝업 호출
                };

                //  변수
@@ -46,6 +47,11 @@

                //  호스팅 관리 리스트 불러오기
                $scope.$on("getPageList", function () {
                    $scope.fn.getPageList(0);
                });

                //  호스팅 목록 데이터 갱신
                $scope.$on("getHostingFieldList", function () {
                    $scope.fn.getPageList(0);
                });

@@ -271,6 +277,16 @@
                        });
                }

                //  엑셀 import 팝업 호출
                function importExcel() {
                    $uibModal.open({
                        templateUrl : 'views/hostingField/hostingFieldExcelImport.html',
                        size : "lg",
                        controller : 'hostingFieldImportExcelController',
                        backdrop : 'static'
                    });
                }

                function makeSearchConditions() {
                    var conditions = {
                        name: $scope.vm.search.name

 src/main/webapp/scripts/app/ispField/ispField.js

@@ -33,7 +33,7 @@
                        loadController : ["$q", function ($q) {
                            var deferred = $q.defer();
                            require(["ispFieldListController", "ispFieldAddController", 'jsTable', 'tableColumnGenerator', 'ispFieldService', 'modalFormAutoScroll'
                                , 'ispFieldAddController', 'ispFieldModifyController'
                                , 'ispFieldAddController', 'ispFieldModifyController', 'ispFieldImportExcelController'
                                , 'formSubmit', 'jsShortCut', 'inputRegex'], function () {
                                deferred.resolve();
                            });

 src/main/webapp/scripts/app/ispField/ispFieldImportExcel.controller.js

New file
@@ -0,0 +1,80 @@
/**
 * Created by wisestone on 2018-01-17.
 */
'use strict';

define([
        'app',
        'angular'
    ],
    function (app, angular) {
        app.controller('ispFieldImportExcelController', ['$scope', '$rootScope', '$log', '$resourceProvider', '$uibModalInstance', '$injector', '$controller', '$tableProvider', 'IspField', 'SweetAlert', '$filter', '$timeout',
            function ($scope, $rootScope, $log, $resourceProvider, $uibModalInstance, $injector, $controller, $tableProvider, IspField, SweetAlert, $filter, $timeout) {

                $scope.fn = {
                    cancel : cancel,    //  팝업 창 닫기
                    formSubmit : formSubmit,    //  폼 전송
                    formCheck : formCheck,  //  폼 체크
                    onFileSelect : onFileSelect, //  파일 업로드
                };

                $scope.vm = {
                    form : {
                        file : "",   //  업로드하는 Excel File
                        fileName : ""
                    },
                    tab : "VIDEO"
                };

                //  파일 업로드
                function onFileSelect($files) {
                    $scope.vm.form.file = $files;

                    if ($files.length > 0) {
                        $scope.vm.form.fileName = $files[0].name;
                    }
                    else {
                        $scope.vm.form.fileName = "";
                    }
                }

                //  폼 체크
                function formCheck() {
                    if ($scope.vm.form.file.length < 1) {
                        return true;
                    }
                    return false;
                }

                //  폼 전송
                function formSubmit() {
                    $rootScope.spinner = true;

                    IspField.importExcel({
                        method : "POST",
                        file : $scope.vm.form.file,
                        //      data 속성으로 별도의 데이터 전송
                        fileFormDataName : "file"
                    })
                    .then(function (result) {
                        if (result.data.message.status === "success") {
                            $timeout(function () {
                                SweetAlert.success($filter("translate")("ispField.succeededIspFieldRegistration"), result.data.message.message); // 등록 성공
                                $scope.fn.cancel();
                                $rootScope.$broadcast("getIspFieldList");
                                $rootScope.spinner = false;
                            }, 1000);
                        }
                        else {
                            SweetAlert.error($filter("translate")("ispField.failedIspFieldRegistration"), result.data.message.message); // 등록 실패
                            $rootScope.spinner = false;
                        }
                    });
                }

                function cancel() {
                    $rootScope.$broadcast("closeLayer");    //  팝업이 열리고 나서 js-multi, js-single 등에서 body 이벤트가 날아가는 현상 수정
                    $uibModalInstance.dismiss('cancel');
                }
            }]);
    });

 src/main/webapp/scripts/app/ispField/ispFieldList.controller.js

@@ -19,7 +19,8 @@
                    initSearch : initSearch, //검색 조건 초기화
                    add : add, //ISP 생성
                    removes : removes, //ISP 삭제
                    makeSearchConditions : makeSearchConditions //엑셀 다운로드
                    makeSearchConditions : makeSearchConditions, //엑셀 다운로드
                    importExcel : importExcel,  //  엑셀 import 기능 팝업 호출
                };

                //  변수
@@ -46,6 +47,11 @@

                //  iSP 관리 리스트 불러오기
                $scope.$on("getPageList", function () {
                    $scope.fn.getPageList(0);
                });

                //  iSP 목록 데이터 갱신
                $scope.$on("getIspFieldList", function () {
                    $scope.fn.getPageList(0);
                });

@@ -271,6 +277,16 @@
                        });
                }

                //  엑셀 import 팝업 호출
                function importExcel() {
                    $uibModal.open({
                        templateUrl : 'views/ispField/ispFieldExcelImport.html',
                        size : "lg",
                        controller : 'ispFieldImportExcelController',
                        backdrop : 'static'
                    });
                }

                function makeSearchConditions() {
                    var conditions = {
                        name: $scope.vm.search.name

 src/main/webapp/scripts/components/HostingField/hostingField.service.js

@@ -6,7 +6,7 @@
define([
    'app'
], function (app) {
    app.factory("HostingField", ['$http', '$log', function ($http, $log) {
    app.factory("HostingField", ['$http', '$log', '$upload', function ($http, $log, $upload) {
        return {
            find : function (conditions) {
                return $http.post("hostingField/find", conditions).then(function (response) {
@@ -38,13 +38,20 @@
                    return response;
                });
            },
            importExcel : function (conditions) {
                conditions.url = "hostingField/importExcel";
                return $upload.upload(conditions).then(function (response) {
                    $log.debug("hostingField Import 결과 : ", response);
                    return response;
                });
            },
            // 업체/ISP/호스팅 조회
            getPartnerList : function (conditions) {
                return $http.post("company/getPartnerList", conditions).then(function (response) {
                    $log.debug("업체/isp/호스팅 목록 조회 결과 : ", response);
                    return response;
                });
            }
            },

        }
    }

 src/main/webapp/scripts/components/ispField/ispField.service.js

@@ -6,7 +6,7 @@
define([
    'app'
], function (app) {
    app.factory("IspField", ['$http', '$log', function ($http, $log) {
    app.factory("IspField", ['$http', '$log', '$upload', function ($http, $log, $upload) {
        return {
            find : function (conditions) {
                return $http.post("ispField/find", conditions).then(function (response) {
@@ -37,6 +37,13 @@
                    $log.debug("IspField 삭제 결과 : ", response);
                    return response;
                });
            },
            importExcel : function (conditions) {
                conditions.url = "ispField/importExcel";
                return $upload.upload(conditions).then(function (response) {
                    $log.debug("ispField Import 결과 : ", response);
                    return response;
                });
            }
        }
    }

 src/main/webapp/scripts/main.js

@@ -364,6 +364,7 @@
        'ispFieldListController' : 'app/ispField/ispFieldList.controller', // ISP 목록 컨트롤러
        'ispFieldAddController' : 'app/ispField/ispFieldAdd.controller', // ISP 생성 컨트롤러
        'ispFieldModifyController' : 'app/ispField/ispFieldModify.controller', // ISP 수정 컨트롤러
        'ispFieldImportExcelController' : 'app/ispField/ispFieldImportExcel.controller', // ISP 엑셀 임포트 컨트롤러

        /* 호스팅 */
        'hostingFieldRoute' : 'app/hostingField/hostingField',   // 호스팅 route 정보
@@ -371,6 +372,7 @@
        'hostingFieldListController' : 'app/hostingField/hostingFieldList.controller', // 호스팅 목록 컨트롤러
        'hostingFieldAddController' : 'app/hostingField/hostingFieldAdd.controller', // 호스팅 생성 컨트롤러
        'hostingFieldModifyController' : 'app/hostingField/hostingFieldModify.controller', // 호스팅 수정 컨트롤러
        'hostingFieldImportExcelController' : 'app/hostingField/hostingFieldImportExcel.controller', // 호스팅 임포트 컨트롤러

        /* 이메일 템플릿*/
        'emailTemplateService' : 'components/emailTemplate/emailTemplate.service',  // 호스팅 관련된 통신 담당

 src/main/webapp/views/hostingField/hostingFieldExcelImport.html

New file
@@ -0,0 +1,83 @@
<div class="formModal">
    <div class="modal-header faded smaller">
        <div class="modal-title">
            <strong translate="hostingField.registerExcelHostingFields">엑셀로 호스팅 등록하기</strong>
        </div>
        <button aria-label="Close" class="close" type="button" ng-click="fn.cancel()">
            <span aria-hidden="true"> &times;</span>
        </button>
    </div>

    <div class="modal-body">
        <form role="form" name="hostingFieldExcelImportForm">
            <!--<div class="row">
                <div class="col-lg-5">-->

                    <button type="button"
                            class="btn btn-success"
                            form-submit="hostingFieldExcelImportForm" make-search-conditions="fn.makeSearchConditions()"
                            style="width:100%;"><i class="fa fa-file-excel-o"></i>&nbsp;<span
                            translate="common.downloadExcel">엑셀 다운로드</span>
                    </button>

                    <div class="form-group pdt10">
                        <div class="filebox input-group">
                            <input class="upload-name form-control"
                                   placeholder="{{'users.pleaseSelectFile' | translate}}"
                                   tabindex="-1"
                                   disabled="disabled"
                                   ng-model="vm.form.fileName">
                            <label for="uploadFileField"><span translate="common.uploadExcel">엑셀 업로드</span></label>
                            <input id="uploadFileField"
                                   class="form-control"
                                   type="file"
                                   ng-file-select="fn.onFileSelect($files)"/>
                        </div>
                    </div>

                    <button type="button" class="btn btn-md btn-primary bold"
                            ng-show="$root.isDefined(vm.form.file)"
                            style="width:100%;"
                            ng-disabled="fn.formCheck()"
                            ng-click="fn.formSubmit()"><span
                            translate="hostingField.registerExcelUploadHostingField">엑셀 업로드 호스팅 등록</span>
                    </button>
                <!--</div>-->

                <!--<div class="col-lg-7 bdl1">
                    <div class="os-tabs-w">
                        <div class="os-tabs-controls">
                            <ul class="nav nav-tabs smaller">
                                <li class="nav-item cursor">
                                    <a class="nav-link" ng-class="{ 'active' : vm.tab == 'VIDEO' }"
                                       ng-click="vm.tab = 'VIDEO'"><span
                                            translate="issue.videoExample">동영상 예제</span></a>
                                </li>
                            </ul>
                        </div>

                        <div class="tab-content">
                            <div ng-show="vm.tab == 'VIDEO'">
                                <video style="width:100%;" width="100%" height="300px"
                                       src="/excelTemplate/excelExample_new.mp4" controls autoplay loop muted
                                       preload="auto"></video>
                            </div>
                        </div>
                    </div>
                </div>-->
            <!--</div>-->
        </form>
        <!--    호스팅 Excel Import 템플릿 다운로드 폼  -->
        <form class="form-inline justify-content-sm-end"
              method="post"
              action="/hostingField/downloadExcelTemplate"
              name="hostingFieldExcelImportForm">
            <input type="hidden" name="conditions">
        </form>
    </div>

    <div class="modal-footer buttons-on-right">
        <button type="button" class="btn btn-md btn-grey" ng-click="fn.cancel()"><span
                translate="common.close">닫기</span></button>
    </div>
</div>

 src/main/webapp/views/hostingField/hostingFieldList.html

@@ -76,6 +76,7 @@
                                    </button>
                                    <div aria-labelledby="dropdownMenuButton2" class="dropdown-menu left-menu"
                                         x-placement="bottom-start">
                                        <a class="dropdown-item cursor" ng-click="fn.importExcel()"> <span translate="common.importExcel">Excel Import</span></a>
                                        <a class="dropdown-item cursor" form-submit="hostingFieldListForm" make-search-conditions="fn.makeSearchConditions()"><span translate="common.allDownloadExcel">엑셀 다운로드</span></a>
                                        <a class="dropdown-item cursor" ng-click="fn.removes()"> <span translate="common.selectDelete">삭제</span></a>
                                    </div>

 src/main/webapp/views/ispField/ispFieldExcelImport.html

New file
@@ -0,0 +1,83 @@
<div class="formModal">
    <div class="modal-header faded smaller">
        <div class="modal-title">
            <strong translate="ispField.registerExcelIspFields">엑셀로 업체 등록하기</strong>
        </div>
        <button aria-label="Close" class="close" type="button" ng-click="fn.cancel()">
            <span aria-hidden="true"> &times;</span>
        </button>
    </div>

    <div class="modal-body">
        <form role="form" name="ispFieldExcelImportForm">
            <!--<div class="row">
                <div class="col-lg-5">-->

                    <button type="button"
                            class="btn btn-success"
                            form-submit="ispFieldExcelImportForm" make-search-conditions="fn.makeSearchConditions()"
                            style="width:100%;"><i class="fa fa-file-excel-o"></i>&nbsp;<span
                            translate="common.downloadExcel">엑셀 다운로드</span>
                    </button>

                    <div class="form-group pdt10">
                        <div class="filebox input-group">
                            <input class="upload-name form-control"
                                   placeholder="{{'users.pleaseSelectFile' | translate}}"
                                   tabindex="-1"
                                   disabled="disabled"
                                   ng-model="vm.form.fileName">
                            <label for="uploadFileField"><span translate="common.uploadExcel">엑셀 업로드</span></label>
                            <input id="uploadFileField"
                                   class="form-control"
                                   type="file"
                                   ng-file-select="fn.onFileSelect($files)"/>
                        </div>
                    </div>

                    <button type="button" class="btn btn-md btn-primary bold"
                            ng-show="$root.isDefined(vm.form.file)"
                            style="width:100%;"
                            ng-disabled="fn.formCheck()"
                            ng-click="fn.formSubmit()"><span
                            translate="ispField.registerExcelUploadIspField">엑셀 업로드 ISP 등록</span>
                    </button>
                <!--</div>-->

                <!--<div class="col-lg-7 bdl1">
                    <div class="os-tabs-w">
                        <div class="os-tabs-controls">
                            <ul class="nav nav-tabs smaller">
                                <li class="nav-item cursor">
                                    <a class="nav-link" ng-class="{ 'active' : vm.tab == 'VIDEO' }"
                                       ng-click="vm.tab = 'VIDEO'"><span
                                            translate="issue.videoExample">동영상 예제</span></a>
                                </li>
                            </ul>
                        </div>

                        <div class="tab-content">
                            <div ng-show="vm.tab == 'VIDEO'">
                                <video style="width:100%;" width="100%" height="300px"
                                       src="/excelTemplate/excelExample_new.mp4" controls autoplay loop muted
                                       preload="auto"></video>
                            </div>
                        </div>
                    </div>
                </div>-->
            <!--</div>-->
        </form>
        <!--    업체 Excel Import 템플릿 다운로드 폼  -->
        <form class="form-inline justify-content-sm-end"
              method="post"
              action="/ispField/downloadExcelTemplate"
              name="ispFieldExcelImportForm">
            <input type="hidden" name="conditions">
        </form>
    </div>

    <div class="modal-footer buttons-on-right">
        <button type="button" class="btn btn-md btn-grey" ng-click="fn.cancel()"><span
                translate="common.close">닫기</span></button>
    </div>
</div>

 src/main/webapp/views/ispField/ispFieldList.html

@@ -76,6 +76,7 @@
                                    </button>
                                    <div aria-labelledby="dropdownMenuButton2" class="dropdown-menu left-menu"
                                         x-placement="bottom-start">
                                        <a class="dropdown-item cursor" ng-click="fn.importExcel()"> <span translate="common.importExcel">Excel Import</span></a>
                                        <a class="dropdown-item cursor" form-submit="ispFieldListForm" make-search-conditions="fn.makeSearchConditions()"><span translate="common.allDownloadExcel">엑셀 다운로드</span></a>
                                        <a class="dropdown-item cursor" ng-click="fn.removes()"> <span translate="common.selectDelete">삭제</span></a>
                                    </div>