Asiakirjan yhteenvetotietojen purkaminen Excel-tiedostoista Delphissä

Käske liukuhihnaa (pipeline) reitittämään kymmenen tuhatta laskentataulukkoa tekijän, yrityksen tai viimeisimmän muokkauspäivän mukaan, ja pahinta, mitä se voi tehdä, on avata jokainen työkirja kokonaan. Vastaukset kulkevat tiedoston asiakirjan ominaisuuksissa (document properties), joita Office-maailma kutsuu nimellä Document Summary Information: se on metatietokerros, jonka Windowsin haku indeksoi, jonka mukaan SharePoint lajittelee tiedostot, ja jonka Excel näyttää Ominaisuudet-valintaikkunassaan. Tämä kerros on korkeintaan kilotavujen kokoinen, ja se sijaitsee hyvin dokumentoidussa paikassa molemmissa Excel-muodoissa. Juju on siinä, miten siihen päästään käsiksi Delphistä ilman, että joudutaan maksamaan miljoonasta solusta, joita ei tarvita

Todellisia reittejä on kolme, ja ne eroavat vähemmän siinä, mitä ne palauttavat, kuin siinä, mitä ne vaativat niitä suorittavalta koneelta. COM-automaatio ohjaa itse Exceliä ja lukee kaiken työpöytäympäristön kustannuksella. .xls-muoto pitää ominaisuutensa OLE-ominaisuusjoukkovirroissa (property-set streams), jotka Windows jäsentää puolestasi. .xlsx-muoto pitää ne kahdessa pienessä XML-osassa zip-paketin sisällä, jonka Delphi RTL voi avata itse. Alla on toimiva koodi kullekin tavalle, ja niiden kustannukset on esitetty selkeästi

Reitti 1: COM-automaatio lukee kaiken työpöytäympäristön kustannuksella

Automaatio on ainoa reitti, joka tarjoaa täyden kattavuuden yhden objektimallin kautta: vakioyhteenvetojoukko (standard summary set), laajennettu joukko Yritys- ja Esimies-tiedoilla sekä käyttäjän määrittämät mukautetut ominaisuudet ovat kaikki saavutettavissa BuiltinDocumentProperties- ja CustomDocumentProperties-kokoelmien kautta. Kaikki saapuu OleVariant-muodossa, ja rajapinnalla on yksi tapa, joka kannattaa tietää ennen kuin se puree: sisäänrakennettu ominaisuus, jolle ei koskaan määritetty arvoa, ei palaa tyhjänä, vaan se nostaa EOleException-poikkeuksen heti, kun kosketat Value-kenttää. Alla oleva apufunktio käsittelee tätä "ei asetettu" -tilanteena eikä virheenä

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 := '';   // ominaisuus on olemassa mutta sitä ei ole asetettu
    end;
  end;

begin
  Excel := CreateOleObject('Excel.Application');
  try
    Excel.DisplayAlerts := False;
    Book := Excel.Workbooks.Open(FileName, 0, True);   // vain luku
    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;   // saavuta tämä joka polulla, tai EXCEL.EXE jää taustalle
    Excel := Unassigned;
  end;
end;

Sitten hintalappu. Excelin on oltava asennettuna jokaiselle koneelle, jossa tämä koodi suoritetaan, mikä sulkee itsessään pois useimmat palvelimet, ja Microsoftin tukikäytäntö on selkeä siitä, että Officea ei ole suunniteltu eikä lisensoitu valvomattomaan palvelinpuolen automaatioon. CreateOleObject käynnistää täyden EXCEL.EXE:n ja Workbooks.Open jäsentää koko työkirjan, joten odota noin kahdesta neljään sekuntia tiedostoa kohti ennen kuin ensimmäinen ominaisuus palaa. try..finally-lohko Quit-kutsun ympärillä ei ole koriste: poikkeus, joka karkaa CreateOleObject:n ja Quit:n välillä, jättää orvon EXCEL.EXE:n, joka pitää tiedoston lukittuna, ja se on näkymätön, kunnes seuraava suoritus epäonnistuu sitä vasten. Yhden Excel-esiintymän uudelleenkäyttö eräajossa kuolettaa käynnistyskustannuksen, mutta keskittää riskin, koska yksi eksynyt valintaikkuna piilotetulla työpöydällä pysäyttää jokaisen sen taakse jonotetun tiedoston

