자바에서 대용량 엑셀 데이터를 읽어들이는 ExcelParser를 만들어보자.

https://suzuworld.tistory.com/301

자바 프로젝트에서 엑셀 데이터를 읽어들이는 방법(Apache POI Workbook , Sax Parsing)

 

지난 글에서 엑셀 데이터를 자바 프로젝트에 불러와 다루는 방법에 대해 간단히 살펴보았다. 

당시에는 한 개의 엑셀 데이터를 csv 파일로 변환하는 작업만 하면 됐기 때문에 SAX 방식에 대해 제대로 살펴보지 않았는데 이번에 회사의 신규 프로젝트에서 엑셀 데이터를 파싱 할 일이 또 생겨 관련 내용을 정리해야겠다는 생각이 들었다. 특히나 이번 프로젝트의 경우 유저로부터 엑셀 데이터를 업로드받는 기능이 들어가므로 에러 없이 처리가 될 수 있게 확실한 파악 및 정리가 필요하기도 했다. 이 글을 읽기 전에 위의 글을 가볍게 읽고 오면 빠른 이해가 가능하다.

 

 이 글에서 설명하는 모든 방식은 Apache POI를 기반으로 한다. 먼저 Apache POI 라이브러리에서 알아야 할 필수 기능에 대해 살펴보자. Apache POI 라이브러리에서는 Excel 파일 형식에 따라 HSSF와 XSSF로 구분되며, 각각 다른 클래스를 사용한다.

 

HSSF 클래스

• Excel 97(-2007) 파일 형식을 지원하며, HSSFWorkbook과 HSSFSheet 등이 있다.

 

XSSF 클래스

• Excel 2007 OOXML(.xlsx) 파일 형식을 지원하며, XSSFWorkbook과 XSSFSheet 등이 있다.

 

SXSSF 클래스

• XSSF의 API 호환 스트리밍 확장으로, 거대한 스프레드시트를 생성할 때 힙 공간이 제한되어 있는 경우 사용한다.
• SXSSFWorkbook과 SXSSFSheet 등이 있으며, 슬라이딩 윈도우 내의 행에 대한 액세스를 제한하여 메모리 사용량을 줄인다. 
• 쓰기에만 사용할 수 있기 때문에 만들어진 엑셀 데이터를 자바 프로젝트에 업로드하는 과정에서 발생하는 OOM을 해결할 수 없다.
  • 대용량 엑셀 데이터 읽기에서는 SAX 방식을 활용해야 한다.

 

XSSF 클래스를 이용

• 대용량이 아니라면 개별 데이터를 쉽게 다룰 수 있는 등 유연하고 확장적인 Apache POI의 라이브러리를 그대로 이용하는 것이 좋다.
• HSSF의 경우 옛날 엑셀 파일이니 2007년 이후 .xlsx 확장자를 사용하는 엑셀 데이터를 다루는 XSSF 클래스의 사용 방법을 간단히 알아보자.

 

우선 Apache POI 의존성을 추가해야한다.

 

의존성 추가

// https://mvnrepository.com/artifact/org.apache.poi/poi
implementation group: 'org.apache.poi', name: 'poi', version: '5.2.3'

• Gradle 기준이다.
• 버전은 적절히 선택하면 된다.

 

예시 코드

FileInputStream file = new FileInputStream("/액셀/파일/경로/액셀파일명.xlsx"); // (1) 파일 읽기

XSSFWorkbook workbook = new XSSFWorkbook(file); // (2) 엑셀 파일 파싱(대용량 시 OOM 발생 원인)

XSSFSheet sheet = workbook.getSheetAt(0); // (3) 첫번째 시트 불러오기

for (int i = 0; i <= sheet.getLastRowNum(); i++) {  
// 시트의 마지막 행의 숫자를 가져와 처음부터 마지막 행까지 for 문을 돌면서 한 행씩 가져오는 반복문을 실행

    XSSFRow row = sheet.getRow(i); 

    …(코드)
    
    // example
    row.getCell(0).getNumericCellValue()) 
    // 가져온 행의 첫번째 cell이 숫자형일 경우 알맞은 자바 타입으로 변경
    
    row.getCell(1).getStringCellValue()
    // 가져온 행의 두번째 cell이 문자형일 경우 알맞은 자바 타입으로 변경
}

