Excel-documenteigenschappen lezen in Delphi: drie routes

Vraag een pijplijn om tienduizend spreadsheets te routeren op auteur, bedrijf of datum van laatste wijziging, en het ergste wat zij kan doen is elke werkmap volledig openen. De antwoorden zitten in de documenteigenschappen van het bestand, wat de Office-wereld Document Summary Information noemt: de metadatalaag die Windows Search indexeert, waarop SharePoint bestanden ordent en die Excel in zijn dialoogvenster Eigenschappen toont. Die laag is hooguit enkele kilobytes groot, en zij woont op een goed gedocumenteerde plek in beide Excel-formaten. De kunst is haar vanuit Delphi te bereiken zonder te betalen voor het miljoen cellen dat u niet nodig hebt

Er zijn drie echte routes, en ze verschillen minder in wat ze teruggeven dan in wat ze eisen van de machine die ze draait. COM-automatisering stuurt Excel zelf aan en leest alles, tegen desktopprijzen. Het formaat .xls bewaart zijn eigenschappen in OLE property-set-streams die Windows voor u zal parsen. Het formaat .xlsx bewaart ze in twee kleine XML-onderdelen in een zip die de Delphi-RTL zelfstandig kan openen. Werkende code voor elk daarvan volgt hieronder, met de kosten er ronduit bij

Route 1: COM-automatisering leest alles, tegen desktopprijzen

Automatisering is de enige route met volledige dekking via één objectmodel: de standaard samenvattingsset, de uitgebreide set met Company en Manager, en gebruikersgedefinieerde aangepaste eigenschappen, allemaal bereikbaar via BuiltinDocumentProperties en CustomDocumentProperties. Alles komt binnen als een OleVariant, en de API heeft één gewoonte die u moet kennen voordat zij toeslaat: een ingebouwde eigenschap die nooit is toegewezen komt niet leeg terug, maar werpt een EOleException op zodra u Value aanraakt. De helper hieronder behandelt dat als "niet ingesteld" en niet als een mislukking

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 := '';   // eigenschap bestaat maar is nooit toegewezen
    end;
  end;

begin
  Excel := CreateOleObject('Excel.Application');
  try
    Excel.DisplayAlerts := False;
    Book := Excel.Workbooks.Open(FileName, 0, True);   // alleen-lezen
    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;   // bereik dit op elk pad, anders blijft EXCEL.EXE achter
    Excel := Unassigned;
  end;
end;

Nu de rekening. Excel moet geïnstalleerd zijn op elke machine waarop deze code draait, wat de meeste servers alleen daarmee al uitsluit, en het ondersteuningsbeleid van Microsoft stelt expliciet dat Office niet is ontworpen noch gelicentieerd voor onbeheerde automatisering aan serverzijde. CreateOleObject start een volledige EXCEL.EXE en Workbooks.Open parseert de hele werkmap, dus reken op ruwweg twee tot vier seconden per bestand voordat de eerste eigenschap terugkomt. En de try..finally rond Quit is geen versiering: een exceptie die tussen CreateOleObject en Quit ontsnapt, laat een verweesde EXCEL.EXE achter die een vergrendeling op het bestand vasthoudt, onzichtbaar totdat de volgende run daarop stukloopt. Eén Excel-instantie hergebruiken over een batch spreidt de opstartkosten maar concentreert het risico, want één verdwaald dialoogvenster op het verborgen bureaublad legt elk bestand stil dat erachter in de wachtrij staat

Route 2: .xls bewaart eigenschappen in OLE property-set-streams

Een BIFF8-werkmap is een OLE compound file, een miniatuurbestandssysteem van storages en streams. De celdata woont in de Workbook-stream; de metadata woont ernaast in twee property-set-streams waarvan de namen beginnen met het stuurteken #5: \005SummaryInformation voor de klassieke velden en \005DocumentSummaryInformation voor de uitgebreide en aangepaste velden. In elk daarvan zit een binaire property set in de MS-OLEPS-indeling, met secties op sleutel van een format-identifier (FMTID) en eigenschappen op sleutel van een geheeltallige property-ID. De samenvattingssectie is FMTID {F29F85E0-4FF9-1068-AB91-08002B27B3D9}, waar PIDSI_TITLE gelijk is aan $02 en PIDSI_AUTHOR aan $04; Company ($0F) en Manager ($0E) wonen in de document-summary-sectie, en aangepaste eigenschappen in een tweede sectie achter een naamwoordenboek

Het goede nieuws is dat u die bytes op Windows nooit zelf parseert. Structured storage stelt de streams beschikbaar via IPropertySetStorage, en het volgende compileert zoals getoond tegen de standaard RTL-units

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: niet aanwezig
  try
    case Value.vt of
      VT_LPSTR:  Result := string(AnsiString(Value.pszVal));
      VT_LPWSTR: Result := Value.pwszVal;
    end;
  finally
    PropVariantClear(Value);
  end;
