[SpringBoot] 대용량 엑셀 파일 생성 및 다운로드 삽질기

개요

작년에 이어 올해도 엑셀 파일 생성 및 다운로드 기능을 담당하게 되었습니다.

https://jaimemin.tistory.com/1889

[SpringBoot] 대용량 엑셀 다운로드를 위한 SXSSFWorkbook

한 가지 달라진 점은 기존 프로젝트와 달리 이번에는 OOM을 방지하기 위해 파일 업로드 및 생성을 별도 인스턴스에서 진행한다는 점입니다.

기존에는 모두 한 서버 내에서 진행했기 때문에 API에 대해 크게 신경을 쓰지 않아도 됐는데 이번에는 파일 전용 서버가 별도로 있었기 때문에 API 설계부터 많은 고민을 해야 했습니다.

제가 워낙 부족한 개발자이기 때문에 제가 한 방법이 정답은 아니겠지만 누군가에게는 도움이 될 것이라고 생각하고 과정과 코드를 기록에 남깁니다.

더 좋은 방법이 있다면 댓글로 알려주시면 감사하겠습니다!

 

1. 초기 접근법

초기에 생각했던 접근 방법은 아래와 같습니다.

• 화면에서 엑셀 생성 요청을 하면 컨트롤러에 엑셀 생성 요청
  • 조회 조건 RequestParam으로 전달
• 컨트롤러에서 엑셀 생성 인스턴스에 해당 요청을 전달
• 엑셀 생성 인스턴스에서 조회 조건 기반 엑셀 생성 후 생성된 엑셀 반환
• AbstractXlsxView를 통해 엑셀 다운로드

 

얼핏 보기에는 별 문제없을 것 같지만 해당 API 설계에는 몇 가지 치명적인 단점이 있습니다.

• API의 Body 내 Workbook이 이동하기 때문에 엑셀의 크기가 커질 경우 API 성능 과부하 발생 가능
• 요청한 엑셀 Row가 많아 엑셀 생성하는데 시간이 오래 걸릴 경우 gateway timeout이 발생하여 클라이언트 측에서 생성된 엑셀을 다운로드하지 못할 가능성 존재
  • gateway timeout이 발생하더라도 파일 인스턴스에서는 계속 엑셀을 생성하기 때문에 자원 낭비

 

2. 보완된 접근법

앞선 접근법에 존재하는 단점을 보완하기 위해 나름 짱구를 굴려 보완된 방법을 생각해봤습니다.

• 화면에서 엑셀 생성을 요청을 하여 컨트롤러에 엑셀 생성 요청
  • 조회 조건과 함께 고유 파일명을 RequestParam으로 전달
  • 요청하는 순간 고유 파일명을 SessionStorage에 저장하여 polling 방식으로 해당 파일명이 세션에 존재하는지 확인
• 컨트롤러에서 엑셀 생성 인스턴스에 요청 전달하고 화면에서 전달한 파라미터에 더해 엑셀 생성을 마친 뒤 호출할 콜백 URL도 함께 전달
• 엑셀 생성인스턴스에서 조회 조건 기반으로 엑셀 생성 후 NAS에 엑셀 저장
  • 해당 프로젝트에서는 NAS를 사용하기 때문에 NAS에 저장했지만 파일 인스턴스 내 저장해도 무방할 것 같음
• NAS에 엑셀 저장 후 파라미터로 받은 callback URL을 호출
  • NAS 내 파일 경로를 RequestParam으로 전달
• callback URL 호출받은 후 세션 내 파일명 저장
• 세션에 파일명이 존재할 경우 초기에 주기적으로 polling 했던 API에서 파일명이 존재한다고 응답을 할 것이고 이때 AbstractXlsxView를 통해 엑셀 다운로드

 

위 방법을 적용할 경우 기존의 문제점을 해결할 수 있습니다.

