Extrahování souhrnných informací o dokumentu ze souborů Excel v Delphi

Při žádosti po pipelině, aby roztřídila deset tisíc tabulek podle autora, společnosti nebo data poslední úpravy, je to nejhorší, co může udělat, plně otevřít každý sešit (workbook). Odpovědi se skrývají ve vlastnostech dokumentu samotného souboru, které svět Office nazývá Document Summary Information (Souhrnné informace o dokumentu): vrstva metadat, kterou Windows Search indexuje, podle které SharePoint třídí a kterou Excel zobrazuje v dialogu Vlastnosti. Tato vrstva má nanejvýš několik kilobajtů a nachází se v obou formátech Excelu na dobře zdokumentovaném místě. Trik spočívá v tom, jak se k ní dostat z Delphi bez toho, abyste museli platit daň za milion buněk, které vůbec nepotřebujete

Existují tři reálné cesty a neliší se ani tak v tom, co vracejí, jako spíše v tom, co vyžadují od stroje, na kterém běží. COM automatizace řídí samotný Excel a čte vše – za cenu výkonu na úrovni desktopu. Formát .xls uchovává své vlastnosti v tocích (streams) OLE property-set, které za vás zpracuje operační systém Windows. Formát .xlsx je má uloženy ve dvou malých částech XML uvnitř archivu zip, které Delphi RTL umí otevřít samo. Funkční kód pro každou cestu s jasným vyčíslením nákladů následuje

Cesta 1: COM automatizace přečte vše, za desktopovou cenu

Automatizace je jedinou cestou s naprostým pokrytím prostřednictvím jednoho objektového modelu: standardní sada souhrnů, rozšířená sada s vlastnostmi Společnost (Company) a Manažer (Manager) a uživatelsky definované vlastní vlastnosti. Vše je dostupné přes BuiltinDocumentProperties a CustomDocumentProperties. Všechno je vraceno jako OleVariant a toto API má jeden zvyk, který stojí za to znát předtím, než vás „kousne“: vestavěná vlastnost, která nebyla nikdy přiřazena, se nevrátí jako prázdná, nýbrž vyvolá výjimku EOleException v okamžiku, kdy se dotknete hodnoty (Value). Níže uvedený pomocník s tím nakládá jako se stavem „nenastaveno“ (not set) spíše než jako s chybou

uses
  System.SysUtils, System.Variants, System.Win.ComObj;

procedure ReadPropertiesViaCom(const FileName: string);
var
  Excel, Book, Builtin, Custom: OleVariant;
  I: Integer;

  function BuiltinProp(const Name: string): string;
  begin
    try
      Result := VarToStr(Builtin.Item(Name).Value);
    except
      on EOleError do
        Result := '';   // property exists but was never assigned
    end;
  end;

begin
  Excel := CreateOleObject('Excel.Application');
  try
    Excel.DisplayAlerts := False;
    Book := Excel.Workbooks.Open(FileName, 0, True);   // read-only
    try
      Builtin := Book.BuiltinDocumentProperties;
      Writeln('Author : ', BuiltinProp('Author'));
      Writeln('Title  : ', BuiltinProp('Title'));
      Writeln('Subject: ', BuiltinProp('Subject'));
      Writeln('Company: ', BuiltinProp('Company'));
      Writeln('Manager: ', BuiltinProp('Manager'));

      Custom := Book.CustomDocumentProperties;
      for I := 1 to Custom.Count do
        Writeln(VarToStr(Custom.Item(I).Name), ' = ',
          VarToStr(Custom.Item(I).Value));
    finally
      Book.Close(False);
    end;
  finally
    Excel.Quit;   // reach this on every path, or EXCEL.EXE stays behind
    Excel := Unassigned;
  end;
end;

