Apache POI로 Excel 업로드/다운로드

2. 핵심 원리

2.1 POI 구성 — 어떤 jar 가 무엇을 하는가

poi-5.3.0.jar            ── HSSF (.xls, 바이너리 BIFF8) + 공통 인터페이스 ss.usermodel (Workbook, Sheet, Row, Cell)
poi-ooxml-5.3.0.jar      ── XSSF (.xlsx) + SXSSF (스트리밍 쓰기) + 이벤트 API
poi-ooxml-lite-5.3.0.jar ── XSSF 가 쓰는 OOXML 스키마 클래스(XMLBeans 로 생성). full 버전은 훨씬 큼
xmlbeans, commons-compress, commons-io, commons-collections4, commons-math3, commons-codec, log4j-api, SparseBitSet
                          ── 의존성. 하나라도 빠지면 NoClassDefFoundError

코드는 org.apache.poi.ss.usermodel.* 인터페이스(Workbook, Sheet, Row, Cell, CellStyle)로 작성하고, 구현 클래스는 new XSSFWorkbook() 처럼 생성 지점에서만 등장하게 합니다. 그러면 .xls 지원이나 SXSSF 전환이 한 줄 변경으로 끝납니다.

POI 는 log4j-api 로 로그를 남깁니다. log4j-core 가 없으면 "no Log4j 2 configuration file / provider" 경고가 뜨므로, main 첫 줄에서 단순 로거로 고정합니다.

System.setProperty("log4j2.loggerContextFactory", "org.apache.logging.log4j.simple.SimpleLoggerContextFactory");

2.2 객체 모델 — Workbook → Sheet → Row → Cell

Workbook (파일 하나)
 ├─ CellStyle[]  Font[]  DataFormat        ← 워크북 자원. 시트/셀이 인덱스로 참조
 ├─ Sheet "회원목록"
 │   ├─ Row 0  ── Cell 0 "이름"  Cell 1 "이메일"  Cell 2 "나이" ...
 │   ├─ Row 1  ── Cell 0 "김철수" Cell 1 ...
 │   ├─ (Row 2 없음: null)                  ← 사용자가 지운 행. getRow(2) == null
 │   └─ Row 3  ── Cell 0 "이영희" (Cell 1 없음: null) Cell 2 28.0
 └─ Sheet "작성안내"

for (Row row : sheet) 는 물리적으로 존재하는 행만 순회합니다. 중간에 비어 있는 행이 있으면 건너뛰므로, "3행에 오류가 있습니다"라고 알려주려면 row.getRowNum() + 1 을 써야지 루프 카운터를 쓰면 안 됩니다. 이 레슨의 검증기는 for (int r = 0; r <= getLastRowNum(); r++) 인덱스 루프로 null 행을 직접 다룹니다.

row.getCell(c) 는 MissingCellPolicy 를 받는 오버로드가 있습니다. getCell(c, RETURN_BLANK_AS_NULL) 은 서식만 남은 빈 셀도 null 로, CREATE_NULL_AS_BLANK 는 없는 셀을 빈 셀 객체로 돌려줍니다. 어느 쪽이든 "null 이 올 수 있다"를 헬퍼 한 곳에서 처리하면 호출부는 깨끗해집니다.

2.3 셀 타입 — 엑셀 화면과 실제 저장 타입은 다르다

