HotXLS ile Delphi'de Excel Pivot Tabloları Oluşturma ve Yenileme

HotXLS, makinede Excel kurulumu ve COM otomasyonu olmadan Delphi ve C++Builder'da yerel XLSX pivot tabloları oluşturur ve yeniler. Bir kaynak aralığıyla AddPivotTable yöntemini çağırır, alanları satır, sütun, sayfa ve veri eksenlerine bırakır, hesaplanmış öğeler veya toplamın yüzdesi görünümü katmanları eklersiniz ve bileşen Excel'in canlı, yenilenebilir bir pivot olarak açtığı pivotCacheDefinition ve pivotTableDefinition kısımlarını yazar

Bunu zahmete değer kılan senaryo bir raporlama sunucusudur. Gece yarısı her biri bir hesabı özetleyen yüzlerce çalışma kitabı oluşturursunuz ve gelecek ay sayılar değiştiğinde her dosyanın yeni kaynak satırları yansıtması gerekir. Bir Windows servisinden Excel'i çalıştırmak kırılgan ve sunucu kullanımı için lisanssızdır; pivot XML'ini elle yazmak ise asla bitmeyen bir spec-arkeoloji projesidir. HotXLS bu iki çıkmazın arasında durur: OOXML pivot kısımları üzerinde türü belirtilmiş bir nesne modeli; bu sayede hücreleri dolduran aynı Pascal programı pivotu tanımlar ve önbelleğini aynı işlemde yeniden oluşturur

Excel olmadan Delphi'de pivot tabloyu nasıl oluşturursınız?

Tek bir çağrıyla bir tane oluşturur ve ardından alanları eksenlere yerleştirirsiniz. AddPivotTable, kaynak aralığını A1 gösteriminde, tablonun bağlandığı hedef hücreyi ve bir adı alır; aralığı ayrıştırır, veri türünü çıkarsamak için her sütunu tarar, bir pivot önbelleği oluşturur (veya aynı aralığa zaten bağlı olanı yeniden kullanır) ve tüm alanları eksen dışı başlayan bir TXLSPivotTable döndürür. Buradan, kolaylık sağlayan AddRowField, AddColumnField, AddPageField ve AddDataFieldByName yöntemleri düzeni kurar ve her veri alanı TXLSPivotAggregation listesinden on bir toplamadan birini alır (xlpaSum, xlpaCount, xlpaAverage, xlpaMax, xlpaMin, xlpaProduct, xlpaCountNums, xlpaStdDev, xlpaStdDevP, xlpaVar, xlpaVarP)

uses
  lxHandleX, lxPivot;

var
  Book : TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Pivot: TXLSPivotTable;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('sales.xlsx');
    Sheet := Book.Sheets[1];                 // report sheet (1-based in the XLSX engine)

    // Source A1:E500 on the 'Data' sheet; anchor the pivot at row 3, col 1.
    Pivot := Sheet.AddPivotTable('Data!$A$1:$E$500', 3, 1, 'SalesByRegion');
    if Pivot <> nil then
    begin
      Pivot.AddRowField('Region');
      Pivot.AddColumnField('Quarter');
      Pivot.AddPageField('Year');
      Pivot.AddDataFieldByName('Revenue', xlpaSum);
      Pivot.AddDataFieldByName('Units', xlpaAverage);
      Book.SaveAs('sales-pivot.xlsx');
    end;
  finally
    Book.Free;
  end;
end;

Önbellek ve tablo neden iki ayrı kısımdır?

Bir pivot tablo, aslında birbirine referans veren iki yapıttır ve bu ayrımı anlamak geri kalanını net tutar. pivotCacheDefinition veri anlık görüntüsüdür: kaynak aralığa işaret eden bir worksheetSource ve o sütunun benzersiz değerlerini (sharedItems) artı türetilmiş sınırları tutan sütun başına bir cacheField. pivotTableDefinition ise görünümdür: hangi önbellek alanının hangi eksende oturduğu, veri alanları ve bunların toplamaları, düzen geçişleri. Görünüm, çalışma kitabının pivotCaches ilişkisi aracılığıyla cacheId ile önbelleğe bağlanır; tam olarak ECMA-376 Bölüm 1 §18.10 ve [MS-XLSX] içinde düzenlendiği gibi

