[Spring/POI] SXSSF 방식을 활용한 엑셀 다운로드

그동안 엑셀 다운로드 구현 시 HSSF, XSSF의 방식으로 구현하여 엑셀을 다운로드하였는데, 최근 한 프로젝트 중 약 10만 건 정도의 데이터를 엑셀로 다운받아야 하는 경우가 생겼다.

 

문제는 많은 데이터를 엑셀로 받을 때 속도가 느린것도 느린 것이지만 엑셀 데이터를 읽으면서 메모리에 쌓아두긴 때문에 서버가 과부하가 생겨 멈추는 사태가 발생하였다.

 

물론 이 엑셀 다운로드가 100프로 원인일 것이라고 생각하는 것은 아니지만 어느 정도 큰 영향을 주고 있다고 판단을 하였다. 그래서 이 문제점을 어떻게 해결해야할까 찾아보니 SXSSF방식으로 구현하는 방법이 있었다.

 

XSSF는 파일을 다운로드 시 메모리에 파일 데이터를 쌓아두었다고 다운로드 받는 방식이면, SXSSF는 임시 파일을 생성해 중간중간 임시파일에 작성하여 메모리를 적게 사용하고 있다. (내가 이해하기로는 그런것으로 판단이 된다.)

 

그래서 기존의 XSSF 방식을 SXSSF 방식으로 변경하여 적용하였다.

 

 

 

 

 

▶ pom.xml (이것만 있으면 되는것 같긴한데 오래되서 정확히 기억이 안나네...)

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi</artifactId>
	<version>3.13</version>
</dependency>


<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>3.13</version>
</dependency>

 

▶ 직접 작성한 간단한 예제 (실제 실무에서 적용된 소스에서 일부분을 가져온것입니다.)

// flush되기 전까지 메모리에 들고있는 행의 갯수
int ROW_ACCESS_WINDOW_SIZE = 500;

XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(xssfWorkbook, ROW_ACCESS_WINDOW_SIZE);

SXSSFSheet objSheet = null;
SXSSFRow objRow = null;
SXSSFCell objCell = null; // 셀 생성

// 제목 폰트
Font font = sxssfWorkbook.createFont();
font.setFontHeightInPoints((short) 9);
font.setBold(Boolean.TRUE);
font.setFontName("맑은고딕");

// 제목 스타일에 폰트 적용, 정렬
CellStyle styleHd = sxssfWorkbook.createCellStyle(); // 제목 스타일
styleHd.setFont(font);
styleHd.setAlignment(CellStyle.ALIGN_CENTER);
styleHd.setVerticalAlignment(CellStyle.VERTICAL_CENTER);

styleHd.setBorderTop(CellStyle.BORDER_THIN);
styleHd.setBorderBottom(CellStyle.BORDER_THIN);
styleHd.setBorderRight(CellStyle.BORDER_THIN);
styleHd.setBorderLeft(CellStyle.BORDER_THIN);
styleHd.setBottomBorderColor(IndexedColors.BLACK.getIndex());
styleHd.setTopBorderColor(IndexedColors.BLACK.getIndex());
styleHd.setRightBorderColor(IndexedColors.BLACK.getIndex());
styleHd.setLeftBorderColor(IndexedColors.BLACK.getIndex());

objSheet = sxssfWorkbook.createSheet("사업현황"); // 워크시트 생성


// 스타일 미리 적용
for( int i = 0; i < 1; i++ ) {
	objRow = objSheet.createRow(i);
	for( int j = 0; j < 35; j++ ) {
		objCell = objRow.createCell(j);
		objCell.setCellStyle(styleHd);
		objSheet.setColumnWidth(j, (short) 6000);
	}
}

// 0번째 열
objRow = objSheet.getRow(0);
objRow.setHeight((short) 0x150);

// 연번
objCell = objRow.getCell(0);
objCell.setCellValue("연번");

// 광역
objCell = objRow.getCell(1);
objCell.setCellValue("광역");

// 자치단체
objCell = objRow.getCell(2);
objCell.setCellValue("자치단체");

// 유형
objCell = objRow.getCell(3);
objCell.setCellValue("유형");

// 사업명
objCell = objRow.getCell(4);
objCell.setCellValue("사업명");

// 접수일
objCell = objRow.getCell(5);
objCell.setCellValue("접수일");


int rowNum = 1;
		
CellStyle cellStyle = sxssfWorkbook.createCellStyle(); // 제목 스타일
cellStyle.setFillForegroundColor( HSSFColor.GREY_25_PERCENT.index );
cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);

// list는 실제 데이터 List입니다.
for( CovidSupportVo vo : list ) {

	// 열
    objRow = objSheet.createRow(rowNum);
    objRow.setHeight((short) 0x150);

    objCell = objRow.createCell(0);
    objCell.setCellValue( rowNum );
    
    // Todo 로직처리

	++rowNum;
    
}


response.setContentType("Application/Msexcel");
response.setHeader("Content-Disposition", "ATTachment; Filename=" + URLEncoder.encode("사업관리", "UTF-8") + ".xls");

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

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

sxssfWorkbook.dispose();

 

 

 

저렇게 소스를 적용하고나니 임시파일의 경로와 정말로 지워지는 것인가 궁금하여 찾아보았다.

 

결과적으로 윈도우 OS에서는 환경변수 TEMP에 쓰여진 경로에 poifiles라는 폴더안에 작성되었다가 삭제되는것이고,

리눅스 OS에서는 /tmp 디렉토리에 쓰여졌다가 삭제된다고 하더라. (리눅스 경로 부분은 추측이다.)

물론 위의 소스에서 sxssfWorkbook.dispose(); 이 명령어를 주석처리 해줘야지 임시파일을 삭제하지 않는다.

 

임시 저장 경로와 파일

 

임시파일 경로를 환경변수로 설정하는 것 이외에 다른 방법이 있는지는 좀 더 조사를 해봐야지 알 것 같다.

 

 

SXSSF에도 문제점은 있다고한다.

poi 문서를 읽어보면 약 20MB csv의 데이터 경우 임시파일의 용량이 GB가 된다고 하더라 그럴경우 gzip 압축을 사용할 수 있도록 옵션을 변경하면 된다고 한다.

SXSSFWorkbook wb = new SXSSFWorkbook (); 
wb.setCompressTempFiles (true); // 임시 파일이 압축됩니다

 

참조 : https://poi.apache.org/spreadsheet/how-to.html#sxssf

The New Halloween Document