Spring Boot + Apache POI로 엑셀 파일 처리하기

 

📌 들어가며..

이번에 진행하고 있는 프로젝트에서 서울시 공공 자전거(따릉이) 대여소 데이터를 활용해야 했어요.

서울 열린데이터 광장에서 서울시 공공 자전거 대여소 현황 데이터를 1년에 2번 업데이트되는 엑셀 파일 형태로 제공하고 있습니다. 

 

즉, 6개월 단위로 새로운 데이터가 올라올 때마다 수동으로 엑셀을 다운받고, 필요한 데이터를 DB에 적재해야 하는 상황입니다. 

그래서 Spring Boot 프로젝트에 Apache POI를 도입해서 엑셀 데이터를 파싱하고 데이터베이스에 저장하는 기능을 구현했습니다. 

 

이 글에서는 Apache POI가 무엇인지, 실제로 어떻게 구현했는지, 그리고 구현하는 과정에서 만난 문제들을 어떻게 해결했는지 정리해보려고 해요. 

 

 

📌 Apache POI가 뭘까

Apache POI는 Java에서 Microsoft Office 파일 처리의 표준 라이브러리로, 엑셀뿐만 아니라, 워드나 파워포인트 등의 오피스 파일을 다룰 수 있습니다. 

 

Apache POI에서 엑셀 관련해서 제공하는 포맷은 3가지가 있어요.

• HSSF (Horrible Spreadsheet Format)
• XSSF (XML Spreadsheet Format)
• SXSSF (Streaming XSSF)

 

	HSSF	XSSF	SXSSF
파일 형식	.xls (Excep 97 ~ 2003)	.xlsx (Excel 2007 이상)	.xlsx
최대 행 수	65,536	1,048,576	1,048,576
메모리 사용	중간	높음	낮음
읽기 / 쓰기	모두 가능	모두 가능	쓰기 가능, 읽기는 제한적
구현체	HSSFWorkbook HSSFSheet HSSFCell HSSFRow	XSSFWorkbook XSSFSheet XSSFCell XSSFRow	SXSSFWorkbook SXSSFSheet SXSSFCell SXSSFRow

 

 

 

저는 이 세 포맷 중 XSSF를 사용했는데, 그 이유는 아래와 같습니다. 

• HSSF는 .xls 파일 용도이기 때문에 파일 형식이 맞지 않음
• SXSSF는 대용량 파일을 다루고, 파일을 읽는 기능이 제한적임

 

 

📌 구현

1. 의존성 추가

먼저 build.gradle에 POI 의존성을 추가합니다.

implementation 'org.apache.poi:poi-ooxml:5.3.0'

 

2. File Reader 구현 

@Service
@RequiredArgsConstructor
public class StationFileReader {

    private static final String SHEET_NAME = "station state";
    private static final int START_ROW = 5;

    public List<Station> readStationsFromFile(MultipartFile file) {
        try {
            InputStream inputStream = file.getInputStream();
            Workbook workbook = new XSSFWorkbook(inputStream);
            Sheet sheet = workbook.getSheet(SHEET_NAME);

            DataFormatter formatter = new DataFormatter();
            List<Station> stations = new ArrayList<>();

            for (int i = START_ROW; i < sheet.getPhysicalNumberOfRows(); i++) {
                Row row = sheet.getRow(i);
                if (row == null) continue;

                try {
                    String latString = formatter.formatCellValue(row.getCell(4));
                    String lonString = formatter.formatCellValue(row.getCell(5));

                    double latitude = latString.isEmpty() ? 0.0 : Double.parseDouble(latString);
                    double longitude = lonString.isEmpty() ? 0.0 : Double.parseDouble(lonString);

                    Station station = Station.builder()
                            .number(formatter.formatCellValue(row.getCell(0)))
                            .name(formatter.formatCellValue(row.getCell(1)))
                            .address(formatter.formatCellValue(row.getCell(3)))
                            .latitude(latitude)
                            .longitude(longitude)
                            .build();

                    stations.add(station);

                } catch (NumberFormatException e) {
                    throw new ReadFileException("엑셀 파일의 데이터 형식이 올바르지 않습니다.");
                }
            }
            workbook.close();

            return stations;

        } catch (IOException | RuntimeException e) {
            throw new ReadFileException("엑셀 파일 처리 중 오류가 발생했습니다.");
        }
    }
}

 

