본문 바로가기
자바(Java)

Java - 엑셀 업로드 후 DB 저장

hoky 2023. 8. 25. 10:01
반응형

1. 작업 환경

- Java 버전: 8.0
- spring.maven.artifact.version: 4.1.2.RELEASE
- egovframework.rte.version: 3.5.0
- apachetiles.version: 3.0.5 
- Tomcat: 8.0
- Tool: Eclipse IDE for Enterprise Java and Web Developers - 2023-06
- DB: oracle
- Used File Strurcture: VO, Dao, Service, ServiceImpl, Controller, JSP, mybatis

- used API: apache.poi, springframework.web.multipart.MultipartFile

2. 작업 내용

1) 다운로드 받은 엑셀 파일(공양식)을 업로드한다.
2) 데이터 유효성 검사 후, 적합한 경우 DB에 데이터를 저장한다.

*업로드 버튼을 클릭하면, 파일 선택이 가능한 팝업창이 생성되고, 아래 상황별로 로직이 처리된다.

1)엑셀 파일이 아닌경우 엑셀 파일만 업로드 가능하다는 오류 알림창 생성, 2)양식에 맞지 않은 엑셀 파일 업로드할 경우 오류 알림창 생성, 3)양식에 맞는 엑셀 파일 업로드할 경우 데이터 정상적으로 저장, 4)파일 선택을 하지 않을 경우 선택된 파일 없음 알림창 생성

3. Controller 주요 코드

// 엑셀 업로드 [23.08.22, th]
 @RequestMapping(value="uploadMethod", method = RequestMethod.POST)
 @ResponseBody
 public int uploadMethod(MultipartFile file, MultipartHttpServletRequest request) throws Exception {
        // System.out.println("====================uploadMethod"); // --- 확인 완료

    // 0. 결과 포인트 변수 생성 (성공: 0, 파일 empty: -1, 데이터 유효성 문제: -2, 업로드 실패: -3)
    int resultPoint = 0;

    // 1. 파일 존재 여부 확인
    if( file.isEmpty() ) {
        resultPoint--;
        return resultPoint;
    }

    // 2. 파일 저장 장소 생성 및 파일명 가공 작업 (저장 필요할 수도 있으니 우선 전달하자)
    String realPath = servletContext.getRealPath("/");
    String fileStorePath = realPath + "WEB-INF/jsp/";
    String fileName = "excel_" + System.currentTimeMillis() + ".xlsx";

    // 3. 서비스 메소드에 파일 전달[업로드] (반환값 - 성공: 0, 데이터 유효성 문제: -2, 업로드 실패: -3)
    resultPoint = Service.uploadMethod(file, fileStorePath, fileName);
        // System.out.println("resultPoint: " + resultPoint); // --- 확인 완료

    // 4. 결과 반환 (성공: 0, 파일 empty: -1, 데이터 유효성 문제: -2, 업로드 실패: -3)
    return resultPoint;
 }

4. Service 코드

1) 파일 저장 없이 업로드 된 자료를 바로 읽어 사용

