Čitanje svojstava Excel dokumenta u Delphiju: tri puta

Zatražite od cjevovoda da deset tisuća proračunskih tablica razvrsta po autoru, tvrtki ili datumu zadnje izmjene i najgore što može učiniti jest svaku radnu knjigu otvoriti u cijelosti. Odgovori putuju u svojstvima dokumenta, onome što svijet Officea zove Document Summary Information: sloj metapodataka koji Windows Search indeksira, po kojem SharePoint razvrstava i koji Excel prikazuje u svom dijalogu Properties. Taj sloj teži najviše nekoliko kilobajta i u oba Excelova formata leži na dobro dokumentiranom mjestu. Trik je do njega doprijeti iz Delphija bez plaćanja milijuna ćelija koje vam ne trebaju

Stvarna su tri puta i razlikuju se manje po tome što vraćaju, a više po tome što traže od stroja koji ih izvodi. COM automatizacija pogoni sam Excel i čita sve, po cijeni radne stanice. Format .xls svoja svojstva drži u OLE tokovima skupova svojstava koje će Windows parsirati umjesto vas. Format .xlsx ih drži u dvama malim XML dijelovima unutar zip arhive koju Delphi RTL može otvoriti sam. Slijedi radni kod za svaki, uz otvoreno navedene troškove

Put 1: COM automatizacija čita sve, po cijeni radne stanice

Automatizacija je jedini put s potpunom pokrivenošću kroz jedan objektni model: standardni skup sažetka, prošireni skup s poljima Company i Manager te korisnički definirana prilagođena svojstva, sve dohvatljivo kroz BuiltinDocumentProperties i CustomDocumentProperties. Sve stiže kao OleVariant, a API ima jednu naviku koju vrijedi znati prije nego što ugrize: ugrađeno svojstvo koje nikada nije dodijeljeno ne vraća se prazno, nego podigne EOleException u trenutku kada dotaknete Value. Pomoćna funkcija u nastavku to tretira kao „nije postavljeno“, a ne kao neuspjeh

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 := '';   // svojstvo postoji, ali nikada nije dodijeljeno
    end;
  end;

begin
  Excel := CreateOleObject('Excel.Application');
  try
    Excel.DisplayAlerts := False;
    Book := Excel.Workbooks.Open(FileName, 0, True);   // samo za čitanje
    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;   // dosegni ovo na svakom putu, inače EXCEL.EXE ostaje iza
    Excel := Unassigned;
  end;
end;

Sada račun. Excel mora biti instaliran na svakom stroju na kojem se ovaj kod izvodi, što samo po sebi isključuje većinu poslužitelja, a Microsoftova je politika podrške izričita da Office nije ni zamišljen ni licenciran za automatizaciju bez nadzora na strani poslužitelja. CreateOleObject pokreće cijeli EXCEL.EXE, a Workbooks.Open parsira cijelu radnu knjigu, pa očekujte otprilike dvije do četiri sekunde po datoteci prije nego što se vrati prvo svojstvo. I try..finally oko poziva Quit nije ukras: iznimka koja pobjegne između CreateOleObject i Quit ostavlja siroti EXCEL.EXE koji drži zaključanu datoteku, nevidljiv sve dok se sljedeće pokretanje o njega ne spotakne. Ponovna upotreba jedne instance Excela kroz cijelu seriju amortizira trošak pokretanja, ali koncentrira rizik, jer jedan zalutali dijalog na skrivenoj radnoj površini zaustavi svaku datoteku iza sebe u redu

Put 2: .xls svojstva pohranjuje u OLE tokovima skupova svojstava

BIFF8 radna knjiga OLE je složena datoteka, minijaturni datotečni sustav spremišta i tokova. Podaci ćelija žive u toku Workbook; metapodaci žive uz njega u dvama tokovima skupova svojstava čiji nazivi počinju upravljačkim znakom #5: \005SummaryInformation za klasična polja i \005DocumentSummaryInformation za proširena i prilagođena. Unutar svakog sjedi binarni skup svojstava u rasporedu MS-OLEPS, s odjeljcima ključanima identifikatorom formata (FMTID) i svojstvima ključanima cjelobrojnom oznakom svojstva. Odjeljak sažetka nosi FMTID {F29F85E0-4FF9-1068-AB91-08002B27B3D9}, u kojemu je PIDSI_TITLE jednak $02, a PIDSI_AUTHOR jednak $04; Company ($0F) i Manager ($0E) žive u odjeljku sažetka dokumenta, a prilagođena svojstva u drugom odjeljku iza rječnika naziva

Dobra je vijest da te bajtove na Windowsima nikada ne parsirate sami. Strukturirano spremište tokove izlaže kroz IPropertySetStorage, a sljedeći se kod prevodi upravo ovako uz standardne RTL jedinice

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

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

