[JAVA] DB 쿼리 결과 Excel File로 저장하기 - POI 라이브러리

 

[JAVA] Eclipse와 Oracle DB 연결(1)

 

[JAVA] Eclipse와 Oracle DB 연결(2) - XML파일에서 DB 정보 가져오기

 

저번 포스팅에서 Java와 Oracle을 연동하여 OracleDB의 데이터를 가져와보았다. 이번에는 가져온 데이터를 엑셀 파일에 저장해보도록 하자 ㅇ0ㅇ

 

Java에서 Excel 파일을 쓰기 위해 JXL, POI 두가지의 라이브러리를 가장 많이 사용하는 것 같다.

 

개인적인 생각으로 POI보다 JXL 라이브러리가 사용하기에 훨씬 편리한 것 같지만 JXL 라이브러리는 .xlsx 파일을 지원하지 않아 최대 65,535 라인밖에 쓰지 못한다는 단점이 있다.

나는 아주 많은 데이터를 저장해야 했기 때문에 .xlsx 파일을 지원해주는 POI 라이브러리를 사용했다.

(xls는 최대 65535라인, xlsx는 최대 1048576라인)

 

1. POI.jar 파일 추가해주기

 

POI 라이브러리는 https://poi.apache.org/download.html 에서 다운받을 수 있다.

 

나는 윈도우 환경이기 때문에 가장 최근의 poi-bin.gz파일을 다운받았다.

 

다운받은 gz파일의 압축을  풀고 .jar 파일 java project에 모두 추가해주면 된다. (ooxml-lib, lib 폴더의 .jar file도 모두 추가)

(사실, 필요한 .jar파일만 추가하면 되는데 나는 헷갈리고 귀찮아서 그냥 몽땅 추가해줬다,,^^)

[프로젝트 오른쪽 마우스 클릭] - [Properties] - [Java Build Path] - [classpath] - [Add External JARs...]

 

 

2. POI 라이브러리를 이용하여 EXCEL 파일 쓰기

우선 나는 413887건의 데이터를 저장해야 하는 상황이었다. 처음에는 뭣도 모르고 POI 중 XSSF 라이브러리를 사용하여 코드를 작성했었는데 몇시간이 지나도 작업이 끝나지를 않았다. 그래서 코드가 잘못된건가 생각했는데 또 rownum을 지정하여 데이터를 축소시키면 제대로 수행되었다. 

구글에 검색해본 결과 "대용량 데이터를 다루는 경우 XSSF가 아닌 SXSSF를 사용해야 한다."

그 이유는 XSSF는 파일을 다운로드할 때 메모리에 파일 데이터를 쌓아두었다가 다운로드 받는 방식인 반면, SXSSF는 임시 파일을 생성해 중간중간 임시 파일에 작성하여 메모리를 적게 사용한다. 따라서 대용량 EXCEL 파일을 사용하는 경우 XSSF는 속도가 매우 느리기 때문에 SXSSF를 사용해야 한다는 것이다 ㅇ0ㅇ!!

 

이전 포스팅에서 Oracle DB의 쿼리 결과를 출력하는 Test 코드를 수정하여 Excel 파일에 저장하도록 한 코드이다.

 

<Test 전체 소스코드>

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import java.util.Scanner;
import java.util.Date;
import java.text.SimpleDateFormat;
import java.io.FileOutputStream;
import java.io.File;

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;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

public class Test {
	
		public static void main(String[] args) {
			
			Connection conn = null;
			PreparedStatement pstm = null;
			ResultSet rs = null;
			
			String file_size = null;
			String file_path = null;
			
			Workbook xworkbook = null; //엑셀파일 객체 생성
			Sheet xSheet = null; //시트 객체 생성
			Row xRow = null; //행 객체 생성
			Cell xCell = null; //열 객체 생성
			
			SimpleDateFormat date = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
				
			try {
								
				xworkbook = new SXSSFWorkbook(); //엑셀파일 생성
				xSheet = xworkbook.createSheet("sheet1"); //시트 생성
				
				String quary = "SELECT s_doc_no, description FROM ENGINEER.DOCUMENT";
								
		        conn = DBConnection.getConnection();
				pstm = conn.prepareStatement(quary);
				rs = pstm.executeQuery();
				
				//파일 경로 입력
				System.out.println("Enter a path to save .xlsx file: ");
				Scanner scan = new Scanner(System.in);
				file_path = scan.nextLine();
				scan.close();
				
				//파일 생성
				File file = new File(file_path + ".xlsx"); // 파일 확장자 .xlsx로 고정		
				FileOutputStream fos = new FileOutputStream(file);
				
				System.out.println("Write Start " + date.format(new Date()));
					
				//첫 행 입력
				xRow = xSheet.createRow(0);
				xCell = xRow.createCell(0);
				xCell.setCellValue("S_DWG_NO");
				xCell = xRow.createCell(1);
				xCell.setCellValue("DESCRIPTION");
				xCell = xRow.createCell(2);				

				int row = 1; //row번째 행
					
				while(rs.next()) {
					
					// quary 결과에서 데이터 가져오기
					String s_dwg_no = rs.getString(1); // 쿼리 select문의 첫번째 열
					String description = rs.getString(2); // 쿼리 select문의 두번째 열
                    //String description = rs.getString(description) 처럼 숫자 대신 컬럼명 입력해도 문제 없음
					
					// 데이터 입력
					xRow = xSheet.createRow(row);
					xCell = xRow.createCell(0);
					xCell.setCellValue(s_dwg_no);
					xCell = xRow.createCell(1);
					xCell.setCellValue(description);
					
					row++;		
				}
					
				rs.close();
				pstm.close();
				conn.close();
				
				// 파일 쓰기
				xworkbook.write(fos);
				if(fos != null) {
					fos.close();
				}
				System.out.println("Write complete " + date.format(new Date()));
										
					
			} catch(SQLException sqle) {
					
				System.out.println("quary Error");
				sqle.printStackTrace();
					
			} catch (Exception e) {
					
				System.out.print(e);
				
			} finally {
					
				try {
						
					if(rs != null) { rs.close();}
					if(pstm != null) { pstm.close();}
					if(conn != null) { conn.close();}
						
				} catch(Exception e) {
						
					throw new RuntimeException(e.getMessage());
						
				}
				
			}
		
		}		
}

 

소스코드에 대한 설명은 주석으로 상세히 적어두었기 때문에 따로 설명은 하지 않겠다.

console 창에서 입력한 경로에 .xlsx파일이 생성되어 있을 것이고, 생성된 파일은 아래와 같다. 코드에서 입력한 첫 행인 S_DWG_NO과 DESCRIPTION이 1행으로 들어가 있고 그 밑으로 데이터들이 쭉 저장되어 있다. 

나는 s_dwg_no과 description 데이터가 동일하게 들어있어서 이렇게 나타났지만 다른 데이터가 들어있다면 정상적으로 출력 될 것이니 오해 하지 마쇼 ~~~