[SpringBoot] Excel 생성 및 다운로드

개요

ElasticSearch로 조회한 목록들을 엑셀로 다운로드할 수 있는 기능을 추가해달라는 요청이 들어와 구현을 해봤습니다.

 

2021.08.08

해당 코드는 OOM 예외를 야기할 수 있으므로 보다 나은 코드는 아래 게시글을 참고해주세요.

https://jaimemin.tistory.com/1889

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

 

구현 코드

ExcelController.java

	package com.tistory.jaimemin.core.order;
	import org.springframework.beans.factory.annotation.Autowired;
	import org.springframework.stereotype.Controller;
	import org.springframework.ui.Model;
	import java.io.UnsupportedEncodingException;
	import java.text.ParseException;
	@Controller
	public class ExcelController {
	private final ExcelService excelService;
	@Autowired
	public ExcelController(ExcelService excelService) {
	this.excelService = excelService;
	}
	@GetMapping(value = "/download/excel", produces = "application/vnd.ms-excel")
	public String downloadExcel(Model model
	, ExampleCriteria criteria) throws UnsupportedEncodingException, ParseException {
	model.addAttribute("excelMap", excelService.getExcel(criteria));
	return "excelView";
	}
	}

view raw .java hosted with ❤ by GitHub

 

ExampleService.java

	package com.tistory.jaimemin.core.order;
	import org.springframework.stereotype.Service;
	import java.text.ParseException;
	import java.util.*;
	@Service
	public class ExcelService {
	public Map<String, Object> getExcel(ExampleCriteria criteria) throws ParseException {
	// 엑셀에 저장할 내용이 있는 리스트를 불러옴
	List<ExampleDto> exampleList = ...;
	// 헤더 키 설정
	List<String> headerKeys = Arrays.asList("HEADER1", "HEADER2", "HEADER3", ...);
	// 헤더 명 설정
	List<String> headers = Arrays.asList("헤더명1", "헤더명2", "헤더명3", ...)
	List<Map<String, Object>> list = new ArrayList<>();
	// 헤더 키에 1:1 매칭
	for (ExampleDto example : exampleList) {
	Map<String, Object> tempMap = new HashMap<>();
	tempMap.put("HEADER1", example.get...);
	tempMap.put("HEADER2", example.get...);
	tempMap.put("HEADER3", example.get...);
	...
	}
	// 헤더당 셀 너비 설정
	List<String> widths = Arrays.asList("15", "20", "30", ...);
	/*
	* 셀 당 정렬 기준 설정
	* LEFT: 왼쪽 정렬
	* CENTER: 가운데 정렬
	* RIGHT: 오른쪽 정렬
	*/
	List<String> aligns = Arrays.asList("LEFT", "CENTER", "RIGHT", ...);
	// 파일명 설정
	String fileName = "EXAMPLE_EXCEL";
	Map<String, Object> excelMap = new HashMap<>();
	excelMap.put("headers", headers);
	excelMap.put("keys", headerKeys);
	excelMap.put("widths", widths);
	excelMap.put("aligns", aligns);
	excelMap.put("list", list);
	excelMap.put("fileName", fileName);
	return excelMap;
	}
	}

view raw .java hosted with ❤ by GitHub

 

ExcelView.java

	package com.tistory.jaimemin.core.order;
	import org.springframework.stereotype.Component;
	import org.springframework.util.ObjectUtils;
	import org.springframework.util.StringUtils;
	import org.springframework.web.servlet.view.document.AbstractXlsView;
	import org.apache.poi.ss.usermodel.Workbook;
	import org.apache.poi.xssf.streaming.SXSSFWorkbook;
	import org.apache.poi.hssf.util.HSSFColor.HSSFColorPredefined;
	import org.apache.poi.ss.usermodel.*;
	import org.apache.poi.ss.util.CellRangeAddress;
	import javax.servlet.http.HttpServletRequest;
	import javax.servlet.http.HttpServletResponse;
	import java.math.BigDecimal;
	import java.net.URLEncoder;
	import java.nio.charset.StandardCharsets;
	import java.util.List;
	import java.util.Map;
	@Component("excelView")
	public class ExcelView extends AbstractXlsView {
	@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<Map<String, Object>> list = (List<Map<String, Object>>) excelMap.get("list");
	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");
	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");
	}
	Workbook workbook = getWorkbook(fileName, headers, keys, widths, aligns, list);
	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();
	}
	private Workbook getWorkbook(String fileName
	, List<String> headers
	, List<String> keys
	, List<String> widths
	, List<String> aligns
	, List<Map<String, Object>> list) {
	Workbook workbook = new SXSSFWorkbook();
	Sheet sheet = workbook.createSheet("Sheet1");
	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);
	}
	idx = 0;
	int rowIdx = 0;
	Row row = sheet.createRow(rowIdx);
	Cell cell = null;
	for (String columnName : headers) {
	cell = row.createCell(idx++);
	cell.setCellStyle(headerStyle);
	cell.setCellValue(columnName);
	}
	for (Map<String, Object> tempRow : list) {
	idx = 0;
	rowIdx++;
	row = sheet.createRow(rowIdx);
	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++;
	}
	}
	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

 

* 주석으로도 작성했지만 셀 내 개행을 위해서는 개행문자 '\r\n'을 추가해줘야 하고 덧붙여 bodyStyle에 setWrapText(true)로 지정해줘야 합니다.

 

요청하는 클라이언트 사이드

	<!DOCTYPE html>
	<html lang="en">
	<head>
	<meta charset="UTF-8">
	<title>엑셀 다운로드 예시</title>
	</head>
	<body>
	<div>
	<button id="excel-download">엑셀 다운로드</button>
	</div>
	<script>
	$('#excel-download').click(function(e){
	excelDownload('/download/excel', '');
	$.alert('', '엑셀 다운로드 시작! 잠시만 기다려주세요.');
	})
	function excelDownload(url, fileName) {
	var params = ...; // 매개변수는 원하는대로
	params.fileName = fileName;
	var queryString = Object.keys(params).map(function(key){
	return `${key}=${params[key]}`;
	}).join('&');
	var excelDownloadUrl = `${url}?${queryString}&fileName=` + encodeURIComponent(fileName);
	if (navigator.userAgent.indexOf("MSIE") !== -1
	|| navigator.userAgent.indexOf("Trident") !== -1) {
	window.open(excelDownloadUrl, "excelWin", 'width=200, height=200, left=2000, top=2000');
	} else {
	$('#downloadFrame').remove();
	$('body').append('<ipframe id="downloadFrame" style="display:none"></ipframe>');
	$('#downloadFrame').get(0).contentWindow.location.href = excelDownloadUrl;
	}
	}
	</script>
	</body>
	</html>

view raw .html hosted with ❤ by GitHub

 

참고자료

https://stackoverflow.com/questions/48040638/how-to-insert-a-linebreak-as-the-data-of-a-cell

How to insert a linebreak as the data of a cell?