Pivot Öğelerini Değere Göre Sıralamak: autoSortScope ve -2

HotXLS, PivotTable öğelerini Delphi'de TXLSPivotField üzerindeki iki özellikle değere göre sıralar: SortType artan ya da azalan seçer ve SortDataField, sıralamayı süren toplamları olan sıfır tabanlı veri alanını adlandırır. HotXLS Excel tarafından yazılmış bir XLSX açtığında o veri alanını autoSortScope öğesinden kurtarır; Excel orada Values alanını field="4294967294" olarak, yani -2'nin işaretsiz yazımı olarak saklar

Değere göre sıralama, her satış raporunun er ya da geç aldığı istektir: "bölgeleri büyükten küçüğe göster". Excel arayüzünde iki tıktır. Dosya biçiminde ise bir öznitelik, iç içe bir pivot alanı, rezerve bir alan indeksi ve seçilmiş bir veri alanı ordinalidir; bu parçalardan birini kaçıran bir okuyucu dosyayı yine şikâysiz açar. Yalnızca yanlış şeye göre sıralar

Excel değere göre sıralama ölçütünü nerede saklar?

Excel sıralama ölçütünü yalnızca autoSortScope'un içinde saklar; alanın üzerinde değil. <pivotField> üzerindeki sortType özniteliği (ECMA-376 Part 1 ST_FieldSortType değeri: manual, ascending ya da descending) hangi yöne sıralanacağını söyler. Neyin ölçütüne göre sıralanacağı ise bir çocuk <autoSortScope>'dadır; o da veri ekseni referansı bir veri alanı seçen bir <pivotArea>'yı sarar

<pivotField axis="axisRow" showAll="0" sortType="descending">
  <items count="5"> ... </items>
  <autoSortScope>
    <pivotArea dataOnly="0" outlineLevel="all" fieldPosition="0">
      <references count="1">
        <reference field="4294967294" count="1" selected="0">
          <x v="0"/>
        </reference>
      </references>
    </pivotArea>
  </autoSortScope>
</pivotField>

reference öğesi normalde bir pivot alanını indeksle gösterir. Alan indeksi -2, ECMA-376'nin PivotTable bölümünde Values sözde-alanı için rezervedir; iki ya da daha çok ölçüt bir eksende durduğunda Excel'in "Values" olarak gösterdiği alandır. Öznitelik işaretsiz 32 bitlik bir tamsayı olarak tiplidir; -2 bu yüzden hat üzerinden 4294967294 olarak gider. Onun altındaki <x v="0"/>, <dataFields>'in ilk girdisi olan veri alanı 0'ı seçer. <pivotField> üzerinde bir rankBy özniteliği de vardır ama karşılaştırdığımız dosyalarda Excel onu bırakıp yalnızca scope'a güvenmişti

Sıralama ölçütü neden -1 olarak döndü?

HotXLS okuyucusu sıralama ölçütünü başlangıçta kaybetti, çünkü 4294967294 işaretli 32 bitlik bir tamsayıya sığmaz. Öznitelik yardımcısı StrToIntDef'in ince bir sarmalayıcısıdır ve aralık dışı bir değer sessizce varsayılanı döndürür; veri ekseni testi hiç eşleşmedi, SortDataField -1'de kaldı ve alan, ölçütsüz olarak yalnızca sortType="descending"'i tuttu

Hata en kötü biçimde sessizdi. Ölçütsüz azalan bir alan yine sıralar; yalnızca öğe etiketine göre tersten. Excel'in toplama göre dizdiği bir rapor HotXLS'ten öğe sırası ters çevrilmiş olarak döndü. Hiçbir şey istisna fırlatmadı ve her hücredeki sayılar doğruydu. Yalnızca satır sırası yanlıştı ve yalnızca Excel'in yazdığı dosyalarda

lxPivotXml.pas içindeki düzeltme, ham öznitelik belirtecini herhangi bir tamsayı dönüşümünden önce dizge olarak karşılaştırır. rankBy zaten bir ölçüt ayarladıysa o kazanır; aksi hâlde field belirteci tam olarak '4294967294' olan bir referans, SortDataField'ı seçili x değerinden çözer. Değerlendirme tarafının değişikliğe ihtiyacı yoktu: karşılaştırıcı, satır ve sütun eksenlerinde üye başına toplamları çoktan nihai hâliyle hesaplıyor, azalan sırayı yönetiyor ve beraberlikleri kırıyordu. Veri ona basitçe hiç ulaşmıyordu

