Čtení souborů Excel 2.0 až 4.0 v Delphi pomocí HotXLS

HotXLS otevírá sešity zapsané Excelem 2.0, 3.0 a 4.0 přímo z Delphi a C++Builder. Tyto soubory předcházejí kontejneru složeného dokumentu OLE, který používá každý pozdější soubor .xls, takže jde o syrové proudy záznamů BIFF zcela bez úložného obalu, a čtečka postavená pro BIFF8 v nich nenajde jedinou rozpoznatelnou strukturu. Otevření takového souboru používá stejné volání Open jako kterýkoli jiný sešit; čtečka formát rozpozná a přepne cestu

Tyto soubory se stále objevují, a to je jediný důvod, proč na tom všem záleží. Technické archivy, státní evidence s povinností uchovávání záznamů, laboratorní data z přístrojů, jejichž řídicí software byl napsán v roce 1993, i dlouho běžící účetní systémy po sobě zanechaly sešity BIFF2 a BIFF4. Moderní Excel několik z nich rovnou odmítne otevřít, protože z bezpečnostních důvodů odstranil starší převodníky, což zanechá datovou sadu, kterou nikdo neumí přečíst žádným dostupným nástrojem

Čím se sešit z doby před OLE liší?

Každý soubor .xls od Excelu 5.0 výše je složený soubor OLE2, malý souborový systém uvnitř souboru, přičemž sešit žije v proudu nazvaném Workbook nebo Book. Parsování takového souboru začíná parsováním tohoto kontejneru, jak popisuje článek binární formát složeného souboru v Pascalu

BIFF2 až BIFF4 žádný kontejner nemají. Soubor začíná rovnou záznamem BOF a číslo tohoto záznamu BOF kóduje generaci: $0009 pro BIFF2, $0209 pro BIFF3 a $0409 pro BIFF4. HotXLS před přechodem na syrovou cestu ověří délku těla BOF, která je mezi čtyřmi a šesti bajty, a typ podproudu, $0010 pro list, $0020 pro graf a $0040 pro makro list. Právě tato validace brání tomu, aby byl poškozený nebo špatně identifikovaný soubor interpretován jako velmi starý sešit

Tři generace, tři rozvržení záznamů

Nejviditelněji se generace liší v záznamech buněk. BIFF2 zabírá souvislý blok nízkých čísel záznamů, $0001 až $0005 pro prázdnou, celočíselnou, číselnou, popiskovou a booleovskou nebo chybovou buňku, a každé tělo nese třibajtové pole atributů, kam pozdější verze umísťují rozšířený index formátu. BIFF3 a BIFF4 to opouštějí a znovu používají čísla a rozvržení záznamů BIFF5, $0201, $0203, $0204 a $0205, s dvoubajtovým indexem XF

Právě tento poslední detail způsobuje konkrétní a snadno špatně diagnostikované selhání. Záznam LABEL v BIFF3 nebo BIFF4 je strukturálně shodný se svým protějškem v BIFF5: řádek a sloupec, následované indexem formátu a poté počtem znaků. Napište čtečku, která předpokládá rozvržení BIFF2, a přečte o dva bajty méně, poté sejde z konce záznamu a špatně interpretuje vše, co následuje. Příznakem není výjimka; je jím sešit, který se přečte s věrohodně vypadajícím balastem uvnitř

Záznamy vzorců zabírají paralelní číslování napříč všemi třemi generacemi, $0006, $0206 a $0406. Když vzorec produkuje řetězcový výsledek, tento řetězec přichází v samostatném následujícím záznamu, $0007 nebo $0207, a forma BIFF2 používá jednobajtový prefix délky namísto dvoubajtového používaného později

Proč se vzorce vrací jako hodnoty, ne jako text

HotXLS v těchto souborech čte uložený výsledek vzorce a nepokouší se rekonstruovat samotný výraz vzorce. Jde o záměrnou hranici, ne o mezeru čekající na doplnění

Rozebraný výraz v BIFF2 až BIFF4 používá kódování tokenů, které se od BIFF5 a pozdějších liší způsoby přesahujícími kosmetiku: délky tokenů mají jiné prefixy, referenční tokeny mají jiné velikosti a tabulky indexů funkcí byly mezi generacemi přečíslovány. Prohnání těchto bajtů překladačem výrazů BIFF8 nevytvoří chybný vzorec, vytvoří náhodný. Přečtení uložené hodnoty vám dá číslo nebo řetězec, který Excel naposledy vypočítal, což je přesně to, co migrace archivu ve skutečnosti potřebuje

