Artykuł techniczny

Odczyt plików Excel 2.0-4.0 w Delphi za pomocą HotXLS

Skoroszyt zapisany przez Excel 4.0 w 1992 roku zaczyna się od bajtów 09 04 06 00, podczas gdy każdy .xls od Excel 5.0 wzwyż zaczyna się od sygnatury pliku złożonego D0 CF 11 E0. Czytnik sprawdzający tę sygnaturę odrzuca starszy plik, zanim przeczyta choć jedną komórkę, a czytnik, który pomija sprawdzenie i zakłada BIFF8, parsuje pierwszy rekord jako coś, czym nie jest. Oba niepowodzenia wychodzą z tego samego założenia: że każdy .xls ma kontener. Pliki Excel 2.0, 3.0 i 4.0 nie mają żadnego, więc sam strumień rekordów musi powiedzieć, która generacja go napisała, a rekordy komórek muszą być dekodowane układem tej generacji

Te pliki wciąż się pojawiają, i to jedyny powód, dla którego cokolwiek z tego ma znaczenie. Archiwa inżynieryjne, przechowalnie dokumentacji, eksporty laboratoryjne z przyrządów, których oprogramowanie sterujące pochodzi z wczesnych lat 90., i długo działające systemy księgowe zostawiły po sobie skoroszyty BIFF2 do BIFF4. Współczesne buildy Excela blokują kilka z tych formatów domyślnie przez ustawienia File Block, więc zbiór danych często siedzi w folderze, którego nikt nie otworzy narzędziami na swoim biurku

Jak skoroszyt sprzed OLE się zawiadamia

HotXLS rozpoznaje skoroszyt sprzed OLE po jego pierwszym rekordzie, wewnątrz tego samego wywołania TXLSWorkbook.Open, które wczytuje każdy inny .xls. Każdy .xls od Excel 5.0 wzwyż to plik złożony OLE2, mały system plików wewnątrz pliku, ze skoroszytem mieszkającym w strumieniu o nazwie Workbook albo Book; parsowanie zaczyna się od parsowania tego kontenera, jak opisano w binarnym formacie pliku złożonego w Pascalu

BIFF2 do BIFF4 pomijają to wszystko. Plik zaczyna się od razu rekordem BOF, a numer rekordu tego BOF koduje generację: $0009 dla BIFF2, $0209 dla BIFF3 i $0409 dla BIFF4. Słowo wersji wewnątrz ciała, na którym polegają późniejsze formaty, jest przez te generacje ignorowane, więc identyfikator rekordu to jedyny godny zaufania sygnał. Zanim zaangażuje się w surową ścieżkę, warstwa strumieni HotXLS sprawdza jeszcze dwie rzeczy: ciało BOF musi mieć od czterech do sześciu bajtów, a słowo typu podstrumienia musi wynosić $0010 dla arkusza, $0020 dla wykresu albo $0040 dla arkusza makr. Cokolwiek innego każe Open zwrócić kod błędu

Te sprawdzenia mają większe znaczenie, niż wyglądają. Dwa bajty $09 $00 na offsecie zerowym nie są rzadkie w dowolnych danych binarnych, a folder kwarantanny niezidentyfikowanych plików .xls ma w zwyczaju zawierać przemianowane eksporty tekstowe, obcięte kopie i okazjonalny plik z zupełnie innego programu. Trzy niezależne pola zgadzające się naraz to o wiele mocniejszy sygnał niż jedno

Diagram wykrywania plików Excel sprzed OLE przez HotXLS z Delphi: nowoczesne skoroszyty w kontenerze OLE2 po jednej stronie, surowe strumienie rekordów BIFF2 do BIFF4 po drugiej, a pola rekordu BOF sprawdzane, zanim ścieżka przestarzała zostanie wybrana
Starsze pliki nie niosą owijki magazynu, więc wiodący rekord BOF sam dowodzi generację, długość ciała i typ podstrumienia, zanim jakakolwiek komórka zostanie przeczytana

Trzy generacje, trzy układy rekordów

