[POI] poi를 이용한 Excel 다운로드

요즘에 왜 이렇게 피곤이 풀리지 않는지 모르겠네요. :D

항상 피곤하고 눈은 계속 나빠지고 아프기만 합니다.

마치 내 인생 신체적인 정점을 찍고 내려오는 듯한 느낌이랄까 ㅋㅋㅋ

그냥 느낌이겠죠 ㅋㅋ

 

이번엔 poi를 써서 Excel 다운로드를 해볼까 생각중이에요 ..

 

자 그럼 poi는 무엇일까 ?

마이크로소프트 다큐먼트를 위한 JAVA API라고 되어 있습니다.

이 poi에는 여러가지 서브 프로젝트들이 존재합니다.

이 poi를 이용해서 대표적으로 excel을 핸들링 할 수 있게 됩니다. 예를 들어 데이터베이스에 존재하는 데이터들을 excel로 추출할 수 있게 됩니다.

그 방법중에서는 poi를 사용하는 방법도 있지만 html table로 그려서 excel로 저장하는 방법도 있습죠.

 

왜 사용해야 하나라고 묻는다면

간단하게 Excel 파일을 java로 읽고 쓰고 하기 위함이다라고 말씀드릴 수 있습니다.

사실 번역하다가 이런 시.. ㅁ 공부 좀 해야겠어용

 

제가 poi로 뭘했는지에 대해서 얘기하고 코드를 적어내려가도록 할게요.

제가 필요한건 DB에서 어떤 값을 가져오든 컬럼이 몇개이든 상관없이 Excel 시트랑 매핑시켜 데이터를 출력해줘야 했습니다.

 

샘플을 가져다가 사용을 해봤는데 일단 갖다놓고 되는지 안되는지만 하다보니 for문이랑 반복코드가 작열 하더군요 ㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋ.. 이게 나의 진짜 실력

원래 코드는 for문이 난무해야함 ㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋ

 

그래서 바꿨습니다.

그 바꾼걸 포스팅합니다.

 

1. Excel 컬럼이랑 DB 컬럼이랑 매핑 시킬려면 어떻게든 매핑되는 데이터를 가지고 있어야 할 필요가 있었습니다. 배열이 되든 맵이 되든 뭐가 되든 뭐가 있어야 소스는 터치하지 않고 매핑되는 부분만 수정 바꿔주면 되지 않겠음 ? 그래서 짧은 생각으로 이렇게 처리했습니다.

 

map.put("drwng_nm", FormatData.nullCheck(rs.getString("drwng_nm"))+"@@3");

 

이렇게 가져올때 컬럼 순서를 그냥 값에다가 박아줬어요. 물론 저 실데이터를 추출해낼때도 split 해야합니다. 저 숫자는 실데이터와 같이 한번만 사용되게 됩니다.

 

이렇게 db에서 데이터는 가져온겁니다.

 

2. 다음에는 Excel에 작성될 컬럼을 선언해야합니다.

 

String[] titleArr = {"번호","도면경로","설계건명","시공건명","주제목"...};

 

저는 배열에 담았어요. 결국은 저 배열의 순서랑 아까 데이터베이스의 @@뒤에 숫자랑 일치해야 하는거죠.

 

이제 기본적인 작업은 된거에요. EXCEL에 컬럼이랑 그 컬럼에 들어갈 값들을 다 가져온거니까요.

poi로 저것들을 어떻게 담아줘야 하나를 고민해볼 시간입니다.

 

3. poi로 Excel 작성하기

클래스를 새로 작성해서 분리시켰습니다.

그리고 그 클래스 구조는 아래와 같습니다.

 

DataSetVO dsv = new DataSetVO();
HSSFCell cell = null;
HSSFSheet sheet = null;
HSSFRow row = null;
HSSFWorkbook workbook = null;
int tr = 0;

 

public GetExcelHandler(DataSetVO dvo) { this.dsv = dvo; }//생성자

public HSSFWorkbook excute() {}//실 호출메서드

private void headerMapper() {}//EXCEL 컬럼 매퍼

private void dataMapper() {}//DATA 매퍼

private HSSFCellStyle styleMapper(int no) {}//Style 매퍼

private void mergeJobCell() {}//셀 병합

 

작업 순서는 이렇습니다.

DB -> excute 호출(DataSetVO에 header, data, style, megerinfo를 담아 넘김) ->

header 셋팅(style 적용) -> data셋팅 -> 셀 병합

 

그냥 생각나는대로 막 짠거라 클래스 설계원칙도 잘 모르는 ........ 근데 그건 나몰라라 ㅋㅋㅋ

할거니까 생관없음 ㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋ 이 포스팅이 해가 되지 않기를 ㅋㅋ

 

public GetExcelHandler(DataSetVO dvo) {
this.dsv = dvo;
}

생성자입니다. 이 클래스를 생성할 당시에 셀 병합 정보랑 data, header 데이터들을 담고 있습니다.

style도 마찬가지입니다.

 

해더를 매핑합니다 뭐 이 얘긴 컬럼을 생성한다가 쉽겠네요.