// 엑셀 데이터 업로드(DB저장) 메소드 [23.08.22, th]
@Override
public int uploadMethod(MultipartFile excelFile, String fileStorePath, String fileName) throws Exception {

    // 0. 검사 통과한 데이터를 저장할 리스트
    List<xxxVO> validDataList = new ArrayList<>();

    // try-with-resources 적용
    // 기능: 엑셀 파일을 InputStream으로 읽은 후 XSSFWorkbook 객체인 Workbook에 저장하여 엑셀파일 내용 읽기, 옵션: closing 자동
    // 특이점: 업로드된 파일 원본을 저장할 필요가 없기 때문에 들어온 자료를 바로 읽어 로직 처리함.
    try( InputStream inputStream = excelFile.getInputStream();
        Workbook workbook = new XSSFWorkbook(inputStream) ) {

        // 2. 엑셀 시트(첫 번째 시트)를 가져오기
        Sheet sheet = workbook.getSheetAt(0);

        // 3. 액셀 행 순회하며, 데이터 추출 작업
        for (Row row : sheet) {

            // 3-1. 첫 행(열 제목) DB 저장 대상에서 제외
            if (row.getRowNum() == 0) { 
                continue;
            }

            // 3-2. A열 값 추출 (A 열에 해당하는 열의 인덱스를 지정)
            Cell cellA = row.getCell(0); // A열에 해당하는 셀
            String columnS = getStringValue(row.getCell(18));

            // 3-2. A/S열 값이 null인 경우 순회 중단
            if ( (columnA == null || columnA.isEmpty()) && ( columnS == null || columnS.isEmpty() )) {
                break;
            }

            // 3-3. A열 값 외 데이터 추출(B-R열에 해당하는 값 추출..)
            String columnB = getStringValue(row.getCell(1));
            String columnC = getStringValue(row.getCell(2));
            String columnD = getStringValue(row.getCell(3));
            String columnE = getStringValue(row.getCell(4));
            String columnF = getStringValue(row.getCell(5));
            String columnG = getStringValue(row.getCell(6));
            String columnH = getStringValue(row.getCell(7));
            String columnI = getStringValue(row.getCell(8));
            String columnJ = getStringValue(row.getCell(9));
            String columnK = getStringValue(row.getCell(10));
            String columnL = getStringValue(row.getCell(11));
            String columnM = getStringValue(row.getCell(12));
            String columnN = getStringValue(row.getCell(13));
            String columnO = getStringValue(row.getCell(14));
            String columnP = getStringValue(row.getCell(15));
            String columnQ = getStringValue(row.getCell(16));
            String columnR = getStringValue(row.getCell(17));
            String columnS = getStringValue(row.getCell(18));

                // System.out.println(columnC); // --- 45160으로 확인됨 (엑셀 입력값: 2023-08-22)

            // 3-4. 날짜 데이터 가공작업 (columnC:실시일자_시작, columnD:실시일자_종료)
            SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
            Date dateC = DateUtil.getJavaDate(Double.parseDouble(columnC));
            Date dateD = DateUtil.getJavaDate(Double.parseDouble(columnD));

            // 3-5. 데이터 유효성 검사              
            if( !isValidData(columnA, columnB, dateC, dateD, columnE, 
                    columnF, columnG, columnH, columnI, columnJ, columnK, 
                    columnL, columnM, columnN, columnO, columnP, columnQ, columnR, columnS) ) {
                return -2; // 데이터 유효성 검사 부적합인 경우 -2 반환 
            }

            // 3-6. DB에 저장하기 위한 데이터 가공 작업               
            xxxVo sampleVo = new xxxVo();
                sampleVo.setSubject(columnA);
                sampleVo.setPurpose(columnB);
                sampleVo.setBeginDt(dateFormat.format(dateC));
                sampleVo.setEndDt(dateFormat.format(dateD));
                sampleVo.setInsptTime(columnE);
                sampleVo.setInsptObj(columnF);
                sampleVo.setInexIe(columnG);
                sampleVo.setTranYn(columnH);
                sampleVo.setInsptPlace(columnI);
                sampleVo.setInsptUser(columnJ);
                sampleVo.setInstNm(columnK);
                sampleVo.setInsptTarget(columnL);
                sampleVo.setfStore(columnM);
                sampleVo.setChicor(columnN);
                sampleVo.setPartnerComp(columnO);
                sampleVo.setEtc(columnP);
                sampleVo.setContent(columnQ);
                sampleVo.setAplp(columnR);
                sampleVo.setCreateId(columnS);
                // System.out.println(sampleVo.getBeginDt()); //--- "yyyy-MM-dd" 타입 확인 완료
            validDataList.add(sampleVo);
        }

        // 4. DB에 데이터 저장
        for (sampleVo validData : validDataList) {
            EgovMap egovMap = Common.voToEgovMap(validData);
            xxxDAO.Save(egovMap);
        }

        // 6. 로직 이상 없이 수행 시, 0 반환 
        return 0;

    } catch (IOException e) { // --- 예외 처리 (업로드 실패를 의미하며, 실패 시 -1 반환)
        e.printStackTrace();
        return -3;
    }
}

