[JAVA] 대용량 엑셀 업로드 기능 구현 (POI, SAX Parser)

개요

회사에서 신규 프로젝트로 마이그레이션 자동화 애플리케이션 개발을 진행하게 되었는데,

 

마이그레이션 API 내에 대용량 엑셀 파일을 읽어서 DB에 저장하는 기능을 개발하게 되었다.

 

기능 구현을 위해 구글링을 열심히 해보니, 아파치 POI 라이브러리를 사용해 엑셀을 읽을수 있다는 것을 알게 되었다. 

 

POI 라이브러리에는 WorkBook 이라는 클래스가 존재했고, 해당 클래스를 사용하여 엑셀을 읽고 간편하게 엑셀 데이터를 가져올 수 있었다. 

 

하지만 결론적으로 나는 이 WorkBook 방식을 사용하여 개발한 것을 갈아엎기로 했다.

 

이유는 속도 이슈 때문이었다.  

 

내가 구현해야했던 어플리케이션은 주로 대용량 엑셀 파일을 업로드 해야 했다. 

 

그러나 WorkBook 방식으로 구현한 기능은 엑셀 데이터가 N만건 이상만 되어도 속도가 매우 느려졌고, OOM 오류가 발생하기도 했다. 

 

그래서 나는 WorkBook의 대안으로 SAX Parser 라이브러리(자바 API에서 제공하는 XML 파싱 라이브러리)를 같이 사용하여 대용량 업로드 기능을 구현했다.   

 

이번 게시글에서는 어떤 방식으로 엑셀 읽기 기능을 구현했고,

왜 WorkBook 방식이 아닌 SAX Parser 방식을 적용했는지 내가 느낀 장단점에 대해 공유하고자 한다. 

 

 

엑셀 읽기 기능 구현 방법

1. 종속성 추가 (pom.xml)

먼저 POI 라이브러리를 사용해야 하기 때문에 pom.xml에 dependency를 추가한다.

<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>

 

2. Controller 구성

클라이언트로부터 오는 요청을 처리할 controller를 생성한다.

해당 Controller는 비동기 처리 되는 Service를 호출한 뒤 바로 클라이언트에게 응답을 리턴한다.

@RestController
public class ExcelController {

    @RequestMapping(value = "/excelUpload", method = RequestMethod.POST)
    @ResponseBody
    public Object excelUpload(@RequestPart(value = "file") MultipartFile multipartFile) throws Exception {

        // 엑셀 업로드 서비스 로직 수행 (Async)
        try {
            asyncService.excelUpload(multipartFile.getInputStream());
        } catch (TaskRejectedException e) {
        	// 에러 처리 로직
        } catch (Exception e){
        	// 에러 처리 로직
        }

        // 결과 리턴
        APIResult result = new APIResult();
        result.setResultCode(200); // 결과 코드(int)
        result.setResultData(true); // 결과 값(Object)
        return result;
    }
}

 

3. Service 구성  

엑셀 업로드 서비스 로직을 수행하는 Service 클래스를 생성하고, 대용량 처리를 위해 비동기 처리되게 구현한다.

 

📌 Service 로직 비동기 처리

서비스 클래스 중 비동기 처리할 메소드 위에 @Async 어노테이션을 붙이면 해당 메서드는 비동기 호출 방식으로 동작하게 된다.  

 

주의할 점으로 @Async에 TaskExecutor의 이름을 지정해야 하는데, 만약 어떤 TaskExecutor를 사용할 것인지 지정하지 않으면 기본으로 SimpleAsyncTaskExecutor가 사용된다.

(TaskExecutor : 독립적인 스레드에서 실행되어야 하는 객체를 실행하는 메서드를 제공하는 인터페이스)

 

이 SimpleAsyncTaskExecutor는 스레드 풀을 사용하지 않고 클라이언트의 요청이 발생할 때 마다 매번 새로운 스레드를 생성하기 때문에 스레드 생성 비용이 발생하게 된다. 

 

그래서 스레드 풀을 사용하는 ThreadPoolTaskExecutor을 Bean으로 등록하고, @Async 어노테이션에 해당 Bean 이름을  지정해서 ThreadPoolTaskExecutor 을 사용하도록 해야한다.

@Service
@Transactional
public class ExcelAsyncService {

