[SXSSFWorkbook] 엑셀 드롭다운 셀 생성하는 방법

개요

기존에 Excel 생성 및 다운로드 관련 게시글을 여럿 공유했었습니다.

https://jaimemin.tistory.com/2069

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

https://jaimemin.tistory.com/1889

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

 

이번에는 특정 셀을 드롭다운 셀로 구현해달라는 요구사항을 받아 구현해봤습니다.

 

코드

기존 코드들은 모두 비슷하고 수정된 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 if (value instanceof String[]) {
	String[] options = (String[]) value;
	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) 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

 

* line 152 ~ 163 주목

* String[] 타입으로 넘길 경우 드롭다운 셀 생성하고 value는 String 배열의 첫 번째 인자로 설정

* 드롭다운 셀 같은 경우 monitorjbl님이 제공해주신 excel-streaming-reader로 재구성하는 방법이 없는 것 같아 XSSFWorkbook으로 파일을 열어 다운로드 해주는 방식으로 구현

-> row 개수가 많아질 경우 heap 메모리 초과 에러 발생 가능하므로 row 개수가 적은 경우 위와 같은 방식으로 적용하는 것이 중요

-> 참고 링크를 확인해보면 MS excel의 한계로 드롭다운 셀 개수가 많으면 파일 자체가 깨짐

-> 결론: row 개수가 작은 엑셀에 대해서만 해당 코드 참고

 

참고

https://stackoverflow.com/questions/56865788/poi-writes-corrupted-xlsx-when-adding-large-number-of-validationdata-in-a-sheet

POI writes corrupted .xlsx when adding large number of ValidationData in a sheet