Reitti 2: .xls tallentaa ominaisuudet OLE-ominaisuusjoukkovirtoihin

BIFF8-työkirja on OLE-yhdistelmätiedosto, pienoiskoossa oleva tiedostojärjestelmä tallennuspaikkoja (storages) ja virtoja (streams). Solutiedot asuvat Workbook-virrassa; metatiedot asuvat sen vieressä kahdessa ominaisuusjoukkovirrassa, joiden nimet alkavat ohjausmerkillä #5: \005SummaryInformation klassisille kentille ja \005DocumentSummaryInformation laajennetuille ja mukautetuille kentille. Kummankin sisällä istuu binäärinen ominaisuusjoukko MS-OLEPS-asettelulla, jossa osiot (sections) on avaimennettu muototunnisteella (FMTID) ja ominaisuudet on avaimennettu kokonaislukuisella ominaisuuden tunnuksella (Property ID). Yhteenveto-osio (summary section) on FMTID {F29F85E0-4FF9-1068-AB91-08002B27B3D9}, jossa PIDSI_TITLE on $02 ja PIDSI_AUTHOR on $04; Yritys ($0F) ja Esimies ($0E) sijaitsevat asiakirjan yhteenveto-osiossa, ja mukautetut ominaisuudet toisessa osiossa nimisanakirjan takana

Hyvä uutinen on, että Windowsissa sinun ei koskaan tarvitse jäsentää näitä tavuja itse. Rakenteellinen tallennus (structured storage) paljastaa virrat IPropertySetStorage-rajapinnan kautta, ja seuraava koodi kääntyy sellaisenaan vakiomuotoisia RTL-yksiköitä vasten

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

// käyttö: Writeln('Author: ', ReadXlsSummaryString('ledger.xls', PIDSI_AUTHOR));

Rehellinen sana siitä, mitä koodinpätkä piilottaa. Merkkijonot voivat saapua muodossa VT_LPWSTR tai VT_LPSTR, ja ANSI-tapauksessa tavut koodataan ominaisuusjoukon omaan koodisivuun, joka itsessään on tallennettu osion ominaisuutena 1, joten yllä oleva tyyppimuunnos (cast) on tarkka vain, kun kyseinen koodisivu vastaa järjestelmän koodisivua. Aikaleimat palautetaan muodossa VT_FILETIME UTC-ajassa. Mukautetut ominaisuudet tarkoittavat käyttäjän määrittelemän osion, FMTID {D5CDD505-2E9C-101B-9397-08002B2CF9AE}, avaamista ja sen nimisanakirjan läpikäymistä. IPropertyStorage absorboi kaiken tämän Windowsissa; oman MS-OLEPS-jäsentimen kirjoittaminen ympäristöön, jossa ei ole rakenteellista tallennusta, on todellinen projekti, ei iltapäivän pikkuhomma

Reitti 3: .xlsx pitää docProps-tiedot XML:nä zip-paketin sisällä

Tämä on reitti, jota useimmat liukuhihnat (pipelines) todella tarvitsevat, koska uudet tiedostot ovat olleet .xlsx-muodossa lähes kahden vuosikymmenen ajan. OOXML-työkirja on zip-paketti, ja sen ominaisuudet on jaettu pieniin osiin tarkoituksen mukaan: docProps/core.xml pitää sisällään Dublin Core -kentät, dc:title, dc:creator, cp:lastModifiedBy sekä dcterms:created ja dcterms:modified W3CDTF-aikaleimoina UTC-ajassa, kun taas docProps/app.xml sisältää sovellustason kenttiä, kuten Yritys ja Sovelluksen versio, ja docProps/custom.xml sisältää mukautettuja ominaisuuksia. Koska zip-paketin keskusluettelo paikantaa jokaisen osan suoraan, niiden lukeminen maksaa muutaman kilotavun riippumatta työkirjan koosta. TZipFile ja IXMLDocument, jotka molemmat toimitetaan RTL:n mukana, tekevät koko työn

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;

