[SpringBoot] Apache Poi를 이용한 엑셀 다운로드 구현

목차

 

 

 

 

 

2022.12.03 내용 추가

아래 코드에서는 XSSFWorkbook을 사용하는데, 실제 운영서버에서 나는 Out Of Memory 에러를 경험했다.
편안한 주말을 보내고싶다면 꼭 반드시 아래 글을 읽어보고 SXSSFWorkbook을 사용하자.

https://lotuus.tistory.com/153

[SpringBoot] Apache Poi를 이용한 엑셀다운로드는 SXSSF를 쓰자..!

---

 

 

 

 

 

 

 

통계 API를 만들다보면

화면에도 값을 보여주어야하고, 엑셀 다운로드도 가능해야합니다 ㅎㅎ

 

대표적으로 사용되는 Apache Poi를 이용하여 엑셀 다운로드를 구현해보겠습니당!

 

 

예제

 

UserPoint Entity

@Getter
@Entity
@NoArgsConstructor(access = AccessLevel.PROTECTED)
public class UserPoint {

    @Id
    @GeneratedValue
    @Column(name = "id")
    private Long id;

    @Column(name = "user_code")
    private String userCode;   //유저 코드

    @Column(name = "user_name")
    private String userName;   //유저명

    @Column(name = "pay_cnt")
    private Integer payCnt;    //결제건수

    @Column(name = "pay_sum")
    private Long paySum;     //결제금액

}

 

 

 

StatsController

@RestController
@RequestMapping("/stats")
@RequiredArgsConstructor
public class StatsController {
    private final StatsService statsService;
    
    @GetMapping("/user/point")
    public ResponseEntity getUsersPointStats(HttpServletResponse response, boolean excelDownload){
        return ResponseEntity.ok(statsService.getUsersPointStats(response, excelDownload));
    }

}

 

 

 

StatsService

@Service
@Transactional
@RequiredArgsConstructor
public class StatsService {
    private final UserPointRepository userPointRepository;
    private final ObjectMapper objectMapper;

    public Object getUsersPointStats(HttpServletResponse response, boolean excelDownload) {

        List<UserPoint> userPointList = userPointRepository.findAll();

        List<Map> userPointMap = userPointList.stream()
                .map(userPoint -> objectMapper.convertValue(userPoint, Map.class))
                .collect(Collectors.toList());

        return userPointMap;
    }
}

 

 

 

API 응답

 

 

 

 

반응형

엑셀 다운로드 구현

 

build.gradle

//excel download : poi
implementation 'org.apache.poi:poi:5.2.2'                // .xls 확장자
implementation 'org.apache.poi:poi-ooxml:5.2.2'          // .xlsx 확장자

 

 

StatsService

import com.fasterxml.jackson.databind.ObjectMapper;
import lombok.RequiredArgsConstructor;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import javax.transaction.Transactional;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

@Service
@Transactional
@RequiredArgsConstructor
public class StatsService {
    private final UserPointRepository userPointRepository;
    private final ObjectMapper objectMapper;

    public Object getUsersPointStats(HttpServletResponse response, boolean excelDownload) {

        List<UserPoint> userPointList = userPointRepository.findAll();

        if(excelDownload){
            createExcelDownloadResponse(response, userPointList);
            return null; //없으면 에러!
        }

        List<Map> userPointMap = userPointList.stream()
                .map(userPoint -> objectMapper.convertValue(userPoint, Map.class))
                .collect(Collectors.toList());
        return userPointMap;
    }


    /**
     * 엑셀 다운로드 구현 🔥🔥🔥
     */ 
    private void createExcelDownloadResponse(HttpServletResponse response, List<UserPoint> userPointList) {

        try{
            Workbook workbook = new XSSFWorkbook();
            Sheet sheet = workbook.createSheet("사용자 포인트 통계");

            //숫자 포맷은 아래 numberCellStyle을 적용시킬 것이다다
            CellStyle numberCellStyle = workbook.createCellStyle();
            numberCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0"));

            //파일명
            final String fileName = "사용자 포인트 통계";

            //헤더
            final String[] header = {"번호", "유저코드", "유저명", "결제건수", "결제금액"};
            Row row = sheet.createRow(0);
            for (int i = 0; i < header.length; i++) {
                Cell cell = row.createCell(i);
                cell.setCellValue(header[i]);
            }

            //바디
            for (int i = 0; i < userPointList.size(); i++) {
                row = sheet.createRow(i + 1);  //헤더 이후로 데이터가 출력되어야하니 +1

                UserPoint userPoint = userPointList.get(i);

                Cell cell = null;
                cell = row.createCell(0);
                cell.setCellValue(userPoint.getId());

                cell = row.createCell(1);
                cell.setCellValue(userPoint.getUserCode());

                cell = row.createCell(2);
                cell.setCellValue(userPoint.getUserName());

                cell = row.createCell(3);
                cell.setCellValue(userPoint.getPayCnt());

                cell = row.createCell(4);
                cell.setCellStyle(numberCellStyle);      //숫자포맷 적용
                cell.setCellValue(userPoint.getPaySum());
            }


            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename="+ URLEncoder.encode(fileName, "UTF-8")+".xlsx");
            //파일명은 URLEncoder로 감싸주는게 좋다!

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

        }catch(IOException e){
            e.printStackTrace();
        }

    }
}

 

 

 

 

API 응답

 

excelDownload = false일때는 json 반환

 

 

 

excelDownload = true일때는 엑셀 다운로드

결제건수, 결제금액을 보면

별다른 처리 없이도 문자열이 아닌 숫자로 처리되어서 바로 연산이 가능하다

( setCellValue(userPoint.getPayCnt().toString()) 으로 처리해서 엑셀 다운로드해보면 숫자연산이 불가하다 )

 

또한 결제금액에 적용한 숫자포맷도 잘 적용되었다!

 

 

 

 

 

프론트쪽에서는 어떻게 호출하지?

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>excel</title>
</head>
<body>

<a href="http://localhost:8080/stats/user/point?excelDownload=true">포인트정보 엑셀다운로드</a>

</body>
</html>

a태그 href 속성으로 요청을 보내주면 다운로드 가능하다!