얼마전 토지조서 엑셀을 받아와 DB에 엑셀 행들을 저장하고, 엑셀 내용 읽어 응답해줘야하는 요구사항이 있었습니다.
개발을 하며 엑셀 개발 시 고려가 필요한 두 가지 상황을 발견해서 공유하고자 합니다.
제 개발 환경은 아래와 같으나, 개발 환경이 다르신 분들도 한 번 읽어보시면 도움이 되실 것 같습니다.
Spring Boot 3.4.2 + Java 21 + Apache POI 라이브러리
첫번째 상황: NumberOfRows()는 얼마가 나올까?
위와 같은 엑셀 시트가 하나 있다고 생각해봅시다.
제가 사용하는 Apache POI 라이브러리에서는 `worksheet.getPhysicalNumberOfRows()`를 사용하여 시트에 내용이 몇 행까지 있는지 가져올 수 있는데요. 여러분들이 사용하시는 라이브러리에도 Row를 가져오는 함수가 분명 있을 것입니다.
그럼 그 함수들을 실행해보면, 위 엑셀 시트의 행은 몇 개가 나올까요?
사실 저는 처음에 별 생각 하지 않았습니다.
그냥 적당히 나오겠거니, 마음속으로 3개가 나오지 않을까라고 생각했던 것 같아요.
그런데 3개보다는 더 많은 값이 출력되었습니다.
아 설마설마 7행까지 색깔이 칠해져 있으니 7개가 출력될까요?
아닙니다. 위 엑셀은 놀랍게도 9개가 출력됩니다. 이렇게 Row를 가져오는 함수가 저희 예상대로 동작 되는지 꼭 확인이 필요합니다. 저의 경우 데이터가 있는 셀은 3개뿐인데 개발 중 확인해보니 정확히 219개가 나오고 있었습니다.
물론 엑셀 형식을 맞춰서 필요하지 않은 선이나 색깔이 없는 상태로 제공해주는 게 가장 좋지만, 그렇게 제공한다고 해도 사용자가 어떻게 던져줄지 모르기 때문에 이 부분에 대한 예외처리도 같이 해보면 좋을 것 같습니다.
보통 엑셀 개발을 할 때 Row 갯수를 가져오고, 갯수를 이용해 반복문을 돌며 행을 가져올 것입니다. 아래는 자바 코드이지만 자바를 모르셔도 충분히 이해가 가능합니다.
int rowCount = worksheet.getPhysicalNumberOfRows()
for (int i = 시작할 행; i < rowCount; i++) {
Row row = worksheet.getRow(i);
if(row.getCell(startCell).getCellType() == CellType.BLANK) {
continue;
}
... 엑셀 불러오는 함수 호출
}
if문의 내용을 통하여 Row가 실제 데이터보다 많이 가져왔을 경우 continue를 해주는 간단한 코드입니다.
저는 처음에 row.getCell(startCell)==null getCell을 통하여 cell을 가져와 null인지 확인을 하는 코드를 썼었는데, 동작하지 않더라고요. getCellType()같이 각 라이브러리에서 제공해주는 getCellType 함수를 쓰셔서 BLANK인지 비교해주세요. 타입 기반 언어가 아니라면 content를 가져와 null인지 확인하게끔 하면 됩니다. (ex python이라면 바로 worksheet.cell(row, column) is None)
이렇게 셀이 비어있을 경우 예외처리를 해주면 불필요한 반복을 방지할 수 있습니다.
저는 유저가 행 하나를 비워두고 작성했을 경우를 고려하고 싶어 continue를 사용했는데, 상황에 맞추어 break를 사용하셔도 좋을 것 같습니다.
두번째 상황: 1/1은 엑셀에서 어떤 타입일까?
두번째 상황은 강타입 언어인 Java에서만 해당하는 상황일 수도 있습니다. 우선, Java 엑셀 라이브러리인 Apach POI에서는 셀을 가져올 때 getStringCell, getNumericCell 등 데이터 타입에 맞게 가져와야합니다.
저는 토지 조서 엑셀을 받는 작업을 진행했었습니다. '토지 지분'이라는 컬럼에는 '1/1', '1/2', '1/3' 등 분수가 입력되고, 저는 이 데이터가 String이라고 생각했어요. 그래서 저는 '1/1'이라는 값을 cell.getStringCellValue()을 통해 가져오고자 했고...
에러가 발생하게 됩니다.
자, 1/1은 엑셀에서 어떤 타입으로 인식한 걸까요?
사실 답은 엑셀 파일에 있습니다. 엑셀을 많이 다뤄보신 분들은 감이 오셨을 수도 있어요.
사진을 보시면 분명 1/1을 클릭했지만 데이터에는 2025.1.1이 나오게 됩니다. 즉, 1/1이 엑셀에서는 '날짜'로 인식되는 것이죠.
이는 엑셀의 셀 서식 때문으로, 숫자를 입력하면 날짜로 자동 변환해주기 때문에 일어나는 문제입니다. 비단 1/1 뿐만이 아니라, 아래와 같은 형식 모두 날짜로 바뀌게 됩니다.
이를 해결하는 방법은 엑셀 내부에서 셀 서식을 TEXT로 바꿔주는 것인데... 그럼 예외 상황이 발생할 가능성이 너무너무 높아지죠. 사용자가 엑셀 셀 서식을 삭제할 수도 있고, 다른 엑셀 파일을 업로드 할 수도 있으니까요.
생년월일도 주의해야합니다.
000818을 엑셀에 입력했다고 했을 때, 특별한 서식 설정이 없다면 818이라는 값이 들어가게 됩니다.
저도 위와 같은 문제가 발생했어서 Numberic 타입일 경우 앞에 0을 붙여주도록 설계하였습니다.
OK, 그러면 1/1은 DATE 타입이 나오는군요?
놀랍게도 그렇지 않습니다. cell.getCellType()을 이용하여 셀의 타입을 가져올 수 있는데, 해당 함수로 찍으면 NUMERIC 즉, 숫자 타입으로 나오게 됩니다. 엑셀이 내부적으로 date를 숫자로 변환해서 관리하기 때문이죠.
그래서 저는 아래와같이 해결하였습니다.
private String getLandStake(Cell cell) {
if (cell.getCellType() == CellType.STRING) {
return cell.getStringCellValue();
} else if (cell.getCellType() == CellType.NUMERIC) {
Date dateValue = cell.getDateCellValue();
SimpleDateFormat dateFormat = new SimpleDateFormat("M/d"); // "1/1" 형식으로 변환
return dateFormat.format(dateValue);
} ...
}우선 STRING 타입으로 잘 가져와졌으면, 그대로 응답을 하고, 만약 NUMERIC 타입이라면 "M/d" 형식으로 바꾸어서 응답을 해주는 코드입니다. 왜 NUMERIC 타입이지만 getDateCellValue()과 getNumericCellValue() 둘 다로 가져올 수 있습니다.
else if (cell.getCellType() == CellType.NUMERIC) {
double numericValue = cell.getNumericCellValue();
Date dateValue = DateUtil.getJavaDate(numericValue); // 숫자 값을 날짜로 변환
SimpleDateFormat dateFormat = new SimpleDateFormat("M/d");
return dateFormat.format(dateValue);
}
이렇게 getNumericCellValue()로 가져와도 되지만, 굳이 필요하지 않은 단계인 것 같아 저는 생략했습니다.
총정리 🙋
1) 행 갯수에 관한 예외처리를 꼭 걸어주자.
보통 엑셀 조회 API, 업로드 API 등을 개발할 때 각 라이브러리의 getRows() 총 행을 가져오곤 하는데, 이 때 getRows()가 너무 많은 행을 응답하고 있진 않은지 항상 확인합시다.
그리고 빈 행들이 count 되고 있을 가능성이 있으니, if문을 통하여 예외 처리를 해주면 더욱 좋을 것입니다.
2) 엑셀 서식 타입을 고려하자.
Java같은 강타입 언어에서는 셀을 가져올 때 타입별로 가져오게 됩니다. 셀 서식이 어떻게 설정되어 있냐에 따라 String 타입으로 생각했지만 실제로는 Date 타입일 수도 있고, Date 타입으로 생각했지만 Integer 타입일 수도 있습니다.
또한 셀 서식에 따라서 예상하는 값과 다른 값이 나오게 될 수 있습니다. (ex 040818이 나올 것이라 예상했는데 40818이 나온다.)
만약 셀 서식에 따라서 타입이 달라질 수 있는 값들이라면, 그에 따른 예외 처리를 해주는 것이 좋겠죠?
물론 가장 중요한건...
물론 가장 중요한 건 최대한 예외 상황을 안 만드는 것이라고 생각합니다. 필요하지 않은 서식들(ex 배경색) 때문에 너무 많은 행이 count 된다던가, 사용자가 TEXT로 입력했는데 실제로 숫자나 날짜가 들어가는 일이 안 생기는 게 가장 좋다고 생각해요.
하지만 그런 이상적인 상황은 사실상 불가능에 가깝습니다.
저희는 이런 사실들을 바탕으로 최대한 요청 단계에서부터 예외 상황이 안 생기도록 기획자분들과 소통하되, 그런 상황이 생겼을 때도 문제가 없도록 대처해야 한다고 생각합니다.
읽어주셔서 감사합니다. 피드백도 항상 받고 있습니다.
'Etc' 카테고리의 다른 글
| 왜 우리는 CORS를 알아야 할까? (3) | 2024.11.09 |
|---|---|
| 나는 어떻게 살아왔는가 - 삶의 지도 (0) | 2024.09.21 |