[Spring] POI 사용해서 엑셀 저장

환경: JDK 1.8, Spring Boot 2.7.10

build.gradle

implementation 'commons-io:commons-io:2.7'
implementation group: 'org.apache.poi', name: 'poi', version: '4.1.2'
implementation group: 'org.apache.poi', name: 'poi-ooxml', version: '4.1.2'

ExcelUtils.java

import com.fasterxml.jackson.databind.ObjectMapper;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.*;
import java.util.stream.Collectors;

@Slf4j
public class ExcelUtils {

    public static void makeExcelDataAndDownload(String[] column, List data, String fileName, HttpServletResponse response) throws IOException {
        makeExcelDataAndDownload(column, data, fileName, "sheet1", response);
    }

    public static void makeExcelDataAndDownload(String[] column, List data, String fileName, String sheetName, HttpServletResponse response) throws IOException {
        OutputStream outputStream = null;
        try {
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

            response.setHeader("Content-Disposition", String.format("Attachment; Filename=%s", fileName));

            outputStream = new BufferedOutputStream(response.getOutputStream());
            makeExcelData(column, data, sheetName).write(outputStream);
            outputStream.flush();
        } catch (Exception e) {
            throw e;
        } finally {
            if(outputStream != null) outputStream.close();
        }
    }

    public static XSSFWorkbook makeExcelData(String[] column, List data) throws IOException {
        return makeExcelData(column, data, "sheet1");
    }

    public static XSSFWorkbook makeExcelData(String[] column, List data, String sheetName) {
        List<Map<String, Object>> list = (List<Map<String, Object>>) data.stream().map((obj) -> new ObjectMapper().convertValue(obj, HashMap.class)).collect(Collectors.toList());

        XSSFWorkbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet(sheetName);
        Row row = null;
        Cell cell = null;

        CellStyle headerStyle = CellStyleSetting(workbook, "header");
        CellStyle dataStyle = CellStyleSetting(workbook, "data");

        row = sheet.createRow(0);
        for (int i = 0; i < column.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(column[i]);
            cell.setCellStyle(headerStyle);
        }

        for (int i = 0; i < data.size(); i++) {
            row = sheet.createRow(i + 1);
            Map<String, ?> dataRow = list.get(i);

            List keys = new ArrayList(dataRow.keySet());
            for(int j = 0; j < keys.size(); j++) {
                cell = row.createCell(j);

                String value = dataRow.get(keys.get(j)) != null ? dataRow.get(keys.get(j)).toString() : "";

                cell.setCellValue(value);
                cell.setCellStyle(dataStyle);
            }
        }

        for (int i=0; i<column.length; i++) {
            sheet.autoSizeColumn(i);
            sheet.setColumnWidth(i, sheet.getColumnWidth(i));
        }

        return workbook;
    }

    public static CellStyle CellStyleSetting (XSSFWorkbook workbook, String type){
        CellStyle cellStyle = workbook.createCellStyle();

        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);

        if(type.equals("header")) {
            cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        }

        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        Font fontOfGothic = workbook.createFont();
        fontOfGothic.setFontName("맑은 고딕");
        cellStyle.setFont(fontOfGothic);

        return cellStyle;
    }
}

Controller

@GetMapping(value = "/excelDown")
public void excelDown(HttpServletResponse response) throws Exception{
    List<Map<String, Object>> list = testService.getExcelTestList();

    String[] columns = {"company_name", "companyCode", "companyOwnerName", "companyLicense", "companyDomain", "companyPhone", "useYn", "customerYn", "createDate", "createId", "updateDate", "updateId"};

    ExcelUtils.makeExcelDataAndDownload(columns, list, "테스트.xlsx", response);
}