Poi를 이용한 엑셀파일 만들기 -본론-

건강한노예·2022년 7월 27일
JavaPOISpringexcel
0

poi를 이용한 대용량 excel 만들기

목록 보기
2/2

그럼 바로 본론으로 들어가보자 . ExcelHandler라는 공통단으로 만들어진 코드이다.



import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import org.apache.ibatis.session.ResultContext;
import org.apache.ibatis.session.ResultHandler;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public abstract class ExcelHandler implements ResultHandler{
	
	String[] header;
	String fileName;
	String sheetName;
	String titleName;
 	int ROW_ACCESS_WINDOW_SIZE;
	XSSFWorkbook xssfWorkbook;
   	SXSSFWorkbook wb;
   	
	SXSSFSheet sheet = null;
	SXSSFRow row = null;
	SXSSFCell cell = null; 
	
	int rowNum = 0; //row 카운트

기본으로 헤더로 지정될 String 배열과 파일이름, 시트 이름, 타이틀이름, 한 로우의 사이즈 변수를 가진다.
기존 XSSF는 대용량 데이터를 이용한 excel을 만들때 oom을 잘 만난다고 한다. 그래서
이번에는 SXSSF를 이용하여 만들기로 했다.


HSSF , XSSF, SXSSF 성능 차이표

	public void ExcelHandler(String[] header, String fileName, String sheetName,String titleName ,int size){
		this.header = header;
		this.fileName = fileName;
		this.sheetName = sheetName;
		this.titleName = titleName;
		this.ROW_ACCESS_WINDOW_SIZE = size;
		this.xssfWorkbook = new XSSFWorkbook();
		this.wb = new SXSSFWorkbook(xssfWorkbook, ROW_ACCESS_WINDOW_SIZE);
		this.sheet = wb.createSheet(this.sheetName); // sheet 생성
		
	}

첫번째 메소드입니다 여기는 뭐 따로 설명할 부분이 없을 것 같아서 넘어가겠습니다.

public void createExcelHeader(){
		List<CellStyle> styleList = styleList();
		CellStyle titleStyle = styleList.get(0);
		//헤더가 0부터 시작하기때문에 0부터 헤더 사이즈 -1 만큼 셀 병합해줄려고 세팅함
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, header.length-1));
		row = sheet.createRow(rowNum++);
		cell = row.createCell((short) 0);
		cell.setCellStyle(titleStyle);
		cell.setCellValue(titleName);
		
		
		CellStyle headStyle = styleList.get(1);
		row = sheet.createRow(rowNum++);
    	// 0번째 부터 시작
    	for(int i=0; i<1; i++) {
    		row = sheet.createRow(rowNum++);
    		for(int j=0; j <header.length; j++) {
    			cell = row.createCell(j);
    			cell.setCellStyle(headStyle);
    			cell.setCellValue(header[j]);
    		}
    	}
	}

전달받은 헤더 배열을 이용해 헤더값을 지정하는 부분입니다.
그리고 titleName받은걸 이용해서 기존 타이틀 이름도 지정할 수 있도록 했습니다.

public void setExcelCell(int num, Object value){
		List<CellStyle> styleList = styleList();
		CellStyle etcStyle = styleList.get(2);
		
	  	cell = row.createCell(num);//cell 생성
	  	cell.setCellStyle(etcStyle);//cell 스타일 지정
    	cell.setCellValue(String.valueOf(value));//생성된 cell에 값 매핑
	}

해당 코드에 제일 중요한 부분인 cell값을 매칭하는 부분.
DB에서 가져온 값이 long 형(db 기준 number)을 String 값으로 변환후 넣어야 따로 오류 없이 자동하여 따로 한번 더 치환.

public void writeExcelFile(HttpServletResponse response) throws IOException{
    	response.setContentType("application/xlsx");
    	response.setHeader("Content-Disposition", "ATTachment; Filename=" + this.fileName +".xlsx");

    	OutputStream fileOut = response.getOutputStream();
    	wb.write(fileOut);
    	fileOut.close();

    	response.getOutputStream().flush();
    	response.getOutputStream().close();

    	wb.dispose(); 
	}

이런식으로 따로 호출하여 종료할 수 있게 했다.

