본 게시글에서는 Java + POI 라이브러리를 사용해서 대용량 엑셀 업로드 기능을 구현한 경험을 공유하고자 한다

개요

회사에서 마이그레이션 자동화 프로그램 개발을 진행 했는데

이관 데이터를 엑셀 파일로 업로드 받아서 DB에 저장하는 기능을 개발해야 했다.

기능을 어떻게 구현해야 할지 이것저것 찾아본 결과

아파치 재단의 POI 라이브러리를 통해 엑셀 읽기 기능을 구현할 수 있다는 것을 알게 됐다.

POI 라이브러리를 사용하면 File 객체 또는 InputStream 객체를 통해 엑셀 파일에 접근하고

WorkBook 객체를 생성해서 각 시트, 데이터에 접근할 수 있다.

WorkBook 클래스에는 다양한 메소드가 제공되어서 데이터를 가공해서 DB에 저장하는 로직을 구현할 때 굉장히 편하다는 것을 느꼈다.

하지만, 결론적으로 내가 구현해야 하는 업무 요구사항에는 맞지 않은 방법이었다.

내가 구현해야 하는 기능은 대용량 엑셀 파일을 업로드할 수 있어야 했는데

WorkBook 방식으로 데이터를 가져오면 속도가 매우 느리고, OOM 오류가 높은 확률로 발생했다.

그래서 나는 POI 라이브러리에 SAX Parser 라이브러리를 같이 사용하여 엑셀 대용량 업로드 처리를 구현했다.

자세한 구현 히스토리는 아래 내용을 참고 바란다.

---

WorkBook 으로 엑셀 읽기 기능 구현

POI 라이브러리에는 다양한 컴포넌트들이 있지만 주로 사용하는 컴포넌트들은 아래와 같다.

1. HSSF: 엑셀 버전이 93 ~ 2003 버전인 경우 사용하는 컴포넌트 (예: HSSFWorkbook, HSSFSheet.)
2. XSSF: 엑셀 버전이 2007 버전 이상인 경우 (예: XSSFWorkbook, XSSFSheet.)

(참고로, SXSSF라는 대용량 엑셀을 처리하기 위한 컴포넌트가 있지만 아쉽게도 쓰기만 지원한다)

WorkBook 을 사용하여 엑셀 읽기를 구현하는 방법은 간단하다

우선, 본인 프로젝트에 POI 라이브러리의 종속성을 추가한다.

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>5.2.3</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.2.3</version>
</dependency>

그리고 다루고자 하는 엑셀 버전에 따라 XSSFWorkBook 또는 HSSFWorkbook 클래스를 사용하는데

생성자 인자 값으로 file 또는 inputstream 객체를 전달하면 엑셀파일을 읽어서 workbook 객체를 생성한다.

1. XSSF 컴포넌트 사용 (.xlsx 확장자)

XSSFWorkbook workbook = new XSSFWorkbook(file);// 2007 버전 이상인 경우

2. HSSF 컴포넌트 사용 (.xls 확장자)

HSSFWorkbook workbook = new HSSFWorkbook(file);// 93 ~ 2003 버전인 경우

두개 외에도 엑셀 버전에 상관없이 모두 읽을 수 있는 방법이 있다.

(단, 객체 생성 시 인자값으로 File 객체를 전달해야 한다.)

3. File 객체로 엑셀 파일을 읽어서 workBook 객체 생성 (1번보다는 메모리 사용 적음)

// file : File 객체
Workbook workbook = WorkbookFactory.create(file);

(참고한 블로그 : https://m.blog.naver.com/PostView.naver?isHttpsRedirect=true&blogId=hyoun1202&logNo=220245067954)

가져온 workBook 객체의 사용 방법은 아래와 같이 사용하면 된다.

(이것 외에 사용방법은 구글링하면 매우 많이 나오고, 메소드명만 보더라도 직관적인 이해가 가능하다)

// workbook에서 특정 시트를 가져온다
XSSFSheet sheet = workbook.getSheetAt(0);

// 시트의 모든 로우 조회for (Row row : sheet) {
// 각 로우의 모든 열(cell)을 순회한다.
    Iterator<Cell> cellIterator = row.cellIterator();
}

WorkBook 방식의 읽기 문제점

내가 구현하는 서버는 프론트 단에서 MultipartFile 유형의 파일을 전달받기 때문에, File 객체를 전달하는 workbookFactory 방법은 고려하지 않았고

1번과 2번 방법 중 XSSFWorkbook 방식으로 구현을 진행했다.

개요 부분에서 말했듯 해당 방법은 기능 구현이 매우 쉬웠고,  다양한 메소드를 지원하기 때문에 workBook 객체 내의 데이터 접근이 편리했다. ( ex) 시트명 가져오기, 특정 cell에 접근하기 등)

