Извличане на обобщена информация за документа от Excel файлове в Delphi

Помолете даден конвейер (pipeline) да маршрутизира десет хиляди електронни таблици по автор, компания или дата на последна промяна и най-лошото, което може да направи, е да отвори напълно всяка работна книга (workbook). Отговорите се намират в свойствата на документа на файла, това което светът на Office нарича Обобщена информация за документа (Document Summary Information): слоят с метаданни, който Windows Search индексира, по който SharePoint сортира файловете и който Excel показва в своя диалог Свойства. Този слой е най-много килобайти и се намира на добре документирано място и в двата формата на Excel. Номерът е да го достигнете от Delphi, без да плащате за милиона клетки, от които не се нуждаете

Има три реални маршрута и те се различават по-малко в това, което връщат, отколкото в това, което изискват от машината, която ги изпълнява. COM автоматизацията управлява самия Excel и чете всичко на десктоп цени. Форматът .xls съхранява своите свойства в OLE потоци (property-set streams), които Windows ще парсира вместо вас. Форматът .xlsx ги съхранява в две малки XML части вътре в zip, които Delphi RTL може да отвори самостоятелно. По-долу следва работещ код за всеки от тях, като разходите са посочени ясно

Маршрут 1: COM автоматизацията чете всичко на десктоп цени

Автоматизацията е единственият маршрут с пълно покритие чрез един обектен модел: стандартният обобщаващ набор, разширеният набор с Company и Manager и потребителски дефинирани (custom) свойства, всички достъпни чрез BuiltinDocumentProperties и CustomDocumentProperties. Всичко пристига като OleVariant и API-то има един навик, който си струва да знаете, преди да ви ухапе: вградено свойство, което никога не е било присвоено, не се връща празно, то повдига EOleException в момента, в който докоснете Value. Помощникът (helper) по-долу третира това като "не е зададено", а не като неуспех

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;

Сега сметката. Excel трябва да бъде инсталиран на всяка машина, на която се изпълнява този код, което само по себе си изключва повечето сървъри, а политиката за поддръжка на Microsoft изрично гласи, че Office не е проектиран, нито лицензиран за сървърна автоматизация без надзор (unattended). CreateOleObject стартира пълен EXCEL.EXE, а Workbooks.Open парсира цялата работна книга, така че очаквайте приблизително две до четири секунди на файл, преди да се върне първото свойство. И try..finally около Quit не е декорация: изключение, което избяга между CreateOleObject и Quit, оставя сирак EXCEL.EXE, държащ заключване върху файла, невидимо докато следващото изпълнение не се провали срещу него. Повторното използване на един екземпляр (instance) на Excel в партида (batch) амортизира разходите за стартиране, но концентрира риска, защото един заблуден диалог на скрития десктоп спира всеки файл, нареден на опашка зад него

Маршрут 2: .xls съхранява свойствата в OLE property-set потоци

Работната книга BIFF8 е OLE съставен файл (compound file), миниатюрна файлова система от хранилища и потоци. Данните за клетките живеят в потока Workbook; метаданните живеят до тях в два потока за набори от свойства (property-set streams), чиито имена започват с контролния символ #5: \005SummaryInformation за класическите полета и \005DocumentSummaryInformation за разширените и персонализираните. Вътре във всеки седи двоичен набор от свойства в оформлението MS-OLEPS, с секции с ключове (keyed) по идентификатор на формат (FMTID) и свойства с ключове по цяло число за ID на свойство. Обобщаващата секция (summary) е FMTID {F29F85E0-4FF9-1068-AB91-08002B27B3D9}, където PIDSI_TITLE е $02, а PIDSI_AUTHOR е $04; Company ($0F) и Manager ($0E) живеят в секцията за обобщение на документа (document-summary), а персонализираните свойства във втора секция зад речник с имена

Добрата новина е, че в Windows никога не парсирате тези байтове сами. Структурираното съхранение (Structured storage) излага потоците чрез IPropertySetStorage и следното се компилира както е показано срещу стандартните 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));

