[java] poi 활용하여 excel upload 후 db insert / excel upload and DB insert

excel upload 기능 구현하기 

---

excel 파일을 업로드하여 데이터를 DB에 입력시키는 과정입니다.

업로드된 파일은 필요가 없어서 삭제처리하였습니다.

 

poi로 excel 업로드하는 것에 대해 구글링을 하면, 아래와 같은 소스 파일이 존재하는데 수정할 사항들이 있어 수정하여 tistory에 공유합니다.

---

---

 1. pom.xml  

Dependency 추가 

<!-- Excel Read/Write 를 위한 Dependency 추가 -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.11</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.11</version>
</dependency>

---

 2. dispatcher-servlet.xml  

been 추가

<!-- been 추가 -->
<bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
     <property name="maxUploadSize" value="100000000" />
     <property name="maxInMemorySize" value="100000000" />
</bean>

---

 3. ExcelFileType.java 

Excel 파일을 읽어 확장자를 비교하는 java 파일

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelFileType {
    /**
     * 
     * 엑셀파일을 읽어서 Workbook 객체에 리턴한다.
     * XLS와 XLSX 확장자를 비교한다.
     * 
     * @param filePath
     * @return
     * 
     */
    public static Workbook getWorkbook(String filePath) {
        
        /*
         * FileInputStream은 파일의 경로에 있는 파일을
         * 읽어서 Byte로 가져온다.
         * 
         * 파일이 존재하지 않는다면은
         * RuntimeException이 발생된다.
         */
        FileInputStream fis = null;
        try {
            fis = new FileInputStream(filePath);
        } catch (FileNotFoundException e) {
            throw new RuntimeException(e.getMessage(), e);
        }
        
        Workbook wb = null;
        
        /*
         * 파일의 확장자를 체크해서 .XLS 라면 HSSFWorkbook에
         * .XLSX라면 XSSFWorkbook에 각각 초기화 한다.
         */
        if(filePath.toUpperCase().endsWith(".XLS")) {
            try {
                wb = new HSSFWorkbook(fis);
            } catch (IOException e) {
                throw new RuntimeException(e.getMessage(), e);
            }
         }
         else if(filePath.toUpperCase().endsWith(".XLSX")) {
            try {
                wb = new XSSFWorkbook(fis);
            } catch (IOException e) {
                throw new RuntimeException(e.getMessage(), e);
            }
         }
         if(fis != null) {
            try {
                  fis.close();
            } catch (IOException e) {
                 System.out.println("예외상황발생");
            }
         }
         return wb;   
    }
}

---

 4. ExcelReadOption.java 

Excel 파일을 읽을 때 옵션을 설정하는 java 파일

import java.util.ArrayList;
import java.util.List;

public class ExcelReadOption {
    /**
     * 엑셀파일의 경로
     */
    private String filePath;
    
    /**
     * 추출할 컬럼 명
     */
    private List<String> outputColumns;
    
    /**
     * 추출을 시작할 행 번호
     */
    private int startRow;
    
    public String getFilePath() {
        return filePath;
    }
    public void setFilePath(String filePath) {
        this.filePath = filePath;
    }
    public List<String> getOutputColumns() {
        
        List<String> temp = new ArrayList<String>();
        temp.addAll(outputColumns);
        
        return temp;
    }
    public void setOutputColumns(List<String> outputColumns) {
        
        List<String> temp = new ArrayList<String>();
        temp.addAll(outputColumns);
        
        this.outputColumns = temp;
    }
    
    public void setOutputColumns(String ... outputColumns) {
        
        if(this.outputColumns == null) {
            this.outputColumns = new ArrayList<String>();
        }
        
        for(String ouputColumn : outputColumns) {
            this.outputColumns.add(ouputColumn);
        }
    }
    
    public int getStartRow() {
        return startRow;
    }
    public void setStartRow(int startRow) {
        this.startRow = startRow;
    }
    
