HotXLS ile Delphi'de Excel Pivot Tablo Kurma ve Yenileme

HotXLS, Delphi ve C++Builder içinden makinede hiç Excel kurulumu ve COM otomasyonu olmadan yerel XLSX pivot tabloları kurar ve yeniler. Bir kaynak aralığıyla AddPivotTable çağırır, alanları satır, sütun, sayfa ve veri eksenlerine bırakır, üzerine hesaplanmış öğeler ya da toplama oranı görünümü eklersiniz; bileşen de Excel tarafından canlı ve yenilenebilir bir pivot olarak açılan pivotCacheDefinition ile pivotTableDefinition parçalarını yazar

Bunu zahmete değer kılan senaryo bir raporlama sunucusudur. Her gece yüzlerce çalışma kitabı üretirsiniz, her biri tek bir hesabı özetleyen bir pivot taşır ve gelecek ay sayılar değişir, her dosyanın yeni kaynak satırlarını yansıtması gerekir. Bir Windows hizmetinden Excel sürmek kırılgandır ve sunucu kullanımı için lisanslı değildir; pivot XML dosyasını elle yazmak ise hiç bitmeyen bir belirtim arkeolojisi projesidir. HotXLS bu iki çıkmazın arasında durur: OOXML pivot parçaları üzerinde türlenmiş bir nesne modeli, dolayısıyla hücreleri dolduran aynı Pascal kodu pivotu da bildirir ve önbelleğini aynı süreçte yeniden kurar

Excel olmadan Delphi içinde nasıl pivot tablo oluşturursunuz?

Tek bir çağrıyla oluşturur, sonra alanları eksenlere yerleştirirsiniz. AddPivotTable; kaynak aralığını A1 gösteriminde, tablonun çapalanacağı hedef hücreyi ve bir adı alır; aralığı ayrıştırır, veri türünü çıkarmak için her sütunu tarar, bir pivot önbelleği kurar (ya da aynı aralığa bağlı olan birini yeniden kullanır) ve alanlarının hepsi eksen dışı başlayan bir TXLSPivotTable döndürür. Oradan sonra AddRowField, AddColumnField, AddPageField ve AddDataFieldByName kolaylık yöntemleri düzeni bağlar ve her veri alanı, TXLSPivotAggregation numaralandırmasındaki on bir toplama işleminden 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];                 // rapor sayfası (XLSX motorunda 1 tabanlı)

    // Kaynak: 'Data' sayfasındaki A1:E500; pivotu 3. satır, 1. sütuna çapala.
    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 ile tablo neden iki ayrı parçadır

Bir pivot tablo aslında birbirine başvuran iki üründür ve bu ayrımı anlamak, geri kalanı karıştırmamayı sağlar. pivotCacheDefinition veri anlık görüntüsüdür: kaynak aralığını gösteren bir worksheetSource ve her sütun için o sütunun ayrık değerlerini (sharedItems) artı türetilmiş sınırları tutan birer cacheField. pivotTableDefinition ise görünümdür: hangi önbellek alanının hangi eksende oturduğu, veri alanları ile toplama işlemleri, düzen anahtarları. Görünüm, tam olarak ECMA-376 Bölüm 1 §18.10 ile [MS-XLSX] belgelerinin ortaya koyduğu gibi, çalışma kitabının pivotCaches ilişkisi üzerinden cacheId ile önbelleğe bağlanır

Bu dolaylılık bürokrasi değildir, iki şey kazandırır. Tek bir önbellek birkaç tabloyu besleyebilir, dolayısıyla önbelleği bir kez yenilemek ondan çizilen her görünümü günceller. Ayrıca önbellek her sütunun değerlerini ham kılavuz yerine kayıt başına bir dizin tablosuyla ve yinelenmemiş olarak saklar; bir pivot kurmanın hücre kopyalamak değil kaynağı taramak anlamına gelmesinin nedeni budur. HotXLS bunu her iki motor için de aynı biçimde modeller, dolayısıyla yukarıdaki kod klasik .xls pivotlarının ardındaki ikili SX kayıt düzeni yazısında belgelenen klasik yolla neredeyse aynıdır. Kaynağınız başka bir sayfada yaşıyorsa ya da ona bir adla erişiyorsanız, aralık öneki ile tanımlanmış adlar ve sayfalar arası başvurular her zamanki A1 kurallarını izler; boşluk içeren adlar için tırnaklı sayfa adları da kullanılabilir

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 tek bir alanın içinde yaşar ve o alanın kendi öğelerini ada göre birleştirir; örneğin bir Region alanının içinde North artı South değerine eşit sentetik bir CoreMarkets satırı tanımlayabilirsiniz. HotXLS bunu TXLSPivotField.AddCalculatedItem olarak açığa çıkarır ve o alanın <calculatedItems> düğümü altına bir <calculatedItem> yazar. Hesaplanmış bir alan ise farklıdır: Revenue ile Cost değerlerinden türeyen Margin gibi, başka sütunlardan türetilmiş yeni bir değerdir ve TXLSPivotCacheField.Formula üzerinden bir önbellek alanında formül olarak taşınır. TXLSPivotTable.AddCalculatedMember ile eklenen hesaplanmış bir üye ise, bir ölçü (data üye türü) ya da bir boyut üyesi olarak davranabilen tablo düzeyinde özel bir üyedir ve daha çok OLAP biçimli pivotlar için ilgi çekicidir

