[POI] 엑셀 다운로드(셀 스타일, 셀 병합)

엑셀 다운로드 셀 스타일 지정과 셀 병합

POI 엑셀 다운로드는 간단하게 구현된다.

하지만 병합한 셀에 테두리가 적용되지 않아 확인해 보니 병합전 각 셀에 스타일을 먼저 입히고 병합을 해야했다.

---

아래사진처럼 엑셀을 구현하려 한다. 

• 문구 부분은 셀을 병합하여 엑셀 내용을 설명하는 부분으로 생성
• 컬럼 부분은 색상과 폰트강조로 잘 보이게 생성
• 전체적으로 자동 줄바꿈과 셀 넓이, 높이 지정

 

 

---

 

▽ ▽ ▽ JSP로 작업한 엑셀 다운로드 예제 ▽ ▽ ▽

 

1.  엑셀 다운로드 제목 정하기

<%@ page import="org.apache.poi.hssf.usermodel.*" %>
<%@ page import="org.apache.poi.hssf.util.*" %>

<%

Calendar cal = Calendar.getInstance();
String year 	= Integer.toString(cal.get(Calendar.YEAR));
String month 	= cal.get(Calendar.MONTH)+1<10?"0" + Integer.toString(cal.get(Calendar.MONTH)+1):Integer.toString(cal.get(Calendar.MONTH)+1);
String date 	= cal.get(Calendar.DATE)<10?"0" + Integer.toString(cal.get(Calendar.DATE)):Integer.toString(cal.get(Calendar.DATE));
String day		= year + month + date;

String fileName = "엑셀다운로드_" + day;
fileName = URLEncoder.encode(fileName, "UTF-8");
fileName = fileName.replaceAll("\\+", "%20");
%>

fileName은 엑셀다운로드_다운로드날짜로 지정하였다. 

Calendar 클래스를 이용해서 날짜를 가져왔다

 

▽ 아래는 정리한 Calendar 클래스 관련 링크

Calendar 클래스

 

2.  workbook 생성 및 스타일 지정

// spreadsheet workbook 생성
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
HSSFRow row = sheet.createRow(0);
HSSFCell cell = row.createCell(0);

// 폰트 설정
HSSFFont font = wb.createFont();
font.setFontHeightInPoints((short)13);
font.setBoldweight((short)font.BOLDWEIGHT_BOLD);

// 헤더(컬럼열)에 들어갈 스타일
HSSFCellStyle headCellStyle = wb.createCellStyle();			
headCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
headCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
headCellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
headCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
headCellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
headCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
headCellStyle.setRightBorderColor(HSSFColor.BLACK.index);
headCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
headCellStyle.setTopBorderColor(HSSFColor.BLACK.index);
headCellStyle.setAlignment (HSSFCellStyle.ALIGN_CENTER);
headCellStyle.setVerticalAlignment (HSSFCellStyle.VERTICAL_CENTER);
headCellStyle.setWrapText(true);
headCellStyle.setFont(font);
headCellStyle.setFillForegroundColor(HSSFColor.LEMON_CHIFFON.index);  
headCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