• 지난 글에서 소개한 방식을 가져왔다.
• (1) FileInputStream으로 엑셀 데이터를 자바 프로젝트에 가져와, (2) 엑셀 데이터를 파싱 한다.
• 이때 한 번에 엑셀 파일을 메모리에 올려두기 때문에 대용량 엑셀 데이터의 경우 Out Of Memory 에러가 발생하는 것이다.
• 만약 대용량이 아닌 경우에는 (3)처럼 시트를 가져온다. 여러 개의 시트일 경우 반복문 등을 사용해서 가져오면 될 것 같다.
• sheet에는 여러 메서드가 존재한다. 그중 getLastRowNum() 메서드는 마지막 Row의 Number를 가져온다. 이를 반복문과 함께 사용하여 모든 행을 읽어 들여 개발자 마음대로 처리가 가능하다.
• 예를 들어 위의 코드처럼 인덱스 0번(1번 Row)부터 LastRowNum - 1(마지막 Row)까지 반복문을 돌며 하나의 Row를 가져와 각 cell을 get 메서드를 통해 알맞은 자바 타입으로 변경하여 원하는 방식으로 데이터를 처리하는 방법이 가장 일반적인 것 같다.

 

 

SAX 방식을 이용

• Apache POI의 라이브러리 클래스를 그대로 이용하지는 않아 개별 데이터를 다루는 데 제한적이지만 대용량 데이터를 에러 없이 빠르게 업로드할 수 있는 장점이 있다.
• Apache POI 라이브러리와 XML 데이터를 다루는 SAX Parser 방식을 믹스한 것이다.

 

의존성 추가

// https://mvnrepository.com/artifact/org.apache.poi/poi
implementation group: 'org.apache.poi', name: 'poi', version: '5.2.3'
implementation group: 'org.apache.poi', name: 'poi-ooxml', version: '5.2.3' //sax 사용

 

예시 코드

import lombok.Getter;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.util.XMLHelper;
import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.XSSFComment;
import org.xml.sax.ContentHandler;
import org.xml.sax.InputSource;
import org.xml.sax.XMLReader;

import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * SAX(Simple API for XML) 파싱 방식을 이용하는 방법으로 대용량 엑셀 파일을 다뤄도 OOM이 일어나지 않게 한다.(나눠서 처리하기 때문)
 */
@Slf4j
public class ExcelSheetHandler implements XSSFSheetXMLHandler.SheetContentsHandler {

    @Getter
    private List<String> header = new ArrayList<>();

    @Getter
    private List<List<String>> rows = new ArrayList<>();

    private List<String> row = new ArrayList<>();

    private int checkedCol = -1;

    private int startRowNum = 4;

    private int currentRowNum = startRowNum - 1;

    @Override
    public void startRow(int currentRowNum) {
        this.checkedCol = -1; 
        this.currentRowNum = currentRowNum;
    }
    