• StationFileReader 클래스는 업로드된 엑셀 파일(MultipartFile)을 읽어서 Station 객체 리스트로 변환하는 역할을 해요.
• MultipartFile을 InputStream으로 변환 후 XSSFWorkbook에 전달해서 .xlsx 형식의 엑셀 파일을 처리합니다. 
• 그리고, 상수로 설정한 시트 이름으로 지정한 시트를 가져옵니다. 
• 셀 값을 문자열로 안전하게 변환하기 위해 DataFormatter를 사용했습니다. 
• 아래의 엑셀 파일을 보면 실제 대여소 데이터는 6행부터 시작하기 때문에 반복문에서는 5부터 시작합니다. 

• 그리고 반복문은 sheet.getPhysicalNumberOfRows() - 1까지 도는데, getPhysicalNumberOfRows()는 실제로 데이터가 존재하는 행의 개수를 반환합니다. 그래서 중간에 빈 행이 있는 경우 행을 무시하고 잘못 동작되는 경우가 발생할 수 있어요. 
  이럴 땐,getLastRowNum()을 사용해야 합니다. 
  • getLastRowNum() : 중간에 빈 행이 있더라도, 가장 마지막 데이터가 있는 행을 알려줌
  • getPhysicalNumberOfRows() : 중간에 비어있는 행이 있다면 그 행은 세지 않고, 단순히 생성된 행 개수를 알려줌

 

결과

 

2780개의 대여소가 정상적으로 저장되었습니다!

 

 

📌 트러블 슈팅

파일 크기 초과 에러

 

원본 엑셀 파일 크기는 643KB이고, Spring Boot max-file-size의 기본값이 1MB이기 때문에 파일 크기 문제는 없을 거라고 생각했지만,

아래처럼 요청을 보내니 413 Request Entity Too Large 에러가 발생했습니다.

 

원인을 하나씩 확인해보니

1. 파일 크기 자체는 1MB 이하 -> 문제 아님
2. POI는 파일 크기 제한 없음 -> 문제 아님

결국 문제는 파일명이었습니다. 

파일명을 공공자전거 대여소 정보(25.6월 기준).xlsx -> 대여소 정보(25.6월 기준).xlsx 로 바꿨더니 성공적으로 업로드할 수 있었어요. 

 

2785개의 행 중 31개의 행만 읽힌다?

처음에는 엑셀 파일을 열어보니 시트가 하나밖에 없어서, 아래와 같이 첫 번째 시트를 가져오도록 했습니다.

Sheet sheet = workbook.getSheetAt(0); // 첫 번째 시트 가져오기

 

실제로 요청을 보내봤는데, 포맷 변환 시 데이터 형식 오류가 발생했습니다. 

 

혹시나 해서 sheet.getLastRowNum()으로 전체 행 수를 확인해 봤더니, 원래 2785개여야 할 행이 31개만 출력되는 상황이었어요. 

 

알고 보니까 엑셀 파일에 숨겨진 시트가 있어서, 첫 번째 시트를 가져오면 실제로 보여지는 첫 번째 시트를 가져오는 게 아니었던 것입니다.

실제로 엑셀 파일에서 숨기기 취소를 눌러보니 5개의 시트가 숨겨져있었습니다. 

 

그래서 시트를 이름으로 지정해서 가져오도록 코드를 바꿨고, 이후에는 문제없이 모든 행을 읽을 수 있었습니다!

workbook.getSheetAt(0) -> workbook.getSheet("station state");

 

그리고 숨겨진 시트가 실제로 없더라도, 시트를 이름으로 지정해서 가져오는 방식이 훨씬 안전할 것 같아요. 나중에 엑셀 파일 구조가 바뀌거나 시트가 추가/삭제될 경우가 있을 수도 있으니까요!!

 

---

참고

• Apache POI 공식 문서
• POI GitHub