• API의 body 내 무거운 workbook이 왔다 갔다 하지 않으므로 성능 과부하를 피할 수 있음
• 엑셀이 생성된 후 callback URL을 호출하는 방식이므로 gateway timeout을 피할 수 있음

 

이 방법을 적용할 경우 view에서 주기적으로 엑셀이 생성되었는지 확인하기 위해 polling을 해야 한다는 문제점이 있지만 아직까지는 마이너 한 문제점이라고 판단하여 현재 엑셀 생성 및 다운로드하는 기능은 위 방법을 통해 진행하고 있습니다.

 

2.1 좀 더 보완된 접근법 (2022.05.16 기준)

보완된 접근법에서 추가적으로 보완할 방법을 생각하던 중 팀장님으로부터 Nginx reverse proxy를 적용하는 것을 추천받았습니다.

관련 내용은 아래 링크를 참고해주세요.

https://jaimemin.tistory.com/2121

[Nginx] Nginx Reverse Proxy 정리

 

우선, 기존 보완된 접근법의 문제점은 아래와 같습니다.

• 파일 인스턴스로부터 callback을 받은 WAS가 파일을 내려보낼 때 엑셀 파일을 File 객체를 통해 연 뒤 OutputStream으로 내려보내기 때문에 파일 인스턴스가 아닌 WAS에서 추가적으로 리소스를 사용합니다. 엑셀 크기가 클 경우 부하를 줄 수 있습니다.
• WAS끼리 RestTemplate으로 api를 호출하기 때문에 불필요한 지연이 발생합니다.

 

위 문제점을 해결하기 위해 Nginx Reverse Proxy를 적용하였고 파일 관련 api는 모두 파일 인스턴스에서 처리할 수 있도록 적용했습니다.

이를 통해 파일 관련 리소스는 파일 인스턴스에서만 잡아먹고 WAS끼리의 불필요한 네트워크 통신을 방지할 수 있었습니다.

 

3. 코드 및 설명

코드는 앞서 설명한 순서대로 소개해보겠습니다.

 

3.1 View 내 jQuery

 

	<script type="text/javascript">
	$(function () {
	var fileNames = [];
	sessionStorage.setItem("EXCEL_KEYS", JSON.stringify(fileNames));
	excelPoll();
	});
	function fnExcelDownload() {
	var fileName = "DEPLOY_UPLOAD_EXCEL_" + createUUID();
	alert("엑셀 다운로드가 시작되었습니다.");
	$.ajax({
	url: "/downloadExcelAjax.do",
	method: "get",
	data: $("#searchConditionForm").serialize() + "&fileName=" + fileName
	success: function (data) {
	var fileNames = JSON.parse(sessionStorage.getItem("EXCEL_KEYS"));
	fileNames.push(fileName);
	sessionStorage.setItem("EXCEL_KEYS", JSON.stringify(fileNames));
	},
	error: function (xhr, status, error) {
	alert(xhr.status + " : 서버와의 통신이 원활하지 않습니다. 다시 시도해 주십시오.");
	}
	});
	}
	function createUUID() {
	return 'xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx'.replace(/[xy]/g, function(c) {
	var r = Math.random() * 16 | 0, v = c == 'x' ? r : (r & 0x3 | 0x8);
	return v.toString(16);
	});
	}
	function excelPoll() {
	var fileNames = JSON.parse(sessionStorage.getItem("EXCEL_KEYS"));
	for (var i = 0; i < fileNames.length; i++) {
	var fileName = fileNames[i];
	requestExcelDownload(fileName);
	}
	setTimeout(function () {
	excelPoll();
	}, 2000);
	}
	function requestExcelDownload(fileName) {
	$.ajax({
	url: "/stt/deploy/upload/check/file",
	method: "get",
	data: {
	fileName: fileName
	},
	contentType: "application/json",
	dataType: "json",
	success: function (result) {
	if (result == false) {
	return;
	}
	var excelDownloadUrl = "/downloadExcelView.do?fileName=" + fileName;
	removeFileName(fileName);
	window.open(excelDownloadUrl, "_self", 'width=200, height=200, left=2000, top=2000');
	},
	error: function (xhr, status, error) {
	alert(xhr.status + " : 서버와의 통신이 원활하지 않습니다. 다시 시도해 주십시오.");
	}
	});
	}
	function removeFileName(fileName) {
	var fileNames = JSON.parse(sessionStorage.getItem("EXCEL_KEYS"));
	fileNames = arrayRemove(fileNames, fileName);
	sessionStorage.setItem("EXCEL_KEYS", JSON.stringify(fileNames));
	}
	function arrayRemove(arr, value) {
	return arr.filter(function(ele){
	return ele != value;
	});
	}
	</script>