하지만 XSSFWorkBook 으로 구현된 서버에서 5만건 로우 이상의 엑셀 시트가 포함된 파일을 업로드 했을 때 OOM 오류가 발생하며 서버가 죽는 현상이 반복되었다.

속도가 느려지는 원인을 분석해본 결과, 아래부분에서 엑셀을 읽을 때 엑셀의 모든 내용을 메모리에 로딩해놓은 뒤 WorkBook 객체로 생성하는 방식 때문에 속도 지연이 발생했다.

XSSFWorkbook workbook = new XSSFWorkbook(file);

이는 엑셀 파일이 커질수록 메모리에 올리는 데이터 양도 많아지기 때문에, 높은 확률로 속도가 느려지거나 OOM 오류를 발생시킨다.

---

대용량 엑셀 업로드 방법 (POI + SAX Parser)

OOM 오류 해결을 위해서 여러 방법은 찾아본 결과,  SAX Parser라는 XML 파싱 라이브러리를 함께 사용하면 대용량 처리가 가능하다는 것을 알게 되었다.

이 방법에서는 메모리에 한번에 올리는 WorkBook 객체를 생성하지 않고, SAX Parser를 통해 이벤트 기반으로 엑셀 파일을 순회하며 데이터를 한줄 씩 읽고 이벤트 핸들러 객체의 변수에 담는다.

그리고 읽은 데이터는 flush 하여 버퍼에서 지우는 방식으로 엑셀 파일을 읽는다.

workBook 객체 생성을 위해서 엑셀 파일의 모든 데이터를 메모리에 올리는 방식이 아니기 때문에

대용량 파일에도 매우 빠른 속도로 읽을 수 있다.

👊🏻구현하기

대용량 엑셀 업로드 기능은 아래 두가지 파트로 구성된다. (클래스,메소드 명은 각자 알아서 정하면 됨)

1. SAX Parser의 이벤트 핸들러 클래스인 ExcelSheetHandler 클래스
2. 엑셀 파일을 읽어서 파싱하는 기능을 수행하는 readExcel 메소드

ExcelSheetHandler 구현

SAX Parser가 파싱한 엑셀 데이터를 담기위한 그릇 역할로 각 시트 별 데이터, 헤더 정보를 담는다.

• rows : 시트 별 로우 데이터
• header : 엑셀 헤더 리스트 (첫번째 로우가 저장된다)

import com.amaranth10.interlockmig.constant.ExcelConstant;
import lombok.Getter;
import lombok.Setter;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.binary.XSSFBSheetHandler.SheetContentsHandler;
import org.apache.poi.xssf.usermodel.XSSFComment;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@Getter
@Setter
public class ExcelSheetHandler implements SheetContentsHandler {

// 미사용 ----------private int currentCol = -1;
    private int currRowNum = 0;
    private List<String> row = new ArrayList<>();
// ---------------private List<List<String>> rows = new ArrayList<>();// 실제 엑셀을 파싱해서 담아지는 데이터private List<String> header = new ArrayList<>();
    private List<Map<String, String>> rowsWithHeader = new ArrayList<>();// DB Insert를 위한 변수private String sheetType = "";
    private Integer firstRowIndex = 0;

    @Override
    public void startRow(int arg0) {
        this.currentCol = -1;
        this.currRowNum = arg0;
    }

    @Override
    public void cell(String columnName, String value, XSSFComment var3) {
        int iCol = (new CellReference(columnName)).getCol();
        int emptyCol = iCol - currentCol - 1;

        for (int i = 0; i < emptyCol; i++) {
            row.add("");
        }
        currentCol = iCol;
        row.add(value);
    }

    @Override
    public void headerFooter(String arg0, boolean arg1, String arg2) {
// 사용안합니다.
    }

    @Override
    public void endRow(int rowNum) {
        if (rowNum == 0) {
            header = new ArrayList(row);
        } else {
            if (row.size() < header.size()) {
                for (int i = row.size(); i < header.size(); i++) {
                    row.add("");
                }
            }
            rows.add(new ArrayList(row));
        }
        row.clear();
    }

    @Override
    public void hyperlinkCell(String arg0, String arg1, String arg2, String arg3, XSSFComment arg4) {
// TODO Auto-generated method stub

    }
}

ReadExcel 메소드 구현

해당 메소드는 엑셀 파일을 SAX Parser 로 파싱하고, 각 시트별로 읽어서 ExcelSheetHandler 객체로 만드는 기능을 수행한다.

한줄이면 엑셀 파일을 읽을 수 있는 workBook 방식에 비해 구현의 난이도가 좀더 높은 것 같다.

