Teknik Makale

HotXLS ile XLSX Pivot Alanı Şema Geçerliliği

Bir pivotField öğesi üzerindeki axis="rowAxis" özniteliği, gayet makul XML gibi okunur ve onu satır alanına eşleyen bir okuyucu, pivotu her alanı sağ eksende olacak biçimde yükler. Aynı parçayı ECMA-376 Part 1 §18.10 şemasından kurulmuş bir doğrulayıcıya verin; ilk öznitelikte düşer: ST_Axis'in tam olarak dört tokenı vardır — axisRow, axisCol, axisPage ve axisValues — ve rowAxis bunlardan biri değildir. Bir pivot yazıcısı, kendi okuyucusunu değil şemayı tatmin etmeli, çünkü dosyayı açanlar Excel'i ve kendi ayrıştırıcılarını kullanır

Pivot alan tanımları bu tür kurallarla doludur. Üçü, bir gidiş-dönüş testinin gözünden kaçar: sayım (enumeration) tokenları, bir items öğesinin asla boş olmaması gereksinimi ve Excel'in veri öğelerinden sonra listelediği sondaki ara toplam girdileri. İkisi de okuma tarafındadır, Excel'in şema varsayılanını taşıdıkları için atladığı özniteliklerde. Bu yazının geri kalanı her kuralı tek tek gezinir, XML'in neye benzemesi gerektiğini gösterir ve onu üreten Delphi çağrılarını gösterir

Gidiş-dönüş yanlış bir axis tokenını neden yakalayamaz

Bir gidiş-dönüş, yazar ile okuyucunun birbiriyle anlaştığını kanıtlar, başka hiçbir şeyi değil. Okuyucu hem axisRow'u hem rowAxis'i kabul ediyorsa yanlışını üreten bir yazar, yazabileceğiniz her kaydet-sonra-yükle denetimini geçer, çünkü okuyucu hatayı geri dönüş yolunda onarır. Kusur ancak daha katı bir tüketici parçayı açtığında görünür: bir şema doğrulayıcı, XSD'den üretilmiş bir ayrıştırıcı ya da parçanın onarım gerektirdiğine karar veren Excel

HotXLS iki tarafı bilerek ayırır. XLSX yazıcısı yalnızca dört ST_Axis tokenını üretir; pivot okuyucusu ise İngilizce görünümlü yazımlar rowAxis, colAxis, pageAxis ve dataAxis'i de eşleşen eksene eşler, böylece bunları kullanan üreticilerin dosyaları düzenleri bozulmadan yüklenir. Çıktı şemaya bağlanır; girdinin yalnızca tanınabilir olması yeter. Tersi, titiz bir okuyucuyla özensiz bir yazar, en azından kendi testlerinizde gürültüyle düşerdi; affedici bir okuyucuyla özensiz bir yazar ise görebildiğiniz hiçbir yerde düşmez

<!-- geçersiz: rowAxis bir ST_Axis değeri değildir ve CT_Items boş olamaz -->
<pivotField axis="rowAxis" defaultSubtotal="1"><items count="0"></items></pivotField>

<!-- geçerli: ST_Axis tokenı, gerçek öğeler, sondaki varsayılan ara toplam -->
<pivotField axis="axisRow" defaultSubtotal="1">
  <items count="4"><item x="0"/><item x="1"/><item x="2" h="1"/><item t="default"/></items>
</pivotField>
Geçersiz ve geçerli pivotField XML'i: uydurma axis değeri rowAxis ile boş bir items öğesi CT_PivotField'ı ihlal eder; HotXLS ise axisRow gibi ST_Axis tokenlarını gerçek öğe girdileriyle, korunan bir gizli imiyle ve şemanın kabul ettiği sondaki varsayılan ara toplamla üretir
Her iki yazımı da kabul eden affedici bir okuyucu, dosya her katı şema denetimini kırarken her gidiş-dönüşün geçmesine izin verir — yalnızca dört ST_Axis tokenını yazın ve CT_Items en az bir öğe taşısın

CT_PivotField'ın eksenin ötesinde bekledikleri

CT_PivotField'ın düşmesi kolay üç gereksinimi daha vardır. Değerler alanında toplanan bir alan, bunu kendi tanımında dataField="1" ile söylemek zorundadır; <dataFields> listesi ona zaten referans veriyor olsa bile. HotXLS bu imi, DataFields içindeki bir girdinin işaret ettiği her pivot alanına atar, böylece AddDataFieldByName ile eklenen bir alan iki yerde de işaretlenir