    /**
    * 엑셀 업로드 (비동기)
    */
    @Async("excelAsyncExecutor")
    public void excelUpload(InputStream fileInputStream) throws Exception {

        // .. 서비스 로직 

        // 엑셀 파일 읽어오기 
        List<ExcelSheetHandler> excelDataBySheet = excelUtil.readExcel(fileInputStream);

        // .. 서비스 로직 

        // 엑셀 DB 저장
        excelUploadService.insertExcelData(excelDataBySheet);

    }

}

 

📌 ThreadPool 설정

ThreadPoolTaskExecutor를 생성하고 Spring Bean으로 등록하기 위해 Config 클래스를 하나 생성한다. 

 

Config 클래스에 @Configurtaion 과 @EnableAsync 어노테이션을 붙이고, ThreadPoolTaskExecutor를 등록할 메서드를 생성하고 Bean으로 등록해준다.

 

그리고 해당 Bean 이름을 @Async 어노테이션에 지정했던 TaskExecutor 이름으로 설정한다. 

 

등록하는 TaskExecutor 에는 다음과 같이 ThreadPool에 대한 설정을 할 수 있다.

1. corePoolSize  : 스레드 풀의 기본 스레드 개수
2. maxPoolSize : 스레드 풀이 모두 사용 중일 때 증설되는 최대 스레드 개수
3. queueCapacity : 스레드 작업 큐
4. threadNamePrefix : 스레드의 이름 지정

 

만약 설정한 기본 스레드 개수(CorePoolSize) 만큼 스레드가 모두 할당 되고 스레드풀 작업 큐에 요청이 모두 쌓인 경우에는 설정한 최대 스레드 수 만큼 스레드 개수를 늘린다.  (만약 작업 큐 크기를 설정하지 않았다면 기본으로 Integer.MAX_VALUE 크기로 작업 큐가 생성된다.)

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.scheduling.annotation.EnableAsync;
import org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor;

import java.util.concurrent.Executor;

@Configuration
@EnableAsync
public class AsyncConfig {

    // 1. 스레드풀에 corePoolSize 만큼 스레드를 만들어서 비동기 작업 수행
    // 2. 스레드풀의 스레드가 모두 사용중이라면, queueCapacity 만큼 스레드풀의 큐에 작업을 쌓아놓는다.
    // 3. 만약 queueCapacity의 큐가 모두 사용중이라면 , maxPoolSize만큼 스레드를 증설한다.
    @Bean(name = "excelAsyncExecutor")
    public Executor getAsyncExecutor() {
        ThreadPoolTaskExecutor executor = new ThreadPoolTaskExecutor();
        executor.setCorePoolSize(5); // 기본 스레드 수
        executor.setMaxPoolSize(20); // 최대 스레드 수
        executor.setQueueCapacity(50); // 스레드풀의 작업큐 크기
        executor.setThreadNamePrefix("InterlockMig-Async-"); // Thread 접두사
        return executor;
    }
}

 

4. 엑셀 파일 읽기 기능 구현 (POI + SAX Parser 사용)

그 다음 실제로 엑셀 파일을 읽는 기능을 readExcel 메서드와 ExcelSheetHandler 이벤트 핸들러를 통해 구현한다.

📌 readExcel 메소드

@Component
public class ExcelUtil {
    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()) {
                InputStream inputStream = sheetIterator.next();

                // OOXML 형식의 엑셀 파일을 SAX parser를 사용하여 읽는다.
                InputSource inputSource = new InputSource(inputStream);
				
                // XML 파서 생성
                SAXParserFactory saxParserFactory = SAXParserFactory.newInstance();
                saxParserFactory.setNamespaceAware(true);
                SAXParser parser = saxParserFactory.newSAXParser();
                XMLReader xmlReader = parser.getXMLReader();

				// XML 파서의 이벤트 핸들러 등록 (ContentHandler) 
                ExcelSheetHandler sheetHandler = new ExcelSheetHandler();
                sheetHandler.setSheetType(sheetIterator.getSheetName());
            	
                DataFormatter dataFormatter = new DataFormatter();
                dataFormatter.addFormat("General", new java.text.DecimalFormat("#.###############")); // ** 셀 값중 숫자 데이터가 지수형태로 변경되지 않게 해주는 세팅
                
                ContentHandler handle = new XSSFSheetXMLHandler(styles, strings, sheetHandler, dataFormatter, false);

                xmlReader.setContentHandler(handle);
                
                // 엑셀 시트별로 읽기 (엑셀 파일을 한줄 씩 읽는 이벤트 기반 파싱 방식 사용)
                xmlReader.parse(inputSource);
                inputStream.close();
                
                // 읽은 결과 저장 
                sheetHandlers.add(sheetHandler);
                }
            }

            opc.close();

        } catch (Exception e) {
            e.printStackTrace();
        }
        
        return sheetHandlers;

    }
}

 

