SXSSF (Streaming Usermodel API)

매우 큰 사이즈의 스프레드시트를 생성해야하고 힙 공간이 제한되어 있을 때 사용되는 XFF의 API 호환 스트리밍 확장 프로그램
단점으로는 약 20MB csv의 데이터 경우 임시파일의 용량이 GB 단위가 된다고 함. 그럴 경우 gzip 압축을 사용할 수 있도록 옵션을 변경하면 된다고 한다

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

<aside> 💡 HSSF : EXCEL 2007 이전 버전(.xls) - 65535 라인까지 사용가능

XSSF : EXCEL 2007 이후 버전(2007포함 .xlsx - 65535 라인 이상 사용가능

SXSSF : XSSF의 Streaming Version으로 메모리를 적게 사용 - 65535 라인 이상 사용가능

</aside>

SXSSF 방식을 활용한 엑셀 다운로드 예시

pom.xml

dependency 설정

<!-- 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>

예시 1

public void excelDownLoad() {
	FileOutputStream fos = null;

	SXSSFWorkbook workbook = null; // 워크북		 
	SXSSFRow row = null; // 행
	SXSSFCell cell = null; // 셀		 		
	
	CellStyle styleMoneyFormat = null; // 셀 스타일	
	int index = 0; // 셀 헤더 카운트							
	int rowIndex = 1; // 행 카운트							

	List<DataDto> dataList = null;

	// 엑셀 헤더 정보 구성
	String[] cellHeader = {"번호", "테스트", "날짜", "시간"};

	try {
			dataList = mapper.getData();
	    // 워크북 생성
	    workbook = new SXSSFWorkbook();
	    workbook.setCompressTempFiles(true);

			// 워크시트 생성
	    SXSSFSheet sheet = (SXSSFSheet) workbook.createSheet("시트이름");
	    sheet.setRandomAccessWindowSize(100); // 메모리 행 100개로 제한, 초과 시 Disk로 flush

	
		sheet.setColumnWidth(2, 300); 	//셀 칼럼 크기 설정
		row = sheet.createRow(0);		// 행 생성
		// 셀 스타일 생성
		styleMoneyFormat = workbook.createCellStyle(); 
		CreationHelper ch = workbook.getCreationHelper();
		styleMoneyFormat.setDataFormat(ch.createDataFormat().getFormat("#,##0"));

		// 헤더 적용
		for(String head : cellHeader ) {
			cell = row.createCell(index++);
			cell.setCellValue(head);
		}

		for(DataDto dataDto : dataList) {
			row = sheet.createRow(rowIndex);

            cell = row.createCell(0);
            cell.setCellValue(rowIndex++); //번호

            cell = row.createCell(1);
            cell.setCellValue(dataDto.getTest());	//테스트

            cell = row.createCell(2);
            cell.setCellValue(dataDto.getDate());	//날짜

            cell = row.createCell(3);
            cell.setCellValue(dataDto.getTime());	//시간

		}
		
        String filename = "파일명.xlsx";
        String orgFileName = "TEST_TEMP_FILE_01_.xlsx"; // 서버저장파일명
        String fileDownLoadPath = "/home/excelTemp/"

        // 파일생성
        fos = new FileOutputStream(fileDownLoadPath + orgFileName);
        workbook.write(fos);

        paramMap.put("filePath", fileDownLoadPath);
        paramMap.put("realFilNm", orgFileName);
        paramMap.put("viewFileNm", filename);

    } catch (Exception e) {
        if(fos != null) try { fos.close(); } catch(Exception ignore) {}
    } finally {
        try {
            workbook.close();
            workbook.dispose();
            if(fos != null) try { fos.close(); } catch(Exception ignore) {}
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

예시 2

// 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 );
    
	  // 로직처리
		...

	++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(); 임시파일 삭제

예시 3

DB에서 가져오는 data형식이 map과 list를 오가는 구조여서 자료형을 중간에 변경하는 케이스

/*
 *  SXSSFWorkBook 방식으로 excel 다운로드 처리하기.
 */
public void excelDownload(HttpServletRequest request, HttpServletResponse response, Map<String, Object> ExcelMap,  String fileName, String templateFile, String string) throws ParsePropertyException, IOException, org.apache.poi.openxml4j.exceptions.InvalidFormatException {

	ByteArrayOutputStream bos = new ByteArrayOutputStream();
	SXSSFWorkbook workbook = null;
	Row row = null;
	Cell cell = null;
	Sheet sheet1 = null;
	CellStyle cellStyle = null;

	String[] headerName = {
			"순번","카드번호","결제금액"
	};

	String[] headerKey = {
			"ROW","CARD_NO","PRICE"
	};

	int indexRow = 1;
	int indexCell = 0;
	int index = 0;

	try {
			ArrayList<Map<String,Object>> list = (ArrayList<Map<String,Object>>) ExcelMap.get("dataList");
			String writeDate = (String) ExcelMap.get("date");
			System.out.println("list check : " + list);
	
			//워크북 생성
			workbook = new SXSSFWorkbook(100);
			workbook.setCompressTempFiles(true);
	
			//워크시트 생성
			Sheet sheet = workbook.createSheet("sheet1");
	
			//셀스타일 생성
			cellStyle = workbook.createCellStyle();
			CreationHelper helper = workbook.getCreationHelper();
			cellStyle.setDataFormat(helper.createDataFormat().getFormat("#,##0"));
			cellStyle.setFillBackgroundColor(IndexedColors.YELLOW.getIndex());
			cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
	
			//행 생성
			row = sheet.createRow(0);
			//header create
			for(String head : headerName){
				cell = row.createCell(indexCell++);
				cell.setCellValue(head);
				cell.setCellStyle(cellStyle);
			}
			
	
			//body 
			for(int i=0; i<list.size(); i++){
	
				row = sheet.createRow(indexRow++);
				Map<String, Object> maps = (Map<String, Object>) list.get(i);
				for(int j=0; j<headerKey.length;j++){
					if(maps.containsKey(headerKey[j])){
						Object value = maps.get(headerKey[j]);
						cell = row.createCell(cellInt++);
						cell.setCellValue(String.valueOf(value));
					}else{
						cell = row.createCell(cellInt++);
						cell.setCellValue("-");
					}
				}
			}
	
			response.setCharacterEncoding("utf-8");
			response.setContentType("application/ms-excel");
			response.setHeader("Content-Disposition", "attachment; filename=\\"" + fileName + ".xlsx\\";");
	
			workbook.write(bos);
			bos.flush();
			bos.close();

			ServletOutputStream out = response.getOutputStream();
			out.write(bos.toByteArray());
			out.flush();
			out.close();

	} catch (IOException e) {
			e.printStackTrace();
	} finally {
			workbook.close();
	}
}