엑셀을 코드로 만들 때 조용히 깨지는 것들 — openpyxl 실전

「완료」가 찍혔는데 파일이 안 열렸다

매주 같은 양식의 회의록을 엑셀로 내는 일이 있었다. 사람이 손으로 만들면 40분쯤 걸리고, 무엇보다 매번 같은 자리에서 서식이 흐트러진다. 그래서 파이썬으로 넘겼다. 라이브러리는 openpyxl이다.

첫 판은 잘 돌았다. 스크립트가 끝나고 로그에 「생성 완료」가 찍혔다. 파일 크기도 정상이었고, 다시 load_workbook으로 열어 셀 값을 읽어 봐도 전부 제자리에 있었다.

그런데 그 파일을 엑셀에서 더블클릭하자 이렇게 떴다.

«복구할 수 없는 내용이 있습니다. 통합 문서를 복구하시겠습니까?»

복구를 누르면 열리기는 한다. 대신 서식이 통째로 날아간 채로 열린다. 자동화로 아낀 40분을 서식 복구에 다시 쓰는 셈이라, 이건 「됐다」고 말할 수 없는 상태다.

더 나쁜 건 파이썬이 아무 말도 하지 않았다는 것이다. 예외도 경고도 없었다. 검사 코드를 아무리 촘촘히 짜도, 검사하는 쪽과 판정하는 쪽이 다르면 소용이 없다.

쓰는 쪽과 여는 쪽이 다르다

이유는 xlsx 파일의 정체를 보면 단순하다. xlsx는 압축된 XML 묶음이다. 확장자를 zip으로 바꿔 풀면 시트별 XML과 공유 문자열 테이블, 스타일 정의가 각각 파일로 들어 있다.

openpyxl이 하는 일은 그 XML을 규격에 맞게 써서 압축하는 것까지다. 그 XML이 엑셀이 받아들이는 조합인지는 검사하지 않는다. 문법적으로 올바른 XML이면 쓰고 끝난다.

판정은 엑셀이 파일을 열 때 한다. 그래서 이런 구도가 된다.

한 가지 더 알아 두면 이해가 빠르다. xlsx 안에서 값과 서식은 서로 다른 파일에 들어간다. 셀에 적힌 글자는 공유 문자열 테이블에 모여 있고, 굵게·색·테두리 같은 서식은 스타일 정의 쪽에 따로 있다. 셀은 그 둘을 번호로 가리키기만 한다.

그래서 «값은 멀쩡한데 파일이 깨지는» 일이 가능하다. 값을 담은 쪽은 온전한데 그것을 가리키는 구조가 엑셀이 받아들이지 않는 모양이면, 파이썬으로 값을 읽을 때는 아무 문제가 없고 엑셀에서 열 때만 걸린다. 내가 만난 사고는 전부 이 유형이었다.

즉 스크립트가 성공했다는 사실은 파일이 정상이라는 뜻이 아니다. 이 문장을 규칙으로 박아 두지 않으면 같은 사고를 계속 만나게 된다. 아래 네 가지가 실제로 그렇게 만난 것들이다.

함정 하나 — 서식 있는 텍스트의 빈 블록

한 셀 안에서 일부 단어만 굵게 하거나 색을 바꾸고 싶을 때가 있다. 회의록이라면 「결정」 두 글자만 강조하는 식이다. openpyxl은 이걸 위해 서식 있는 텍스트(rich text)를 지원한다. 셀 하나에 서식이 다른 조각을 여러 개 넣는 방식이다.

셀 전체에 굵게를 거는 것과는 다른 기능이다. 그쪽은 «이 셀은 이 서식을 쓴다»고 번호 하나를 가리키면 끝난다. 반면 한 셀 안에서 서식을 갈라야 하면 셀 안에 조각 목록을 따로 만들어야 한다. 구조가 한 겹 깊어지는 만큼 어긋날 자리도 늘어난다.

여기서 파일이 두 번 깨졌다. 원인은 둘 다 내용이 없는 조각이었다.

첫째, 줄바꿈만 든 조각을 넣었을 때다. 항목과 항목 사이를 띄우려고 개행 문자 하나짜리 조각을 사이사이에 끼웠다. 파이썬은 통과했고 엑셀은 복구를 요구했다.

