POI SAX XSSFReader 대용량 엑셀 파일 읽기 OOME(Out of Memory Error) 방지

* 일단 시작 전에 대용량 테스트를 위한  eclipse Heap 영역 늘리기

eclipse .ini 파일을 열기 Xms (시작크기) / Xmx (최대크기) 수정하기 

참고로 Xmx 최대크기는 자기의 pc ram 사양을 확인하고 바꾸기 추천

 

JVM 메모리 체크하는 방법 

// 엑셀 파일 처리 전의 메모리 상태 출력
Runtime runtime = Runtime.getRuntime();
long memoryBefore = runtime.totalMemory() - runtime.freeMemory();

totalMemory(): JVM이 할당한 전체 메모리 양을 반환.  JVM의 초기 메모리 크기와 최대 메모리 크기를 합친 값 
freeMemory(): 현재 사용 가능한 메모리 양을 반환. 현재 할당된 메모리 중에서 사용되지 않은 영역의 크기를 의미

엑셀 파일읽기를 위해 사용한 3가지 방법 

<form action="/excelUpload.do"  enctype="multipart/form-data" method="post">
    <input type="file" name="excelFile">
    <button type="submit">Upload</button>
</form>

1.  XSSFWorkbook 사용

@ResponseBody
	@RequestMapping(value = "/excelUpload.do", method = RequestMethod.POST)
	public void excelUpload(MultipartHttpServletRequest request, HttpServletResponse response) throws IOException, ServletException {
	    response.setContentType("text/html;charset=UTF-8");
	    try {
	        // 엑셀 파일 처리 전의 메모리 상태 출력
	        Runtime runtime = Runtime.getRuntime();
	        long memoryBefore = runtime.totalMemory() - runtime.freeMemory();
	        System.out.println("Memory Before Processing (bytes): " + memoryBefore);

	        MultipartFile file = request.getFile("excelFile");

	        if (file != null) {
	            // 파일 크기 가져오기
	            File uploadedFile = new File(file.getOriginalFilename());
	            file.transferTo(uploadedFile);
	            long fileSizeBytes = uploadedFile.length(); // 파일 크기 (바이트 단위)
	            double fileSizeMB = fileSizeBytes / (1024.0 * 1024.0); // 파일 크기 (MB 단위)
	            System.out.println("파일 크기: " + fileSizeMB + " MB");

	            InputStream fileContent = file.getInputStream();
	            XSSFWorkbook workbook = new XSSFWorkbook(fileContent);

	            // 특정 이름의 시트
	            XSSFSheet sheet = workbook.getSheet("B");
	            System.out.println("sheet ::" + sheet);

	            int rowLength = 0;
	            for (Row row : sheet) {
	                rowLength = row.getLastCellNum();
	                for (Cell cell : row) {
	                }
	            }

	            // 엑셀 파일 처리 후, 메모리 리소스 해제
	            workbook.close();

	        } else {
	            response.getWriter().println("No file uploaded.");
	        }

	        // 엑셀 파일 처리 후의 메모리 상태 출력
	        long memoryAfter = runtime.totalMemory() - runtime.freeMemory();
	        System.out.println("Memory After Processing (bytes): " + memoryAfter);
	        long memoryUsed = memoryAfter - memoryBefore;
	        System.out.println("Memory Used (bytes): " + memoryUsed);

	    } catch (Exception e) {
	        e.printStackTrace();
	        response.getWriter().println("Error processing Excel file.");
	    }
	}

2. XSSFWorkbook  + opcPackage 사용

<form action="/excelUploadOpc.do"  enctype="multipart/form-data" method="post">
    <input type="file" name="excelFile">
    <button type="submit">Upload</button>
