Čítanie vlastností dokumentu Excel v Delphi: tri cesty

Zadajte pipeline, aby smerovala desaťtisíc tabuliek podľa autora, spoločnosti alebo dátumu poslednej zmeny, a najhoršie, čo môže urobiť, je otvoriť každý zošit celý. Odpovede cestujú vo vlastnostiach dokumentu, tomu, čo svet Office nazýva Document Summary Information: vrstva metadát, ktorú indexuje Windows Search, podľa ktorej triedi SharePoint a ktorú Excel zobrazuje v dialógu vlastností. Táto vrstva má nanajvýš kilobajty a v oboch formátoch Excelu sídli na dobre zdokumentovanom mieste. Trik je dostať sa k nej z Delphi bez toho, aby ste platili za milión buniek, ktoré nepotrebujete

Existujú tri skutočné cesty a nelíšia sa ani tak v tom, čo vracajú, ako v tom, čo vyžadujú od stroja, na ktorom bežia. Automatizácia COM riadi samotný Excel a prečíta všetko, za desktopovú cenu. Formát .xls drží svoje vlastnosti v OLE property-set streamoch, ktoré za vás rozparsuje Windows. Formát .xlsx ich drží v dvoch malých XML častiach vnútri zipu, ktorý si RTL v Delphi otvorí sám. Funkčný kód pre každú z nich nasleduje, s otvorene pomenovanými nákladmi

Cesta 1: automatizácia COM prečíta všetko, za desktopovú cenu

Automatizácia je jedinou cestou s úplným pokrytím cez jediný objektový model: štandardná sada súhrnných polí, rozšírená sada s poľami Company a Manager aj používateľom definované vlastné vlastnosti, všetko dostupné cez BuiltinDocumentProperties a CustomDocumentProperties. Všetko prichádza ako OleVariant a toto API má jeden zvyk, ktorý je dobré poznať skôr, než vás poštípe: zabudovaná vlastnosť, ktorá nikdy nebola priradená, sa nevráti prázdna, ale vyhodí EOleException v okamihu, keď siahnete na Value. Pomocná funkcia nižšie to považuje za „nenastavené“, nie za zlyhanie

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 := '';   // vlastnosť existuje, ale nikdy nebola priradená
    end;
  end;

begin
  Excel := CreateOleObject('Excel.Application');
  try
    Excel.DisplayAlerts := False;
    Book := Excel.Workbooks.Open(FileName, 0, True);   // len na čítanie
    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;   // sem sa dostaňte pri každej ceste, inak EXCEL.EXE zostane bežať
    Excel := Unassigned;
  end;
end;

A teraz účet. Excel musí byť nainštalovaný na každom stroji, kde tento kód beží, čo samo osebe vylučuje väčšinu serverov, a podporná politika Microsoftu výslovne hovorí, že Office nie je navrhnutý ani licencovaný na bezobslužnú automatizáciu na strane servera. CreateOleObject spustí plnohodnotný EXCEL.EXE a Workbooks.Open rozparsuje celý zošit, takže než sa vráti prvá vlastnosť, rátajte zhruba s dvoma až štyrmi sekundami na súbor. A try..finally okolo Quit nie je ozdoba: výnimka, ktorá unikne medzi CreateOleObject a Quit, zanechá osirelý EXCEL.EXE držiaci zámok na súbore, neviditeľný až do chvíle, keď na ňom zlyhá ďalší beh. Opätovné použitie jednej inštancie Excelu naprieč dávkou rozloží náklady na štart, ale sústredí riziko, pretože jediný zblúdilý dialóg na skrytej ploche zastaví každý súbor, ktorý stojí v rade za ním

Cesta 2: .xls ukladá vlastnosti v OLE property-set streamoch

Zošit BIFF8 je zložený súbor OLE, miniatúrny súborový systém úložísk a streamov. Dáta buniek sídlia v streame Workbook; metadáta ležia vedľa nich v dvoch property-set streamoch, ktorých názvy sa začínajú riadiacim znakom #5: \005SummaryInformation pre klasické polia a \005DocumentSummaryInformation pre rozšírené a vlastné. Vnútri každého sedí binárna množina vlastností v rozložení MS-OLEPS so sekciami kľúčovanými identifikátorom formátu (FMTID) a vlastnosťami kľúčovanými celočíselným ID vlastnosti. Súhrnná sekcia má FMTID {F29F85E0-4FF9-1068-AB91-08002B27B3D9}, kde PIDSI_TITLE je $02 a PIDSI_AUTHOR je $04; Company ($0F) a Manager ($0E) sídlia v sekcii document-summary a vlastné vlastnosti v druhej sekcii za slovníkom názvov

Dobrou správou je, že na Windows tie bajty nikdy neparsujete sami. Štruktúrované úložisko sprístupňuje tieto streamy cez IPropertySetStorage a nasledujúci kód sa preloží tak, ako je uvedený, oproti štandardným RTL jednotkám

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: nie je prítomná
  try
    case Value.vt of
      VT_LPSTR:  Result := string(AnsiString(Value.pszVal));
      VT_LPWSTR: Result := Value.pwszVal;
    end;
  finally
    PropVariantClear(Value);
  end;