    public int getSheetNum() {
        return sheetNum;
    }
    public void setSheetNum(int sheetNum) {
        this.sheetNum = sheetNum;
    }
}

---

 5. ExcelCellRef.java 

Cell의 이름과 값을 가져오는 java 파일

import org.apache.poi.ss.usermodel.Cell;
import java.text.SimpleDateFormat;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellReference;

public class ExcelCellRef {
    /**
     * Cell에 해당하는 Column Name을 가젼온다(A,B,C..)
     * 만약 Cell이 Null이라면 int cellIndex의 값으로
     * Column Name을 가져온다.
     * @param cell
     * @param cellIndex
     * @return
     */
    public static String getName(Cell cell, int cellIndex) {
        int cellNum = 0;
        if(cell != null) {
            cellNum = cell.getColumnIndex();
        }
        else {
            cellNum = cellIndex;
        }
        
        return CellReference.convertNumToColString(cellNum);
    }
    
    public static String getValue(Cell cell, WorkBook wb) {
        String value = "";
        
        if(cell == null) {
            value = "";
        }
        if(cell != null {
            swicth(cell.getCellType()) {
            case HSSFCell.CELL_TYPE_FORMULA :
               if(evaluator.evaluateFormulaCell(cell)==HSSFCell.CELL_TYPE_NUMERIC) {
                    value = (float)cell.getNumericCellValue() + ""; // 계산된 수식의 값을 가져옴
                }
                else if(evaluator.evaluateFormulaCell(cell)==HSSFCell.CELL_TYPE_STRING) {
                    value = cell.getStringCellValue(); // 계산된 수식의 문자값을 가져옴
                }
                break;
                
            case Cell.CELL_TYPE_NUMERIC :
                if(HSSFDateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat formatter = new SimpleDateFormat("YYYY-MM-dd");
                    value = formatter.format(cell.getDateCellValue());
                }else {
                    if((cell.getNumericCellValue()+"").contains(".0")) {
                    	value = (int)cell.getNumericCellValue() + "";
                    }else {
                    	value = (float)cell.getNumericCellValue() + "";
                    }
                }
                break;
                
            case Cell.CELL_TYPE_STRING :
            	value = cell.getStringCellValue();
                break;
                
            case Cell.CELL_TYPE_BOOLEAN : 
            	value = cell.getBooleanCellValue();
                break;
                
            case Cell.CELL_TYPE_BLANK : 
            	value = "";
                break;
                
            case Cell.CELL_TYPE_ERROR :
            	value = cell.getErrorCellValue() + "";
                break;
                
            default : 
            	value = cell.getStringCellValue();
            }
        }
        return value;
    }
}

---

