LlosLab Software Development BlogTechnické poznámky pro vývojáře PDF, Delphi a dokumentového softwaru.

Technický článek

Čtení a zápis binárních sešitů XLSB v Delphi

HotXLS Delphi Component

HotXLS čte a zapisuje soubory Excel Binary Workbook (XLSB) z Delphi přes stejnou fasádu TXLSXWorkbook, jaká slouží pro XLSX: Open rozpozná binární sešit z obsahu balíčku a SaveAs nebo SaveAsChecked s xlsxExcelBinaryWorkbook ho zapíše. Backend pokrývá validovanou podmnožinu a odmítá cokoli, co nedokáže reprezentovat, ještě než sáhne na cíl

Tu poslední větu čtěte pomalu, ta je ta podstatná. XLSB vypadá jako prostá variace XLSX a v obalu je to tak. Problém bydlí v detailech, které oba formáty kódují jinak, a v tom, co si knihovna potichu domyslí, když jeden přemostí do druhého. Nejvýživnější chyba v tomhle backendu nebyla vůbec deformovaný záznam. Byl to výchozí font, který zmizel, a sloupec dat, který se v Excelu proměnil v ####, ačkoli hodnota, formátový kód i šířka sloupce byly úplně správné

Co je soubor XLSB a čím se liší od XLSX?

Soubor XLSB je OPC balíček s přesně tou strukturou částí, jakou má sešit XLSX, jen části sešitu, listu, stylů a sdílených řetězců ukládají binární záznamy místo XML. Formát specifikuje [MS-XLSB] a záznamovému kódování se často říká BIFF12. Stále dostáváte [Content_Types].xml, relační části a ZIP kontejner; mění se, že xl/workbook.bin nahrazuje xl/workbook.xml, xl/worksheets/sheet1.bin nahrazuje XML listu a tak dále

Každý záznam BIFF12 začíná kompaktní hlavičkou proměnné délky. Typ záznamu zabírá jeden nebo dva bajty, sedm bitů na bajt s vysokým bitem jako pokračovacím příznakem, takže typ záznamu se vejde do 14 bitů. Velikost payloadu následuje v jednom až čtyřech bajtech ve stejném sedmibitovém schématu. Řetězce jsou 32bitový počet znaků následovaný kódovými jednotkami UTF-16. Oproti BIFF8 je to výrazně čistší uspořádání: žádné dělení na 8224bajtové CONTINUE, žádné příznaky komprimovaného proti širokému řetězci uprostřed záznamu. Oproti XLSX vynechává tokenizaci XML úplně, proto bývají velké soubory XLSB menší na disku a Excel je otevírá rychleji

Jeden obalový detail shodí detekční kód. Excel registruje část sešitu XLSB s obsahovým typem application/vnd.ms-excel.sheet.binary.macroEnabled.main, i pro sešity, které neobsahují žádná makra. HotXLS tu registraci kontroluje v [Content_Types].xml jako první a k příponě části .bin spadne jen tehdy, když obsahový typ chybí, a stejně obě cesty musí projít přísným validátorem záznamů. Obecná pravidla o názvech částí, relacích a obsahových typech jsou tatáž, která rozebírá proč Excel opravuje platný XLSX a pravidla OPC balíčku za tím

Kdy zvolit XLSB místo XLSX?

Volte XLSB, když to vyžaduje navazující systém, nebo když si s uživateli Excelu vyměňujete velké sešity plné hodnot, kterým záleží na době otevření; jinak zůstává bezpečnější výchozí volbou XLSX. XLSX je norma ISO (ISO/IEC 29500) s širokou podporou nástrojů, dá se prohlížet ZIP nástrojem a textovým editorem a čte ho každý tabulkový produkt. XLSB je formát Microsoftu s publikovanou specifikací, ale užším ekosystémem

