엑셀파일 읽어서 DB에 저장하기

 

 

 

작업을 하다보면 간혹 엑셀 데이터를 일괄 등록하는 기능이 필요할 때가 있습니다.

이를 위해 엑셀 파일을 읽어 DB 에 저장을 해줘야 하는데 이를 위해서는 아래와 같은 절차가 필요합니다.

 

1. 엑셀 파일 양식을 작성한다.

2. 엑셀 파일을 서버에 업로드한다.

3. 서버에 업로드한 엑셀 파일을 읽어 내용을 행단위로 가져온다.

4. 유효성 검사 후 DB 에 Insert 한다.

5. 서버에 업로드한 엑셀 파일을 삭제한다.

 

엑셀 파일을 서버에 업로드 하지 않고 바로 사용할 수 있는 방법은 찾지 못해 제가 사용한 방법은 cos 컴포넌트로 서버에 임시로 업로드 한 후 jxl 컴포넌트로 엑셀 데이터를 읽어서 처리한 후 업로드 한 파일을 삭제하는 방법입니다.

 

cos 컴포넌트로 서버에 파일 업로드하는 방법은 이전 포스팅(http://ninebear.tistory.com/80) 을 참고하시고 이걸 토대로 엑셀 데이터 처리방법을 덧붙여졌습니다.

 

jxl 컴포넌트는 http://www.andykhan.com/jexcelapi/download.html 에서 JExcelApi v2.6.12 (1911kbytes) 을 다운로드 받으시면 됩니다.

 

 

 

다운로드 받은 jexcelapi_2_6_12.tar.tar 파일을 압축을 풀고 jxl.jar 파일을 톰캣 폴더내의 Common\lib 폴더 내에 넣습니다.

 

라이브러리를 추가하셨다면 톰캣 재가동 합니다.

 

이제 사전준비작업은 끝났고 이제 소스 코딩에 들어갑니다.

우선, 엑셀 파일을 등록하는 폼을 만듭니다.

 

예제) Example.jsp

 

<화면>

  

<소스코드>

 

확장자가 xls 인 Excel 2003 버전 파일만 업로드 가능하도록 하고 Sheet1 에만 작성하도록 사전 조치한 뒤 양식을

다운로드 받을 수 있도록 했습니다.

 

 

 

파일을 선택했는지 여부를 판단하여 알려주기 위해 자바스크립트를 사용하여 요청을 처리하도록 했구요.

이제 선택한 엑셀 파일을 읽어 DB 에 저장하는 Example2.jsp 를 만듭니다.

 

예제) Example2.jsp

 

<소스코드>

 

Sheet1 에 있는 데이터를 데이터가 있는 행 갯수만큼 읽어서 Example 이라는 테이블에 Insert 를

했습니다. 정상적으로 등록이 되었다면 "등록이 완료되었습니다." 라는 메시지창이 나오고

창이 닫힙니다.

 

그리고 테이블을 확인해 보면 데이터가 정상적으로 들어가 있는 것을 확인할 수 있습니다.

 

<DB 테이블 조회>

 

소스코드를 보면 AutoCommit 을 False 로 한 후 정상적으로 일괄 등록이 되었다면

commit 을 하기 때문에 중간에 데이터가 잘못될 경우 rollback 이 되어 전체 등록이 취소됩니다.

대신, DB connection 에 대한 오류 처리부분은 없기 때문에 DB 오류를 제외하고입니다.

 

이를 참고하여 엑셀 데이터 업로드 기능을 구현하시면 됩니다.

감사합니다.

 

 

-by 권군™