Java 엑셀 데이터 읽어오기 및 다중 insert 한방에 하기

 

오늘은 엑셀 데이터를 업로드해서 자바에서 데이터를 받아서

insert를 하든 아니면 다시 그 데이터를 다시 조합해서 웹에 다시 전달하는 등

핸들링하는 방법을 알아보겠습니다

 

그리고 클래스명을 잘 확인하도록 하시길 바랍니다

 

---

 

엑셀 데이터 읽어오기

   

@Override
public List<MemberVO> excelReadData(MemberVO memberVO) throws Exception {

    String[] columnNames = new String[20];
	
    // InputStream 클래스에 가지고 있는 파일의 bytes 생성자 매개변수에 할당하게 됩니다
    InputStream inputStream = new ByteArrayInputStream(memberVO.getMbFile()[0].getBytes());

	// inputStream에 엑셀파일이 들어가게 됩니다 이때 WorkbookFactory를 사용하여 파일의 엑셀데이트를 Workbook으로 만들어줍니다 
    org.apache.poi.ss.usermodel.Workbook workbook = WorkbookFactory.create(inputStream);
	
    // 그럼 workbook에 엑셀의 대한 정보값을 읽어올 준비가 됩니다 엑셀에 sheet도 정할수 있습니다
    // getSheetAt(0)의 값으로 엑셀의 첫번째를 읽어온다는 의미가된다
    org.apache.poi.ss.usermodel.Sheet sheet = workbook.getSheetAt(0);
    
    // row별로 이제 iterator() 메서드를 사용하여 행별로 값을 읽어옵니다
    Iterator<Row> rowItr = sheet.iterator(); 

    List<MemberVO> memberList = new ArrayList<MemberVO>();

		// 이제 rowItr를 while로 전체 행의 데이터를 불러옵니다
        while(rowItr.hasNext()) { 
            MemberVO member = new MemberVO();
            Row row = rowItr.next(); 

            if(row.getRowNum() == 0) { 
            	// 첫번째행은 제목이나 컬럼명이 들어있기 때문에 그 컬럼명을 모아서 배열로 넣어 줍니다
                // cellItr에 열에대한 데이터를 0번째 행의 전체 열에 대한 데이터를 가져올 준비를 합니다
                Iterator<org.apache.poi.ss.usermodel.Cell> cellItr = row.cellIterator(); 
                // cellItr에 0번째 행의 열에 대한 데이터를 가져옵니다 그 데이터는 컬럼명이다
                while(cellItr.hasNext()) {
                    org.apache.poi.ss.usermodel.Cell cell = cellItr.next();
                    int index = cell.getColumnIndex();
                    columnNames[index] = cell.getStringCellValue().toString();
                }
                continue; 
            } 
            Iterator<org.apache.poi.ss.usermodel.Cell> cellItr = row.cellIterator(); 

			// 컬럼명의 예상 값이다
            //String[] columnNames = {"우편번호","주소","상세주소","이름","아이디","휴대폰번호","이메일주소","회원가입구분"};
			
            // 이제 인설트할 비즈니스 코드를 우선 조합합니다
            String mbTpcd = "0020";
            String mbAprvKncd = "0010".equals(mbTpcd) ? "0020" : "0010"; 
            member.setMbSttCd("0010".equals(mbTpcd) ? "0030" : "0020");
            member.setMbAprvKncd(mbAprvKncd);
            member.setMbTpcd(mbTpcd);
            member.setSeqNo(memberVO.getSeqNo());
            member.setCtyCd(memberVO.getCtyCd());
            member.setLocCd(memberVO.getLocCd());

            String mbBrn = "";
			
            // 다시 전체 열에 대한 데이터를 가져옵니다 그 데이터를 하나씩 불러와 데이터를 셋팅한다
            while(cellItr.hasNext()) {
            
            	// 한개의 열에 대한 데이터를 가져와 cell에 담습니다
                org.apache.poi.ss.usermodel.Cell cell = cellItr.next();
                
                // 열의 인덱스 넘버를 받아와 처음 0번째 행의 컬럼명을 불러와 case문과 일치할 경우 그 데이터를 삽입합니다
                int index = cell.getColumnIndex();
                if(!"".equals(getValueFromCell(cell))) {
                
                	// columnNames 배열에는 컬럼명을 담아 놓았기때문에
                    switch(columnNames[index].replaceAll(" ","")) {
                       case "우편번호": // 우편번호
                       	   // 숫자를 읽어 올때 소수점으로 들고 올때가 있다 이 경우를 위해 분기작업을 진행한다
                           if(getValueFromCell(cell) instanceof Double) {
                               String stringValue = String.format("%f", getValueFromCell(cell));
                               int indx = stringValue.indexOf(".");
                               String value =  stringValue.substring(0, indx);
                               member.setMbZcd(value);
                               member.setMbStrZcd(value);

                           } else {
                               member.setMbZcd((String)getValueFromCell(cell)); 
                               member.setMbStrZcd((String)getValueFromCell(cell)); 
                           }
                           break; 
                       case "주소": // 주소 
                           member.setMbAddrEnc((String)getValueFromCell(cell)); 
                           member.setMbStrAddr((String)getValueFromCell(cell)); 
                           break; 
                       case "상세주소": // 상세주소
                           member.setMbDtlAddrEnc((String)getValueFromCell(cell)); 
                           member.setMbStrDtlAddr((String)getValueFromCell(cell)); 
                           break; 
                       case "이름": // 이름 
                           member.setMbNm((String)getValueFromCell(cell)); 
                           break; 
                       case "아이디": // 아이디 
                           member.setMbId((String)getValueFromCell(cell)); 
                           break; 
                       case "휴대폰번호": //  
                           member.setMbHpPnEnc(((String) getValueFromCell(cell)).replaceAll("-","")); 
                           member.setMbHpPn(((String) getValueFromCell(cell))); 
                           break; 
                       case "이메일주소": // 이메일주소 
                           member.setMbEmlAddrEnc((String)getValueFromCell(cell)); 
                           break; 
                       case "회원가입구분": // 회원가입구분 
                           String mbJoinDvcd = "";
                           String cellValue = ((String) getValueFromCell(cell)).replaceAll("\\p{Z}", "");
                           if("딜러".equals(cellValue)){
                               mbJoinDvcd = "0010";
                           } else if("대표".equals(cellValue)){
                               mbJoinDvcd = "0020";
                               member.setPeprNm(member.getMbNm());
                           }
                           member.setMbJoinDvcd(mbJoinDvcd);
                           break; 
                    }
                }
            }
            // member에 모든 데이터를 set한 상태이다 그리고 uploadType이 기능은 타입이 두가지이다
            // 데이터를 인설트 하는 것과 엑셀의 데이터를 읽어서 웹화면에 노출해주는 기능 두가지이다
            if("insert".equals(memberVO.getUploadType())){
                if(!mbBrn.equals(member.getMbBrn())){
                    member.setReprHpnoEnc(member.getMbHpPnEnc());
                    member.setEntrFaxnoEnc(member.getMbStrFaxno());
                    member.setBrn(member.getMbBrn());
                    member.setEntrZcd(member.getMbZcd());
                    member.setEntrAddrEnc(member.getMbAddrEnc());
                    member.setEntrDtlAddrEnc(member.getMbDtlAddrEnc());
                }
            }

			// List형에 member를 담아서 한방에 insert를 한다
            memberList.add(member); 

        }
        // while 끝나고 한방에 인설트 한다
        if("insert".equals(memberVO.getUploadType())){
            int resCnt = memberDealerService.insertMbInfoList(memberList);
            if(resCnt <= 0){
                memberList = new ArrayList<MemberVO>();
            }
        }
        
        // uploadType이 insert가 아닐 경우 데이트를 인설트 하지않고 그냥 데이터만 return한다
        return memberList;

}

 

 