Genel ders, Delphi ile yazılmış her OOXML okuyucusuna uyar. ECMA-376, birkaç indeks özniteliğini xsd:unsignedInt olarak tipler ve aralığın tepesini bir nöbetçi (sentinel) olarak kullanır; varsayılan yedeği olan işaretli bir okuyucu, rezerve değeri "öznitelik eksik"e çevirir. Bir alan indeksinin Values anlamına gelebileceği her yerde belirteci karşılaştırın ya da Int64 ya da Cardinal'a ayrıştırın

Pivot öğelerini Delphi kodundan bir ölçüte göre sıralamak

Bir HotXLS pivot alanını değere göre sıralamak için SortType'ı xlpfstAscending ya da xlpfstDescending'e ayarlayın ve SortDataField'ı tablonun DataFields listesindeki ölçütün sıfır tabanlı indeksine ayarlayın. Varsayılan olan SortType = xlpfstManual ile SortDataField yok sayılır ve öğeler önbellek ya da programatik sıralarını korur

uses
  SysUtils, lxHandleX, lxPivot;

var
  Book: TXLSXWorkbook;
  Data, Report: TXLSXWorksheet;
  Pivot: TXLSPivotTable;
  Region: TXLSPivotField;
begin
  Book := TXLSXWorkbook.Create;
  try
    Data := Book.Sheets.Add('Data');
    Data.Cells[1, 1].Value := 'Region';
    Data.Cells[1, 2].Value := 'Product';
    Data.Cells[1, 3].Value := 'Units';
    // ... 2..41 satırlarını doldur ...
    Report := Book.Sheets.Add('Report');

    Pivot := Report.AddPivotTable('Data!A1:C41', 3, 1, 'UnitsByRegion');
    Region := Pivot.AddRowField('Region');
    Pivot.AddDataFieldByName('Units', xlpaSum);   // veri alanı 0

    Region.SortType := xlpfstDescending;  // en büyük bölge önce
    Region.SortDataField := 0;            // Units toplamına göre sırala
    Region.AutoSortScope := '0';          // Excel'in kendi autoSortScope'unu da yaz

    if Book.MaterializeNativePivotTable(Pivot) <> 1 then
      raise Exception.Create('Pivot layout was rejected');
    Book.SaveAs('units-by-region.xlsx');
  finally
    Book.Free;
  end;
end;

İki ayrıntı notu hak eder. HotXLS yazıcısı SortDataField'ı rankBy olarak yazar ve AutoSortScope boş olmadığında 4294967294 veri ekseni referansıyla bir autoSortScope bloğu da yazar; özellik dizgesini x v yuvasına koyar. Yükte AutoSortScope aynı x v belirtecini alır; değer sıralamalarında özellik bu yüzden veri alanı ordinal'ini metin olarak taşır. Onu '0' yapmak böylece ölçütü her iki yerde taşıyan bir dosya üretir; Excel'in kendisinin yazdığına en yakın eşleşme budur. MaterializeNativePivotTable ardından sıralanmış ızgarayı düzen üst verisiyle birlikte sahneye koyar ve başarıda 1, teşhisle birlikte -1 döndürür

Sıralanan tek şey öğe satırları değildir. Sütun alanları aynı karşılaştırıcıdan geçer; satır başına toplamlar yerine sütun başına toplamlarla, dolayısıyla SortType = xlpfstDescending ve geçerli bir SortDataField'e sahip bir sütun alanı sütunlarını tam olarak aynı biçimde değere göre dizer

Bir Excel çalışma kitabının hâlihazırda taşıdığı sıralamayı incelemek

Excel tarafından yazılmış dosyalar için HotXLS, pivot tanımını yüklerken SortType, SortDataField ile AutoSortScope'u doldurur; raporun sıralamasını tabloyu yeniden kurmadan denetleyebilir ya da değiştirebilirsiniz. Aşağıdaki parçacık, bir ölçüte göre sıralayan her alanı listeler ve ölçütü DisplayName'iyle adlandırır

