Delphi'de Excel Tablo Slicer'ları: HotXLS Round-Trip

HotXLS, Delphi ve C++Builder'dan Excel tablo slicer'larını tek çağrıyla oluşturur — Worksheet.Slicers.Add(Table, ColumnName) — ve bir slicer'ın ihtiyaç duyduğu sekiz paket konumunun tamamını yazar: slicer ve slicerCache parçaları, iki content-type override'ı, eşleşen extLst bloklarıyla worksheet ve workbook relationship'leri ve çizim anchor'u

Bir slicer, sayfaya bıraktığın bir kontrol gibi görünür. Dosyanın içinde hiç de öyle değildir. İki yeni parçaya, iki relationship grafiğine, üç extension bloğuna ve bir çizim anchor'una dağılmış dağınık bir kayıttır ve Excel paketi açtığında bütünün tamamı tek bir birim olarak doğrulanır. Tek bir parçayı kaçırırsan düşük kaliteli bir slicer elde etmezsin; onarım iletişim kutusu alırsın, arkasından slicer'ı sessizce kaldırılmış bir workbook gelir. Bu hata modu slicer'ları elle üretmeyi çekilmez kılıyor ve ilginç mühendisliğin render değil defter tutma olmasının nedeni de bu

Bir slicer neden pakette sekiz yere dokunur?

Çünkü slicer, OPC'nin üç ayrı eve zorladığı üç ayrı kavramdır: filtre durumu (workbook kapsamlı), görünüm (worksheet kapsamlı) ve şekil (drawing kapsamlı). HotXLS tablo slicer başına tam sekiz landing point yazar ve her biri yük taşır

• xl/slicers/slicerN.xml — worksheet başına bir parça; o sayfadaki her <slicer> görünümünü http://schemas.microsoft.com/office/spreadsheetml/2009/9/main ad alanında tutar
• xl/slicerCaches/slicerCacheN.xml — workbook düzeyinde, dilimlenen her tablo sütunu için bir tane; gerçek bağlamayı taşır
• bu iki parçanın her biri için [Content_Types].xml içinde bir <Override>; application/vnd.ms-excel.slicer+xml ve application/vnd.ms-excel.slicerCache+xml kullanır (ECMA-376 Part 2 her parça için bir content type şart koşar ve Excel onlarsız paketi reddeder)
• türü .../office/2007/relationships/slicer olan bir worksheet relationship'i ve worksheet extLst içinde o rid'i gösteren bir <x14:slicerList> extension'ı
• türü .../office/2007/relationships/slicerCache olan bir workbook relationship'i ve workbook extLst içinde aynı rid'leri listeleyen bir <x15:slicerCaches> extension'ı
• sheet drawing'de slicer'a konum ve boyut veren bir mc:AlternateContent anchor'u
• cache parçasının içinde onu bir tablo id'sine ve bir tablo sütunu id'sine bağlayan bir <x15:tableSlicerCache> bloğu

Extension blokları standart ECMA-376 Part 1 §18.2.10 extLst mekanizmasını kullanır: her <ext> özelliği tanımlayan bir uri özniteliği taşır ve GUID'i tanımayan okuyucular bloğu atlar. HotXLS tablo slicer'larını pivot slicer'lardan yalnızca bu GUID'e bakarak ayırır — tablo slicerList için {3A4CF648-6AED-40f4-86FF-DC5316D8AED3}, pivot için {A8765BA9-456A-4dab-B4F3-ACF838C121DE} ve buna karşılık iki workbook slicerCaches extension'ı için {46BE6895-7355-4a93-B00E-2C351335B9C9} ve {BBE1A952-AA13-448e-AADC-164F8A28A991}. Tablo bağlamasının kendisi {2F2917AC-EB37-4324-AD4E-5DD8C200BD13} altında yaşar. Bu sabitler unit içinde XlsxExtUriSlicerListX15, XlsxExtUriWbSlicerCachesX15 ve XlsxExtUriTableSlicerCache olarak açığa çıkar, yani yazma yolunda tek bir sihirli string yoktur

Hangi parçalar birebir yeniden oynatılabilir, hangileri yeniden kurulmalı?

