2022-10-10-TIL

2022-10-10-TIL

Today I Learned

Apache POI로 대용량 엑셀 쓰기

대용량 엑셀 다운로드가 느리거나 메모리를 많이 쓰는 문제를 다룬 자료를 모았다. 원인은 대부분 같다. XSSFWorkbook은 시트의 모든 행과 셀을 객체로 메모리에 올리기 때문에, 행 수가 많아지면 힙 사용량과 GC 시간이 함께 늘어난다.

해결책은 쓰기 전용 스트리밍 API인 SXSSFWorkbook이다. 지정한 개수(기본값 DEFAULT_WINDOW_SIZE = 100행)의 행만 메모리에 두고, 창을 벗어난 행은 임시 XML 파일로 내보낸다. POI 문서가 밝히는 제약은 다음과 같다(Apache POI: SXSSF).

• 디스크로 내보낸 행은 더 이상 getRow()로 접근할 수 없다. 위에서 아래로 한 번에 쓰는 보고서 형태에 맞다.
• 임시 파일은 시트마다 생기고 매우 커질 수 있다. setCompressTempFiles(true)로 gzip 압축을 켤 수 있다.
• 작업이 끝나면 반드시 dispose()를 호출해 임시 파일을 지워야 한다.
• 병합 영역, 하이퍼링크, 코멘트는 여전히 메모리에 남으므로 많이 쓰면 메모리를 차지한다.

속도 쪽에서 자주 지적되는 실수는 셀 스타일이다. 셀마다 createCellStyle()을 호출하면 스타일 객체가 셀 수만큼 생겨 메모리와 파일 크기가 함께 늘어난다. 같은 모양의 스타일은 한 번 만들어 재사용한다. Stack Overflow 답변에서도 셀 포매터를 매번 새로 만들지 말고 하나를 재사용하라는 조언이 나온다.

CellStyle money = wb.createCellStyle();
money.setDataFormat(wb.createDataFormat().getFormat("#,##0"));
for (...) {
    Cell cell = row.createCell(1);
    cell.setCellValue(amount);
    cell.setCellStyle(money);   // 셀마다 새로 만들지 않는다
}

100만 행 쓰기를 비교한 글의 측정 결과는 9월 21일 TIL에 정리했다. POI가 무겁다고 판단해 다른 엑셀 라이브러리로 옮긴 경험담(첫 번째 링크)도 있다.

• https://deepakschoudhary.medium.com/how-i-escaped-apache-poi-and-found-my-dream-3rd-party-excel-writer-5e56dd73e407
• https://blog.naver.com/PostView.naver?blogId=stylehosting&logNo=221962575020&categoryNo=7&parentCategoryNo=0&viewDate=&currentPage=1&postListTopCurrentPage=1&from=postView
• https://blog.naver.com/PostView.nhn?blogId=sence255&logNo=222374664383&parentCategoryNo=15&categoryNo=&viewDate=&isShowPopularPosts=false&from=postView
• https://velog.io/@codesweaver/Spring4-%EC%97%91%EC%85%80-%EB%8B%A4%EC%9A%B4%EB%A1%9C%EB%93%9C-%EC%86%8D%EB%8F%84-64%EB%B0%B0-%EA%B0%9C%EC%84%A0%ED%95%9C-%EC%9D%B4%EC%95%BC%EA%B8%B0-0pb9j1h4
• https://velog.io/@codesweaver/Spring4-%EC%97%91%EC%85%80-%EB%8B%A4%EC%9A%B4%EB%A1%9C%EB%93%9C-%EC%86%8D%EB%8F%84-64%EB%B0%B0-%EA%B0%9C%EC%84%A0%ED%95%9C-%EC%9D%B4%EC%95%BC%EA%B8%B0-0pb9j1h4
• https://andriymz.github.io/misc/apache-poi-slow-excel-generation/#
• https://levelup.gitconnected.com/how-to-write-a-million-rows-in-an-excel-file-efficiently-5f7ce6c69314
• https://gist.github.com/kawasima/f33ab14000ba14b7dada
• https://javadoc.io/static/org.apache.poi/poi-ooxml/4.1.1/org/apache/poi/xssf/streaming/SXSSFWorkbook.html
• https://stackoverflow.com/questions/33786219/apache-poi-streaming-sxssf-for-reading
• https://poi.apache.org/components/spreadsheet/how-to.html
• https://poi.apache.org/apidocs/dev/org/apache/poi/xssf/streaming/SXSSFWorkbook.html
• https://poi.apache.org/components/spreadsheet/limitations.html
• https://www.programcreek.com/java-api-examples/?api=org.apache.poi.xssf.streaming.SXSSFWorkbook
• https://www.digitalocean.com/community/tutorials/apache-poi-tutorial
• https://howtodoinjava.com/java/library/readingwriting-excel-files-in-java-poi-tutorial/
• https://github.com/T5750/poi
• https://blog.simplicityweb.co.uk/120/generating-large-spreadsheets-more-efficiently-using-sxssf
• https://blog.naver.com/hj_kim97/222374962489

