Udtrækning af dokumentoversigtsoplysninger fra Excel-filer i Delphi

Bed en pipeline om at dirigere titusind regneark efter forfatter, virksomhed eller senest ændret dato, og det værste, den kan gøre, er at åbne hver projektmappe fuldstændigt. Svarene ligger i filens dokumentegenskaber, det som Office-verdenen kalder Document Summary Information: det metadatalag, som Windows Search indekserer, SharePoint arkiverer efter, og som Excel viser i sin Egenskaber-dialogboks. Det lag er højst på nogle få kilobytes, og det bor på et veldokumenteret sted i begge Excel-formater. Tricket er at nå det fra Delphi uden at betale for de millioner af celler, du ikke har brug for

Der er tre reelle ruter, og de adskiller sig mindre i, hvad de returnerer, end i hvad de kræver af maskinen, der kører dem. COM-automatisering driver selve Excel og læser alt til desktop-priser. .xls-formatet beholder sine egenskaber i OLE property-set streams, som Windows vil parse for dig. .xlsx-formatet opbevarer dem i to små XML-dele inde i en zip-fil, som Delphi RTL kan åbne på egen hånd. Fungerende kode for hver følger herunder, med omkostningerne angivet tydeligt

Rute 1: COM-automatisering læser alt til desktop-priser

Automatisering er den eneste rute med total dækning gennem én objektmodel: det standardiserede summary-sæt, det udvidede sæt med Firma og Leder (Company og Manager) samt brugerdefinerede egenskaber, der alle kan nås gennem BuiltinDocumentProperties og CustomDocumentProperties. Alt ankommer som en OleVariant, og API'et har en vane, som er værd at kende, før den bider: en indbygget egenskab, der aldrig blev tildelt, kommer ikke tom tilbage; den rejser en EOleException, i det øjeblik du rører ved Value. Hjælperen nedenfor behandler det som "ikke indstillet" i stedet for som en fejl

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;

Og nu regningen. Excel skal være installeret på hver maskine, denne kode kører på, hvilket i sig selv udelukker de fleste servere, og Microsofts supportpolitik angiver udtrykkeligt, at Office hverken er designet eller licenseret til uovervåget automatisering på serversiden. CreateOleObject starter en fuld EXCEL.EXE, og Workbooks.Open parser hele projektmappen, så forvent cirka to til fire sekunder pr. fil, før den første egenskab kommer tilbage. Og try..finally omkring Quit er ikke til pynt: en undtagelse (exception), der undslipper mellem CreateOleObject og Quit, efterlader en forældreløs EXCEL.EXE, der holder en lås på filen, usynlig indtil næste kørsel fejler på grund af det. Genbrug af én Excel-instans på tværs af en batch afskriver opstartsomkostningerne, men koncentrerer risikoen, fordi en enkelt vildfaren dialogboks på det skjulte skrivebord vil blokere hver eneste fil, der står i kø bag den

Rute 2: .xls gemmer egenskaber i OLE property-set streams

En BIFF8-projektmappe er en OLE compound-fil, et miniature filsystem bestående af storages og streams. Celledataene bor i Workbook-streamen; metadataene bor ved siden af den i to property-set streams, hvis navne begynder med kontroltegn #5: \005SummaryInformation til de klassiske felter og \005DocumentSummaryInformation til de udvidede og brugerdefinerede. Inden i hver sidder et binært property-set i MS-OLEPS-layoutet med sektioner, der er nøglet af en formatidentifikator (FMTID), og egenskaber nøglet med et integer property ID. Summary-sektionen er FMTID {F29F85E0-4FF9-1068-AB91-08002B27B3D9}, hvor PIDSI_TITLE er $02 og PIDSI_AUTHOR er $04; Firma ($0F) og Leder ($0E) bor i dokument-summary-sektionen, og brugerdefinerede egenskaber i en anden sektion bag en navneordbog (name dictionary)

Den gode nyhed er, at du på Windows aldrig selv behøver at parse disse bytes. Structured storage eksponerer streams gennem IPropertySetStorage, og det følgende kompileres som vist med standard 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: 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));