Bu dolaylılık bürokrasi değildir, iki şey kazandırır. Bir önbellek birkaç tabloyu destekleyebilir, bu nedenle önbelleği bir kez yenilemek ondan çizilen her görünümü günceller. Ve önbellek, her sütunun değerlerini ham ızgara yerine kayıt bazında bir dizin tablosuyla tekilleştirilmiş olarak saklar; bu nedenle bir pivot oluşturmak hücreleri kopyalamak değil, kaynağı taramak anlamına gelir. HotXLS bunu her iki motor için de aynı şekilde modeller, bu nedenle yukarıdaki kod, klasik .xls pivotlarının arkasındaki ikili SX-kayıt düzeni ile belgelenen klasik yolla neredeyse aynıdır. Kaynağınız başka bir sayfada yaşıyorsa veya ona bir adla başvuruyorsanız, aralık öneki ile tanımlanmış adlar ve sayfalar arası başvurular olağan A1 kurallarını izler, boşluk içeren adlar için tırnak içine alınmış sayfa adlarına izin verilir

Hesaplanmış öğeler hesaplanmış alanlar değildir

Bu üç terim üç farklı şeyi adlandırır ve bunları karıştırmak klasik pivot hatasıdır. Hesaplanmış bir öğe (calculated item) tek bir alanın içinde yaşar ve o alanın kendi öğelerini adlandırıyla birleştirir, böylece bir Bölge (Region) alanı içinde Kuzey artı Güney'e eşit sentetik bir CoreMarkets satırı tanımlayabilirsiniz; HotXLS bunu TXLSPivotField.AddCalculatedItem olarak sunar ve o alanın <calculatedItems> altına bir <calculatedItem> yazar. Hesaplanmış bir alan (calculated field) ise farklıdır: Gelir (Revenue) ve Maliyet'ten (Cost) türetilen Kar marjı (Margin) gibi diğer sütunlardan türetilen yeni bir değerdir ve TXLSPivotCacheField.Formula aracılığıyla bir önbellek alanında bir formül olarak taşınır. TXLSPivotTable.AddCalculatedMember ile eklenen hesaplanmış bir üye (calculated member), bir ölçü (üye türü data) veya bir boyut üyesi olarak hareket edebilen tablo düzeyinde özel bir üyedir, çoğunlukla OLAP şeklindeki pivotların ilgisini çeker

var
  Region: TXLSPivotField;
  Member: TXLSPivotCalculatedMember;
begin
  // A calculated ITEM combines items of a single field by name.
  Region := Pivot.AddRowField('Region');
  Region.AddCalculatedItem('CoreMarkets', '=North+South');

  // A calculated MEMBER is declared at the table level. Member type
  // 'data' marks it as a measure; an empty type is a dimension member.
  Member := Pivot.AddCalculatedMember('AvgTicket', '=Revenue/Units');
  Member.MemberType := 'data';
end;

Dürüst bir sınır üçü için de geçerlidir. HotXLS formül metnini tanım XML'ine yazar; onu değerlendirmez. Excel dosyayı açtığında, ızgaradaki her toplamı hesapladığı gibi hesaplanmış öğeyi, alanı veya üyeyi de hesaplar. HotXLS sonuçları değil talimatları yazar, bu nedenle sağladığınız formüllerin Excel'in kendi diyalektinde geçerli pivot formülleri olması, alan ve öğe adlarına Excel'in yaptığı gibi başvurması gerekir

Değerleri toplamın yüzdesi olarak nasıl gösterirsiniz?