Честна дума за това какво крие фрагментът. Низовете могат да пристигнат като VT_LPWSTR или като VT_LPSTR, и в ANSI случая байтовете са кодирани в собствената кодова страница на набора от свойства, която сама по себе си се съхранява като свойство 1 на секцията, така че преобразуването (cast) по-горе е точно само когато тази кодова страница съвпада със системната. Времевите марки (timestamps) се връщат като VT_FILETIME в UTC. Персонализираните свойства означават отваряне на дефинираната от потребителя секция, FMTID {D5CDD505-2E9C-101B-9397-08002B2CF9AE}, и обхождане на нейния речник с имена. IPropertyStorage абсорбира всичко това в Windows; написването на собствен MS-OLEPS парсер за среда без структурирано съхранение е истински проект, а не работа за един следобед

Маршрут 3: .xlsx запазва docProps като XML вътре в zip

Това е маршрутът, от който повечето конвейери всъщност се нуждаят, тъй като новите файлове са .xlsx от близо две десетилетия. Работната книга на OOXML е zip пакет и нейните свойства са разделени в малки части според предназначението: docProps/core.xml съдържа полетата на Dublin Core, dc:title, dc:creator, cp:lastModifiedBy, плюс dcterms:created и dcterms:modified като W3CDTF времеви марки в UTC, докато docProps/app.xml държи полета на ниво приложение като Company и AppVersion, а docProps/custom.xml съдържа персонализирани свойства. Тъй като централната директория на zip-а локализира всяка част директно, прочитането им струва няколко килобайта, без значение колко голяма е работната книга. TZipFile и IXMLDocument, и двете в доставения RTL, вършат цялата работа

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;

Два детайла поддържат това стабилно в продукция. Първо, частите са незадължителни: минимален пакет без никакъв docProps е напълно валиден според ECMA-376, поради което кодът проверява с IndexOf, вместо да предполага. Второ, съпоставяйте елементите по локално име и URI на пространство от имена (namespace), както прави FindNode по-горе, никога по буквален префикс; dc: и cp: са конвенции на писателя на Excel, а файлове, създадени от други генератори, са свободни да избират различни префикси. Една бележка за средата: доставчикът по подразбиране на IXMLDocument е MSXML, така че конзолно приложение или работна нишка трябва да извика CoInitialize преди LoadXMLData, в противен случай първият опит за парсиране умира с COM грешка

Листът с разходите и кога една библиотека побеждава и двата парсера

Измерен на обикновена машина на разработчик, COM маршрутът достига приблизително до две до четири секунди на файл, когато сесията за автоматизация се създава за всеки файл, почти цялото от което е стартиране на EXCEL.EXE плюс пълно парсиране на работната книга, и изисква инсталиран, лицензиран Excel където и да работи. Двата директни маршрута четат само контейнерите с метаданни, приключват за едноцифрени милисекунди на файл и не се нуждаят от нищо инсталирано отвъд това, което един изпълним файл (executable) на Delphi вече свързва. При дял от десет хиляди файла, това е разликата между по-голямата част от работния ден и по-малко от минута, без да е свързан въпрос с внедряването на Office

Уловката при директните маршрути е, че те са два. Конвейер, който приема и двата формата, поддържа два парсера с два разделени режима на повреда, кодови страници и PROPVARIANT типове от едната страна, пространства от имена (namespaces) и незадължителни части от другата, и нито един от тях не чете формата на другия. Това натоварване по поддръжката е случаят с нативна библиотека: HotXLS, библиотеката на losLab за електронни таблици на Object Pascal за Delphi и C++Builder в Windows, излага същите полета като обикновени свойства на работната книга (Title, Author, Company, Created и останалите), попълнени от Open както за .xls, така и за .xlsx, без инсталация на Excel и без нищо от водопроводната инфраструктура на контейнерите по-горе. Тя чете свойствата като част от пълно отваряне на работната книга, а не като сондаж само за метаданни, така че пасва на конвейери, които така или иначе продължават да докосват данните в клетките; пълната повърхност за свойства на двете фасади, включително страната за запис, е обхваната в нашата статия относно задаването на свойства на Excel документ с HotXLS

Забележка: Инструментите за пълно парсиране на Excel и извличане на метаданни са налични в компонента HotXLS VCL