둘째, 조각 목록을 이어 붙일 때다. 여러 곳에서 만든 조각들을 하나로 합치면서 리스트를 확장하는 방식으로 붙였는데, 그 과정에서 빈 조각이 섞여 들어갔다. 역시 파이썬은 조용했다.

고친 방법은 단순하다. 조각을 셀에 넣기 전에 «빈 것»을 걸러 낸다. 공백만 있거나 개행만 있는 조각은 버리고, 줄을 띄우고 싶으면 조각을 나누지 말고 한 조각 안의 문자열에 개행을 넣는다. 셀에 실제로 여러 줄을 표시하려면 그 셀에 자동 줄바꿈을 켜 두어야 한다.

그리고 애초에 강조가 꼭 필요한 칸이 아니면 서식 있는 텍스트를 쓰지 않는다. 셀 전체에 굵게를 거는 것은 스타일 속성이라 이 문제가 없다. 한 셀 안에서 서식을 갈라야 할 때만 쓰는 기능이고, 그 순간부터 파일이 깨질 경로가 하나 열린다.

함정 둘 — 행 높이 409.5의 벽

회의록에는 한 칸에 긴 글이 들어가는 자리가 있다. 논의 내용을 통째로 넣는 칸이다. 줄 수에 맞춰 행 높이를 계산해 지정하도록 짰다. 한 줄에 15포인트씩 잡고, 줄 수를 곱해 높이를 준다.

어느 회차부터 글의 끝부분이 화면에서 잘려 보였다. 셀에는 값이 다 들어 있다. 수식 입력줄에는 전문이 나온다. 그런데 인쇄해도 화면에도 뒷부분이 없다.

엑셀의 행 높이에는 상한이 있다. 409.5포인트다. 그 위로 지정하면 오류가 나는 것이 아니라 상한값으로 조용히 내려앉는다. 계산상 30줄이 필요해 450을 줬다면 실제로는 409.5가 적용되고, 넘친 부분은 그냥 안 보인다.

이 함정이 고약한 이유는 데이터 검사를 아무리 해도 안 걸린다는 것이다. 셀 값을 읽어 글자 수를 세면 100퍼센트 일치한다. 잘린 것은 값이 아니라 표시이기 때문이다.

그래서 대응은 값 검사가 아니라 배치 자체를 바꾸는 것이 된다. 한 칸에 409.5포인트를 넘길 분량이 들어갈 것 같으면 칸을 쪼개거나 항목을 행으로 나눈다. 그 자리에 그대로 두어야 한다면 최소한 넘긴 사실을 로그에 남긴다. 지정값과 상한을 비교하는 한 줄이면 된다.

함정 셋 — 좁은 칸에서 글이 접힌다

위 문제를 잡고 나서도 잘림이 다시 나왔다. 이번엔 계산값이 409.5 아래였는데도 잘렸다.

원인은 접힘이었다. 열 너비가 좁으면 한 줄로 적은 문장이 화면에서 두 줄, 세 줄로 접힌다. 개행 문자를 기준으로 줄 수를 세면 3줄인데, 실제로 차지하는 것은 8줄인 식이다. 그만큼 필요한 높이가 늘어나고, 계산값은 그걸 모른다.

이건 줄 수로는 절대 안 잡히는 종류다. 글자 수와 열 너비를 같이 봐야 한다. 완벽하게 재려면 글꼴 폭까지 알아야 하는데, 실무에서는 그렇게까지 갈 필요가 없었다. 한글은 두 칸, 영문·숫자는 한 칸으로 치고 열 너비로 나눠 올림하면 실제 접힘 수에 충분히 가깝게 나온다.

열 너비 자체도 사람이 생각하는 단위가 아니다. 엑셀의 열 너비는 픽셀이 아니라 기본 글꼴로 숫자 한 글자가 차지하는 폭을 1로 놓고 센다. 너비 20이면 «스무 글자쯤 들어간다»는 뜻에 가깝고, 한글은 그보다 적게 들어간다. 이 단위를 픽셀로 착각하면 계산이 처음부터 어긋난다.

그 값으로 다시 높이를 잡고, 그 결과가 409.5를 넘으면 앞 절의 규칙으로 넘긴다. 두 함정은 따로 있는 게 아니라 이어져 있다. 접힘을 안 세면 상한도 못 만난다.

함정 넷 — 수식은 값이 아니다

네 번째는 쓰기가 아니라 읽기 쪽이다. 다른 사람이 만든 엑셀에서 숫자를 뽑아 오는 스크립트를 짤 때 만난다.