// 엑셀의 셀 타입 String으로 설정 [23.08.22, th]
private String getStringValue(Cell cell) {
    if (cell == null) {
        return "";
    }
    cell.setCellType(CellType.STRING); // 셀의 데이터 타입을 문자열로 설정
    return cell.getStringCellValue();
}

// 엑셀 데이터 전체 유효성 검사 메소드 [23.08.23, th]
private boolean isValidData(String columnA, String columnB, Date columnC, Date columnD, String columnE,
        String columnF, String columnG, String columnH, String columnI, String columnJ,
        String columnK, String columnL, String columnM, String columnN, String columnO,
        String columnP, String columnQ, String columnR, String columnS) {

    try {
        // 1. Null 미허용 데이터 유효성 검사 (내용: Null 여부, DB설정 용량 적부, 데이터 패턴 검사)
        if ( columnA.isEmpty() || columnA.length() > 50 
            || columnE.isEmpty() || columnE.length() > 20
            || columnF.isEmpty() || columnF.length() > 300
            || columnK.isEmpty() || columnK.length() > 20 
            || columnQ.isEmpty() || columnQ.length() > 1300
            || columnB.length() != 6 || !columnB.contains("P09") 
            || columnI.length() != 6 || !columnI.contains("P08")
            || columnL.length() != 6 || !columnL.contains("P05") 
            || columnG.length() != 1 || (!columnG.equals("I") && !columnG.equals("E"))
            || columnH.length() != 1 || (!columnH.equals("Y") && !columnH.equals("N"))) {
            System.out.println("Mg09ServiceImpl isValidData type1(Required Input Data): data validation error");
            return false;
        }

        // 2. Null 허용 데이터 유효성 검사
        // 내용: Column J, M, N, O, P, R이 Null이 아니면서 글자수가 DB 설정 용량 초과하는 경우 false (null 허용)
        if ( (!columnJ.isEmpty() && columnJ.length() > 600)
            || (!columnM.isEmpty() && columnM.length() > 10)
            || (!columnN.isEmpty() && columnN.length() > 10)
            || (!columnO.isEmpty() && columnO.length() > 10)
            || (!columnP.isEmpty() && columnP.length() > 10)
            || (!columnR.isEmpty() && columnR.length() > 1300)) {
            System.out.println("Mg09ServiceImpl isValidData type2(Capacity Exceeded): data validation error");
            return false;
        }

        // 3. 날짜 데이터 유효성 검사 (내용: columnC, D 타입 검사)
        if( columnC == null || !isValidDate(columnC) 
            || columnD == null || !isValidDate(columnD) ) {
            System.out.println("Mg09ServiceImpl isValidData type3(Date Type): data validation error");
            return false;
        }

        // 4. 보고자 ID 유효성 검사 (내용: Null 여부, DB에 등록된 ID인지 확인)
        if( columnS.length() != 7 || mg09DAO.selectUserId(columnS) != 1) { 
            System.out.println("Mg09ServiceImpl isValidData type4(Unregistered User): data validation error");
            return false; 
        }
        // 5. 1~4번 모두 적합일 경우
        return true;

    } catch (Exception e) { // --- 예외 처리
        System.out.println("Mg09ServiceImpl isValidData() Method: Exception");
        return false;
    }
}
    

// 유효한 날짜 형식인지 확인하는 메소드 (엑셀 날짜 형식) [23.08.23, th]
private boolean isValidExcelDate(Date date) {
    try {
        // 1. Date를 LocalDate로 변환
        LocalDate localDate = date.toInstant().atZone(ZoneId.systemDefault()).toLocalDate();

        // 2. "2000-01-01"부터 "2999-12-31" 사이에 있는지 확인
        LocalDate minDate = LocalDate.of(2000, 1, 1);
        LocalDate maxDate = LocalDate.of(2999, 12, 31);

        // 3. 유효한 날짜인지 확인 후 결과 반환
        return !localDate.isBefore(minDate) && !localDate.isAfter(maxDate);
    } catch (Exception e) {
        System.out.println("Service uploadMethod isValidExcelDate() Method: Exception");
        return false;
    }
}


