SXSSF 대용량 엑셀 다운로드 공통화

엑셀을 추출할 해당 java entity에  @ExcelColumn이라는 커스텀 annotation을 붙인 값만 엑셀 다운로드가 되도록 구현하였다.

@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelColumn {
    String headerName() default "";
}

 

 @ExcelColumn(headerName = "bbsUid")
private Long bbsUid;   

엑셀 공통화 작업을 위해 자바 entity와 해당 contents list객체만 넘겨주면 처리할 수 있게 구현하였다. 자바 reflaction을 통해서 추출해온다.

@Service
public class ExcelRender {
    private SXSSFWorkbook wb;
    private Row row;
    private Cell cell;
    private Class<?> clazz;
    private Sheet sheet;
    private Map<String, XSSFCellStyle> cellStyles;
    @Setter
    private int maxRowCanBeRendered = 50000;
    private int ROW_START_INDEX = 0;
    private List<Integer> nums;
    @Autowired
    private DefaultExcelCellStyle defaultExcelCellStyle; // cellStyle

    /*
     * excel 생성
     * */
    public void setExcelRender(Class<?> clazz) {
        log.info("=======================START===========================");
        wb = new SXSSFWorkbook();
        wb.setCompressTempFiles(true); // 압축여부
        this.clazz = clazz; // @ExcelColumn이 가지고 있는 class
        cellStyles = defaultExcelCellStyle.setWb(wb); // [header, body, amount]style Map으로 받아옴
        nums = new ArrayList<>();
    }


    /*
     * excel 데이터 그리기
     */
    public void renderExcel(List<?> list) throws IOException {
        // list가 없으면 header만 sheet와 header만 그리고 반환
        if (list.isEmpty()) {
            createNewSheetWithHeader();
            return;
        }
        // sheet와 header 생성
        createNewSheetWithHeader();
        // 랜더링 된 데이터 cnt
        int renderedDataCnt = 0;
        // body에 cell 위치
        int rowIndex = ROW_START_INDEX + 1;
        // for문 돌아가면서 contents를 채움
        for (Object renderedData : list) {
            renderContent(renderedData, rowIndex++);
            renderedDataCnt++;
            // 시트당 최대값에 도달하게 되면 새로운 sheet 생성
            if (renderedDataCnt == maxRowCanBeRendered + 1) {
                log.info("rendering");
                // 초기화
                renderedDataCnt = 0;
                rowIndex = 1;
                // 새로운 sheet
                createNewSheetWithHeader();
            }
        }
        // sheet 캐쉬 비우기
        ((SXSSFSheet) sheet).flushRows(list.size());
        // list clear
        list.clear();
    }

    /*
      SXSSFWorkbook 쓰기(엑셀 다운로드)
     */
    public void writeWorkbook(HttpServletResponse res) throws IOException {
        OutputStream target = res.getOutputStream();
        wb.write(target);
        target.close();
        // 임시파일 삭제
        wb.dispose();
    }

    /* sheet 생성 및 header 만들어주는 메소드 호출 */
    public void createNewSheetWithHeader() {
        sheet = wb.createSheet();
        renderHeadersWithNewSheet(sheet, ROW_START_INDEX);

    }

    /* 헤더 생성 */
    protected void renderHeadersWithNewSheet(Sheet sheet, int rowIndex) {
        Row row = sheet.createRow(rowIndex);
        AtomicInteger counter = new AtomicInteger(0);
        this.nums.clear();
        Arrays.stream(clazz.getDeclaredFields())
                .filter(f -> f.isAnnotationPresent(ExcelColumn.class))
                .forEachOrdered(f -> {
                    ExcelColumn ec = f.getAnnotation(ExcelColumn.class);
                    cell = row.createCell(counter.getAndIncrement());
                    cell.setCellValue(ec.headerName());
                    cell.setCellStyle(cellStyles.get("headerStyle"));
                    this.nums.add(ec.headerName().length());
                });
    }

