손으로 관리하던 재무 워크북에는 피벗 캐시와 슬라이서, 조건부 서식, 수년 치 셀 스타일이 쌓여 있는데, 야간 작업이 바꿔야 하는 것은 데이터 탭 하나의 숫자뿐이다. 전체 로드 후 저장은 모든 파트를 다시 파싱하고 재구축하고 deflate한다. 그 워크시트 파트만 다시 쓰고 나머지 zip 항목은 손대지 않고 그대로 건네주면 그 일의 거의 전부를 건너뛴다
이를 가능하게 하는 것은 패키지 형식 자체다. .xlsx 파일은 Open Packaging Conventions(ECMA-376 Part 2)이 정한 배치의 XML 파트들을 담은 zip 아카이브이고, 모든 zip 항목은 자신의 압축 방식과 CRC-32, 압축 크기를 로컬 헤더와 중앙 디렉터리에 기록한다. 복사자가 그 세 값을 원본 바이트와 함께 써 주기만 하면, 항목은 한 번도 inflate되지 않은 채 아카이브에서 아카이브로 옮겨 갈 수 있다. 피벗 캐시는 보존되기 위해 이해될 필요가 없다. 복사되기만 하면 된다
워크북을 다시 저장하는 대신 압축 바이트를 복사하는 이유는?
압축 수준에서의 복사는 패치의 대부분을 단순 스트림 복사로 바꾼다. HotXLS에서 TXLSDirectWriter.BeginPatch는 소스 패키지를 읽기 전용으로 열고, 패치가 손대지 않는 모든 항목을 TZipArchive.CopyCompressedFrom에 넘긴다. 이 함수는 소스 항목의 압축 바이트와 CRC, 크기를 대상 아카이브에 곧장 기록한다. 평범한 쓰기 경로는 들어갈 때 inflate, 나올 때 deflate 비용을 치르는데, 비싼 쪽은 deflate다. 대형 피벗 캐시와 내장 이미지 수십 장이 대부분을 차지하는 워크북이라면 시간은 실제로 쓰고 있는 시트에 들어간다
있는 그대로의 복사는 압축 없이 저장된 항목(zip 방식 0)과 deflate된 항목(방식 8)을 다룬다. 다른 압축 방식을 쓰는 항목도 여전히 옮겨 간다. writer가 압축 해제 스트림으로 읽어 다시 기록하므로 그 파트는 어느 쪽이든 대상에 도착한다. 디렉터리 표식 항목은 내용이 없으므로 건너뛴다. writer가 자기 것을 만들기 때문이다. 몇몇 항목은 아예 복사되지 않는데, 재생성되거나 편집되기 때문이다. 대상 워크시트와 그 _rels 파트, xl/calcChain.xml, xl/_rels/workbook.xml.rels, [Content_Types].xml, 그리고 시트를 삽입할 때는 xl/workbook.xml이 그 대상이다
BeginPatch로 시트 하나 제자리 교체하기
인자 두 개짜리 오버로드 BeginPatch(FileName, SheetName)는 기존 워크시트를 교체하고 결과를 같은 경로에 다시 쓴다. xl/workbook.xml의 시트 레지스트리와 각 시트를 파트에 묶는 릴레이션십 맵으로 이름을 판별하고, 이름을 대소문자 무시하고 비교하며, 맞는 것이 없으면 "worksheet not found"를 담은 예외를 던진다. 그 후에는 스트리밍 writer가 새 파일에 쓰는 것과 같은 AddSheet, AddRow, Write* 호출로 시트를 작성한다
var
W: TXLSDirectWriter;
begin
W := TXLSDirectWriter.Create;
try
W.BeginPatch('monthly-dashboard.xlsx', 'Data'); // 제자리 교체
W.AddSheet('Data'); // 패치 모드에서 이름은 정보성일 뿐
W.AddRow(1);
W.WriteString(1, 'Region');
W.WriteString(2, 'Revenue');
W.AddRow(2);
W.WriteString(1, 'North');
W.WriteNumber(2, 184320.55);
W.AddRow(3);
W.WriteString(1, 'South');
W.WriteNumber(2, 97410.20);
W.AddRow(4);
W.WriteString(1, 'Total');
W.WriteFormula(2, 'SUM(B2:B3)'); // 앞에 '=' 없음
W.Close;
finally
W.Free;
end;
end;
이 코드에서 잘못 읽기 쉬운 두 가지가 있다. AddSheet에 넘긴 이름은 아무것도 이름을 바꾸지 않는다. 탭은 sheetId와 xl/workbook.xml에서 이미 갖고 있던 위치, 이름을 유지하므로 AddSheet('Totals')조차 Data에 쓴다. 그리고 두 번째 AddSheet는 예외를 일으킨다. 하나의 패치는 정확히 하나의 워크시트만 쓰기 때문이다. 탭 셋을 갱신하는 것은 패치 셋이며, 각각은 패키지를 한 번 더 복사한다
수식은 등호 없이 들어간다. WriteFormula는 텍스트를 셀의 <f> 엘리먼트에 곧장 넣는데, ECMA-376 Part 1 §18.3.1.40은 수식을 Excel이 수식 입력줄에 보여 주는 "=" 없이 저장한다. '=SUM(B2:B3)'을 쓰면 literal 등호가 XML에 들어간다. WriteFormula는 캐시 결과도 내보내지 않는다. 재계산 대신 캐시 값을 읽는 무언가가 하류에 있다면, WriteFormulaNumber가 평가 결과를 이미 알고 있는 숫자를 수식과 함께 써 준다
제자리 오버로드는 작업하는 동안 원본에 절대 쓰지 않는다. 패치된 패키지는 소스 이름에 .hxlpatch.tmp를 붙인 형제 파일에서 만들어진다. Close에서 소스 핸들을 놓아주고 준비된 파일이 단 한 번의 rename으로 원본을 대체한다. 그 rename이 실패하면 Close는 양쪽 경로를 메시지에 담은 예외를 일으키고, 준비된 사본은 남기고 원본은 제자리에 둔다. Close 전에 죽은 프로세스는 원본을 온전히 남기고 그 옆에 .hxlpatch.tmp를 하나 흘린다. 다음 그 파일에 대한 BeginPatch는 시작 전에 그것을 지운다
템플릿 사본에 새 워크시트 덧붙이기
인자 네 개짜리 오버로드 BeginPatch(SourceFileName, TargetFileName, SheetName, InsertSheet)는 소스 파일을 그대로 두고 새 패키지를 쓴다. InsertSheet = False면 사본에서 이름이 일치하는 시트를 교체하고, True면 그 이름으로 새 워크시트를 덧붙이며, 템플릿에 이미 그 이름의 탭이 있으면 "worksheet already exists" 예외를 일으킨다
// template.xlsx는 그대로. output.xlsx에 탭 하나 추가
W.BeginPatch('template.xlsx', 'output.xlsx', 'Extra', True);
W.AddSheet('Extra');
W.AddRow(1);
W.WriteString(1, 'Appended by the nightly job');
W.Close;
삽입이 패키지 장부를 고치는 자리다. writer는 기존 xl/worksheets/sheetN.xml 항목들에서 최대 파트 번호를, <sheet> 엘리먼트들에서 최대 sheetId를, 워크북 릴레이션십에서 최대 rId를 찾고 각각의 다음 값을 취한다. 새 <sheet> 엘리먼트는 </sheets> 바로 앞에 들어가므로 새 탭은 마지막에 나타난다. 릴레이션십 속성은 소스 워크북이 시트에 묶어 쓰는 접두어, 보통은 r:id이지만 반드시 그래야 하는 것은 아닌, 그 접두어를 재사용한다
릴레이션십 타입도 소스 패키지를 따른다. 워크북 릴레이션십이 이미 purl.oclc.org 아래의 ISO/IEC 29500 Strict 네임스페이스를 쓰고 있다면 새 워크시트 릴레이션십은 Strict 타입 URI로 기록된다. Transitional 워크북은 Transitional 타입을 받는다. 패치된 Strict 워크북이 Strict 사이에 Transitional 릴레이션십 하나를 품는 일은 없다
스타일, 문자열, 패치가 손대지 않는 파트들
패치 안에서는 새 시트가 가리키는 모든 것이 소스 패키지에 이미 존재해야 한다. 문자열은 그래서 xl/sharedStrings.xml이 아니라 시트 안에 인라인으로 쓰인다. shared string 테이블은 바이트 그대로 건너가며 늘어날 수 없기 때문이다. 패치 모드에서 UseSharedStrings를 True로 두면 AddSheet는 예외를 일으킨다
같은 규칙이 StyleIndex의 의미도 바꾼다. 패치에서 이것은 소스 워크북의 cellXfs 목록(ECMA-376 Part 1 §18.8.10)에서의 위치이고, writer는 그것이 0과 65534 사이에 있는지만 검사한다. 인덱스 14가 그 특정 파일에 존재하는지는 알 방법이 없으므로 틀린 숫자는 출력으로 곧장 들어간다. 올바른 값을 찾는 가장 믿을 만한 방법은 시트의 현재 버전 압축을 풀고 원하는 서식을 이미 가진 셀의 s 속성을 읽는 것이다. 그 숫자가 cellXfs 위치다. Excel은 템플릿에서 누군가 셀 서식을 지정할 때마다 그 목록에 항목을 추가할 수 있으므로, 1년 전에 적어 둔 상수를 믿지 말고 템플릿을 고칠 때마다 인덱스를 다시 확인하라
const
// 이 템플릿에서 날짜 서식의 cellXfs 위치. 기존 날짜 셀의
// s="..." 속성에서 가져온 값. 템플릿을 고친 뒤에는 다시 확인
TemplateDateXf = 5;
begin
...
W.AddRow(5);
W.WriteString(1, 'Refreshed');
W.WriteDateTime(2, Now, TemplateDateXf); // 명시적 인덱스: 허용됨
// W.WriteDateTime(2, Now); 패치 모드에서 예외: 암시적 날짜 서식이
// 패치가 쓰지 않는 styles 파트에 있다
end;
새 패키지 파트를 추가할 것이라면 무엇이든 거부된다. AddTable, AddChart, AddImage 계열, AddComment, AddDefinedName, AddCellStyle는 호출되는 즉시 예외를 일으키고, Close는 커밋 전에 다시 검사한다. 이는 자체 채우기, 글꼴 색, 굵게, 기울임 서식을 설정하는 조건부 서식도 걸러낸다. 그런 서식은 styles.xml에 새 항목을 필요로 하기 때문이다. 거부된 호출이 아무도 등록하지 않은 테이블 파트를 시트가 참조하는 패키지보다 낫다
교체 모드는 시트를 백지에서 시작한다. 예전 시트의 릴레이션십 파트는 복사되지 않으므로 이전 버전에 붙어 있던 드로잉, 주석, 외부 하이퍼링크는 새 시트에 붙지 않고, 예전 시트 XML에 저장된 열 너비, 틀 고정, 병합 범위, 데이터 유효성 검사도 사라진다. 시트 XML에 사는 것들은 SetColumnWidth, FreezePanes, MergeCells, AddDataValidation, AddHyperlink로 다시 쓸 수 있다. 데이터 탭에 고정된 차트나 그림은 패치로 되돌아오지 않으므로, 차트는 자기 시트에 두고 시리즈를 데이터 탭으로 향하게 하라
계산 체인은 두 모드 모두에서 버려진다. xl/calcChain.xml은 Excel이 마지막에 평가한 순서의 수식 셀들을 나열한다. 교체 후에는 그 항목들이 더는 그런 형태로 존재하지 않는 셀들을 기술하고, 삽입 후에는 참조하던 시트 인덱스들이 밀려 있다. 그 파트는 복사에서 빠지고, 릴레이션십과 Override — [Content_Types].xml에 있는 그 항목 — 도 제거되어 패키지가 없는 파트를 참조하지 않게 된다. Excel은 다음 재계산 때 체인을 다시 만든다
행 루프가 중간에 예외를 던질 때
TXLSDirectWriter의 소멸자는 Close가 한 번도 호출되지 않았을 때 패키지를 마무리한다. 잊힌 Close가 절삭된 zip을 널려 두지 않는다는 뜻이다. writer가 쥐고 있는 무엇이든 마무리하기 때문에, 제자리 오버로드 아래에서 Free를 타고 풀려 나가는 예외도 여태 쓴 행들을 커밋하고 원본 위로 교체해 버린다. 행을 읽어 쓰다가 중간에 예외를 던지고 finally 블록에서 writer를 놓아주는 루프는 정확히 그렇게 끝난다
무인으로 도는 것이라면 명시적인 대상에 쓰고, 결과를 열어 검사한 뒤에야 실제 파일을 직접 교체하라. 행 루프의 예외는 여전히 Free가 준비된 파일을 마무리하게 두지만 교체는 건너뛴다. 실제 워크북은 오늘 숫자의 절반이 아니라 어제의 숫자를 유지한다
uses
Windows, SysUtils, Variants, lxDirectWrite, lxHandleX;
procedure RefreshDashboard;
const
Live = 'monthly-dashboard.xlsx';
Staged = 'monthly-dashboard.next.xlsx';
var
W: TXLSDirectWriter;
Check: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
begin
W := TXLSDirectWriter.Create;
try
W.BeginPatch(Live, Staged, 'Data', False);
W.AddSheet('Data');
WriteRefreshedRows(W); // 여러분의 행 루프, HotXLS 일부 아님
W.Close;
finally
W.Free;
end;
Check := TXLSXWorkbook.Create;
try
if Check.Open(Staged) <> 1 then
raise Exception.Create('Staged workbook does not open');
Sheet := Check.Sheets.SheetByName('Data');
if (Sheet = nil) or (VarToStr(Sheet.Cells[2, 1].Value) <> 'North') then
raise Exception.Create('Data sheet did not come back as written');
finally
Check.Free;
end;
// Windows API, HotXLS 아님: 검증을 마친 뒤에만 실제 파일 교체
if not MoveFileEx(PChar(Staged), PChar(Live), MOVEFILE_REPLACE_EXISTING) then
RaiseLastOSError;
end;
TXLSXWorkbook.Open은 성공 시 1을 반환하고 Sheets와 Cells는 모두 1 기반이다. Check.Sheets[1]은 첫 탭이고 Cells[2, 1]은 A2다. 지점 값 하나 외에 두 가지 구조 검사가 대부분의 실수를 잡는다. Check.Sheets.Count는 교체 후에는 템플릿의 개수와 같아야 하고 삽입 후에는 하나 많아야 하며, 아무 zip 도구로 본 아카이브 목록에 xl/calcChain.xml이나 두 번 나오는 항목 이름이 없어야 한다. 중복 이름이 중요한 이유는 일부 리더가 그 이름의 마지막 항목을 취하는데, 그러면 두 시트가 마치 내용이 서로 바뀐 것처럼 보이기 때문이다
패치인가, 스트리밍인가, 워크북 로드인가?
패치는 워크북이 크고, 변경이 한 시트에 갇혀 있고, 파일의 나머지가 비트 단위로 살아남아야 할 때 맞다. 변경이 여러 시트에 걸치거나, 새 데이터가 템플릿이 아직 정의하지 않은 서식을 필요로 하거나, 차트나 테이블을 만들어야 하거나, 파일이 작아서 평범한 로드와 저장이 측정할 가조차 없는 비용일 때는 잘못된 도구다. 백지부터 대량 생성에는 스트리밍 direct writer가 더 맞다. 같은 AddRow와 Write* 호출을 공유하므로 둘 사이의 코드 이동은 대개 BeginFile을 BeginPatch로 바꾸는 일이다
작업이 정말로 완전한 객체 모델을 필요로 한다면, 로드된 워크북 안의 시트 수준 작업은 XLSX 패키지에서 워크시트 복제하기에서 다룬다. 그리고 패치를 고려하게 된 이유가 워크북 전체 처리가 느려졌기 때문이라면, 방식을 정하기 전에 대용량 워크북 성능이 로드와 저장 시간과 메모리가 실제로 어디로 가는지 살펴 본다
패치 writer와 스트리밍 writer, 완전한 TXLSXWorkbook 객체 모델은 모두 Delphi와 C++Builder용 하나의 라이브러리로 제공된다. 에디션과 전체 기능 목록은 HotXLS Delphi 스프레드시트 컴포넌트 페이지에 있다