2) 파일 저장 후 삭제 처리 진행

// 엑셀 데이터 업로드(DB저장) 메소드 [23.08.22, th]
@Override
public int uploadMethod(MultipartFile excelFile, String fileStorePath, String fileName) throws Exception {	

    // 0. 검사 통과한 데이터를 저장할 리스트
    List<Mg01VO> validDataList = new ArrayList<>();

    try {
        // 1. 경로에 파일 저장 (경로 파일 저장/삭제 없어도 코드 실행됨, 보관 필요 시 저장만 on)
        Path path = Paths.get(fileStorePath + fileName); 
        File outputFile = path.toFile();
        excelFile.transferTo(outputFile);

        // try-with-resources 적용
        try( InputStream inputStream = new FileInputStream(outputFile);
            Workbook workbook = new XSSFWorkbook(inputStream) ) {

            // 2. 엑셀 시트(첫 번째 시트)를 가져오기
            Sheet sheet = workbook.getSheetAt(0);

            // 3. 액셀 행 순회하며, 데이터 추출 작업
            for (Row row : sheet) {
                // 3-1. 첫 행(열 제목) DB 저장 대상에서 제외
                if (row.getRowNum() == 0) { 
                    continue;
                }
                // 3-2. A열 값 추출 (A 열에 해당하는 열의 인덱스를 지정)
                Cell cellA = row.getCell(0); // A열에 해당하는 셀
                String columnA = getStringValue(cellA);
                String columnS = getStringValue(row.getCell(18));
                
                // 3-2. A/S열 값이 null인 경우 순회 중단
                if ( (columnA == null || columnA.isEmpty()) && ( columnS == null || columnS.isEmpty() )) {
                    break;
                }
                
                // 3-3. A열 값 외 데이터 추출(B-R열에 해당하는 값 추출)
                String columnB = getStringValue(row.getCell(1));
                String columnC = getStringValue(row.getCell(2));
                String columnD = getStringValue(row.getCell(3));
                String columnE = getStringValue(row.getCell(4));
                String columnF = getStringValue(row.getCell(5));
                String columnG = getStringValue(row.getCell(6));
                String columnH = getStringValue(row.getCell(7));
                String columnI = getStringValue(row.getCell(8));
                String columnJ = getStringValue(row.getCell(9));
                String columnK = getStringValue(row.getCell(10));
                String columnL = getStringValue(row.getCell(11));
                String columnM = getStringValue(row.getCell(12));
                String columnN = getStringValue(row.getCell(13));
                String columnO = getStringValue(row.getCell(14));
                String columnP = getStringValue(row.getCell(15));
                String columnQ = getStringValue(row.getCell(16));
                String columnR = getStringValue(row.getCell(17));
                String columnS = getStringValue(row.getCell(18));

                    // System.out.println(columnC); // --- 45160으로 확인됨 (엑셀 입력값: 2023-08-22)

                // 3-4. 날짜 데이터 가공작업 (columnC:실시일자_시작, columnD:실시일자_종료)
                SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
                Date dateC = DateUtil.getJavaDate(Double.parseDouble(columnC));
                Date dateD = DateUtil.getJavaDate(Double.parseDouble(columnD));

                // 3-4. 데이터 유효성 검사              
                if( !isValidData(columnA, columnB, dateC , dateD , columnE, 
                        columnF, columnG, columnH, columnI, columnJ, columnK, 
                        columnL, columnM, columnN, columnO, columnP, columnQ, columnR, columnS) ) {
                    return -2; // 데이터 유효성 검사 부적합인 경우 -2 반환 
                }

                // 3-5. DB에 저장하기 위한 데이터 가공 작업               
                xxxVo sampleVo = new xxxVo();
                    sampleVo.setSubject(columnA);
                    sampleVo.setPurpose(columnB);
                    sampleVo.setBeginDt(dateFormat.format(dateC));
                    sampleVo.setEndDt(dateFormat.format(dateD));
                    sampleVo.setInsptTime(columnE);
                    sampleVo.setInsptObj(columnF);
                    sampleVo.setInexIe(columnG);
                    sampleVo.setTranYn(columnH);
                    sampleVo.setInsptPlace(columnI);
                    sampleVo.setInsptUser(columnJ);
                    sampleVo.setInstNm(columnK);
                    sampleVo.setInsptTarget(columnL);
                    sampleVo.setfStore(columnM);
                    sampleVo.setChicor(columnN);
                    sampleVo.setPartnerComp(columnO);
                    sampleVo.setEtc(columnP);
                    sampleVo.setContent(columnQ);
                    sampleVo.setAplp(columnR);
                    sampleVo.setCreateId(columnS);
                    // System.out.println(sampleVo.getBeginDt()); //--- "yyyy-MM-dd" 타입 확인 완료
                validDataList.add(sampleVo);
            }

            // 4. DB에 데이터 저장
            for (sampleVo validData : validDataList) {
                EgovMap egovMap = Common.voToEgovMap(validData);
                xxxDAO.Save(egovMap);
            }
        }

        // 5. 파일을 업로드한 후 삭제
        if (excelFile.getSize() > 0 && outputFile.exists()) { 
            if (outputFile.delete()) {
                System.out.println("Service uploadMethod: Uploaded file deleted.");
            } else { 
             System.out.println("Service uploadMethod: Failed to delete uploaded file.");
             return -1; // 삭제 실패 시, -1 반환 } }
            }
        }
      return 0; // 성공 시 0 반환          
    } catch (IOException e) {
        e.printStackTrace();
        return -1; // 업로드 실패 시 -1 반환
    }
}

