# Spring Boot - POI로 엑셀 다운로드 기능 만들기 (maven)

1.pom.xml에 dependency 추가

<!-- # excel 다운로드 -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>5.2.3</version>
</dependency>

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.2.3</version>
</dependency>


<dependency>
    <groupId>org.apache.commons</groupId>
    <artifactId>commons-collections4</artifactId>
    <version>4.4</version>
</dependency>

 

2.DTO 만들기

// StudentGradeDTO.java
package com.example.demo.student.dto;

import lombok.Data;

@Data
public class StudentGradeDTO {
    private String name;
    private String subject;
    private double score;
}

 

3.공통 Excel Export 유틸 만들기

// ExcelExporter.java
package com.example.demo.global.excel;

import jakarta.servlet.http.HttpServletResponse;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;
import java.util.function.Function;

public class ExcelExporter {

    public static <T> void exportListToExcel(List<T> dataList, String[] headers,
                                             Function<T, List<String>> rowMapper,
                                             String sheetName, String fileName,
                                             HttpServletResponse response) throws IOException {

        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet(sheetName);

        Row headerRow = sheet.createRow(0);
        for (int i = 0; i < headers.length; i++) {
            headerRow.createCell(i).setCellValue(headers[i]);
        }

        int rowNum = 1;
        for (T item : dataList) {
            Row row = sheet.createRow(rowNum++);
            List<String> cellValues = rowMapper.apply(item);
            for (int i = 0; i < cellValues.size(); i++) {
                row.createCell(i).setCellValue(cellValues.get(i));
            }
        }

        String encodedFileName = URLEncoder.encode(fileName, "UTF-8");
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setHeader("Content-Disposition", "attachment; filename=\"" + encodedFileName + "\"");

        workbook.write(response.getOutputStream());
        workbook.close();
    }
}

 

4.Controller - 엑셀 다운로드 엔드포인트

// StudentController.java
package com.example.demo.student.controller;

import com.example.demo.global.excel.ExcelExporter;
import com.example.demo.student.dto.StudentGradeDTO;
import jakarta.servlet.http.HttpServletResponse;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.io.IOException;
import java.time.LocalDate;
import java.util.List;

@RestController
public class StudentController {

    @GetMapping("/api/students/excel")
    public void downloadExcel(HttpServletResponse response) throws IOException {
        List<StudentGradeDTO> list = List.of(
                create("김영희", "국어", 88.5),
                create("이철수", "수학", 92.0),
                create("홍길동", "영어", 76.5)
        );

        String[] headers = {"이름", "과목", "점수"};

        ExcelExporter.exportListToExcel(
                list,
                headers,
                dto -> List.of(
                        dto.getName(),
                        dto.getSubject(),
                        String.valueOf(dto.getScore())
                ),
                "학생 성적표",
                "성적표_" + LocalDate.now() + ".xlsx",
                response
        );
    }

    private StudentGradeDTO create(String name, String subject, double score) {
        StudentGradeDTO dto = new StudentGradeDTO();
        dto.setName(name);
        dto.setSubject(subject);
        dto.setScore(score);
        return dto;
    }
}

 

엑셀 내용

Sheet1: 학생 성적표

| 이름   | 과목 | 점수  |
|--------|------|--------|
| 김영희 | 국어 | 88.5   |
| 이철수 | 수학 | 92.0   |
| 홍길동 | 영어 | 76.5   |