[JAVA] 엑셀파일 업로드 및 DB insert 방법!! (영상 有)

 

안녕하세요. 오늘은 엑셀파일을 업로드해서 데이터베이스에 저장하는 방법에 대해 이야기해보겠습니다.

아래는 영상입니다.

저는 관리자 페이지에서 신청자를 엑셀파일에 작성한 다음, 업로드하여 데이터베이스에 저장하였습니다.

 

 

 

의존성 주입

 

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.9</version>
</dependency>
    <dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-contrib</artifactId>
    <version>3.6</version>
    <exclusions>
        <exclusion>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
        </exclusion>
    </exclusions>
    </dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.9</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-schemas</artifactId>
    <version>3.9</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-scratchpad</artifactId>
    <version>3.9</version>
</dependency>

 

엑셀 파일을 읽기 위해선 아파치에서 제공하는 poi라이브러리를 사용해야합니다.

pom.xml에 의존성을 추가해줍니다.

 

 

 

 

파일업로드 화면

 

controller

 

@RequestMapping(value ="/adms/reserve/applicant/list.do")
  public String listApplicant(
          HttpServletRequest request,
          ModelMap model) throws Exception {
 
    return "tiles:adms/reserve/applicant/list";
}

 

업로드 화면의 컨트롤러는 데이터를 사용할 일이 없기때문에 ,

RequestMapping 값과 return 값만 생성해줍니다.

 

 

 

view

 

<%@ page language="java" contentType="text/html; charset=utf-8" pageEncoding="utf-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="ui" uri="http://egovframework.gov/ctl/ui"%>
<%@ taglib prefix="fn" uri="http://java.sun.com/jsp/jstl/functions" %>
<%@ taglib prefix="spring" uri="http://www.springframework.org/tags"%>
<%@ taglib prefix="tiles" uri="http://tiles.apache.org/tags-tiles"%>
<%@ taglib prefix="form" uri="http://www.springframework.org/tags/form" %>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %>
 
<script type="text/javascript">
    
    $(document).ready(function() {
      var msg = "${resMap.msg}";
        if(msg != "") alert(msg);
    });
    
    function _onSubmit(){
        
        if($("#file").val() == ""){
            alert("파일을 업로드해주세요.");
            $("#file").focus();
            return false;
        }
        
        if(!confirm(gTxt("confirm.save"))){
            return false;
        }
        
        return true;
    }
    
</script>
 
<div id="contAreaBox">
    <form name="inputForm" method="post" onsubmit="return _onSubmit();" action="${path}/adms/reserve/applicant/create_action.do"   enctype="multipart/form-data" class="form-horizontal">
        <div class="panel">
            <div class="panel-body">
            <h4 class="mt0"><i class="fa fa-cube" aria-hidden="true"></i>신청자 업로드</h4>
                <div class="table-responsive">
                <p> 양식파일을 다운로드 하시고 파일내에 있는 모든 항목들을 채워서 업로드하셔야 정상적으로 등록됩니다.</p>
                <table id="datatable-scroller" class="table table-bordered tbl_Form">
                        <caption>신청자 업로드</caption>
                        <colgroup>
                            <col width="250px" />
                            <col />
                        </colgroup>
                        <tbody>
                            <tr>
                                <th class="active" style="text-align:right"><label class="control-label" for="">파일 업로드</label></th>
                                <td>
                                    <input type="file" name="file" id="file" accept=".xlsx, .xls"/>
                                </td>
                            </tr>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
        
        <div class="pull-right">
            <input type="submit" value="엑셀파일 업로드" class="btn btn btn-primary btn-lg" />
            <a href="${path}/document/applicant_excelUpload_form.xlsx" class="btn btn btn-primary btn-lg">양식파일 다운로드</a>
        </div>
    </form>
</div>
 
 

 

뷰 페이지도 특별한 점은 없습니다.

form의 action값을 저장 실행하는 url로 설정해주고 input을 file로 설정해줍니다.

 

 

 

 

파일업로드 데이터베이스 저장

 

controller

 

