Izdvajanje informacija sažetka dokumenta iz Excel datoteka u Delphiju

Prilikom obrade velikih serija Excel proračunskih tablica u automatiziranom cjevovodu, rijetko želite učitati cijeli dokument u memoriju samo kako biste shvatili o čemu se radi. Često su metapodaci ugrađeni unutar datoteke, kao što su autor, naslov, datum stvaranja i prilagođena svojstva, dovoljni za usmjeravanje, indeksiranje ili odbacivanje dokumenta. U svijetu Microsoft Officea, ti metapodaci poznati su kao informacije sažetka dokumenta (Document Summary Information)

Za izvorno izdvajanje ovih informacija u Delphiju bez oslanjanja na OLE automatizaciju (koja zahtijeva instaliran Excel na glavnom stroju) potrebno je izravno parsirati temeljnu strukturu datoteke. U ovom ćemo članku pogledati kako sažeci dokumenata funkcioniraju u Excel datotekama i kako ih učinkovito izdvojiti pomoću parsiranja sirovih tokova

Razumijevanje tokova Excel metapodataka

Povijesno gledano, starije Excel datoteke (.xls) pohranjene su u OLE Compound Document formatima, koji efektivno djeluju kao mini datotečni sustavi koji sadrže tokove i pohrane. Metapodaci su smješteni u dva specifična toka:

• SummaryInformation: Sadrži standardna svojstva kao što su naslov, predmet, autor, ključne riječi i broj revizije
• DocumentSummaryInformation: Sadrži proširena svojstva kao što su tvrtka, menadžer i prilagođena korisnička svojstva

Moderne Excel datoteke (.xlsx) koriste format Office Open XML (OOXML), koji je zipovana XML struktura. Metapodaci se ovdje nalaze u docProps/core.xml, docProps/app.xml i docProps/custom.xml. Robusna Delphi komponenta za parsiranje mora besprijekorno rukovati objema unutarnjim strukturama, a istovremeno izložiti jedinstveni API programeru

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;

Parsiranje OLE Compound dokumenata u Delphiju

Da biste pročitali SummaryInformation iz naslijeđene .xls datoteke bez alata trećih strana, morate parsirati OLE Structured Storage. Microsoft to izlaže kroz COM sučelje IPropertySetStorage. Ovdje je sirova Delphi implementacija koja izbjegava pokretanje Excela:

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

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;

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

Programsko izdvajanje pomoću HotXLS-a

Iako Windows COM API radi za .xls datoteke, on ne radi za moderne .xlsx datoteke (koje su ZIP arhive). Nadalje, korištenje COM API-ja na više platformi (npr. na Linuxu ili macOS-u putem FireMonkeyja) je nemoguće. Nedavna ažuriranja komponente HotXLS uvela su namjenske jedinice (npr. lxXlsSummary) za izoliranje i optimizaciju čitanja ovih tokova sažetaka u oba formata potpuno izvorno u Delphi kodu

Primjer za više platformi

Koristeći sučelja XlsReadDocumentSummaryInformation i XlsReadSummaryInformation, možete brzo preuzeti nizove metapodataka i iz .xls i iz .xlsx bez brige o temeljnoj arhitekturi datotečnog sustava

Zašto je namjensko izdvajanje sažetka važno

Primarna prednost ovog pristupa je izvedba i memorijska sigurnost. Izbjegavanjem instanciranja cjelokupnog DOM-a radne knjige (Document Object Model) i parsiranjem samo docProps/core.xml ili OLE tokova svojstava, otisak vaše aplikacije ostaje nevjerojatno mali. Ako indeksirate 10.000 Excel datoteka preko mrežnog dijeljenja, pokušaj potpunog parsiranja svake od njih iscrpit će vašu memoriju i trajati satima. Namjensko izdvajanje sažetka obavlja isti zadatak u sekundi

Nadalje, izvorno čitanje tokova osigurava da se vaša aplikacija može izvoditi kao pozadinska usluga ili na Linux poslužitelju bez grafičkog sučelja (headless) bez ikakvog pozivanja Excel.exe procesa, što je ključan zahtjev za moderne skalabilne arhitekture

Napomena: Sveobuhvatni alati za parsiranje Excela i izdvajanje metapodataka dostupni su u HotXLS VCL Component