Uložená hodnota žije na offsetu uvnitř záznamu závislém na generaci: bajt 7 pro BIFF2 a bajt 6 pro BIFF3 a BIFF4. Speciální hodnoty, řetězce, booleovské hodnoty, chyby a prázdné buňky, jsou kódovány značkovým slovem $FFFF s rozlišovačem, stejnou konvencí, kterou si zachovaly i pozdější generace BIFF

Otevření souboru

Volající kód je nenápadný, a to je právě podstata. Detekce probíhá uvnitř Open:

uses
  lxHandle;

var
  Book: TXLSWorkbook;
  Sheet: TXLSWorksheet;
  R, C: Integer;
  V: Variant;
begin
  Book := TXLSWorkbook.Create;
  try
    if Book.Open('archive\1993-inventory.xls') <> 1 then
    begin
      Writeln('unreadable - quarantine for manual review');
      Exit;
    end;
    Sheet := Book.Sheets[1];          // Sheets[] je indexováno od jedné
    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
        V := Sheet.Cells[R, C].Value;
        if not VarIsEmpty(V) then
          Writeln(Format('R%dC%d = %s', [R, C, VarToStr(V)]));
      end;
  finally
    Book.Free;
  end;
end;

Všimněte si indexové aritmetiky v této smyčce. Meze UsedRange jsou indexované od nuly, zatímco kolekce listů i přístup k buňkám jsou indexované od jedné, což je nesrovnalost, jež předchází současné API a je zachována kvůli kompatibilitě. Zapomenutá úprava projde auditem špatný obdélník a přitom nenahlásí nic neobvyklého. Levné předběžné kontroly, které se vyhnou načtení celého souboru, popisuje článek lehká inspekce sešitu

Co nedostanete, a co s tím dělat

Formátování se neinterpretuje. HotXLS neparsuje záznamy XF a FONT těchto generací, takže fonty, barvy, ohraničení a formáty čísel nejsou dostupné, a buňky, které Excel kdysi zobrazoval jako data, se vrátí jako svá syrová pořadová čísla

Tento poslední bod je třeba ošetřit ve vlastním kódu, ne v čtečce, a důvod je upřímný: formáty čísel v BIFF2 až BIFF4 nejsou dost spolehlivé na to, aby řídily automatické rozhodnutí o datu. Sloupec pětimístných čísel může být data, nebo může být čísla dílů. Převádějte záměrně, s použitím systému data sešitu, jehož pravidla popisuje článek pořadová čísla dat, systém 1904 a formáty čísel:

// Rozhodujte podle sloupce, nikdy podle jednotlivé hodnoty: pětimístné
// číslo může být datum nebo číslo dílu a starý formát vám to neřekne
if ColumnHoldsDates(C) then
begin
  // Dva systémy data jsou od sebe vzdáleny 1462 dní, takže stejné
  // pořadové číslo označuje dvě data vzdálená čtyři roky. Systém
  // čtěte ze sešitu, nepředpokládejte jej
  if Book.Date1904 then
    Writeln(DateToStr(SerialToDate1904(V)))
  else
    Writeln(DateToStr(SerialToDate1900(V)));
end
else
  Writeln(VarToStr(V));

Dvě strukturální poznámky dokreslují obraz. Záznamy ochrany heslem a znakové stránky se objevují uvnitř jediného proudu listu místo v proudu na úrovni sešitu, protože žádný proud na úrovni sešitu neexistuje, kam by se daly umístit, takže je třeba je rozpoznat v kontextu listu. A soubor BIFF2 až BIFF4 obsahuje přesně jeden podproud listu; sešity s více listy neexistovaly, dokud formát nezískal svůj kontejner

Pragmatická migrační cesta je tedy dvoukroková: přečíst starý soubor kvůli jeho hodnotám, poté zapsat moderní sešit, který tyto hodnoty ponese s formátováním, jež si sami aplikujete. Čtení starších formátů, moderní zápis a vše mezi tím běží v jedné knihovně pro Delphi a C++Builder, popsané na stránce komponenty HotXLS pro tabulky v Delphi