İkinci kural yapısal. CT_Items, en az bir item dölü gerektirir, bu da <items count="0"></items>'u, iyi biçimli XML olması ve her yerde ayrışması bir yana, geçersiz kılar. Bir alanın öğesi yokken HotXLS bütün items öğesini dışarıda bırakır. Üçüncü kural, her öğedeki durumla ilgilidir: gizli bir üye h="1" taşır, ayrıntıları katlanmış bir üye sd="0". İkisi de TXLSPivotItem.IsHidden ile TXLSPivotItem.IsDetailHidden'dan gelir ve her kayıtta yazılır, böylece kodla kurulmuş bir filtre Excel'e kadar yaşar

Sondaki ara toplam öğeleri ve count tuzağı

Bir alanın öğeleri olduğunda Excel, veri öğelerini ara toplam işlevi başına bir ek item ile izler; ST_ItemType ile türlenir. Otomatik ara toplam <item t="default"/>'dur; açık işlevler sum, countA, avg, max, min, product, count, stdDev, stdDevP, var ve varP kullanır. Bu girdiler önbelleğe hiçbir x indeksi taşımaz ve items'in count özniteliğine dahildir. Onları dışarıda bırakan bir yazar, artık aynı öğe üzerindeki ara toplam imleriyle uyuşmayan bir items listesi üretir

HotXLS izi, kayıt anında TXLSPivotField.Subtotals'dan türetir: setteki işlev başına bir t="…" öğesi, sayım sırasıyla, count'a eklenir ve alana eşleşen sumSubtotal="1" tarzı öznitelik gelir. AddPivotTable'ın kurduğu alanlar boş bir setle başlar; bu, defaultSubtotal="0" yazar ve hiçbir sondaki öğe yazmaz. Üretilmiş raporlar için seti boş bırakın: ara toplam satırları pivotun yüksekliğini değiştirir ve altına konmuş her şeyi kaydırır. Subtotals'a işlevleri, yalnızca bir okuyucunun o ek satırlara gereksinim duyduğu alanlarda ekleyin

Bir işlev seçmeden önce adlandırmaya ikinci bir bakış değer. xlpsCount, tüm boş olmayan girdileri sayan countA'ya eşlenir ve xlpsCountNums, yalnızca sayıları sayan count'a. XML'de count okuyup xlpsCount'a uzanan herkes öbür işlevi alır

HotXLS pivot items listesi anatomisi: veri öğe girdilerini, TXLSPivotField.Subtotals'tan türeyen t=default ile t=avg gibi sondaki ara toplam öğeleri izler ve items count'una sayılır; xlpsCount'un countA'ya, xlpsCountNums'un count'a eşlenmesi tuzağı da açıkça yazılıdır
AddPivotTable'dan gelen alanlar boş bir Subtotals setiyle başlar; bu defaultSubtotal=0 yazar ve sondaki öğe yazmaz — istediğiniz işlevleri isteyin, yazar işlev başına bir öğe türetip count'a ekler

Aşağıdaki program, var olan bir veri sayfasından satır alanı, sütun alanı ve bir değer alanıyla bir pivot kurar. Region alanı otomatik ara toplamı artı bir ortalama alır; bu, veri öğelerinin ardından iki sondaki öğe üretir ve Revenue üzerine dataField="1" koyar

uses
  SysUtils, lxHandleX, lxPivot;

var
  Book  : TXLSXWorkbook;
  Sheet : TXLSXWorksheet;
  Pivot : TXLSPivotTable;
  Region: TXLSPivotField;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('orders.xlsx');
    Sheet := Book.Sheets[1];                  // Sheets 1 tabanlıdır
    Pivot := Sheet.AddPivotTable('Data!$A$1:$D$800', 3, 6, 'RegionTotals');
    if Pivot = nil then
      raise Exception.Create('Bad source range or anchor');

    Region := Pivot.AddRowField('Region');    // böyle bir alan yoksa nil
    if Region <> nil then
      Region.Subtotals := [xlpsDefault, xlpsAverage];  // -> t="default", t="avg"
    Pivot.AddColumnField('Quarter');
    Pivot.AddDataFieldByName('Revenue', xlpaSum);      // Revenue, dataField="1" alır

    Book.SaveAs('orders-pivot.xlsx');
  finally
    Book.Free;
  end;