V praxi žádosti přicházejí ze dvou stran. Některé reportingové pipeline a archivy se před lety standardizovaly na .xlsb a odmítají cokoliv jiného. Jiné týmy generují sešity se stovkami tisíc číselných buněk a zjistily, že uživatelé otevírají binární verzi nápadně rychleji. Obojí jsou legitimní důvody. „Zní to efektivněji“ není, protože binární backend HotXLS připravuje kompletní balíček v paměti, takže to není nástroj na serverové exporty s ohraničenou pamětí. Pro ty je správnou cestou streamovací zapisovač XLSX

Otevírání a ukládání XLSB přes TXLSXWorkbook

TXLSXWorkbook.Open nepotřebuje nápovědu formátu: táž volání načte XLSX, XLSM i XLSB a po úspěšném binárním načtení hlásí TXLSXWorkbook.SourceFormat xlsxExcelBinaryWorkbook. Zapisování je explicitní. SaveAs(FileName) bez formátu vždycky vyprodukuje sešit Open XML, i když název souboru končí .xlsb, takže binární výstup vždy jmenuje formát

uses
  SysUtils, lxHandleX;

procedure ConvertToXlsb(const Source, Target: string);
var
  Book: TXLSXWorkbook;
begin
  Book := TXLSXWorkbook.Create;
  try
    // Open rozpozná XLSX, XLSM nebo XLSB ze samotného balíčku
    if Book.Open(Source) <> 1 then
      raise Exception.Create('Input is unsupported or invalid');

    // Samotná přípona nikdy nevybere binární výstup
    if Book.SaveAsChecked(Target, xlsxExcelBinaryWorkbook) <> 1 then
      raise Exception.Create('XLSB output is unsupported or failed');
  finally
    Book.Free;
  end;
end;

Výstup do streamu jde přes SaveAs(Stream, xlsxExcelBinaryWorkbook) nebo streamovou přetíženou verzi SaveAsChecked. Interně obě směrují do privátních metod SaveAsXlsbStream a SaveAsXlsbFile; ty nevoláte nikdy přímo. Výstup do souboru se připraví do vedlejšího dočasného souboru a potvrdí se jednou finální výměnou. Cíl streamu musí být čitelný a seekovatelný, protože zapisovač si pořídí snapshot svých bajtů, velikosti a pozice a obnoví je, když finální kopie selže. TXLSXWorkbook.LastSaveUsedStreaming hlásí po binárním uložení False, což je přesné: celý balíček existuje v paměti dřív, než jediný bajt dorazí na váš cíl

Proč validovaná podmnožina místo plné věrnosti?

HotXLS si neudržuje druhý objektový model pro XLSB. Při otevření převede XlsbToOpenXml binární balíček na Open XML balíček v paměti a obyčejný XML parser ho načte; při uložení postaví XML zapisovač balíček a OpenXmlToXlsb ho zakóduje do binárních záznamů. Každá funkce tedy musí přežít dva překlady a funkci, kterou most nedokáže přeložit přesně, čeká odmítnutí místo aproximace

Podporovaná sada k HotXLS 2.384.113 je pro většinu datových sešitů široká: typované hodnoty, sdílené i inline řetězce, běžné fonty, výplně, rámečky a číselné formáty, výšky řádků a šířky sloupců, obyčejné buněčné vzorce s cacheovanými výsledky, definované názvy, spojené buňky, hyperlinky, ukotvené a dělené panely, rozsahy AutoFilter s jednoduchými vlastními kritérii a obyčejné tabulky listu. Mimo ni sedí sdílené a maticové vzorce, budoucí a uživatelsky definované funkce, odkazy na externí sešity, grafy a kresby, PivotTables a slicery, VBA, podmíněné formátování, validace dat a nenormální výpočetní nastavení jako ruční výpočet nebo iterace

Odmítnutí je povinné, ne poradce. TXLSXWorkbook.GetConversionReport(xlsConversionExcelBinaryWorkbook) vypíše nepodporované funkce dřív, než uložíte, a ty položky uložení zhatí i s AAllowLossy = True nebo nekontrolovaným SaveAs. To je záměr. Potichu vypustit PivotTable z binární kopie je ten druh ztráty, které si nikdo nevšimne, dokud sešit nestojí před klientem