HotXLS buna tek bir kuralla yanıt verir: bir parça, ancak referans verdiği hiçbir şey kayıt sırasında yeniden numaralandırılamıyorsa orijinal baytlarını olduğu gibi yeniden oynatabilir. Tablo slicer cache'leri bu testi geçemez, bu yüzden HotXLS onları her kayıtta modelden koşulsuz olarak yeniden kurar — kullanıcı slicer'a hiç dokunmasa bile. Neden tableId: <x15:tableSlicerCache> öğesi tablosunu <table> öğesinin id özniteliğiyle tanımlar ve HotXLS tablo id'lerini yazarken workbook genelinde yeniden numaralandırır. Erken bir uygulama cache parçasını açılışta ham bayt olarak saklayıp kayıtta geri itiyordu; bu, çoğu opak parça için doğru strateji. Yaygın durumda temiz açılan dosyalar üretiyordu ve ilginç durumda onarım tetikliyordu: daha önceki bir sayfaya tablo ekle, kaydet ve cache artık başka bir şeye — ya da hiçbir şeye — ait bir tablo id'sine işaret ediyor. Bug, düz bir aç-kaydet regresyonunda görünmez çünkü id'ler şans eseri kımıldamıyor

Pivot biçimli cache'ler ters yolu izler. Yükleri <pivotTables> artı önbelleğe alınmış öğeleri, sıralama durumunu ve öğe indekslerini anlatan bir <x14:data> bloğudur — HotXLS tarafında yeniden numaralandırma olmayan ve sadık biçimde modellenmesi pahalı bir yapı. Bunlar ham bayt olarak tutulur ve birebir yeniden oynatılır; ikisini ayırt etmek için TXLSXSlicerCache.IsPivot ve IsTableCache vardır. Slicer görünümleri üçüncü bir politikayı izler, kirli izleme: dokunulmamış bir <slicer> öğesi ve çizim anchor'u olduğu gibi yeniden oynatılır ve sayfadaki herhangi bir görünüm değiştirildiği anda tüm sheet parçası modelden yeniden üretilir. Yargının genel biçimi budur — referanslar sabitken birebir yeniden oynatma, yazar kimliği yeniden atarken modelden yeniden kurma, ikisi de kabul edilebilirken kirli izleme — ve slicer'ların çok ötesine uzanır; temalar, extLst blokları ve calcChain üzerine lossless round-trip notları bunu daha derinlemesine işler

Delphi'den tablo slicer oluşturmak

TXLSXSlicers.Add tüm oluşturma API'sidir. Bir TXLSXTable ve bir sütun adı verildiğinde tablo sütunu id'lerini somutlaştırır, arkasındaki TXLSXSlicerCache'i bulur ya da oluşturur, eşleşen tanımlı adı ayırır ve görünümü tablonun sağına sabitler. İki argümanlı overload yerleşimi seçer; altı argümanlı olan açık bir 1-tabanlı kapsayıcı kutu alır

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Slicer: TXLSXSlicer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('sales.xlsx');
    Sheet := Book.Sheets[0];

    // Varsayılan yerleşim: tablodan iki sütun açıkta,
    // 19pt varsayılan satır yüksekliğinde 13 satır boyunda
    Slicer := Sheet.Slicers.Add(Sheet.Tables[0], 'Region');
    Slicer.Caption   := 'Sales region';
    Slicer.StyleName := 'SlicerStyleLight1';
    Slicer.ColumnCount := 2;

    Book.SaveAs('sales-sliced.xlsx');
  finally
    Book.Free;
  end;
end;

Bilmeye değer iki adlandırma kuralı var çünkü bunlar HotXLS icadı değil Excel geleneği. Cache Slicer_<column> olarak adlandırılır ve tanımlayıcı olmayan karakterler alt çizgiye katlanır; çakışmalar sayısal sonek ekler — önce Slicer_Region, sonra Slicer_Region1 ve böyle devam eder. Ayrıca her cache adı, formülü #N/A olan bir workbook tanımlı adı olarak da ayrılır; TXLSXSlicers.ReserveCacheDefinedName tam olarak bunu yapar. Excel bu adı kendisi oluşturur ve önemlidir: cache adları ve tanımlı adlar tek bir ad alanını paylaşır, bu yüzden zaten Slicer_Region diye bir adı olan bir workbook bir sonraki kayıtta çakışır. Tabloları ve doğrulamayı birlikte kuruyorsan veri doğrulama, AutoFilter ve tablolar üzerine makale komşu durumları kapsar. Paylaşım Excel'in kullandığı kuralla aynı kuralı izler. Yeni bir cache oluşturulmadan önce TXLSXSlicerCaches.FindForTableColumn(ATableId, ATableColumnId) sorgulanır; böylece aynı tablo sütunu üzerindeki iki slicer görünümü — biri dashboard sayfasında, biri detay sayfasında — tek bir cache parçasını paylaşır ve filtre eşitlenmiş kalır. Bir görünümü silmek workbook'taki her sayfada ters kontrolü çalıştırır ve ona referans veren son görünüm kaldırıldığında cache'i ve ayrılmış #N/A adını ancak o zaman düşürür