end;

Excel'in dışarıda bıraktıklarını okumak

Okuyucunun sorunu ayna görüntüsüdür. Excel yapımı bir pivot alanı, ara toplam girdilerini items içinde listeler ve onları sıradan üyeler olarak yüklemek, alana hiçbir önbellek değerini göstermeyen hayalet öğeler verirdi. HotXLS, t özniteliği bulunan ve data olmayan her item'ı atlar; bu, ara toplam, grand-total ve boş girdileri kapsar. Ara toplam işlevleri, alanın kendi imleri üzerinden gelir ve Subtotals'a düşer, böylece Items üzerinde gezinen kod yalnızca gerçek üyeleri görür ve her CacheItemIndex bir önbellek değerine işaret eder

Eksik öznitelikler öteki yarıdır. Şemada CT_PivotField üzerindeki defaultSubtotal ile CT_SharedItems üzerindeki containsString ikisi de true varsayılanlıdır ve Excel, o değeri taşıdıklarında onları atlar. HotXLS, olmayan bir defaultSubtotal'ı true okur ve alan açık bir ara toplam imi taşımıyorsa Subtotals'a xlpsDefault ekler; Excel'in kendisi de böyle bir alanı böyle gösterir. Olmayan bir containsString da true okunur, dolayısıyla düz metin bir önbellek alanı dizge verisi olarak sınıflanır. İki özniteliği de false okuyan bir yazılımda, Excel'in kaydettiği her pivot yükleme üzerinde otomatik ara toplamlarını yitirir, metin alanları başka bir türe döner. Her özniteliği her zaman açıkça yazan bir yazar bu yola hiç uğramaz; onu yalnızca başka bir üreticinin dosyaları çalıştırır

Excel'in kaydettiği bir çalışma kitabı burada doğru test girdisidir, çünkü o öznitelikleri dışarıda bırakan tek dosya türü budur. Aşağıdaki denetim bir tanesini açar ve bir satır alanını gezer:

var
  Book : TXLSXWorkbook;
  Pivot: TXLSPivotTable;
  Fld  : TXLSPivotField;
  I, J : Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('saved-by-excel.xlsx');
    Pivot := Book.Sheets[1].PivotTables.FindByName('PivotTable1');
    if Pivot = nil then
      Exit;
    for I := 0 to Pivot.FieldCount - 1 do
    begin
      Fld := Pivot.Fields[I];
      if Fld.Axis <> xlpfaRowField then
        Continue;
      // Ara toplam özniteliği olmayan bir Excel alanı yine de varsayılanı bildirir
      if xlpsDefault in Fld.Subtotals then
        Writeln(Fld.Name, ': automatic subtotal');
      // Ara toplam girdileri atlandı, dolayısıyla her öğe bir önbellek değerine eşlenir
      for J := 0 to Fld.ItemCount - 1 do
        Assert(Fld.Items[J].CacheItemIndex >= 0);  // kendi testleriniz için önerilen denetim
    end;
  finally
    Book.Free;
  end;
end;

numFmtId="General" önbellek alanlarında neden geçersizdir

numFmtId="General", ST_NumFmtId işaretsiz bir tamsayı olduğu için geçersizdir; Hücreleri Biçimlendir penceresinde kullanıcıların gördüğü adı ödünç almak ne kadar doğal olursa olsun. Öznitelikleri şemadan türlendiren bir ayrıştırıcı o adı açıkça reddeder ve bu tür bir başarısızlık, tam olarak Excel'de onarım penceresine dönüşen türdendir; Excel onarım penceresinin ardındaki OPC ve işaretleme kuralları yazısı o pencerelerin nasıl tetiklendiğini kapsar. HotXLS her cacheField'ı, önbellek alanının NumberFormat'ından alınan sayısal bir numFmtId ile yazar; bir şey atamadıysa bu, gömülü General biçimi olan 0'dır

65535 satırının altındaki pivot tablolar 32 bitlik konumlar gerektirir

XLS ile XLSX motorlarının paylaştığı bir pivot modeli, alan genişliklerini BIFF8 SxView kaydından ödünç almaya eğilimlidir; orada 16 bit, klasik bir sayfanın taşıyabileceği her satırı kapsar. Bir XLSX sayfası 1.048.576 satıra uzanır. FirstRow, LastRow, FirstHeaderRow, FirstDataRow ve sütun karşılıklarını Word olarak saklayıp Min(Value, High(Word)) ile kırparsanız, 70001. satıra demirlenmiş bir pivot 65535'te kesilir