view raw .js hosted with ❤ by GitHub

 

* 고유 파일명을 부여하기 위해 UUID를 파일명에 포함했습니다.

* 편의를 위해 조회 조건을 넘기는 것은 생략했습니다.

 

 

3.2 Controller + ExcelView

 

	@Controller
	@Slf4j
	public class SampleExcelController {
	private final SampleExcelDownloadService excelDownloadService;
	@Value("${file.upload.path}")
	private String filePath;
	@PostMapping("/downloadExcelAjax.do")
	public ModelAndView getSXSSFWorkbook(ModelAttribute("searchCondition") SearchCondition searchCondition
	, @RequestParam String fileName
	, HttpServletRequest request) throws IOException {
	new Thread(() -> {
	try {
	Map<String, Object> map = excelDownloadService.getExcelMap(searchCondition);
	SXSSFWorkbook workbook = excelDownloadService.getWorkbook(map);
	createExcel(requestDto, workbook, request);
	} catch (IOException e) {
	log.error("[SttTrainExcelController.getSXSSFWorkbook] {}", e.getMessage());
	}
	}).start();
	ModelAndView mav = new ModelAndView("jsonView");
	mav.addObject("code", 1);
	mav.addObject("msg", "정상적으로 처리되었습니다.");
	return mav;
	}
	GetMapping ("/downloadExcelView.do")
	public String downloadSampleExcel(@RequestParam String fileName
	, Model model) throws IOException {
	model.addAttribute("fileName", fileName);
	return "excelView";
	}
	private void createExcel(SttTrainDataExcelRequestDto requestDto
	, SXSSFWorkbook workbook
	, HttpServletRequest request) throws IOException {
	String excelFilePath = filePath
	+ requestDto.getFileName()
	+ ".xlsx";
	String tempFilePath = filePath
	+ UUID.randomUUID().toString().substring(0, 8)
	+ ".xlsx";
	FileOutputStream out = null;
	try {
	//Write the workbook in file system
	Files.createDirectories(Paths.get(filePath));
	out = new FileOutputStream(tempFilePath);
	workbook.write(out);
	saveFilePath(request, excelFilePath);
	} catch (Exception e) {
	e.printStackTrace();
	} finally {
	File tempFile = new File(tempFilePath);
	File excelFile = new File(excelFilePath);
	tempFile.renameTo(excelFile);
	if (ObjectUtils.isNotEmpty(workbook)) {
	workbook.close();
	}
	if (ObjectUtils.isNotEmpty(out)) {
	out.close();
	}
	}
	}
	private void saveFilePath(HttpServletRequest request, String filePath) {
	HttpSession session = request.getSession();
	Set<String> filePaths = (Set<String>) session.getAttribute(ExcelConstants.SESSION_KEY);
	if (ObjectUtils.isEmpty(filePaths)) {
	filePaths = new HashSet<>();
	}
	filePaths.add(filePath);
	session.setAttribute(ExcelConstants.SESSION_KEY, filePaths);
	}
	}
	@RestController
	public ExcelApiController {
	@Value("${file.upload.path}")
	private String filePath;
	@GetMapping("/check/file")
	public ResponseEntity<Boolean> doesFileExists(@RequestParam String fileName) {
	HttpSession session = request.getSession(false);
	Set<String> fileNames = (Set<String>) session.getAttribute(ExcelConstants.SESSION_KEY);
	if (ObjectUtils.isEmpty(fileNames)) {
	return ResponseEntity.ok()
	.body(false);
	}
	return ResponseEntity.ok()
	.body(filePaths.contains(fileName));
	}
	}
	@Component("excelView")
	public class ExcelView extends AbstractXlsView {
	@Value("${file.upload.path}")
	private String filePath;
	@Override
	protected void buildExcelDocument(Map<String, Object> model
	, Workbook wb
	, HttpServletRequest request
	, HttpServletResponse response) throws Exception {
	String fileName = (String) model.get("fileName");
	String totalFilePath = filePath + fileName + ".xlsx";
	File file = new File(totalFilePath);
	if (!file.exists()) {
	log.info("[ExcelView] file not exists");
	return;
	}
	// FileInputStream fis = new FileInputStream(file);
	// XSSFWorkbook workbook = new XSSFWorkbook(fis);
	InputStream is = new FileInputStream(file);
	Workbook workbook = StreamingReader.builder()
	.rowCacheSize(100)
	.bufferSize(4096)
	.open(is);
	SXSSFWorkbook sxssfWorkbook = createSXSSFWorkbook(workbook);
	response.setContentType("application/vnd.ms-excel");
	response.setHeader("Content-Disposition"
	, "attachment;filename=" + file.getName());
	file.delete();
	ServletOutputStream out = response.getOutputStream();
	out.flush();
	sxssfWorkbook.write(out);
	out.flush();
	out.close();
	}
	private SXSSFWorkbook createSXSSFWorkbook(Workbook workbook) {
	SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(-1);
	CellStyle headStyle = SxssfExcelBuilder.makeHeadStyle(sxssfWorkbook);
	CellStyle bodyStyle = SxssfExcelBuilder.makeBodyStyle(sxssfWorkbook);
	for (Sheet sheet : workbook){
	Sheet sxssfSheet = sxssfWorkbook.createSheet(sheet.getSheetName());
	int rowIdx = 0;
	boolean isHeader = true;
	boolean setWidth = false;
	List<Integer> widths = new ArrayList<>();
	for (Row r : sheet) {
	Row sxssfRow = sxssfSheet.createRow(rowIdx++);
	int cellIdx = 0;
	for (Cell c : r) {
	String cellValue = c.getStringCellValue();
	if (!setWidth) {
	widths.add(StringUtils.isEmpty(cellValue)
	? 0 : cellValue.length());
	} else {
	widths.set(cellIdx
	, Math.max(StringUtils.isEmpty(cellValue) ? 0 : cellValue.length(), widths.get(cellIdx)));
	}
	Cell sxssfCell = sxssfRow.createCell(cellIdx++);
	sxssfCell.setCellStyle(isHeader ? headStyle : bodyStyle);
	sxssfCell.setCellValue(cellValue);
	}
	isHeader = false;
	setWidth = true;
	}
	int widthIdx = 0;
	for (int width : widths) {
	sxssfSheet.setColumnWidth(widthIdx++, 256 * Math.max(30, width));
	}
	}
	return sxssfWorkbook;
	}
	}

