Delphi에서 직접 스트리밍을 통해 거대한 Excel 파일 쓰기

보고서 작업이 1년 동안 문제없이 실행됩니다. 쿼리 결과로 시트를 채워 통합 문서를 작성하고 저장하는 작업입니다. 그러다 5년 치 기록을 보유한 고객이 전체 데이터 내보내기를 요청하여 행 수가 백만 개를 넘어서면, 파일이 디스크에 쓰이기도 전에 메모리 부족 오류로 프로세스가 종료됩니다. 코드에는 아무런 문제가 없었습니다. 단지 마지막에 통합 문서를 직렬화할 수 있도록 전체 데이터를 RAM에 보관하고 있었고, 이에 필요한 메모리는 작성해야 하는 행의 수와 비례하여 증가했을 뿐입니다

해결책은 더 큰 장비를 도입하는 것이 아닙니다. 다른 쓰기 모델을 사용하는 것입니다. HotXLS의 스트리밍 직접 쓰기 도구(streaming direct writer)는 행이 도착하는 대로 OOXML 패키지를 점진적으로 내보내기 때문에 사용하는 메모리는 작성하는 행 수에 의존하지 않습니다. 이것은 스트리밍 리더에 대한 쓰기 측면의 상대입니다. 리더가 셀 트리를 구성하지 않고 대규모 시트를 탐색하는 것처럼, 쓰기 도구 역시 셀 트리를 구성하지 않고 파일을 생성합니다

일반적인 저장 경로가 데이터에 따라 커지는 이유

일반적인 TXLSXWorkbook 경로는 먼저 전체 객체 모델을 구축합니다. 값, 유형 및 스타일 참조를 포함한 모든 셀은 저장을 호출할 때까지 메모리 내에서 객체로 존재하며, 저장 시점에 전체 트리가 패키지로 직렬화됩니다. 이 모델은 시트를 읽고 편집하고 다시 계산하여 저장하려는 경우에는 올바른 선택입니다. 편집 기능에는 모든 셀에 대한 무작위 액세스(random access)가 꼭 필요하기 때문입니다. 하지만 한 방향으로만 행을 쏟아붓고 다시는 돌아보지 않는 경우에는 아무런 이점 없이 모든 행을 상주시킬 비용을 지불해야 하므로 잘못된 선택입니다. 백만 개의 객체 행은 나중에 다시 방문하든 안 하든 여전히 백만 개의 객체 행입니다

스트리밍 쓰기 도구는 트리를 제거합니다. 셀이 기록되는 즉시 워크시트 파트의 바이트가 되며 그 바이트들은 zip 출력으로 전달됩니다. 워크시트 스트림은 유일하게 커지는 버퍼이지만, 이는 힙 메모리의 라이브 Delphi 객체로서가 아니라 출력 측면에서 커지는 것입니다. 시트 이름, 소수의 플래그, 현재 행 번호, 셀 카운터 등 정해진 양의 기록만 메모리에 상주하게 됩니다. 이 집합은 첫 번째 행과 천만 번째 행 사이에 변하지 않습니다

공유 문자열 테이블의 함정과 인라인 문자열의 해법

대부분의 스트리밍 XLSX 쓰기 도구는 텍스트를 만나기 전까지는 잘 작동합니다. 일반적인 OOXML 형식은 공유 문자열 테이블에 문자열을 저장합니다. 고유한 문자열은 별도의 파트에 한 번 기록되고 해당 문자열을 포함하는 모든 셀은 텍스트 대신 테이블에 대한 인덱스를 지니게 됩니다. 이는 반복되는 레이블로 가득 찬 파일에서 공간을 최적화하기에 좋은 방식이며, 일반적인 저장 경로가 기본적으로 사용하는 방식입니다. 그러나 스트리밍 쓰기 도구에게 이는 치명적인 문제입니다. 중복을 제거하려면 아직 오지 않은 행이 이미 쓰인 행의 문자열을 반복할 수 있고, 지금까지 본 문자열의 완전한 메모리 내 맵이 있어야만 올바른 인덱스를 할당할 수 있기 때문에 전체 작업 동안 테이블이 메모리에 상주해야 합니다. 결과적으로 스트리밍 쓰기 도구가 스트리밍할 수 없는 단 하나의 구조는, 아이러니하게도 파일 크기를 작게 만들어야 할 바로 그 구조입니다. 텍스트가 많은 데이터는 스트리밍의 목적을 좌절시킵니다

직접 쓰기 도구는 테이블을 완전히 피합니다. 문자열은 <is><t> 요소를 통해 셀 내부에 텍스트가 직접 배치되는 t="inlineStr" 셀로서 인라인으로 쓰입니다. 축적할 테이블도 없고 유지할 기존 문자열 맵도 없기 때문에 텍스트 열이 숫자 열보다 더 많은 메모리를 차지하지 않습니다. 이러한 교환 방식은 명확하며 솔직하게 언급할 가치가 있습니다. 인라인 문자열은 나타나는 곳마다 동일한 텍스트를 반복하므로, 동일한 레이블이 많은 파일은 공유 문자열을 사용하는 동등한 파일보다 디스크 상에서 더 커집니다. 일정한 메모리를 확보하기 위해 파일 크기를 포기하는 것입니다. 한 번만 패스하는 내보내기(one-pass export)에서는 이것이 올바른 교환이며, 출력 시에 zip 압축이 그 반복의 대부분을 흡수합니다

