[SpringBoot] Execute failed: Invalid row number (1048576) outside allowable range (0..1048575)

개요

기존에 대용량 엑셀 다운로드를 위한 SXSSFWorkbook에 대해 공유한 적이 있습니다.

https://jaimemin.tistory.com/1889

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

 

엑셀 칼럼을 추가해달라는 운영팀 요청에 추가한 뒤 테스트하는 과정에서 아래와 같은 에러 메시지가 발생했습니다.

Execute failed: Invalid row number (1048576) outside allowable range (0..1048575)

 

확인해본 결과, 엑셀 시트당 최대 행의 개수는 1,048,575개인데 저는 그것보다 많은 양의 데이터를 하나의 시트에 작성하려고 하다 보니 위와 같은 에러가 발생했습니다.

즉, 기능적으로 문제가 있는 것이 아니라 엑셀 프로그램의 한계 때문에 발생하는 문제였습니다.

 

해결 방법

위 에러를 해결하기 위해 저는 시트 당 1,040,000개의 데이터를 작성하는 방식으로 코드를 수정했습니다.

자세한 내용은 아래 수정된 ExcelView.java 코드를 참고해주세요.

 

ExcelView.java

	@Component("excelView")
	@RequiredArgsConstructor
	public class ExcelView extends AbstractXlsView {
	private static final int MAX_ROW = 1040000;
	private static final int PAGING_SIZE = 10000;
	private final ExcelService excelService;
	@Override
	protected void buildExcelDocument(Map<String, Object> map, Workbook wb
	, HttpServletRequest request, HttpServletResponse response) throws Exception {
	Map<String, Object> excelMap = (Map<String, Object>) map.get("excelMap");
	List<String> keys = (List<String>) excelMap.get("keys");
	List<String> headers = (List<String>) excelMap.get("headers");
	List<String> widths = (List<String>) excelMap.get("widths");
	List<String> aligns = (List<String>) excelMap.get("aligns");
	long listSize = (Long) excelMap.get("listSize");
	String fileName = (String) excelMap.get("fileName");
	String userAgent = request.getHeader("User-Agent");
	if (userAgent.contains("Trident")
	|| (userAgent.indexOf("MSIE") > -1)) {
	fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
	} else if (userAgent.contains("Chrome")
	|| userAgent.contains("Opera")
	|| userAgent.contains("Firefox")) {
	fileName = new String(fileName.getBytes("UTF-8"), "ISO-8859-1");
	}
	SXSSFWorkbook sxssfWorkbook = null;
	try {
	for (int start = 0; start < listSize; start += PAGING_SIZE) {
	List<Map<String, Object>> list = excelSize.getExcelList(start, PAGING_SIZE);
	sxssfWorkbook = getWorkbook(filename, headers, keys, widths
	, aligns, list, start, sxssfWorkbook);
	list.clear(); // 리스트 페이징 처리 및 메모리
	}
	response.setContentType("application/vnd.ms-excel");
	response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
	ServletOutputStream output = reponse.getOutputStream();
	output.flush();
	workbook.write(output);
	output.flush();
	output.close();
	} catch (Exception e) {
	log.error("[SxssfExcelView] error message: {}", e.getMessage());
	} finally {
	if (ObjectUtils.isNotEmpty(sxssfWorkbook)) {
	sxssfWorkbook.close();
	}
	}
	}
	private SXSSFWorkbook getWorkbook(String fileName
	, List<String> headers
	, List<String> keys
	, List<String> widths
	, List<String> aligns
	, List<Map<String, Object>> list
	, int rowIdx
	, SXSSFWorkbook sxssfWorkbook) {
	// 최초 생성이면 manual flush를 위해 new SXSSFWorkbook(-1)
	// 이어서 작성일 경우 매개변수로 받은 sxssfWorkbook
	SXSSFWorkbook workbook = ObjectUtils.isNotEmpty(sxssfWorkbook)
	? sxssfWorkbook : new SXSSFWorkbook(-1);
	// 최초 생성이면 SheetN 생성
	// 이어서 작성일 경우 SheetN에서 이어서
	String sheetName = "Sheet" + (rowIdx / MAX_ROW + 1); // 각 시트 당 1,040,000개씩
	boolean newSheet = ObjectUtils.isEmpty(workbook.getSheet(sheetName));
	Sheet sheet = ObjectUtils.isEmpty(workbook.getSheet(sheetName))
	? workbook.createSheet(sheetName) : workbook.getSheet(sheetName);
	CellStyle headerStyle = createHeaderStyle(workbook);
	CellStyle bodyStyleLeft = createBodyStyle(workbook, "LEFT");
	CellStyle bodyStyleRight = createBodyStyle(workbook, "RIGHT");
	CellStyle bodyStyleCenter = createBodyStyle(workbook, "CENTER");
	// \r\n을 통해 셀 내 개행
	// 개행을 위해 setWrapText 설정
	bodyStyleLeft.setWrapText(true);
	bodyStyleRight.setWrapText(true);
	bodyStyleCenter.setWrapText(true);
	int idx = 0;
	for (String width : widths) {
	sheet.setColumnWidth(idx++, Integer.parseInt(width) * 256);
	}
	Row row = null;
	Cell cell = null;
	// 매개변수로 받은 rowIdx % MAX_ROW 행부터 이어서 데이터
	int rowNo = rowIdx % MAX_ROW;
	if (newSheet) {
	row = sheet.createRow(rowNo);
	idx = 0;
	for (String columnName : headers) {
	cell = row.createCell(idx++);
	cell.setCellStyle(headerStyle);
	cell.setCellValue(columnName);
	}
	}
	for (Map<String, Object> tempRow : list) {
	idx = 0;
	row = sheet.createRow(++rowNo);
	for (String key : keys) {
	if (StringUtils.isEmpty(key)) {
	continue;
	}
	cell = row.createCell(idx);
	if (ObjectUtils.isEmpty(aligns)) {
	// 디폴트 가운데 정렬
	cell.setCellStyle(bodyStyleCenter);
	} else {
	String hAlign = aligns.get(idx);
	if ("LEFT".equals(hAlign)) {
	cell.setCellStyle(bodyStyleLeft);
	} else if ("RIGHT".equals(hAlign)) {
	cell.setCellStyle(bodyStyleRight);
	} else {
	cell.setCellStyle(bodyStyleCenter);
	}
	}
	Object value = tempRow.get(key);
	if (value instanceof BigDecimal) {
	cell.setCellValue(((BigDecimal) value).toString());
	} else if (value instanceof Double) {
	cell.setCellValue(((Double) value).toString());
	} else if (value instanceof Long) {
	cell.setCellValue(((Long) value).toString());
	} else if (value instanceof Integer) {
	cell.setCellValue(((Integer) value).toString());
	} else {
	cell.setCellValue((String) value);
	}
	idx++;
	// 주기적인 flush 진행
	if (rowNo % 100 == 0) {
	((SXSSFSheet) sheet).flushRows(100);
	}
	}
	}
	return workbook;
	}
	private CellStyle createHeaderStyle(Workbook workbook) {
	CellStyle headerStyle = createBodyStyle(workbook);
	// 취향에 따라 설정 가능
	headerStyle.setFillForegroundColor(HSSFColorPredefined.LIGHT_YELLOW.getIndex());
	headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
	// 가로 세로 정렬 기준
	headerStyle.setAlignment(HorizontalAlignment.CENTER);
	headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
	return headerStyle;
	}
	private CellStyle createBodyStyle(Workbook 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.isEmpty(align) == false) {
	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