기술 문서

HotXLS로 Delphi에서 Excel 2.0부터 4.0까지 파일 읽기

1992년 Excel 4.0이 저장한 워크북은 바이트 09 04 06 00으로 시작하고, Excel 5.0 이후의 모든 .xls는 복합 파일 시그니처 D0 CF 11 E0으로 시작합니다. 그 시그니처를 검사하는 리더는 셀 하나 읽기도 전에 오래된 파일을 거부하고, 검사를 건너뛰고 BIFF8을 가정하는 리더는 첫 레코드를 엉뚱한 것으로 파싱합니다. 두 실패는 같은 가정에서 나옵니다. 모든 .xls에는 컨테이너가 있다는 가정입니다. Excel 2.0, 3.0, 4.0 파일에는 컨테이너가 없으므로, 레코드 스트림 스스로가 어느 세대가 썼는지 말해야 하고 셀 레코드는 그 세대의 레이아웃으로 디코딩되어야 합니다

이런 파일은 여전히 등장합니다. 그것이 이 모든 이야기가 중요한 유일한 이유입니다. 엔지니어링 아카이브, 기록 보존 저장소, 1990년대 초반 제어 소프트웨어를 쓰는 계측기의 실험실 내보내기, 오래 운영되어 온 회계 시스템이 모두 BIFF2부터 BIFF4 워크북을 남겼습니다. 최신 Excel 빌드는 File Block 설정으로 이 형식들 중 여럿을 기본적으로 차단하므로, 데이터 세트는 보통 아무도 손에 쥔 도구로 열 수 없는 폴더에 앉아 있습니다

OLE 이전 워크북은 자신을 어떻게 알리는가

HotXLS는 모든 .xls를 로드하는 같은 TXLSWorkbook.Open 호출 안에서, 첫 레코드로 OLE 이전 워크북을 알아봅니다. Excel 5.0 이후의 모든 .xls는 OLE2 복합 파일, 즉 파일 안에 든 작은 파일 시스템이며 워크북은 Workbook이나 Book이라는 이름의 스트림에 삽니다. 파싱은 그 컨테이너 파싱에서 시작되며, Pascal에서 다루는 복합 파일 바이너리 형식에서 설명합니다

BIFF2부터 BIFF4는 그 모든 것을 건너뜁니다. 파일은 곧바로 BOF 레코드로 시작하고 그 BOF의 레코드 번호가 세대를 인코딩합니다. BIFF2는 $0009, BIFF3는 $0209, BIFF4는 $0409입니다. 이후 형식이 의존하는 본문 안의 버전 워드는 이 세대들이 무시하므로, 레코드 id만이 믿을 수 있는 신호입니다. 원시 경로에 투입하기 전에 HotXLS 스트림 계층은 두 가지를 더 검사합니다. BOF 본문은 4바이트에서 6바이트 사이여야 하고, 서브스트림 타입 워드는 워크시트면 $0010, 차트면 $0020, 매크로 시트면 $0040이어야 합니다. 그 외의 것은 Open이 오류 코드를 반환하게 만듭니다

이 검사들은 보이는 것보다 중요합니다. 오프셋 0의 $09 $00 두 바이트는 임의의 바이너리 데이터에서 희귀하지 않고, 정체불명의 .xls 격리 폴더에는 이름만 바꾼 텍스트 내보내기와 잘린 사본, 전혀 다른 프로그램의 파일이 섞여 있습니다. 세 독립 필드가 모두 일치하는 것은 하나보다 훨씬 강한 신호입니다

Delphi에서 HotXLS가 OLE 이전 Excel 파일을 감지하는 방법 다이어그램: 한쪽은 현대 OLE2 컨테이너 통합문서, 다른 쪽은 원시 BIFF2~BIFF4 레코드 스트림이며, 레거시 경로를 확정하기 전에 BOF 레코드 필드를 검사
레거시 파일은 저장소 래퍼가 없으므로, 선두 BOF 레코드 자체가 어떤 셀도 읽기 전에 세대, 본문 길이, 서브스트림 유형을 증명합니다

