Delphi'de Tanımlanmış Adlar ve Sayfalar Arası Formüller

Tanımlanmış bir ad (defined name), çalışma kitabında bir kez saklanan ve ihtiyaç duyulan her yerde sembolik olarak başvurulan bir sabiti, bir hücre aralığını veya bir formül ifadesini temsil eden bir etikettir. Bir formüle TaxRate yazarsınız ve motor bunu adın tanımının tuttuğu şeye (ister sabit bir 0.08 ister Data!$A$2:$D$100 aralığı olsun) çözümler. Sayfalar arası başvuru ise dikey bir fikirdir: Data!D2, adresi bir sayfa adıyla niteleyerek başka bir sayfadaki bir hücreye ulaşır. İkisini bir araya getirdiğinizde, bir özet sayfası, bir ayrıntı sayfasını asla doğrudan bir adresten bahsetmeyen bir ad aracılığıyla toplayabilir; bu, bir oluşturucunun bir araya getirdiği ve bir muhasebecinin daha sonra denetlediği bir çalışma kitabında tam olarak istediğiniz şeydir

losLab'in XLS ve XLSX dosyaları için yerel Delphi kütüphanesi olan HotXLS, süreç içinde (in-process) adları ve sayfalar arası başvuruları çözümleyen bir formül motorunun yanı sıra oluşturma, bulma ve silme erişimi ile her iki biçimin ad tablosunu sunar. İki biçim ayrı sınıf hiyerarşileri tutar ve ad API'leri arasındaki farklar, birinden diğerine taşınan kodları tökezleten kısımdır

Arayüz paylaşmayan iki ad deposu

XLS tarafında, TXLSWorkbook.GetNames, Add(Name, RefersTo, Visible) aşırı yüklemesi BIFF ad tablosuna bir ad yazan bir IXLSNames koleksiyonu döndürür. Münferit girişler; Name, RefersTo, çözümlenmiş bir RefersToRange ve bir Delete yöntemi taşıyan IXLSName nesneleri olarak geri döner. XLSX tarafında, TXLSXWorkbook.DefinedNames; Add, FindByName ve DeleteByName özelliklerine sahip bir TXLSXDefinedNames koleksiyonudur

Arama kuralları, derleme zamanından ziyade taşıma sırasında ortaya çıkacak şekilde ayrışır. XLS koleksiyonunun varsayılan Item özelliği bir Variant kabul eder, bu nedenle hem Names[0] hem de Names['TaxRate'] buna karşı çözümlenir. XLSX koleksiyonunun böyle bir varsayılan özelliği yoktur; ad mevcut olmadığında nil döndüren FindByName('TaxRate') işlevini çağırırsınız. Bir arayüz için yazılan kod, diğeri için yalnızca tesadüfen derlenir ve hata, IDE'de kırmızı bir dalgalı çizgi yerine çalışma zamanında bir nil erişimi olarak görünme eğilimindedir

Kapsam (scope), sonradan ekleyeceğiniz bir bayrak değil, ilk karardır

Tanımlanmış bir ad, ya çalışma kitabı düzeyindedir (her sayfadaki formüller tarafından görülebilir) ya da sayfa düzeyindedir (yalnızca sahibi olan sayfadaki formüller tarafından görülebilir). XLSX API'sinde bu ayrım tek bir isteğe bağlı parametredir. DefinedNames.Add(AName, AFormula) çalışma kitabı düzeyinde bir ad oluştururken, Add(AName, AFormula, ASheetIndex) bunu bir sayfaya bağlar. Geri okunduğunda, TXLSXDefinedName.SheetIndex çalışma kitabı kapsamı için -1, aksi takdirde 0 tabanlı sayfa dizinini döndürür

Kapsam, çakışma politikanız olarak da işlev görür ve ilk adı yazmadan önce bunu belirlemenizin nedeni budur. Excel, her sayfada yerel bir Total'a ek olarak çalışma kitabı düzeyinde bir Total'a izin verir ve belirli bir sayfadaki formül önce yerel olanı çözümler. Oluşturulan çalışma kitapları kasıtlı olarak buna dayanmalıdır. Vergi oranları, döviz kurları ve raporlama dönemi gibi birden fazla sayfanın tükettiği iş varsayımları çalışma kitabı kapsamına aittir. Yalnızca bir sayfanın formüllerinin başvurduğu yardımcı aralıklar, hiçbir şeyin onları gölgeleyemeyeceği ve hiçbir şeyi gölgeleyemeyecekleri sayfa düzeyinde daha güvenlidir