    protected void renderContent(Object data, int rowIndex) {
        row = sheet.createRow(rowIndex);
        AtomicInteger counter = new AtomicInteger(0);
        Arrays.stream(clazz.getDeclaredFields())
                .filter(field -> field.isAnnotationPresent(ExcelColumn.class))
                .forEachOrdered(field -> {
                    cell = row.createCell(counter.get());
                    Object value = this.getValue(data, field);
                    this.maxColumnWidth(counter.get(), value);
                    counter.getAndIncrement();
                });
       this.updateColumnWidths();
    }
    private String getMethodName(Field field) {
        String methodName = field.getName();
        return "get" + methodName.substring(0, 1).toUpperCase() + methodName.substring(1);
    }
    // obj value
    private Object getValue(Object data, Field field) {
        try {
            String methodName = getMethodName(field);
            MethodHandle methodHandle = MethodHandles.lookup()
                    .findVirtual(data.getClass(),methodName, MethodType.methodType(field.getType()));
            this.applyCellStyle(cell, methodHandle.invoke(data), methodName);
            return methodHandle.invoke(data);
        } catch (Throwable e) {
            throw new RuntimeException(e);
        }

    }

    // cellStyle 적용
    private void applyCellStyle(Cell cell, Object value, String column) {
        if (value instanceof Long && !column.toUpperCase().contains("UID")) {
            cell.setCellStyle(cellStyles.get("amountStyle"));
            cell.setCellValue(value == null ? 0 : Long.parseLong(value.toString()));
        } else {
            cell.setCellStyle(cellStyles.get("bodyStyle"));
            cell.setCellValue(value == null ? "" : value.toString());
        }
    }
    // 오토사이징
    private void updateColumnWidths() {
        IntStream.range(0, nums.size())
                .forEach(i -> sheet.setColumnWidth(i, (nums.get(i) * 256) + 1048));
    }

    // length 최대값 구하기
    private void maxColumnWidth(int column, Object value) {
        int length = value == null ? 0 : value.toString().length();
        nums.set(column, Math.max(nums.get(column), length));
    }

poi에서 제공하는 autosizing같은 경우 성능 면에서 매우 느려서 컬럼에 최대 문자열을 구해서 컬럼 width를 조절해줬다.

 

controller

@GetMapping(value= "/joinServicelistExcel.json", produces = "application/vnd.ms-excel")
public void joinServicelistExcel(CetusBoardEx cetusBoardEx, HttpServletResponse res) throws IOException {

   // cntPerPage : 1개의 excel-sheet에 들어갈 data 갯수
   cetusBoardEx.CetusPaging(service.joinServicelistExcelCount(cetusBoardEx), 1, 100000);

   render.setExcelRender(CetusBoardEx.class);
   render.setMaxRowCanBeRendered(cetusBoardEx.getCntPerPage());

   // 가져올 데이터 갯수보다, cntPerPage가 작을 경우 한번에 전체 데이터 가져오기
   if(cetusBoardEx.getTotal() < cetusBoardEx.getCntPerPage()){
      render.renderExcel(service.joinServicelistExcel(cetusBoardEx));
   }else{
      for(int i=0; i < cetusBoardEx.getLastPage(); i++){
         cetusBoardEx.setNum(i);
         render.renderExcel(service.joinServicelistExcel(cetusBoardEx));
      }
   }
   // render.autoSizeColumns();
   render.writeWorkbook(res);
}

컨트롤러에서는 페이징 후 renderExcel을 호출해서 나눠서 처리하고 write 해준다

 

 

 

javascript

 

if (getExcel) {
  this.excelLoading = true;
  param = param + bbsUidListStr
  GetBoardData.excel(param)
      .then(res => {
        const url = window.URL.createObjectURL(new Blob([res.data], {type: res.headers['content-type']}));
        const link = document.createElement('a');
        link.href = url;
        link.setAttribute('download', '게시글_리스트.xlsx');
        document.body.appendChild(link);
        link.click();
        console.timeEnd('code_measure');
      })
      .then(() => this.excelLoading = false)
}

이런식으로 호출하면 excel down 완료

 

100만 건까지 테스트 완료