// 데이터 행,열에 들어갈 스타일
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
cellStyle.setRightBorderColor(HSSFColor.BLACK.index);
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
cellStyle.setAlignment (HSSFCellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment (HSSFCellStyle.VERTICAL_CENTER);
cellStyle.setWrapText(true);

workbook, ceateSheet을 만들고 원하는 스타일을 지정하면 된다.

공통 스타일은 가운데 정렬, 테두리를 설정했고 헤더에는 폰트와 셀색상을 지정했다.

여기서 setWrapText(true)는 자동 줄바꿈이다.

 

 

2.  문구부분(셀병합, 셀높이, 스타일 지정) / 타이틀(컬럼헤더)

<%

//----------- 타이틀 문구 
row = sheet.createRow(0); 	// 1. 첫 번째 행 만들기
row.setHeight((short)1000);	// 2. 행 높이설정 
cell = row.createCell(0);	// 3. 첫 번째 열 만들기
cell.setCellValue("문구");	// 4. 내용
cell.setCellStyle(cellStyle);	// 5. 스타일 적용
cell = row.createCell(1);	// 6. 두번째 열 만들기
cell.setCellStyle(cellStyle);	// 7. 두번째 열 스타일 적용
cell = row.createCell(2);	// (반복)
cell.setCellStyle(cellStyle);
cell = row.createCell(3);
cell.setCellStyle(cellStyle);

//셀 병합
CellRangeAddress mergedRegion = new CellRangeAddress(0, 0, 0, 3); // 첫 번째 행, 첫 번째 열부터 네 번째 열까지 병합
sheet.addMergedRegion(mergedRegion);


//----------- 타이틀(헤더)-컬럼
row = sheet.createRow(1);
cell = row.createCell(0);
cell.setCellValue("번호");
cell.setCellStyle(headCellStyle);
cell = row.createCell(1);
cell.setCellValue("제목");
cell.setCellStyle(headCellStyle);
cell = row.createCell(2);
cell.setCellValue("시작일");
cell.setCellStyle(headCellStyle);
cell = row.createCell(3);
cell.setCellValue("종료일");
cell.setCellStyle(headCellStyle);

%>

 

병합된 셀에 스타일을 적용하려면 각 셀에 스타일을 모두 적용하고 병합을 진행해야한다.

 

* CellRangeAddress(행번호,행몇개, 열번호, 열몇개)
나는 첫번째 행에 첫번째 열부터 네번째 열까지 병합을 위해서 (0,0,0,3)으로 병합하였다.

첫번째 행부터 가로3, 세로3 병합을 원하면 (0,2,0,2)로 하면된다.

번호는  0부터 시작 !

 

2.  문구부분(셀병합, 셀높이, 스타일 지정)

<%
List<Map<String,Object>> dao = null;
int rowCnt = 2; // 위 타이틀 행 이후 번호
try
{
	// DB 데이터
	dao = jdbcTemplate.query(calExcel(), new Db(), year);
	
	String gubunStr = "";
	for (int i = 0; i < dao.size(); i++) {
        row = sheet.createRow(rowCnt++);
        Map<String,Object> ob = dao.get(i);
        // 번호
       	cell    =   row.createCell(0); cell.setCellValue(i+1); cell.setCellStyle(cellStyle);
        // 제목
        cell    =   row.createCell(1); cell.setCellValue(getString(ob, "SUPPORT_NM")); cell.setCellStyle(cellStyle);
        // 시작일
        cell    =   row.createCell(2); cell.setCellValue(getString(ob, "START_DT")); cell.setCellStyle(cellStyle);
        // 종료일
        cell    =   row.createCell(3); cell.setCellValue(getString(ob, "END_DT")); cell.setCellStyle(cellStyle);
    }
	

	// 셀 너비
 	for (short j = 0; j < 13; j++) {
 	    sheet.autoSizeColumn(j);
 	    if (j == 2) {
 	        sheet.setColumnWidth(j, sheet.getColumnWidth(j) + (short)3000); // 2번째 열(시작일)
 	    } else if (j == 3) {
 	        sheet.setColumnWidth(j, sheet.getColumnWidth(j) + (short)3000); // 3번째 열(종료일)
 	    } else {
 	    	sheet.setColumnWidth(j, (sheet.getColumnWidth(j))+(short)1024);	// 1024 = 열너비 3.14
 	    }
 	}

	
	// 엑셀 보내자!
	ByteArrayOutputStream outByteStream = new ByteArrayOutputStream();
	wb.write(outByteStream);
	byte [] outArray = outByteStream.toByteArray();
	outByteStream.close();
	response.setContentType("application/ms-excel");
	response.setContentLength(outArray.length);
	response.setHeader("Expires", "0"); 
	response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".xls");
	OutputStream outStream = response.getOutputStream();
	outStream.write(outArray);
	outStream.flush();
	outStream.close();

}catch(NullPointerException e){
	out.println("<script>alert('처리중 오류가 발생하였습니다. 1');history.go(-1);</script>");
}catch(ArrayIndexOutOfBoundsException e){
	out.println("<script>alert('처리중 오류가 발생하였습니다. 2');history.go(-1);</script>");
} 
%>

DB 데이터를 가져와 엑셀에 출력해준다.

셀 너비는 자동으로 전부 지정했지만 시작일, 종료일 너비가 좁은 것 같아 조금 더 넓게 지정했다.

 

 

 

---

공부하면서 유용했던 부분 메모겸 공유하고자 끄적입니다.

고쳐야하는 부분있다면 댓글 남겨주시면 수정하겠습니다.

행복한 하루 보내세요 (❁´◡`❁)