Relationship id'leri iki uçta da nasıl sabit kalır?

Sabit kalıyorlar çünkü HotXLS slicer rid'ini, yaydığı her diğer relationship'in tam sırasını yeniden oynatarak hesaplar ve yeni relationship'leri kuyruğun sonuna ekler. Dosyada zaten var olan hiçbir şey kımıldamaz. Bu, alternatifini debug edene kadar aşırı mühendislik gibi duyulur. Bir worksheet .rels dosyasındaki relationship id'leri worksheet parçasının içinden referans alınır — hyperlink'ler, drawing, yorumlar, tablo parçaları tarafından — ve ECMA-376 Part 2 onları relationship grafiği içinde konumsal olarak çözer, sabit bir kimlikle değil. Yazar slicer relationship'ini ortaya eklerse, ondan sonraki her referans bir kayar ve sayfa sessizce yanlış parçayı göstermeye başlar. Bu yüzden XlsxWorksheetSlicerRelNumber deterministik zincirin tamamını yürür — harici hyperlink'ler, sonra yorum VML'si, yorumlar, header/footer görüntüleri, threaded comments, drawing, sayfa arka planı, yazıcı ayarları, tablolar, pivot tablolar, sorgu tabloları, tablo tek hücreleri — ve ancak o zaman slicer için sonraki numarayı talep eder. XlsxWorkbookSlicerCacheFirstRid workbook tarafında aynısını yapar: sheets, sharedStrings, styles, VBA, external linkler, tema, bağlantılar, pivot cache'ler, person, calcChain, sheet metadata, XML eşlemeleri, custom XML, sonra slicerCache serisi. Genel problem OPC relationship çözümlemesi üzerine yazıda işlenir. Workbook tarafındaki extension daha sonra bu numaralardan XlsxBuildWorkbookSlicerCachesExtXml ile kurulur ve kaynak dosyada zaten slicerCaches extension blokları varsa önce ham extLst'ten soyulurlar; böylece model onları bayat bir kopya bırakmak yerine güncel rid'lere karşı yeniden yayar

AlternateContent anchor'u ve eski geri dönüşü

Çizim anchor'u slicer işaretlemesinin gerçekten tuhaf olduğu tek yerdir ve tuhaflık bilinçlidir. HotXLS şekli, ECMA-376 Part 3'e göre bir mc:AlternateContent öğesinin içine yayar; Excel 2013 ve sonrasının aldığı bir Choice dalı ve daha eskileri için bir Fallback dalıyla

// XlsxSlicerAnchorPayloadXml'in yapısal olarak ürettiği:
//
//   <mc:AlternateContent>
//     <mc:Choice xmlns:sle15=".../drawing/2012/slicer" Requires="sle15">
//       <xdr:graphicFrame>
//         <a:graphicData uri=".../drawing/2010/slicer">
//           <sle:slicer xmlns:sle=".../drawing/2010/slicer" name="Region"/>
//         </a:graphicData>
//       </xdr:graphicFrame>
//     </mc:Choice>
//     <mc:Fallback><xdr:sp>...notice shape...</xdr:sp></mc:Fallback>
//   </mc:AlternateContent>

İki ad alanına bak. Requires="sle15" belirteci 2012 slicer ad alanına çözülür; sürüm kapısı budur — 2012 slicer'larını anlamayan bir okuyucu tüm Choice dalını atlamak zorundadır. Ama içindeki graphicData/@uri ve sle:slicer öğesi 2010 ad alanını kullanır, çünkü graphic frame'in hep kullandığı drawing tarafı tanımlayıcısı budur. Bunları tersine çevirirsen Excel'in itirazsız açtığı ama boş bir çerçeve olarak render ettiği bir dosya elde edersin; unutulmaz derecede yararsız bir belirti. Fallback dalı, tablo slicer'larının bu sürümde desteklenmediğini anlatan bir metin gövdesiyle düz bir dikdörtgen tutar — 2007 dönemi bir okuyucunun kontrolün yerine çizdiği şey budur ve eski bir Excel'in workbook'u hiçbir şeyi bozmadan round-trip edebilmesinin nedeni de budur. TXLSXSlicer geometriyi SetBounds(ARow1, ACol1, ARow2, ACol2) ve ayrı FromRow / ToCol özellikleriyle 1-tabanlı kapsayıcı bir kutu olarak açığa çıkarır; ayrıca hücrelerle birlikte taşı ve boyutlandır davranışı için AnchorType vardır. HasAnchor, tek bir özel durum için False olur: bir grup şeklinin içine yuvalanmış slicer. Bunlar grup anchor'larıyla birebir yeniden oynatılır ve HotXLS onlar için ikinci bir anchor sentezlemeyi bilerek yapmaz, çünkü yapmak şekli iki kez yaymak olurdu

