-
java excell downloader in spring bootTech/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