// 엑셀의 셀 타입 String으로 설정 [23.08.22, th]
private String getStringValue(Cell cell) {
    if (cell == null) {
        return "";
    }
    cell.setCellType(CellType.STRING); // 셀의 데이터 타입을 문자열로 설정
    return cell.getStringCellValue();
}

// 엑셀 데이터 전체 유효성 검사 메소드 [23.08.23, th]
private boolean isValidData(String columnA, String columnB, Date columnC, Date columnD, String columnE,
        String columnF, String columnG, String columnH, String columnI, String columnJ,
        String columnK, String columnL, String columnM, String columnN, String columnO,
        String columnP, String columnQ, String columnR, String columnS) {

    try {
        // 1. Null 미허용 데이터 유효성 검사 (내용: Null 여부, DB설정 용량 적부, 데이터 패턴 검사)
        if ( columnA.isEmpty() || columnA.length() > 50 
            || columnE.isEmpty() || columnE.length() > 20
            || columnF.isEmpty() || columnF.length() > 300
            || columnK.isEmpty() || columnK.length() > 20 
            || columnQ.isEmpty() || columnQ.length() > 1300
            || columnB.length() != 6 || !columnB.contains("P09") 
            || columnI.length() != 6 || !columnI.contains("P08")
            || columnL.length() != 6 || !columnL.contains("P05") 
            || columnG.length() != 1 || (!columnG.equals("I") && !columnG.equals("E"))
            || columnH.length() != 1 || (!columnH.equals("Y") && !columnH.equals("N"))) {
            System.out.println("Mg09ServiceImpl isValidData type1(Required Input Data): data validation error");
            return false;
        }

        // 2. Null 허용 데이터 유효성 검사
        // 내용: Column J, M, N, O, P, R이 Null이 아니면서 글자수가 DB 설정 용량 초과하는 경우 false (null 허용)
        if ( (!columnJ.isEmpty() && columnJ.length() > 600)
            || (!columnM.isEmpty() && columnM.length() > 10)
            || (!columnN.isEmpty() && columnN.length() > 10)
            || (!columnO.isEmpty() && columnO.length() > 10)
            || (!columnP.isEmpty() && columnP.length() > 10)
            || (!columnR.isEmpty() && columnR.length() > 1300)) {
            System.out.println("Mg09ServiceImpl isValidData type2(Capacity Exceeded): data validation error");
            return false;
        }

        // 3. 날짜 데이터 유효성 검사 (내용: columnC, D 타입 검사)
        if( columnC == null || !isValidDate(columnC) 
            || columnD == null || !isValidDate(columnD) ) {
            System.out.println("Mg09ServiceImpl isValidData type3(Date Type): data validation error");
            return false;
        }

        // 4. 보고자 ID 유효성 검사 (내용: Null 여부, DB에 등록된 ID인지 확인)
        if( columnS.length() != 7 || mg09DAO.selectUserId(columnS) != 1) { 
            System.out.println("Mg09ServiceImpl isValidData type4(Unregistered User): data validation error");
            return false; 
        }
        // 5. 1~4번 모두 적합일 경우
        return true;

    } catch (Exception e) { // --- 예외 처리
        System.out.println("Mg09ServiceImpl isValidData() Method: Exception");
        return false;
    }
}
    