uses
  Classes, lxHandleX, lxDiagnostics;

function CanSaveAsXlsb(Book: TXLSXWorkbook; Log: TStrings): Boolean;
var
  Report: TXLSConversionReport;
  I: Integer;
begin
  Report := Book.GetConversionReport(xlsConversionExcelBinaryWorkbook);
  try
    Result := True;
    for I := 0 to Report.Count - 1 do
      if Report[I].Fidelity = xlsConversionUnsupported then
      begin
        Result := False;
        Log.Add(Report[I].SheetName + ' ' + Report[I].Location + ': ' +
          Report[I].Feature + ' - ' + Report[I].Message);
      end;
  finally
    Report.Free;   // report vlastní volající
  end;
end;

Report pokrývá živý model. Finální validace balíčku zůstává poslední branou pro věci, které model nikdy nevidí, jako deformované záznamy nebo neznámé atributy ve zdrojovém souboru, takže čistý report následovaný neúspěšným uložením je možný a podle výsledku uložení se řídíte

Proč zmizely výchozí font a formát 0.00?

Výchozí font i vestavěné číselné formáty zmizely, protože čtečka XML stylů dělala dva předpoklady, které drží u souborů, jež si HotXLS zapisuje samo, ale ne u sešitů od Excelu. Most zachoval oba zdroje dokonale; ztráta se stala o vrstvu výš, ve veřejné cestě Open. Ta mezera je hlavní lekce tohohle backendu: správný formátový most negarantuje věrnou veřejnou cestu tam a zpátky

První předpoklad se týkal fonts[0]. Když HotXLS ukládá XLSX, zapisuje implicitní výchozí font do slotu nula a jeho XML čtečka ten slot přeskočí, takže kolekce Fonts viděná uživatelem začíná na indexu nula. XLSB od Excelu nese v tom slotu vlastní explicitní výchozí a jeho přeskočení ten výběr vyhodilo. Druhý předpoklad se týkal číselných formátů. Čtečka mapovala do NumberFormats jen vlastní hodnoty numFmtId (164 a výš); vestavěná id zůstávala nula. Naformátovaná prázdná buňka nesoucí vestavěný formát 2, který je 0.00, se vrátila jako General

Oprava bydlí úplně v cestě binárního importu. Jakmile plný balíček projde preflightem, nastaví otevření XLSB privátní stylový mód jen pro synchronní XML parse. V tom módu čtečka načte každý font včetně slotu nula a posune každé fontId o jedničku, převede každé podporované vestavěné id na explicitní formátový kód v modelu a pro detekci dat pořád používá původní numFmtId, takže vestavěný datový formát si drží svou datovou sémantiku. Blok finally mód vždycky vymaže a pozdější obyčejné Open pro XLSX nebo ODS vymaže i udržovaný stav výchozího fontu

Jak fonts[0] ovlivňuje šířky sloupců v Excelu?

Excel počítá šířky sloupců z výchozího fontu sešitu v fonts[0], ne z fontů aplikovaných na buňky v tom sloupci. Šířka sloupce width se ukládá v znakových jednotkách maximální šířky číslice výchozího fontu, takže změna fonts[0] změní pixelovou šířku každého sloupce, včetně sloupců, jejichž buňky všechny používají jiný font

To vyprodukovalo nejméně srozumitelný příznak celého vyšetřování. Znovu uložený sešit měl identické hodnoty buněk, identické formátové kódy i identické atributy width, buněčný font i styl Normal ukazovaly na zachovaný font a Excel pořád renderoval sloupec dat jako ####. Zapsané fonts[0] se vrátilo na Calibri 11, maximální šířka číslice se zmenšila a data se už nevešla. HotXLS teď drží výchozí font zdroje a zapisuje ho zpátky jako fonts[0], s pádem na Calibri 11 jen u sešitů, které nikdy neměly binární zdroj. Celou aritmetiku šířek rozebírá šířka sloupce Excel a max digit width (MDW) v Delphi

Formáty závislé na locale se odmítají, nehádají se