</form>

 OPCPackage를 사용 : 엑셀 파일을 OOXML(오픈 XML 문서) 형식으로 압축해서 가져옴. 

	@ResponseBody
	@RequestMapping(value = "/excelUploadOpc.do", method = RequestMethod.POST)
	public void excelUploadOpc(MultipartHttpServletRequest request, HttpServletResponse response) throws IOException, ServletException {
	    response.setContentType("text/html;charset=UTF-8");
        
	    try {
	        // 엑셀 파일 처리 전의 메모리 상태 출력
	        Runtime runtime = Runtime.getRuntime();
	        long memoryBefore = runtime.totalMemory() - runtime.freeMemory();
	        System.out.println("Memory Before Processing (bytes): " + memoryBefore);

	        MultipartFile file = request.getFile("excelFile");

	        if (file != null) {
	            // 파일 크기 가져오기
	            File uploadedFile = new File(file.getOriginalFilename());
	            file.transferTo(uploadedFile);
	            long fileSizeBytes = uploadedFile.length(); // 파일 크기 (바이트 단위)
	            double fileSizeMB = fileSizeBytes / (1024.0 * 1024.0); // 파일 크기 (MB 단위)
	            System.out.println("파일 크기: " + fileSizeMB + " MB");

	            InputStream fileContent = file.getInputStream();

	            // OPCPackage를 사용 : 엑셀 파일을 OOXML(오픈 XML 문서)형식으로 압축해서 가져온다.
	            OPCPackage opcPackage = OPCPackage.open(fileContent);
	            XSSFWorkbook workbook = new XSSFWorkbook(opcPackage);

	            // 특정 이름의 시트
	            XSSFSheet sheet = workbook.getSheet("B");
	            System.out.println("sheet ::" + sheet);

	            int rowLength = 0;
	            for (Row row : sheet) {
	                rowLength = row.getLastCellNum();
	                for (Cell cell : row) {
	                }
	            }

	            // 엑셀 파일 처리 후, 메모리 리소스 해제
	            workbook.close();
	            opcPackage.close();

	        } else {
	            response.getWriter().println("No file uploaded.");
	        }

	        // 엑셀 파일 처리 후의 메모리 상태 출력
	        long memoryAfter = runtime.totalMemory() - runtime.freeMemory();
	        System.out.println("Memory After Processing (bytes): " + memoryAfter);
	        long memoryUsed = memoryAfter - memoryBefore;
	        System.out.println("Memory Used (bytes): " + memoryUsed);

	    } catch (Exception e) {
	        e.printStackTrace();
	        response.getWriter().println("Error processing Excel file.");
	    }
	}

*** 3. SAX 사용 *** 

데이터를 순차적으로 읽어 내려가며 노드의 시작과 끝부분에 이벤트를 발생시킨다.

문서 전체를 메모리에 올리지 않기 때문에 메모리 사용량이 적고 단순히 읽기만 할 때 빠른 속도를 보인다.

<form action="/excelUploadSax.do"  enctype="multipart/form-data" method="post">
    <input type="file" name="excelFile">
    <button type="submit">Upload</button>
</form>

@ResponseBody
	@RequestMapping(value = "/excelUploadSax.do", method = RequestMethod.POST)
	public void excelUploadSax(MultipartHttpServletRequest request, HttpServletResponse response) throws IOException, ServletException {
	    response.setContentType("text/html;charset=UTF-8");
	
	    try {
	        // 엑셀 파일 처리 전의 메모리 상태 출력
	        Runtime runtime = Runtime.getRuntime();
	        long memoryBefore = runtime.totalMemory() - runtime.freeMemory();
	        System.out.println("Memory Before Processing (bytes): " + memoryBefore);

	        MultipartFile file = request.getFile("excelFile");
	        
                // 방식1
                // SheetHandler excelSheetHandler = ExcelSheetHandler.readExcel(file);
                // 방식2
                SheetHandler excelSheetHandler = ExcelSheetHandler.readExcel2(file);
            
                // 엑셀 헤더 값 가져오기 
                List<String> excelHeader = excelSheetHandler.getHeader();
                // 엑셀 담긴 데이터 가져오기 
                List<List<String>> excelDatas = excelSheetHandler.getRows();
	        
                System.out.println("excelDatas :: " + excelDatas);
                System.out.println("excelHeader :: " + excelHeader);
                
	        // 엑셀 파일 처리 후의 메모리 상태 출력
	        long memoryAfter = runtime.totalMemory() - runtime.freeMemory();
	        System.out.println("Memory After Processing (bytes): " + memoryAfter);
	        long memoryUsed = memoryAfter - memoryBefore;
	        System.out.println("Memory Used (bytes): " + memoryUsed);

	    } catch (Exception e) {
	        e.printStackTrace();
	        response.getWriter().println("Error processing Excel file.");
	    }
	}