TXLSPivotTable üzerinde o özellikler Integer'dır ve onları yalnızca BIFF8 yazıcısı kayıt sınırında daraltır. TXLSXWorksheet.AddPivotTable, 1..1048576 arası 1..16384 dışındaki bir demir için nil döndürür ve AddPivotTableCopy, kopyanın kapsamı ızgarayı aşacaksa nil döndürür

16 bitlik tavanda 70001. satırdaki pivot demiri: Word olarak saklanıp Min ile High(Word), yani 65535 karşısında kırpılan FirstRow ile LastRow, pivotları çizgide ya da altında keser; HotXLS ise onları Integer alanlar olarak tutar ve ızgara dışında nil döndürür
Word genişliğinde satır alanları BIFF8 SxView kayıtlarına sığar ama 1048576 satıra uzanan bir sayfaya sığmaz — onlarla, 65535. satırın ötesindeki bir demir 65535'e kırpılır ve pivot artık konduğu yerde durmaz
var
  Book, Check: TXLSXWorkbook;
  Pivot      : TXLSPivotTable;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('orders.xlsx');
    // 70001. satır 16 bitlik aralığın ötesindedir
    Pivot := Book.Sheets[1].AddPivotTable('Data!$A$1:$D$800', 70001, 1, 'LateTotals');
    if Pivot = nil then
      Exit;  // sayfa dışı bir demir ya da çözümlenemeyen kaynak aralık
    Pivot.AddRowField('Region');
    Pivot.AddDataFieldByName('Revenue', xlpaSum);
    Book.SaveAs('late.xlsx');
  finally
    Book.Free;
  end;

  Check := TXLSXWorkbook.Create;
  try
    Check.Open('late.xlsx');
    Pivot := Check.Sheets[1].PivotTables.FindByName('LateTotals');
    // Assert, kendi testleriniz için önerilen bir denetimdir
    Assert((Pivot <> nil) and (Pivot.FirstRow = 70001));
  finally
    Check.Free;
  end;
end;

Klasik XLS motoru da aynı 1 tabanlı kuralı izler. SxView ile DConRef kayıtları 0 tabanlı değerler saklar; model ise, XLSX motoru ve Cells[Row, Col] gibi, 1 tabanlı konumları tutar: BIFF8 okuyucusu kayıt sınırında 1 ekler, yazıcısı 1 çıkarır. Klasik AddPivotTable, 1..65536 arası 1..256 dışındaki bir demir için nil döndürür, dolayısıyla pivotları (0, 0) yerine (1, 1)'e demirleyin. Kayıt düzeninin kendisi klasik .xls pivot tablolarının ardındaki BIFF8 SX kayıtları yazısında anlatılır

Kendi okuyucunuza değil şemaya karşı test etmek

Affedici taraf ile hatalı taraf aynı kütüphanede yaşadığında yukarıdaki her tuzak bir gidiş-dönüşü geçirir. Üç tür denetim bunları yakalar. Üretilen pivotTableN.xml ile pivotCacheDefinitionN.xml parçalarının şema doğrulaması, uydurma tokenları, boş items öğelerini ve adla yazılmış biçim idlerini yakalar. Excel'in kaydettiği dosyalar, okuyucunuzdan geçirildiğinde, Excel'in varsayılanında atladığı öznitelikleri çalıştırır. Yazılan XML'deki tam tokenı çivileyen doğrulamalar — axis="axisRow" gibi — yazarı yalnızca kendi okuyucunuzun anladığı bir yazıma savurmayı yakalar; ayrıştırılmış eksen üzerinde doğrulamak yakalayamaz

hesaplanan alanlarla XLSX pivot tabloları kurma ve tazeleme yazısında gösterilen hesaplanan alanlar, hesaplanan öğeler ve yüzde-toplam düzenleri dahil, API üzerinden kurulan pivotlar bu kurallar uygulanmış çıkar; sizin tarafınızda ekstra kod gerekmez. Size kalan iş, hangi alanların ara toplam alacağını ve hangi count işlevini kast ettiğinizi seçmektir

HotXLS Delphi spreadsheet component, XLS ile XLSX pivot tablolarını Delphi ve C++Builder kodundan kurar, okur ve kaydeder; onu çalıştıran makinede Excel kurulumu ya da COM otomasyonu gerektirmez