스타일 테이블은 끝에 도착하며 단 하나의 날짜 형식을 가집니다

스타일도 문자열과 똑같은 긴장을 유발합니다. 통합 문서는 스타일 파트를 통해 서식을 참조하는데, 스트리밍 쓰기 도구는 이미 플러시(flush)된 셀과 함께 늘어나는 스타일 팔레트를 메모리에 유지할 수 없습니다. 직접 쓰기 도구는 스타일 테이블을 작고 고정된 크기로 유지하고, 이를 맨 처음이 아닌 닫을 때 내보냄으로써 이 문제에 답합니다. 하나의 기본 셀 서식이 일반적인 셀을 덮습니다. 셀 서식 목록의 알려진 위치에 yyyy-mm-dd의 형식 코드로 등록된 단 하나의 날짜 숫자 서식이 날짜를 담당합니다

이 날짜 서식은 WriteDateTime이 별도의 호출로 존재하는 이유입니다. Excel에는 고유한 날짜 유형이 없으며, 날짜는 날짜 서식을 갖춘 숫자에 불과합니다. WriteDateTime은 값을 단순한 일련번호로 쓰고 그 셀에 유일한 날짜 스타일을 태그합니다. 그래서 스프레드시트는 이를 5자리 정수가 아닌 날짜로 렌더링합니다. 쓰는 일련번호는 라운드트립(round-tripping)에 중요합니다. 1900년 날짜 시스템으로 TDateTime 값을 직접 저장하는데, 이는 일반적인 TXLSXWorkbook 저장 경로가 사용하는 규칙과 동일합니다. 양쪽 경로가 동일한 일련번호에 동의하기 때문에 스트리밍 쓰기 도구가 생성한 파일은 HotXLS 리더를 통해 다시 읽을 수 있으며, 쓰기 도구와 리더 간에 1일 오차나 기점(epoch) 문제 없이 의도한 날짜 그대로 Excel에서 열립니다

바이트가 이미 사라졌기 때문에 순서가 필수적입니다

스트리밍은 지켜야 할 단 하나의 규칙과 함께 메모리 프로필을 구매합니다. 출력은 진행하는 즉시 내보내지고 다시 돌아갈 수 없기 때문에 모든 것은 파일에 표시되는 순서대로 기록되어야 합니다. 행 내에서는 오름차순의 열 순서로 셀이 위치합니다. 시트 내에서는 오름차순으로 행이 배치됩니다. 조금 전에 닫은 행은 이미 zip 스트림의 바이트가 되어 더 이상 도달할 수 없으므로, 쓰기 도구가 사후에 셀을 정렬할 수 있는 버퍼는 존재하지 않습니다. 동일한 행에서 5번째 열을 준 다음 2번째 열을 제공하면, 쓰기 도구는 주어지는 순서대로 내보내기 때문에 출력 형식이 잘못됩니다

일반적인 사례를 위해 행 API에는 작은 편의 기능이 있습니다. AddRow는 1부터 시작하는 행 인덱스를 갖지만, 0을 전달하면 이전 행의 바로 다음 행을 취한다는 뜻이므로 순차적인 채우기에서 증가하는 카운터를 추적하거나 전달할 필요가 없습니다. 각 AddRow는 그 앞의 행을 닫고, 각 AddSheet는 그 앞의 시트를 닫기 때문에 명시적으로 행이나 시트를 끝낼 필요가 없습니다. 다음 항목을 시작하기만 하면 쓰기 도구가 열린 구조를 대신 마무리해 줍니다

텍스트가 XML에 들어가는 위치에서 이스케이프 처리됩니다

여러분이 쓰는 모든 텍스트는 XML 문서의 일부가 되므로, 5개의 미리 정의된 XML 엔터티를 이스케이프(escape)해야 합니다. 그렇지 않으면 값에 앰퍼샌드(&)나 꺾쇠괄호(<, >)가 포함된 순간 패키지가 유효하지 않게 됩니다. 쓰기 도구는 인라인 문자열 텍스트와 수식 텍스트 모두에서 자동으로 &, <, >, " 및 '를 이스케이프 처리합니다. 이 두 곳은 호출자가 제공한 문자가 마크업 내부에 배치되는 위치입니다. 여러분이 처리되지 않은 WideString을 전달하면 쓰기 도구가 이를 안전하게 만듭니다. Smith & Co <Ltd>와 같은 제품 이름이나 인용된 시트 이름을 참조하는 수식은 여러분 측에서의 어떠한 이스케이프 처리 없이도 올바른 형식의 XML로 산출됩니다

수명 주기 및 Destroy가 여전히 Close하는 이유

