spring boot restful api에서 대용량 엑셀 다운로드

java excel download를 검색하면 많이 나오는 방식으로 엑셀 다운로드를 개발 때 다음과 같은 경우에 메모리 문제(out of memory)가 발생할 수 있습니다.

• 대용량 엑셀 다운로드가 발생할 경우
• 여러 명이 동시에 엑셀 다운로드를 할 경우

이번 포스팅에서는 Restful API에서 엑셀 다운로드하는 방법을 알아보겠습니다. 

 

 

Out of Memory(OOM)의 원인

1. 페이징 처리

Out of Memory가 발생하는 가장 흔한 원인은 페이징 처리입니다.

보통 엑셀 다운로드는 페이징 처리 없이 모든 데이터를 엑셀로 만들어야 합니다. 모든 데이터를 보기 위해 엑셀 다운로드를 하는 것이죠. 그래서 엑셀 다운로드 기능 개발 시, 페이징 처리 없이 모든 데이터를 한 번에 가져와서 처리합니다. 구글에서 검색해서 나오는 많은 예제에서 페이징 처리가 없기 때문에 간과하게 되는 것입니다.

 

 

2. flush()

엑셀 다운로드를 개발할 때 주기적으로 flush()를 실행하여 메모리를 확보합니다.

 

 

 

spring boot restful api에서 대용량 엑셀 다운로드

 

1. 사전 작업

1.1. 테스트를 진행하기 위해 데이터베이스에 테이블을 생성한 후, 데이터를 미리 넣어놓습니다.

 

1.2. JPA entity와 repository를 생성

@Getter
@Setter
@Entity
public class User {
  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Long userId;
  private String userName;
  private String email;
}

import org.springframework.data.jpa.repository.JpaRepository;

public interface UserRepository extends JpaRepository<User, Long> {
}

 

 

1.3. 엑셀 column의 정보를 담기 위한 DTO 생성

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
public class ExcelDto {
  private String columnId;
  private String columnName;
}

 

 

1.4. 엑셀 생성 개발

import com.example.demo.domain.User;
import com.example.demo.domain.UserRepository;
import com.example.demo.excel.ExcelDto;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;
import lombok.RequiredArgsConstructor;
import org.apache.poi.hssf.util.HSSFColor.HSSFColorPredefined;
import org.apache.poi.ss.usermodel.BorderStyle;
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.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.stereotype.Component;

@Component
@RequiredArgsConstructor
public class ExcelUtils {
  private static final int PAGING_SIZE = 3;
  private final UserRepository userRepository;