var
  Region: TXLSPivotField;
  Member: TXLSPivotCalculatedMember;
begin
  // Hesaplanmış bir ÖĞE, tek bir alanın öğelerini ada göre birleştirir.
  Region := Pivot.AddRowField('Region');
  Region.AddCalculatedItem('CoreMarkets', '=North+South');

  // Hesaplanmış bir ÜYE tablo düzeyinde bildirilir. 'data' üye
  // türü onu bir ölçü yapar; boş tür ise bir boyut üyesidir.
  Member := Pivot.AddCalculatedMember('AvgTicket', '=Revenue/Units');
  Member.MemberType := 'data';
end;

Üçü için de geçerli dürüst bir sınır var. HotXLS formül metnini tanım XML dosyasına yazar; onu değerlendirmez. Hesaplanmış öğeyi, alanı ya da üyeyi Excel dosyayı açtığında hesaplar; tıpkı kılavuzdaki her toplamı hesapladığı gibi. HotXLS sonuçları değil talimatları yazar, dolayısıyla verdiğiniz formüllerin Excel kendi lehçesinde geçerli pivot formülleri olması ve alan ile öğe adlarına Excel gibi başvurması gerekir

Değerleri toplama oranı olarak nasıl gösterirsiniz?

Sayıları kendiniz dönüştürmek yerine veri alanında değer görüntüleme kipini ayarlarsınız. TXLSPivotDataField.ShowDataAs, OOXML içindeki ST_ShowDataAs değerlerini yansıtan TXLSPivotShowDataAs numaralandırmasını alır: xlpsdaNormal, xlpsdaDifference, xlpsdaPercent, xlpsdaPercentDiff, xlpsdaRunTotal, xlpsdaPercentOfRow, xlpsdaPercentOfCol, xlpsdaPercentOfTotal ve xlpsdaIndex. Sık kullanılan bir numara, aynı kaynak sütununu veri eksenine iki kez yerleştirmektir; bir kez ham toplam, bir kez genel toplamdaki pay olarak, böylece rapor hem sayıyı hem 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;

  // Öğeye göreli kipler karşılaştırılacak bir taban ister. 'Quarter'
  // üzerinde birikimli toplam (önbellek alan dizini 3) şöyle olurdu:
  //   Share.ShowDataAs := xlpsdaRunTotal;
  //   Share.BaseField  := 3;         // dönüşümün üzerinde çalıştığı önbellek alan dizini
  //   Share.BaseItem   := $7FFD;     // $7FFD = "(All)"
end;

xlpsdaPercentOfTotal genel toplama göreli olduğu için tabana ihtiyaç duymaz, ama öğeye göreli kipler duyar. xlpsdaDifference, xlpsdaPercentDiff ve xlpsdaRunTotal; BaseField (karşılaştırmanın üzerinde çalıştığı önbellek alan dizini) ve öğeye çapalı biçimler için bir BaseItem dizini ister; burada $7FFD (All) nöbetçisini temsil eder. HotXLS bunları <dataField showDataAs="percentOfTotal" baseField="N" baseItem="M"/> öznitelikleri olarak yazar ve hesaplanmış formüllerde olduğu gibi aritmetiği Excel tarafına bırakır

Tarih ile sayı gruplama ve sayfa alanı süzgeçleri

Gruplama, pivot alanında değil önbellek alanında yapılandırılır, çünkü kaynak alanının nasıl kovalara ayrılacağını değiştirir. Cache.FindFieldByName ile dönen önbellek alanında HasGroup := True ayarlayın, sonra ya sayısal aralık düğmelerini (GroupStartNum, GroupEndNum, GroupInterval) ya da tarih hiyerarşisi bayraklarını (GroupMonths, GroupQuarters, GroupYears ile birlikte GroupByDate ve bir GroupStartDate / GroupEndDate aralığı) seçin. HotXLS eşleşen <fieldGroup><rangePr groupBy="months"/> öğesini yazar, böylece aya ya da 1000 genişliğinde sayısal bir banda göre gruplanmış bir alan, Excel onu nasıl gruplayacaksa öyle açılır