view raw .java hosted with ❤ by GitHub

 

 

* ExcelController는 엑셀 생성 요청을 받는 API, 세션에 파일명을 저장하는 콜백 API

* ExcelApiController는 view에서 polling 방식으로 파일명이 세션 내 존재하는지 확인하는 API

* ExcelView는 AbstractXlsView를 상속받은 View

 

ExcelView 부연 설명

ExcelView 클래스의 경우 부연설명이 필요합니다.

저는 초기에 주석으로 표시된 두 줄처럼 FileInputStream과 XSSFWorkbook을 통해 엑셀을 workbook으로 저장하고 뷰에 엑셀을 내려주려고 했습니다.

하지만 이럴 경우 엑셀 파일의 용량이 조금만 커지더라도(10MB만 되더라도) 아래와 같이 heap size 메모리 오류가 발생합니다.

java.lang.OutOfMemoryError: Java heap space

 

저장된 엑셀은 분명 10MB 밖에 안되기 때문에 메모리 부족이 발생할 것 같지 않았는데 OKKY 게시글 답변을 보고 해당 에러가 발생한 이유를 알 수 있었습니다.

엑셀파일 용량 작아보이죠.

근데 그건 파일 형식 자체에 압축기능이 들어가서 그런 것이고, 압축 풀면, 고작 5만건 정도에 수백메가 정도 합니다.

