[Application] 엑셀 파일을 Parsing해서 DB로 저장하기

다소 무거워지던 프로젝트의 열화판으로, 오직 공지사항 스크래핑과 시간표 기능만 모바일 애플리케이션으로 구현한다. 모든 백엔드 서버는 스프링부트로 구축하며, 안드로이드 스튜디오에서 API에 접근해 UI를 구현한다. 이를 위해 먼저 엑셀로 올라오는 개설과목 정보를 읽어 DB에 저장하는 기능을 구현하였다. 

 

실사용 시에는 외부 서버에 파일을 따로 관리해야 하지만, 현재는 프로젝트 directory에 테스트용으로 둔다. 자세한 코드는 github에 저장해두었으며, 본 포스트에서는 핵심적인 내용만 다뤄보겠다. DB 연결과 관련한 내용은 SideProject 카테고리의 '중단'에서 자세히 다루었다. 

 

Course.java 엔티티

package zerogod.android_backend.model;

import jakarta.persistence.*;
import lombok.Getter;
import lombok.Setter;

@Setter
@Getter
@Entity
@Table(name = "course")
public class Course {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "faculty", nullable = false) // 개설학부
    private String faculty;

    @Column(name = "major", nullable = false) // 개설전공
    private String major;

    @Column(name = "course_name", nullable = false)
    private String courseName;   // 과목명

    @Column(name = "credits", nullable = false)
    private int credits;         // 학점

    @Column(name = "course_no", nullable = false)
    private String courseNo; // 수강번호

    @Column(name = "classification", nullable = false)
    private String classification; // 전선 전필 교필

    @Column(name = "category", nullable = false)
    private String category; // 기초, 전공

    @Column(name = "english", nullable = false) // 원어강의 여부
    private String english; // 원어강의

    @Column(name = "instructor", nullable = false)// 담당교수
    private String instructor;

    @Column(name = "class", nullable = false) // 강의 시간 및 장소
    private String class_;

    // 기본 생성자
    public Course() {
    }

    public Course(String faculty, String major, String courseName, int credit, String courseNo, String classification, String category, String english, String instructor, String class_) {
        this.faculty = faculty;
        this.major = major;
        this.courseName = courseName;
        this.credits = credit;
        this.courseNo = courseNo;
        this.classification = classification;
        this.category = category;
        this.english = english;
        this.instructor = instructor;
        this.class_ = class_;
    }
}

개설과목 정보를 기반으로 DB에 저장할 정보들을 지정한다. 

 

ExcelService.java

package zerogod.android_backend.service;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import zerogod.android_backend.model.Course;
import zerogod.android_backend.repository.CourseRepository;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;

@Service
public class ExcelService {

    private final CourseRepository courseRepository;

    @Autowired
    public ExcelService(CourseRepository courseRepository) {
        this.courseRepository = courseRepository;
    }

    public void processExcelFile(MultipartFile file) throws IOException {
        String uploadDir = System.getProperty("user.dir") + "/uploaded"; // 저장 경로
        File targetFile = new File(uploadDir, file.getOriginalFilename());
        file.transferTo(targetFile); // 파일 저장

        processExcelFileFromPath(targetFile.getAbsolutePath());
    }

    public void processExcelFileFromPath(String filePath) throws IOException {
        File file = new File(filePath);
        if (!file.exists()) {
            throw new IOException("File not found at path: " + filePath);
        }

        try (Workbook workbook = new XSSFWorkbook(new FileInputStream(file))) {
            Sheet sheet = workbook.getSheetAt(0);

            for (Row row : sheet) {
                if (row.getRowNum() < 3) continue; // 헤더 무시

                Course course = parseRowToCourse(row);
                if (!isDuplicate(course)) {
                    courseRepository.save(course);
                }
            }
        }
    }

    public void processExcelFilesInFolder(String folderPath) throws IOException {
        File folder = new File(folderPath);
        File[] files = folder.listFiles((dir, name) -> name.endsWith(".xlsx"));

        if (files == null || files.length == 0) {
            System.out.println("No Excel files to process in folder: " + folderPath);
            return;
        }

        for (File file : files) {
            processExcelFileFromPath(file.getAbsolutePath());
        }
    }

    private Course parseRowToCourse(Row row) {
        Course course = new Course();
        course.setFaculty(getStringCellValue(row.getCell(1)));
        course.setMajor(getStringCellValue(row.getCell(2)));
        course.setCourseName(getStringCellValue(row.getCell(3)));
        course.setCredits(getNumericCellValue(row.getCell(4)));
        course.setCourseNo(getStringCellValue(row.getCell(6)));
        course.setClassification(getStringCellValue(row.getCell(7)));
        course.setCategory(getStringCellValue(row.getCell(8)));
        course.setEnglish(getStringCellValue(row.getCell(9)));
        course.setInstructor(getStringCellValue(row.getCell(14)));
        course.setClass_(getStringCellValue(row.getCell(15)));
        return course;
    }

