[Spring Boot] 엑셀(xlsx) 파일 파싱 후, 서비스 로직에 추가하는 코드 작성하기

📖 발단

교내에서 진행하는 캠퍼스SW아카데미인 TABA 프로젝트 기간에 발생했던 내용들을 기록하고자 한다.

 

 

우리팀의 경우 이미지 인식 AI를 활용한 두피 관리 서비스를 주제로 개발을 진행했었다.

아래 사진과 같이 사용자의 두피 사진을 통해 6가지 증상을 측정하는데,

6가지 증상에 대한 사용자의 두피 점수와 성별 및 같은 나이대에 대한 평균 값과 비교하여

한눈에 보기 쉽게끔 육각형 그래프로 나타내고자 했었다.

 

백엔드에서 전달한 값을 통해 프론트에서 만든 육각형 그래프

 

 

프론트에서 이러한 육각형 그래프를 그리기 위해서는

백엔드에서 사용자의 성별과 나이대에 맞는 평균 값을 보내줘야했다.

해당 값들은 AI 파트를 맡은 팀원이, 추출한 값들을 엑셀(xlsx) 파일로 보내줬고

이를 스프링부트에서 파싱하여 프론트에 값들을 보내도록 구현했다.

 

 

아래 사진은 해당 엑셀 파일의 일부로,

남/여에 대한 0대부터 80대까지의 6가지 증상에 대한 평균 값들이 기록되어있다.

 

40대 남/여, 50대 남/여의 평균값들

 

 

📖 구현

1. Apache POI 의존성 추가하기

Apache POI는 Java를 통해 Excel 통합 문서를 만들고 조작하기위한 오픈 소스 Java 라이브러리이다. 

해당 라이브러리를 사용하기 위해서, build.gradle에 아래와 같은 의존성을 추가해주자!

// Excel
implementation group: 'org.apache.poi', name: 'poi', version: '5.0.0'
implementation group: 'org.apache.poi', name: 'poi-ooxml', version: '5.0.0'

 

2. application.yml에 xlsx파일 경로 추가하기

 

다음과 같이 averageByAge.xlsx 파일이 /home/ubuntu 경로에 저장되어있고,

이를 application.yml에 해당 경로를 추가해줘야한다.

 

하지만 나의 경우 application.yml에 아래와 같이 "/app/averageByAge.xlsx"로 경로를 추가했다.

 

그 이유는 build된 .jar 파일을 docker로 이미지화를 수행하고 pull 받은 이미지를 실행할 때

아래와 같은 명령어를 통해 run 되기 때문이다.

docker run -it -d --rm -p 8081:8080 -v /home/ubuntu:/app -e SPRING_CONFIG_LOCATION=/app/application.yml $USERNAME/$REPO:${GITHUB_SHA::7}

이는 호스트 머신의 /home/ubuntu 경로에서 컨테이너 내부의 /app 경로로 볼륨 마운트를 수행하기 때문에

application.yml 파일안에 "/app/averageByAge.xlsx"라고 작성한 것이다.

만약에, 나와 같은 과정을 수행하지 않고 일반적으로 build하며 .jar 파일을 실행하는 방식이라면

"/home/ubuntu/averageByAge.xlsx"라고 작성해주면 된다.

 

 

3. 코드 구현하기

사용자가 기록해 놓은 나이, 성별의 경우 숫자와 male/female의 문자열 형태로 기록이 되기 때문에

아래와 같이 Map을 사용하여, 이에 맞는 엑셀 파일의 행 번호와 매칭 시켰다.

Map<String, Integer> averageMap = new HashMap<>() {{
   put("0male", 0);
    put("0female", 1);
    put("10male", 2);
    put("10female", 3);
    put("20male", 4);
    put("20female", 5);
    put("30male", 6);
    put("30female", 7);
    put("40male", 8);
    put("40female", 9);
    put("50male", 10);
    put("50female", 11);
    put("60male", 12);
    put("60female", 13);
    put("70male", 14);
    put("70female", 15);
    put("80female", 16);
}};

 

 

@Value("${excel.path}")
private String resourcePath;


private ResponseAverageByAgeDTO getAverageByAgeInExcel(String gender, String old) throws FileNotFoundException, IOException, InvalidFormatException {
    String target = old + gender;

    File file = new File(resourcePath);

    InputStream inputStream = new FileInputStream(file);
    OPCPackage opcPackage = OPCPackage.open(inputStream);
    Workbook workbook = null;
    try {
        if (resourcePath.endsWith(".xlsx")) {
            workbook = new XSSFWorkbook(opcPackage);
        }
        else {
            throw new RuntimeException("File type not matched");
        }

        if (workbook == null) {
            throw new RuntimeException("No data in file");
        }
    } catch(Exception e) {
        e.printStackTrace();
    }

    Sheet sheet = workbook.getSheetAt(0);

    Integer averageIndex = averageMap.get(target);

    ResponseAverageByAgeDTO dto = ResponseAverageByAgeDTO.builder()
            .gender(gender)
            .old(old)
            .avgFindDeadSkinCells(getCell(sheet, 1, averageIndex).getNumericCellValue())
            .avgExcessSebum(getCell(sheet, 2, averageIndex).getNumericCellValue())
            .avgErythemaBetweenHairFollicles(getCell(sheet, 3, averageIndex).getNumericCellValue())
            .avgErythemaPustules(getCell(sheet, 4, averageIndex).getNumericCellValue())
            .avgDandruff(getCell(sheet, 5, averageIndex).getNumericCellValue())
            .avgHairLoss(getCell(sheet, 6, averageIndex).getNumericCellValue())
            .build();

    return dto;
}

 