Sayıları kendiniz dönüştürmek yerine veri alanındaki değer görüntüleme modunu ayarlarsınız. TXLSPivotDataField.ShowDataAs, OOXML ST_ShowDataAs değerlerini yansıtan TXLSPivotShowDataAs listesini alır: xlpsdaNormal, xlpsdaDifference, xlpsdaPercent, xlpsdaPercentDiff, xlpsdaRunTotal, xlpsdaPercentOfRow, xlpsdaPercentOfCol, xlpsdaPercentOfTotal ve xlpsdaIndex. Yaygın bir numara, aynı kaynak sütunu veri eksenine iki kez yerleştirmektir; biri ham toplam, diğeri ise genel toplamın payı olarak, böylece rapor hem sayıyı hem de ağırlığını gösterir

var
  Rev, Share: TXLSPivotDataField;
begin
  Rev := Pivot.AddDataFieldByName('Revenue', xlpaSum);

  Share := Pivot.AddDataFieldByName('Revenue', xlpaSum);
  Share.DisplayName := 'Share of total';
  Share.ShowDataAs  := xlpsdaPercentOfTotal;

  // Item-relative modes need a base to compare against. A running total
  // down 'Quarter' (cache-field index 3) would read:
  //   Share.ShowDataAs := xlpsdaRunTotal;
  //   Share.BaseField  := 3;         // cache-field index the transform runs over
  //   Share.BaseItem   := $7FFD;     // $7FFD = "(All)"
end;

xlpsdaPercentOfTotal genel toplama göre olduğundan bir tabana ihtiyaç duymaz, ancak öğeye göre olan modlar ihtiyaç duyar. xlpsdaDifference, xlpsdaPercentDiff ve xlpsdaRunTotal modları BaseField (karşılaştırmanın çalıştığı önbellek alanı dizini) og öğeye bağlı formlar için, $7FFD değerinin (Tümü) sentinelini temsil ettiği bir BaseItem dizini gerektirir. HotXLS bunları <dataField showDataAs="percentOfTotal" baseField="N" baseItem="M"/> öznitelikleri olarak yazar og hesaplanmış formüllerde olduğu gibi aritmetiği Excel'e bırakır

Tarih ve sayıları gruplama ve sayfa alanı filtreleri

Gruplama pivot alanında değil, önbellek alanında yapılandırılır, çünkü kaynak etki alanının nasıl bölümleneceğini değiştirir. Cache.FindFieldByName tarafından döndürülen önbellek alanında HasGroup := True ayarlayın, ardından sayısal aralık düğmelerini (GroupStartNum, GroupEndNum, GroupInterval) veya tarih hiyerarşisi bayraklarını (GroupByDate ile birlikte GroupMonths, GroupQuarters, GroupYears ve bir GroupStartDate / GroupEndDate aralığı) seçin. HotXLS eşleşen <fieldGroup><rangePr groupBy="months"/> öğesini yazar, böylece ay veya 1000 genişliğindeki sayısal bantla gruplanmış bir alan Excel'in onu gruplayacağı şekilde açılır

Sayfa alanları (page fields), tablonun üzerindeki filtre açılır listeleridir. AddPageField bir alanı sayfa eksenine yerleştirir ve PageItemIndex tek bir önbellek öğesini önceden seçer, varsayılan olarak xlPageItemAll ($7FFD, yani (Tümü)) değerini alır. Okuyucunun aynı anda birkaç öğeyi seçmesine izin vermek için MultipleItemSelectionAllowed := True ayarlayın, HotXLS bunu <pivotField multipleItemSelectionAllowed="1"/> olarak yazar. Manuel seçimin ötesindeki kriterler için, her alan ST_FilterType ailelerini — sayı, yüzde, toplam, başlık, değer ve tarih filtreleri — kapsayan yazılmış bir Filters koleksiyonu taşır; her girdi bir filtre türünü karşılaştırma değerleriyle eşleştirir

Kaynak değiştiğinde pivot önbelleğini nasıl yenilersiniz?