https://okky.kr/article/972184

OKKY | JVM heap 메모리 사용량 많을 때 해결 법 질문드립니다.

 

위 답변을 보면 알 수 있다시피 엑셀 파일이 조금만 커지더라도 workbook을 바로 읽을 수 없으므로 저는 monitorjbl님이 개발해주신 오픈소스를 통해 스트리밍 방식으로 엑셀을 cell 단위로 읽고 SXSSFWorkbook을 재구성하는 방식으로 위 문제를 해결했습니다.

https://github.com/monitorjbl/excel-streaming-reader

GitHub - monitorjbl/excel-streaming-reader: An easy-to-use implementation of a streaming Excel reader using Apache POI

 

엑셀 생성 인스턴스에서도 SXSSFWorkbook을 생성하고 ExcelView 내에서도 SXSSFWorkbook을 생성하기 때문에 다소 비효율적인 방법이지만 현재로서는 이 방법이 최선이라고 생각했기 때문에 이렇게 구현했습니다.

 

3.3 엑셀 생성 인스턴스

 

	@Slf4j
	@Service
	@RequiredArgsConstructor
	public class SampleExcelDownloadService {
	private static final int PAGE_SIZE = 10000;
	private final SampleService sampleService;
	/**
	* Gets workbook.
	*
	* @param excelMap the map
	* @return the workbook
	* @throws IOException the io exception
	*/
	public SXSSFWorkbook getWorkbook(Map<String, Object> excelMap) throws IOException {
	List<String> keys = (List<String>)excelMap.get(ExcelConstants.KEYS);
	List<String> headers = (List<String>)excelMap.get(ExcelConstants.HEADERS);
	long listSize = (long)excelMap.get(ExcelConstants.LIST_SIZE);
	SXSSFWorkbook sxssfWorkbook = null;
	try {
	for (int start = 0; start < listSize; start += PAGE_SIZE) {
	List<Map<String, Object>> list = getExcelList(excelMap, start, PAGE_SIZE);
	sxssfWorkbook = SxssfExcelBuilder.createExcel(headers
	, keys
	, null
	, null
	, list
	, start
	, start == 0 ? null : sxssfWorkbook);
	list.clear();
	}
	} catch (Exception e) {
	log.error("[SxssfExcelService] error message: {}", e.getMessage());
	}
	if (listSize == 0) {
	sxssfWorkbook = SxssfExcelBuilder.createExcel(headers
	, keys
	, null
	, null
	, new ArrayList<>()
	, 0
	, null);
	}
	return sxssfWorkbook;
	}
	private List<Map<String, Object>> getListForExcel(SearchCondition searchCondition
	, int start
	, int size) {
	PageHelper.startPage(start / size + 1, size, "REG_DT DESC");
	Page<Example> page = sampleService.list(searchCondition);
	List<Example> examples = page.getResult();
	long listSize = sampleService.count(searchCondition);
	int idx = 1;
	List<Map<String, Object>> list = new ArrayList<>();
	for (Example example : examples) {
	Map<String, Object> tempMap = new HashMap<>();
	// tempMap에 엑셀에 넣고 싶은 데이터 넣어줌
	// key: ExcelConstants.KEY
	// value: 넣고 싶은 데이터
	list.add(tempMap);
	}
	return list;
	}
	}
	@Slf4j
	public class SxssfExcelBuilder {
	private static final String SHEET_NAME = "Sheet";
	private static final String LEFT = "LEFT";
	private static final String CENTER = "CENTER";
	private static final String RIGHT = "RIGHT";
	private static final int MAX_ROW = 1040000;
	private static final int FLUSH_ROW_NUM = 100;
	/**
	* Create excel sxssf workbook.
	*
	* @param headers the headers
	* @param keys the keys
	* @param widths the widths
	* @param sheetName the sheet name
	* @param list the list
	* @param rowIdx the row idx
	* @param sxssfWorkbook the sxssf workbook
	* @return the sxssf workbook
	* @throws IOException the io exception
	*/
	public static SXSSFWorkbook createExcel(List<String> headers
	, List<String> keys
	, List<String> widths
	, String sheetName
	, List<Map<String,Object>> list
	, int rowIdx
	, SXSSFWorkbook sxssfWorkbook) throws IOException {
	/**
	* 최초 생성이면 manual flush를 위해 new SXSSFWorkbook(-1);
	* 이어서 작성일 경우 매개변수로 받은 sxssfWorkbook
	*/
	SXSSFWorkbook workbook = ObjectUtils.isNotEmpty(sxssfWorkbook)
	? sxssfWorkbook : new SXSSFWorkbook(-1);
	/**
	* 최초 생성이면 SHEET_NAME 시트 생성
	* 이어서 작성일 경우 SHEET_NAME에서 이어서 작성
	*/
	sheetName = StringUtils.isEmpty(sheetName)
	? SHEET_NAME + (rowIdx / MAX_ROW + 1) : sheetName;
	boolean newSheet = ObjectUtils.isEmpty(workbook.getSheet(sheetName));
	Sheet sheet = newSheet ? workbook.createSheet(sheetName) : workbook.getSheet(sheetName);
	Row row = null;
	Cell cell = null;
	// 매개변수로 받은 rowNo부터 이어서 작성
	int rowNo = rowIdx % MAX_ROW;
	int index = 0;
	CellStyle headStyle = makeHeadStyle(workbook);
	CellStyle bodyStyleCenter = makeBodyStyle(workbook, CENTER);
	CellStyle bodyStyleLeft = makeBodyStyle(workbook, LEFT);
	CellStyle bodyStyleRight = makeBodyStyle(workbook, RIGHT);
	/**
	* 셀 내 개행을 위해 추가
	* \r\n 추가 시 개행 가능
	*/
	bodyStyleCenter.setWrapText(true);
	bodyStyleLeft.setWrapText(true);
	bodyStyleRight.setWrapText(true);
	if (ObjectUtils.isNotEmpty(widths)) {
	for (String width : widths) {
	sheet.setColumnWidth(index++, Integer.parseInt(width) * 256);
	}
	}
	// 헤더 생성
	if (newSheet) {
	row = sheet.createRow(rowNo);
	index = 0;
	for (String colName : headers) {
	cell = row.createCell(index++);
	cell.setCellStyle(headStyle);
	cell.setCellValue(colName);
	}
	}
	// 데이터와 cell alignment
	for (Map<String,Object> aRow: list) {
	row = sheet.createRow(++rowNo);
	index = 0;
	for (String aKey: keys) {
	if (StringUtils.isEmpty(aKey)) {
	continue;
	}
	cell = row.createCell(index);
	cell.setCellStyle(bodyStyleCenter);
	Object aValue = aRow.get(aKey);
	if (aValue instanceof BigDecimal) {
	cell.setCellValue(((BigDecimal) aValue).toString());
	} else if (aValue instanceof Double) {
	cell.setCellValue(((Double) aValue).toString());
	} else if (aValue instanceof Long) {
	cell.setCellValue(((Long) aValue).toString());
	} else if (aValue instanceof Integer) {
	cell.setCellValue(((Integer) aValue).toString());
	} else if (aValue instanceof String[]) {
	// String[]일 경우 드롭다운 셀 생성
	String[] options = (String[]) aValue;
	DataValidationConstraint constraint = sheet.getDataValidationHelper()
	.createExplicitListConstraint(options);
	// firstRow, lastRow, firstCol, lastCol
	CellRangeAddressList addressList = new CellRangeAddressList(rowNo, rowNo, index, index);
	DataValidation dataValidation = sheet.getDataValidationHelper().createValidation(constraint, addressList);
	dataValidation.setSuppressDropDownArrow(true);
	sheet.addValidationData(dataValidation);
	cell.setCellValue(ObjectUtils.isNotEmpty(options) ? options[0] : null);
	} else {
	cell.setCellValue((String) aValue);
	}
	index++;
	// 주기적인 flush 진행
	if (rowNo % FLUSH_ROW_NUM == 0) {
	((SXSSFSheet) sheet).flushRows(FLUSH_ROW_NUM);
	}
	}
	}
	return workbook;
	}
	/**
	* Make excel data map map.
	*
	* @param listSize the list size
	* @param criteria the criteria
	* @param keys the keys
	* @param headers the headers
	* @param widths the widths
	* @param sheetName the sheet name
	* @return the map
	*/
	public static Map<String, Object> makeExcelDataMap(long listSize
	, Object criteria
	, List<String> keys
	, List<String> headers
	, List<String> widths
	, String sheetName) {
	Map<String, Object> map = new HashMap<>();
	map.put(ExcelConstants.HEADERS, headers);
	map.put(ExcelConstants.CRITERIA, criteria);
	map.put(ExcelConstants.KEYS, keys);
	map.put(ExcelConstants.LIST_SIZE, listSize);
	map.put(ExcelConstants.WIDTHS, widths);
	map.put(ExcelConstants.SHEET_NAME, sheetName);
	return map;
	}
	/**
	* Make head style cell style.
	*
	* @param workbook the workbook
	* @return the cell style
	*/
	public static CellStyle makeHeadStyle(SXSSFWorkbook workbook) {
	CellStyle headStyle = makeBodyStyle(workbook);
	headStyle.setFillForegroundColor(HSSFColor
	.HSSFColorPredefined
	.PALE_BLUE
	.getIndex());
	headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
	headStyle.setAlignment(HorizontalAlignment.CENTER);
	headStyle.setVerticalAlignment(VerticalAlignment.CENTER);
	return headStyle;
	}
	/**
	* Make body style cell style.
	*
	* @param workbook the workbook
	* @return the cell style
	*/
	public static CellStyle makeBodyStyle(SXSSFWorkbook workbook) {
	CellStyle bodyStyle = workbook.createCellStyle();
	bodyStyle.setBorderTop(BorderStyle.THIN);
	bodyStyle.setBorderBottom(BorderStyle.THIN);
	bodyStyle.setBorderLeft(BorderStyle.THIN);
	bodyStyle.setBorderRight(BorderStyle.THIN);
	bodyStyle.setVerticalAlignment(VerticalAlignment.CENTER);
	bodyStyle.setAlignment(HorizontalAlignment.CENTER);
	return bodyStyle;
	}
	/**
	* Make body style cell style.
	*
	* @param workbook the workbook
	* @param align the align
	* @return the cell style
	*/
	public static CellStyle makeBodyStyle(SXSSFWorkbook workbook, String align) {
	CellStyle bodyStyle = workbook.createCellStyle();
	bodyStyle.setBorderTop(BorderStyle.THIN);
	bodyStyle.setBorderBottom(BorderStyle.THIN);
	bodyStyle.setBorderLeft(BorderStyle.THIN);
	bodyStyle.setBorderRight(BorderStyle.THIN);
	bodyStyle.setVerticalAlignment(VerticalAlignment.CENTER);
	if (StringUtils.isNotEmpty(align)) {
	if (LEFT.equals(align)) {
	bodyStyle.setAlignment(HorizontalAlignment.LEFT);
	} else if (RIGHT.equals(align)) {
	bodyStyle.setAlignment(HorizontalAlignment.RIGHT);
	} else {
	bodyStyle.setAlignment(HorizontalAlignment.CENTER);
	}
	}
	return bodyStyle;
	}
	}