private List<CellStyle> styleList(){
		List<CellStyle> styleList = new ArrayList<CellStyle>();
		if(wb != null) {
			List<Font> fontList = fontList();
			CellStyle titleStyle = wb.createCellStyle();
			CellStyle headStyle = wb.createCellStyle();
			CellStyle etcStyle = wb.createCellStyle();
			
			// 타이틀
			headStyle.setBorderTop(BorderStyle.THIN);
			headStyle.setBorderBottom(BorderStyle.THIN);
			headStyle.setBorderLeft(BorderStyle.THIN);
			headStyle.setBorderRight(BorderStyle.THIN);
		
			// 일반 내용쪽 테두리
			etcStyle.setBorderTop(BorderStyle.THIN);
			etcStyle.setBorderBottom(BorderStyle.THIN);
			etcStyle.setBorderLeft(BorderStyle.THIN);
			etcStyle.setBorderRight(BorderStyle.THIN);
			
			// 배경색
			((XSSFCellStyle)headStyle).setFillForegroundColor(new XSSFColor(new java.awt.Color(204, 255, 255),null))//1);
			headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
			
			titleStyle.setFont(fontList.get(0));
			headStyle.setFont(fontList.get(1));
			etcStyle.setFont(fontList.get(2));
			
			styleList.add(titleStyle);
			styleList.add(headStyle);
			styleList.add(etcStyle);
			
			return styleList;
		}else {
			return null;
		}
	}
	
	
	
	//폰트 세팅
	private List<Font> fontList(){
		if(wb != null) {
			// font 지정
			Font titleFont = wb.createFont();
			Font headFont = wb.createFont();
			Font etcFont = wb.createFont();
			
			// font 세팅------
			// 글씨체 , 폰트 크기 , 볼드체 사용 유무, 폰트색상
			headFont.setFontName("ARIAL");
			titleFont.setFontName("ARIAL");
			etcFont.setFontName("ARIAL");
			
			// 폰트 크기 설정
			// 원하는 폰트 크기 x2 후 0을 붙여서 입력
			titleFont.setFontHeight((short) 320);
			headFont.setFontHeight((short) 200);
			etcFont.setFontHeight((short) 200);
			
			// 이탤릭 사용유무
			titleFont.setBold(true);
			headFont.setBold(true);
			etcFont.setBold(true);
			
			// 폰트색
			titleFont.setColor(IndexedColors.BLUE.getIndex());
			headFont.setColor(IndexedColors.BLACK.getIndex());
			etcFont.setColor(IndexedColors.BLACK.getIndex());
			
			
			List<Font> list = new ArrayList<Font>();
			list.add(titleFont);
			list.add(headFont);
			list.add(etcFont);
			
			return list;
		}else {
			return null;
		}

	}

나는 꾸미는걸 별로 안좋아하지만... 엑셀은 기본만 있으면 좀 칙칙하니깐 좀 꾸밀 수 있도록 만들었다.

1)같은 경우는 XSSF는 특정 byte 단위로 해서 배경색을 지정해줄려니 기존 HSSF에서 쓰던 방식과 좀 달라져서 저런식으로 RGB 코드값을 넣어줬다.. 만약에 입맛대로 수정하고싶으시면 메서드에 rgb값을 받아서 저기다 넣어줄 수 있게 변경하면 좋을 것 같습니다.