Sayfa alanları, tablonun üstündeki süzgeç açılır listeleridir. AddPageField bir alanı sayfa eksenine yerleştirir ve PageItemIndex tek bir önbellek öğesini önceden seçer; varsayılanı xlPageItemAll değeridir ($7FFD, yani (All)). Bir okuyucunun aynı anda birkaç öğe işaretlemesine izin vermek için MultipleItemSelectionAllowed := True ayarlayın; HotXLS bunu <pivotField multipleItemSelectionAllowed="1"/> olarak yazar. Elle seçimin ötesindeki ölçütler için her alan, OOXML ST_FilterType ailelerini kapsayan türlenmiş bir Filters koleksiyonu taşır — sayı, yüzde, toplam, başlık, değer ve tarih süzgeçleri — ve her girdi bir süzgeç türünü karşılaştırma değerleriyle eşler

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

TXLSXWorkbook üzerindeki RefreshPivotCache, kaynak aralığını yeniden tarar ve önbelleği yerinde yeniden kurar ki bir toplu iş hattının alttaki satırları düzenledikten sonra ihtiyaç duyduğu şey budur. Önbellek kimliğini verin; yöntem kaynak aralığındaki her hücreyi yeniden okur (ilk satırda başlık, ikinciden itibaren veri), değerleri yeniden yinelemeden arındırıp tür başına sınırları yeniden türeterek her alanın paylaşılan öğe alanını yeniden kurar ve kayıt başına öğe dizinlerini yeniden yazar. Başarıda 1, önbellek kimliği bilinmediğinde ya da kaynak sayfa eksik olduğunda -1 döndürür

var
  Rc: Integer;
begin
  // ...pivot kurulduğundan beri kaynak veri değişti...
  Sheet := Book.Sheets[1];
  Sheet.Cells[2, 5].Value := 128000;             // düzeltilmiş bir Revenue rakamı

  // Önbelleğin paylaşılan öğelerini ve kayıt dizinlerini kaynaktan yeniden kur.
  Rc := Book.RefreshPivotCache(Pivot.CacheId);   // 1 = yenilendi, -1 = önbellek/kaynak yok
  if Rc = 1 then
    Book.SaveAs('sales-pivot-refreshed.xlsx');
end;

Buradaki anlamsal sınırı açıkça söylemekte yarar var. Bu yöntem var olmadan önce HotXLS refreshOnLoad="1" bayrağına dayanıyor ve bütün yenileme işini bir sonraki açılışta Excel tarafına bırakıyordu ki bu, dosyayı bir insan açacaksa sorunsuzdur ama doğru veriyi devretmek zorunda olan başsız bir hatta yaramaz. RefreshPivotCache, kaydedilen önbelleği süreç içinde güncel kılar ve tablolar önbelleğe kimlikle bağlandığı için, o önbellekten çizilen her pivot güncellemeyi görür. Yapmadığı şey, görünen kılavuzu yerleştirmek ya da toplamaktır — dosya açıldığında işlenmiş tabloyu yenilenmiş önbellekten yine Excel yeniden hesaplar

HotXLS neyi yazar, Excel neyi hesaplar

İş bölümünü göz önünde tutun, hiçbir şey sizi şaşırtmasın. HotXLS bir tanım yazıcısıdır: pivotCacheDefinition parçasını, önbellek kayıtlarını ve eksenler, toplama işlemleri, hesaplanmış öğeler ile üyeler, değer görünümleri, gruplama ve süzgeçlerle tam donanımlı pivotTableDefinition parçasını üretir. Excel hesap makinesidir: açılışta grup kovalarını 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 Excel değerleridir ve HotXLS yazdığı talimatlardan üretilir; her formülün ve taban dizininin işleme anında denetlenmek yerine yazma anında doğru olması gerekmesinin nedeni budur

Bu özelliğin çevresinde tasarım yapmadan önce iki sınırı bilmekte yarar var. AddPivotTable, isteğe bağlı bir sayfa öneki taşıyan dikdörtgen bir A1 aralığını ayrıştırır; adlandırılmış aralık ve dış çalışma kitabı kaynakları kurucunun çözdüğü şeyin dışındadır, ancak Excel eliyle yazılmış bir dosyadan okunan önbellekler gidiş dönüşte adlandırılmış aralık kaynağını korur. Ayrıca HotXLS kütüphanesinin diskten okuduğu Excel yapımı pivotlar kaydederken bayt bayt yeniden yazılır, dolayısıyla burada anlatılan türlenmiş düzenlemeler kodda kurduğunuz pivotlara temiz uygulanırken var olan pivotlar kayıpsız kalır. Bir raporun girdi tarafı için — pivotun özetlediği doğrulanmış hücreler ve süzülmüş tablolar — bkz. veri doğrulama, Otomatik Süzgeç ve yapılandırılmış tablolar

Burada gösterilen pivot modeli, Delphi ve C++Builder için standart HotXLS Delphi Excel Bileşeni kapsamındadır; bileşen hem XLSX türlenmiş pivot parçalarını hem klasik BIFF8 kayıtlarını aynı nesne modelinden okur ve yazar