view raw .java hosted with ❤ by GitHub

 

* 2022.05.16 부족한 코드 업데이트했습니다.

 

우선, 엑셀 생성하는 코드는 아래 링크를 참고해주세요.

[SpringBoot] 대용량 엑셀 다운로드를 위한 SXSSFWorkbook

* 어차피 ExcelView에서 SXSSFWorkbook을 재구성하므로 기존에 map으로 넘겼던 align과 width는 안 넘겨도 될 것 같습니다.

 

엑셀 생성 인스턴스 부연 설명

20번째 줄을 보면 임시 파일명으로 우선 엑셀을 저장한 뒤 46번째 줄 finally 절에서 파라미터로 넘겨받은 파일명으로 변경하는 것을 확인할 수 있습니다.

이렇게 한 이유는 대용량 엑셀을 생성할 때 데이터를 다 입력한 상태에서 파일이 생성되는 것이 아니라 파일을 생성한 뒤 데이터들이 입력되기 때문입니다.

위 방식대로 진행하지 않고 바로 파라미터로 넘겨받은 파일명으로 엑셀 파일을 생성할 경우 polling 방식으로 요청할 때 파일이 다 생성되지 않았는데도 파일이 존재한다고 판단하여 다운로드를 시도하고 그렇게 할 경우 아래와 같은 에러가 발생합니다.