엑셀에서 "34" 라고 보이는 셀은 사용자가 어떻게 입력했느냐에 따라 NUMERIC 34.0 일 수도 STRING "34" 일 수도 있습니다(셀 서식이 "텍스트"였거나 앞에 ' 를 붙였거나 다른 시스템이 문자열로 내보냈거나). 업로드 검증에서 나이 컬럼을 getNumericCellValue() 로만 읽으면 절반의 파일에서 예외가 납니다.

타입을 먼저 보고 분기하거나, 다음 절의 DataFormatter 로 문자열로 통일한 뒤 파싱합니다.

사용자가 본 것       저장된 것                    raw 값
   34          →   NUMERIC 34.0             →   34.0 (Double)
   34          →   STRING "34"              →   "34"
   2026-09-08  →   NUMERIC 46273.0 + yyyy-mm-dd →  2026-09-08T00:00 (LocalDateTime)
   46273       →   NUMERIC 46273.0 (서식 없음) →  46273.0
   =B1*2       →   FORMULA "B1*2", 캐시 24690  →  24690.0

2.4 DataFormatter — "화면에 보이는 값"으로 읽기

DataFormatter.formatCellValue(cell, evaluator) 는 셀 타입과 서식을 모두 반영해 엑셀에 표시되는 문자열을 돌려줍니다.

숫자 12345 는 "12345", #,##0"원" 서식의 1234567 은 "1,234,567원", 날짜는 "2026-09-08", 수식은 evaluator 로 계산한 결과, 빈 셀은 "". 업로드 검증처럼 "사용자가 본 그대로를 문자열로 받아 우리가 파싱"하는 용도에는 이것이 정답입니다.

주의할 점은 DataFormatter 가 서식을 그대로 따른다는 것입니다. #,##0 서식이면 "49,000" 이 오므로 Long.parseLong 전에 쉼표를 제거하거나, 숫자가 필요한 컬럼은 NUMERIC 이면 getNumericCellValue() 를 우선 쓰는 하이브리드가 안전합니다. 예제의 ExcelReader.raw() 와 text() 가 그 두 경로입니다.

2.5 쓰기 — 스타일은 워크북 자원이다

CellStyle, Font, DataFormat 은 셀의 속성이 아니라 워크북에 등록되는 자원이고, 셀은 그 인덱스를 가리킵니다. 따라서:

❌ 셀 10만 개 × createCellStyle()  →  스타일 10만 개 등록  →  64,000 한계 초과 → 엑셀이 "복구" 대화상자
✅ 워크북당 스타일 6~7개 생성 → 10만 셀이 인덱스로 공유 → styles.xml 은 몇 줄

CellStyle 은 값이 아니라 참조이므로 cell.getCellStyle().setFont(...) 처럼 셀에서 꺼낸 스타일을 수정하면 그 스타일을 공유하는 모든 셀이 바뀝니다. 변형이 필요하면 newStyle.cloneStyleFrom(base) 로 복제합니다.

자주 쓰는 서식 코드:

cell.setCellValue(LocalDate) 는 숫자(일련번호)로 저장됩니다. 날짜 서식 스타일을 함께 주지 않으면 엑셀에서 46273 으로 보입니다.

2.6 시트 기능 — 너비, 병합, 틀 고정, 필터, 유효성

수식 셀은 POI 가 계산하지 않습니다. 엑셀은 파일을 열 때 재계산하지만, 미리보기 앱이나 다른 라이브러리는 캐시된 값을 그대로 보여 주므로 wb.getCreationHelper().createFormulaEvaluator().evaluateAll() 을 저장 직전에 호출합니다.

2.7 대용량 다운로드 — SXSSFWorkbook

XSSFWorkbook 은 셀 하나를 XMLBeans 객체 여러 개로 들고 있어 셀당 수백 바이트를 씁니다. 10만 행 × 6열 = 60만 셀이면 힙 400~500MB 입니다. 동시 다운로드 요청 3개면 서버가 죽습니다.

XSSFWorkbook:   createRow → 힙에 누적 ─────────────────────────────▶ write() 시 전체 직렬화
                [row1][row2][row3] ... [row100000]  (모두 힙)

SXSSFWorkbook(100):
                createRow → 힙에 최근 100행만 ─▶ 101번째 행 생성 시 가장 오래된 행을 임시 파일에 XML 로 flush
                [row99901 ... row100000] (힙)      /tmp/poi-sxssf-sheet123.xml (디스크)
                write() 시 임시 XML 을 zip 으로 복사

"먼저 데이터를 다 쓰고 나중에 합계 행을 위에 넣는다"처럼 위로 돌아가는 작업은 불가능합니다. 합계는 미리 계산해 헤더 아래에 쓰거나 맨 아래에 씁니다.

2.8 대용량 업로드 — 이벤트 API 와 행 단위 검증

읽기에는 SXSSF 같은 스트리밍 usermodel 이 없습니다. 선택지는 둘입니다.

usermodel (XSSFWorkbook):  zip 해제 → sheet1.xml 전체를 DOM(XMLBeans) 으로 → Row/Cell 객체 생성 → 힙에 전부
                            10만 행 = 400MB+, 그러나 코드가 단순

이벤트 API (XSSFReader + SAX):  sheet1.xml 을 SAX 로 훑으며 <row>, <c> 태그마다 콜백
                                 메모리 = 현재 행 하나. 코드는 핸들러 구현 필요

POI 는 XSSFSheetXMLHandler 라는 기성 핸들러를 제공합니다. startRow / cell(ref, formattedValue) / endRow 콜백만 구현하면 되고, 공유 문자열·서식 적용은 핸들러가 처리합니다(변형 3). 빈 셀은 콜백이 오지 않으므로 셀 참조(C5)에서 열 인덱스를 꺼내 빈 자리를 채워야 합니다.

실무 기준: 업로드 파일에 행 수 상한(예: 1만 행)을 두고 usermodel 로 읽되 검증은 행 단위로 하는 것이 대부분의 시스템에 맞습니다. 상한을 둘 수 없는 정산 파일 수십만 행은 이벤트 API 로 갑니다. 어느 쪽이든 "행 하나 → 검증 → DTO 또는 에러" 구조는 같으므로 검증 로직은 List<String> 한 행을 받도록 분리해 두면 리더를 바꿔도 재사용됩니다.

2.9 업로드 검증 패턴 — 행 번호가 붙은 에러 리포트

업로드 XLSX
   │
   ▼
[1] 헤더 검증 ── 컬럼 이름/순서가 양식과 다르면 즉시 반환 (본문 검증 무의미)
   │
   ▼
[2] 행 루프 (r = 1 .. lastRowNum)
   ├─ 완전 빈 행 → skip
   ├─ 필수값: 이름, 이메일
   ├─ 타입: 나이 = 정수, 가입일 = 날짜 셀 또는 yyyy-MM-dd
   ├─ 범위/허용값: 0 ≤ 나이 ≤ 150, 등급 ∈ {BASIC, SILVER, GOLD}, 미래 날짜 금지
   ├─ 중복: 이메일 (파일 안 + 필요하면 DB)
   └─ 오류 없으면 → Member DTO, 있으면 → RowError(엑셀 행 번호, 컬럼, 입력값, 사유)
   │
   ▼
[3] 결과: (members, errors)
      errors 비어 있음 → DB 일괄 저장
      errors 있음     → 에러 리포트 XLSX 로 반환. 저장은 하지 않음(전체 또는 없음)

핵심은 첫 오류에서 멈추지 않고 끝까지 모아서 한 번에 알려주는 것입니다. 500 행 중 12 곳이 틀렸는데 한 번에 하나씩 알려주면 사용자는 12 번 업로드해야 합니다. 에러 목록은 화면에도 보여 주고 XLSX 로도 내려줍니다. 사용자는 그 파일을 옆에 두고 원본을 고칩니다.

2.10 템플릿 다운로드

업로드 오류의 절반은 양식 문제입니다(컬럼 순서, 날짜 형식, 등급 오타). 서버가 빈 양식을 내려주면 오류가 줄어듭니다. 템플릿에 넣을 것: 굵은 헤더(틀 고정), 회색 예시 행, 등급 드롭다운(데이터 유효성), 작성 안내 시트. 헤더 검증(2.9 의 [1])은 이 템플릿의 헤더와 문자열 비교이므로 헤더 배열을 상수 하나로 공유해 두 곳이 어긋나지 않게 합니다.

2.11 메모리/성능 실측 (100,000 행 × 6 열, 예제 7)

시간은 실행 환경(이 측정은 다른 작업과 병렬로 돌아간 느린 노트북)에 따라 크게 달라지지만 힙의 차이는 환경과 무관합니다. 쓰기는 SXSSF, 대용량 읽기는 이벤트 API 가 메모리를 상수로 만듭니다.