반응형

 

 

위의 코드에서 getValueFromCell 메서드선언문을 살펴보자

String, Boolean, Numeric 등의 자료형일 경우 데이터를 return 한다

 

 

  // 셀서식에 맞게 값 읽기 
private static Object getValueFromCell(org.apache.poi.ss.usermodel.Cell cell) { 
    switch(cell.getCellType()) { 
        case STRING: return cell.getStringCellValue(); 
        case BOOLEAN: return cell.getBooleanCellValue(); 
        case NUMERIC: 
            if(DateUtil.isCellDateFormatted(cell)) { 
                return cell.getDateCellValue(); 
             } 
            return cell.getNumericCellValue(); 
        case FORMULA: return cell.getCellFormula(); 
        case BLANK: return "";
        default: 
            return ""; 
    } 
}

 

 

mybatis의 foreach를 활용해서 여러 개의 데이터를 insert를 합니다

아래와 같이 문법에 맞게 값을 할당하면 간편히 다중 insert가 가능합니다

 

 

---

다중 insert 하기

 

 

 <insert id="insertMbInfoList" parameterType="java.util.List">
        INSERT  
        INTO MB_MST 
        (
             MB_ID,                        /* 회원 ID                      */                      
            MB_NM,                        /* 회원 명                       */          
            MB_HP_PN_ENC,                 /* 회원 휴대폰 전화번호 암호화    */           
            MB_TPCD,                      /* 회원 유형코드                    */      
            MB_ZCD,                       /* 회원 우편번호                    */      
            MB_ADDR_ENC,                  /* 회원 주소 암호화              */       
            MB_DTL_ADDR_ENC,              /* 회원 상세 주소 암호화           */        
        )
        VALUES
     <foreach collection="list" index="index" item="mbInfo" separator="," >
        (
            #{mbInfo.mbId},
            #{mbInfo.mbNm},
            #{mbInfo.mbPwdEnc},
            XX1.ENC_VARCHAR2_INS(#{mbInfo.mbHpPnEnc},10,'GLVSSL'),
            #{mbInfo.mbTpcd},
            #{mbInfo.mbZcd},
            XX1.ENC_VARCHAR2_INS(#{mbInfo.mbAddrEnc},10,'GLVSSL'),
            XX1.ENC_VARCHAR2_INS(#{mbInfo.mbDtlAddrEnc},10,'GLVSSL'),
        )
     </foreach>
</insert>

 

 

---

 

이렇게 엑셀 데이터를 읽어와서 그 데이터를 조합하여 다중 insert를 만들어 보았습니다

이것을 활용해서 조합한 데이터를 인설트를 하지 않고 return 해서 웹 화면에 노출하는 것도 가능합니다

어드민 페이지를 만들다 보면 업무에 필요한 작업들을 코딩해야 하는 경우가 있는데

엑셀 작업을 많이 하는 작업자들의 요청은 피할 수 없는 것 같습니다

 

그럼 수고하시고!!

언제나 조금씩 조금씩 한 걸음씩 나갑시다

우리 대모험은 끝나지 않았으니까요!!