Extrair Informações de Resumo de Documentos de Ficheiros Excel em Delphi

Pedir a um pipeline que organize dez mil folhas de cálculo por autor, empresa ou data da última modificação, e a pior coisa que ele pode fazer é abrir cada livro por completo. As respostas estão nas propriedades do documento do ficheiro, aquilo a que o mundo do Office chama Document Summary Information: a camada de metadados que o Windows Search indexa, pela qual o SharePoint organiza ficheiros, e que o Excel apresenta na sua caixa de diálogo Propriedades. Essa camada tem, no máximo, alguns kilobytes, e reside num local bem documentado em ambos os formatos do Excel. O truque está em aceder a ela a partir do Delphi sem pagar o custo do milhão de células de que não se precisa

Existem três vias reais, que diferem menos naquilo que devolvem do que nas exigências que colocam à máquina que as executa. A automação COM controla o próprio Excel e lê tudo, a preços de desktop. O formato .xls guarda as suas propriedades em streams de conjuntos de propriedades OLE que o Windows analisa por conta do programador. O formato .xlsx guarda-as em duas pequenas partes XML dentro de um zip que o RTL do Delphi consegue abrir sozinho. Segue-se código funcional para cada uma delas, com os custos indicados com clareza

Via 1: a automação COM lê tudo, a preços de desktop

A automação é a única via com cobertura total através de um único modelo de objetos: o conjunto de resumo padrão, o conjunto alargado com Company e Manager, e as propriedades personalizadas definidas pelo utilizador, todas acessíveis através de BuiltinDocumentProperties e CustomDocumentProperties. Tudo chega como OleVariant, e a API tem um comportamento que convém conhecer antes que morda: uma propriedade incorporada que nunca foi atribuída não é devolvida vazia, gera uma EOleException no momento em que se acede a Value. A função auxiliar abaixo trata isso como "não definido" em vez de como uma falha

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 := '';   // a propriedade existe mas nunca foi atribuída
    end;
  end;

begin
  Excel := CreateOleObject('Excel.Application');
  try
    Excel.DisplayAlerts := False;
    Book := Excel.Workbooks.Open(FileName, 0, True);   // só leitura
    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;   // alcançar isto em todos os caminhos, ou o EXCEL.EXE fica para trás
    Excel := Unassigned;
  end;
end;

Agora, a conta. O Excel tem de estar instalado em cada máquina onde este código é executado, o que por si só exclui a maioria dos servidores, e a política de suporte da Microsoft é explícita quanto ao facto de o Office não ter sido concebido nem licenciado para automação não assistida no lado do servidor. CreateOleObject lança um EXCEL.EXE completo e Workbooks.Open analisa o livro inteiro, pelo que convém contar com cerca de dois a quatro segundos por ficheiro antes de a primeira propriedade ser devolvida. E o try..finally à volta de Quit não é decoração: uma exceção que escape entre CreateOleObject e Quit deixa um EXCEL.EXE órfão a reter um bloqueio sobre o ficheiro, invisível até que a próxima execução falhe contra ele. Reutilizar uma única instância do Excel ao longo de um lote amortiza o custo de arranque mas concentra o risco, porque uma única caixa de diálogo perdida no ambiente de trabalho oculto bloqueia todos os ficheiros em fila atrás dela

Via 2: .xls guarda as propriedades em streams de conjuntos de propriedades OLE

Um livro BIFF8 é um ficheiro composto OLE, um sistema de ficheiros em miniatura de storages e streams. Os dados das células residem na stream Workbook; os metadados residem ao lado, em duas streams de conjuntos de propriedades cujos nomes começam pelo carácter de controlo #5: \005SummaryInformation para os campos clássicos e \005DocumentSummaryInformation para os campos alargados e personalizados. Dentro de cada uma reside um conjunto de propriedades binário no formato MS-OLEPS, com secções identificadas por um identificador de formato (FMTID) e propriedades identificadas por um ID de propriedade inteiro. A secção de resumo é o FMTID {F29F85E0-4FF9-1068-AB91-08002B27B3D9}, onde PIDSI_TITLE é $02 e PIDSI_AUTHOR é $04; Company ($0F) e Manager ($0E) residem na secção de resumo do documento, e as propriedades personalizadas numa segunda secção, atrás de um dicionário de nomes

A boa notícia é que, no Windows, nunca é preciso analisar esses bytes manualmente. O structured storage expõe as streams através de IPropertySetStorage, e o código seguinte compila tal como é apresentado, contra as unidades RTL de série

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