var
  Book: TXLSXWorkbook;
  Data, Summary: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Data := Book.Sheets.Add('Data');
    Summary := Book.Sheets.Add('Summary');
    // ... Data!A2:D100 ayrıntı satırlarıyla doldurulur ...

    Book.DefinedNames.Add('TaxRate', '0.08');                // çalışma kitabı kapsamı, bir sabit
    Book.DefinedNames.Add('DataBlock', 'Data!$A$2:$D$100');  // çalışma kitabı kapsamı, bir aralık
    Book.DefinedNames.Add('LocalNote', 'Summary!$B$1', 1);   // yalnızca sayfa dizini 1 ile sınırlı

    // XLSX formülleri başında '=' almaz
    Summary.Cells[2, 2].Formula := 'SUM(Data!D2:D100)*TaxRate';
    Book.SaveAs('model.xlsx');
  finally
    Book.Free;
  end;
end;

Tanımlanmış bir adın bir aralığı işaret etmesi gerekmez. Yukarıdaki TaxRate yalın 0.08 sabitine başvurur ve bu, bir iş varsayımını yayınlamanın en temiz yoludur. Excel'in Ad Yöneticisinde bir kez görünür, her formül buna sembolik olarak başvurur ve bir sonraki çeyreğin oran değişikliği, birleştirilmiş on dört formül dizesinde arama yapmak yerine oluşturucuda tek satırlık bir düzenledir

Yalnızca bir tarafa ait olan eşittir işareti

Formül giriş kanalı, taşınan kodun en sık kırıldığı yerdir çünkü iki arayüz eşittir işareti konusunda anlaşamaz. XLS hücreleri formülleri başında = ile Value aracılığıyla alır. XLSX hücreleri ise ifadeyi bu ön ek olmadan alan özel bir Formula özelliğine sahiptir. TXLSXCell.Formula içine '=SUM(A1:A10)' yazarsanız, eşittir işareti bir işaretçi yerine saklanan ifade metninin bir parçası haline gelir ve dosya, aynı dizgenin XLS tarafında davrandığı gibi davranmaz

var
  Book: IXLSWorkbook;   // interface-counted: do not Free
  Names: IXLSNames;
begin
  Book := TXLSWorkbook.Create;
  // 'Data' adlı bir sayfanın zaten ayrıntı satırlarını tuttuğunu varsayalım
  Names := Book.GetNames;
  Names.Add('TaxRate', '0.08');
  Names.Add('Helper', 'Data!$A$2:$A$100', False);  // False = Ad Yöneticisinden gizlenmiş

  // XLS formülleri '=' ön eki ile Value üzerinden gider
  Book.Sheets[1].Cells.Item[2, 2].Value := '=SUM(Data!A2:A100)*TaxRate';
  Book.SaveAs('model.xls');
end;

Bu kod parçası XLS tarafındaki iki tuhaflığı daha göstermektedir. Sayfa koleksiyonu 1 tabanlıdır, bu nedenle 0 tabanlı XLSX Sheets[0]'a karşılık Sheets[1] ilk sayfadır. Ve üçüncü Add parametresi gizli bir ad oluşturur: Dosyada mevcut olan ve formüller tarafından kullanılabilen ancak Excel'in Ad Yöneticisinde görünmeyen bir ad. Gizli adlar, son kullanıcıların asla yanlışlıkla düzenlememesi veya silmemesi gereken oluşturucu içi boru hatları (plumbing) için doğru araçtır

Sayfalar arası başvurular ve satırlar hareket ettiğinde ne olduğu

Her iki formül motoru da standart sayfalar arası söz dizimini kabul eder. Düz sayfa adları doğrudan Data!A1 olarak nitelenir; boşluk veya noktalama işareti içeren bir ad, 'Sheet With Space'!A1 örneğinde olduğu gibi tek tırnak gerektirir. Bir adın RefersTo metninin içinde, neredeyse her zaman Data!$A$2:$D$100 gibi mutlak başvurulara yönelin. Tanımlanmış bir adın içindeki göreceli başvuru, onu kullanan hücreye göre çözümlenir; bu, kasıtlı bir Excel özelliğidir ve yanlışlıkla tetiklendiğinde güvenilir bir kafa karışıklığı kaynağıdır