세 세대, 세 가지 레코드 레이아웃

셀 레코드가 세대가 가장 눈에 띄게 갈라지는 곳입니다. BIFF2는 blank, integer, number, label, boolean-or-error 셀에 대해 $0001부터 $0005까지 낮은 레코드 번호의 연속된 블록을 차지합니다. 각 본문은 이후 버전이 2바이트 확장 서식 인덱스를 두는 자리에 3바이트 속성 필드를 두므로, 모든 값이 레코드 안에서 한 바이트 뒤로 밀립니다. BIFF2 INTEGER 레코드는 자기만의 별종입니다. 소수부가 없는 부호 없는 16비트 값이고, BIFF3이 RK로 대체하며 버린 레코드 타입입니다

BIFF3와 BIFF4는 낮은 번호를 버리고 BIFF5 레코드 id와 레이아웃을 재사용합니다. $0201, $0203, $0204, $0205, $027E에 각각 2바이트 XF 인덱스입니다. 이 재사용이 특정하고 오진하기 쉬운 실패를 낳습니다. BIFF3나 BIFF4 LABEL 레코드는 구조적으로 BIFF5 대응 레코드와 동일합니다. 행, 열, 서식 인덱스, 그다음 오프셋 6의 2바이트 문자 수입니다. 그 레이아웃을 BIFF2 레이블에 적용하면 문자 수가 마지막 속성 바이트와 실제 1바이트 길이로 조립되어, 다섯 글자 레이블이 최소 1,280자를 주장하고 리더는 레코드 끝을 넘어 달립니다. 반대로 BIFF3 레코드에 BIFF2 레이아웃을 적용하면 길이가 문자 수의 상위 바이트에서 읽히는데, 짧은 문자열에서는 0입니다. 어느 방향으로도 아무것도 던져지지 않습니다. 워크북은 빈 레이블이나 그럴듯한 쓰레기를 들고 돌아오고, HotXLS는 BOF에서 가져온 세대와 레코드 id를 함께 봐서 디스패치함으로써 둘 다 피합니다

BIFF2 속성 필드는 함정을 하나 더 숨깁니다. 첫 바이트가 하위 6비트에 서식 인덱스를 담고, 값 $3F는 "진짜 인덱스가 6비트에 너무 크다. 앞의 IXFE 레코드를 보라"를 뜻합니다. HotXLS는 가장 최근의 IXFE를 추적해 셀에 스타일을 배정하기 전에 이스케이프를 풀므로, 서식 인덱스가 6비트를 넘는 셀도 자기 서식을 받습니다

Delphi에서 HotXLS가 읽는 BIFF2와 BIFF3/BIFF4 셀 레코드 레이아웃 비교: 세대별 레코드 번호, LABEL 필드 레이아웃 차이, 잘못 읽는 실패 모드, 병렬 formula 및 string 레코드 번호 매기기를 보여줌
BIFF2 본문은 3바이트 속성 블록을 실은 반면 BIFF3와 BIFF4는 2바이트 XF 인덱스로 BIFF5 번호를 재사용하며, 레이아웃을 섞으면 레코드 뒤의 모든 것이 조용히 어긋납니다

세 세대 모두의 텍스트는 유니코드가 전혀 없는 평범한 바이트 문자열입니다. 인코딩은 파일의 CODEPAGE 레코드에서 오는데, 이 파일들에서는 워크북 수준 스트림이 없어 그 레코드가 워크시트 스트림 하나 안에 앉아 있습니다. HotXLS는 레이블과 문자열 결과를 그 코드 페이지로 디코딩하고 레코드가 없으면 Windows-1252로 폴백하는데, 이 파일들 대부분이 쓰던 서유럽 기본값을 커버합니다. CODEPAGE 레코드가 없는 일본어나 중앙유럽 워크북은 따라서 라틴 텍스트로 디코딩되고, 유일한 해결은 파일이 어디서 왔는지 아는 것입니다