Kaksi yksityiskohtaa pitää tämän vankkana tuotannossa. Ensinnäkin osat ovat valinnaisia: minimaalinen paketti ilman mitään docProps-kansiota on täysin validi ECMA-376:n puitteissa, minkä vuoksi koodi tutkii tilannetta IndexOf-funktiolla olettamisen sijaan. Toiseksi, täsmää elementit paikallisella nimellä ja nimiavaruuden URI:lla (namespace URI), kuten FindNode tekee yllä, äläkä koskaan kirjaimellisella etuliitteellä; dc: ja cp: ovat Excel-kirjoittajan konventioita, ja muiden generaattoreiden tuottamat tiedostot voivat vapaasti valita eri etuliitteitä. Yksi ympäristöä koskeva huomautus: oletusarvoinen IXMLDocument-toimittaja on MSXML, joten konsolisovelluksen tai työsäikeen on kutsuttava CoInitialize-funktiota ennen LoadXMLData-funktiota, tai ensimmäinen jäsennys kuolee COM-virheeseen

Kustannuslaskelma, ja milloin kirjasto voittaa molemmat jäsentimet

Mitattuna tavallisella kehittäjäkoneella COM-reitti asettuu noin kahdesta neljään sekuntiin tiedostoa kohden, kun automaatio-istunto luodaan tiedostoa kohti, josta lähes kaikki on EXCEL.EXE:n käynnistymistä plus työkirjan täyttä jäsentämistä, ja se vaatii asennetun, lisensoidun Excelin kaikkialla missä se suoritetaan. Kaksi suoraa reittiä lukevat vain metatietosäiliöt, valmistuvat yksinumeroisissa millisekunneissa tiedostoa kohti, eivätkä tarvitse asennetuksi mitään muuta kuin mitä Delphi-suoritettava tiedosto (executable) jo linkittää mukaansa. Kymmenentuhannen tiedoston jaossa tämä on ero suurimman osan työpäivää ja alle minuutin välillä, ilman että Office-asennukseen liittyy kysymyksiä

Suorien reittien juju on siinä, että niitä on kaksi. Molemmat tiedostomuodot hyväksyvä liukuhihna (pipeline) ylläpitää kahta jäsennintä, joilla on kaksi erillistä vikatilaa, koodisivuja ja PROPVARIANT-tyyppejä toisella puolella, nimiavaruuksia ja valinnaisia osia toisella, eikä kumpikaan lue toisen muotoa. Tämä ylläpitotaakka on natiivikirjaston (native library) heiniä: HotXLS, losLabin Object Pascal -taulukkolaskentakirjasto Delphille ja C++Builderille Windowsissa, paljastaa samat kentät yksinkertaisina työkirjan ominaisuuksina, Otsikko, Tekijä, Yritys, Luotu ja loput, jotka Open-komento täyttää sekä .xls- että .xlsx-tiedostoille samalla tavalla, ilman Excel-asennusta ja ilman mitään yllä mainittua säiliöiden putkityötä. Se lukee ominaisuudet osana täyttä työkirjan avaamista pelkän metatietojen tarkistuksen sijaan, joten se sopii liukuhihnoihin (pipelines), jotka muutenkin koskettavat solujen tietoja; koko ominaisuuspinta molemmissa fasadeissa (facades), mukaan lukien kirjoituspuoli, käsitellään artikkelissamme Excel-asiakirjan ominaisuuksien asettamisesta HotXLS:llä

Huomautus: Täydet Excel-jäsennyksen ja metatietojen purkamisen työkalut ovat saatavilla HotXLS VCL -komponentissa