1. 개요
대용량 엑셀 데이터를 수기로 검수하거나 필터링하는 과정을 모두 API로 만들어 자동화하였다.
이 과정에서 HashSet과 SAX(Simple API for XML)을 활용하였고, 어떠한 알고리즘을 사용해 진행하였으며 기존 방식과 어떤 점에서 성능 차이가 발생하였는지 파악한 내용을 기록하였다.
기존 방식과 개선 방식의 차이점부터 찬찬히 살펴보고, 사용한 알고리즘까지 알아보도록 하자.
2. 기존 방식 - IOUtils/WorkbookFactory (XSSFWorkbook 등)
기존 IOUtils.setByteArrayMaxOverride와 WorkbookFactory(XSSFWorkbook)를 활용하는 방식은 Excel 파일을 한 번에 메모리로 전체 로드해서 처리한다.
내부적으로 처리 과정을 자세하게 살펴보면,
- WorkbookFactory.create(inputStream) → 내부적으로 XSSFWorkbook 생성
- XSSFWorkbook은 모든 시트, 셀, 스타일, 포맷 정보까지 전부 "메모리"로 읽음
- 이후 Sheet sheet = workbook.getSheetAt(0) 등으로 시트 접근 후 반복 처리한다.
사실상 "단순 반복" "순환" 처리와 같다.
이 방식은 크기가 조금만 커져도 매우 느려지고, 메모리에 올려놓고 처리를 진행하기에 컴퓨터 설정의 영향을 많이 받을 수 밖에 없다.
- Excel 크기가 커질수록 해당 엑셀 데이터가 JVM heap에 고스란히 올라간다.
- 수천/수만 행이면 OutOfMemoryError 또는 GC 쓰레기로 성능 폭락하고 컴퓨터 성능에 영향을 미친다(위험성).
2. 개선 방식 - SAX 기반 Streaming (SheetContentsHandler + XSSFSheetXMLHandler)
이 방식은 엑셀 파일을 스트리밍으로, 즉 줄 단위로 읽고 바로 처리하는 방식이다.
POI의 ooxml-schemas + xmlbeans + XSSFReader, XSSFSheetXMLHandler 조합으로 사용하는데, 결국 핵심은 컴퓨터 사양에 영향을 받지 않고 단순 순환식 접근이 아닌 Stream방식으로 데이터를 읽는 방식으로 처리한다는 점이다.
간략히 대용량 데이터 엑셀 파일을 읽는 과정을 정리하자면 다음과 같다.
- .xlsx 파일은 내부적으로 압축된 XML 묶음(zip)으로 읽어온다.
- OPCPackage로 Excel 압축 해제한다.
- XSSFReader로 시트 XML 스트림 접근한다(엑셀 데이터를 일단 한번 훑는 것으로 이해하면 된다).
- XMLReader (SAX 파서)를 통해 읽어온 데이터를 기반으로 이벤트를 발생시킬 준비를 한다(이벤트 핸들러).
- 최종저으로 이벤트를 발생시키고, 이벤트가 발생할 때마다 SheetContentsHandler가 한 줄씩 처리한다.
즉, 데이터를 읽어오는 방식과 처리하는 방식이, 한 메모리에 모든 데이터를 올려두고 처리하는 방식이 아닌 한줄 씩 처리하는 Stream 기반 방식이기에, 성능적으로 매우 빠르고 용량 상관없는 데이터 읽기가 가능하다.
참고로 Xlsx 파일은 다수의 xml 형태가 모인 하나의 zip파일로 간주되어지기에, 최초 읽어올때는 zip파일을 해석하는 형태로 불러오는 점을 기억하자.
아래의 예시를 살펴보도록 하자.
/*
* 필터링을 위해 매칭파일에 있는 설비목록을 추출하는 메소드
* */
public static HashSet<String> collectMatchedPoplntSetList(InputStream popLntInputStream, String targetColumnName) throws Exception{
OPCPackage pkg = OPCPackage.open(popLntInputStream);
XSSFReader reader = new XSSFReader(pkg);
SharedStringsTable sst = (SharedStringsTable) reader.getSharedStringsTable();
StylesTable styles = reader.getStylesTable();
SAXExcelPoplntListCollector handler = new SAXExcelPoplntListCollector(targetColumnName);
DataFormatter formatter = new DataFormatter();
XMLReader parser = XMLReaderFactory.createXMLReader();
ContentHandler sheetHandler = new XSSFSheetXMLHandler(
styles,
null,
sst,
handler,
formatter,
false
);
// 1. 이벤트 핸들러 지정
parser.setContentHandler(sheetHandler);
// 2. 엑셀 내부에서 실제 시트를 꺼내 InputStream으로 변환
try(InputStream sheetStream = reader.getSheetsData().next()) {
// 3. 여기서부터 "실제 파싱"이 시작됨
parser.parse(new InputSource(sheetStream));
}
return handler.getPoplntSet();
}위에서 핵심은 파서정보에 이벤트핸들러 정보를 담고, Stream 방식을 통해 파서 이벤트를 발생하면서 한 줄씩 처리한다는 점이다.
// 1. 이벤트 핸들러 지정
parser.setContentHandler(sheetHandler);
// 2. 엑셀 내부에서 실제 시트를 꺼내 InputStream으로 변환
try(InputStream sheetStream = reader.getSheetsData().next()) {
// 3. 여기서부터 "실제 파싱"이 시작됨
parser.parse(new InputSource(sheetStream));
}
return handler.getPoplntSet();실질적인 로직은 핸들러에서 처리하므로, 처리한 데이터들은 handler에서 받아오면 된다.
| 방식 | XSSFWorkbook (workbook.getSheetAt(...)) | SAX 방식 (SheetContentsHandler) |
|---|---|---|
| 구조 | DOM 방식 (전체 메모리에 로드) | SAX 방식 (이벤트 기반 스트리밍) |
| 메모리 사용량 | 파일 전체를 메모리에 올림 → 고용량일수록 위험 | 한 줄씩 처리 → 메모리 적게 사용 |
| 속도 | 파일 크기 커질수록 급격히 느려짐 | 일정한 처리 속도 유지 |
| 유연성 | 셀 병합, 수식 등 복잡한 조작 가능 | 단순 읽기 위주, 병합/스타일 어려움 |
| 적합한 용도 | 복잡한 서식 유지가 필요한 문서 작업 | 대용량 파일의 빠른 단순 데이터 처리 |
기존 DOM과 Stream 방식의 차이점을 알게된다면, 향후 대용량 데이터 처리에 대한 기본적인 방향과 Stream의 기본 개념에 대해 잘 이해하고 적용할 수 있을 것이라 생각하였다.
이 차이점을 잘 숙지해두면 좋겠다.
3. 수기 작업 자동화 #1 - 필터링 1단계
자동화 작업의 1단계는 특정 대상을 다른 특정 기준으로 필터링하는 작업인데, 기본적으로 List보다는 hashSet이 성능과 시간복잡도 상으로 훨씬 유리하기 때문에 hashSet 자료구조를 사용하였다.
HashSet<String> matchedPoplntSetList = CollectProvider.collectMatchedPoplntSetList(matchedInputStream, targetColumnName);여기서 필터링한 정보들을 "HashSet"으로 받아오는데,
@Override
public void startRow(int rowNum) {
}
@Override
public void cell(String cellReference, String formattedValue, XSSFComment comment) {
//int columnIndex = this.convertColumnToIndex(cellReference.replaceAll("\\d+", ""));
int columnIndex = new CellAddress(cellReference).getColumn();
String popLntItem = formattedValue.trim();
if(isHeaderRow && targetColumnName.equals(popLntItem)){
targetColumnIndex = columnIndex;
}else if(!isHeaderRow && columnIndex == targetColumnIndex){
poplntSet.add(popLntItem);
}
}
@Override
public void endRow(int rowNum) {
isHeaderRow = false;
}내부적으로 SheetContentsHandler에서 위와 같은 로직으로 필터링한다.
- 맨 처음 행은 헤더(Column 정보)이고, 여기서 선별이 필요한 데이터 컬럼 정보(ColumnIndex)를 가져온다(targetColumnName).
- 각 row를 순환하고 cell 정보를 가져올때, 위에서 선별한 columnIndex와 일치할때 해당 정보를 HashSet에 넣는다.
3-1. HashSet vs List
HashSet과 List의 내부 구현 로직을 살펴보면 성능적으로 크게 차이가 날 수 밖에 없음을 알 수 있다.
- List
List.Contains는 "Value"가 들어있는지(=equal) 모든 요소를 순차 탐색하여 확인하고, 탐색이 늦게 이루어질 수록 그만큼 소요 시간이 길어진다(O(N)).
for (T item : list) {
if (item.equals(target)) {
return true;
}
}- HashSet
HashSet.Contains는 마찬가지로 요소가 들어있는지(equal) 확인하는데, 순차 탐색이 아닌 해시값 기반 "선택"이고 해당 hash값은 요소의 값을 기반으로 만들어지기에 value를 저장한다기보다는 Key값을 저장한다는 표현이 더 어울릴 것 같다.
int index = hash(value) % array.length;
return array[index].equals(value); // O(1) 비교위에서 볼 수 있듯이 해쉬값을 기반으로 요소의 위치를 바로 알아낸다. 시간 복잡도가 배열의 크기에 상관없이 O(1)이며, list에 비해 강력한 성능적 이점을 가진다.
3-2. SheetContentsHandler
최종적으로 HashSet을 변수에 할당하기 위해 Getter 어노테이션을 사용하여 가져올 수 있도록 하였다.
@Getter
public class SAXExcelPoplntListCollector implements SheetContentsHandler {try(InputStream sheetStream = reader.getSheetsData().next()) {
parser.parse(new InputSource(sheetStream));
}
return handler.getFilteredPoplntMapList();이때 SheetContentsHandler는 Override를 제외한 필요 기능들을 사용자가 재량으로 구현할 수 있다는 점을 기억하자.
4. 수기 작업 자동화 #2 - 필터링 2단계
다음으로 위에서 필터링한 내역들을 기준으로 다른 데이터 내역을 최종 필터링하는 단계이다.
여기서는 단순히 문자열을 저장하는 것이 아니라, 헤더(Column) 정보와 이에 따른 모든 데이터(Value) 정보까지 담아야 하는게 관건이다.
/*
* 인증서발급대상 설비목록에서 1차매칭파일에 존재하는 내역만 필터링하여 추출하는 메소드
* */
public static List<Map<String, String>> filterMatchedPoplntList(InputStream matchedInputStream, InputStream poplntInputStream, String targetColumnName) throws Exception {
HashSet<String> matchedPoplntSetList = CollectProvider.collectMatchedPoplntSetList(matchedInputStream, targetColumnName);
OPCPackage pkg = OPCPackage.open(poplntInputStream);//
XSSFReader reader = new XSSFReader(pkg);
SharedStringsTable sst = (SharedStringsTable) reader.getSharedStringsTable();
StylesTable styles = reader.getStylesTable();
SAXExcelPoplntListFilter handler = new SAXExcelPoplntListFilter(matchedPoplntSetList);
DataFormatter formatter = new DataFormatter();
XMLReader parser = XMLReaderFactory.createXMLReader();
ContentHandler sheetHandler = new XSSFSheetXMLHandler(
styles,
null,
sst,
handler,
formatter,
false
);
parser.setContentHandler(sheetHandler);
try(InputStream sheetStream = reader.getSheetsData().next()) {
parser.parse(new InputSource(sheetStream));
}
return handler.getFilteredPoplntMapList();
}위에서
HashSet<String> matchedPoplntSetList = CollectProvider.collectMatchedPoplntSetList(matchedInputStream, targetColumnName);을 통해 받아온 hashSet을
SAXExcelPoplntListFilter handler = new SAXExcelPoplntListFilter(matchedPoplntSetList);와 같이 ContentsSheetHandler로 넘겨주어 필터링된 데이터들을 얻을 수 있도록 구성하였다.
최종적으로 필터링하는 내부 로직은
@Override
public void startRow(int rowNum) {
cellValues.clear();
}
@Override
public void cell(String cellReference, String formattedValue, XSSFComment comment) {
int columnIndex = new CellAddress(cellReference).getColumn();
while (cellValues.size() <= columnIndex) {
cellValues.add(""); // 빈 값으로 패딩
}
// 정확한 위치에 셀 값 삽입
cellValues.set(columnIndex, formattedValue);
}
@Override
public void endRow(int rowNum) {
if(isHeaderRow) {
headerList.addAll(cellValues);
isHeaderRow = false;
}else{
int targetIndex = headerList.indexOf(targetColumnName);
if(targetIndex != -1) {
String code = cellValues.get(targetIndex);
if(matchedPoplntSet.contains(code)) {
Map<String, String> rowMap = new HashMap<>();
for(int i = 0 ; i < headerList.size() ; i++) {
rowMap.put(headerList.get(i), cellValues.get(i));
}
filteredPoplntMapList.add(rowMap);
}
}
}
}위와 같은데
- headerList라는 ArrayList에 헤더정보를 순차적으로 넣는다. Cell 정보가 없어 건너뛰는 경우를 고려하여 CellValues의 size와 columnIndex의 관계를 연산하여 빈 값으로 패딩하는 과정을 Cell에 넣어주었다.
- 이에 매핑되는 value들을 cellValues라는 ArrayList에 넣어주었다. 이에 따라 각각 헤더정보와 Cell 정보(데이터)들은 순차적으로 매핑이된 상태로 삽입이 되었고, 이 정보를 그대로 Map<String, String>에 넣는다.
이 정보를 모든 row에 진행해서 filteredPoplntMapList에 결과를 누적한다.
(*final로 변수를 선언하였기에 다른 객체의 할당 걱정없이 단일 ArrayList에 결과가 계속 누적되는 것을 보장할 수 있다)
[
{ "설비코드": "A001", "소재지": "서울특별시" }, // index 0
{ "설비코드": "A002", "소재지": "부산광역시" } // index 1
]최종적으로 저장된 filteredPoplntMapList를 보면 위와 같은 구조로 되어있다.
5. 수기 작업 자동화 #3 - 데이터 쓰기 단계
데이터 쓰기 역시 대용량 데이터 상황을 고려하여 DOM방식이 아닌 Stream 방식(SXSSFWorkbook)으로 쓰기 작업을 진행해주어야 했다.
이때 InputStream을 컨트롤러에서 하나만 넘겨받기에 SXSSFWorkbook으로 타입캐스팅을 하는 과정에서 엑셀을 읽어올 수 없는 connection 문제가 발생하였다.
이 문제를 해결하기 위해 헤더정보만 읽어오는 읽기용 inputStream, 데이터를 쓰는 쓰기용 inputStream을 분리해서 구현하게 되었다.
5-1. inputStream 분리
public Workbook writePoplntDataToExcel(InputStream poplntFormatFileStreamForheaderReading, InputStream poplntFormatFileStreamForValueWriting, List<Map<String, String>> resultList) throws IOException {일단 inputStream을 forReading / forWriting으로 분리해주었다.
5-2. 읽기용 inputStream 구현
기존 데이터 읽기 쓰기를 모두 Workbook (DOM) 방식으로 처리하는 방식에서 읽기용/쓰기용 inputStream을 나누어 처리하는 방안을 생각해 낸 근거는 포멧 데이터의 용량이다.
"포멧" 데이터를 사용해서 어떤 포멧에 데이터를 넣어야 하는지 header 정보를 List에 넣는 과정이 필요한데, 이때 "포멧" 데이터 용량이 크지 않아서 읽기/쓰기 inputStream을 분리할 수 있었다.
/*
* 1단계 : 헤더정보 읽어오기
* - 포멧 파일은 용량이 크지 않으므로 workook
* */
Workbook poplntFormatExcelFileForReading = WorkbookFactory.create(poplntFormatFileStreamForheaderReading);
Sheet sheetForReading = poplntFormatExcelFileForReading.getSheetAt(0);
List<String> headerEngList = headerEngList(sheetForReading);위와 같이 용량이 크지 않은 데이터에 대해 일단 "Workbook", 즉 DOM 방식으로 불러오도록 하였다.
5-3. 쓰기용 inputStream 구현
/*
* 2단계 : 데이터 쓰기
* - 용량이 큰 데이터 쓰기 작업은 SXSSWorkbook
* */
XSSFWorkbook workbook = new XSSFWorkbook(poplntFormatFileStreamForValueWriting);
SXSSFWorkbook poplntFormatExcelFileForWriting = new SXSSFWorkbook(workbook);
Sheet sheetForWriting = poplntFormatExcelFileForWriting.getSheetAt(0);최종적으로 데이터 쓰기는 SXSSFWorkbook에 하기 위해 쓰기용 inputStream을 workbook으로 바꾼후 SXSSFWorkbook으로 변환해주었다.
SXSSFWorkbook은 데이터를 읽은 상태인 workbook(XSSWorkbook)을 반드시 인자로 받아야 하며, 이처럼 나누어서 구성을 해야 inputStream을 소모함으로 인한 nullPointerException을 방지할 수 있다.
5-4. 최종 데이터 쓰기 로직 구현
이를 바탕으로 데이터 쓰기 로직을 구현하였다.
inputStream의 제약이 너무 많기도 하고, 다른 곳에서 빌더패턴 등을 써버리면 NullPointerException이 발생하게 되어 리팩토링을 최대한 진행하였는데도 한계가 많았다.
일단은 구현은 하여서 다행인 것 같다.
int currentRowNum = 1;
for(Map<String, String> rowMap : resultList){
Row row = sheetForWriting.createRow(currentRowNum++);
for(int colIndex = 0; colIndex < headerEngList.size(); colIndex++){
String engHeader = headerEngList.get(colIndex);
//시도영문명은 시도하면서 이미 데이터 반영 완료하였으므로 건너뛰기
if (SIDO_EN.equals(engHeader)) {
continue;
}
//시군구영문명은 시군구하면서 이미 데이터 반영 완료하였으므로 건너뛰기
if (SIGUNGU_EN.equals(engHeader)) {
continue;
}
//발전소영문명은 발전소명하면서 이미 데이터 반영 완료하였으므로 건너뛰기
if (POPLNT_TERM_EN.equals(engHeader)){
continue;
}
String value = rowMap.entrySet().stream() //국문명 컬럼으로 매핑된 데이터(필터링) 정보들
//.peek(entry -> System.out.println("key : value " + entry.getKey() + ": " + entry.getValue())) //로그 확인용
.filter(entry -> {
String engName = ExcelColumnEnum.getByKorName(entry.getKey());
return engName != null && engName.equals(engHeader);
})
//.map(Map.Entry::getValue)
.map(entry -> {
if(SIGUNGU.equals(ExcelColumnEnum.getByKorName(entry.getKey())) || SIDO.equals(ExcelColumnEnum.getByKorName(entry.getKey())))
return this.getFilteredValue(entry.getValue());
else
return entry.getValue();
})
.findFirst()
.orElse("");
row.createCell(colIndex).setCellValue(value);
//시도 : 시도영문명과 함께 데이터 삽입
if(engHeader.equals(SIDO)) {
String sidoEngName = String.valueOf(engDataReaderRepository.findById(value).map(TbReGreenRegionMeta::getRegionNameEn).orElseThrow(() -> new RuntimeException("시도 ["+value+"]에 해당하는 시도영문명이 존재하지 않습니다. 메타데이터를 추가하십시오.")));
row.createCell(colIndex + 1).setCellValue(sidoEngName);
}
//시군구 : 시군구영문명과 함께 데이터 삽입
if(engHeader.equals(SIGUNGU)) {
String sigunguEngName = String.valueOf(engDataReaderRepository.findById(value).map(TbReGreenRegionMeta::getRegionNameEn).orElseThrow(() -> new RuntimeException("시군구 ["+value+"]에 해당하는 시군구영문명이 존재하지 않습니다. 메타데이터를 추가하십시오.")));
row.createCell(colIndex + 1).setCellValue(sigunguEngName);
}
//발전소 : 발전소영문명과 함께 데이터 삽입
if(engHeader.equals(POPLNT_TERM)) {
String poplntTermEngName = KoreanRomanizer.romanize(value, KoreanCharacter.Type.Kea);
row.createCell(colIndex + 1).setCellValue(poplntTermEngName);
}데이터 쓰기 로직은 간단하다.
순차적으로 옮겨야할 데이터가 저장되어있는 resultList을 순환하면서 데이터쓰기 작업을 진행한다.
이때 데이터가 올바른 컬럼에 매핑하기 위해 Enum에서 국문 컬럼명을 영문 컬럼명으로 매핑여부를 먼저 확인하고, 매핑이 된다면 해당 Value를 Map의 EntrySet으로 부터 불러와서 그 데이터를 저장한다.
String value = rowMap.entrySet().stream() //국문명 컬럼으로 매핑된 데이터(필터링) 정보들
//.peek(entry -> System.out.println("key : value " + entry.getKey() + ": " + entry.getValue())) //로그 확인용
.filter(entry -> {
String engName = ExcelColumnEnum.getByKorName(entry.getKey());
return engName != null && engName.equals(engHeader);
})
//.map(Map.Entry::getValue)
.map(entry -> {
if(SIGUNGU.equals(ExcelColumnEnum.getByKorName(entry.getKey())) || SIDO.equals(ExcelColumnEnum.getByKorName(entry.getKey())))
return this.getFilteredValue(entry.getValue());
else
return entry.getValue();
})
.findFirst()
.orElse("");이 쓰기 작업은 한 row에 대해 모든 포멧 컬럼명이 매핑될때까지 지속(=colIndex)하고, 전체 과정은 resultList가 모두 매핑될때까지 지속(=rowMap)한다.
6. 수기 작업 자동화 #4 - 데이터 반환
SXSSFWorkbook poplntFormatExcelFileForWriting최종적으로 쓰여진 데이터는 SXSSFWorkbook의 형태이지만, Workbook을 implements하고 있는 형태라서 별도 타입 캐스팅없이 바로 반환이 가능하였다.
7. 결론
메모리에 한번에 엑셀 데이터를 올려두는 기존 DOM방식에서, 순환 형태로 처리하는 SAX/SXSS는 Streaming 에 대한 기초개념을 이해하는데 매우 효과적으로 쓰일 것 같다.
| 항목 | SXSSFWorkbook | SAX (XSSFReader + XMLReader) |
|---|---|---|
| 기본 목적 | 쓰기 작업에서 메모리 절약 | 읽기 작업에서 메모리 절약 |
| Streaming 방식 | O (쓰기 시 일정 개수의 row만 메모리에 유지) | O (읽기 시 row 단위로 이벤트 처리) |
| 전체 데이터를 메모리에 올리는가? | ❌ 일부만 메모리에 유지 (기본 100개 row) | ❌ 한 줄씩 읽어 처리 |
| 지원 포맷 | .xlsx (XSSF 기반) | .xlsx (XSSF 기반) |
| 작업 대상 | 쓰기(write) 중심 | 읽기(read) 중심 |
| API 스타일 | High-level POI API (Sheet.createRow, Cell.setValue) | Low-level XML 이벤트 기반 |
| Thread-safe? | 상대적으로 안전 | 직접 구현에 따라 달림 |
이 두 Streaming 방식을 기억해서 나중에 시스템 확장 시 적극적으로 적용해보도록 한다.
또한 ArrayList가 아닌 HashSet을 이용해서 시간 복잡도를 획기적으로 줄일 수 있었기에, 내부적으로 어떠한 과정으로 구현을 하였는지 찬찬히 깊게 이해하도록 하자.