---

How to use POI SXSSF to read large spreadsheet

SXSSF는 쓰기 전용이라 읽기에는 쓸 수 없다. Stack Overflow 답변이 안내하듯, 큰 xlsx 파일을 읽을 때는 POI의 XSSF SAX(event) API를 쓴다. 워크북 전체를 객체로 만들지 않고 시트 XML을 SAX로 흘려 읽으면서 셀을 만날 때마다 콜백을 받는 방식이라 메모리 사용이 작다. POI 예제의 XLSX2CSV가 이 방식의 대표 예시다(POI: XSSF and SAX (Event API)).

SAX 콜백 코드는 다루기 번거로워서 이를 감싼 excel-streaming-reader 라이브러리도 있다. StreamingReader.builder().rowCacheSize(100).bufferSize(4096).open(is)로 열면 익숙한 POI Workbook, Row, Cell 인터페이스로 순회할 수 있다. 대신 xlsx만 지원하고, 메모리에 일부 행만 두므로 행을 임의 순서로 접근할 수 없으며, 수식 같은 일부 기능은 읽을 수 없다(excel-streaming-reader).

• https://stackoverflow.com/questions/10053421/how-to-use-poi-sxssf-to-read-a-large-spreadsheet
• https://okky.kr/articles/348270
• https://roytuts.com/how-to-read-large-excel-file-using-apache-poi/
• https://stackoverflow.com/questions/42380765/read-huge-excel-file500k-rows-in-java
• https://poi.apache.org/components/spreadsheet/how-to.html#xssf_sax_api
• https://github.com/monitorjbl/excel-streaming-reader

---

JXL vs Apache POI

JXL(JExcelApi)은 POI와 함께 오래 쓰인 자바 엑셀 라이브러리다. 링크의 답변이 꼽는 가장 큰 차이는 JXL이 Excel 2007 이후의 .xlsx 형식을 지원하지 않고 옛 바이너리 .xls만 다룬다는 점이다. 답변 작성 당시 기준으로 JXL의 자바 버전은 2009년 이후 갱신이 없었고, 조건부 서식, 서식 있는 텍스트, 도형도 지원하지 않는다. 반면 POI는 두 형식을 같은 설계로 지원하고, 읽기 전용 이벤트 API까지 갖추고 있어서 답변자는 POI를 택했다. 새로 시작한다면 xlsx 지원만으로도 POI 쪽이 맞다.

• https://stackoverflow.com/questions/14980717/what-is-the-better-api-to-reading-excel-sheets-in-java-jxl-or-apache-poi

참고한 자료외부 출처 2

외부 출처

• Apache POI: The New Halloween Document (SXSSF, XSSF SAX API)Apache POI
• excel-streaming-readerGitHub