end;

// použitie: Writeln('Author: ', ReadXlsSummaryString('ledger.xls', PIDSI_AUTHOR));

Úprimné slovo o tom, čo táto ukážka skrýva. Reťazce môžu prísť ako VT_LPWSTR alebo ako VT_LPSTR a v prípade ANSI sú bajty zakódované v kódovej stránke samotnej množiny vlastností, ktorá je uložená ako vlastnosť 1 danej sekcie, takže pretypovanie vyššie je presné len vtedy, keď sa táto kódová stránka zhoduje so systémovou. Časové značky sa vracajú ako VT_FILETIME v UTC. Vlastné vlastnosti znamenajú otvoriť používateľom definovanú sekciu s FMTID {D5CDD505-2E9C-101B-9397-08002B2CF9AE} a prejsť jej slovník názvov. IPropertyStorage to všetko na Windows pohltí; napísať si vlastný parser MS-OLEPS pre prostredie bez štruktúrovaného úložiska je skutočný projekt, nie práca na jedno popoludnie

Cesta 3: .xlsx drží docProps ako XML vnútri zipu

Toto je cesta, ktorú väčšina pipeline naozaj potrebuje, keďže nové súbory sú už takmer dve desaťročia .xlsx. Zošit OOXML je zipový balík a jeho vlastnosti sú rozdelené do malých častí podľa účelu: docProps/core.xml drží polia Dublin Core, dc:title, dc:creator, cp:lastModifiedBy plus dcterms:created a dcterms:modified ako časové značky W3CDTF v UTC, kým docProps/app.xml drží polia na úrovni aplikácie, napríklad Company a AppVersion, a docProps/custom.xml drží vlastné vlastnosti. Keďže centrálny adresár zipu lokalizuje každú časť priamo, ich čítanie stojí pár kilobajtov bez ohľadu na to, aký veľký zošit je. TZipFile a IXMLDocument, obe v dodávanom RTL, zvládnu celú prácu

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žia tento kód v produkcii odolný. Po prvé, tieto časti sú voliteľné: minimálny balík úplne bez docProps je podľa ECMA-376 celkom platný, a preto kód namiesto predpokladov testuje prítomnosť cez IndexOf. Po druhé, elementy hľadajte podľa lokálneho názvu a URI menného priestoru, tak ako to robí FindNode vyššie, nikdy nie podľa doslovného prefixu; dc: a cp: sú konvenciou zapisovača Excelu a súbory z iných generátorov si môžu zvoliť odlišné prefixy. Jedna poznámka k prostrediu: predvoleným dodávateľom pre IXMLDocument je MSXML, takže konzolová aplikácia alebo pracovné vlákno musí pred LoadXMLData zavolať CoInitialize, inak prvé parsovanie zomrie s chybou COM

Prehľad nákladov a kedy knižnica prekoná oba parsery

Merané na bežnom vývojárskom stroji pristáva cesta cez COM zhruba na dvoch až štyroch sekundách na súbor, keď sa automatizačná relácia vytvára pre každý súbor zvlášť, pričom takmer všetko je štart EXCEL.EXE plus úplné rozparsovanie zošita, a vyžaduje nainštalovaný, licencovaný Excel všade, kde beží. Dve priame cesty čítajú iba kontajnery metadát, dokončia sa v jednotkách milisekúnd na súbor a nepotrebujú nič nainštalované nad rámec toho, čo si spustiteľný súbor v Delphi už tak linkuje. Naprieč zdieľaným priečinkom s desaťtisíc súbormi je to rozdiel medzi väčšinou pracovného dňa a menej než minútou, a to bez otázky nasadenia Office

Háčik pri priamych cestách je ten, že sú dve. Pipeline, ktorá prijíma oba formáty, udržiava dva parsery s dvoma disjunktnými spôsobmi zlyhania, kódové stránky a typy PROPVARIANT na jednej strane, menné priestory a voliteľné časti na druhej, pričom ani jeden nečíta formát toho druhého. Táto údržbová záťaž je argumentom pre natívnu knižnicu: HotXLS, tabuľková knižnica pre Object Pascal od losLab pre Delphi a C++Builder na Windows, sprístupňuje tie isté polia ako obyčajné vlastnosti zošita, Title, Author, Company, Created a ďalšie, naplnené volaním Open rovnako pre .xls ako pre .xlsx, bez inštalácie Excelu a bez celej kontajnerovej inštalatérčiny popísanej vyššie. Vlastnosti číta ako súčasť plného otvorenia zošita, nie ako sondu len po metadáta, takže sa hodí do pipeline, ktoré sa aj tak dostanú k dátam buniek; celú plochu vlastností na oboch fasádach vrátane zápisu pokrýva náš článok o nastavovaní vlastností dokumentu Excel pomocou HotXLS

Poznámka: Kompletné nástroje na parsovanie Excelu a extrakciu metadát sú dostupné v komponente HotXLS Delphi VCL Component