해당 게시글은 개인 프로젝트인 "광고 관리 플랫폼 대행사 센터 제작" 중
#129 "보고서 파일 다운로드 기능 구현" 이슈를 다루고 있습니다.
// Apachi poi
implementation 'org.apache.poi:poi:5.2.2' // .xls
implementation 'org.apache.poi:poi-ooxml:5.2.2' // .xlsx
public enum ReportType {
PERFORMANCE("실적 보고서"),
CREATIVE("소재 보고서"),
CAMPAIGN("캠페인 보고서"),
CLIENT("광고주 보고서");
@Getter
private final String description;
ReportType(String description) {
this.description = description;
}
}
@GetMapping("/report")
public ResponseEntity campaignReport(
@PathVariable("clientId") String clientId,
@RequestParam(required = false) @DateTimeFormat(iso = DateTimeFormat.ISO.DATE) LocalDate startDate,
@RequestParam(required = false) @DateTimeFormat(iso = DateTimeFormat.ISO.DATE) LocalDate lastDate,
HttpServletResponse response
) {
return ResponseEntity.ok(reportService.getPerformanceStatistics(response, clientId, startDate, lastDate, ReportType.CAMPAIGN));
}
// 실적 보고서, 소재 실적 보고서
@Transactional(readOnly = true)
public Object getPerformanceStatistics(HttpServletResponse response, Long id, LocalDate startDate, LocalDate lastDate, ReportType reportType) {
LocalDate defaultLastDate = LocalDate.parse(LocalDate.now().minusDays(1)
.format(DateTimeFormatter.ofPattern("yyyy-MM-dd")));
LocalDate startDateBeforeSevenDays = defaultLastDate.minusDays(6);
LocalDate startDateBeforeThirtyDays = defaultLastDate.minusDays(30);
if (lastDate == null) {
lastDate = defaultLastDate;
}
if (startDate == null) {
startDate = startDateBeforeThirtyDays;
}
if (reportType == ReportType.PERFORMANCE) {
List<PerformanceStatisticsDto> performanceList = statisticsQueryRepository.findByCreative_IdAndStatisticsDefault(id, startDate, lastDate);
createPerformanceExcelReportResponse(response, performanceList, reportType);
return null;
}
if (reportType == ReportType.CREATIVE) {
List<PerformanceStatisticsDto> performanceList = statisticsQueryRepository.findByCampaign_IdAndStatisticsDefault(id, startDate, lastDate);
createPerformanceExcelReportResponse(response, performanceList, reportType);
return null;
}
return null;
}
CellStyle numberCellStyle = workbook.createCellStyle();
numberCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0"));
CellStyle percentCellStyle = workbook.createCellStyle();
percentCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));
CellStyle dateCellStyle = workbook.createCellStyle();
dateCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));
// 실적 보고서 파일 생성
private void createPerformanceExcelReportResponse(HttpServletResponse response, List<PerformanceStatisticsDto> performanceList, ReportType reportType) {
try {
Workbook workbook = new SXSSFWorkbook();
Sheet sheet = workbook.createSheet(setFileName(reportType) + "_실적_보고서"); // 동적
// 숫자 포맷 적용
CellStyle numberCellStyle = workbook.createCellStyle();
numberCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0"));
CellStyle percentCellStyle = workbook.createCellStyle();
percentCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));
CellStyle dateCellStyle = workbook.createCellStyle();
dateCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));
LocalDate reportDate = LocalDate.parse(LocalDate.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd")));
final String fileName = setFileName(reportType) + "_실적_보고서_" + reportDate; // 동적
....
for (int i = 0; i < performanceList.size(); i++) {
row = sheet.createRow(i + 1);
PerformanceStatisticsDto performance = performanceList.get(i);
Cell cell = null;
cell = row.createCell(0); // 기간 동적
setDate(cell, performance);
cell = row.createCell(1); // 동적 (광고주 / 캠페인 / 소재 ID)
setIdValue(cell, performance, reportType);
cell = row.createCell(2); // 동적 (광고주명 / 캠페인명 / 소재 키워드)
setNameValue(cell, performance, reportType);
cell = row.createCell(3); // 동적 (광고주 업종 / 예산 / 입찰가)
cell.setCellStyle(numberCellStyle);
setSubValue(cell, performance, reportType);
....
private String setFileName(ReportType reportType) {
return switch (reportType) {
case PERFORMANCE -> "상세";
case CREATIVE -> "소재";
case CAMPAIGN -> "캠페인";
case CLIENT -> "광고주";
};
}
private void setIdValue(Cell cell, PerformanceStatisticsDto psd, ReportType reportType) {
switch (reportType) {
case PERFORMANCE -> cell.setCellValue(psd.getCreativeId());
case CREATIVE -> cell.setCellValue(psd.getCreativeId());
case CAMPAIGN -> cell.setCellValue(psd.getCampaignId());
case CLIENT -> cell.setCellValue(psd.getClientId());
}
;
}
private void setNameValue(Cell cell, PerformanceStatisticsDto psd, ReportType reportType) {
switch (reportType) {
case PERFORMANCE -> cell.setCellValue(psd.getKeyword());
case CREATIVE -> cell.setCellValue(psd.getKeyword());
case CAMPAIGN -> cell.setCellValue(psd.getName());
case CLIENT -> cell.setCellValue(psd.getUsername());
}
;
}
private void setSubValue(Cell cell, PerformanceStatisticsDto psd, ReportType reportType) {
switch (reportType) {
case PERFORMANCE -> cell.setCellValue(psd.getBidingPrice());
case CREATIVE -> cell.setCellValue(psd.getBidingPrice());
case CAMPAIGN -> cell.setCellValue(psd.getBudget());
case CLIENT -> cell.setCellValue(psd.getCategory());
}
;
}