Membaca Properti Dokumen Excel di Delphi: Tiga Rute

Mintalah sebuah pipeline mengarahkan sepuluh ribu lembar kerja berdasarkan penulis, perusahaan, atau tanggal terakhir diubah, dan hal terburuk yang bisa dilakukannya adalah membuka setiap workbook secara penuh. Jawabannya menumpang di properti dokumen berkas itu, yang di dunia Office disebut Document Summary Information: lapisan metadata yang diindeks Windows Search, dipakai SharePoint untuk mengarsip, dan ditampilkan Excel di dialog Properties-nya. Lapisan itu paling banter berukuran beberapa kilobita, dan ia berada di tempat yang terdokumentasi baik pada kedua format Excel. Kuncinya adalah menjangkaunya dari Delphi tanpa membayar sejuta sel yang tidak Anda butuhkan

Ada tiga rute yang sungguhan, dan perbedaannya lebih sedikit pada apa yang mereka kembalikan daripada pada apa yang mereka tuntut dari mesin yang menjalankannya. Otomasi COM mengendalikan Excel itu sendiri dan membaca segalanya, dengan harga desktop. Format .xls menyimpan propertinya dalam stream property-set OLE yang akan diurai Windows untuk Anda. Format .xlsx menyimpannya dalam dua bagian XML kecil di dalam zip yang dapat dibuka sendiri oleh RTL Delphi. Kode yang berfungsi untuk masing-masing menyusul, dengan biayanya disebutkan terus terang

Rute 1: otomasi COM membaca segalanya, dengan harga desktop

Otomasi adalah satu-satunya rute dengan cakupan total lewat satu object model: himpunan summary standar, himpunan diperluas dengan Company dan Manager, serta properti kustom yang didefinisikan pengguna, semuanya dapat dijangkau lewat BuiltinDocumentProperties dan CustomDocumentProperties. Semuanya tiba sebagai OleVariant, dan API ini punya satu kebiasaan yang perlu diketahui sebelum menggigit: properti bawaan yang tak pernah diberi nilai tidak kembali dalam keadaan kosong, ia melemparkan EOleException begitu Anda menyentuh Value. Helper di bawah memperlakukan hal itu sebagai "tidak disetel" alih-alih sebagai kegagalan

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 := '';   // properti ada tetapi tak pernah diberi nilai
    end;
  end;

begin
  Excel := CreateOleObject('Excel.Application');
  try
    Excel.DisplayAlerts := False;
    Book := Excel.Workbooks.Open(FileName, 0, True);   // hanya-baca
    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;   // capai ini di setiap jalur, atau EXCEL.EXE tertinggal
    Excel := Unassigned;
  end;
end;

Sekarang tagihannya. Excel harus terpasang di setiap mesin yang menjalankan kode ini, yang dengan sendirinya menyingkirkan sebagian besar server, dan kebijakan dukungan Microsoft menyatakan secara eksplisit bahwa Office tidak dirancang maupun dilisensikan untuk otomasi sisi server tanpa pengawasan. CreateOleObject meluncurkan EXCEL.EXE utuh dan Workbooks.Open mengurai seluruh workbook, jadi perkirakan kira-kira dua sampai empat detik per berkas sebelum properti pertama kembali. Dan try..finally di sekitar Quit bukan hiasan: eksepsi yang lolos antara CreateOleObject dan Quit meninggalkan EXCEL.EXE yatim yang memegang kunci pada berkas itu, tak terlihat sampai jalannya berikutnya gagal karenanya. Memakai ulang satu instance Excel untuk satu batch mengamortisasi biaya awal tetapi memusatkan risikonya, karena satu dialog nyasar di desktop tersembunyi akan memacetkan setiap berkas yang mengantre di belakangnya

Rute 2: .xls menyimpan properti dalam stream property-set OLE

Workbook BIFF8 adalah OLE compound file, sebuah sistem berkas mini berisi storage dan stream. Data selnya berada di stream Workbook; metadatanya berada di sebelahnya dalam dua stream property-set yang namanya diawali karakter kendali #5: \005SummaryInformation untuk field klasik dan \005DocumentSummaryInformation untuk yang diperluas dan yang kustom. Di dalam masing-masing terdapat property set biner dengan tata letak MS-OLEPS, dengan section yang dikunci oleh format identifier (FMTID) dan properti yang dikunci oleh property ID berupa bilangan bulat. Section summary adalah FMTID {F29F85E0-4FF9-1068-AB91-08002B27B3D9}, di mana PIDSI_TITLE adalah $02 dan PIDSI_AUTHOR adalah $04; Company ($0F) dan Manager ($0E) berada di section document-summary, dan properti kustom berada di section kedua di balik sebuah kamus nama

Kabar baiknya, di Windows Anda tidak pernah mengurai byte-byte itu sendiri. Structured storage memaparkan stream tersebut lewat IPropertySetStorage, dan kode berikut dapat dikompilasi apa adanya terhadap unit RTL bawaan

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