private void headerMapper() {
for(tr=0; tr<dsv.getHeaderData().size(); tr++) {

//아까 컬럼이라고 만들어둔 배열을 담은 리스트를 반복합니다. 컬럼이 2줄이라면 2번 돌겠죠.
row = sheet.createRow((short) tr ); //생성된 sheet에 row를 생성합니다.
for(int i=0; i<dsv.getHeaderData().get(tr).length; i++) { //리스트의 배열을 반복합니다.
cell = row.createCell(i); //row에 cell을 생성합니다. i라는 것은 셀의 순서에요.

cell.setCellStyle(styleMapper(0)); //셀 스타일을 적용해요. 메서드인데 아래서 볼게요.
cell.setCellValue(dsv.getHeaderData().get(tr)[i]); //cellvalue 셋팅
if(!StringUtils.isEmpty(dsv.getHeaderData().get(tr)[i])){

//이건 저 같은 경우 header가 2줄이라 병합도 하고 값이 없는 셀이 있어서 .. 그걸 분간하기 위함
sheet.autoSizeColumn((short)i); //cell이 null이 아니라면 셀에 든 text값으로 컬럼 width를 조정
sheet.setColumnWidth(i, (sheet.getColumnWidth(i))+1024 ); //그 사이즈로 부족해서 더 크게 조
}
}
}
}

 

private void dataMapper() {

ArrayList<String> keySet = new ArrayList<String>();
Iterator<String> mapIter = dsv.getDataList().get(0).keySet().iterator();
while(mapIter.hasNext()) keySet.add(mapIter.next().toString());
//map의 젓번째 index를 가져와서 keySet을 미리 담아놓아요.

//keySet는 동일하니까 반복적으로 가져올 필요 없자나요.

for(int i=0; i<dsv.getDataList().size(); i++) {

//실데이터를 가지고 있는 리스트를 반복합니다.
row = sheet.createRow(tr); //sheet에 row를 생성하고
cell = row.createCell(0);

//Excel 첫번째 셀에 번호열을 주기 위해 일부러 0으로 박은거에요.

cell.setCellValue(i+1); //그리고 증가하는 i 값을 넣었습니다.
for(String key : keySet) { //keySet List가 반복합니다.
String[] keyLocale = dsv.getDataList().get(i).get(key).split("@@");

//아까 DB에 실DATA@@INDEX로 담은 애를 split해요.
cell = row.createCell(Integer.parseInt(keyLocale[1]));

//@@뒤의 index로 해당 위치에 셀을 생성합니다~
sheet.autoSizeColumn((short)i); //셀 크기르 조정하구요.
sheet.setColumnWidth(i, (sheet.getColumnWidth(i))+1024 );

//모자랄 것 같아서 좀 더 크게
cell.setCellStyle(styleMapper(1)); //스타일 적용
cell.setCellValue(keyLocale[0]); //@@ 앞의 값으로 셀 데이터 반영
}
tr++;

//이 tr은 뭐냐면요. 아까 header 매퍼에서 excel 헤더들 셋팅 후 행에 대한 값을 가지고 있습니다.

//그래야 데이터가 몇 행부터 들어가야 할지 아니까요.
}
}

 

//스타일이에요. 그냥 header와 body로 나눠서 적용만했어요.

//이 부분이 모듈화 하려니까 좀 애매하더라구요. 다른것도 잘됐다라고 보긴 힘듬 핫핫
private HSSFCellStyle styleMapper(int no) {

HSSFCellStyle CellStyle = workbook.createCellStyle();

if(no == 0) { //0이면 header style이고
CellStyle.setBorderBottom(dsv.getHeaderBorder()); //밑으로 4줄까진 셀 테두리
CellStyle.setBorderLeft(dsv.getHeaderBorder());
CellStyle.setBorderRight(dsv.getHeaderBorder());
CellStyle.setBorderTop(dsv.getHeaderBorder());
CellStyle.setFillPattern(dsv.getHeaderFillpattern()); //셀 패턴
CellStyle.setFillForegroundColor(dsv.getHeaderFillforegroundColor()); //셀 배경 색
CellStyle.setAlignment(dsv.getHeaderAlignment()); //샐 정렬
CellStyle.setVerticalAlignment(dsv.getHeaderVerticalAlignment());//셀 v정렬
} else { //1이면 body style이게 ㅋㅋ
CellStyle.setAlignment(dsv.getBodyAlignment());
CellStyle.setVerticalAlignment(dsv.getBodyVerticalAlignment());
}
return CellStyle;
}

 

//merge입니다

//merge 작업을 해주는 열이

// sheet.addMergedRegion(new CellRangeAddress(Integer.parseInt(lvl_2[0]),v ...

//이부분입니다. 근데 CellRangeAddress에 생성자에 값을 넘겨줘야 하는데 그 값의 형태는 아래와

//같아요.

// new CellRangeAddress(시작 row, 종료 row, 시작 col, 종료 col)

//그래서 값을 배열에 담아서 하나의 index에 1@1@1@1# 과같이 넣었어요. 병합될 셀이 많다면