var
  Book: TXLSXWorkbook;
  Pivot: TXLSPivotTable;
  Field: TXLSPivotField;
  I: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('sales-report.xlsx');
    Pivot := Book.Sheets.SheetByName('Report').PivotTables[0];
    for I := 0 to Pivot.FieldCount - 1 do
    begin
      Field := Pivot.Fields[I];
      if (Field.SortType <> xlpfstManual) and (Field.SortDataField >= 0) then
        Writeln(Field.Name, ' sorted by ',
          Pivot.DataFields[Field.SortDataField].DisplayName);
    end;

    // Region sıralamasını artana çevir ve mevcut ızgarayı yeniden kur
    Field := Pivot.FindFieldByName('Region');
    if Field <> nil then
      Field.SortType := xlpfstAscending;
    if Book.MaterializeNativePivotTable(Pivot, True) <> 1 then
      raise Exception.Create('Could not re-materialize the pivot');
    Book.SaveAs('sales-report-asc.xlsx');
  finally
    Book.Free;
  end;
end;

MaterializeNativePivotTable'ın ikinci argümanı HotXLS'e, pivotun mevcut çıktı aralığını bir çakışma saymak yerine benimsemesini söyler. Aynı desen HotXLS'te Show Values As sıralama ve kümülatif toplamlar rehberinde de görünür; sıralama düzeni orada önemlidir, çünkü kümülatif toplamlar görüntüleme düzeninde birikir

İki öğe aynı değere sahipken HotXLS beraberlikleri nasıl kırar?

HotXLS beraberlikleri öğe etiketine göre, belirleyici biçimde kırar; bu, Excel'den bilinen bir farktır. İki üyenin toplamları özdeşse karşılaştırıcı altyazılarını karşılaştırmaya döner ve azalan bayrağı o karşılaştırmayı da tersine çevirir. Ölçütü geçerli bir sayı olmayan öğeler artan sırada geçerlilerden sonra gelir; azalan bayrağı ise bütün karşılaştırmayı, o gruplamayla birlikte tersine çevirir

Excel hiçbir beraberlik düzeni söz vermez. Dört üyenin — A, B, C ve D — aynı toplama sahip olduğu bir test setinde Excel'deki artan sıralama B, C, A, D üretti. Saklanan üst veride o permütasyonu öngören bir şey yok; kararsız bir sıralamanın yan ürünüdür ve dosyalar arasında farklı olabilir. HotXLS aynı girdi için A, B, C, D döndürür. O durum için doğrulamamız diziyi değil üye kümesini ve üye başına değerleri kontrol eder ve 15/15 beraberlikli azalan set, Excel ile hücre hücre eşleşti. Aşağı akış kodunuz eşit satırların tam konumuna bağlıysa ikinci bir ölçüte göre sıralayın ya da değerleri farklılaştırın

Daraltılmış üyeler ve en içteki alan

Daraltılmış üyeler HotXLS'te daraltılan her öğe için bir toplama (aggregate) satırı olarak somutlaşır. Excel bir öğenin ayrıntılarını gizlediğinde o <item>'e sd="0" yazar; HotXLS onu TXLSPivotItem.IsDetailHidden olarak açar. Daraltılmış öğe düz etiketiyle render edilir, bütün alt ağacının yerini alır ve daraltma her ebeveyn altında o öğeye uygulanır; kapsayan bloklar kendi ara toplamlarını korur

var
  Category: TXLSPivotField;
begin
  // Pivot, satırlarda Category (dış) ve Product (iç) içeriyor
  Category := Pivot.FindFieldByName('Category');
  if (Category <> nil) and (Category.ItemCount > 0) then
    Category.Items[0].IsDetailHidden := True;   // ilk kategoriyi daralt
  if Book.MaterializeNativePivotTable(Pivot, True) <> 1 then
    raise Exception.Create('Collapse was rejected');
end;