Exception: Unexpected end of ZLIB input stream

 

따라서, 저는 임시 파일명으로 파일을 생성한 뒤 데이터가 전부 입력되면 원래 파일명으로 바꾸는 방식으로 구현을 했습니다.

 

3.4 SessionListener

엑셀 파일이 NAS나 인스턴스에 쌓일 경우 메모리를 상당히 잡아먹을 수 있기 때문에 일정 시간이 지나면 삭제해줘야 합니다.

따라서, 엑셀이 생성되고 30분이 지나면 파일을 제거하는 방식을 선택했고 코드는 아래와 같습니다.

 

	Slf4j
	public class SessionListener implements HttpSessionListener {
	@Value("${server.servlet.session.timeout}")
	private int sessionTime;
	@Value("${file.upload.path}")
	private String filePath;
	@Override
	public void sessionCreated(HttpSessionEvent se) {
	se.getSession().setMaxInactiveInterval(sessionTime);
	}
	@Override
	public void sessionDestroyed(HttpSessionEvent se) {
	HttpSession session = se.getSession();
	Set<String> filePathes = (HashSet<String>) session.getAttribute(ExcelConstants.SESSION_KEY);
	if (ObjectUtils.isEmpty(filePathes)) {
	return;
	}
	for (String filePath : filePathes) {
	File fileToDelete = new File(filePath);
	if (!fileToDelete.exists()) {
	continue;
	}
	fileToDelete.delete();
	}
	}
	}

view raw .java hosted with ❤ by GitHub

 

 

* SessionListener는 Bean으로 등록해줘야 동작합니다.

 

4. 후기

우선, 긴 글 읽어주셔서 감사합니다.

API를 직접 설계하고 구현까지 완료해서 대단한 일을 한 것처럼 느껴졌는데 막상 글로 작성하고 보니 태클받을 요소가 많이 보이는 것 같습니다.

앞서 개요에서도 말했다시피 지적, 조언은 언제나 환영입니다!

고수님들 많은 도움 부탁드립니다!

이제 저는 wav 파일 업로드 기능을 개발하러 20000...

 

[업데이트] 22.09.15

response stream에 바로 XLSX를 작성할 수 있는 fastexcel 라이브러리를 도입하기 위해 정리한 내용 공유 드립니다.

https://jaimemin.tistory.com/2191

[SpringBoot + Fastexcel] 대용량 엑셀 생성 및 다운로드