// 유효한 날짜 형식인지 확인하는 메소드 (엑셀 날짜 형식) [23.08.23, th]
private boolean isValidExcelDate(Date date) {
    try {
        // 1. Date를 LocalDate로 변환
        LocalDate localDate = date.toInstant().atZone(ZoneId.systemDefault()).toLocalDate();

        // 2. "2000-01-01"부터 "2999-12-31" 사이에 있는지 확인
        LocalDate minDate = LocalDate.of(2000, 1, 1);
        LocalDate maxDate = LocalDate.of(2999, 12, 31);

        // 3. 유효한 날짜인지 확인 후 결과 반환
        return !localDate.isBefore(minDate) && !localDate.isAfter(maxDate);
    } catch (Exception e) {
        System.out.println("Service uploadMethod isValidExcelDate() Method: Exception");
        return false;
    }
}

5. 세팅 코드 (환경 설정)

1) dispatcher-servlet.xml

<bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver" />


2) pom.xml

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.17</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.17</version>
</dependency>
<dependency>
    <groupId>commons-fileupload</groupId>
    <artifactId>commons-fileupload</artifactId>
    <version>1.3.3</version>
</dependency>
<dependency>
    <groupId>org.apache.commons</groupId>
    <artifactId>commons-lang3</artifactId>
    <version>3.3.2</version>
</dependency>
<dependency>
    <groupId>commons-io</groupId>
    <artifactId>commons-io</artifactId>
    <version>2.5</version>
</dependency>

6. 작업 중 문제 사항

1) DB 데이터
양식 업로드 후, DB(오라클)에 정상적으로 데이터가 입력되고 있음을 확인하고 DELETE 문을 이용하여 데이터를 강제로 삭제하였음. 삭제 처리했을 때는 데이터가 조회되지 않으나, 일정 시간이 지났을 때 데이터가 다시 복원되는 문제점을 확인함.
 
처음, 임시 저장하는 방식으로 진행하여 프로그램이 실행되는 동안 DB에 data가 저장되는 오류가 아닐까 생각하였으나, 실제 문제는 DELETE문 실행 후 Commit 처리를 하지 않아 일정 시간 후 data가 rollback되고 있었음. 이에 Commit 처리하여 해결함.
 
주로, MySQL에서 auto commit으로 설정한 후 작업을 진행하여 Oracle DB 환경을 생각하지 못함. 
DML(Select, Insert, Update, Delete)는 Auto Commit이 되지 않는다는 점을 인지하자!

7. 특이사항

VO, Dao, JSP, Mybatis 코드 업로딩은 생략함.
- VO: 일반적인 필드 정보가 들어있음
- Dao: 쿼리 실행하기 위한 코드 기재되어 있음
- JSP: Back에서 전송한 데이터 처리하기 위한 간단한 코드
- Mybatis: 기본적인 insert문과 select문으로 구성되어 있으며, 유저 식별을 위해 DB에 저장되어 있는 유저인지 조회하는 sql문 사용함

반응형

'자바(Java)' 카테고리의 다른 글

코드로 보는 SOLID 원칙  (0) 2025.02.06
Java - equals, hashCode  (2) 2024.11.08
Java - Stream  (0) 2023.08.17
Java - 메소드 참조와 Optional 클래스  (0) 2023.08.11
Java - 람다식  (0) 2023.08.11

'자바(Java)' Related Articles

티스토리툴바