java excell downloader in spring boot

1. dependency

// xls 엑셀 파일 읽기 쓰기
implementation 'org.apache.poi:poi:4.1.2'
// xlsx 엑셀 파일 읽기 쓰기
implementation 'org.apache.poi:poi-ooxml:4.1.2'

 

2. excell writer

//column enum
@Getter
@AllArgsConstructor
public enum ReconcileColumnType {
  PAYMENT_DATE("결제 일자", 0),
  CANCEL_DATE("취소 일자", 1),
  PG_REQUEST_KEY("결제 요청 번호", 2),
  USER_ID("사용자 아이디", 3),
  PAYMENT_STATUS("결제 상태", 4),
  PAYMENT_AMOUNT("결제(취소) 금액", 5),
  ;

  private String colName;
  private int colIndex;
}


public ByteArrayInputStream convertToExcelFile(String sheetName,
      List<TargetDto> reconcileDtoList) throws IOException {

    try (
        Workbook workbook = new XSSFWorkbook();
        ByteArrayOutputStream out = new ByteArrayOutputStream();
    ) {
      CreationHelper createHelper = workbook.getCreationHelper();
      Sheet sheet = workbook.createSheet(sheetName);

      Font headerFont = workbook.createFont();
      headerFont.setBold(true);
      headerFont.setColor(IndexedColors.BLUE.getIndex());

      CellStyle headerCellStyle = workbook.createCellStyle();
      headerCellStyle.setFont(headerFont);

      // Row for Header
      Row headerRow = sheet.createRow(0);

      // Header
      Arrays.stream(ReconcileColumnType.values()).forEach(c -> {
        Cell cell = headerRow.createCell(c.getColIndex());
        cell.setCellValue(c.getColName());
        cell.setCellStyle(headerCellStyle);
      });

      // CellStyle for Amount
      CellStyle paymentAmountCellStyle = workbook.createCellStyle();
      paymentAmountCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("#"));

      //set data
      int rowIdx = 1;
      for (TargetDto rec : TargetDtoList) {
        Row row = sheet.createRow(rowIdx++);
        row.createCell(ReconcileColumnType.PAYMENT_DATE.getColIndex())
            .setCellValue(rec.getPaymentDate());
        row.createCell(ReconcileColumnType.CANCEL_DATE.getColIndex())
            .setCellValue(rec.getCancelDate());
        row.createCell(ReconcileColumnType.PG_REQUEST_KEY.getColIndex())
            .setCellValue(rec.getPgRequestKey());
        row.createCell(ReconcileColumnType.USER_ID.getColIndex()).setCellValue(rec.getUserId());
        row.createCell(ReconcileColumnType.PAYMENT_STATUS.getColIndex())
            .setCellValue(rec.getPaymentStatus());
        Cell paymentAmountCell = row.createCell(ReconcileColumnType.PAYMENT_AMOUNT.getColIndex());
        paymentAmountCell.setCellValue(rec.getPaymentAmount());
        paymentAmountCell.setCellStyle(paymentAmountCellStyle);
      }

      workbook.write(out);
      return new ByteArrayInputStream(out.toByteArray());
    }
  }

 

3. controller

@GetMapping("/excell_download")
  public ResponseEntity<InputStreamResource> excellDownload() throws Exception {

    ByteArrayInputStream in = convertToExcelFile("sample", list);
    HttpHeaders headers = new HttpHeaders();
    headers.add("Content-Disposition", "attachment; filename=sample.xlsx");

    return ResponseEntity
        .ok()
        .headers(headers)
        .body(new InputStreamResource(in));
  }