[Spring] POI 라이브러리를 이용한 Excel 생성

POI 라이브러리를 이용한 Excel 생성

 

 

마케팅팀, 운영팀에서 요청하는 데이터를 제공하기 위해 MySql Workbench의 데이터 내보내기 기능을 사용하고 있었는데, 매번 쿼리를 입력하고 엑셀로 추출하는 번거로움이 있기 때문에 이를 직접 원하는 기간 등을 입력하고 다운받을 수 있도록 API로 만들게 되었다.

 

 

1. 의존성

    compile('org.apache.poi:poi-ooxml:3.16') // .xlsx
    compile('org.apache.poi:poi:3.16') // .xls

 

Excel 파일을 생성하기 위해 POI 라이브러리를 추가합니다.

 

2. Code

2.1 ReportService.java

package com.kingpiggy.study.service;

import com.kingpiggy.study.domain.entity.Movie;
import com.kingpiggy.study.domain.repository.MovieRepository;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.time.LocalDate;
import java.util.List;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Slf4j
@RequiredArgsConstructor
@Service
public class ReportService {

    private final MovieRepository movieRepository;

    @Transactional(readOnly = true)
    public ByteArrayInputStream getMoviesToExcel(LocalDate startedAt, LocalDate endedAt) throws IOException {
        // 1. Get Movie Data
        List<Movie> movies = movieRepository.findAllByPeriod(startedAt, endedAt);

        // 2. Create File
        Workbook workbook = new XSSFWorkbook();
        ByteArrayOutputStream out = new ByteArrayOutputStream();

        // 3. Set Style
        Font headerFont = workbook.createFont();
        headerFont.setBold(true);
        headerFont.setColor(IndexedColors.BLACK.getIndex());

        CellStyle headerCellStyle = workbook.createCellStyle();
        headerCellStyle.setFont(headerFont);
        headerCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        headerCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        // 4. Create Sheets
        Sheet moviesSheet = workbook.createSheet("Movies");
        //moviesSheet.autoSizeColumn();

        // 5. Write Dashboard Sheet
        createDashboardSheet(movies, moviesSheet, headerCellStyle);

        // 6. Write File
        workbook.write(out);
        log.info("[ReportService:getMoviesToExcel] create movie report done. row count:[{}]", movies.size());
        return new ByteArrayInputStream(out.toByteArray());
    }

    public void createDashboardSheet(List<Movie> movies, Sheet moviesSheet, CellStyle headerCellStyle) {
        // 1. Create header row
        Row headerRow = moviesSheet.createRow(0);
        String[] headerStrings = {"Title", "Adult", "Overview", "Release Date"};

        int idx = 0;
        Cell headerCell = null;
        for (String s : headerStrings) {
            headerCell = headerRow.createCell(idx++);
            headerCell.setCellValue(s);
            headerCell.setCellStyle(headerCellStyle);
        }

        // 2. Create rows
        Row bodyRow = null;
        Cell bodyCell = null;
        int index = 1;
        for(Movie data : movies) {
            bodyRow = moviesSheet.createRow(index++);
            bodyCell = bodyRow.createCell(0);
            bodyCell.setCellValue(data.getTitle());
            bodyCell = bodyRow.createCell(1);
            bodyCell.setCellValue(data.getAdult());
            bodyCell = bodyRow.createCell(2);
            bodyCell.setCellValue(data.getOverview());
            bodyCell = bodyRow.createCell(3);
            bodyCell.setCellValue(data.getReleaseDate().toString());
        }

        // 3. Set Column Width
        for (int i=0; i<headerStrings.length; i++){
            moviesSheet.autoSizeColumn(i);
            moviesSheet.setColumnWidth(i, (moviesSheet.getColumnWidth(i))+(short)1024);
        }
    }

}

 

 

 

2.2 ReportApiController.java

package com.kingpiggy.study.web.controller.api;

import com.kingpiggy.study.service.ReportService;
import java.io.ByteArrayInputStream;
import java.time.LocalDate;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.springframework.core.io.InputStreamResource;
import org.springframework.format.annotation.DateTimeFormat;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

@Slf4j
@RequiredArgsConstructor
@RestController
public class ReportApiController {

    private final ReportService reportService;

    @GetMapping("/api/movies/report")
    public ResponseEntity<InputStreamResource> getMoviesToExcel(@RequestParam @DateTimeFormat(pattern = "yyyy-MM-dd") LocalDate startedAt,
        @RequestParam @DateTimeFormat(pattern = "yyyy-MM-dd") LocalDate endedAt) {
        try {
            ByteArrayInputStream result = reportService.getMoviesToExcel(startedAt, endedAt);

            String fileName = "Movies_" + startedAt.toString() + "-" + endedAt.toString() + ".xlsx";
            HttpHeaders headers = new HttpHeaders();
            headers.add("Content-Type", "application/vnd.ms-excel");
            headers.add("Content-Disposition", "attachment; filename=" + fileName);

            return ResponseEntity
                .ok()
                .headers(headers)
                .body(new InputStreamResource(result));
        } catch (Exception e) {
            log.info(e.getMessage());
        }

        // return for exception
        return new ResponseEntity<>(HttpStatus.BAD_REQUEST);
    }
}

 

 

 

3. 산출물 확인

 

Movies_2021-01-01-2021-12-31.xlsx

0.00MB