Rekordy komórek to miejsce, gdzie generacje rozjeżdżają się najbardziej widocznie. BIFF2 zajmuje spójny blok niskich numerów rekordów, $0001 do $0005, dla komórek pustych, całkowitych, liczbowych, etykiet i logiczno-błędnych. Każde z tych ciał niesie trójbajtowe pole atrybutu tam, gdzie późniejsze wersje kładą dwubajtowy rozszerzony indeks formatu, więc każda wartość siedzi o bajt dalej w rekordzie. Rekord INTEGER BIFF2 to osobna osobliwość: nieujemna wartość 16-bitowa bez części ułamkowej, typ rekordu, który BIFF3 wyrzucił na rzecz RK

BIFF3 i BIFF4 porzucają niskie numery i ponownie używają identyfikatorów oraz układów rekordów BIFF5: $0201, $0203, $0204, $0205 i $027E, każdy z dwubajtowym indeksem XF. To ponowne użycie powoduje specyficzne i łatwe do błędnej diagnozy niepowodzenie. Rekord LABEL BIFF3 albo BIFF4 jest strukturalnie identyczny ze swoim odpowiednikiem z BIFF5: wiersz, kolumna, indeks formatu, potem dwubajtowa liczba znaków na offsecie 6. Zastosuj ten układ do etykiety BIFF2, a liczba zostanie złożona z ostatniego bajtu atrybutu i prawdziwej jednobajtowej długości, więc pięcioznakowa etykieta twierdzi co najmniej 1,280 znaków, a czytnik ucieka za koniec rekordu. Idź w drugą stronę — układ BIFF2 na rekordzie BIFF3 — a długość jest czytana ze starszego bajtu liczby, który dla każdego krótkiego łańcucha jest zerem. Nic nie rzuca w żadnym kierunku. Skoroszyt wraca z pustymi etykietami albo wiarygodnie wyglądającymi śmieciami, a HotXLS unika obu, rozsyłając po identyfikatorze rekordu razem z generacją wziętą z BOF

Pole atrybutu BIFF2 chowa jeszcze jedną pułapkę. Jego pierwszy bajt trzyma indeks formatu w młodszych sześciu bitach, a wartość $3F znaczy "prawdziwy indeks jest za duży na sześć bitów, spójrz na poprzedzający rekord IXFE". HotXLS śledzi najnowszy IXFE i rozwiązuje escape przed przydzieleniem komórce stylu, więc komórka, której indeks formatu potrzebuje więcej niż sześciu bitów, i tak dostaje swoje formatowanie

Porównanie układów rekordów komórek BIFF2 i BIFF3/BIFF4 czytanych przez HotXLS w Delphi, pokazujące numery rekordów per generacja, różnicę układu pól LABEL, tryb błędnego odczytu oraz równoległą numerację rekordów formuł i ciągów
Ciała BIFF2 niosą trójbajtowy blok atrybutów, podczas gdy BIFF3 i BIFF4 używają ponownie numerów BIFF5 z dwubajtowym indeksem XF, a mieszanie układów po cichu rozjeżdża wszystko za rekordem

Tekst we wszystkich trzech generacjach to goły łańcuch bajtowy bez Unicode gdziekolwiek. Kodowanie pochodzi z rekordu CODEPAGE pliku, który w tych plikach siedzi wewnątrz jedynego strumienia arkusza, bo nie ma strumienia na poziomie skoroszytu, który by go trzymał. HotXLS dekoduje etykiety i wyniki łańcuchowe przez tę stronę kodową i przechodzi na Windows-1252, gdy rekordu brak, co pokrywa zachodni domyślny, z którym większość tych plików została zapisana. Japoński albo środkowoeuropejski skoroszyt bez rekordu CODEPAGE dekoduje się więc jako tekst łaciński, a jedyną poprawką jest wiedzieć, skąd plik przyszedł

Otwieranie takiego pliku z Delphi

Wykrywanie dzieje się wewnątrz Open, więc kod wołający jest taki sam jak dla każdego skoroszytu. Pętla niżej obchodzi używany zakres i wypisuje każdą komórkę podwójnie, gdy trzyma formułę: przetłumaczone wyrażenie i wartość tak, jak wyświetliłby ją Excel przez format liczby komórki

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
      // Open rzuca, gdy plik to naprawdę pakiet ZIP,
      // typycznie .xlsx, który ktoś przemianował na .xls
      on E: Exception do
      begin
        Writeln('rejected: ', E.Message);
        Exit;
      end;
    end;

    Sheet := Book.Sheets[1];          // Sheets[] jest liczone od 1
    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;