Var olan bir slicer'ı düzenlemek

var
  Slicer: TXLSXSlicer;
  Cache: TXLSXSlicerCache;
begin
  Slicer := Sheet.Slicers.FindByName('Region');
  if Slicer <> nil then
  begin
    Slicer.SetBounds(2, 8, 16, 11);      // 1-tabanlı, kapsayıcı
    Slicer.ShowCaption := 1;             // -1 = öznitelik atlanmış
    Slicer.LockedPosition := 1;
    Slicer.RowHeightEmu := 19 * XlsxEmuPerPoint;   // 241300 EMU

    Cache := Slicer.Cache;
    if Cache <> nil then
    begin
      Cache.SortOrder   := 'descending';
      Cache.CrossFilter := 'showItemsWithNoData';
    end;
  end;

  // Bir sütun üzerindeki son görünümü kaldırmak cache'ini
  // ve onunla birlikte ayrılmış #N/A tanımlı adını düşürür
  Sheet.Slicers.Delete(Sheet.Slicers.IndexByName('Region'));

Üç durumlu tamsayı özellikleri bir notu hak ediyor, çünkü modelleme kazası gibi görünürler ve değildirler. ShowCaption, LockedPosition, ColumnCount, StartItem ve CustomListSort — hepsi -1 değerini özniteliğin kaynak dosyada olmadığı ve yazarken de olmaması gerektiği anlamında kullanır; 0 ve 1 açık değerlerdir. Excel birkaç yerde yokluk ile açıkça varsayılanı farklı ele alır ve bu ayrımı korumak, dokunulmamış bir kaydın byte diff'ini temiz tutan şeydir. RowHeight ve ColumnWidth ilişkili bir nedenle ham string olarak tutulur: Excel bu EMU özniteliklerine ondalıklar yazar ve onları tamsayı üzerinden yeniden ayrıştırmak round-trip'te hassasiyet kaybettirir. RowHeightEmu aynı depolama alanı üzerindeki tamsayı kolaylık erişimcisidir; XlsxSlicerDefaultRowHeight 241300 EMU'dur — XlsxEmuPerPoint oranı 12700'de 19 punto

Burası nerede duruyor

İki dürüst sınır. Birincisi, HotXLS tablo slicer'ı eksiksiz modeller ama pivot slicer oluşturmayı modellemez — pivot slicer içeren bir workbook'u açabilir, kimliklerini okuyabilir, kayıt boyunca sağlam tutabilir ve silebilirsin, ama cache tarafı bayt olarak yeniden oynatılır ve sıfırdan bir tane kurmanın API'si yoktur. İkincisi, bir slicer kendi başına hiçbir şeyi filtrelemez. Bir tablo sütununun üzerindeki bir UI'dır ve sürdüğü görünür-satır durumu Excel tarafındadır; HotXLS kontrolü ve bağlamayı yazar, hesaplanmış bir filtre sonucunu değil. Kaydedilen dosyada satırların gerçekten filtrelenmiş olması gerekiyorsa tablo AutoFilter'ını da ayarla. HotXLS'in <slicer> ya da <x15:tableSlicerCache> üzerinde modellemediği herhangi bir öznitelik değiştirilmeden taşınır; yani daha yeni bir Excel sürümünden gelen bir slicer, onu taşısan bile yabancı öznitelikleri sağlam kalacak şekilde yeniden serileşir. Kirli-yeniden kurma yolunu güvenli kılan özellik budur: modelden yeniden kurmak yalnızca model, ayrıştırdığının bir üst kümesiyse kabul edilebilir. ECMA-376 Part 1 §18.2.10'daki GUID etiketli extension mekanizması ve [MS-XLSX] içindeki slicer parça tanımları, bu garantiyi en başta ifade edilebilir kılan şeylerdir

Tablo slicer'ları, XLSX, XLSM ve eski BIFF workbook'larını Delphi 5'ten güncel sürüme kadar, ayrıca C++Builder ve Free Pascal'dan, Excel kurulumu ve OLE olmadan okuyup yazan HotXLS Delphi Component'inde gelir. Tam API referansı ve deneme sürümü HotXLS Delphi Spreadsheet Component ürün sayfasında