    @Override
    public void endRow(int currentRowNum) {
        if (currentRowNum < startRowNum - 1) {}
        else if (currentRowNum == startRowNum - 1) { 
            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 cell(String columnName, String value, XSSFComment comment) {
        int currentCol = new CellReference(columnName).getCol(); 
        int emptyColumnCount = currentCol - checkedCol - 1;

        for (int i = 0; i < emptyColumnCount; i++) {
            row.add(""); 
        }

        row.add(value); 
        checkedCol = currentCol; 
    }

    public static ExcelSheetHandler readExcel(FileInputStream excelFile) {

        ExcelSheetHandler excelSheetHandler = new ExcelSheetHandler();

        try {
            OPCPackage opcPackage = OPCPackage.open(excelFile);

            XSSFReader xssfReader = new XSSFReader(opcPackage);
            
            StylesTable stylesTable = xssfReader.getStylesTable();
            
            ReadOnlySharedStringsTable data = new ReadOnlySharedStringsTable(opcPackage);


            InputStream sheetStream = xssfReader.getSheetsData().next();

            InputSource sheetSource = new InputSource(sheetStream);

            ContentHandler handler = new XSSFSheetXMLHandler(stylesTable, data, excelSheetHandler, false);

            XMLReader sheetParser = XMLHelper.newXMLReader();

            sheetParser.setContentHandler(handler);

            sheetParser.parse(sheetSource);

            sheetStream.close();
        } catch (Exception e) {
            log.error("엑셀 파일 읽기 에러 :", e.getCause(), e);
            throw new RuntimeException(e);
        }

        return excelSheetHandler;
    }
}

• Apache POI 라이브러리와 Sax 방식의 데이터 처리를 혼합하기 위해서는 XSSFSheetXMLHandler.SheetContentsHandler를 구현할 클래스가 필요하다.
• 위는 이를 구현한 클래스이다.
• 이제 하나하나 뜯어서 살펴보자.

 

SheetContentsHandler

   /**
    * This interface allows to provide callbacks when reading
    * a sheet in streaming mode.
    *
    * The XSLX file is usually read via {@link XSSFReader}.
    *
    * By implementing the methods, you can process arbitrarily
    * large files without exhausting main memory.
    */
   public interface SheetContentsHandler {
      /** A row with the (zero based) row number has started */
      void startRow(int rowNum);

      /** A row with the (zero based) row number has ended */
      void endRow(int rowNum);

      /**
       * A cell, with the given formatted value (may be null),
       * and possibly a comment (may be null), was encountered.
       *
       * Sheets that have missing or empty cells may result in
       * sparse calls to <code>cell</code>. See the code in
       * <code>poi-examples/src/main/java/org/apache/poi/xssf/eventusermodel/XLSX2CSV.java</code>
       * for an example of how to handle this scenario.
       */
      void cell(String cellReference, String formattedValue, XSSFComment comment);

      /** A header or footer has been encountered */
      default void headerFooter(String text, boolean isHeader, String tagName) {}

      /** Signal that the end of a sheet was been reached */
      default void endSheet() {}
   }
}

• 액셀의 각 Row의 첫 셀을 읽기 전 초기값을 세팅하는 startRow 메서드
• 해당 Row의 각 셀을 읽을 때 사용하는 cell 메서드
• Row의 마지막 셀을 모두 읽고 난 후 빈 셀에 해당하는 부분을 공백처리하는 endRow 메서드 등을 활용한다.
• 간단하게 요약하면 액셀에 입력되어 있는 nXn 정보를 2차원 ArrayList에 넣는 작업이다. 
• 이를 구현할 ExcelSheetHandler를 만들어보자.
• 쉬운 이해를 위해 예시로 사용할 엑셀 데이터를 먼저 살펴보겠다.

 

예시 엑셀 데이터

통합식품영양성분DB_음식_20230509 - 출처 <https://various.foodsafetykorea.go.kr/nutrient/>

• 이 엑셀 데이터는 4번째 행이 Header(컬럼명)이며,
• 5번째 행부터 실제 데이터가 각 셀에 입력되어 있다.

 

변수 선언

@Slf4j
public class ExcelSheetHandler implements XSSFSheetXMLHandler.SheetContentsHandler {

    @Getter
    private List<String> header = new ArrayList<>();
    // 엑셀 파일의 header 정보가 들어감

    @Getter
    private List<List<String>> rows = new ArrayList<>();
    // header를 제외한 실제 데이터(1 row)가 요소로 들어감

    private List<String> row = new ArrayList<>();
    // 한 행에 대한 리스트(cell이 요소로 들어감)

    private int checkedCol = -1;
    // cell 메서드에서 빈 cell을 체크하기 위해 사용할 cell 번호로 초기값은 -1이어야 함.
    // -1에 특별한 의미는 없고 단지 0보다는 작아야 한다.
    // check 된 column임을 표시해야 하기 때문에 인덱스 값으로 존재할 수 있는 숫자는 들어가면 안된다.

    private int startRowNum = 4;
    // 실제 데이터가 시작되는 Row Number의 인덱스 값. ex) 데이터가 5행부터 시작이면 -1한 값인 index 4