readExcel 메소드는 엑셀 파일에서 데이터를 읽어서 메모리에 올리는 기능을 담당한다. 

 

먼저 엑셀 파일에 접근하기 위한 inputStream 을 OOXML 형식으로 생성하고, 해당 inputStream 객체를 통해 엑셀 데이터를 읽어오기 위한 기능을 제공하는 XSSFReader 객체를 생성한다.

- inputStream : 사용자가 업로드한 엑셀파일은 디스크에 임시 파일로 저장되는데, 해당 파일에 접근하기 위한 인터페이스로 multipartFile 객체가 생성되고 엑셀 파일에 접근하기 위한 inputStream 객체가 제공된다.
- OOXML 형식 : 압축된 XML 파일로 .xlxs 엑셀 파일이 이에 해당한다. XML 파일보다 용량이 가볍다
- XSSFReader : OOXML 타입의 InputStream으로 부터 엑셀 데이터를 읽기 위한 여러 기능을 제공한다. 

 

그 다음 XSSFReader 객체를 통해 디스크에 임시 저장된 엑셀 파일을 메모리에 올리는 과정을 진행한다.

 

엑셀 파일은 시트별로 루프를 돌며 읽어오고, SAX Parser를 이용해서 XML 형태의 시트 데이터를 1개의 Row씩 읽어서 시트 별 객체 내에 리스트로 저장한다.

- SAX Parser는 엑셀 파일을 메모리에 한번에 올려서 처리하는 것이 아닌 한줄 씩 순차적으로 읽어가는 이벤트 기반의 파싱 방식을 사용한다.

- 그래서 SAX Parser를 생성할 때는 한줄 씩 읽을 때 사용되는 메서드와 읽은 데이터를 저장하는 기능을 수행하는 ContentHandler를 구현하는 이벤트 핸들러를 등록해줘야 한다.

- 코드에서는 ExcelSheetHandler라는 클래스를 생성해서 ContentHandler를 구현했고, SAX Parser 생성 시 해당 ExcelSheetHandler를 등록하도록 구현했다. 

 

 

readExcel 메소드의 코드 상세 설명은 아래를 참고하길 바란다. 

InputStream -> OPCPackage

클라이언트로 받은 InputStream 읽어서, 압축된 XML 형식인 OOXML 형식으로 엑셀을 메모리에 올린다.

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

XSSFReader 객체 생성

OOXML 형식의 엑셀 데이터를 읽기 위한 XSSFReader 객체를 생성한다

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

XSSFReader xssfReader = new XSSFReader(opc);

엑셀 시트 스타일 가져오기

XSSFReader 객체로부터 엑셀 공통 스타일 정보를 가져온다. 이 스타일 정보는 엑셀을 시트별로 읽을 때 사용된다

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

시트별로 엑셀 데이터 가져오기

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

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

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

ReadOnlySharedStringsTable(opc)

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

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

ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(opc);

SAXParser 생성

OOXML 형식의 엑셀 데이터를 시트 별로 읽는 SAXParser 객체를 생성한다

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

이벤트 핸들러 객체 등록

SAX Parser에 이벤트 핸들러 객체(ContentHandler 객체)를 등록한다.

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);

 

엑셀 시트 읽기 & 결과 저장

xmlReader.parse(inputSource);

sheetHandlers.add(sheetHandler);

 

📌 ExcelSheetHandler

SAX Parser가 엑셀을 읽을 때 사용되는 이벤트 핸들러 객체로, SAX Parser가 엑셀을 읽을 때 필요한 설정과 사용되는 메서드를 정의하고 읽은 데이터를 저장한다.

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 String sheetType = "";
    private List<Map<String, String>> rowsWithHeader = new ArrayList<>();
    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) {
        // 사용 X
    }

    @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

    }
}

 

5. 엑셀 데이터 전처리 (Option)

내가 구현한 시스템은 엑셀 업로드 시 아래와 같이 지정된 엑셀 양식을 통해 업로드하게 제한을 두었다. 

 