import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import org.apache.ibatis.session.ResultContext;
import org.apache.ibatis.session.ResultHandler;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public abstract class ExcelHandler implements ResultHandler{
	
	String[] header;
	String fileName;
	String sheetName;
	String titleName;
 	int ROW_ACCESS_WINDOW_SIZE;
	XSSFWorkbook xssfWorkbook;
   	SXSSFWorkbook wb;
   	
	SXSSFSheet sheet = null;
	SXSSFRow row = null;
	SXSSFCell cell = null; 
	
	int rowNum = 0; //row 카운트
	
	public void ExcelHandler(String[] header, String fileName, String sheetName,String titleName ,int size){
		this.header = header;
		this.fileName = fileName;
		this.sheetName = sheetName;
		this.titleName = titleName;
		this.ROW_ACCESS_WINDOW_SIZE = size;
		this.xssfWorkbook = new XSSFWorkbook();
		this.wb = new SXSSFWorkbook(xssfWorkbook, ROW_ACCESS_WINDOW_SIZE);
		this.sheet = wb.createSheet(this.sheetName); // sheet 생성
		
	}
	
	public void createExcelHeader(){
		List<CellStyle> styleList = styleList();
		CellStyle titleStyle = styleList.get(0);
		//헤더가 0부터 시작하기때문에 0부터 헤더 사이즈 -1 만큼 셀 병합해줄려고 세팅함
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, header.length-1));
		row = sheet.createRow(rowNum++);
		cell = row.createCell((short) 0);
		cell.setCellStyle(titleStyle);
		cell.setCellValue(titleName);
		
		
		CellStyle headStyle = styleList.get(1);
		row = sheet.createRow(rowNum++);
    	// 0번째 부터 시작
    	for(int i=0; i<1; i++) {
    		row = sheet.createRow(rowNum++);
    		for(int j=0; j <header.length; j++) {
    			cell = row.createCell(j);
    			cell.setCellStyle(headStyle);
    			cell.setCellValue(header[j]);
    		}
    	}
	}
	
	public void setExcelCell(int num, Object value){
		List<CellStyle> styleList = styleList();
		CellStyle etcStyle = styleList.get(2);
		
	  	cell = row.createCell(num);//cell 생성
	  	cell.setCellStyle(etcStyle);//cell 스타일 지정
    	cell.setCellValue(String.valueOf(value));//생성된 cell에 값 매핑
	}
	
	public void writeExcelFile(HttpServletResponse response) throws IOException{
    	response.setContentType("application/xlsx");
    	response.setHeader("Content-Disposition", "ATTachment; Filename=" + this.fileName +".xlsx");

    	OutputStream fileOut = response.getOutputStream();
    	wb.write(fileOut);
    	fileOut.close();

    	response.getOutputStream().flush();
    	response.getOutputStream().close();

    	wb.dispose(); 
	}
	
	
	//스타일 지정해서 스타일 설정하는 부분
	private List<CellStyle> styleList(){
		List<CellStyle> styleList = new ArrayList<CellStyle>();
		if(wb != null) {
			List<Font> fontList = fontList();
			CellStyle titleStyle = wb.createCellStyle();
			CellStyle headStyle = wb.createCellStyle();
			CellStyle etcStyle = wb.createCellStyle();
			
			// 타이틀
			headStyle.setBorderTop(BorderStyle.THIN);
			headStyle.setBorderBottom(BorderStyle.THIN);
			headStyle.setBorderLeft(BorderStyle.THIN);
			headStyle.setBorderRight(BorderStyle.THIN);
		
			// 일반 내용쪽 테두리
			etcStyle.setBorderTop(BorderStyle.THIN);
			etcStyle.setBorderBottom(BorderStyle.THIN);
			etcStyle.setBorderLeft(BorderStyle.THIN);
			etcStyle.setBorderRight(BorderStyle.THIN);
			
			// 배경색
			((XSSFCellStyle)headStyle).setFillForegroundColor(new XSSFColor(new java.awt.Color(204, 255, 255),null));
			headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
			
			titleStyle.setFont(fontList.get(0));
			headStyle.setFont(fontList.get(1));
			etcStyle.setFont(fontList.get(2));
			
			styleList.add(titleStyle);
			styleList.add(headStyle);
			styleList.add(etcStyle);
			
			return styleList;
		}else {
			return null;
		}
	}
	
	
	
	//폰트 세팅
	private List<Font> fontList(){
		if(wb != null) {
			// font 지정
			Font titleFont = wb.createFont();
			Font headFont = wb.createFont();
			Font etcFont = wb.createFont();
			
			// font 세팅------
			// 글씨체 , 폰트 크기 , 볼드체 사용 유무, 폰트색상
			headFont.setFontName("ARIAL");
			titleFont.setFontName("ARIAL");
			etcFont.setFontName("ARIAL");
			
			// 폰트 크기 설정
			// 원하는 폰트 크기 x2 후 0을 붙여서 입력
			titleFont.setFontHeight((short) 320);
			headFont.setFontHeight((short) 200);
			etcFont.setFontHeight((short) 200);
			
			// 이탤릭 사용유무
			titleFont.setBold(true);
			headFont.setBold(true);
			etcFont.setBold(true);
			
			// 폰트색
			titleFont.setColor(IndexedColors.BLUE.getIndex());
			headFont.setColor(IndexedColors.BLACK.getIndex());
			etcFont.setColor(IndexedColors.BLACK.getIndex());
			
			
			List<Font> list = new ArrayList<Font>();
			list.add(titleFont);
			list.add(headFont);
			list.add(etcFont);
			
			return list;
		}else {
			return null;
		}

	}

	
	public abstract void createExcelBody(HashMap<String,String> vo);

	@Override
	public void handleResult(ResultContext resultContext) {
		
		HashMap<String,String> data = (HashMap<String, String>) resultContext.getResultObject();

		createExcelBody(data);
		
	}
}

