Wydobywanie informacji podsumowujących dokument z plików Excel w Delphi

Poproś potok o przekierowanie dziesięciu tysięcy arkuszy kalkulacyjnych według autora, firmy czy daty ostatniej modyfikacji, a najgorszą rzeczą, jaką może zrobić, jest pełne otwarcie każdego skoroszytu. Odpowiedzi kryją się we właściwościach dokumentu pliku, tym, co świat Office nazywa informacjami podsumowującymi dokument (Document Summary Information): warstwą metadanych, którą indeksuje Windows Search, według której porządkuje pliki SharePoint i którą Excel pokazuje w swoim oknie dialogowym Właściwości. Ta warstwa ma najwyżej kilka kilobajtów i mieszka w dobrze udokumentowanym miejscu w obu formatach Excela. Sztuczka polega na dotarciu do niej z Delphi bez płacenia za milion komórek, których nie potrzebujesz

Istnieją trzy realne drogi i różnią się one mniej tym, co zwracają, niż tym, czego wymagają od maszyny, która je uruchamia. Automatyzacja COM steruje samym Excelem i odczytuje wszystko, po cenach komputera stacjonarnego. Format .xls przechowuje swoje właściwości w strumieniach zestawów właściwości OLE, które Windows sparsuje za ciebie. Format .xlsx trzyma je w dwóch małych częściach XML wewnątrz archiwum zip, które RTL Delphi potrafi otworzyć samodzielnie. Dalej znajdziesz działający kod dla każdej z nich, z kosztami przedstawionymi wprost

Droga 1: automatyzacja COM odczytuje wszystko, po cenach komputera stacjonarnego

Automatyzacja to jedyna droga z pełnym pokryciem przez jeden model obiektów: standardowy zestaw podsumowania, rozszerzony zestaw z Company i Manager oraz zdefiniowane przez użytkownika właściwości niestandardowe, wszystkie osiągalne przez BuiltinDocumentProperties i CustomDocumentProperties. Wszystko przychodzi jako OleVariant, a API ma jeden nawyk warty poznania, zanim ugryzie: wbudowana właściwość, która nigdy nie została przypisana, nie wraca jako pusta, lecz zgłasza EOleException w momencie, gdy dotkniesz Value. Poniższa funkcja pomocnicza traktuje to jako „nieustawione” zamiast jako niepowodzenie

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;

Teraz rachunek. Excel musi być zainstalowany na każdej maszynie, na której działa ten kod, co samo w sobie wyklucza większość serwerów, a polityka wsparcia Microsoftu jednoznacznie mówi, że Office nie jest ani zaprojektowany, ani licencjonowany do nienadzorowanej automatyzacji po stronie serwera. CreateOleObject uruchamia pełny EXCEL.EXE, a Workbooks.Open parsuje cały skoroszyt, więc spodziewaj się mniej więcej od dwóch do czterech sekund na plik, zanim pierwsza właściwość wróci. A try..finally wokół Quit to nie ozdoba: wyjątek, który wymknie się między CreateOleObject a Quit, pozostawia osierocony EXCEL.EXE trzymający blokadę na pliku, niewidoczny aż do momentu, gdy następne uruchomienie nie powiedzie się na jego tle. Ponowne użycie jednej instancji Excela w całej partii amortyzuje koszt startu, ale koncentruje ryzyko, bo jedno zabłąkane okno dialogowe na ukrytym pulpicie zatrzymuje każdy plik ustawiony w kolejce za nim

Droga 2: .xls przechowuje właściwości w strumieniach zestawów właściwości OLE

Skoroszyt BIFF8 to plik złożony OLE, miniaturowy system plików ze storage'ów i strumieni. Dane komórek mieszkają w strumieniu Workbook; metadane leżą obok w dwóch strumieniach zestawów właściwości, których nazwy zaczynają się od znaku sterującego #5: \005SummaryInformation dla klasycznych pól i \005DocumentSummaryInformation dla rozszerzonych i niestandardowych. Wewnątrz każdego siedzi binarny zestaw właściwości w układzie MS-OLEPS, z sekcjami kluczowanymi identyfikatorem formatu (FMTID) i właściwościami kluczowanymi całkowitym identyfikatorem właściwości. Sekcja podsumowania to FMTID {F29F85E0-4FF9-1068-AB91-08002B27B3D9}, gdzie PIDSI_TITLE to $02, a PIDSI_AUTHOR to $04; Company ($0F) i Manager ($0E) mieszkają w sekcji document-summary, a właściwości niestandardowe w drugiej sekcji za słownikiem nazw

Dobra wiadomość jest taka, że w Windows nigdy nie parsujesz tych bajtów samodzielnie. Structured storage udostępnia strumienie przez IPropertySetStorage, a poniższe kompiluje się w takiej postaci, w jakiej jest pokazane, względem standardowych modułów RTL

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));