Uważaj na arytmetykę indeksów. Granice UsedRange są liczone od zera, podczas gdy i kolekcja arkuszy, i dostęp do komórek są liczone od jedynki — niespójność, którą API trzyma dla kompatybilności z istniejącym kodem. Zapomnij o korekcie, a pętla audytuje niewłaściwy prostokąt, nie zgłaszając niczego niezwykłego. Pliki, które ta ścieżka przyjmuje, niosą pojedynczy podstrumień arkusza, wykresu albo arkusza makr, więc Sheets[1] to jedyny arkusz, jaki znajdziesz; powiązany skoroszyt Excel 4.0 (typ BOF $0100) wypada poza ten zbiór i Open zwraca kod błędu. Tanio sprawdzanie wstępne, które w ogóle omija wczytywanie pliku, opisuje lekka inspekcja skoroszytu

Pliki chronione hasłem podążają za tą samą regułą pojedynczego strumienia. Rekord FILEPASS pojawia się tuż za BOF w strumieniu arkusza, gdzie HotXLS go podnosi i włącza legacy'owe odszyfrowanie XOR, którego używały te generacje; hasło podaj przez przeciążenie Open(FileName, APassWord)

Co się dzieje z formułami w pliku BIFF2 do BIFF4?

HotXLS czyta obie połowy przestarzałej formuły: zbuforowany wynik, który Excel zapisał przy ostatnim obliczeniu, i strumień tokenów, który jest tłumaczony na bieżący model obliczeń podczas wczytywania. Rekordy formuł dzielą równoległe numerowanie przez trzy generacje — $0006, $0206 i $0406 — ale ciała różnią się dokładnie w miejscach, na których potknąłby się parser BIFF8

Zbuforowany wynik startuje na bajcie 7 w BIFF2 — przez pole atrybutu — i na bajcie 6 w BIFF3 i BIFF4. Wyniki nienumeryczne używają słowa znacznika $FFFF w ostatnich dwóch bajtach tego ośmiobajtowego slocika, z dyskryminatorem w pierwszym bajcie: 0 dla łańcucha, 1 dla booleana, 2 dla kodu błędu, 3 dla pustego. Wynik łańcuchowy nie mieści się w slociku wcale, więc tekst przychodzi w następnym rekordzie — $0007 w BIFF2 z jednobajtową długością albo $0207 w BIFF3 i BIFF4 z dwubajtową

Anatomia buforowanego wyniku formuły w rekordach FORMULA BIFF2 do BIFF4 czytanych przez HotXLS z Delphi: wartość startuje od bajtu 7 w BIFF2 i bajtu 6 w BIFF3 i BIFF4, ze słowem znacznika FFFF dla ciągów, wartości logicznych, błędów i pustych
HotXLS zwraca wartość ostatnio policzoną przez Excel zamiast tłumaczyć tokeny wyrażenia, a nienumeryczne wyniki przybywają jako słowo znacznikowe plus dyskryminator

Strumień tokenów to miejsce, gdzie naiwna implementacja produkuje bezsens. BIFF2 poprzedza wyrażenie jednobajtową długością na offsecie 16 i zaczyna tokeny na 17; BIFF3 i BIFF4 używają dwubajtowej długości i zaczynają na 18. Wewnątrz strumienia tokeny funkcji niosą jednobajtowy indeks funkcji przed BIFF5 i dwubajtowy od BIFF5 wzwyż. Podaj tokeny BIFF4 niezmienione tłumaczowi BIFF8, a każde wywołanie funkcji zje o bajt za dużo, więc wynikiem nie jest lekko zła formuła, lecz niepowiązana. Tłumacz HotXLS bierze wersję BIFF jako parametr i czyta każdy token szerokościami tej generacji

Miejąc obie połowy, buduje się praktyczne pytanie: która liczba wchodzi do migrowanego skoroszytu? Zbuforowana wartość to to, co użytkownicy pliku widzieli, drukowali i poświadczali w 1992 roku. Przeliczenie na nowoczesnym silniku liczy, co formuła znaczy dziś, a zachowanie funkcji nie stało w miejscu przez trzy dekady. Dla migracji archiwum zbuforowana wartość jest zapisem, więc trzymaj ją i uruchamiaj przeliczenie tylko jako sprawdzenie krzyżowe, które mówi ci, które komórki zasługują na ludzkie spojrzenie:

// Sprawdzenie krzyżowe: porównaj zapisane wyniki ze świeżym przeliczeniem.
// Ten helper i jego tablica CachedText to twój własny kod
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;

Uruchamiaj to na kopii albo przed zapisem, bo Recalculate zastępuje zapisane wyniki w pamięci. Pusty raport to wynik, którego chcesz; krótki zwykle wskazuje funkcje zmienne, takie jak NOW(), albo referencje, które pierwotny autor zostawił wiszące

Formatowanie, daty i pułapka formatu General

HotXLS zamienia rekordy formatowania tych generacji w ten sam model skoroszytu, jaki produkuje nowoczesny .xls. Rekordy FONT (z osobnym rekordem FONTCOLOR BIFF2 wchłoniętym w środek), łańcuchy FORMAT i rekordy XF każdej generacji, ze swoimi wzorami wypełnienia, stylami obramowań i przestarzałymi indeksami kolorów, stają się zwykłymi fontami, formatami liczb i stylami komórek, które czytasz i edytujesz przez Font, NumberFormat i pozostałe właściwości zakresu

Daty zyskują wprost. Gdy komórka liczbowa niesie format liczby daty, czytnik stosuje też flagę DATEMODE skoroszytu, więc skoroszyt w systemie 1904 ląduje na tej samej skali TDateTime co system 1900, a FormattedText pokazuje datę tak, jak zrobił to Excel. Reguły za przesunięciem 1462 dni opisuje liczby seryjne dat, system 1904 i formaty liczb

Pułapką jest kolumna trzymająca daty pod formatem General. Mnóstwo skoroszytów lat 90. było wypełnianych makrami albo importami, które zapisywały liczby seryjne, nie macząc się z ich formatowaniem, a plik nie daje sygnału, że 33604 znaczy dzień w styczniu 1992, a nie numer części. Te komórki wracają dokładnie tak, jak zostały zapisane, bez korekty na system dat. Rozstrzygaj per kolumna, z tego, co wiesz o źródle, i konwertuj te kolumny sam:

// ColumnHoldsDates to twoje własne wyszukiwanie, zbudowane z wiedzy o
// systemie źródłowym; stary plik nie odpowie ci na to pytanie.
// Serial to lokalny Double; Cell i C pochodzą z pierwszego listingu
if ColumnHoldsDates(C) and (Pos('d', LowerCase(Cell.NumberFormat)) = 0) then
begin
  // Liczba seryjna w formacie General: bez korekty systemu dat na razie
  Serial := Cell.Value;
  if Book.Date1904 then
    Serial := Serial + 1462;
  Writeln(DateToStr(TDateTime(Serial)))
end
else
  Writeln(Cell.FormattedText);

Test Pos('d', ...) jest celowo gruby i służy tylko do pomijania komórek, które już niosą token dnia w swoim formacie; ostrzejsze sprawdzenie względem twojej własnej listy znanych formatów dat jest warte dodatkowych linii, jeśli archiwum jest duże

Zapis migrowanej kopii

Skoroszyt otwarty z pliku BIFF2 do BIFF4 jest zapisywany przez writery BIFF5/BIFF8 albo XLSX, bo nie ma powodu produkować nowych plików w formacie, który współczesny Excel blokuje. Book.SaveAs('migrated\1992-inventory.xls', xlExcel97) zapisuje kopię BIFF8, która niesie wartości, przetłumaczone formuły i skonwertowane style, i każde późniejsze narzędzie w potoku może ją otworzyć bez specjalnej obsługi. Trzymaj oryginał obok: jeśli komórka wypuści pytanie dwa lata od dziś, bajty z 1992 roku są jedynym autorytetem

Odczyt przestarzały, zapis nowoczesny, przeliczanie i formatowanie mieszkają wszystkie w jednej bibliotece dla Delphi i C++Builder; pokrycie formatów jest wypisane na stronie komponentu arkusza kalkulacyjnego HotXLS dla Delphi