위에 메서드를 전부다 합치면 이러한 java 파일이 하나 뚝딱 완성된다. 그럼 이건 추상 클래스니 이걸 참고할 클래스도 만들어보자..

import java.util.HashMap;


/*
 * resultHandelr를 참조한 ExcelHandler를 참조함 
 * 
*/
public class ExExcelHandler extends ExcelHandler{
	
	private int cellNum = 0;
	private int substitution = 0;
	
	public AExcelHandler(String[] header, String fileName, String sheetName, String titleName,int size, int substitution) {
		super.ExcelHandler(header, fileName, sheetName, titleName ,size);
		this.substitution = substitution; 
	
	}
	//작동 방식 db에서 select 하면 1row를 엑셀에 해당 1row에 맞춰서 뽑아서 넣어줌
	@Override
	public void createExcelBody(HashMap<String, String> map) {
  	    

      	
  		//1. row생성
   	    this.row = sheet.createRow(this.rowNum++);
    	
   	    //2-1. cell생성
   	    for(int i= 1; i<header.length+1; i++) {
   	    	//2-2. 값매핑
   	    	if(substitution== 1) {
   	    		//substitution : 0을 - 이걸로 치환할꺼면 1을 주면 이걸로 치환해서 엑셀 만들어줌
   	    		checkValue(String.valueOf(map.get("key"+i)));
   	    	}else {
   	    		setExcelCell(cellNum++, map.get("key"+i));    	    		   	    		
   	    	}
   	    }
   		cellNum = 0; //cell 번호 0으로 초기화
	}
	
	//0값 -값으로 치환해주는 메서드 .
	private void checkValue(String value) {
		if(value.equals("0")) {
			setExcelCell(cellNum++, "-");
		}else {
			setExcelCell(cellNum++, value);
		}
	}
	
}

실제로 컨트롤러 단에서 같이 쓸 구현시켜놓은 클래스 이다.
나는 Count해서 값을 cell에 넣을려고 하는데 그게 0이 나오면 - 이걸로 대체하고 싶어서 checkValue라는 메서드를 하나 만들어서 추가적으로 사용한다.