// pemakaian: Writeln('Author: ', ReadXlsSummaryString('ledger.xls', PIDSI_AUTHOR));

Satu catatan jujur tentang apa yang disembunyikan cuplikan itu. String bisa tiba sebagai VT_LPWSTR atau sebagai VT_LPSTR, dan dalam kasus ANSI byte-nya disandikan dalam code page milik property set itu sendiri, yang tersimpan sebagai properti 1 dari section-nya, sehingga cast di atas hanya tepat ketika code page tersebut cocok dengan code page sistem. Cap waktu kembali sebagai VT_FILETIME dalam UTC. Properti kustom berarti membuka section yang didefinisikan pengguna, FMTID {D5CDD505-2E9C-101B-9397-08002B2CF9AE}, lalu menelusuri kamus namanya. IPropertyStorage menyerap semua itu di Windows; menulis pengurai MS-OLEPS Anda sendiri untuk lingkungan tanpa structured storage adalah proyek sungguhan, bukan pekerjaan satu sore

Rute 3: .xlsx menyimpan docProps sebagai XML di dalam zip

Inilah rute yang sebenarnya paling dibutuhkan kebanyakan pipeline, karena berkas baru sudah berformat .xlsx selama hampir dua dasawarsa. Workbook OOXML adalah paket zip, dan propertinya dipecah ke bagian-bagian kecil menurut tujuannya: docProps/core.xml menyimpan field Dublin Core, dc:title, dc:creator, cp:lastModifiedBy, ditambah dcterms:created dan dcterms:modified sebagai cap waktu W3CDTF dalam UTC, sedangkan docProps/app.xml menyimpan field tingkat aplikasi seperti Company dan AppVersion, dan docProps/custom.xml menyimpan properti kustom. Karena central directory zip menemukan tiap bagian secara langsung, membacanya hanya memakan beberapa kilobita betapa pun besarnya workbook itu. TZipFile dan IXMLDocument, keduanya ada di RTL bawaan, mengerjakan seluruh pekerjaannya

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;

Dua detail menjaga kode ini tetap tangguh di produksi. Pertama, bagian-bagian itu opsional: paket minimal tanpa docProps sama sekali sepenuhnya valid menurut ECMA-376, itulah sebabnya kode ini menyelidiki dengan IndexOf alih-alih berandai-andai. Kedua, cocokkan elemen berdasarkan nama lokal dan URI namespace, seperti yang dilakukan FindNode di atas, jangan pernah berdasarkan prefiks harfiah; dc: dan cp: adalah konvensi penulis milik Excel, dan berkas yang dihasilkan generator lain bebas memilih prefiks berbeda. Satu catatan lingkungan: vendor IXMLDocument default adalah MSXML, jadi aplikasi konsol atau thread pekerja harus memanggil CoInitialize sebelum LoadXMLData, atau penguraian pertama akan mati dengan error COM

Lembar biaya, dan kapan sebuah library mengalahkan kedua pengurai itu

Diukur pada mesin pengembang biasa, rute COM mendarat di kisaran dua sampai empat detik per berkas ketika sesi otomasi dibuat per berkas, nyaris seluruhnya adalah waktu awal EXCEL.EXE ditambah penguraian workbook penuh, dan ia memerlukan Excel yang terpasang dan berlisensi di mana pun ia berjalan. Kedua rute langsung hanya membaca kontainer metadata, selesai dalam milidetik satu digit per berkas, dan tidak membutuhkan apa pun yang terpasang di luar apa yang sudah ditautkan sebuah executable Delphi. Pada sebuah share berisi sepuluh ribu berkas, itulah perbedaan antara hampir seharian kerja dan kurang dari satu menit, tanpa pertanyaan penyebaran Office yang menempel

Ganjalan pada rute langsung adalah jumlahnya ada dua. Pipeline yang menerima kedua format memelihara dua pengurai dengan dua mode kegagalan yang terpisah, code page dan tipe PROPVARIANT di satu sisi, namespace dan bagian opsional di sisi lain, dan tak satu pun bisa membaca format yang lain. Beban pemeliharaan itulah alasan memilih library native: HotXLS, library lembar kerja Object Pascal dari losLab untuk Delphi dan C++Builder di Windows, memaparkan field yang sama sebagai properti workbook biasa, Title, Author, Company, Created, dan seterusnya, yang diisi oleh Open untuk .xls maupun .xlsx, tanpa pemasangan Excel dan tanpa satu pun pipa kontainer di atas. Ia membaca properti sebagai bagian dari pembukaan workbook penuh alih-alih penyelidikan metadata saja, jadi ia cocok untuk pipeline yang toh akan menyentuh data selnya; permukaan properti lengkap pada kedua fasad, termasuk sisi penulisan, dibahas di artikel kami tentang menyetel properti dokumen Excel dengan HotXLS

Catatan: perkakas penguraian Excel penuh dan ekstraksi metadata tersedia dalam HotXLS Delphi VCL Component