@RequestMapping(value = "/adms/reserve/applicant/create_action.do", method = RequestMethod.POST)
public String createApplicant_action(
        @ModelAttribute("searchVO") tbl_reserveVO searchVO,
        RedirectAttributes redirectAttributes,
        HttpServletRequest request,
        final MultipartHttpServletRequest multiRequest,
        ModelMap model) throws Exception {
 
LoginVO loginVO = loginService.getLoginInfo();
 
 
Map<String, Object> resMap = new HashMap<String, Object>();
 
try{
    
    ExcelRequestManager em = new ExcelRequestManager();
    final Map<String, MultipartFile> files = multiRequest.getFileMap();
    List<HashMap<String,String>> apply =null;
    
    apply = em.parseExcelSpringMultiPart(files,"applicant", 0, "", "reserve");
      
    for(int i = 0; i < apply.size(); i++){
     
       searchVO.setResv_program_type(apply.get(i).get("cell_0"));
       searchVO.setResv_program(apply.get(i).get("cell_1"));
        searchVO.setResv_biz_name(apply.get(i).get("cell_2"));
        searchVO.setResv_biz_owner(apply.get(i).get("cell_3"));
        searchVO.setResv_postno(apply.get(i).get("cell_4").replaceAll(",", ""));
        searchVO.setResv_adrs1(apply.get(i).get("cell_5").replaceAll(",", ""));
        searchVO.setResv_adrs2(apply.get(i).get("cell_6").replaceAll(",", ""));
        searchVO.setResv_biz_tel(apply.get(i).get("cell_7"));
        searchVO.setResv_name(apply.get(i).get("cell_8"));
        searchVO.setResv_birth(apply.get(i).get("cell_9"));
        searchVO.setResv_gender(apply.get(i).get("cell_10"));
        searchVO.setResv_tel(apply.get(i).get("cell_11"));
        searchVO.setResv_email(apply.get(i).get("cell_12"));
        searchVO.setResv_depositor(apply.get(i).get("cell_13"));
        searchVO.setResv_refund(apply.get(i).get("cell_14"));
        searchVO.setResv_state(stateType.getMain_code());
        
        searchVO.setSite_code(loginService.getSiteCode());
        searchVO.setCret_id(loginVO.getId());
        searchVO.setCret_ip(request.getRemoteAddr());
        searchVO.setResv_gubun("L");
                                     
        reserveService.insertReserveVO(searchVO);
        
    }
    
    resMap.put("res", "ok");
    resMap.put("msg", "txt.success");
    
 
}catch(Exception e){
    System.out.println(e.toString());
    resMap.put("res", "error");
    resMap.put("msg", "txt.fail");
    }
 
 
redirectAttributes.addFlashAttribute("resMap", resMap);
return "redirect:/adms/reserve/applicant/list.do";
}

 

본격적인 부분은 이제부터입니다.

클래스 ExcelRequestManager의 parseExcelSpringMultiPart 메서드를 이용하여 엑셀 파일을 해석하고

반복문을 이용하여 저장하는 이 부분이죠.

특이점은 리스트에 cell_+번호 형태로 키값을 넣어주고 그것을 키값으로 순서대로 빼어주면서

각각의 변수에 넣어주어 데이터베이스에 저장하는 형태입니다.

 

 

 

 

 

 

(+추가내용)

 

위의 저장방식은 반복문을 돌면서 하나의 행마다 insert를 하는 구조입니다.

대략 1000건이 넘어가면 과부하 걸릴 리스크가 있기에,

대용량 저장 방식은 아래 배치 방식을 추천드립니다.

 

[Java] 대용량 Insert 상세 설명 (Batch, Dynamic sql)

[Java] 대용량 Insert 상세 설명 (Batch, Dynamic sql)

 

 

 

 

 

 

반응형

 

 

 

class (ExcelRequestManager )  / method (parseExcelSpringMultiPart)

 

public class ExcelRequestManager {public List<HashMap<String, String>> parseExcelSpringMultiPart
(Map<String, MultipartFile> files , String KeyStr, int fileKeyParam,String atchFileId ,String storePath) throws Exception{
    List<HashMap<String, String>> list = null;
    int fileKey = fileKeyParam;
    
    String storePathString = "";
    String atchFileIdString = "";
 
    if ("".equals(storePath) || storePath == null) {
        storePathString = "/home/ikpca/upload/";
    } else {
        storePathString = "/home/ikpca/upload/"+storePath;
    }
 
    if (!"".equals(atchFileId) || atchFileId != null) {
        atchFileIdString = atchFileId;
    } 
 
    File saveFolder = new File(EgovWebUtil.filePathBlackList(storePathString));
 
    if (!saveFolder.exists() || saveFolder.isFile()) {
        saveFolder.mkdirs();
    }
 
    Iterator<Entry<String, MultipartFile>> itr = files.entrySet().iterator();
    MultipartFile file;
    String filePath = "";
 
    while (itr.hasNext()) {
        Entry<String, MultipartFile> entry = itr.next();
        
        file = entry.getValue();
        String orginFileName = file.getOriginalFilename();
 
        if ("".equals(orginFileName)) {
        continue;
        }
 
        int index = orginFileName.lastIndexOf(".");
        String fileExt = orginFileName.substring(index + 1);
        String newName = KeyStr + getTimeStamp() + fileKey;
 
        if (!"".equals(orginFileName)) {
        filePath = storePathString + File.separator + newName+"."+fileExt;
        file.transferTo(new File(EgovWebUtil.filePathBlackList(filePath)));
        }
            list = ExcelManagerXlsx.getInstance().getListXlsxRead(filePath);
        
        fileKey++;
    }    
    return list;
}}

 