  public ByteArrayInputStream createListToExcel(String excelType) {
    long count = getCount(excelType);
    List<ExcelDto> excelDtoList = getExcelDtoList(excelType);

    try (SXSSFWorkbook workbook = new SXSSFWorkbook()) {
      SXSSFSheet sheet = workbook.createSheet();
      Row row;
      Cell cell;
      int rowNo = 0;

      int headerSize = excelDtoList.size();

      // 테이블 헤더 스타일 설정
      CellStyle headerStyle = workbook.createCellStyle();
      // 경계선 설정
      headerStyle.setBorderTop(BorderStyle.THIN);
      headerStyle.setBorderBottom(BorderStyle.THIN);
      headerStyle.setBorderLeft(BorderStyle.THIN);
      headerStyle.setBorderRight(BorderStyle.THIN);
      // 색상
      headerStyle.setFillForegroundColor(HSSFColorPredefined.YELLOW.getIndex());
      headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
      // 헤더 가운데 정렬
      headerStyle.setAlignment(HorizontalAlignment.CENTER);

      // 헤더 생성
      row = sheet.createRow(rowNo++);
      for (int i=0; i<headerSize; i++) {
        cell = row.createCell(i);
        cell.setCellStyle(headerStyle);
        cell.setCellValue(excelDtoList.get(i).getColumnName());
      }

      // 내용 생성
      for (int start = 0; start < count; start += PAGING_SIZE) {
        int page = start / PAGING_SIZE;
        List list = getList(excelType, page);

        for (Object o : list) {
          Row dataRow = sheet.createRow(rowNo++);

          for (int columnIndex = 0; columnIndex < excelDtoList.size(); columnIndex++) {
            cell = dataRow.createCell(columnIndex);
            Object value = getValue(o, excelDtoList.get(columnIndex).getColumnName());
            cell.setCellValue(String.valueOf(value == null ? "" : value));
          }

          // 메모리 효율을 위해 주기적으로 flush
          if (rowNo % 100 == 0) {
            sheet.flushRows(100);
          }
        }

        // list 초기화
        list = null;
      }

      // 컬럼의 넓이를 자동으로 설정
      sheet.trackAllColumnsForAutoSizing();
      for(int columnIndex=0; columnIndex<excelDtoList.size(); columnIndex++) {
        sheet.autoSizeColumn(columnIndex);
      }

      ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
      outputStream.flush();
      workbook.write(outputStream);
      outputStream.flush();
      return new ByteArrayInputStream(outputStream.toByteArray());

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

  /**
   * 엑셀 데이터에서 가져올 데이터의 column 정보를 가져온다.
   * @param excelType
   * @return
   */
  private List<ExcelDto> getExcelDtoList(String excelType) {
    List<ExcelDto> list = new ArrayList<>();

    if (excelType.equals("user")) {
      list.add(new ExcelDto("userId", "ID"));
      list.add(new ExcelDto("userName", "이름"));
      list.add(new ExcelDto("email", "이메일"));
    }

    return list;
  }

  /**
   * 엑셀에 실제로 넣을 데이터를 가져온다. 메모리 관리를 위해 페이징 처리 필수.
   * @param excelType 엑셀 타입
   * @param page 페이지 번호
   * @return
   */
  private List<?> getList(String excelType, int page) {
    if ("user".equals(excelType)) {
      Page<User> list = userRepository.findAll(PageRequest.of(page, PAGING_SIZE));
      return list.getContent();
    } else {
      return null;
    }
  }

  /**
   * database query를 하여 실제 count를 가져온다.
   * @param excelType
   * @return
   */
  private long getCount(String excelType) {
    if ("user".equals(excelType)) {
      // 테스트 코드이기 때문에 하드코딩
      return 10;
    } else {
      return 65536;
    }
  }

  /**
   * Class에서 특정 컬럼의 값을 가져온다.
   * @param obj
   * @param fieldName
   * @return
   */
  private Object getValue(Object obj, String fieldName) {
    try {
      Field field = obj.getClass().getDeclaredField(fieldName);
      field.setAccessible(true);
      return field.get(obj);
    } catch (NoSuchFieldException | IllegalAccessException e) {
      e.printStackTrace();
      return null;
    }
  }
}

 

 

1.5. Controller

package com.example.demo.excel;

import com.example.demo.utils.ExcelUtils;
import java.io.ByteArrayInputStream;
import java.io.IOException;
import javax.servlet.http.HttpServletResponse;
import lombok.RequiredArgsConstructor;
import org.apache.tomcat.util.http.fileupload.IOUtils;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
@RequiredArgsConstructor
public class ExcelController {
  private final ExcelUtils excelUtils;
  @GetMapping(value = "/download")
  public void excelDownload(HttpServletResponse response) throws IOException {
    String fileName = "testExcel1.xlsx";
    response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
    response.setContentType("application/octet-stream");

    ByteArrayInputStream stream = excelUtils.createListToExcel("user");
    IOUtils.copy(stream, response.getOutputStream());
  }
}

 

 

 

 

참고 사이트

https://jaimemin.tistory.com/1889

[SpringBoot] 대용량 엑셀 다운로드를 위한 SXSSFWorkbook

https://wildeveloperetrain.tistory.com/71

스프링부트 REST API Excel download 방법

https://yjh5369.tistory.com/entry/java-class%EC%9D%98-%EB%AA%A8%EB%93%A0-field-%EA%B0%80%EC%A0%B8%EC%98%A4%EA%B8%B0-private-%ED%8F%AC%ED%95%A8

[java] class의 모든 field 가져오기 (private 포함)