Delphi에서 열기

감지는 Open 안에서 일어나므로 호출 코드는 어떤 워크북이나 같습니다. 아래 루프는 사용된 범위를 훑으며 수식을 담은 셀은 두 번 출력합니다. 번역된 식과, 셀의 숫자 서식을 거쳐 Excel이 보여 줄 값입니다

uses
  SysUtils, Variants, lxHandle;

var
  Book: TXLSWorkbook;
  Sheet: TXLSWorksheet;
  Cell: IXLSRange;
  R, C: Integer;
begin
  Book := TXLSWorkbook.Create;
  try
    try
      if Book.Open('archive\1992-inventory.xls') <> 1 then
      begin
        Writeln('not a readable workbook - quarantine for manual review');
        Exit;
      end;
    except
      // 파일이 진짜로 ZIP 패키지일 때 Open은 예외를 던집니다.
      // 보통 누군가 .xls로 이름만 바꾼 .xlsx입니다
      on E: Exception do
      begin
        Writeln('rejected: ', E.Message);
        Exit;
      end;
    end;

    Sheet := Book.Sheets[1];          // Sheets[] is 1-based
    for R := Sheet.UsedRange.FirstRow + 1 to Sheet.UsedRange.LastRow + 1 do
      for C := Sheet.UsedRange.FirstCol + 1 to Sheet.UsedRange.LastCol + 1 do
      begin
        Cell := Sheet.Cells[R, C];
        if VarIsEmpty(Cell.Value) or VarIsNull(Cell.Value) then
          Continue;
        if Cell.HasFormula then
          Writeln(Format('R%dC%d  %s  -> %s',
            [R, C, VarToStr(Cell.Formula), Cell.FormattedText]))
        else
          Writeln(Format('R%dC%d  %s', [R, C, Cell.FormattedText]));
      end;
  finally
    Book.Free;
  end;
end;

인덱스 산술을 조심하세요. UsedRange 경계는 0 기반이지만 시트 컬렉션과 셀 접근은 1 기반인데, 이 불일치는 기존 코드와의 호환성을 위해 API가 유지하는 것입니다. 보정을 잊으면 루프는 아무 이상도 보고하지 않은 채 엉뚱한 사각형을 감사합니다. 이 경로가 받는 파일은 워크시트, 차트, 매크로 시트 서브스트림 중 하나만 갖고 있으므로 Sheets[1]이 유일한 시트입니다. Excel 4.0 바운드 워크북(BOF 타입 $0100)은 그 집합 밖이고 Open은 오류 코드를 반환합니다. 파일을 아예 로드하지 않는 저렴한 사전 점검은 가벼운 워크북 검사에서 다룹니다

비밀번호 보호 파일도 같은 단일 스트림 규칙을 따릅니다. FILEPASS 레코드는 워크시트 스트림에서 BOF 바로 뒤에 나타나고, HotXLS는 그것을 집어 이 세대들이 쓰던 레거시 XOR 디오퍼스케이션을 켭니다. 비밀번호는 Open(FileName, APassWord) 오버로드로 넘기세요

BIFF2부터 BIFF4 파일에서 수식은 어떻게 되는가?

HotXLS는 레거시 수식의 양쪽 절반을 읽습니다. Excel이 마지막으로 계산했을 때 저장한 캐시된 결과와, 로드 동안 현재 계산 모델로 번역되는 토큰 스트림입니다. 수식 레코드는 세 세대에서 $0006, $0206, $0406으로 병렬 번호를 공유하지만, 본문은 BIFF8 파서가 걸려 넘어질 정확히 그 자리들에서 다릅니다