TXLSXWorkbook üzerindeki RefreshPivotCache, kaynak aralığı yeniden tarar ve önbelleği yerinde yeniden oluşturur; bu, toplu bir işlem hattının alttaki satırları düzenledikten sonra ihtiyaç duyduğu şeydir. Önbellek kimliğini (cache id) geçirin; yöntem kaynak aralıktaki her hücreyi (ilk satırdaki başlık, ikincisinden itibaren veriler) yeniden okur, değerleri yeniden tekilleştirerek ve tür bazında sınırları yeniden türeterek her alanın paylaşılan öğe etki alanını yeniden oluşturur ve kayıt başına öğe dizinlerini yeniden yazar. Başarı durumunda 1, önbellek kimliği bilinmiyorsa veya kaynak sayfa eksikse -1 döndürür

var
  Rc: Integer;
begin
  // ...the source data has changed since the pivot was built...
  Sheet := Book.Sheets[1];
  Sheet.Cells[2, 5].Value := 128000;             // a corrected Revenue figure

  // Rebuild the cache's shared items and record indices from the source.
  Rc := Book.RefreshPivotCache(Pivot.CacheId);   // 1 = refreshed, -1 = cache/source missing
  if Rc = 1 then
    Book.SaveAs('sales-pivot-refreshed.xlsx');
end;

Buradaki anlamsal sınırın açıkça belirtilmesinde fayda vardır. Bu yöntem var olmadan önce HotXLS, refreshOnLoad="1" bayrağına güveniyor ve tüm yenileme işini bir sonraki açılışta Excel'e bırakıyordu; bu, bir insan dosyayı açacağında iyidir ancak doğru verileri teslim etmek zorunda olan arka planda çalışan bir işlem hattında kullanışsızdır. RefreshPivotCache, kaydedilen önbelleği işlem içinde güncel kılar ve tablolar önbelleğe kimlikle bağlandığından, o önbellekten çizilen her pivot güncellemeyi görür. Yapmadığı şey ise görünür ızgarayı düzenlemek veya toplamaktır — Excel, dosya açıldığında yenilenen önbellekten işlenen tabloyu yine de yeniden hesaplar

HotXLS'in yazdığı ve Excel'in hesapladığı şeyler

İş bölümünü göz önünde bulundurun; hiçbir şey sizi şaşırtmayacaktır. HotXLS bir tanım yazarıdır: eksenler, toplamalar, hesaplanmış öğeler ve üyeler, değer gösterimleri, gruplama ve filtrelerle birlikte pivotCacheDefinition, önbellek kayıtları ve pivotTableDefinition kısımlarını üretir. Excel ise hesaplayıcıdır: açılışta grup bölmelerini somutlaştırır, hesaplanmış formülleri değerlendirir, showDataAs dönüşümlerini uygular ve gövdeyi toplar. Kullanıcının gördüğü değerler, HotXLS'in yazdığı talimatlardan üretilen Excel değerleridir; bu nedenle her formül ve temel dizin işleme zamanında kontrol edilmek yerine yazma zamanında doğru olmalıdır

Bu özelliğin etrafında tasarım yapmadan önce iki sınırı bilmekte fayda vardır

AddPivotTable, isteğe bağlı bir sayfa önekiyle dikdörtgen bir A1 aralığını ayrıştırır; tanımlanmış aralık ve harici çalışma kitabı kaynakları, oluşturucunun çözdüğü şeylerin dışındadır, ancak diskteki bir Excel dosyasından okunan önbellekler karşılıklı aktarımda tanımlanmış aralık kaynaklarını korur. Ve HotXLS'in diskten okuduğu Excel yapımı pivotlar kaydetmede bayt bayt oynatılır, bu nedenle burada açıklanan yazılı düzenlemeler kodda oluşturduğunuz pivotlara temiz bir şekilde uygulanırken, mevcut pivotlar kayıpsız kalır. Pivotun özetlediği bir raporun giriş tarafı — doğrulanan hücreler ve filtrelenen tablolar — için veri doğrulama, Otomatik Filtre ve yapılandırılmış tablolar makalesine bakın

Burada gösterilen pivot modeli, hem XLSX yazılmış pivot kısımlarını hem de klasik BIFF8 kayıtlarını aynı nesne modelinden okuyan ve yazan Delphi ve C++Builder için standart HotXLS Delphi Excel Bileşeni'nin bir parçasıdır