Java에서 엑셀 파일 읽어서 파싱(Parsing)하기

SpringBoot를 이용한 프로젝트 도중 엑셀 파일을 받아서 그 안의 일부 데이터를 파싱(나누기)을 할 일이 생겨서 기록한다

build.gradle

	//dependencies 추가
	implementation group: 'org.apache.poi', name: 'poi', version: '5.0.0'
	implementation group: 'org.apache.poi', name: 'poi-ooxml', version: '5.0.0'

버전은 5가 최근인 것 같아 다음과 같이 설치했다
https://poi.apache.org/

Controller

// 엑셀 업로드
    @PostMapping("/excelupload")
    public ResponseEntity uploadFile(@RequestParam("file") MultipartFile file) throws IOException {
        timeTableService.makeTimetableFromExcel(file);
        return new ResponseEntity(HttpStatus.CREATED);
    }

파일을 받을 때는 MultipartFile을 사용했다

Service

// 엑셀 데이터 읽어오기
    public List<String> readExcel(MultipartFile file) throws IOException {

        List<String> timetableList = new ArrayList<>();

        InputStream inputStream = file.getInputStream();// 파일 읽기
        XSSFWorkbook workbook = new XSSFWorkbook(inputStream); // 엑셀 파일 파싱

        XSSFSheet sheet = workbook.getSheetAt(0); // 엑셀 파일의 첫번째 (0) 시트지
        int rows = sheet.getPhysicalNumberOfRows(); // 행의 수

        for (int r = 1; r < rows-1; r++) { // 마지막에 건수 있음. 그 줄 제외
            XSSFRow row = sheet.getRow(r); // 0 ~ rows

            if (row != null) { // 행이 비어있지 않을 때
                int cells=row.getPhysicalNumberOfCells(); // 열의 수

                for (int c = 0; c < cells; c++) {
                    XSSFCell cell = row.getCell(c); // 0 ~ cell
                    String value = "";

                    // 12반쩨 열이 강의시간(강의실)
                    // r열 c행의 cell이 비어있을 때 혹은 시간표 열이 아닐때
                    if (cell == null || c != 12 || cell.getCellType().equals(CellType.BLANK)) { 
                        continue;
                    } else { // 타입별로 내용 읽기
                        switch (cell.getCellType()) {
                            case FORMULA:
                                value = cell.getCellFormula();
                                break;
                            case NUMERIC:
                                value = cell.getNumericCellValue() + "";
                                break;
                            case STRING:
                                value = cell.getStringCellValue() + "";
                                break;
                            case BLANK: // 빈칸에 false 값 들어있는 듯
                                value = cell.getBooleanCellValue() + "";
                                break;
                            case ERROR:
                                value = cell.getErrorCellValue() + "";
                                break;
                        }

                    }
                    timetableList.add(value);
                }
            }
        }

        return timetableList;
    }

InputStream으로 읽었고, 엑셀 파일의 M열에 원하는 데이터가 있어 12번째 줄만 불러와 String List으로 만들어 반환하였다
내가 사용한 데이터는 모두 String이었고, 빈칸일 때 BLANK로 들어가서 false로 출력이 되었다. 이 부분도 list에 넣지 않았다

    // List<String> 파싱하기
    public void parseStringList(List<String> timetableList) {

        // 데이터 종류
        // 화10:30~11:45 목16:30~17:45(공1302)
        // 화14:30~16:20(공2515) 수16:00~17:50(공2222)
        // 목14:00~17:50(공2415)
        // 화16:00~17:50 목13:00~14:50(공1420-1)
        // " "을 기준으로 split하고, "\\("을 기준으로 다시 split한 데이터를 정규표현식으로 잘라야할 듯

        DayOfWeek week1 = null;
        LocalTime starttime1 = null;
        LocalTime endtime1 = null;
        DayOfWeek week2 = null;
        LocalTime starttime2 = null;
        LocalTime endtime2 = null;
        String building = null;
        String classNum = null;

        Map<String, String> changeBuildingValue = new HashMap<>();
        changeBuildingValue.put("공1", "공대1호관");
        changeBuildingValue.put("공2", "공대2호관");
        changeBuildingValue.put("공3", "공대3호관");
        changeBuildingValue.put("공4", "공대4호관");
        changeBuildingValue.put("공5", "공대5호관");
        changeBuildingValue.put("건7", "덕래관");
        changeBuildingValue.put("오", "오산관");
        changeBuildingValue.put("쉐", "쉐턱관");
        changeBuildingValue.put("의", "의양관");


        for (String timetable : timetableList) {

            // 화10:30~11:45
            // or
            // 목16:30~17:45(공1302)
            String[] timeAndPlace1 = timetable.split(" ");

            for (String tAndp : timeAndPlace1) {
                // 화10:30~11:45
                // or
                // 목16:30~17:45
                // 공1302)
                String[] timeAndPlace2 = tAndp.split("\\(");

                for (String input : timeAndPlace2) {
                    TimeTableMakeDto timeTableMakeDto = makeTimeTableData(input);

                    if (timeTableMakeDto.getBuilding() == null && week1 == null) { // 요일, 시간 정보, 처음으로 받아오는 정보
                        week1 = timeTableMakeDto.getWeek();
                        starttime1 = timeTableMakeDto.getStarttime();
                        endtime1 = timeTableMakeDto.getEndtime();
                    }
                    else if (timeTableMakeDto.getBuilding() == null && week1 != null) { // 요일, 시간 정보, 두번째로 받아오는 정보
                        week2 = timeTableMakeDto.getWeek();
                        starttime2 = timeTableMakeDto.getStarttime();
                        endtime2 = timeTableMakeDto.getEndtime();
                    }
                    else if(timeTableMakeDto.getWeek() == null) { // 건물, 강의실 정보
                        building = changeBuildingValue.get(timeTableMakeDto.getBuilding());
                        classNum = timeTableMakeDto.getClassNum();

                        if (week2 == null) { // 시간정보1, 위치정보1, 하나만 저장

                            TimetableSaveDto saveDto = TimetableSaveDto.builder()
                                    .week(week1)
                                    .starttime(starttime1)
                                    .endtime(endtime1)
                                    .building(building)
                                    .classNum(classNum)
                                    .build();
                            save(saveDto);

                        }
                        else { // 시간정보2, 위치정보1, 2개 저장

                            TimetableSaveDto saveDto1 = TimetableSaveDto.builder()
                                    .week(week1)
                                    .starttime(starttime1)
                                    .endtime(endtime1)
                                    .building(building)
                                    .classNum(classNum)
                                    .build();

                            TimetableSaveDto saveDto2 = TimetableSaveDto.builder()
                                    .week(week2)
                                    .starttime(starttime2)
                                    .endtime(endtime2)
                                    .building(building)
                                    .classNum(classNum)
                                    .build();

                            save(saveDto1);
                            save(saveDto2);

                        }

                        // 초기화
                        week1 = null;
                        starttime1 = null;
                        endtime1 = null;
                        week2 = null;
                        starttime2 = null;
                        endtime2 = null;
                        building = null;
                        classNum = null;

                    }
                }
            }

        }
    }