캐시된 결과는 속성 필드 때문에 BIFF2에서는 7바이트에서, BIFF3와 BIFF4에서는 6바이트에서 시작합니다. 숫자가 아닌 결과는 그 8바이트 슬롯의 마지막 2바이트에 $FFFF 마커 워드를 쓰고 첫 바이트의 판별자를 둡니다. 0은 문자열, 1은 불리언, 2는 오류 코드, 3은 빈 칸입니다. 문자열 결과는 슬롯에 아예 안 들어가므로 텍스트는 다음 레코드로 옵니다. BIFF2는 1바이트 길이의 $0007, BIFF3와 BIFF4는 2바이트 길이의 $0207입니다

Delphi에서 HotXLS가 읽는 BIFF2~BIFF4 FORMULA 레코드의 캐시된 수식 결과 해부도: 값은 BIFF2에서 7바이트, BIFF3과 BIFF4에서 6바이트에 시작하며, 문자열, 불리언, 오류, 빈 칸용 FFFF 마커 워드를 지님
HotXLS는 번역된 표현식 옆에 Excel이 마지막으로 계산한 값을 유지하고, 비숫자 결과는 마커 워드와 판별자로 도착합니다

토큰 스트림은 성급한 구현이 무의미한 것을 만들어 내는 곳입니다. BIFF2는 오프셋 16에 1바이트 길이를 붙이고 토큰은 17부터이고, BIFF3와 BIFF4는 2바이트 길이로 18부터입니다. 스트림 안에서 함수 토큰은 BIFF5 전까지는 1바이트 함수 인덱스를, BIFF5부터는 2바이트 인덱스를 갖습니다. BIFF4 토큰을 그대로 BIFF8 번역기에 먹이면 모든 함수 호출이 한 바이트씩 더 소모되어, 결과는 약간 틀린 수식이 아니라 무관한 수식입니다. HotXLS 번역기는 BIFF 버전을 매개변수로 받아 각 토큰을 그 세대의 폭으로 읽습니다

양쪽 절반을 갖게 되면 실용적인 질문이 생깁니다. 이전된 워크북에는 어느 숫자를 넣을 것인가? 캐시된 값은 파일의 사용자들이 1992년에 보고, 인쇄하고, 서명했던 것입니다. 현대 엔진의 재계산은 오늘날 그 수식이 의미하는 것을 계산하며, 함수 동작은 30년간 제자리에 있지 않았습니다. 아카이브 이전에서 캐시된 값이 기록이므로, 그것을 유지하고 재계산은 어느 셀을 사람의 눈으로 봐야 하는지 알려 주는 교차 검사로만 돌리세요:

// 교차 검사: 저장된 결과와 새 재계산 결과를 비교합니다.
// 이 헬퍼와 CachedText 배열은 직접 작성하는 코드입니다
procedure CompareWithRecalc(Book: TXLSWorkbook; Sheet: TXLSWorksheet);
var
  CachedText: array of array of string;
  Rows, Cols, R, C: Integer;
begin
  Rows := Sheet.UsedRange.LastRow + 1;
  Cols := Sheet.UsedRange.LastCol + 1;
  SetLength(CachedText, Rows + 1, Cols + 1);
  for R := 1 to Rows do
    for C := 1 to Cols do
      if Sheet.Cells[R, C].HasFormula then
        CachedText[R, C] := VarToStr(Sheet.Cells[R, C].Value);

  Book.Recalculate;

  for R := 1 to Rows do
    for C := 1 to Cols do
      if Sheet.Cells[R, C].HasFormula and
         (VarToStr(Sheet.Cells[R, C].Value) <> CachedText[R, C]) then
        Writeln(Format('R%dC%d  stored=%s  now=%s  formula=%s',
          [R, C, CachedText[R, C], VarToStr(Sheet.Cells[R, C].Value),
           VarToStr(Sheet.Cells[R, C].Formula)]));
end;