 6. ExcelRead.java 

Excel 파일을 읽어오는 java 파일,  service에서 호출할 파일

 

import java.util.ArrayList;

import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

public class ExcelRead {
     public static List<Map<String, String>> read(ExcelReadOption excelReadOption) {
     	if(excelReadOption != null) { 
            /*
             * 엑셀파일을 읽어 들인다.
             * FileType.getWorkbook() <-- 파일의 확장자에 따라서 적절하게 가져온다.
             */
            Workbook wb = ExcelFileType.getWorkbook(excelReadOption.getFilePath());

            int sheetNum = wb.getNumberOfSheets();

            Row row = null;        // row
            Cell cell = null;      // cell
            String cellName = "";  // cell name
            int numOfCells = 0;    // cell number 
            
            /*
             * 각 row마다의 값을 저장할 맵 객체
             * 저장되는 형식은 다음과 같다.
             * put("A", "이름");
             * put("B", "게임명");
             */
            Map<String, String> map = null;
            
            /*
             * 각 Row를 리스트에 담는다.
             * 하나의 Row를 하나의 Map으로 표현되며, List에는 모든 Row가 포함될 것이다.
             */
            List<Map<String, String>> result = new ArrayList<Map<String, String>>(); 
            
            /* 엑셀 시트가 1개가 아닌 여러개의 경우 for문을 통해 처리 */
            for(int k=0; k<sheetNum; k++) {
            	System.out.println("Sheet Name : " + wb.getSheetName(k));
            	Sheet sheet = wb.getSheetAt(k);
            	
                /* sheet에서 유효한 행의 개수를 가져온다. */
                int numOfRows = sheet.getLastRowNum() + 1; 
                // System.out.println("numOfRows 전체 행의 개수 : " + numOfRows);
                
                /* 엑셀 파일의 numOfRows가 1이 반환될 경우 예외처리 */
                if(numOfRows <= 1) {
                    map = new HashMap<String, String>();
                    map.put("errorMessage", "numOfRows 1이 반환되는 오류 발생");
                    result.add(map);
                    return result;
                }
            
                /* 각 Row만큼 반복을 한다. */
                for(int rowIndex = excelReadOption.getStartRow() - 1; rowIndex < numOfRows; rowIndex++) {
                    /*
                     * 워크북에서 가져온 시트에서 rowIndex에 해당하는 Row를 가져온다.
                     * 하나의 Row는 여러개의 Cell을 가진다.
                     */
                    row = sheet.getRow(rowIndex);
					
                    // sheet.getRow(rowIndex).getCell(2) : cell(2) (= 3번쨰 컬럼값)이 null인 경우에는 if문을 빠져나가도록 설정
                    if(sheet.getRow(rowIndex).getCell(2) != null && row != null) {
                    
                        /* 가져온 Row의 Cell의 개수를 구한다.*/
                        // numOfCells = row.getPhysicalNumberOfCells();
                        numOfCells = row.getLastCellNum();
                        
                        /* 데이터를 담을 맵 객체 초기화 */
                        map = new HashMap<String, String>();
                        
                        /* cell의 수 만큼 반복한다. */
                        for(int cellIndex = 0; cellIndex < numOfCells; cellIndex++) {
                        
                            /* Row에서 CellIndex에 해당하는 Cell을 가져온다. */
                            cell = row.getCell(cellIndex);
                            
                            /* 
                             * 엑셀 파일로 넘어오는 값이 함수 타입이거나 숫자 타입일 경우, '6.0616xxxxx...'와 같이 표기되는 오류 발생
                             * 함수 타입과 숫자 타입의 경우 String으로 cell타입을 변환 후에 cell값을 가져오는 코드를 추가하였다.
                             */
                            if(cell == null) {
                            	continue;
                            }else {
                            	switch(cell.getCellType()) {
                                    case Cell.CELL_TYPE_NUMERIC :
                                    	if(HSSFDateUtil.isCellDateFormatted(cell)) {
                                        	SimpleDateFormat formatter = new SimpleDateFormat("YYYY-MM-dd");
                                        	cell.setCellValue(formatter.format(cell.getDateCellValue()));
                                        }else {
                                            if((cell.getNumericCellValue() + "").contains(".0"){
                                            	cell.setCellValue((int)cell.getNumericCellValue() + "");
                                            }else {
                                               	cell.setCellValue((float)cell.getNumericCellValue() + "");
                                            }
                                         }
                                         break;
                                    case Cell.CELL_TYPE_STRING :
                                        cell.setCellType(Cell.CELL_TYPE_STRING);
                                        cell.setCellValue(Cell.getStringCellValue().toString());
                                        break;
                                    case Cell.CELL_TYPE_FORMULA :
                                    	cell.setCellType(Cell.CELL_TYPE_STRING);
                                        String temp_value = cell.getStringCellValue();
                                        if(temp_value.indexOf(".") > 0) {
                                            Double value = Double.parseDouble(String.format("%.1f", Double.parseDouble(cell.getRichStringCellValue().toString())));
                                            cell.setCellValue(value);
                                        }else {
                                            cell.setCellValue(Cell.getStringCellValue());
                                        }
                                        break;
                                }
                             }

                            /*
                             * 현재 Cell의 이름을 가져온다
                             * 이름의 예 : A,B,C,D,......
                             */
                            cellName = ExcelCellRef.getName(cell, cellIndex);
                            
                            /*
                             * 추출 대상 컬럼인지 확인한다
                             * 추출 대상 컬럼이 아니라면, 
                             * for로 다시 올라간다
                             */
                            if( !excelReadOption.getOutputColumns().contains(cellName) ) {
                                continue;
                            }
                            
                            /* map객체의 Cell의 이름을 키(Key)로 데이터를 담는다. */
                             map.put(cellName, ExcelCellRef.getValue(cell, wb));
                        }
                        /* 만들어진 Map객체를 List로 넣는다. */
                         map.put("successMessage", "불러오기 성공");
                         result.add(map);
                        }else { /* Column(행) 값이 null이거나 row(열)이 null인 경우 break; */
                            break;
                        }
                    } /* 행의 개수가 끝날 떄까지 */
                } /* 시트 개수가 끝날 떄까지 */
                return result; 
            }
            return null; 
        }
        
/*     
        public static void main(String[] args) {
            
            ExcelReadOption ro = new ExcelReadOption();
            ro.setFilePath("D:/game.xlsx");
            ro.setOutputColumns("A", "B");
            ro.setStartRow(1);
            
            List<Map<String, String>> result = ExcelRead.read(ro);
            
            for(Map<String, String> map : result) {
                System.out.println(map.get("A"));
            }
        }
*/
}

---

