[JAVA] poi에서 엑셀을 다룰 때 발생하는 여러가지 오류

개요

내가 개발하는 프로젝트가 서식과 통계를 주로 다루는 시스템이다 보니 엑셀로 데이터를 다운로드하는 프로그램을 개발하는 경우가 많았다.

지금까지 Java poi 환경에서 엑셀을 다루며 오류 해결에 시간을 많이 쏟았다 보니 다시는 동일한 문제로 시간을 소비하고 싶지 않아

겪었던 오류들 중 자주 마주칠 수 있는 내용이거나 알아두면 좋은 내용들을 정리하고자 포스팅을 남기게 되었다.

 

먼저 엑셀을 다루기 위해 poi를 설치한다. 필자는 5.0.0 버전을 사용하였다.
참고로 2007년 이후의 엑셀을 대응하기 위해서는 poi-ooxml도 필수로 설치해주어야 한다.

/* build.gradle */

// https://mvnrepository.com/artifact/org.apache.poi/poi
implementation 'org.apache.poi:poi:5.0.0'

// https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml
implementation 'org.apache.poi:poi-ooxml:5.0.0'

---

오류

엑셀에 열기 암호가 걸려있을 때

가장 기본적인 부분으로, 당연하게도 열기 암호가 걸려있는 경우 코드 내에서 엑셀을 읽어 들이지 못한다.

org.apache.poi.openxml4j.exceptions.OLE2NotOfficeXmlFileException: The supplied data appears to be in the OLE2 Format. You are calling the part of POI that deals with OOXML (Office Open XML) Documents. You need to call a different part of POI to process this data (eg HSSF instead of XSSF)

해당 오류가 발생한 경우에는 간단하게 엑셀의 열기 암호를 해제해 주면 된다.

참고로 쓰기 암호가 걸려있는 경우에는 정상적으로 엑셀을 읽어들인다.

---

엑셀이 잘못 저장되어 있는 경우 (?)

이 오류 때문에 이 포스팅을 해야겠다고 마음을 먹었다. 비슷한 오류를 구글링 해봐도 명확한 해결책이 나오지 않았다.

java.lang.IllegalArgumentException: Attempting to write a row[1] in the range [0,999] that is already written to disk.

이 오류를 해결하는 데 애를 많이 먹었다.

1행을 디스크에 이미 쓰는 중이라고 하니 코드를 여러 차례 수정해 보았으나 동일한 오류의 반복이었고,

다른 엑셀 시트를 불러와서 동일한 로직을 태웠을 때는 또 정상적으로 작성되었다..

 

이 오류의 원인은 놀랍게도 엑셀 시트에 있었다.

엑셀 양식을 업데이트하면서 시트 하나를 변경하였는데,

에러가 해당 시트에서만 발생하여 그 시트를 건드려보다가 해결책을 찾았다.

왼쪽 시트는 오류가 나고, 오른쪽 시트는 정상 동작한다. 차이가 무엇일까?

사진으로 비교해 보니 조금 더 명확히 보이는데,

위 두 시트의 차이점은 오른쪽 스크롤 바를 보면 알 수 있다.

 

왼쪽 시트의 빈칸들은 이미 저장되어 있는 것으로 취급되어 스크롤 바가 아래로 길게 내릴 수 있도록 되어있고,

오른쪽 시트의 빈칸들은 삭제 처리되어 내려갈 필요가 없어 스크롤 바가 스크롤을 거의 하지 않도록 되어있다.

 

왼쪽 사진의 시트와 같은 경우여도 무조건 오류가 발생하지는 않는다.

되려 오류가 발생하지 않는 경우가 대부분이다.

하지만 동일한 오류가 발생한 경우 빈 row들을 묶어 삭제하여 오른쪽 사진의 시트처럼 만들어 시도하면 해당 오류를 해결하는 데 도움이 될 것이다.

실제로 필자는 이 방식으로 오류를 해결하였다.

포스팅 이후 동일한 오류가 발생하여 해당 방식으로 대응했는데 소용이 없는 경우가 있었다.
이 때에는 아예 시트를 새로 생성하고 동일한 내용으로 시트를 구성하여 해결했다. (복사 붙여넣기 X)

 

정확하게 어떤 원리에서 오류가 발생하는지는 모르겠으나,

해당 엑셀을 작업할 때 해당 시트를 여러 군데 재사용하는 과정에서 복사/붙여넣기 하는 중에 시트의 설정이 꼬여서 발생한 오류로 추측된다.

---

한셀에서 파일이 열리지 않는다

프로젝트 특성상 공공기관의 환경을 따라야 하는 경우가 꽤 있는데, 그중 하나가 한셀이다.

한셀이란 한글과컴퓨터 회사에서 엑셀과 거의 동일한 기능을 수행하는 프로그램이고,

많은 공공기관에서 해당 프로그램을 사용한다.

 

프로젝트를 운영하는 과정에서 시스템에서 다운로드한 엑셀이 한셀에서 열리지 않는다는 보고가 접수되었고,

해당 엑셀을 관리자가 직접 다운로드하여 엑셀에서 한 번 저장을 하고 파일을 전달하면 한셀에서 열린다고 하여 그 방식으로 대응을 하고 계셨다.

오류 확인 끝에 SXSSFWorkbook이 원인임을 확인하였다.

 

poi에서 엑셀을 구성하는 Workbook에는 HSSFWorkbook, XSSFWorkbook, SXSSFWorkbook와 같이 3가지가 있고,

이 중 HSSFWorkbook는 엑셀 97~2003 버전만을 대응하므로 사용할 수가 없다.

XSSFWorkbook의 성능 개선 버전인 가장 최근에 나온 SXSSFWorkbook를 사용하여 엑셀 다운로드를 구현하였는데,

한셀에서 해당 Workbook 대응이 안되어있는 건지 XSSFWorkbook으로 바꾸고 다운로드를 해야 정상적으로 한셀에서 파일을 열 수 있었다.

문제점

XSSFWorkbook의 가장 큰 단점은 성능이 좋지 않다는 것이다.

SXSSFWorkbook에 비해 몇 배는 느리고,

필자의 프로젝트 환경 기준 10,000 row 이상 넘어가는 경우 엑셀 다운로드를 수행해내지 못하였다.

 

다행히 10,000 row가 넘어가는 엑셀을 다운로드해야 하는 클라이언트는 한셀을 대응하지 않아도 되었기 때문에 한셀을 꼭 사용해야 하는 클라이언트의 엑셀 다운로드에만 XSSFWorkbook를 적용하도록 구성하여 해결하였다.

불가피한 경우 엑셀로 해당 파일을 열고 한 번 저장을 한 후 한셀로 열도록 해야 할 수도 있겠다...

---

마무리

엑셀 다운로드는 노가다성이 많고 복잡하지만 시스템에 구성된 데이터를 자료로 받아볼 수 있는 중요한 기능이다.

다만 엑셀 프로그램에 종속되다 보니 여러 가지 알아둬야 할 부분도 많고 세팅해야 할 부분도 많아 고생을 할 수 있다.

이 글을 통해 나와 동일한 오류를 겪는 사람들이 빠르게 해결하고 기능 구현에 집중할 수 있기를 바란다.