Et ærligt ord om, hvad kodestykket skjuler. Strenge kan ankomme som VT_LPWSTR eller som VT_LPSTR, og i ANSI-tilfældet er bytene kodet i property-sættets egen code page, der i sig selv gemmes som egenskab 1 i sektionen, så castet ovenfor er kun eksakt, når den code page matcher systemets. Tidsstempler kommer tilbage som VT_FILETIME i UTC. Brugerdefinerede egenskaber betyder at åbne den brugerdefinerede sektion, FMTID {D5CDD505-2E9C-101B-9397-08002B2CF9AE}, og gennemgå dens navneordbog. IPropertyStorage absorberer alt dette på Windows; at skrive sin egen MS-OLEPS-parser til et miljø uden structured storage er et reelt projekt, ikke noget der klares på en eftermiddag

Rute 3: .xlsx beholder docProps som XML inde i zip'en

Dette er den rute, de fleste pipelines faktisk har brug for, da nye filer har været .xlsx i næsten to årtier. En OOXML-projektmappe er en zip-pakke, og dens egenskaber er opdelt i små dele efter formål: docProps/core.xml indeholder Dublin Core-felterne, dc:title, dc:creator, cp:lastModifiedBy, samt dcterms:created og dcterms:modified som W3CDTF-tidsstempler i UTC, mens docProps/app.xml indeholder felter på applikationsniveau, såsom Firma og AppVersion, og docProps/custom.xml indeholder brugerdefinerede egenskaber. Fordi zip'ens centrale mappe finder hver del direkte, koster det kun et par kilobytes at læse dem, uanset hvor stor projektmappen er. TZipFile og IXMLDocument, der begge medfølger i RTL, klarer hele opgaven

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;

To detaljer holder dette robust i produktion. For det første er delene valgfrie: en minimal pakke uden docProps overhovedet er fuldt gyldig under ECMA-376, hvilket er grunden til, at koden tester med IndexOf i stedet for bare at antage. For det andet, match elementer efter lokalnavn og namespace-URI, ligesom FindNode gør ovenfor, og aldrig efter bogstaveligt præfiks; dc: og cp: er konventioner i Excels writer, og filer produceret af andre generatorer er frie til at vælge andre præfikser. En bemærkning til miljøet: standard IXMLDocument-leverandøren er MSXML, så en konsolapplikation eller workertråd skal kalde CoInitialize før LoadXMLData, ellers dør den første parsing med en COM-fejl

Omkostningsarket, og når et bibliotek slår begge parsere

Målt på en almindelig udviklermaskine lander COM-ruten på cirka to til fire sekunder pr. fil, når automatiseringssessionen oprettes pr. fil – næsten det hele er EXCEL.EXE-opstart plus en fuld parsing af projektmappen, og det kræver et installeret og licenseret Excel, uanset hvor det kører. De civiliserede, direkte ruter læser kun metadatacontainerne, er færdige på etcifrede millisekunder pr. fil og har ikke brug for, at andet er installeret end det, en Delphi-eksekverbar fil allerede linker til. På tværs af en andel på titusind filer, udgør dette forskellen på det meste af en arbejdsdag og under et minut, uden at der er knyttet et Office-udrulningsspørgsmål til

Hagen ved de direkte ruter er, at der er to af dem. En pipeline, der accepterer begge formater, vedligeholder to parsere med to disjunkte fejltilstande: code pages og PROPVARIANT-typer på den ene side, namespaces og valgfrie dele på den anden, og ingen af dem læser den andens format. Denne vedligeholdelsesbyrde er sagen for et native bibliotek: HotXLS, losLabs Object Pascal regnearksbibliotek til Delphi og C++Builder på Windows, eksponerer de samme felter som almindelige projektmappeegenskaber (Title, Author, Company, Created og resten), som udfyldes af Open for både .xls og .xlsx, uden nogen Excel-installation og intet af containerarbejdet ovenfor. Det læser egenskaber som en del af en fuld åbning af projektmappen frem for en ren metadata-forespørgsel, så det passer til pipelines, der alligevel vil røre ved celledataene; den fulde egenskabsoverflade på begge facader, inklusive skrivesiden, er dækket i vores artikel om indstilling af Excel-dokumentegenskaber med HotXLS

Bemærk: Fulde Excel-parsing- og metadataudtræksværktøjer er tilgængelige i HotXLS VCL-komponenten