A teď účet. Excel musí být nainstalován na každém stroji, kde tento kód poběží, což samo o sobě vylučuje většinu serverů. Navíc podpora Microsoftu jasně říká, že Office není navržen ani licencován pro bezobslužnou serverovou automatizaci. CreateOleObject spouští kompletní proces EXCEL.EXE a Workbooks.Open parsuje celý sešit, takže očekávejte zhruba dvě až čtyři sekundy na soubor, než se vrátí první vlastnost. Konstrukce try..finally okolo příkazu Quit zde není jen na ozdobu: výjimka, která by unikla mezi CreateOleObject a Quit, za sebou zanechá osiřelý proces EXCEL.EXE držící zámek na souboru, což zůstane neviditelné, dokud na tom neselže další pokus. Opětovné použití jedné instance Excelu v rámci celé dávky (batch) sice amortizuje počáteční náklady na spuštění, ale koncentruje riziko, protože jeden zbloudilý dialog na skrytém desktopu zastaví každý soubor ve frontě za ním

Cesta 2: .xls ukládá vlastnosti v tocích OLE property-set

Sešit ve formátu BIFF8 je v podstatě strukturovaný (compound) soubor OLE, miniaturní souborový systém úložišť (storages) a toků (streams). Samotná data buněk se nacházejí v toku Workbook; metadata žijí vedle nich ve dvou tocích pro sady vlastností (property-set streams), jejichž jména začínají řídicím znakem #5: \005SummaryInformation pro klasická pole a \005DocumentSummaryInformation pro rozšířená a vlastní pole. Uvnitř každého z nich leží binární sada vlastností ve formátu MS-OLEPS, s oddíly (sections) klíčovanými identifikátorem formátu (FMTID) a vlastnostmi klíčovanými celočíselným ID. Sekce Summary má FMTID {F29F85E0-4FF9-1068-AB91-08002B27B3D9}, kde PIDSI_TITLE je $02 a PIDSI_AUTHOR je $04; Company ($0F) a Manager ($0E) žijí v sekci document-summary a vlastní vlastnosti se nacházejí v druhé sekci za slovníkem názvů

Dobrou zprávou je, že ve Windows tyto byty nikdy sami neparsujete. Strukturované úložiště (structured storage) vystavuje toky prostřednictvím rozhraní IPropertySetStorage a následující kód se zkompiluje přesně tak, jak je zobrazen, vůči standardním jednotkám RTL

uses
  System.SysUtils, Winapi.Windows, Winapi.ActiveX, System.Win.ComObj;

const
  FMTID_SummaryInfo: TGUID = '{F29F85E0-4FF9-1068-AB91-08002B27B3D9}';
  PIDSI_TITLE    = $02;
  PIDSI_AUTHOR   = $04;
  STGFMT_STORAGE = 0;

function ReadXlsSummaryString(const FileName: string; PropId: TPropID): string;
var
  Unk: IUnknown;
  Stg: IStorage;
  PropSetStg: IPropertySetStorage;
  PropStg: IPropertyStorage;
  Spec: TPropSpec;
  Value: TPropVariant;
begin
  Result := '';
  OleCheck(StgOpenStorageEx(PWideChar(FileName),
    STGM_READ or STGM_SHARE_DENY_WRITE, STGFMT_STORAGE, 0, nil, nil,
    @IID_IStorage, Unk));
  Stg := Unk as IStorage;
  PropSetStg := Stg as IPropertySetStorage;
  OleCheck(PropSetStg.Open(FMTID_SummaryInfo,
    STGM_READ or STGM_SHARE_EXCLUSIVE, PropStg));
  Spec.ulKind := PRSPEC_PROPID;
  Spec.propid := PropId;
  if PropStg.ReadMultiple(1, @Spec, @Value) = S_OK then  // S_FALSE: not present
  try
    case Value.vt of
      VT_LPSTR:  Result := string(AnsiString(Value.pszVal));
      VT_LPWSTR: Result := Value.pwszVal;
    end;
  finally
    PropVariantClear(Value);
  end;
end;

