Delphi'de HotXLS Formül Motoru ve Özel İşlevler

Yalnızca formül dizgelerini saklayan bir elektronik tablo kütüphanesi ile çalışan bir formül motoruna sahip bir kütüphane, bunlardan birinden bir sayı isteyene kadar aynı görünen iki farklı üründür. Çoğu Delphi elektronik tablo kodu bu açığı asla fark etmez, çünkü Excel bunu kapatır: Bir hücreye SUM(B2:B501) yazın, kaydedin ve Excel, bir insan dosyayı açtığı anda toplamı yeniden hesaplar. İnsanı döngünün dışına çıkarın, aynı çalışma kitabını doğrudan CSV'ye aktaran bir sunucu süreç hattından (pipeline) geçirin ve aradaki fark akademik olmaktan çıkar. CSV, bir sayının ait olduğu yerde tam anlamıyla =SUM(B2:B501) metnini taşır, çünkü hiçbir noktada formülü gerçekten değerlendiren (evaluate) bir şey olmamıştır

HotXLS bu çizginin doğru tarafında yer alır. Bir formülü tıpkı dosya biçimlerinin yaptığı gibi, saklanan bir metin artı isteğe bağlı önbelleğe alınmış bir sonuç olarak ele alır; böylece çıplak bir CSV dışa aktarımı yemek yerine tarifi yeniden üretir. Ancak aynı zamanda doğrudan çağırabileceğiniz bir hesaplama motoru (hem XLS hem de XLSX arayüzlerinde aynı motor) ve motorun daha önce hiç duymadığı işlev adlarını çözmek için bir kanca taşır. HotXLS; Excel otomasyonu olmadan Delphi ve C++Builder'dan XLS ve XLSX okuyan ve yazan yerel bir Object Pascal kütüphanesidir ve bunun hesaplama kısmı, saklanan formülleri talep üzerine tekrar değerlere dönüştüren şeydir

Formüller saklanır, hemen değerlendirilmez

Bir hücreye formül yazmak hiçbir şeyi hesaplamaz. Kaydetme sırasında çalışma kitabı formül metnini kaydeder. XLS tarafında, varsayılan olarak True olan ve Excel'e açılışta yeniden hesaplamasını söyleyen RecalcOnSave tarafından yönetilen bayrakları da kaydeder. Bu model, Excel'e giden dosyalar için doğrudur ve CSV dışa aktarımı, HTML dışa aktarımı veya kendi kodunuzun hücreleri geri okuması gibi hücre değerlerini doğrudan tüketen süreç hatları için yanlıştır. Bunlar için, Calculate ile açıkça değerlendirme yapın. Dört giriş noktasında mevcuttur: TXLSWorkbook, IXLSWorksheet, TXLSXWorkbook ve TXLSXWorksheet nesnelerinin hepsi function Calculate(const Formula: WideString): Variant işlevini sunar

// hesaplama motoru adları ve sayfalar arası başvuruları süreç içinde çözümler
Total := Book.Calculate('SUM(Sales!B2:B501)');
Sheet.Cells[502, 2].Value := Total;
Book.SaveAsCSV('sales.csv', 0, ',');   // CSV artık sayıyı taşıyor

Calculate'e teslim edilen ifade sıradan Excel formül metnidir. Sayfalar arası başvurular, tanımlanmış adlar ve iç içe geçmiş işlevlerin tümü mevcut bellek içi çalışma kitabına göre çözümlenir; bu da çağrıyı CSV dışa aktarımlarını yamamanın çok ötesinde kullanışlı kılar. Bunu bir doğrulama (assertion) mekanizması olarak ele alın. Az önce beş yüz ayrıntı satırı yazmış bir oluşturucu, çalışma kitabından kendi genel toplamını isteyebilir ve bunu Pascal'da bağımsız olarak hesapladığı rakamla karşılaştırarak, bir müşterinin denetçisinden önce bir indeks kayması hatasını yakalayabilir

Ayrıca formül ağırlıklı çıktılar için doğru test stratejisini çerçeveler. Excel, formül dilinin referans uygulaması olmaya devam etmektedir, bu nedenle iş sonuçları taşıyan birkaç formül için, beklenen değerleri bizzat Excel tarafından üretilmiş onaylı bir test verisi (fixture) dosyası tutun ve derleme süreç hattının, oluşturulan çalışma kitabının formüllerini o test verilerine karşı Calculate ile değerlendirmesini sağlayın. Böylece farklılıklar, iki raporu karşılaştıran bir müşteri tarafından keşfedilen tutarsızlıklar yerine Delphi'de başarısız testler olarak ortaya çıkar

OnUserFunction ile iş işlevleri ekleme

Motor tanımadığı bir işlev adıyla karşılaştığında doğrudan başarısız olmak yerine bir olay (event) tetikler. Çalışma kitabı sınıflarından herhangi birine OnUserFunction atayarak çağrıyı kendiniz çözümleyebilirsiniz:

procedure TReportBuilder.HandleUserFunction(Sender: TObject;
  const FunctionName: WideString; const Args: Variant;
  var Value: Variant; var Handled: Boolean);
begin
  if SameText(FunctionName, 'DISCOUNT') then
  begin
    Value := Args[0] * 0.9;   // Args bir Variant dizisi olarak gelir
    Handled := True;
  end;
end;

// kablolama ve kullanım
Book.OnUserFunction := HandleUserFunction;
Sheet.Cells[1, 1].Value := 200;
Sheet.Cells[1, 2].Formula := 'DISCOUNT(A1)';
Net := Book.Calculate('DISCOUNT(A1) + SUM(A1:A1)');

Üç ayrıntı dikkat çekmektedir. İlk olarak, Handled := True değerini yalnızca adı gerçekten tanıdığınızda ayarlayın. Bunu False olarak bırakmak, motorun normal bilinmeyen işlev işlemlerine devam etmesini sağlar, böylece tek bir işleyici, geçen her şeyi sahiplenmeden birkaç çalışma kitabına hizmet edebilir. İkinci olarak, formül yazarları discount( ve DISCOUNT( ifadelerini birbirinin yerine yazabildiğinden, adları SameText ile büyük-küçük harf duyarsız olarak karşılaştırın. Üçüncü olarak, bağımsız değişkenler önceden değerlendirilmiş olarak gelir: DISCOUNT(A1) size referansı değil A1'in değerini verir, bu nedenle bir işlev girdilerinin nereden geldiğini söyleyemez. Bu son nokta, bir sonraki bölümün konusu olan sınırlamayı hazırlar

İşleyici gövdesine herhangi bir harici giriş noktasıyla aynı savunmacılıkla yaklaşın. Args dizisi, formül yazarının yazdığı her şeyi yansıtır, bu nedenle içine dizinlemeden önce bağımsız değişken sayısını ve türlerini doğrulayın ve geçersiz bir çağrının ne döndüreceğine önceden karar verin: Bir Variant hata değeri mi yoksa fırlatılan bir istisna (exception) mı. Bu seçim önemlidir çünkü işleyicinin içinde fırlatılan bir istisna, değerlendirmeyi tetikleyen Calculate çağrısı aracılığıyla dışarı yayılır. Bu, sıkı kontrol edilen bir oluşturucuda kabul edilebilir, ancak tek bir kötü formülün isteği çökerteceği, kullanıcılar tarafından yazılmış çalışma kitaplarını değerlendiren bir serviste kabadır. Bu ortamda, işleyicinin içinde yakalayın ve çevreleyen iş akışının tanıyabileceği ve günlükleyebileceği bir nöbetçi (sentinel) değeri döndürün

Konum duyarlı işlevler Ex varyantına ihtiyaç duyar

Bazı işlevler haklı olarak nerede değerlendirildiklerine bağlıdır. Sayfa başına farklılık gösteren bir oran, satıra göre değişen bir arama, yalnızca bölgesel sayfalarda geçerli olan bölge başına bir çarpan: Bunların hiçbiri yalnızca bağımsız değişken değerleriyle yanıtlanamaz. Düz olay bunu ifade edemez, bu nedenle motor bir ekstra parametre dışında aynı olan OnUserFunctionEx sunar:

procedure TReportBuilder.HandleUserFunctionEx(Sender: TObject;
  const FunctionName: WideString; const Args: Variant;
  const Context: TXLSUserFunctionContext;
  var Value: Variant; var Handled: Boolean);
begin
  if SameText(FunctionName, 'REGIONRATE') then
  begin
    // aynı formül her bölgesel sayfada farklı bir oran verir
    Value := RateForSheet(Context.SheetIndex) * Args[0];
    Handled := True;
  end;
end;

TXLSUserFunctionContext, değerlendirilen hücrenin SheetIndex, Row ve Col değerlerini taşır. Bir işlevin sonucu konumuna azıcık bile bağlıysa, Ex olayını en başından itibaren bağlayın. Halihazırda otuz formülün çağırdığı bir işleyiciye sonradan bağlam (context) eklemek, birinci gün doğru imzayı seçmekten çok daha karmaşıktır ve iki olay aksi takdirde birbirine o kadar benzerdir ki daha dar olanla başlamak için çok az neden vardır

Özel işlevler Excel'e taşınmaz

Özel bir işlev tamamen sizin sürecinizin içinde yaşar. DISCOUNT adı yalnızca Delphi kodunuz ve onun olay işleyicisi çalışırken bir anlam ifade eder. Kaydedilen dosyayı Excel'de açtığınızda DISCOUNT tanınmayan bir addır; kullanıcının makinesinde eşleşen bir VBA işlevi veya eklentisi bulunmadığı sürece hücre #NAME? gösterir. Bu, bir demoyu gönderilebilir bir üründen ayıran tasarım gerçeğidir ve sizi daha sonra keşfetmek yerine kasıtlı olarak yapmanız gereken bir seçime zorlar

Hücre başına hangi iki sözleşmeden hangisini gönderdiğinize karar verin. Kullanıcının Excel içinde yeniden hesaplandığını görmesi amaçlanan hücreler, Excel'in kendi işlev kelime dağarcığından oluşturulmalıdır, başka hiçbir şeyden değil. Mantığı tescilli olan hücreler, Calculate ile süreç içinde değerlendirilmeli ve düz değerler olarak kalıcı hale getirilmelidir; böylece özel işlev, dosya içeriği yerine dahili bir hesaplama kuralı olarak davranır. Güvenilir bir şekilde destek biletleri üreten başarısızlık modu orta yoldur: Özel bir işlev formülünü kalıcı hale getirmek ve Excel'in buna uymasını beklemek

Yalnızca değer içeren sözleşmenin sessiz bir avantajı vardır: Fikri mülkiyeti korur. Delphi sürecinizde değerlendirilen ve sayı olarak gönderilen bir fiyatlandırma kuralı, görünür bir formülün yapılabileceği gibi çalışma kitabından tersine mühendislikle çözülemez ve bir kullanıcı aradaki bir hücreyi düzenleyerek onu bozamaz. Fatura oluşturucular, komisyon beyanları ve fiyat kartları neredeyse her zaman bu kamptadır. Gerçekten canlı formüllere ihtiyaç duyan durum, müşterinin girdileri değiştirmesi ve toplamların hareket etmesini izlemesi beklenen etkileşimli "eğer durum şöyle olursa" (what-if) modelidir ve bunların Excel'in kendi kelime dağarcığı artı tanımlanmış adlardan oluşturulması gerekir

Hesaplama modları, yineleme ve R1C1: XLS arayüzünün kadranları

XLS arayüzü, Excel'in dosyadan okuduğu BIFF düzeyindeki hesaplama ayarlarını sunar. CalculationMode; xlCalcManual, xlCalcAutomatic (varsayılan) veya xlCalcAutomaticExceptTables değerlerini kabul eder ve dosya açıldıktan sonra Excel'in nasıl davranacağını belirler. Binlerce formül içeren bir model çalışma kitabı genellikle manuel modda teslim edildiğinde daha dostanedir, böylece alıcı yeniden hesaplama fırtınasının ne zaman gerçekleşeceğine karar verir. EnableIteration (varsayılan False), MaxIterations (varsayılan 100) ve MaxIterationChange (varsayılan 0.001) ile birlikte, bazı finansal modellerde görülen yinelemeli yakınsama (iterative-convergence) türünden kasıtlı dairesel başvuruların kilidini açar. ReferenceStyle, A1 ve R1C1 ekranı arasında geçiş yapar ve UseFullPrecision, Excel'in görüntülendiği gibi hassasiyet seçeneğini yansıtır

Bu özellikler XLS arayüzünde yaşar çünkü BIFF kayıtlarıyla eşleşirler; .xlsx üretirken formülleri yinelemeli ayarlara bağımlı olmayacak şekilde planlayın veya yakınsanmış değerleri Delphi'de hesaplayıp sonuçları yazın

Dizi formülleri: Genel giriş noktası XLSX'tir

Eski CSE tarzı dizi formülleri TXLSXRange.SetArrayFormula aracılığıyla oluşturulur:

// A2:A4 aralığını kaplayan tek bir dizi formülü
Sheet.RCRange[2, 1, 4, 1].SetArrayFormula('A1*{1;2;3}');

Eşdeğer yöntem XLS sınıf hiyerarşisinde mevcuttur ancak özel (private) bir bölümde yer alır, bu nedenle .xls dosyalarına yeni dizi formülleri yazmanın desteklenen bir yolu yoktur. Açılan dosyalardaki mevcut olanlar sağlam bir şekilde gidiş-dönüş yapar; yapamayacağınız şey onları oluşturmaktır. Bunu takip eden kural yeterince basittir: Dizi semantiği gereksinimin bir parçası olduğunda .xlsx'i hedefleyin. Eski bir .xls çıktısı gerçekten dizi davranışına ihtiyaç duyuyorsa, pragmatik yol Delphi'de dizi sonucunu hesaplamak ve münferit değerleri hücrelere yazmaktır

Bu sitedeki iki ilgili okuma: tanımlanmış adlar ve sayfalar arası formüller motorun gerçekleştirdiği ad çözümlemesini kapsar ve CSV ve TSV dışa aktarma makalesi açık hesaplamayı gerekli kılan dışa aktarma davranışını ayrıntılandırır. Desteklenen işlev seti de dahil olmak üzere tam motor referansı HotXLS Bileşeni ile birlikte gönderilmektedir