이렇게 작성했으면 바로 테스트 해보자
mapper.xml

	<resultMap type="java.util.LinkedHashMap" id="excel">
		<result property="key1" column="name"/>
		<result property="key2" column="1월"/>
		<result property="key3" column="2월"/>
		<result property="key4" column="3월"/>
		<result property="key5" column="4월"/>
		<result property="key6" column="5월"/>
		<result property="key7" column="6월"/>
		<result property="key8" column="7월"/>
		<result property="key9" column="8월"/>
		<result property="key10" column="9월"/>
		<result property="key11" column="10월"/>
		<result property="key12" column="11월"/>
		<result property="key13" column="12월"/>
		<result property="key14" column="total"/>
	</resultMap>


	<select id="selectTotalResultCount" parameterType="Map" resultMap="excel" fetchSize="1000">
	SELECT 
		CONCAT(a.admuserid,'(' ,a.admusername,')') as name, 
		COUNT(case when b.sdate &gt;= ${year}0101000000 and b.sdate &lt;= ${year}0131999999 then 0 END  ) AS '1월',
		COUNT(case when b.sdate &gt;= ${year}0201000000 and b.sdate &lt;= ${year}0231999999 then 0 END  ) AS '2월',
		COUNT(case when b.sdate &gt;= ${year}0301000000 and b.sdate &lt;= ${year}0331999999 then 0 END  ) AS '3월',
		COUNT(case when b.sdate &gt;= ${year}0401000000 and b.sdate &lt;= ${year}0531999999 then 0 END  ) AS '4월',
		COUNT(case when b.sdate &gt;= ${year}0501000000 and b.sdate &lt;= ${year}0531999999 then 0 END  ) AS '5월',
		COUNT(case when b.sdate &gt;= ${year}0601000000 and b.sdate &lt;= ${year}0631999999 then 0 END  ) AS '6월',
		COUNT(case when b.sdate &gt;= ${year}0701000000 and b.sdate &lt;= ${year}0731999999 then 0 END  ) AS '7월',
		COUNT(case when b.sdate &gt;= ${year}0801000000 and b.sdate &lt;= ${year}0831999999 then 0 END  ) AS '8월',
		COUNT(case when b.sdate &gt;= ${year}0901000000 and b.sdate &lt;= ${year}0931999999 then 0 END  ) AS '9월',
		COUNT(case when b.sdate &gt;= ${year}1001000000 and b.sdate &lt;= ${year}1031999999 then 0 END  ) AS '10월',
		COUNT(case when b.sdate &gt;= ${year}1101000000 and b.sdate &lt;= ${year}1131999999 then 0 END  ) AS '11월',
		COUNT(case when b.sdate &gt;= ${year}1201000000 and b.sdate &lt;= ${year}1231999999 then 0 END  ) AS '12월',
		COUNT(b.idx) AS total
		FROM adm_user a 
		LEFT JOIN Rsultlist b 
		ON a.admuser_idx = b.u_idx  GROUP BY a.admuser_idx;
	</select>

mapper.xml은 이런 식으로 작성했다 이러면 결과 값이 1row에 쭈욱 뽑히기 때문에.

이런식으로 나와서 resultHandler를 참조해서 만든 excelHandler가 데이터를 잘 가져오기 때문에

Dao 구성

	public void selectTotalResultCount(@Param("year") String year, ExcelHandler handler){
		Map<String, String> map = new HashMap<String, String>();
		if(!year.equals("")) {
			map.put("year",year);
		}	
		getSqlSession().select("selectTotalResultCount",map, handler);
	}

Dao는 해당 파라미터 값으로 년도만 넘겨주면 해당년도 12월까지에 결과를 쭈욱 뽑아줄 수 있도록 만들고 handler를 넘겨줘서 여기서 가져다가 바로 처리할 수있게 해줬다.

Controller 단

	public void monResultDown(Model model, HttpServletRequest request, HttpServletResponse res,
			@RequestParam(value = "year", defaultValue = "0") String year) throws Exception {
		String [] header = {" ","1월", "2월", "3월", "4월", "5월", "6월", "7월", "8월", "9월", "10월", "11월", "12월","TOTAL"};
		
		ExcelHandler excelHandler = new ExExcelHandler(header, "resultDownload", "sheet1", year+"년 총 발송량" ,500, 1);
		excelHandler.createExcelHeader();

		admUserService.selectTotalResultCount(year, excelHandler);

		excelHandler.writeExcelFile(res);

그럼 전 포스팅했던 서론처럼 반복되는 코드량이 매우 적어지고 이런식으로 간단한 코드가 작성된다. ~resultHandler 그는 신인가?~~
이렇게 하면

이런 식에 Excel 파일이 만들어진다. 물론 지금 이건 대용량 데이터를 따로 처리를 하지는 않지만 곧.. 대용량 데이터를 처리할 일이 있어서 resultHandler와 XSSFWorkbook을 이용했다.

작동방식으 매우 간단하다.
1row가 조회되면 해당 row를 엑셀 row에 대칭 시키고 따로 createExcelBody를 호출 안해도 되는 이유는 아까 execlHandler에 resulthandler에 override한 부분에 넣어둬서 자동으로 매칭 시켜준다. 이러한 작동방식을 가지고 작동한다.
그럼 이번 포스팅도 여기서 마치겠습니다 감사합니다.

profile
건강한노예
코딩하는 노예가 되자
이전 포스트

자바로 대용량 엑셀 만들기 POI 이용 -서론-

0개의 댓글