// usage: Writeln('Author: ', ReadXlsSummaryString('ledger.xls', PIDSI_AUTHOR));

A teď upřímně o tom, co tento snippet skrývá. Řetězce mohou dorazit jako VT_LPWSTR nebo VT_LPSTR. U ANSI varianty jsou byty kódovány podle kódové stránky (code page) samotné sady vlastností, která je uložena jako vlastnost 1 příslušné sekce. Výše uvedené přetypování je tedy naprosto přesné pouze tehdy, pokud se tato kódová stránka shoduje se systémovou. Časová razítka (timestamps) se vracejí jako VT_FILETIME v čase UTC. Vlastní properties znamenají nutnost otevřít uživatelem definovanou sekci s FMTID {D5CDD505-2E9C-101B-9397-08002B2CF9AE} a projít její jmenný slovník. IPropertyStorage absorbuje na Windows všechny tyto detaily; psaní vlastního parseru MS-OLEPS pro prostředí bez strukturovaného úložiště je ovšem projekt sám o sobě, nikoliv práce na jedno odpoledne

Cesta 3: .xlsx drží docProps jako XML uvnitř archivu zip

Toto je cesta, kterou většina pipelin ve skutečnosti potřebuje, protože nové soubory jsou formátu .xlsx již téměř dvě desetiletí. Sešit OOXML je archivační (zip) balíček a jeho vlastnosti jsou rozděleny do malých částí podle účelu: docProps/core.xml obsahuje pole typu Dublin Core, dc:title, dc:creator, cp:lastModifiedBy, plus dcterms:created a dcterms:modified jako časová razítka W3CDTF v čase UTC. Část docProps/app.xml obsahuje pole na úrovni aplikace jako jsou Company (Společnost) a AppVersion (Verze aplikace), a docProps/custom.xml obsahuje uživatelsky definované vlastní vlastnosti. Vzhledem k tomu, že centrální adresář archivu zip přesně identifikuje polohu každé z těchto částí, přečíst je stojí jen pár kilobajtů paměti a I/O, bez ohledu na velikost celého sešitu. Třída TZipFile a rozhraní IXMLDocument, které jsou obě součástí dodávané knihovny RTL, zvládnou celou práci

uses
  System.SysUtils, System.Classes, System.Zip, Xml.XMLDoc, Xml.XMLIntf;

const
  NsDC    = 'http://purl.org/dc/elements/1.1/';
  NsTerms = 'http://purl.org/dc/terms/';
  NsCore  = 'http://schemas.openxmlformats.org/package/2006/metadata/core-properties';
  NsApp   = 'http://schemas.openxmlformats.org/officeDocument/2006/extended-properties';

function PartToXml(Zip: TZipFile; const PartName: string): IXMLDocument;
var
  Bytes: TBytes;
begin
  Zip.Read(PartName, Bytes);
  Result := LoadXMLData(TEncoding.UTF8.GetString(Bytes));
end;

function Field(const Doc: IXMLDocument; const LocalName, Ns: string): string;
var
  Node: IXMLNode;
begin
  Node := Doc.DocumentElement.ChildNodes.FindNode(LocalName, Ns);
  if Node <> nil then
    Result := Node.Text
  else
    Result := '';
end;

procedure ReadXlsxProperties(const FileName: string);
var
  Zip: TZipFile;
  Doc: IXMLDocument;
begin
  Zip := TZipFile.Create;
  try
    Zip.Open(FileName, zmRead);
    if Zip.IndexOf('docProps/core.xml') >= 0 then
    begin
      Doc := PartToXml(Zip, 'docProps/core.xml');
      Writeln('Title   : ', Field(Doc, 'title', NsDC));
      Writeln('Creator : ', Field(Doc, 'creator', NsDC));
      Writeln('Modifier: ', Field(Doc, 'lastModifiedBy', NsCore));
      Writeln('Modified: ', Field(Doc, 'modified', NsTerms));  // W3CDTF, UTC
    end;
    if Zip.IndexOf('docProps/app.xml') >= 0 then
    begin
      Doc := PartToXml(Zip, 'docProps/app.xml');
      Writeln('Company : ', Field(Doc, 'Company', NsApp));
      Writeln('App     : ', Field(Doc, 'Application', NsApp), ' ',
        Field(Doc, 'AppVersion', NsApp));
    end;
  finally
    Zip.Free;
  end;