해당 코드는 엑셀 파일에서 성별과 나이에 따른 특정 지표의 평균값을 읽어와서

이를 ResponseAverageByAgeDTO 객체로 변환하는 메서드이다.

Apache POI를 사용하기 위해서,

File 객체를 생성하여 resourcePath를 이용해 해당 경로에 있는 파일을 가리킨다.

이 후, FileInputStream을 사용하여 파일을 읽는 InputStream 객체를 생성하고

OPCPackage 클래스를 사용하여 InputStream으로부터 Workbook을 생성한다.

Workbook을 생성하는 과정에서 파일의 확장자가 .xlsx라면 XSSFWorkbook을 생성하고,

그렇지 않은 경우에는 예외를 발생시킨다.

 

그리고 해당 엑셀은 하나의 시트만 존재 하므로, 첫 번째 시트에 접근하기 위해서

getSheetAt(0)를 사용해준다.

ResponseAverageByAgeDTO 객체를 생성하고, 해당 객체의 각 필드에 시트에서 읽은 평균값을 할당한다.

이때 getCell 메서드를 사용하여 특정 셀의 값을 읽어온다.

 

org.apache.poi.ss.util.SheetUtil.getCell 메서드의 경우 아래와 같은 구조를 띄고 있다.

public static org.apache.poi.ss.usermodel.Cell getCell(     
	@NotNull  org.apache.poi.ss.usermodel.Sheet sheet,
    int rowIx,
    int colIx 
)

sheet – The workbook sheet to look at. 

rowIx – The 0-based index of the row. 

colIx – The 0-based index of the cell.

 

 

아래는 서비스 단에서 엑셀 파싱 후, 결과를 전달하는 부분의 전체 코드이다.

@Service
@Component
@RequiredArgsConstructor
public class DiagnosisResultService {

    Map<String, Integer> averageMap = new HashMap<>() {{
       put("0male", 0);
        put("0female", 1);
        put("10male", 2);
        put("10female", 3);
        put("20male", 4);
        put("20female", 5);
        put("30male", 6);
        put("30female", 7);
        put("40male", 8);
        put("40female", 9);
        put("50male", 10);
        put("50female", 11);
        put("60male", 12);
        put("60female", 13);
        put("70male", 14);
        put("70female", 15);
        put("80female", 16);
    }};

    @Value("${excel.path}")
    private String resourcePath;

    public ResponseDetailDiagnosisResultDTO findDetailDiagnosisResult(RequestDetailMemberDTO dto) throws FileNotFoundException, IOException, InvalidFormatException {
        Member member = memberRepository.findByNickname(dto.getNickname()).orElseThrow(UserNotFoundException::new);

        DiagnosisResult diagnosisResult = diagnosisResultRepository.findByDateForDetail(member.getId(), changeLocalDateTimeFormat(dto.getDate())).orElseThrow(DiagnosisResultNotFoundException::new);
        Survey survey = surveyRepository.findByDate(member.getId(), changeLocalDateTimeFormat(dto.getDate())).orElseThrow(SurveyNotFoundException::new);

        ResponseDetailDiagnosisResultDTO responseDto = ResponseDetailDiagnosisResultDTO.builder()
                .diagnosisResult(diagnosisResult)
                .responseAverageByAgeDTO(getAverageByAgeInExcel(survey.getGender(), survey.getOld()))
                .build();

        return responseDto;
    }


    private LocalDateTime changeLocalDateTimeFormat(String targetDate) {
        LocalDateTime formatLocalDateTimeNow = LocalDateTime.parse(targetDate, DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));

        System.out.println("String to LocalDatetime : " + formatLocalDateTimeNow);
        return formatLocalDateTimeNow;
    }

    private ResponseAverageByAgeDTO getAverageByAgeInExcel(String gender, String old) throws FileNotFoundException, IOException, InvalidFormatException {
        String target = old + gender;

        File file = new File(resourcePath);

        InputStream inputStream = new FileInputStream(file);
        OPCPackage opcPackage = OPCPackage.open(inputStream);
        Workbook workbook = null;
        try {
            if (resourcePath.endsWith(".xlsx")) {
                workbook = new XSSFWorkbook(opcPackage);
            }
            else {
                throw new RuntimeException("File type not matched");
            }

            if (workbook == null) {
                throw new RuntimeException("No data in file");
            }
        } catch(Exception e) {
            e.printStackTrace();
        }

        Sheet sheet = workbook.getSheetAt(0);

        Integer averageIndex = averageMap.get(target);

        ResponseAverageByAgeDTO dto = ResponseAverageByAgeDTO.builder()
                .gender(gender)
                .old(old)
                .avgFindDeadSkinCells(getCell(sheet, 1, averageIndex).getNumericCellValue())
                .avgExcessSebum(getCell(sheet, 2, averageIndex).getNumericCellValue())
                .avgErythemaBetweenHairFollicles(getCell(sheet, 3, averageIndex).getNumericCellValue())
                .avgErythemaPustules(getCell(sheet, 4, averageIndex).getNumericCellValue())
                .avgDandruff(getCell(sheet, 5, averageIndex).getNumericCellValue())
                .avgHairLoss(getCell(sheet, 6, averageIndex).getNumericCellValue())
                .build();

        return dto;
    }
}