[Spring] XSSFWorkbook/ SAX를 이용하여 엑셀 파일 대용량 업로드 (How to Read Large Excel File Using XSSF and SAX)

 

[ 이전 글 ] Apache POI를 이용한 엑셀 파일 읽기

https://frogand.tistory.com/126

[Spring] Apache POI 를 이용한 엑셀 파일 읽기

 

 문제 상황 

POI 라이브러리를 사용해서 Excel 작업을 진행 중이었다.

근데 Excel 용량이 커서인지 진행이 되지 않았음. 계속 뜨는 오류 메시지는 

java.lang.OutOfMemoryError: Java heap space

[삽질 1] Exceed Java Heap Size 

Xmx를 늘려주었다.. 4096M 까지.. (사실 얼마나 늘려야할지 몰랐는데, 아무튼 이게 문제가 아니라는 것을 깨달음)

- 참고: Xmx 옵션은 자바 힙 사이즈의 최대를 결정해주는 옵션이다. 

- 참고: 엑셀 파일의 크기는 58M에서 87M였다.

 

그래도 되지 않았다.

 

[삽질 2] Thread Starvation

HikariPool-1 - Thread starvation or clock leap detected

thread 사용하지도 않았는데 갑자기 Thread starvation,, => Multi-Threaded JDBCTemplate 사용해봤지만

역시나 되지 않았다.

 

[삽질 3] SXSSFWorkbook

=> XSSFWorkbook를 생성하는 시점부터 문제가 생기는 것을 알고,  XSSFWorkbook에 문제가 있을 것 같다는 생각과 함께...

Workbook workbook = new XSSFWorkbook(file.getInputStream());

SXSSFWorkbook 방식이 있다는 것을 알았지만, Write Only로 엑셀 파일을 만들 때만 사용할 수 있다. 

엑셀 파일 읽기에는 사용이 불가능했다.

 

 

 해결 방안 

=> SAX를 이용하여 Excel 파일 읽기

* pom.xml에 의존성을 추가

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.2</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId> <!-- 엑셀 2007 이상 버전에서 사용 -->
    <version>4.1.2</version>
</dependency>
<dependency>
    <groupId>sax</groupId>
    <artifactId>sax</artifactId>
    <version>2.0.1</version>
</dependency>

ExcelSheetHandler.java

import org.apache.poi.ooxml.util.SAXHelper;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.util.CellReference;
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.XMLReader;
import org.xml.sax.InputSource;

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

public class ExcelSheetHandler implements XSSFSheetXMLHandler.SheetContentsHandler {

    private int currentCol = -1;
    private int currRowNum = 0;

    private List<List<String>> rows = new ArrayList<List<String>>();    //실제 엑셀을 파싱해서 담아지는 데이터
    private List<String> row = new ArrayList<String>();
    private List<String> header = new ArrayList<String>();

    public static ExcelSheetHandler readExcel(File file) throws Exception {

        ExcelSheetHandler sheetHandler = new ExcelSheetHandler();
        try {

            OPCPackage opc = OPCPackage.open(file);
            XSSFReader xssfReader = new XSSFReader(opc);
            StylesTable styles = xssfReader.getStylesTable();
            ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(opc);

            //엑셀의 시트를 하나만 가져오기.
            //여러개일경우 iter문으로 추출해야 함. (iter문으로)
            InputStream inputStream = xssfReader.getSheetsData().next();
            InputSource inputSource = new InputSource(inputStream);
            ContentHandler handle = new XSSFSheetXMLHandler(styles, strings, sheetHandler, false);

            XMLReader xmlReader = SAXHelper.newXMLReader();
            xmlReader.setContentHandler(handle);
            
            xmlReader.parse(inputSource);
            inputStream.close();
            opc.close();
        } catch (Exception e) {
            //에러 발생했을때
        }

        return sheetHandler;

    }

    public List<List<String>> getRows() {
        return rows;
    }

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

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

    }
}

 

아래는 위 ExcelSheetHandler를 사용하는 방법이다.

// 엑셀 데이터 양식 example
/*    A열				B열
1행   nero@nate.com		Seoul
2행   jijeon@gmail.com	Busan
3행   jy.jeon@naver.com	Jeju
*/

String filePath = "/Users/jyjeon/Downloads/정의서/복사본.xlsx";
File file = new File(filePath);

ExcelSheetHandler excelSheetHandler = ExcelSheetHandler.readExcel(file);

// excelDatas >>> [[nero@nate.com, Seoul], [jijeon@gmail.com, Busan], [jy.jeon@naver.com, Jeju]]
List<List<String>> excelDatas = excelSheetHandler.getRows();

for(List<String> dataRow : excelDatas) // row 하나를 읽어온다.
    for(String str : dataRow){ // cell 하나를 읽어온다.
        System.out.println(str);
    }
}

 

 

출처

https://poi.apache.org/components/spreadsheet/how-to.html#xssf_sax_api

The New Halloween Document

https://m.blog.naver.com/hyoun1202/220245067954

[JAVA] Apache POI를 사용한 Excel 파일 읽기(대용량 Excel 파일 읽기 포함)

https://hoonzi-text.tistory.com/29

java excel read 문제 해결 (XSSFWorkbook heap space OOM)