end;

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

Een eerlijk woord over wat het fragment verbergt. Strings kunnen binnenkomen als VT_LPWSTR of als VT_LPSTR, en in het ANSI-geval zijn de bytes gecodeerd in de eigen codepagina van de property set, die zelf als eigenschap 1 van de sectie is opgeslagen, dus de cast hierboven klopt alleen precies wanneer die codepagina overeenkomt met die van het systeem. Tijdstempels komen terug als VT_FILETIME in UTC. Aangepaste eigenschappen betekenen dat u de gebruikersgedefinieerde sectie opent, FMTID {D5CDD505-2E9C-101B-9397-08002B2CF9AE}, en haar naamwoordenboek doorloopt. IPropertyStorage vangt dat alles op onder Windows; uw eigen MS-OLEPS-parser schrijven voor een omgeving zonder structured storage is een echt project, geen middagje werk

Route 3: .xlsx bewaart docProps als XML in de zip

Dit is de route die de meeste pijplijnen daadwerkelijk nodig hebben, aangezien nieuwe bestanden al bijna twee decennia .xlsx zijn. Een OOXML-werkmap is een zip-pakket, en haar eigenschappen zijn naar doel over kleine onderdelen verdeeld: docProps/core.xml bevat de Dublin Core-velden, dc:title, dc:creator, cp:lastModifiedBy, plus dcterms:created en dcterms:modified als W3CDTF-tijdstempels in UTC, terwijl docProps/app.xml velden op applicatieniveau bevat zoals Company en AppVersion, en docProps/custom.xml de aangepaste eigenschappen bevat. Omdat de centrale directory van de zip elk onderdeel rechtstreeks lokaliseert, kost het lezen ervan een paar kilobytes hoe groot de werkmap ook is. TZipFile en IXMLDocument, beide in de meegeleverde RTL, doen het hele werk

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;

Twee details houden dit robuust in productie. Ten eerste zijn de onderdelen optioneel: een minimaal pakket zonder enige docProps is onder ECMA-376 volkomen geldig, en daarom tast de code af met IndexOf in plaats van iets aan te nemen. Ten tweede matcht u elementen op lokale naam en namespace-URI, zoals FindNode hierboven doet, nooit op letterlijk voorvoegsel; dc: en cp: zijn conventies van de schrijver van Excel, en bestanden die door andere generatoren zijn geproduceerd mogen andere voorvoegsels kiezen. Eén omgevingsopmerking: de standaardleverancier van IXMLDocument is MSXML, dus een consoleapplicatie of workerthread moet CoInitialize aanroepen vóór LoadXMLData, anders sterft de eerste parse met een COM-fout

Het kostenoverzicht, en wanneer een bibliotheek beide parsers verslaat

Gemeten op een gewone ontwikkelaarsmachine landt de COM-route op ruwweg twee tot vier seconden per bestand wanneer de automatiseringssessie per bestand wordt aangemaakt, vrijwel geheel het opstarten van EXCEL.EXE plus een volledige parse van de werkmap, en zij vereist overal waar zij draait een geïnstalleerde, gelicentieerde Excel. De twee directe routes lezen alleen de metadatacontainers, zijn klaar in enkele milliseconden per bestand, en hebben niets nodig dat geïnstalleerd moet worden buiten wat een Delphi-executable al inlinkt. Over een share van tienduizend bestanden is dat het verschil tussen het grootste deel van een werkdag en minder dan een minuut, zonder enige vraag over Office-uitrol

Het addertje bij de directe routes is dat het er twee zijn. Een pijplijn die beide formaten accepteert, onderhoudt twee parsers met twee onsamenhangende faalwijzen, codepagina's en PROPVARIANT-typen aan de ene kant, namespaces en optionele onderdelen aan de andere, en geen van beide leest het formaat van de ander. Die onderhoudslast is het argument voor een native bibliotheek: HotXLS, de Object Pascal-spreadsheetbibliotheek van losLab voor Delphi en C++Builder op Windows, stelt dezelfde velden beschikbaar als gewone werkmapeigenschappen, Title, Author, Company, Created en de rest, gevuld door Open voor zowel .xls als .xlsx, zonder Excel-installatie en zonder al het containerleidingwerk hierboven. Zij leest eigenschappen als onderdeel van een volledige opening van de werkmap in plaats van een sonde die alleen metadata haalt, dus zij past bij pijplijnen die toch verdergaan met de celdata; het volledige eigenschappenoppervlak op beide facades, inclusief de schrijfkant, wordt behandeld in ons artikel over het instellen van Excel-documenteigenschappen met HotXLS

Opmerking: volledige gereedschappen voor Excel-parsing en metadata-extractie zijn beschikbaar in het HotXLS Delphi VCL Component