ABOUT ME

-

Today
-
Yesterday
-
Total
-
  • java excell downloader in spring boot
    Tech/SpringBoot 2021. 10. 31. 23:26
    반응형

    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));
      }
    반응형

    'Tech > SpringBoot' 카테고리의 다른 글

    Spring mvc async rest api  (0) 2021.11.07
    Spring mvc async rest api  (0) 2021.11.07
    Java conver t JsonArray to List  (0) 2021.10.31
    LocalDateTime hour step  (0) 2021.10.31
    ModelMapper 를 사용하여 객체 컨버팅 하기  (0) 2021.05.23
Designed by Tistory.