출력 결과 
excelDatas :: [[2, 2, 2, 2, 2, 2, 2], [3, 3, 3, 3, 3, 3, 3], [4, 4, 4, 4, 4, 4, 4], [5, 5, 5, 5, 5, 5, 5], [6, 6, 6, 6, 6, 6, 6], [7, 7, 7, 7, 7, 7, 7], [8, 8, 8, 8, 8, 8, 8], [9, 9, 9, 9, 9, 9, 9], [10, 10, 10, 10, 10, 10, 10], [11, 11, 11, 11, 11, 11, 11], [12, 12, 12, 12, 12, 12, 12], [13, 13, 13, 13, 13, 13, 13], [14, 14, 14, 14, 14, 14, 14], [15, 15, 15, 15, 15, 15, 15]]
excelHeader :: [1, 1, 1, 1, 1, 1, 1]

ExcelSheetHandler.java

package egovframework.com.cmm.web;
import java.io.InputStream;

import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.util.SAXHelper;
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.springframework.web.multipart.MultipartFile;
import org.xml.sax.ContentHandler;
import org.xml.sax.InputSource;
import org.xml.sax.XMLReader;

public class ExcelSheetHandler {

  // 방식 1
  public static SheetHandler readExcel(MultipartFile file) {
  SheetHandler sheetHandler = new SheetHandler();
 
  try {
        // MultipartFile에서 InputStream 가져오기
        InputStream inputStream = file.getInputStream();

        // InputStream을 사용하여 OPCPackage 열기
        OPCPackage pkg = OPCPackage.open(inputStream);

        XSSFReader xssfReader = new XSSFReader(pkg);
        ReadOnlySharedStringsTable data = new ReadOnlySharedStringsTable(pkg);
        StylesTable styles = xssfReader.getStylesTable();

        InputStream sheetStream = xssfReader.getSheetsData().next();
        //InputStream sheetStream = xssfReader.getSheet("rId1"); // 첫번째 시트만 꺼내기
        
        // <특정 시트만 꺼내고싶을경우>
        // InputStream sheetStream = xssfReader.getSheet("rId1"); // 첫번째 시트만 꺼내기 
        // rId1 - 1번째시트 rId2 - 2번째시트를 의미 
        
        InputSource sheetSource = new InputSource(sheetStream);
        ContentHandler handler = new XSSFSheetXMLHandler(styles, data, sheetHandler, false);
        XMLReader sheetParser = SAXHelper.newXMLReader();
        sheetParser.setContentHandler(handler);
        sheetParser.parse(sheetSource);
        sheetStream.close();

      } catch (Exception e) {
          throw new RuntimeException(e);
      }
	
   return sheetHandler;
 }



   
   // 방식 2
   public static SheetHandler readExcel2(MultipartFile file) {
   SheetHandler sheetHandler = new SheetHandler();
    try {
        // 업로드된 파일의 InputStream 얻기
        InputStream inputStream = file.getInputStream();

        // InputStream으로부터 OPCPackage 열기
        OPCPackage pkg = OPCPackage.open(inputStream);

        // XSSFReader를 사용하여 OPCPackage에서 데이터를 읽기
        XSSFReader xssfReader = new XSSFReader(pkg);
        ReadOnlySharedStringsTable data = new ReadOnlySharedStringsTable(pkg);
        StylesTable styles = xssfReader.getStylesTable();

        // 시트를 반복하면서 처리
        XSSFReader.SheetIterator sheetIterator = (XSSFReader.SheetIterator) xssfReader.getSheetsData();

        InputStream sheetStream = null;
        while (sheetIterator.hasNext()) {
            InputStream currentSheetStream = sheetIterator.next();
            
            // sheetName으로 분류하기 
            String sheetName = sheetIterator.getSheetName();
            // 특정 시트들을 건너뛰기
            if ("주의사항".equals(sheetName)) { 
                sheetStream = currentSheetStream;
                System.out.println("sheetName :: " + sheetName); 
            }
        }

        // 처리할 시트가 없으면 예외 발생
        if (sheetStream == null) {
            throw new IllegalArgumentException("Sheet not found");
        }

        // 시트의 InputSource 생성
        InputSource sheetSource = new InputSource(sheetStream);

        // XSSFSheetXMLHandler를 사용하여 시트의 내용을 처리할 ContentHandler 생성
        ContentHandler handler = new XSSFSheetXMLHandler(styles, data, sheetHandler, false);

        // SAX 파서를 생성하고 ContentHandler 설정하여 시트의 내용 파싱
        XMLReader sheetParser = SAXHelper.newXMLReader();
        sheetParser.setContentHandler(handler);
        sheetParser.parse(sheetSource);
        sheetStream.close();

    } catch (Exception e) {
        throw new RuntimeException(e);
    }
     return sheetHandler;
  }
}

 