Yapısal düzenlemeler, sayfalar arası muhasebenin değerini kanıtladığı yerdir ve XLSX tarafı adları bunlar boyunca tutarlı tutar. InsertRows ve DeleteRows; tanımlanmış ad aralıklarını hücreler, birleştirmeler, köprüler ve grafik çıpalarıyla birlikte kaydırır, böylece oluşturucu üzerinde bir boşluk açtıktan sonra bile Data!$A$2:$D$100'ü işaret eden bir ad veri bloğunu kapsamaya devam eder. Formüller belgelenmiş bir uyarıyla birlikte gelir: Satır ekleme yalnızca düzenlenen sayfayı hedefleyen başvuruları ayarlar. Data içine satır girdiğinde Data!D2:D100'e başvuran bir Summary formülü yeniden yazılır ki bu genellikle istediğiniz durumdur. Varsaymak yerine bunu doğrulayın çünkü motor size bunu ucuza söyleyecektir:

// hesaplama motoru adları ve sayfalar arası başvuruları süreç içinde çözümler
V := Book.Calculate('SUM(Data!D2:D100)*TaxRate');
if VarIsNumeric(V) then
  Log('net total checks out: ' + FloatToStr(V));

Calculate, hiçbir şeyi kaydetmeden mevcut çalışma kitabı durumuna karşı rastgele bir ifadeyi değerlendirir; bu da onu oluşturucu testleri için doğal doğrulama (assertion) ilkeli yapar. Pascal'da kaynak verilerden beklenen toplamı hesaplayın, çalışma kitabının kendi formülünü değerlendirin ve ikisini karşılaştırın. Formül motoru makalesi motorun neleri, ne zaman değerlendirdiğini ve bunun özel işlevlerle nasıl genişletileceğini kapsar

Özellik katmanının sahip olduğu _xlnm adları

Oluşturulan bir dosyanın ad tablosunu düşük seviyeli bir denetleyicide açtığınızda asla yazmadığınız girişler bulacaksınız: _xlnm.Print_Area, _xlnm.Print_Titles ve akrabaları. OOXML (ECMA-376 / ISO 29500) yazdırma alanlarını ve yinelenen başlık satırlarını ayrılmış tanımlayıcılara sahip adlar olarak bu şekilde saklar. HotXLS bunları özel çalışma sayfası özellikleri aracılığıyla yönetir, bu nedenle PrintArea veya PrintTitleRows ayarı sizin yerinize karşılık gelen _xlnm.* girişini yazar

Tuzak, bu ayrılmış ad alanına elle ulaşmaktır. PrintArea özelliğini de ayarlarken DefinedNames.Add aracılığıyla bir _xlnm.Print_Area girişi eklerseniz çalışma kitabı tek bir ayrılmış ad için çelişkili iki tanım taşır; bu, Excel'in hiçbir ürünün bağımlı olmaması gereken şekillerde çözdüğü bir durumdur. _xlnm. ile başlayan her tanımlayıcıyı özellik katmanına ait olarak ele alın. Yazdırma kurulumunu incelemek için ad tablosunu değil, özellikleri okuyun. Koruma ve sayfa yapısı makalesi yazdırma alanı özelliklerini bağlam içinde ele almaktadır

Bir tasarıma başlamadan önce bilinmesi gereken iki sınır

Tanımlanmış adlar, XLS'den XLSX'e köprü üzerinden taşınmaz. SaveXLSWorkbookAsXLSX hücre içeriğini ve temel biçimlendirmeyi kopyalar ancak ad tablosu belgelenmiş kopyalama listesinde yer almaz, bu nedenle adlarına bağımlı olan bir çalışma kitabı geçiş sırasında bunları kaybeder. Dönüştürmeden sonra adları DefinedNames.Add aracılığıyla yeniden oluşturun. Bu adım göründüğünden daha az zahmetlidir çünkü XLS dosyasında ne varsa onu taşımak yerine kapsamlarını normalleştirmeniz için size bir an verir

Diğer risk ise oluşturucu tarafındadır: Pascal kodu sayfa adı sabitinden formül dizgeleri oluşturduğunda, sayfayı bir yerde yeniden adlandırıp diğer yerde unuttuğunuzda artık var olmayan bir sayfaya başvuru üretilir. Sayfa adını tek bir Delphi sabitinde tutun ve bunu hem Sheets.Add hem de formül düzeneğinize besleyin, böylece ikisi asla çelişemez. Bu, adresleri sabit kodlamak yerine bir raporun çıktı hücrelerini adlandırmayı savunan içgüdüyle aynıdır: Toplam hücresi adlandırılmış bir şablon, tasarımcı üzerine üç satır ekledikten sonra bile çalışmaya devam ederken, sabit bir B17'ye yazan bir oluşturucu numarasını sessizce yanlış yere indirir. Şablon rapor oluşturma makalesi tam olarak bu kalıp üzerine kurulmuştur

Her iki biçim için tanımlanmış adlar API'sinin tamamı, formül motoru referansıyla birlikte HotXLS Bileşeni ile birlikte gönderilmektedir