// utilização: Writeln('Author: ', ReadXlsSummaryString('ledger.xls', PIDSI_AUTHOR));

Uma palavra honesta sobre o que este excerto esconde. As strings podem chegar como VT_LPWSTR ou como VT_LPSTR, e no caso ANSI os bytes são codificados na code page própria do conjunto de propriedades, que é guardada como a propriedade 1 da secção, pelo que a conversão acima só é exata quando essa code page coincide com a do sistema. As datas e horas são devolvidas como VT_FILETIME em UTC. As propriedades personalizadas implicam abrir a secção definida pelo utilizador, FMTID {D5CDD505-2E9C-101B-9397-08002B2CF9AE}, e percorrer o seu dicionário de nomes. IPropertyStorage absorve tudo isso no Windows; escrever um analisador MS-OLEPS próprio para um ambiente sem structured storage é um projeto a sério, não um trabalho de uma tarde

Via 3: o .xlsx guarda o docProps como XML dentro do zip

Esta é a via de que a maioria dos pipelines realmente precisa, já que os ficheiros novos são .xlsx há quase duas décadas. Um livro OOXML é um pacote zip, e as suas propriedades estão divididas por várias partes pequenas, consoante a finalidade: docProps/core.xml contém os campos Dublin Core, dc:title, dc:creator, cp:lastModifiedBy, além de dcterms:created e dcterms:modified como datas W3CDTF em UTC, enquanto docProps/app.xml contém campos ao nível da aplicação, como Company e AppVersion, e docProps/custom.xml contém as propriedades personalizadas. Como o diretório central do zip localiza cada parte diretamente, a leitura custa apenas alguns kilobytes, independentemente da dimensão do livro. TZipFile e IXMLDocument, ambos incluídos no RTL de série, tratam de todo o trabalho

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;

Dois pormenores mantêm isto robusto em produção. Primeiro, as partes são opcionais: um pacote mínimo sem qualquer docProps é perfeitamente válido segundo a norma ECMA-376, razão pela qual o código sonda com IndexOf em vez de presumir a sua existência. Segundo, os elementos devem ser correspondidos pelo nome local e pelo URI do namespace, tal como FindNode faz acima, nunca pelo prefixo literal; dc: e cp: são convenções do escritor do Excel, e ficheiros produzidos por outros geradores são livres de escolher prefixos diferentes. Uma nota sobre o ambiente: o fornecedor predefinido de IXMLDocument é o MSXML, pelo que uma aplicação de consola ou uma worker thread tem de chamar CoInitialize antes de LoadXMLData, ou a primeira análise falha com um erro COM

A folha de custos, e quando uma biblioteca supera os dois parsers

Medida numa máquina de programador comum, a via COM fica-se por cerca de dois a quatro segundos por ficheiro quando a sessão de automação é criada por ficheiro, praticamente tudo isso a arrancar o EXCEL.EXE mais a análise completa do livro, e exige um Excel instalado e licenciado onde quer que seja executada. As duas vias diretas leem apenas os contentores de metadados, terminam em milissegundos de um só dígito por ficheiro, e não precisam de nada instalado além do que um executável Delphi já traz ligado. Numa partilha de dez mil ficheiros, essa é a diferença entre a maior parte de um dia de trabalho e menos de um minuto, sem qualquer questão de implantação do Office associada

O senão das vias diretas é que existem duas delas. Um pipeline que aceite ambos os formatos tem de manter dois parsers com dois modos de falha distintos, code pages e tipos PROPVARIANT de um lado, namespaces e partes opcionais do outro, e nenhum deles lê o formato do outro. Essa carga de manutenção é o argumento a favor de uma biblioteca nativa: o HotXLS, a biblioteca de folhas de cálculo em Object Pascal da losLab para Delphi e C++Builder no Windows, expõe os mesmos campos como simples propriedades do livro, Title, Author, Company, Created, entre outros, preenchidos por Open tanto para .xls como para .xlsx, sem necessidade de instalar o Excel e sem nenhuma da mecânica de contentores acima descrita. Lê as propriedades como parte de uma abertura completa do livro, em vez de uma sondagem apenas de metadados, pelo que se adequa a pipelines que acabam por aceder também aos dados das células; a superfície completa de propriedades em ambas as fachadas, incluindo o lado da escrita, é abordada no nosso artigo sobre a definição de propriedades de documento do Excel com o HotXLS

Nota: estão disponíveis ferramentas completas de análise do Excel e de extração de metadados no HotXLS Delphi VCL Component