Uczciwe słowo o tym, co ten fragment ukrywa. Ciągi mogą przyjść jako VT_LPWSTR lub jako VT_LPSTR, a w przypadku ANSI bajty są zakodowane w stronie kodowej samego zestawu właściwości, która z kolei jest przechowywana jako właściwość 1 sekcji, więc powyższe rzutowanie jest dokładne tylko wtedy, gdy ta strona kodowa pasuje do systemowej. Znaczniki czasu wracają jako VT_FILETIME w UTC. Właściwości niestandardowe oznaczają otwarcie sekcji zdefiniowanej przez użytkownika, FMTID {D5CDD505-2E9C-101B-9397-08002B2CF9AE}, i przejście przez jej słownik nazw. IPropertyStorage pochłania to wszystko w Windows; napisanie własnego parsera MS-OLEPS dla środowiska bez structured storage to prawdziwy projekt, a nie popołudnie

Droga 3: .xlsx trzyma docProps jako XML wewnątrz archiwum zip

To droga, której faktycznie potrzebuje większość potoków, ponieważ nowe pliki są w formacie .xlsx od prawie dwóch dekad. Skoroszyt OOXML to paczka zip, a jego właściwości są rozdzielone na małe części według przeznaczenia: docProps/core.xml zawiera pola Dublin Core, dc:title, dc:creator, cp:lastModifiedBy, plus dcterms:created i dcterms:modified jako znaczniki czasu W3CDTF w UTC, natomiast docProps/app.xml zawiera pola na poziomie aplikacji, takie jak Company i AppVersion, a docProps/custom.xml zawiera właściwości niestandardowe. Ponieważ katalog centralny archiwum zip lokalizuje każdą część bezpośrednio, odczytanie ich kosztuje kilka kilobajtów niezależnie od tego, jak duży jest skoroszyt. TZipFile i IXMLDocument, oba w dostarczanym RTL, wykonują całą robotę

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;

Dwa szczegóły utrzymują to w produkcji w stanie odpornym. Po pierwsze, części są opcjonalne: minimalna paczka bez żadnego docProps jest całkowicie poprawna według ECMA-376, dlatego kod sonduje za pomocą IndexOf zamiast zakładać. Po drugie, dopasowuj elementy według nazwy lokalnej i URI przestrzeni nazw, jak robi to powyżej FindNode, nigdy według dosłownego prefiksu; dc: i cp: to konwencje zapisujące Excela, a pliki wytworzone przez inne generatory mogą swobodnie wybrać inne prefiksy. Jedna uwaga środowiskowa: domyślnym dostawcą IXMLDocument jest MSXML, więc aplikacja konsolowa lub wątek roboczy musi wywołać CoInitialize przed LoadXMLData, w przeciwnym razie pierwsze parsowanie umiera z błędem COM

Karta kosztów oraz kiedy biblioteka bije oba parsery

Zmierzona na zwykłej maszynie deweloperskiej, droga COM ląduje na mniej więcej dwóch do czterech sekund na plik, gdy sesja automatyzacji jest tworzona dla każdego pliku, przy czym prawie całość to start EXCEL.EXE plus pełne parsowanie skoroszytu, i wymaga zainstalowanego, licencjonowanego Excela wszędzie tam, gdzie działa. Dwie bezpośrednie drogi odczytują tylko kontenery metadanych, kończą w jednocyfrowej liczbie milisekund na plik i nie potrzebują niczego zainstalowanego poza tym, co plik wykonywalny Delphi już w sobie linkuje. Na udziale dziesięciu tysięcy plików to różnica między większością dnia roboczego a mniej niż minutą, bez dołączonego pytania o wdrożenie Office

Haczyk bezpośrednich dróg jest taki, że są dwie. Potok, który przyjmuje oba formaty, utrzymuje dwa parsery z dwoma rozłącznymi trybami awarii, strony kodowe i typy PROPVARIANT po jednej stronie, przestrzenie nazw i opcjonalne części po drugiej, i żaden nie odczytuje formatu drugiego. To obciążenie utrzymaniowe jest argumentem za natywną biblioteką: HotXLS, biblioteka arkuszy kalkulacyjnych losLab napisana w Object Pascal dla Delphi i C++Builder na Windows, udostępnia te same pola jako zwykłe właściwości skoroszytu, Title, Author, Company, Created i resztę, wypełniane przez Open zarówno dla .xls, jak i .xlsx, bez instalacji Excela i bez żadnej instalacji kontenerowej powyżej. Odczytuje właściwości w ramach pełnego otwarcia skoroszytu, a nie sondowania wyłącznie metadanych, więc pasuje do potoków, które i tak dotykają dalej danych komórek; pełna powierzchnia właściwości na obu fasadach, w tym strona zapisu, jest omówiona w naszym artykule o ustawianiu właściwości dokumentu Excel za pomocą HotXLS

Uwaga: pełne narzędzia parsowania Excela i wydobywania metadanych są dostępne w HotXLS VCL Component