파일의 저장할 곳을 만들어주고

그곳에 파일을 생성시켜줍니다.

그리고 마지막에 클래스 ExcelManagerXlsx에 getListXlsxRead로 엑셀 파일을 꺼내 리스트로 담아줍니다.

 

 

 

 

class (EgovWebUtil) / method (filePathBlackList)

 

public class EgovWebUtil {
public static String filePathBlackList(String value) {
    String returnValue = value;
    if (returnValue == null || returnValue.trim().equals("")) {
        return "";
    }
 
    returnValue = returnValue.replaceAll("\\.\\./", ""); 
    returnValue = returnValue.replaceAll("\\.\\.\\\\", "");
 
    return returnValue;
}
}

특정 특수문자를 공백으로 치환해줍니다.

 

 

 

 

class (ExcelManagerXlsx) / method (getListXlsxRead)

 

 

public class ExcelManagerXlsx {
 
private static ExcelManagerXlsx excelXlsxMng;
    
    public ExcelManagerXlsx() {
        // TODO Auto-generated constructor stub
    }
    
    public static ExcelManagerXlsx getInstance() {
        if (excelXlsxMng == null)
            excelXlsxMng = new ExcelManagerXlsx();
        return excelXlsxMng;
    }
 
 
public List<HashMap<String, String>> getListXlsxRead(String excel) throws Exception {
 
List<HashMap<String, String>> list = new ArrayList<HashMap<String,String>>();
File file = new File( excel );
if( !file.exists() || !file.isFile() || !file.canRead() ) {
throw new IOException( excel );
}
XSSFWorkbook wb = new XSSFWorkbook( new FileInputStream(file) );
 
//xls시 이용
//HSSFWorkbook  wb = new HSSFWorkbook ( new FileInputStream(file) );
 
int check = 0;
 
try {
for( int i=0; i<1; i++ ) {  
   for( Row row : wb.getSheetAt(i) ) {
       if(check != 0) {
       
       HashMap<String, String> hMap = new HashMap<String, String>();
       String valueStr = ""; 
       int cellLength = (int) row.getLastCellNum();
       for(int j=0; j<cellLength; j++){
    Cell cell = row.getCell(j);
       
      if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK) {
    valueStr = "";
  }else{
    switch(cell.getCellType()){
        case Cell.CELL_TYPE_STRING :
            valueStr = cell.getStringCellValue();
            break;
        case Cell.CELL_TYPE_NUMERIC : // 날짜 형식이든 숫자 형식이든 다 CELL_TYPE_NUMERIC으로 인식함.
            if(DateUtil.isCellDateFormatted(cell)){ // 날짜 유형의 데이터일 경우,
                SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd", Locale.KOREA);
                String formattedStr = dateFormat.format(cell.getDateCellValue());
                valueStr = formattedStr;
                break;
            }else{ // 순수하게 숫자 데이터일 경우,
                Double numericCellValue = cell.getNumericCellValue();
                if(Math.floor(numericCellValue) == numericCellValue){ // 소수점 이하를 버린 값이 원래의 값과 같다면,,
                    valueStr = numericCellValue.intValue() + ""; // int형으로 소수점 이하 버리고 String으로 데이터 담는다.
                }else{
                    valueStr = numericCellValue + "";
                }
                break;
            }
        case Cell.CELL_TYPE_BOOLEAN :
            valueStr = cell.getBooleanCellValue() + "";
            break;
    }
         
  }
       
 hMap.put("cell_"+j ,valueStr);
           
       }
       
      list.add(hMap);
 }
 
 check++; 
}
 
}
 
} catch( Exception ex ) {
ex.printStackTrace();
}
 
return list;
}
}
 
 
 

 

반복문으로 행과 열을 출력해서 스트링 숫자 등을 구분하여 맵에 cell_+번호 형태로 키값을 넣어주고 

배열값에 넣어줍니다.

 

 

 

 

 

 

간략하게 설명하였는데 궁금한점이 있으시면 댓글 달아주세요~~