    private int currentRowNum = startRowNum - 1; // index 3
    // 현재 읽고 있는 cell의 Row Number로, 실제 데이터가 시작되는 RowNumber에서 -1을 해 초기값으로 header를 가리키게 한다. ex) 5행 -> 4행
	...
}


// 파일 읽고 핸들러를 통해 데이터 파싱
FileInputStream excelFile = new FileInputStream("/문서/경로/통합식품영양성분DB_음식_20230509.xlsx");
ExcelSheetHandler excelSheetHandler = ExcelSheetHandler.readExcel(excelFile);

• 내가 원했던 방식은 엑셀 데이터를 자바의 FileInputStream을 통해 Handler에 파라미터로 넣게 되면 핸들러에서는 내가 미리 지정한 Header와 실제 데이터가 시작되는 행의 위치를 인식하여 파싱 하는 것이었다. (위 코드 맨 아래 부분 참고)
• 따라서 위의 엑셀 데이터를 파싱 한다고 하면 startRowNum을 4(index)로 두어 5행부터 데이터가 시작됨을 알리고
• currentRowNum의 초기값을 헤더의 행 위치(index)인 3으로 둔다.

 

 

startRow 메서드

@Override
public void startRow(int currentRowNum) {
    this.checkedCol = -1; // cell 메서드의 마지막에 checkedCol은 마지막 열의 인덱스 값이 들어가니 새로운 행을 탐색할 때 -1로 다시 초기화하는 것.
    this.currentRowNum = currentRowNum;
}

• 이 메서드는 파서가 Excel 시트에서 새로운 행을 만났을 때 호출된다.
  이 메서드가 호출되면 새 행이 시작된다는 표시 역할을 하며 다음 행의 데이터를 처리하는데 필요한 변수나 데이터 구조를 초기화할 수 있다.
• 새로운 행을 탐색하기 위해 현재 열의 인덱스를 나타내는 변수를 -1로 초기화하고 currentRowNum으로 현재 행의 인덱스를 추적한다.

 

endRow 메서드

@Override
public void endRow(int currentRowNum) {
    if (currentRowNum < startRowNum - 1) {} // Header보다 위에 있는 행은 불필요한 데이터이므로 파싱하지 않고 넘긴다.
    else if (currentRowNum == startRowNum - 1) { // 만약 현재 행이 startRowNum - 1인 경우 header라는 의미이므로 header 목록에 데이터(컬럼명)을 할당
        header = new ArrayList<>(row);
    } else { // 만약 실제 데이터가 들어있는 행일 경우 rows 목록에 데이터(row, 한 행에 들어있는 데이터 리스트)를 추가
        if (row.size() < header.size()) { // 만약 header의 사이즈가 row의 사이즈보다 크면 그만큼의 cell이 모두 비었다는 얘기이므로 빈 값 추가
            for (int i = row.size(); i < header.size(); i++) {
                row.add("");
            }
        }
        rows.add(new ArrayList<>(row));
    }
    row.clear();
}

• 이 메서드는 파서가 Excel 시트에서 행의 끝을 만났을 때 호출된다.
• 행 번호를 currentRowNum으로 제공하여 방금 처리가 끝난 행을 나타낸다.

 

cell 메서드

@Override
public void cell(String columnName, String value, XSSFComment comment) {
    int currentCol = new CellReference(columnName).getCol(); // 현재 열의 인덱스 값 (ex."A"열은 0, "B"열은 1)
    int emptyColumnCount = currentCol - checkedCol - 1;
    // 마지막에 처리된 열(currentCol)과 현재 열(iCol) 사이의 빈 셀 수를 나타낸다. 두 열 사이의 개수이니 -1을 해줘야 한다.

    for (int i = 0; i < emptyColumnCount; i++) {
        row.add(""); // 마지막에 처리된 열과 현재 열 사이의 빈 셀 수에 빈 값을 채워준다.(두 컬럼 사이에 공백의 셀이 있는 경우에 동작한다.)
    }

    row.add(value); // 마지막으로 현재 cell의 값을 리스트에 추가
    checkedCol = currentCol; // 현재 열의 인덱스가 가장 마지막에 처리되었음을 체크하기 위해 checkedCol에 현재 열의 인덱스값을 대입
}

• 이 핸들러의 유일한 문제점은 엑셀의 한 셀에 데이터가 없으면 Cell 이벤트를 타지 않고 넘어간다는 것이다.
• 따라서 이를 해결하기 위해 빈 값이 있는 셀을 건너뛰다가 데이터가 있는 셀을 만나면 그 사이의 값을 채우는 부분이 필요하다.
• 현재 열의 인덱스를 식별하여 마지막에 처리된 셀과 현재 셀 사이에 누락된 셀이 있다면 해당 셀을 빈 값을 채운다.
• 예를 들어 10 개의 cell을 가지고 있는 row에서 맨 앞의 3 cell만 데이터가 있고 뒤 7개의 cell은 비어있는 경우에는 어떻게 될까?
  • 이 경우에는 위의 endRow에서 header의 사이즈가 row의 사이즈보다 클 때의 for 문을 이용하여 row.add("");로 빈 문자열을 채워주는 방식으로 해결한다.
• @param columnName : "A", "B", "C"와 같은 현재 cell의 컬럼명이 들어감
  @param value : 현재 cell의 값으로, 현재 처리 중인 row 리스트에 해당 값을 추가함
  @param comment : 사용하지 않음

 

 

readExcel 메서드

public static ExcelSheetHandler readExcel(FileInputStream excelFile) {

    ExcelSheetHandler excelSheetHandler = new ExcelSheetHandler();
    // ExcelSheetHandler를 재정의해서 만든 클래스

    try {
        OPCPackage opcPackage = OPCPackage.open(excelFile);

        XSSFReader xssfReader = new XSSFReader(opcPackage);
        // 메모리를 적게 사용하며 sax 형식을 사용할 수 있게 함

        StylesTable stylesTable = xssfReader.getStylesTable();
        // 읽어온 테이블에 적용되어 있는 Style

        ReadOnlySharedStringsTable data = new ReadOnlySharedStringsTable(opcPackage);
        // 파일의 데이터를 테이블 형식으로 읽을 수 있도록 지원


        InputStream sheetStream = xssfReader.getSheetsData().next();
        // 액셀의 첫번째 sheet 정보만 읽어오기 위해 사용한다. 만약 다중 sheet를 처리하고 싶다면 반복문이 필요함
        InputSource sheetSource = new InputSource(sheetStream);

        ContentHandler handler = new XSSFSheetXMLHandler(stylesTable, data, excelSheetHandler, false);
        // XMLHandler 생성

        XMLReader sheetParser = XMLHelper.newXMLReader();
        // SAX 형식의 XMLReader 생성

        sheetParser.setContentHandler(handler);
        // XMLReader에 재정의하여 구현한 인터페이스 설정

        sheetParser.parse(sheetSource);
        // 파싱하여 처리

        sheetStream.close();
    } catch (Exception e) {
        log.error("엑셀 파일 읽기 에러 :", e.getCause(), e);
        throw new RuntimeException(e);
    }

    return excelSheetHandler;
}

• 주석으로 설명을 달아놓았다.

 

 

예시 코드

FileInputStream excelFile = new FileInputStream("/액셀/파일/경로/액셀파일명.xlsx");

ExcelSheetHandler excelSheetHandler = ExcelSheetHandler.readExcel(excelFile);

List<String> header = excelSheetHandler.getHeader();
List<List<String>> rowDataList = excelSheetHandler.getRows();

• header는 getHeader 메서드로 가져와 사용할 수 있다.
• rowDataList는 2차원 배열 형태이므로 배열의 요소가 한 행으로 이루어져 있다.

 

for (List<String> row : rowDataList) {
	// example
	row.get(5) // for문으로 한 행을 가져와 그 행의 5번째 인덱스 값인 cell의 데이터를 가져온다.
}

• 여기서 한 행의 데이터를 가져와 다시 그 안에서 cell을 index 값을 이용해 가져오는 방식으로 데이터를 다루면 된다.

 

 

📌 아래 github에서 클래스 및 테스트 코드 확인할 수 있다.

https://github.com/wonyongg/test/tree/main/excelParser

 

 

 

 

 

참고

뤼튼

https://m.blog.naver.com/tmondev/221505398958