Poštena riječ o onome što isječak prešućuje. Nizovi znakova mogu stići kao VT_LPWSTR ili kao VT_LPSTR, a u ANSI slučaju bajtovi su kodirani kodnom stranicom samog skupa svojstava, koja je pak pohranjena kao svojstvo 1 tog odjeljka, pa je gornja pretvorba točna samo kada se ta kodna stranica poklapa sa sustavskom. Vremenske oznake vraćaju se kao VT_FILETIME u UTC-u. Prilagođena svojstva znače otvaranje korisnički definiranog odjeljka, FMTID {D5CDD505-2E9C-101B-9397-08002B2CF9AE}, i prolazak kroz njegov rječnik naziva. IPropertyStorage sve to na Windowsima upija; pisanje vlastitog MS-OLEPS parsera za okruženje bez strukturiranog spremišta pravi je projekt, a ne posao za jedno poslijepodne

Put 3: .xlsx docProps drži kao XML unutar zip arhive

To je put koji većini cjevovoda uistinu treba, jer nove su datoteke .xlsx već gotovo dva desetljeća. OOXML radna knjiga zip je paket, a njezina su svojstva po namjeni razdijeljena na male dijelove: docProps/core.xml nosi polja iz standarda Dublin Core, dc:title, dc:creator, cp:lastModifiedBy, uz dcterms:created i dcterms:modified kao W3CDTF vremenske oznake u UTC-u, dok docProps/app.xml nosi polja na razini aplikacije poput Company i AppVersion, a docProps/custom.xml nosi prilagođena svojstva. Budući da središnji direktorij zip arhive svaki dio nalazi izravno, njihovo čitanje stoji nekoliko kilobajta bez obzira na to koliko je radna knjiga velika. TZipFile i IXMLDocument, oboje u isporučenom RTL-u, obavljaju cijeli posao

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 detalja ovo drže otpornim u proizvodnji. Prvo, dijelovi su neobvezni: minimalan paket bez ijednog docProps potpuno je valjan prema ECMA-376, i zato kod provjerava pozivom IndexOf umjesto da pretpostavlja. Drugo, elemente podudarajte po lokalnom nazivu i URI-ju imenskog prostora, kako to gore radi FindNode, a nikada po doslovnom prefiksu; dc: i cp: konvencije su Excelova zapisivača, a datoteke drugih generatora slobodno biraju druge prefikse. Jedna napomena o okruženju: podrazumijevani dobavljač za IXMLDocument jest MSXML, pa konzolna aplikacija ili radna dretva mora pozvati CoInitialize prije poziva LoadXMLData, inače prvo parsiranje umre uz COM pogrešku

Račun troškova i kada biblioteka pobjeđuje oba parsera

Mjereno na običnom razvojnom stroju, COM put slijeće na otprilike dvije do četiri sekunde po datoteci kada se sesija automatizacije stvara za svaku datoteku, gotovo sve od toga na pokretanje EXCEL.EXE i potpuno parsiranje radne knjige, i traži instaliran, licenciran Excel svugdje gdje se izvodi. Dva izravna puta čitaju samo spremnike metapodataka, završe u jednoznamenkastom broju milisekundi po datoteci i ne traže ništa instalirano osim onoga što Delphi izvršna datoteka ionako uključuje. Na dijeljenom mjestu s deset tisuća datoteka to je razlika između većeg dijela radnog dana i manje od minute, bez ijednog pitanja o postavljanju Officea

Kvaka s izravnim putovima jest to što ih je dva. Cjevovod koji prihvaća oba formata održava dva parsera s dvama nepovezanim načinima otkazivanja, kodne stranice i tipove PROPVARIANT s jedne strane, imenske prostore i neobvezne dijelove s druge, i nijedan ne čita format onog drugog. To opterećenje održavanjem argument je za izvornu biblioteku: HotXLS, losLabova Object Pascal biblioteka za proračunske tablice za Delphi i C++Builder na Windowsima, ista polja izlaže kao obična svojstva radne knjige, Title, Author, Company, Created i ostalo, koja poziv Open popunjava jednako za .xls i za .xlsx, bez instaliranog Excela i bez ijednog dijela gornjeg spremničkog vodovoda. Svojstva čita u sklopu potpunog otvaranja radne knjige, a ne kao sondu samo po metapodacima, pa odgovara cjevovodima koji ionako idu dalje u podatke ćelija; potpuna površina svojstava na obje fasade, uključujući stranu zapisivanja, obrađena je u našem članku o postavljanju svojstava Excel dokumenta pomoću HotXLS-a

Napomena: potpuni alati za parsiranje Excela i izvlačenje metapodataka dostupni su u komponenti HotXLS Delphi VCL Component