openpyxl로 셀을 읽으면 수식 셀에서는 계산 결과가 아니라 수식 문자열이 나온다. 합계 칸을 읽었는데 숫자 대신 등호로 시작하는 문자열이 오는 것이다.

이건 data_only=True로 열면 해결된다고 알려져 있다. 절반만 맞다. 이 옵션이 돌려주는 것은 엑셀이 마지막으로 저장할 때 파일에 캐시해 둔 값이다. 계산을 다시 하는 게 아니다.

그래서 스크립트가 만든 파일을 사람이 한 번도 엑셀에서 열지 않았다면 그 캐시가 없다. 이때 data_only=True로 읽으면 전부 None이 온다. 값이 비어 있다는 뜻이 아니라 캐시가 없다는 뜻인데, 코드에서는 구분이 안 된다.

대응은 둘 중 하나다. 계산이 필요한 값은 파이썬에서 계산해 «숫자로» 써 둔다. 수식을 남겨야 한다면 읽는 쪽에서 None을 「값 없음」이 아니라 「모름」으로 다룬다. 두 상태를 같은 것으로 묶으면 합계가 0으로 나오는 보고서가 만들어진다.

검사를 «여는 쪽»으로 옮겼다

네 가지의 공통점을 다시 보면 이렇다.

• 서식 있는 텍스트 — 파이썬 통과, 엑셀에서 복구 요구
• 행 높이 상한 — 값 검사 통과, 화면에서 잘림
• 접힘 — 줄 수 검사 통과, 화면에서 잘림
• 수식 캐시 — 읽기 성공, 값이 None

넷 다 파이썬 쪽 검사를 전부 통과한다. 그래서 검사를 늘리는 것으로는 해결되지 않았다. 검사가 서 있는 자리를 옮겨야 했다.

지금은 파일을 만든 뒤에 다시 열어서 본다. 자동으로 할 수 있는 것은 두 가지다. 하나는 zip으로 풀어 시트 XML이 온전한지 보는 것이고, 다른 하나는 높이 상한과 접힘 계산을 만든 직후에 다시 재는 것이다.

zip 쪽 검사는 거창하지 않다. 만든 파일을 압축 해제해 시트 XML이 파싱되는지 보고, 공유 문자열 테이블이 셀이 가리키는 번호를 실제로 가지고 있는지를 맞춰 본다. 앞 절의 «내용 없는 조각»은 이 단계에서 걸린다. 몇 줄이면 되고, 파일 하나에 1초도 안 걸린다.

그리고 자동으로 못 하는 것이 하나 남는다. 엑셀로 실제로 열어 보는 것이다. 이건 사람이 한다. 매번은 아니고 양식을 바꾼 회차에는 반드시 한다. 서식이 그대로 도는 회차는 앞의 자동 검사로 충분했다.

이 구분이 실무에서 제일 중요했다. 전부 자동으로 잡으려다 보면 검사기가 무거워지고, 무거워진 검사기는 결국 꺼진다. «기계가 잡는 것»과 «사람이 눈으로 볼 것»을 처음부터 갈라 두는 편이 오래간다.

규칙으로 남긴 네 줄

같은 자리에 다시 빠지지 않으려고 네 줄로 적어 두었다.

• 파이썬이 조용한 것은 통과가 아니다. 판정은 파일을 여는 쪽이 한다.
• 서식 있는 텍스트에 빈 조각을 넣지 않는다. 넣기 전에 걸러 내고, 강조가 필요 없으면 아예 쓰지 않는다.
• 행 높이는 409.5에서 멈춘다. 넘길 분량이면 칸을 쪼개고, 못 쪼개면 넘겼다는 사실을 로그에 남긴다.
• 줄 수 대신 접힘을 센다. 열 너비를 같이 보지 않으면 계산이 늘 모자란다.

엑셀 자동화는 «되게 만드는 것»보다 «조용히 깨지는 자리를 아는 것»이 실력이 되는 영역이었다. 라이브러리가 못 미더워서가 아니다. 파일을 만드는 쪽과 판정하는 쪽이 서로 다른 프로그램이라 생기는, 구조에서 오는 틈이다.

그 틈을 없앨 수는 없으니 틈이 어디 있는지를 적어 두는 쪽을 택했다. 이 글이 그 목록이다.

자주 묻는 것