public List<ExcelSheetHandler> readExcel(InputStream fileInputStream) {
		List<ExcelSheetHandler> sheetHandlers = new ArrayList<>();
		try {
// 엑셀 파일을 OOXML(오픈 XML 문서)형식으로 압축해서 가져온다.
			OPCPackage opc = OPCPackage.open(fileInputStream);

// OOXML 엑셀파일 읽기 위한 객체 생성
			XSSFReader xssfReader = new XSSFReader(opc);

// 엑셀 공통 스타일
			StylesTable styles = xssfReader.getStylesTable();

// 시트 별 데이터 가져오기
			Iterator<InputStream> sheets = xssfReader.getSheetsData();

// 엑셀 파일의 공유 문자열 테이블을 생성한다// 공유 문자열 테이블 : 엑셀 파일에서 N개의 셀에서 중복되는 데이터(문자)가 있을때, 중복되는 동일한 데이터를 공유 문자열 테이블에 넣어서 한번만 읽고 쓰도록 해준다  (각 N개의 셀에는 데이터 자체를 넣지 않고, 해당 데이터가 존재하는 공유 문자열 테이블의 정보를 넣는다)// 메모리를 절약하여 매우 효율적으로 엑셀 파일 READ/WRITE를 할수 있게 도와준다.
			ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(opc);

// 엑셀 시트 별로 읽어서 ExcelSheetHandler 객체 생성 후 리스트 담기if (sheets instanceof XSSFReader.SheetIterator) {
				XSSFReader.SheetIterator sheetIterator = (XSSFReader.SheetIterator) sheets;
				while (sheetIterator.hasNext()) {
					ExcelSheetHandler sheetHandler = new ExcelSheetHandler();
					InputStream inputStream = sheetIterator.next();
					sheetHandler.setSheetType(sheetIterator.getSheetName());

// OOXML 형식의 엑셀 파일을 SAX parser를 사용하여 읽는다.
					InputSource inputSource = new InputSource(inputStream);

// ** 셀 값중 숫자 데이터가 지수형태로 변경되지 않게 해주는 세팅
                    DataFormatter dataFormatter = new DataFormatter();
                    dataFormatter.addFormat("General", new java.text.DecimalFormat("#.###############"));
                    ContentHandler handle = new XSSFSheetXMLHandler(styles, strings, sheetHandler, dataFormatter, false);

                    SAXParserFactory saxParserFactory = SAXParserFactory.newInstance();
					saxParserFactory.setNamespaceAware(true);
					SAXParser parser = saxParserFactory.newSAXParser();
					XMLReader xmlReader = parser.getXMLReader();
					xmlReader.setContentHandler(handle);
					xmlReader.parse(inputSource);

					inputStream.close();
					sheetHandlers.add(sheetHandler);
				}
			}

			opc.close();

		} catch (Exception e) {
			e.printStackTrace();
// 에러 발생했을때 하시고 싶은 TO-DO
		}

		return sheetHandlers;

	}

✏️ 코드 상세 설명

OPCPackage.open (fileInputStream)

엑셀 파일의 InputStream 객체를 XML 형식으로 파싱하기 위해 OOXML 형식으로 압축한다.

InputStream fileInputStream = multipartFile.getInputStream()
OPCPackage opc = OPCPackage.open(fileInputStream);

XSSFReader(opc)

OOXML 형식의 엑셀 파일 읽기 위한 XSSFReader 객체를 생성한다

• OOXML 엑셀 파일을 각 파트(시트 데이터, 시트 스타일 ..)별로 읽을 수 있게 해서 Sax 가 파싱하기 유용하게 도와준다.
• 적은 메모리로 ooxml 엑셀 파일을 읽을 수 있게 도와준다

XSSFReader xssfReader = new XSSFReader(opc);

XSSFReader.getStylesTable()

XSSFReader 객체로부터 엑셀 공통 스타일 정보를 가져온다

// 엑셀 공통 스타일
StylesTable styles = xssfReader.getStylesTable();

// 시트 별 데이터 가져오기
Iterator<InputStream> sheets = xssfReader.getSheetsData();

XSSFReader.getSheetData()

XSSFReader 객체로부터 엑셀 데이터를 시트 별로 가져온다

• 리턴 타입은 InputStream 형으로 스트림 형태이다

// 엑셀 공통 스타일
StylesTable styles = xssfReader.getStylesTable();

// 시트 별 데이터 가져오기
Iterator<InputStream> sheets = xssfReader.getSheetsData();

ReadOnlySharedStringsTable(opc)

엑셀 파일의 공유 문자열 테이블 생성

• 엑셀 파일에서 N개의 셀에서 중복되는 데이터(문자)가 있을때, 중복되는 동일한 데이터를 공유 문자열 테이블에 넣어서 한번만 읽고 쓰도록 해준다 (각 N개의 셀에는 데이터 자체를 넣지 않고, 해당 데이터가 존재하는 공유 문자열 테이블의 정보를 넣는다)
• 메모리 절약 효과

ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(opc);