//1@1@1@1#1@1@1@1#1@1@1@1#1@1@1@1#1@1@1@1# 이러헥 되겠지요.

private void mergeJobCell() {
String[] lvl_1 = dsv.getMergeData().split("#"); //#으로 자르고
for(int lvl1=0; lvl1<lvl_1.length; lvl1++) {
String[] lvl_2 = lvl_1[lvl1].split("@") ; //@으로 잘라
for(int lvl2 = 0; lvl2<lvl_2.length; lvl2++) {
sheet.addMergedRegion(new CellRangeAddress(Integer.parseInt(lvl_2[0]), Integer.parseInt(lv l_2[1]), Integer.parseInt(lvl_2[2]),Integer.parseInt(lvl_2[3])));

//병합합니다.
}
}
}

 

이게 위에 메서드들을 실행하는 실행 메서드 ㅋㅋ 이것만 public 이지여 ㅋㅋ

물론 클래스 내부에 생성된 메서드이구요. 외부에서는 클래스 생성후에 호출해야합니다.

클래스의 리턴값은 HSSFWorkbook 형으로 return 합니다.

 

public HSSFWorkbook excute() {
workbook = new HSSFWorkbook();
sheet = workbook.createSheet(dsv.getSheetName());
if(dsv.getHeaderYn()) headerMapper();
dataMapper();
mergeJobCell();
return workbook;
}

 

4. 일단 HSSFWorkbook 형태로 return까지 받았어요. 그러면 저걸 어떻게 사용자가 다운받게 할수있나 ? 라는것 일단 stream으로 떨구는건 간단합니다만 .. 웹을 사용하는 사용자들을 그런걸 바라지 않습니다. 다운받아야 제맛 어디로 다운로드되는진 알아야죠 ㅋㅋ

 

 

=================수정 2013년 5월 10일=====================

기존의 데이터베이스에서 받아온 값을 어떻게 엑셀의 컬럼과 매핑시키는지에 대한 내용이 아래와 같았죠.

map.put("drwng_nm", FormatData.nullCheck(rs.getString("drwng_nm"))+"@@3");

@@의 뒷부분이 엑셀 컬럼 위치를 지정하는 부분이었습니다.

이것을 Action단에서만 수정할 수 있게 만들었습니다.

즉, DAO는 건드릴 필요가 없는겁니다. return 형만 맞으면 그냥 갖다가 쓰면 되는거죠.

String[] titleArr = {"번호@@cnt","도면경로","설계건명@@cnst_nm"....

 

이렇게 변경했구요. 컬럼을 정의한 배열의 뒤에 "@@HashMap의 키값"으로 구성했습니다.

실제로 저 배열을 통해서 데이터베이스에서 가지고온 HashMap의 데이터를 key값을 통해서 가지고 올 수 있습니다.

 

정의해둔 클래스의 생성자에 넣어줘야 할 기본 값들입니다.

아래의 값을 셋팅하고 VO를 던져야만이 데이터가 나옵니다.

단, Style 무방합니다. 근데 지정안하게 될 시, 원하지 않는 색상이 셋팅될지도 모릅니다.

 

DataSetVO dsv = new DataSetVO();
dsv.setHeaderData(str); //Excel 컬럼을 구성할 String[]형 ArrayList
dsv.setDataList(mapList); //Excel 데이터를 구성할 HashMap<String, String> 형 ArrayList
dsv.setSheetName("도면리스트"); //Excel Sheet 명
dsv.setHeaderYn(true); //Excel 컬럼을 구성할지에 대한 여부
dsv.setHeaderBorder(HSSFCellStyle.BORDER_THIN);

//Excel 컬럼의 Borer 값 (top, bottom, right, left 통일 적용)
dsv.setHeaderAlignment(HSSFCellStyle.ALIGN_CENTER);

//Excel 컬럼의 정렬 값 (HSSFCellStyle)
dsv.setHeaderVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //Excel 컬럼의 세로 정렬
dsv.setHeaderFillpattern(HSSFCellStyle.SOLID_FOREGROUND); //Excel 컬럼의 배경 패턴
dsv.setHeaderFillforegroundColor(HSSFColor.LIGHT_YELLOW.index); //Excel 배경 색 지정
dsv.setBodyBorder(HSSFCellStyle.BORDER_DOTTED);

//Excel Body Data 셀의 border (right, left, bottom)
dsv.setBodyAlignment(HSSFCellStyle.ALIGN_LEFT); //Excel Body Data 셀의 가로 정렬
dsv.setBodyVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

//Excel Body Data 셀의 세로 정렬
dsv.setMergeData(mgData);

GetExcelHandler geh = new GetExcelHandler(dsv); //성자로 DataSetVO를 넘겨줌
HSSFWorkbook wb = geh.excute();

 

geh.excute()를 통해서 HSSFWorkbook형의 Excel 데이터를 반환받아 볼 수 있습니다.

 

클래스 첨부해둘게요 ~ 좀 허접하지만 참고하세요 ㅋㅋ

 

GetExcelHandler.java

 

poi-3.7.jar