Daraltma sıralamayla öngörülebilir biçimde etkileşir: daraltılmış bir satır hâlâ alanının bir üyesidir ve konumunu her öteki öğe gibi toplamından alır. En içteki alan farklıdır. İç düzeyi yoktur; daraltacak bir şeyi yoktur ve önceki bir düzeltmeden beri HotXLS, defaultSubtotal bayrağı ayarlıyken bile onun için ara toplam satırı hiç render etmez. Excel tek alanlı ve çok düzeyli eksenlerde aynı davranır; o değişiklikten önce HotXLS en içteki alanın altında hayalet bir Total satırı yazabiliyordu. Burada devrede olan alan düzeyi öznitelikler için HotXLS'in CT_PivotField şema geçerliliği gezintisi, şemanın <pivotField> üzerinde ve öğelerinde ne gerektirdiğini anlatır

Excel otomasyonuyla karşılaştırırken dikkat edilecekler

Bir HotXLS sıralamasını COM otomasyonu üzerinden Excel ile çapraz kontrol ettiğinizde AutoSort'u ölçütün yerelleştirilmiş altyazısıyla, Excel'in gösterdiği gibi harfi harfine çağırın. Çince bir Excel'de toplanmış bir Units alanının varsayılan altyazısı '求和项:Units''dir; o makinede 'Sum of Units' geçirmek hata fırlatmaz ve hiçbir şeyi değiştirmez. İngilizce Excel'de çalışan aynı betik başka yerlerde sessizce bir no-op olur; altyazıyı sabit kodlamak yerine veri alanından okuyun

• PivotField.SortType, test ettiğimiz Excel sürümünde COM üzerinden geri okunamadı; sıralamayı bunun yerine kaydedilmiş XML'den ya da render edilmiş hücrelerden doğrulayın
• O sürümde, elle gruplanmış bir ebeveyn alanını sıralanan alanın dışında aynı eksene koymak değere göre üye sıralamasını bastırdı; elle gruplu ebeveynler altındaki sıralı render doğrulanmadı
• PivotField.Outline ile PivotField.LayoutForm'a yazmalar o sürümde de reddedildi; bir set için ana hat (outline) düzenini bunun yerine PivotTable.RowAxisLayout üzerinden ayarlayın

Bu tuhaflıkların hiçbiri dosya biçimini değiştirmez ama bir regresyon testinin bir şey kanıtlayıp kanıtlamayacağına karar verir. AutoSort'u yanlış altyazıyla çağıran bir test, hiç sıralanmamış bir pivotta geçer

SSS

4294967294 alanı bir PivotTable XML dosyasında ne anlama gelir?

4294967294 alanı, alan indeksi -2'nin işaretsiz 32 bitlik kodlamasıdır; ECMA-376 onu Values sözde-alanı için rezerve eder. autoSortScope içinde bir veri ekseni referansını işaretler ve x çocuğu öğelerin hangi veri alanına göre sıralanacağını seçer

HotXLS, Excel kurulu olmadan pivot öğelerini değere göre sıralar mı?

Evet. HotXLS sıralamayı pivot ızgarasını somutlaştırırken kendisi değerlendirir; seçilen veri alanının üye başına toplamlarını kullanır ve Excel'in yenilemeden sonra aynı düzeni koruması için sortType, rankBy ile isteğe bağlı autoSortScope yazar. Yalnızca eşit değerler arasındaki beraberlik düzeni Excel'den farklı olabilir

Pivot öğelerim neden değere göre değil alfabetik sıralı?

Genellikle alanın bir sıralama yönü ama ölçütü olmadığı içindir. SortType'ın xlpfstManual olmadığını ve SortDataField'ın 0 ile DataFieldCount - 1 arasında olduğunu kontrol edin; -1 ile HotXLS öğeleri istenen yönde etikete göre dizer

Değer sıralaması, daraltılmış üyeler ve yerel pivot hattının geri kalanı XLS ve XLSX için Delphi elektronik tablo bileşeni HotXLS ile gelir; bileşen Excel kurulu olmadan Excel çalışma kitaplarını okur, düzenler ve yazar. Pivot'u sıfırdan kuracaksanız önce HotXLS'in pivot tablo kurma ve yenileme eğitimi önbellekleri, alanları ve yenilemeyi sıralamaya gelmeden anlatır