그래서 엑셀을 읽으면 위의 양식까지 모두 읽어오기 때문에 해당 부분을 제거 하는 전처리 로직을 추가했고, 

 

또한 읽어온 데이터가 List<List<String>> 형식이었기 때문에 DB Insert를 위해 도메인 객체로 변경하는 전처리 로직을 추가했다. 

 

📌 엑셀 시트 별 데이터 시작 로우 설정

각 시트별로 엑셀 양식을 제거한 실질적인 데이터 시작 로우 번호를 지정한다.

public void initExcelConfig(List<ExcelSheetHandler> excelDataBySheet) throws Exception {
    // 데이터 시작 로우 인덱스 설정
    for (ExcelSheetHandler sheet : excelDataBySheet) {
        switch (sheet.getSheetType()) {
            case ExcelConstant.ARCHIVE_SHEET_TYPE:
            	sheet.setFirstRowIndex(ExcelConstant.ARCHIVE_FIRST_ROW_INDEX);
            	break;
            case ExcelConstant.UNITWORK_SHEET_TYPE:
            	sheet.setFirstRowIndex(ExcelConstant.UNITWORK_FIRST_ROW_INDEX);
            	break;
            // ... 생략
            case ExcelConstant.PREVDOC_COMMENT_SHEET_TYPE:
            	sheet.setFirstRowIndex(ExcelConstant.PREVDOC_COMMENT_FIRST_ROW_INDEX);
            	break;
            default:
            	throw new Exception("### removeInvalidExcelData, there is no valid sheet type");
        }
    }
}

 

📌 엑셀 양식 및 빈값 제거

위에서 설정한 엑셀 실제 데이터 시작 로우를 기준으로 시트 별로 양식을 제거하고, 헤더 정보를 ExcelSheetHandler 객체에 저장한다.

 

그리고 만약 엑셀 필수값이 비어있는 로우가 있다면 해당 로우는 삭제하고,  시트의 모든 데이터가 비어있다면 해당 시트를 제거하도록 구성했다. 

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);
}

 

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

시트 별 엑셀 데이터를 담는 rows 변수와 헤더를 담는 header 변수를 조합하여, DB Insert를 하기 위한 List<Map<String,String>> 형태의 변수를 생성한다.

public void mappingCellAndHeader(ExcelSheetHandler excelSheetHandler) {
    // 현재 시트의 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);
}

 

 

위의 순서대로 엑셀 읽기 및 전처리 기능을 구현한 뒤  각자 요구사항 별로 서비스 로직을 추가하고 DB Insert를 진행하면 된다. (DB Insert 부분은 다루지 않는다)

 

WorkBook 방식과 SAX Parser 방식 차이점

WorkBook 방식으로 엑셀을 읽을 때 가장 큰 장점으로는 편리하다는 것이다. 

 

위에서 알아봤던 복잡한 SAXParser 방식과 다르게, File / InputStream 객체만 전달하면 간단하게 WorkBook 객체로 엑셀 파일을 읽어온다. 

XSSFWorkbook workbook = new XSSFWorkbook(file);

 

그리고 WorkBook 클래스는 엑셀을 다루는 다양한 메소드를 제공하고 있어서 서비스 로직 구현에 편리하다는 장점이 있다.

 

하지만 이 방식은 WorkBook 객체를 생성할 때 메모리에 한번에 올리는 방식 때문에 5만건 정도 이상의 엑셀 파일을 업로드하려고 하면 속도가 느려지고 OOM 에러가 발생했다.

 

그에 비해 SAX Parser는 압축된 OOXML 형식으로 엑셀을 메모리에 올려놓고, 데이터를 한줄 씩 읽으면서 읽은 데이터는 flush해서 버퍼에서 지우는 방식을 사용하기 때문에 WorkBook 방식에 비해 대용량의 엑셀 파일을 처리할 수 있었다.

 

다만 이 방식은 구현이 비교적 어렵고, WorkBook에서 제공하는 것과 같은 편의 기능이 거의 없어서 입맛에 맞게 커스터마이징 하기는 어렵다는 느낌을 받았다. 

 

결론

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

 

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

 

하지만 이 방법은 구현하기가 비교적 어렵고 기능이 제한적이기 때문에, 추후 유지보수 할 것을 생각하면 대용량 엑셀 파일을 업로드해야 하는 시스템이 아니라면 WorkBook을 사용하는 것이 좋을 것 같다.