Recalculate는 메모리에서 저장된 결과를 대체하므로 사본에서, 또는 저장 전에 이것을 돌리세요. 빈 리포트가 원하는 결과입니다. 짧은 리포트는 대개 NOW() 같은 휘발성 함수나 원저자가 매달아 둔 채로 둔 참조를 가리킵니다

서식, 날짜, 그리고 General 서식의 함정

HotXLS는 이 세대들의 서식 레코드를 현대 .xls가 만드는 것과 같은 워크북 모델로 변환합니다. FONT 레코드(별도의 BIFF2 FONTCOLOR 레코드를 접어 넣어), FORMAT 문자열, 각 세대의 XF 레코드가 채움 패턴과 테두리 스타일, 레거시 색상 인덱스와 함께 Font, NumberFormat, 나머지 범위 속성으로 읽고 편집할 수 있는 평범한 폰트와 숫자 서식, 셀 스타일이 됩니다

날짜는 직접 이득을 봅니다. 숫자 셀이 날짜 숫자 서식을 갖고 있으면 리더는 워크북의 DATEMODE 플래그도 적용해, 1904 체계 워크북이 1900 체계와 같은 TDateTime 스케일에 착지하고 FormattedText가 Excel이 보여 주던 날짜를 보여 줍니다. 1462일 오프셋 뒤의 규칙은 날짜 일련번호, 1904 체계, 숫자 서식에서 다룹니다

함정은 General 서식 아래 날짜를 담는 열입니다. 1990년대 워크북 상당수는 매크로나 임포트가 서식을 챙길 생각 없이 일련번호만 써서 채웠고, 파일은 33604가 부품 번호가 아니라 1992년 1월의 어느 날을 뜻한다는 신호를 주지 않습니다. 그런 셀은 저장된 그대로, 날짜 체계 보정 없이 돌아옵니다. 원본에 대해 아는 것을 근거로 열별로 결정하고 그 열들은 직접 변환하세요:

// ColumnHoldsDates는 원본 시스템에 대한 지식으로 직접 만든 조회이며
// 레거시 파일은 이 질문에 답해 주지 않습니다.
// Serial은 지역 Double이고 Cell과 C는 첫 목록에서 옵니다
if ColumnHoldsDates(C) and (Pos('d', LowerCase(Cell.NumberFormat)) = 0) then
begin
  // General 서식 일련번호: 아직 날짜 체계 보정은 적용되지 않음
  Serial := Cell.Value;
  if Book.Date1904 then
    Serial := Serial + 1462;
  Writeln(DateToStr(TDateTime(Serial)))
end
else
  Writeln(Cell.FormattedText);

Pos('d', ...) 검사는 의도적으로 성급하며 서식에 이미 일(day) 토큰을 갖는 셀을 건너뛰는 용도입니다. 아카이브가 크다면 알려진 날짜 서식 목록을 상대로 더 엄격한 검사를 쓰는 것이 몇 줄 더 들 값어치를 합니다

이전된 사본 쓰기

BIFF2부터 BIFF4 파일에서 열은 워크북은 BIFF5/BIFF8 또는 XLSX 작성기로 저장됩니다. 현대 Excel이 차단하는 형식으로 새 파일을 만들 이유가 없기 때문입니다. Book.SaveAs('migrated\1992-inventory.xls', xlExcel97)는 값과 번역된 수식, 변환된 서식을 갖는 BIFF8 사본을 쓰고, 파이프라인의 모든 후속 도구가 특수 처리 없이 열 수 있습니다. 원본을 옆에 보관하세요. 2년 뒤에 어떤 셀이 의문을 품으면 1992년 바이트만이 유일한 권위입니다

레거시 읽기와 현대 쓰기, 재계산, 서식은 Delphi와 C++Builder용 하나의 라이브러리에 함께 살며, 형식 커버리지는 HotXLS Delphi 스프레드시트 컴포넌트 페이지에 나열되어 있습니다