split을 이용하여 파싱하였다

화10:30~11:45 목16:30~17:45(공1302)
화14:30~16:20(공2515) 수16:00~17:50(공2222)
목14:00~17:50(공2415)
화16:00~17:50 목13:00~14:50(공1420-1)

데이터가 다음과 같은 4가지 종류로 들어오는데,
이것을 classNum(302), building(공1), starttime(10:30), endtime(11:45), week(1) 이런식으로 넣기 위해서 다음과 같은 코드를 이용했다
코드가 매우 더럽다..ㅎㅎ

3번째 for문에 있는 makeTimeTableData에서 split된 데이터를 받아서 데이터베이스에 저장하는 형식으로 변환한다

private TimeTableMakeDto makeTimeTableData(String input) {

        Map<String, Integer> dayofWeekValue = new HashMap<>();
        dayofWeekValue.put("월", 1);
        dayofWeekValue.put("화", 2);
        dayofWeekValue.put("수", 3);
        dayofWeekValue.put("목", 4);
        dayofWeekValue.put("금", 5);
        dayofWeekValue.put("토", 6);
        dayofWeekValue.put("일", 7);

        // 요일과 시간을 나타내는 정규표현식
        String dayTimeRegex = "(?<dayOfWeek>[월화수목금토일]+)(?<startTime>\\d{2}:\\d{2})~(?<endTime>\\d{2}:\\d{2})";

        // 입력 문자열이 요일과 시간을 나타내는 패턴에 일치하는지 확인
        Pattern dayTimePattern = Pattern.compile(dayTimeRegex);
        Matcher dayTimeMatcher = dayTimePattern.matcher(input);

        if (dayTimeMatcher.matches()) {
            // DayofWeek 형식의 요일 출력
            // 월화수목금토일 -> 숫자로
            String kr_dayofWeek = dayTimeMatcher.group("dayOfWeek");
            Integer num_dayofWeek = dayofWeekValue.get(kr_dayofWeek);
            DayOfWeek dayOfWeek = DayOfWeek.of(num_dayofWeek);

            // LocalTime 형식의 시작 시간 출력
            String startTimeStr = dayTimeMatcher.group("startTime");
            LocalTime startTime = LocalTime.parse(startTimeStr);

            // LocalTime 형식의 끝 시간 출력
            String endTimeStr = dayTimeMatcher.group("endTime");
            LocalTime endTime = LocalTime.parse(endTimeStr);

            return TimeTableMakeDto.builder()
                    .week(dayOfWeek)
                    .starttime(startTime)
                    .endtime(endTime)
                    .build();

        }
        else {
            // 공1, 공2, 공3, 공4, 건7, 쉐, 오...
            // 공이나 건이면 2글자 가져오고 아니면 1글자
            String firstword = String.valueOf(input.charAt(0));
            if (firstword.equals("공") || firstword.equals("건")){
                String building = input.substring(0,2); // 2글자 가져오기
                //System.out.println("building: " + building);
                if (input.contains("-")) {
                    String classNum = input.substring(2,7);

                    return TimeTableMakeDto.builder()
                            .building(building)
                            .classNum(classNum)
                            .build();
                }
                else {
                    String classNum = input.substring(2,5);

                    return TimeTableMakeDto.builder()
                            .building(building)
                            .classNum(classNum)
                            .build();
                }
            }
            else { // 쉐, 오,,
                String building = firstword; // 1글자 가져오기
                if (input.contains("-")) {
                    String classNum = input.substring(1,6);

                    return TimeTableMakeDto.builder()
                            .building(building)
                            .classNum(classNum)
                            .build();
                }
                else {
                    String classNum = input.substring(1,4);

                    return TimeTableMakeDto.builder()
                            .building(building)
                            .classNum(classNum)
                            .build();
                }
            }
        }

    }

정규표현식을 사용하여 한번 분리해주고, 공1, 쉐 처럼 숫자가 붙는 건물이름은 나눠서 데이터를 만들었다
classNum에 101-3처럼 "-"가 붙는 것도 있어서 이 부분도 나눠주었다

DB

다음과 같이 db에 저장된다!

---

참고

https://velog.io/@uuuouuo/parsing-1
https://sunghs.tistory.com/114
https://shinsunyoung.tistory.com/71