Apache POI Streaming API doesn't recognize Excel (xlsx) content

 

SheetHandler.java

package egovframework.com.cmm.web;
import java.util.ArrayList;
import java.util.List;
 
import org.apache.poi.hssf.util.CellReference;
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler.SheetContentsHandler;
import org.apache.poi.xssf.usermodel.XSSFComment;
public class SheetHandler implements SheetContentsHandler {
	 
    private List<List<String>> rows = new ArrayList<>();
 
    private List<String> row = new ArrayList<>();
 
    private List<String> header = new ArrayList<>();
 
    private int currentCol = -1;
 
    private int currRowNum = 0;
     
    public List<String> getHeader() {
      return header;
    }
     
    public List<List<String>> getRows() {
      return rows;
    }
 
    public void startRow(int rowNum) {
 
        this.currentCol = -1;
        this.currRowNum = rowNum;
 
    }
 
    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 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);
    }
 
    public void headerFooter(String text, boolean isHeader, String tagName) {
 
    }
}

---

테스트 결과

 

107MB 크기의 .xlsx 파일 테스트

XSSFWorkbook 사용	OutOfMemoryError 발생
XSSFWorkbook + OPCPackage 사용	OutOfMemoryError 발생
SAX 첫번째 시트민 사용	Memory Before Processing (bytes): 377,279,896 Memory After Processing (bytes): 1,312,857,664 Memory Used (bytes): 935,577,768
SAX 두번째 시트민 사용	Memory Before Processing (bytes): 374,138,704 Memory After Processing (bytes): 1,156,792,120 Memory Used (bytes): 782,653,416
SAX 전체 시트 사용	Memory Before Processing (bytes): 376,451,232 Memory After Processing (bytes): 1,683,191,008 Memory Used (bytes): 1,306,739,776

 

213MB 크기의 .xlsx 파일 테스트

XSSFWorkbook 사용	OutOfMemoryError 발생
XSSFWorkbook + OPCPackage 사용	OutOfMemoryError 발생
SAX 세번째 시트민 사용	Memory Before Processing (bytes): 374,325,384 Memory After Processing (bytes): 2,266,669,232 Memory Used (bytes): 1,892,343,848
SAX 전체 시트 사용	Memory Before Processing (bytes): 235,024,768 Memory After Processing (bytes): 2,764,111,600 Memory Used (bytes): 2,529,086,832

 

 

XSSFReader (POI API Documentation)

Java 대용량 엑셀 업로드