XMLReader 객체 생성

SAXParser 파싱을 위해 xmlReader 객체를 생성한다

SAXParserFactory saxParserFactory = SAXParserFactory.newInstance();
saxParserFactory.setNamespaceAware(true);
SAXParser parser = saxParserFactory.newSAXParser();
XMLReader xmlReader = parser.getXMLReader();

데이터 파싱 및 이벤트 핸들러 객체에 저장

파싱에 필요한 정보를 담아서 ContentHandler 객체를 생성하고

xmlReader 객체를 통해 xml 형태의 시트 데이터를 파싱한 뒤 이벤트 핸들러 객체에 저장한다.

ExcelSheetHandler sheetHandler = new ExcelSheetHandler();
sheetHandler.setSheetType(sheetIterator.getSheetName());
ContentHandler handle = new XSSFSheetXMLHandler(styles, strings, sheetHandler, dataFormatter, false);

xmlReader.setContentHandler(handle);
xmlReader.parse(inputSource);

sheetHandlers.add(sheetHandler);

---

DB 저장을 위한 전처리 (선택사항)

DB 테이블로 Insert 하기 위해서 몇가지 전처리 과정이 필요하다 (실제 저장 과정은 다루지 않는다)

엑셀 양식 , 빈 row 제거

• 엑셀 데이터 외의 양식을 제거한다.
• 엑셀 필수값이 존재하지 않는 경우 해당 로우는 삭제한다.
• 엑셀 시트가 비어있다면 해당 시트는 삭제한다.

public void removeInvalidExcelData(List<ExcelSheetHandler> excelDataBySheet) throws Exception {
// 엑셀 양식 삭제
		for (ExcelSheetHandler sheet : excelDataBySheet) {
			Integer firstRowIndex = sheet.getFirstRowIndex();
			sheet.setHeader(sheet.getRows().get(firstRowIndex - 1));
			sheet.setRows(sheet.getRows().subList(firstRowIndex, sheet.getRows().size()));
		}

// 엑셀 필수값 (mig_type, mig_key, type, pk값 (doc_id, c_aikeycode 등)이 null인 경우 해당 로우 제외
		for (ExcelSheetHandler excelSheetHandler : excelDataBySheet) {
			excelSheetHandler.getRows().removeIf(item -> StringUtil.ifNullThenEmpty(item.get(0)).equals("")
					|| StringUtil.ifNullThenEmpty(item.get(1)).equals("")
					|| StringUtil.ifNullThenEmpty(item.get(2)).equals("")
					|| StringUtil.ifNullThenEmpty(item.get(3)).equals(""));
		}
// 시트에 모든 데이터가 NULL이면 삭제
		excelDataBySheet.removeIf(item -> item.getRows().size() == 0);
	}

시트 데이터 헤더-값 쌍으로 묶기

SAX Parser를 통해 파싱한 데이터는 List<List> 형태의 데이터이다.

이 형태로는 DB Insert하기에 곤란하기 때문에

헤더 값과 Cell의 값을 묶은 List<Map<String,String>> 형태로 만들어 준다.

excelDataBySheet.forEach(excelData -> excelUtil.mappingCellAndHeader(excelData));

...

public void mappingCellAndHeader(ExcelSheetHandler excelSheetHandler) {
		KlagoLog.logInfo(requestInfo, "### mappingCellAndHeader start");
// 현재 시트의 row 데이터List<List<String>> rows = excelSheetHandler.getRows();
// 현재 시트의 헤더List<String> header = excelSheetHandler.getHeader();
// 현재 시트의 row 데이터 (헤더 정보 포함된)List<Map<String, String>> rowsWithHeader = new ArrayList<>();

// Cell 별로 헤더 정보 매핑
		for (List<String> row : rows) {
			Map<String, String> headerAndCell = new HashMap<>();
			for (int i = 0; i < row.size(); i++) {
				headerAndCell.put(header.get(i), row.get(i));
			}
			rowsWithHeader.add(headerAndCell);
		}
		excelSheetHandler.setRowsWithHeader(rowsWithHeader);
		KlagoLog.logInfo(requestInfo, "### mappingCellAndHeader end");
	}

---

마무리

대용량 엑셀 파일 읽기 구현을 위해 POI 라이브러리와 SAX Parser 를 함께 사용하여 엑셀 읽기을 구현했다.

해당 방법으로 구현한 결과 속도 측면에 WorkBook 방식에 비해 확실한 속도 차이를 보여주었다. (15만건 로우 기준 13초 소요됨)

하지만 구현 난이도가 있기 때문에 대용량 엑셀 파일이 아니고 업로드 처리 시 빠른 속도를 요구하는 시스템이 아니라면 업무 효율을 위해 WorkBook 방식을 사용하는게 좋을 것 같다.