패키지를 완료하는 것은 통합 문서 파트, 스타일 파트, 콘텐츠 유형 및 관계 파트, 그리고 마지막으로 zip 중앙 디렉토리를 작성하는 작업입니다. 이 작업은 Close에서 일어납니다. 닫히지 않은 패키지는 스프레드시트 프로그램이 열 수 없는 불완전한 zip 파일이므로 닫는 것은 선택적인 정리가 아니라 파일을 유효하게 만드는 단계입니다. 오류 경로에서 Close를 잊어버리는 상황에 대비하여, 패키지가 아직 열려 있다면 Destroy가 최선을 다해 닫기를 수행하므로 예외 상황에서 명시적인 호출을 건너뛰더라도 쓰기 도구를 해제할 때 기본 zip 객체가 누출되지 않습니다. 신뢰할 수 있는 패턴은 여전히 일반적인 Delphi 패턴과 같습니다. try 내에서 작성하고 Close를 호출하며 finally에서 해제하는 것입니다

대형 시트를 처음부터 끝까지 스트리밍하기

작업의 형태는 시작하고, 시트를 추가하고, 행을 쏟아붓고, 닫는 것입니다. 아래 예제는 헤더 행을 작성한 다음 문자열, 숫자, 캐시된 결과가 없는 수식 및 날짜를 혼합하여 형식이 지정된 긴 데이터 행을 씁니다. 10개 행에 사용하는 메모리나 천만 개 행에 사용하는 메모리가 동일합니다. 왜냐하면 모든 셀은 기록되는 즉시 zip 스트림으로 빠져나가기 때문입니다

uses
  lxDirectWrite;

procedure StreamReport(const Path: string; RowCount: Integer);
var
  W: TXLSDirectWriter;
  I: Integer;
begin
  W := TXLSDirectWriter.Create;
  try
    W.BeginFile(Path);
    W.AddSheet('Sales');

    // 오름차순 열 순서로 작성된 헤더 행
    W.AddRow(1);
    W.WriteString(1, 'Item');
    W.WriteString(2, 'Qty');
    W.WriteString(3, 'Price');
    W.WriteString(4, 'Total');
    W.WriteString(5, 'Date');

    // 데이터 행; AddRow에 0을 전달하여 자동으로 다음 행을 가져옴
    for I := 1 to RowCount do
    begin
      W.AddRow(0);
      W.WriteString(1, 'Item ' + IntToStr(I));
      W.WriteNumber(2, I);
      W.WriteNumber(3, 1.5 + (I mod 10));
      W.WriteFormula(4, Format('B%d*C%d', [I + 1, I + 1]));
      W.WriteDateTime(5, EncodeDate(2026, 1, 1) + I);
    end;

    W.Close;                       // 패키지를 최종적으로 마무리합니다
  finally
    W.Free;
  end;
end;

두 번째 시트는 계속하기 전에 또 다른 AddSheet를 호출하면 되며, 쓰기 도구는 두 번째 시트를 열면서 첫 번째 시트를 닫습니다. 논리 플래그는 텍스트 "True" 대신 형식이 지정된 논리 셀을 작성하는 WriteBoolean을 사용합니다. 파일이 온전하고 라운드트립이 잘 되는지 확인하려면 CellCount 속성을 통해 얼마나 많은 셀이 작성되었는지 알 수 있으며, 스트리밍 리더로 다시 읽을 때도 동일한 총합이 보고되어야 합니다

  // 위의 데이터 시트 뒤에 형식이 지정된 플래그들의 두 번째 시트 추가
  W.AddSheet('Flags');
  W.AddRow(1);
  W.WriteString(1, 'Name');
  W.WriteString(2, 'Active');
  W.AddRow(0);
  W.WriteString(1, 'alpha');
  W.WriteBoolean(2, True);

  WriteLn(Format('wrote %d cells', [W.CellCount]));

파일 대신 스트림에 쓰는 것은 BeginFile 대신 BeginStream을 사용하는 것을 빼면 동일한 코드입니다. 이를 통해 서버는 디스크에 임시 파일을 만들지 않고도 HTTP 응답이나 메모리 스트림으로 통합 문서를 보낼 수 있습니다. 쓰기 도구는 여러분이 전달한 스트림을 소유하지 않으므로 수명 주기에 대한 제어권은 여전히 여러분에게 있습니다

이 작업이 요청에 따라 통합 문서를 구축하는 서버 엔드포인트라면 서버 및 일괄 작업을 위한 스트리밍 쓰기 기사의 패턴이 이 기능을 요청 처리기(request handler)와 예약된 내보내기 작업에 어떻게 연결하는지 보여줍니다. 쓰기와 읽기 모두에서 매우 큰 통합 문서의 전반적인 비용이 궁금하다면 Delphi에서의 대형 통합 문서 성능 기사에서 실제로 시간과 메모리가 어디에 소모되는지 다룹니다. 스트리밍 직접 쓰기 도구는 이 블로그의 다른 곳에서 다루는 전체 읽기, 편집 및 저장 API와 함께 Delphi 및 C++Builder용 HotXLS Component의 일부로 제공됩니다