end;

Dva detaily udržují tento kód v produkčním prostředí dostatečně robustním. Za prvé, části jsou volitelné: minimální balíček zcela bez docProps je podle standardu ECMA-376 naprosto platný. To je důvod, proč kód pomocí IndexOf existenci nejprve ověřuje a nic nepředpokládá. Za druhé, hledejte elementy podle místního jména (local name) a URI jmenného prostoru (namespace URI), přesně jak to výše dělá metoda FindNode, nikdy nevyhledávejte podle fixního literálu prefixu; dc: a cp: jsou pouhé konvence writeru v Excelu a soubory vytvořené jinými generátory si mohou libovolně zvolit prefixy zcela jiné. Jedna poznámka k běhovému prostředí (environment): výchozím vendorem (vendor) pro IXMLDocument je MSXML. Konzolová aplikace nebo pracovní vlákno proto musí zavolat CoInitialize před spuštěním LoadXMLData, jinak první pokus o parsování zhavaruje na chybě COM

Ceník nákladů a kdy knihovna překoná oba parsery

Při měření na běžném vývojářském počítači se cesta s COM dostane na úroveň zhruba dvou až čtyř sekund na soubor, pokud je relace automatizace vytvořena na každý jednotlivý soubor (téměř celou dobu zabírá spuštění procesu EXCEL.EXE plus kompletní parsování sešitu). Navíc vyžaduje nainstalovaný a licencovaný Excel, ať už běží kdekoli. Dvě přímé cesty čtou pouze kontejnery s metadaty, skončí v řádu jednotek milisekund na soubor a nepotřebují vůbec nic instalovat nad rámec toho, co již obsahuje sestavený spustitelný soubor z Delphi. Pokud máte na disku podíl deseti tisíc souborů, je to rozdíl mezi téměř celým pracovním dnem a zhruba méně než jednou minutou, a přitom nemusíte řešit nasazení balíku Office

Pravý háček s přímými cestami spočívá v tom, že jsou nakonec dvě. Datová pipeline, která akceptuje oba formáty, musí udržovat dva nezávislé parsery s dvěma zcela odlišnými režimy možných selhání: kódové stránky a typy PROPVARIANT na jedné straně a jmenné prostory spolu s volitelnými XML částmi na straně druhé. Ani jeden z nich nedokáže přečíst formát toho druhého. Tato dodatečná zátěž na údržbu přesně odpovídá případu pro nativní knihovnu: HotXLS, tabulková knihovna v Object Pascalu od losLab pro Delphi a C++Builder ve Windows, vystavuje stejná pole jako běžné vlastnosti (properties) sešitu, jako Title, Author, Company, Created a další. Knihovna je vyplňuje během otevírání pomocí metody Open, a to u souborů .xls i .xlsx naprosto stejným způsobem, a to zcela bez nutnosti instalovat Excel a bez veškerého výše popsaného instalatérství obou kontejnerů. Čte vlastnosti v rámci celkového otevírání sešitu spíše než jen v režimu "pouze zkoumání metadat", což skvěle zapadá do datových pipelin, které beztak budou s daty uvnitř buněk následně manipulovat. Plná podpora vlastností na obou vrstvách, včetně jejich zápisu (write), je popsána v našem článku o nastavování vlastností dokumentu Excel s HotXLS

Poznámka: Plnohodnotné nástroje pro parsování Excelu a extrakci metadat jsou dostupné v komponentě HotXLS VCL Component