Vestavěné id číselného formátu bez explicitního formátového kódu se přijímá jen tehdy, když je jeho význam pevný; id závislá na locale se odmítají při preflightu balíčku. HotXLS mapuje id 0 až 22, 37 až 40 a 45 až 49 na známé kódy jako 0.00, 0%, mm:ss a @. Zbylá id pod 164 patří formátům, jejichž vykreslení závisí na regionálním nastavení instalace Excelu, a import je odmítá se zprávou „A locale-dependent number format requires an explicit format code“

Vymyslet pro ta id kód by bylo horší než selhat. Uhádnutý východoasijský datový vzorek vypadá věrohodně, projde cestou tam a zpátky bez chyby a ukazuje nesmysl polovině vašich uživatelů. Na čase taky záleží: kontrola běží v preflightu celého balíčku, ještě než se stávající sešit vymaže, takže odmítnutý soubor nechá dřív načtený model, název souboru i zdrojový formát netknuté. Jak vestavěná datová id interagují s datovými systémy 1900 a 1904, rozebírá článek o datových sériích Excel a numFmt

procedure LoadBinaryWorkbook(Book: TXLSXWorkbook; const FileName: string);
var
  I: Integer;
begin
  // Binární vstup potřebuje dychtivé plné načtení; selektivní módy odmítají rovnou
  Book.MetadataOnly := False;
  Book.SelectedSheets.Clear;

  if Book.Open(FileName) <> 1 then
  begin
    for I := 0 to Book.Diagnostics.Count - 1 do
      Writeln(Book.Diagnostics[I].Message);
    Exit;   // dřív načtený sešit je stále v celku
  end;

  // Vzorcový výstup potřebuje cache, pokud uložení nepřepočítává
  Book.RecalcBeforeSave := xrmAlways;
end;

Praktická pravidla pro XLSB v produkčním kódu

Na binární backend se dá spolehnout, když jeho odmítnutí berete jako data, ne jako chyby k potlačení. Z chování výše plyne pár pravidel

  • Pojmenujte formát u každého binárního uložení; samotná přípona .xlsb stále produkuje XLSX
  • Spouštějte GetConversionReport(xlsConversionExcelBinaryWorkbook) před uložením uživatelem upravených sešitů a nepodporované funkce vystavte uživateli
  • Nechávejte MetadataOnly vypnuté a SelectedSheets prázdné pro binární vstup, protože líné načítání by vyžadovalo držet převedený archiv
  • Dejte vzorcům cacheovaný výsledek nebo přepočítejte před uložením, protože chybějící cache odmítne pod xrmManual i pod čistým xrmIfDirty
  • Počítejte s pamětí pro kompletní převedený balíček plus binární výstup; paměť-li musí zůstat ohraničená, použijte streamovací XLSX

Při testování srovnávejte s Excelem přes veřejné API, ne na úrovni mostu. Regrese výchozího fontu i 0.00 obě prošly testy mostu a obě se okamžitě ukázaly v sešitu od Excelu otevřeném a znovu uloženém přes Open a SaveAsChecked

FAQ

Rozpozná HotXLS soubory XLSB automaticky?

Ano. TXLSXWorkbook.Open prozkoumá obsahový typ zaregistrovaný pro část sešitu a zvaliduje binární záznamy, takže táž volání načte XLSX i XLSB a SourceFormat hlásí, který se četl

Proč SaveAs s názvem .xlsb pořád zapisuje XLSX?

HotXLS nikdy neodvozuje výstupní formát z přípony. Předejte xlsxExcelBinaryWorkbook do SaveAs nebo SaveAsChecked, chcete-li zapsat binární sešit

Co se stane, když sešit používá funkce mimo podmnožinu XLSB?

Uložení selže, než se cíl změní, i když je ztrátové ukládání povolené. GetConversionReport vypíše blokující funkce předem a Diagnostics vysvětlí selhání potom

Podpora XLSB, konverzní report i zbytek API sešitu dodává HotXLS, nativní tabulková komponenta pro Delphi a C++Builder