초간단 SpringBoot DB데이터 Excel 다운로드

DB의 값을 불러와 해당 값드을 Excel다운로드 하는 코드를 작성해보겠다.

 

 

Entity - 각자의 데이터에 맞도록 설정

package com.example.exceltest.entity;

import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import lombok.Getter;

@Getter
@Entity
public class Employees {

    // Getters and Setters
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;
    private String position;

    public void setId(Long id) {
        this.id = id;
    }

    public void setName(String name) {
        this.name = name;
    }

    public void setPosition(String position) {
        this.position = position;
    }
}

---

Repository

package com.example.exceltest.repository;

import com.example.exceltest.entity.Employees;
import org.springframework.data.jpa.repository.JpaRepository;

public interface EmployeeRepository extends JpaRepository<Employees, Long> {
    // 기본적인 CRUD 메소드 제공
}

---

Service

package com.example.exceltest.service;

import com.example.exceltest.entity.Employees;
import com.example.exceltest.repository.EmployeeRepository;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class EmployeeService {

    private final EmployeeRepository employeeRepository;

    public EmployeeService(EmployeeRepository employeeRepository) {
        this.employeeRepository = employeeRepository;
    }

    public List<Employees> getAllEmployees() {
        return employeeRepository.findAll();
    }
}

---

Controller

package com.example.exceltest.controller;

import com.example.exceltest.entity.Employees;
import com.example.exceltest.service.EmployeeService;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.List;

@Controller
public class ExcelDownloadController {

    private final EmployeeService employeeService;

    public ExcelDownloadController(EmployeeService employeeService) {
        this.employeeService = employeeService;
    }

    @GetMapping("/api/excel/download")
    public ResponseEntity<byte[]> downloadExcel() throws IOException {
        // 데이터베이스에서 employee 데이터 가져오기
        List<Employees> employees = employeeService.getAllEmployees();

        // 엑셀 파일 생성
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("Employees");

        // 헤더 행 생성
        Row headerRow = sheet.createRow(0);
        headerRow.createCell(0).setCellValue("ID");
        headerRow.createCell(1).setCellValue("Name");
        headerRow.createCell(2).setCellValue("Position");

        // 데이터 행 생성
        int rowNum = 1;
        for (Employees employee : employees) {
            Row dataRow = sheet.createRow(rowNum++);
            dataRow.createCell(0).setCellValue(employee.getId());
            dataRow.createCell(1).setCellValue(employee.getName());
            dataRow.createCell(2).setCellValue(employee.getPosition());
        }

        // 열 너비 자동 조정
        sheet.autoSizeColumn(0);
        sheet.autoSizeColumn(1);
        sheet.autoSizeColumn(2);

        // 엑셀 파일을 ByteArrayOutputStream에 작성
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        workbook.write(outputStream);
        workbook.close();

        // HTTP 응답 헤더 설정
        HttpHeaders headers = new HttpHeaders();
        headers.add("Content-Disposition", "attachment; filename=employees.xlsx");
        headers.add("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

        return new ResponseEntity<>(outputStream.toByteArray(), headers, HttpStatus.OK);
    }
}

 

 

끝!