    private boolean isDuplicate(Course course) {
        return courseRepository.existsByCourseNo(course.getCourseNo());
    }

    private String getStringCellValue(Cell cell) {
        try {
            return cell != null ? cell.getStringCellValue().trim() : "";
        } catch (Exception e) {
            System.err.println("Error reading string cell value: " + e.getMessage());
            return "";
        }
    }

    private int getNumericCellValue(Cell cell) {
        try {
            return (cell != null && cell.getCellType() == CellType.NUMERIC)
                    ? (int) cell.getNumericCellValue() : 0;
        } catch (Exception e) {
            System.err.println("Error reading numeric cell value: " + e.getMessage());
            return 0;
        }
    }
}

ParseRowToCourse함수는 엑셀의 열과, Course 엔티티의 값을 매핑한다. 이는 processExcelFileFromPath함수에서 초기 3개 행을 제외하고 적용되는데, 초기 3개항은 엑셀 파일에서 데이터가 없는 부분과 헤더이기 때문에 제외한다. 

 

사실상 특별한 내용은 없으므로 기타 코드는 github에 남겨두기로 하고, build.gradle의 종속성과 application.properties만 보겠다. 

 

build.gradle

plugins {
    id 'java'
    id 'org.springframework.boot' version '3.4.1'
    id 'io.spring.dependency-management' version '1.1.7'
}

group = 'zerogod'
version = '0.0.1-SNAPSHOT'

java {
    toolchain {
        languageVersion = JavaLanguageVersion.of(17)
    }
}

configurations {
    compileOnly {
        extendsFrom annotationProcessor
    }
}

repositories {
    mavenCentral()
}

dependencies {
    // Spring Boot 기본 의존성
    implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
    implementation 'org.springframework.boot:spring-boot-starter-security'
    implementation 'org.springframework.boot:spring-boot-starter-web'

    // lombok
    compileOnly 'org.projectlombok:lombok'
    //developmentOnly 'org.springframework.boot:spring-boot-devtools'
    annotationProcessor 'org.projectlombok:lombok'

    // Exel 업로드 및 파싱
    implementation 'org.apache.poi:poi:5.2.3'
    implementation('org.apache.poi:poi-ooxml:5.2.3') {
        exclude group: 'stax', module: 'stax-api' // 충돌 방지
        exclude group: 'commons-codec', module: 'commons-codec' // 중복 방지
    }
    implementation 'org.apache.xmlbeans:xmlbeans:5.1.0'


    // Google OAuth 라이브러리
    implementation 'com.google.auth:google-auth-library-oauth2-http:1.7.0'

    // JWT 생성 및 검증
    implementation 'io.jsonwebtoken:jjwt-api:0.11.5'
    implementation 'io.jsonwebtoken:jjwt-impl:0.11.5'
    implementation 'io.jsonwebtoken:jjwt-jackson:0.11.5' // for JSON processing

    // Jsoup library
    implementation 'org.jsoup:jsoup:1.15.3' // Jsoup 라이브러리

    // DB
    implementation 'org.mariadb.jdbc:mariadb-java-client'

    // Hibernate5Module
    implementation 'com.fasterxml.jackson.datatype:jackson-datatype-hibernate6:2.17.1'
    implementation 'org.jboss.logging:jboss-logging:3.5.0.Final'

    // test 의존성
    testImplementation 'org.springframework.boot:spring-boot-starter-test'
    testImplementation 'org.springframework.security:spring-security-test'
    testRuntimeOnly 'org.junit.platform:junit-platform-launcher'
}

tasks.named('test') {
    useJUnitPlatform()
}

 

application.properties

spring.application.name=android_backend

logging.level.org.hibernate.orm.jdbc.bind=TRACE

spring.datasource.driver-class-name=org.mariadb.jdbc.Driver
spring.datasource.url=jdbc:mariadb://${MYSQL_HOST}:${MYSQL_PORT}/${MYSQL_NAME}?serverTimezone=UTC&characterEncoding=UTF-8
spring.datasource.username=${MYSQL_USER}
spring.datasource.password=${MYSQL_PASSWORD}

spring.jpa.hibernate.ddl-auto=none
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.properties.hibernate.use_sql_comments=true
spring.jpa.properties.hibernate.highlight_sql=true

이는 이전 프로젝트에서도 많이 다루었기에 설명은 넘어가도록 하겠다. 

 

https://github.com/zero0zerogod/android_backend.git

GitHub - zero0zerogod/android_backend