 7.excelUpload.jsp 

html

<form id="excelUploadForm" name="excelUploadForm" enctype="multipart/form-data"method="post" action= "excelUpload.do">
        <div class="upload_file">
            <label for="excelFile">Excel 업로드</label> 
            <input type="file" id="excelFile" name="excelFile"/>
            <button type="button" onclick="check()"><span>저장</span></button>
        </div>
</form>

 

javascript

function check() {
    if(confirm("업로드 여부")) {
      var options = {
           success : function(result) {
                if(result.code=="1") {
                    console.log("data 전송 성공");
                    alert(result.msg);
                else {
                    console.log("data 전송 실패");
                    alert(result.msg);        	
                }
            },
            error : function(request, status, error) {
                console.log("error");
                alert("code : " + request.status + "\n" + "message : " + request.responseText + "\n" + "error : " + error);    
            },
            type : "POST"
        };
        $("#excelUploadForm").ajaxSubmit(options);
    }
 }

 

---

 8.controller.java 

// 기본 

@ResponseBody
@RequestMapping(value = "/excelUpload.do", method = RequestMethod.POST)
public Map<String, String> excelUploadAjax(MultipartHttpServletRequest request) throws Exception{
    Map<String, String> result = new HashMap<String, String>();
    MultipartFile excelFile = request.getFile("excelFile");

    try { 
        if(excelFile != null || !excelFile.isEmpty()) {
            result.put("code", "1");
            result.put("msg", "업로드 성공");

            File destFile = new File("C:\\upload\\"+excelFile.getOriginalFilename()); // 파일위치 지정
            excelFile.transferTo(destFile); // 엑셀파일 생성
            service.excelUpload(destFile); // service단 호출
            destFile.delete(); // 업로드된 엑셀파일 삭제
        }else {
            result.put("code", "0");
            result.put("msg", "업로드 실패");
        }
    }catch(Exception e) {
        e.printStackTrace();
    } 
    return result;
}

 

// 수정

@ResponseBody
@RequestMapping(value = "/excelUpload.do", method = RequestMethod.POST)
public Map<String, String> excelUploadAjax(MultipartHttpServletRequest request) throws Exception{
    Map<String, String> result = new HashMap<String, String>();
    MultipartFile excelFile = request.getFile("excelFile");
    try { 
        if(excelFile != null || !excelFile.isEmpty()) {
            String originalFileNm = ""; 
            Stirng fileNm = "";  // 파일명
            String fileExt = ""; // 확장자명
            
            // jsp에서 hidden으로 받아온 날짜 data
            String presentDate = request.getParameter("presentDate"); 
            presentDate = presentDate.substring(0,4)+"-"+presentDate.substring(4,6)+"-"+presentDate.substring(6,8);
           
            // 업로드 파일 위치 지정
            String fileDir = new File("C:\\upload\\"+excelFile.getOriginalFilename());
            fileDir = fileDir.replaceAll("\n", "")..replaceAll("\r", "")..replaceAll("&", "")
            String uploadDir = fileDir + "xxx" + File.separator;
            
            fileNm = excelFile.getOriginalFilename();
            ori_fileNm = CleanUtil.cleanFileNm(fileNm);
            fileExt = CleanUtil.allowExt(fileNm);
            
            if(fileExt == null || fileExt.equals("")) {
            	result.put("code", "1");
                result.put("msg", "허용되지 않는 확장자명");
            }else {
            	File destFile = new File(uploadDir + originalFileNm + "." + fileExt);
                excelFile.transferTo(destFile); // 엑셀파일 생성
                
                String excelDate = service.excelDate(destFile); // 엑셀 cell 1개 데이터 가져오기
                if(!excelDate.equals(presentDate) {
                    result.put("code", "2");
                    result.put("msg", "엑셀 날짜와 업로드 날짜 불일치");
                }else {
                    service.excelUpload(destFile); // service단 호출
                    result.put("code", "3");
                    result.put("msg", "업도르 성공");
                }
                destFile.delete(); // 업로드된 엑셀파일 삭제
            }
        }else {
            result.put("code", "4");
            result.put("msg", "업로드 실패");
        }
    }catch(FileAlreadyExistsException e) {
        System.out.println("FileAlreadyExistsException");
    }catch(NoSuchFileException e) {
        System.out.println("NoSuchFileException");
    }catch(FileNotFoundException e) {
        System.out.println("FileNotFoundException");
    }catch(IOException e) {
        System.out.println("IOException");
    } 
    return result;
}

---

 9.service.java 

<!-- service.java --> 
// 엑셀 업로드 
public void excelUpload(File destFile) throws Exception;

// 엑셀 cell 1개 데이터 가져오기 
public String excelDate(File destFile) throws Exception;

---

 10.serviceImpl.java 

SQL에서 for문을 돌리는 건 나중에 위험성이 있다고 판단하여 impl에서 for문을 통해 처리

<!-- serviceImpl.java -->
    // 엑셀 업로드
    @Override
    public void excelUpload(File destFile) throws Exception {
        ExcelReadOption excelReadOption = new ExcelReadOption();
        excelReadOption.setFilePath(destFile.getAbsolutePath()); //파일경로 추가
        excelReadOption.setOutputColumns("A", "B", "C", "D", "E", "F", "G"); //추출할 컬럼명 추가
        excelReadOption.setStartRow(2); //시작행(헤더부분 제외)
        
        List<Map<String, String>>excelContent  = ExcelRead.read(excelReadOption);
        
        // 첫번쨰 엑셀 cell값(날짜 데이터)을 가지고 전체 변경하기 
        String pDate = excelContent.get(0).get("A").toString();
        for(int i=0; i<excelContent.size(); i++) {
        	if(!excelContent.get(i).get("A").contains(pDate)) {
            	// 첫번째 cell값(날짜 데이터)이 포함되지 않은 cell의 경우에 데이터를 put 시킨다.
        		excelContent.get(i).put("A", pDate); 
            }
        }
            
        for(Map<String, String> article: excelContent){
            // System.out.println(article.get("A"));
            // System.out.println(article.get("B"));
            // System.out.println(article.get("C"));
            // System.out.println(article.get("D"));
            // System.out.println(article.get("E"));
            // System.out.println(article.get("F"));
            // System.out.println(article.get("G"));
             DAO.excelUpload(article); 
        }
    }
    
    // 엑셀 cell 1개 데이터 가져오기
    @Override
    public String excelDate(File destFile) throws Exception {
    	ExcelReadOption excelReadOption = new ExcelReadOption();
        excelReadOption.setFilePath(destFile.getAbsolutePath()); //파일경로 추가
        excelReadOption.setOutputColumns("A", "B", "C", "D", "E", "F", "G"); //추출할 컬럼명 추가
        excelReadOption.setStartRow(2); //시작행(헤더부분 제외)
        
         List<Map<String, String>>excelContent  = ExcelRead.read(excelReadOption);
         String pDate = excelContent.get(0).get("A").toString();
         return pDate;
    }

---

 11.DAO.java 

<!-- DAO.java -->
public void excelUpload(Map<String, String> paramMap) throws Exception {
      this.insert("adminSQL.excelUpload", paramMap);
}

---

 12.SQL.xml 

<!-- SQL.xml -->
<insert id="excelUpload" parameterClass="MAP">
        INSERT INTO list (
                A, 
                B, 
                C,
                D,
                E,
                F,
                G,
                ) 
                VALUES 
                (
                #A#, 
                #B#,
                #C#,
                #D#,
                #E#,
                #F#,
                #G#
                )
</insert>

---

수정사항 

---

[2022-04]

1. 셀 중간에 빈값이 있을때 셀의 갯수를 정확하게 가져오지 못함. (error) 
(기존) numOfCells = row.getPhysicalNumberOfCells();  
(변경) numOfCells = row.getLastCellNum();

 

1. getPhysicalNumberOfCells():
   • 이 메서드는 주어진 행(row)에 실제로 채워진(데이터가 있는) 셀의 수를 반환합니다.
   • 즉, 해당 행의 빈 셀은 세지 않고, 데이터가 있는 셀만을 고려합니다.
   • 예를 들어, 특정 행의 처음 3개 셀에만 데이터가 있다면, 이 메서드는 3을 반환합니다.
   • 이 메서드는 특정 행이 얼마나 많은 데이터가 있는지를 확인할 때 유용합니다.
2. getLastCellNum():
   • 이 메서드는 주어진 행(row)에서 가장 마지막 셀의 인덱스를 반환합니다.
   • 셀의 인덱스는 0부터 시작합니다. 따라서 반환된 값은 가장 마지막 셀의 인덱스 + 1이 됩니다.
   • 이 메서드는 특정 행의 마지막 셀이 어디에 위치하는지 확인할 때 유용합니다.
   • 이 값은 실제로 데이터가 있는지 여부와 관계없이 마지막 셀의 인덱스를 반환하므로, 해당 행의 데이터가 모두 채워져 있지 않더라도 마지막 셀의 위치를 알 수 있습니다.

---

[2022-05]

시트를 지정하는 부분인데, 다른 시트를 읽어야 할 경우에 이 코드를 변경하여햐 한다. 
(기존) Sheet sheet = workbook.getSheet(0); 
(변경) for(int i=0; i < numberOfSheets; i++){ 
               Sheet sheet = workbook.getSheetAt(i); 

---

[2022-08]

excell 에서 열이나 행삭제를 통해서가 아니라 단순히 del키로 삭제한 경우 db에 null값으로 들어가는 문제가 발생

→ excelRead.java에서 null인 경우에 if문을 빠져나가도록 수정하였음 (sheet.getRow(rowIndex).getCell(2))

---

[2022-10]

excell cell 1개 데이터 가져오기 추가 

→  serviceImpl.java - excelDate

 

---

참고 : https://eugene-kim.tistory.com/46

참고 : https://beagle-dev.tistory.com/153