Spring Boot로 Excel을 다룰 일이 생겨, 작업했던 내용을 정리해보려고 한다.
Spring Boot + Excel
엑셀을 활용한 기능은 크게 두 가지로 나누어 볼 수 있다.
- 엑셀 파일의 데이터를 서버에서 활용 가능한 데이터로 변환하기
- 서버 데이터를 엑셀 형식으로 변환하여 반환하기
Dependency
아래와 같이 의존성을 추가하면 된다.
// Excel
implementation 'org.apache.poi:poi-ooxml:5.2.5'
implementation 'com.opencsv:opencsv:5.9'
Maven Repository에서 Apache POI를 확인해보면, 현재 5.x 버전이 모두 취약점이 존재한다는 것을 알 수 있다. 관련 취약점들은 모두 24년도에 발견된 것이고, 2종류는 인증 로직에 관련한 문제, 1종류는 인증서 관련 CPU 소비 문제, 나머지 2종류는 Apache Commons Compress 관련 문제다. 최신 버전이 23년 11월이므로, 24년도에 발생한 취약점이 해결된 버전이 나올 때까지는 해당 취약점에 대해 인지한 상태로 사용하면 될 것 같다.
Maven Repository에서는 Vulnerabilities를 확인할 수 없지만, IntelliJ에서 OpenCSV 또한 취약점이 있다고 한다. 내용을 보면 제어할 수 없는 재귀가 발생할 수 있다고 하는데, 인지하고 사용하면 될 것 같다.
엑셀 파일로 데이터 등록하기
엑셀 파일을 multipart/form-data형식의 API로 받아 데이터베이스 설계에 맞추어 변환해서 저장하는 로직이다.
application.yml
servlet:
multipart:
enabled: true # 멀티파트 업로드 지원여부 (default: true)
file-size-threshold: 0B # 파일을 디스크에 저장하지 않고 메모리에 저장하는 최소 크기 (default: 0B)
max-file-size: 100MB # 한개 파일의 최대 사이즈 (default: 1MB)
max-request-size: 100MB # 한개 요청의 최대 사이즈 (default: 10MB)첫 번째로는 application.yml을 설정해주어야 한다.
설정해야 하는 이유는 서블릿 컨테이너의 동작 과정을 보면 알 수 있다.
Tomcat과 같은 서블릿 컨테이너에서 multipart/form-data 형식의 요청을 받아, 해당 데이터를 임시 또는 지정된 위치에 저장하게 된다. 이 때, 서블릿 컨테이너는 max-file-size 또는 max-request-size 설정을 활용하여 파일 크기와 전체 요청 크기를 제한한다.
서블릿 컨테이너는 추출된 데이터를 포함하는 요청 객체를 생성한다.
이 요청 객체는 Spring MVC의 컨트롤러 메서드에 전달된다.
컨트롤러는 @RequestPart 애노테이션을 사용하여 업로드된 파일과 폼 필드 데이터를 처리할 수 있다.컨트롤러에서 업로드된 데이터에 대해 서비스 계층 등으로 넘겨 비즈니스 로직을 진행한다.
처리가 완료되면 서블릿 컨테이너는 임시 파일이나 메모리에 저장된 데이터를 정리한다.
Controller
@PostMapping("/register/excel")
@Operation(summary = "고객 정보 엑셀로 등록", description = "사용자의 정보 입력 후 회원가입에 따른 성공/실패 여부 반환")
@ApiResponses(
value = {
@ApiResponse(responseCode = "1000", description = "성공 시 반환되는 코드", content = {@Content(schema = @Schema(implementation = ApplicationResponse.class))}),
@ApiResponse(responseCode = "2000", description = "입력 값이 유효하지 않거나, 서버 내부에서 오류가 발생했을 때 반환하는 에러", content = {@Content(schema = @Schema(implementation = ExceptionResponse.class))}),
})
public ResponseEntity<?> registerCustomerExcel(@RequestPart(name = "file") MultipartFile file, @RequestParam(name = "level") String level) {
customerFacade.registerCustomerExcel(file, level);
return ResponseEntity.ok()
.body(ApplicationResponse.ok());
}파일은 MultipartFile 자료형으로 받아서 처리하면 된다.
Facade
public void registerCustomerExcel(MultipartFile file, String level) {
List<ExcelDto> excelDtoList = excelClient.extractExcel(file);
customerService.registerCustomerExcel(excelDtoList, level);
}현재 Facade패턴으로 개발을 진행하고 있기에, 비즈니스 로직을 처리하는 Service 계층의 메소드를 Facade에서 묶어서 처리한다.
이는 Facade패턴이 아니라면, Service 계층에서 바로 Excel을 처리하는 Component에 대한 의존성을 주입해서 개발해도 무방하다.
Component
/**
* 파일의 확장자가 엑셀에 맞는 .csv / .xlsx 인지 확인하는 메소드
* @param file 파일
* @return 파일이 엑셀 확장자일 경우 true, 그 외는 모두 false
*/
private String checkExtension(MultipartFile file) {
if(file == null) {
throw new ApplicationException(ErrorCode.EXCEL_EXTENSION);
}
String fileName = file.getOriginalFilename();
if(fileName == null) {
throw new ApplicationException(ErrorCode.EXCEL_EXTENSION);
}
int dotIndex = fileName.lastIndexOf(".");
if(dotIndex == -1) {
throw new ApplicationException(ErrorCode.EXCEL_EXTENSION);
}
String extension = fileName.substring(dotIndex + 1);
if(!extension.equals("csv") && !extension.equals("xls") && !extension.equals("xlsx")) {
throw new ApplicationException(ErrorCode.EXCEL_EXTENSION);
}
return extension;
}
/**
* xlsx 확장자 파일에서 셀의 값을 문자열로 변환하는 메소드
* @param cell 셀 정보
* @return 셀 값을 변환한 문자열
*/
private String getCellValueAsString(Cell cell) {
if (cell == null) {
return "";
}
CellType cellType = cell.getCellType();
Object cellValue = getCellValue(cell, cellType);
return cellValue != null ? cellValue.toString() : "";
}
/**
* xlsx 확장자 파일에서 특정 셀의 값을 반환하는 메소드
* @param cell 특정 셀 정보
* @param cellType 특정 셀 자료형
* @return 해당 셀의 자료형에 맞는 값 반환
*/
private Object getCellValue(Cell cell, CellType cellType) {
return switch (cellType) {
case STRING -> cell.getStringCellValue();
case NUMERIC -> cell.getNumericCellValue();
case BOOLEAN -> cell.getBooleanCellValue();
case FORMULA -> cell.getCellFormula();
default -> null;
};
}
/**
* 파일에서 데이터 추출
* @param file 파일
* @return 파일의 데이터를 DTO List 변환 후 반환
*/
public List<ExcelDto> extractExcel(MultipartFile file) {
try {
List<ExcelDto> excelDtoList = new ArrayList<>();
String extension = checkExtension(file);
switch (extension) {
case "xlsx":
Workbook workbook = new XSSFWorkbook(file.getInputStream());
Sheet sheet = workbook.getSheetAt(0);
for(Row row : sheet) {
if(row.getRowNum() > 0) {
String customerName = getCellValueAsString(row.getCell(0));
String customerEmail = getCellValueAsString(row.getCell(1));
String customerPhone = getCellValueAsString(row.getCell(2));
String customerType = getCellValueAsString(row.getCell(3));
String company = getCellValueAsString(row.getCell(4));
String team = getCellValueAsString(row.getCell(5));
String position = getCellValueAsString(row.getCell(6));
String etc = getCellValueAsString(row.getCell(7));
excelDtoList.add(ExcelDto.builder()
.customerName(customerName)
.customerEmail(customerEmail)
.customerPhone(customerPhone)
.customerType(customerType)
.company(company)
.team(team)
.position(position)
.etc(etc)
.build()
);
}
// Header 검증
else {
if(!getCellValueAsString(row.getCell(0)).equals("이름")) {
throw new ApplicationException(ErrorCode.EXCEL_HEADER);
}
if(!getCellValueAsString(row.getCell(1)).equals("이메일")) {
throw new ApplicationException(ErrorCode.EXCEL_HEADER);
}
if(!getCellValueAsString(row.getCell(2)).equals("전화번호")) {
throw new ApplicationException(ErrorCode.EXCEL_HEADER);
}
if(!getCellValueAsString(row.getCell(3)).equals("고객 유형")) {
throw new ApplicationException(ErrorCode.EXCEL_HEADER);
}
if(!getCellValueAsString(row.getCell(4)).equals("회사")) {
throw new ApplicationException(ErrorCode.EXCEL_HEADER);
}
if(!getCellValueAsString(row.getCell(5)).equals("부서")) {
throw new ApplicationException(ErrorCode.EXCEL_HEADER);
}
if(!getCellValueAsString(row.getCell(6)).equals("직책")) {
throw new ApplicationException(ErrorCode.EXCEL_HEADER);
}
if(!getCellValueAsString(row.getCell(7)).equals("기타")) {
throw new ApplicationException(ErrorCode.EXCEL_HEADER);
}
}
}
break;
case "csv":
Reader reader = new BufferedReader(new InputStreamReader(file.getInputStream()));
CsvToBean<ExcelDto> csvToBean = new CsvToBeanBuilder<ExcelDto>(reader)
.withType(ExcelDto.class)
.withIgnoreLeadingWhiteSpace(true)
.build();
excelDtoList = csvToBean.parse();
break;
}
return excelDtoList;
} catch (IOException e) {
throw new ApplicationException(ErrorCode.EXCEL_CONVERT_ERROR);
} catch (Exception e) {
log.error(e.getMessage());
throw new RuntimeException(e);
}
}- checkExtension은 확장자를 반환하는 메소드로, 확장자에 따라 다른 엑셀 파일 처리 로직을 진행하기에 개발했다.
- getCellValueAsString은 Pandas에서 엑셀을 읽을 때 사용하는
dtype=str의 역할을 한다. - getCellValue는 getCellValueAsString를 통해 문자열로만 셀의 값을 반환하기 전, 셀의 자료형에 따라 다르게 가져와야 하는 경우를 처리하는 역할을 한다.
- extractExcel은 엑셀 파일을 열어 row별로 데이터를 가져온다.
DTO
@Getter
@NoArgsConstructor
public class ExcelDto {
@CsvBindByName(column = "이름")
private String customerName;
@CsvBindByName(column = "이메일")
private String customerEmail;
@CsvBindByName(column = "전화번호")
private String customerPhone;
@CsvBindByName(column = "고객 유형")
private String customerType;
@CsvBindByName(column = "회사")
private String company;
@CsvBindByName(column = "부서")
private String team;
@CsvBindByName(column = "직책")
private String position;
@CsvBindByName(column = "기타")
private String etc;
@Builder
public ExcelDto(String customerName, String customerEmail, String customerPhone, String customerType, String company, String team, String position, String etc) {
this.customerName = customerName;
this.customerEmail = customerEmail;
this.customerPhone = customerPhone;
this.customerType = customerType;
this.company = company;
this.team = team;
this.position = position;
this.etc = etc;
}
}
java.lang.RuntimeException: com.opencsv.exceptions.CsvBeanIntrospectionException: Basic instantiation of the given bean type (and subordinate beans created through recursion, if applicable) was determined to be impossible.OpenCSV를 활용해 , Dto로 변환하는 과정에서 위와 같은 오류가 발생했다.
해당 오류는@NoArgsConstructor를 추가하지 않아 발생한 오류다.
Csv 파일의 Row별로 가져온 이후, Bean 클래스를 위한 공용 생성자가 있어야 하는데, 이 부분에서 공용 생성자를 찾지 못해 발생한 것이었다. stackoverflow를 찾아보니,@Builder를 사용하는 경우에는@AllArgsConstructor(access = PRIVATE)를 포함해야 한다고 한다.
등록된 데이터 엑셀로 내려주기
xlsx와 csv 중 어떤 확장자로 다운로드할 수 있도록 제공하냐에 따라서 달라진다.
API를 분리해도 되지만, 나는 RequestParameter를 활용한 조건 분기 방식으로 개발했다.
Controller
@GetMapping("/download/excel")
@Operation(summary = "고객 정보 엑셀로 반환", description = "csv, xlsx와 같은 type 구분에 따라 해당 확장자로 모든 고객 정보 반환")
@ApiResponses(
value = {
@ApiResponse(responseCode = "1000", description = "성공 시 반환되는 코드", content = {@Content(schema = @Schema(implementation = ApplicationResponse.class))}),
@ApiResponse(responseCode = "2000", description = "입력 값이 유효하지 않거나, 서버 내부에서 오류가 발생했을 때 반환하는 에러", content = {@Content(schema = @Schema(implementation = ExceptionResponse.class))}),
})
public void downloadCustomerExcel(HttpServletRequest request, HttpServletResponse response) {
try {
// Query Parameter 추출
String type = request.getParameter("type");
// 비즈니스 로직 수행
List<CustomerResponse> customerResponseList = customerFacade.downloadCustomerExcel();
// 엑셀 응답 반환
switch (type) {
case "csv":
// 엑셀 응답 설정
response.setContentType("text/csv; charset=UTF-8"); // Set the character encoding
response.setHeader("Content-Disposition",
"attachment; filename=\"" + URLEncoder.encode("고객 정보.csv", StandardCharsets.UTF_8) + "\"");
OutputStreamWriter writer = new OutputStreamWriter(response.getOutputStream(),
StandardCharsets.UTF_8);
writer.write("\uFEFF");
CSVWriter csvWriter = new CSVWriter(writer);
// 데이터 행 추가
List<String[]> dataList = new ArrayList<>();
dataList.add(new String[]{"이름", "이메일", "전화번호", "고객 레벨", "고객 유형", "회사", "부서", "직책", "기타", "수신거부 여부"});
for (CustomerResponse customer : customerResponseList) {
String[] dataRow = {
customer.customerName(),
customer.customerEmail(),
customer.customerPhone(),
customer.customerLevel(),
customer.customerType(),
customer.company(),
customer.team(),
customer.position(),
customer.etc(),
customer.isRejected() != null ? customer.isRejected().toString() : ""
};
dataList.add(dataRow);
}
// 엑셀 응답 설정
csvWriter.writeAll(dataList);
csvWriter.close();
writer.close();
break;
case "xlsx":
// 엑셀 데이터 생성
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("고객 명단");
int rowNo = 0;
Row headerRow = sheet.createRow(rowNo++);
headerRow.createCell(0).setCellValue("이름");
headerRow.createCell(1).setCellValue("이메일");
headerRow.createCell(2).setCellValue("전화번호");
headerRow.createCell(3).setCellValue("고객 레벨");
headerRow.createCell(4).setCellValue("고객 유형");
headerRow.createCell(5).setCellValue("회사");
headerRow.createCell(6).setCellValue("부서");
headerRow.createCell(7).setCellValue("직책");
headerRow.createCell(8).setCellValue("기타");
headerRow.createCell(9).setCellValue("수신거부 여부");
for (CustomerResponse customer : customerResponseList) {
Row row = sheet.createRow(rowNo++);
row.createCell(0).setCellValue(customer.customerName());
row.createCell(1).setCellValue(customer.customerEmail());
row.createCell(2).setCellValue(customer.customerPhone());
row.createCell(3).setCellValue(customer.customerLevel());
row.createCell(4).setCellValue(customer.customerType());
row.createCell(5).setCellValue(customer.company());
row.createCell(6).setCellValue(customer.team());
row.createCell(7).setCellValue(customer.position());
row.createCell(8).setCellValue(customer.etc());
row.createCell(9).setCellValue(customer.isRejected() != null ? customer.isRejected().toString() : "");
}
// 엑셀 응답 설정
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setHeader("Content-Disposition",
"attachment; filename=\"" + URLEncoder.encode("고객 정보.xlsx", StandardCharsets.UTF_8) + "\"");
workbook.write(response.getOutputStream());
workbook.close();
break;
default:
throw new ApplicationException(ErrorCode.INVALID_VALUE);
}
} catch (IOException e) {
throw new RuntimeException(e);
}
}엑셀 파일 다운로드 기능을 위해 HttpServletResponse를 활용해야 한다.
이 때, Service 계층에서는 List<String[]> 형태의 Row 정보들만 받고 실제 Excel 파일 생성 및 반환하는 로직을 Controller에서 구현했다.
Excel 파일을 생성 및 반환하는 부분이 비즈니스 로직이라고 생각되어 Service 계층에서 작성할까 했지만, HttpServletResponse를 Service 계층까지 내리는 것은 부적절하다고 판단되어 Controller에서 구현했다.
작성하고 보니 엑셀 관련 로직에 문제가 생겼을 때, Controller에서도 오류 발생 여부를 확인해야 하기에, 로직이 여러 파일로 분산되어 있는 것 같아 어떻게 개선해야 할지 고민이 된다.
Facade
public List<CustomerResponse> downloadCustomerExcel() {
List<Customer> customerList = customerService.getAllCustomers();
return customerList.stream()
.map(CustomerResponse::of)
.toList();
}후기 & 개선 필요한 점
Python으로 엑셀 등의 파일을 다룰 때, 얼마나 편했었는지를 다시 한 번 체감했다.
엑셀 Column별로 유효성 검증 등의 로직을 적용해 파일이 잘못되었을 경우를 핸들링할 수 있다면, 더 안정성이 높아질 것 같아 추후 진행사항으로 잡을 예정이다.
위에 작성했듯이 Controller에서 엑셀 파일을 만드는 코드를 어느 계층에서 처리하는 것이 더 좋을지에 대한 고민도 해볼 것이다.
레퍼런스
