Teknik Makale

HotXLS ile Delphi'de Excel Yapılandırılmış Tablo Başvuruları

Amount sütunu =[@Qty]*[@Price] tutan bir Excel tablosu, kaydedilen dosyanın hiçbir yerinde [@Qty] saklamaz. Çalışma kitabını açın (zip) ve sayfa XML'indeki <f> öğesi, Excel'in gerçekte ne yazdığını gösterir: uzun, tablo nitelemeli yazım Lines[[#This Row],[Qty]]*Lines[[#This Row],[Price]]; çünkü ECMA-376 Bölüm 1, §18.17'deki formül dilbilgisinin bir #This Row anahtar sözcüğü vardır ve @ yoktur. Aşağılarda bir özet hücresi SUM(Lines[Amount]) tutar. İki dizge de tek bir hücre adresi içermez; dolayısıyla yalnızca A1 başvurularını ayrıştıran bir motorun, tablonun nerede oturduğunu, hangi satırın başlıkları taşıdığını ve formülün kendisinin hangi satırda yaşadığını bilene kadar hesaplayacağı hiçbir şey yoktur

Son parça bilgi, mahcup olanıdır. Bir sütun başvurusu tablo başına bir kez dikdörtgene çevrilebilir. Geçerli satır başvurusu çevrilemez; kopyalandığı her satır için farklı bir hücre demektir

Yapılandırılmış bir başvuru neden dostane adlı bir aralıktan ibaret değildir?

Tanımlı bir ad bir adresi dondurur; tablo başvurusu dondurmaz. DataBlock adını Sheet1!$A$2:$D$100'e çevirin; bir şey onu yeniden yazana dek o dikdörtgen olarak kalır. Sales[Amount], "Sales tablosunun Amount sütunu" demektir — formül değerlendirildiğinde tablonun kapsamı her ne ise. Tabloya yirmi satır ekleyin ve toplam onları kapsar; ayarlanacak bir başvuru yoktur, çünkü formül baştan beri bir adres tutmamıştır

HotXLS bu başvuruları, değerlendirme için bir formül derlediği her zaman çözer. TXLSXWorkbook, tabloyu çalışma kitabının tablo modelinde ada göre arar (TXLSXTable.Name ya da DisplayName, büyük/küçük harfe duyarsız karşılaştırma), sütunu TXLSXTable.Columns'ta saklanan başlık metniyle eşler, satır bandını seçmek için öge belirtecini uygular ve derleyiciye 'Sales'!$D$2:$D$25 gibi mutlak bir aralık verir. Yeniden yazılan metin yalnızca o derleme için vardır. Hücre formülü tam olarak yazıldığı gibi korur ve arama, yeniden hesaplama anındaki haliyle TXLSXTable.Range'e karşı çalıştığı için tablo büyümeden önce yazılmış bir formül, daha büyük tabloya karşı değerlendirilmeye devam eder

HotXLS'nin Delphi'de SalesTable[Amount] yapılandırılmış referansını çözümleme şeması: tabloyu ada göre bulmak, Amount sütun başlığıyla eşlemek ve somut bir aralık elde etmek için #Data belirtecini uygulamak
HotXLS, Table[Column] başvurularını ayrıştırma anında tablo modeline karşı derler ve onları geçerli somut aralığa çevirir; böylece formül, tablo büyüdükçe onu izlemeye devam eder

Dikdörtgen dilbilgisi: sütunlar, kapsamlar ve öge belirteçleri

HotXLS'in değerlendirdiği her tablo geneli biçim tek bir bitişik blok üretir. Kabul edilen yazımlar [Col] ile köşeli ikizi [[Col]], çıplak öge belirteçleri [#Data], [#All], [#Headers] ve [#Totals], birleşik biçim [[#Data],[Col]], [[#Data],[Col1]:[Col2]] gibi bir öge belirteci içinde kapsam ve düz bir kapsam [[Col1]:[Col2]]'dir. Bir kapsam sağdan sola yazılabilir; [[Q2]:[Q1]], [[Q1]:[Q2]] ile aynı iki sütuna çözümlenir

Her belirtecin seçtiği satır bandı tablo üzerindeki iki bayrağı izler. TXLSXTable.HasHeaderRow, Range'in ilk satırının başlık olup olmadığına karar verir; TXLSXTable.TotalsRowShown, son satırın toplamlar satırı olup olmadığına. Düz bir sütun başvurusu ile [#Data] o ikisinin arasındaki satırları kapsar. [#All], başlık ve toplamlar dâhil tam Range'i alır. Başlık satırı olmayan bir tabloda [#Headers], toplamlar satırı olmayan bir tabloda [#Totals] ya da gövdesinde satır olmayan bir tablonun bir sütunu isteyin; başvuru #REF! olur. Bu, Excel'in aynı istek için gösterdiğiyle eşleşir ve imkânsız bir başvurunun, sıfıra toplanan boş bir aralığa sessizce dönüşmesini önler

Köşeli parantez, diyez, at işareti ya da kesme işareti içeren sütun adları Excel'in kaçış kuralını izler: böyle her karakter, başvurunun içinde bir kesme işaretiyle öneklenir; dolayısıyla Unit [USD] adlı bir başlık Sales[Unit '[USD']] yazılır. Tek sütunlu ve geçerli satır biçimleri eşleşmeden önce o kaçışları çözer. Kapsamlar ile birleşik [#Data],[Col] biçimleri, formülün kendi sayfasındaki tablolara karşı çözümlenir; öbür bir sayfanın tablosu üzerinde bir kapsam gerektiren bir özet sayfası için her sütun için düz bir sütun başvurusu daha iyidir

HotXLS'nin Delphi'de kabul ettiği yapılandırılmış-referans dil bilgisinin haritası: #Data, #Headers, #Totals ve #All biçimlerinin hangi tablo bölgelerini kapsadığını ve köşeli yazımların aynı dikdörtgenlere nasıl eşlendiğini gösterir
Her tablo geneli biçim, tablonun tek bir bitişik bandını kapsar ve köşeli varyantlar düz yazımlarıyla tam olarak aynı şekilde çözümlenir

Aşağıdaki örnek 24 satırlık bir satış tablosu kurar ve beş özet formülünü tabloyla aynı sayfada F2'den F6'ya yerleştirir. 1. satır başlık satırıdır, 2'den 25'e satırlar gövdedir ve toplamlar satırı yoktur; böylece SalesTable[#All] 25 satıra yayılırken SalesTable[Amount] 24'e yayılır

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Cols: TStringList;
begin
  Book := TXLSXWorkbook.Create;
  Cols := TStringList.Create;
  try
    Sheet := Book.Sheets.Add('Sales');
    Cols.Add('Region');
    Cols.Add('Q1');
    Cols.Add('Q2');
    Cols.Add('Amount');
    Sheet.Tables.Add('SalesTable', 'A1:D25', Cols);
    // ... başlık satırını ve 24 veri satırını yazın ...

    Sheet.Cells[2, 6].Formula := 'SUM(SalesTable[Amount])';
    Sheet.Cells[3, 6].Formula := 'SUM(SalesTable[[Q1]:[Q2]])';
    Sheet.Cells[4, 6].Formula := 'COUNTA(SalesTable[[#Data],[Region]])';
    Sheet.Cells[5, 6].Formula := 'ROWS(SalesTable[#All])';
    Sheet.Cells[6, 6].Formula := 'COUNTA(SalesTable[#Headers])';

    Book.Recalculate;
    Book.SaveAs('sales.xlsx');
  finally
    Cols.Free;
    Book.Free;
  end;
end;

O yerleşim için beklenen değerler: F5 25'tir ve F6 4'tür — başlık hücresi başına bir sayım. F2'den F4'e, yazdığınız verilere bağlıdır

[@Column] hangi satırı okuyacağını nasıl bilir?

Geçerli satır başvurusu, satırını değerlendirilmekte olan hücreden alır. Recalculate, 7. satırda Lines[@Qty] içeren bir formüle ulaştığında HotXLS onu 7. satırın Qty sütununa çözer; 8. satırdaki aynı formül metni 8. satırı okur. Excel'in diske yazdığı uzun yazım Lines[[#This Row],[Qty]] aynı başvuru sayılır; boşluk içeren bir başlık için Lines[@[Unit Price]] de öyledir

Önünde tablo adı olmayan niteliksiz [@Qty] biçimi bir olgu daha ister: hangi tabloya ait olduğuna. HotXLS bunu konumla yanıtlar. Değerlendirilen hücre, aynı sayfadaki bir tablonun gövde satırları ile sütun kapsamının içindeyse o tablo sütunu sağlar ve böyle bir tablonun içinde [@[Q1]:[Q2]], o sütunlar boyunca tek satırlık bir aralığa dönüşür. Başka her yerde başvuru çözümsüz bırakılır. Formülü geçerli satır başvurusu kullanan bir tanımlı ad, satırını ada atıf yapan hücreden alır; bir formülün satır içi olarak alacağı davranış da budur

Geçerli satır formüllerini tablonun kendi gövde satırlarında tutun ve kod onları üretirken tablo adıyla niteleyin. Niteli başvurular taşınmaya dayanır, Excel'in sakladığı şekilde okunur ve tablonun kendi satırlarındaki bir formül, Excel ile dosyanın diğer her tüketicisinin @'in ne anlama geldiğinde anlaştığı tek durumdur

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Cols: TStringList;
  Row: Integer;
begin
  Book := TXLSXWorkbook.Create;
  Cols := TStringList.Create;
  try
    Sheet := Book.Sheets.Add('Orders');
    Cols.Add('Item');
    Cols.Add('Qty');
    Cols.Add('Price');
    Cols.Add('Amount');
    Sheet.Tables.Add('Lines', 'A1:D13', Cols);
    // ... başlık satırını ve 2..13. satırlardaki ürünleri, miktarları, fiyatları yazın ...

    // Tek formül metni, on iki farklı sonuç: her hücre kendi satırını okur
    for Row := 2 to 13 do
      Sheet.Cells[Row, 4].Formula := 'Lines[@Qty]*Lines[@Price]';

    // Tablonun altında çapraz denetim: sütun toplamı, yeniden hesaplanmış çarpıma karşı
    Sheet.Cells[15, 4].Formula :=
      'SUM(Lines[Amount])-SUMPRODUCT(Lines[Qty],Lines[Price])';

    Book.Recalculate;
    // Beklenen: her Amount hücresi Qty * Price ile eşleştiğinde D15, 0 değerlenir
    Book.SaveAs('orders.xlsx');
  finally
    Cols.Free;
    Book.Free;
  end;
end;

D15'teki denetim ucuzdur ve üretilmekte olan bir raporda bırakmaya değer. Yanlış satırı yakalayan satır başına bir formül, iki toplamın çelişmesine yol açardı ve fark, herkesin okuyabileceği bir hücrede sıfır olmayan bir sayı olarak belirir

HotXLS'nin Delphi'de geçerli-satır biçimi [@Amount]'ı değerlendirilen hücrenin satırına karşı çözümleme akış şeması; formül metni değişmeden gidiş-dönüş yapar, böylece Excel kayıttan sonra aynı başvuruyu okur
Geçerli satır başvuruları hücre başına, formülün oturduğu satıra karşı çözümlenir ve kaydedilen dosya, Excel'in okuması için özgün başvuru metnini hâlâ taşır

Tablo şekil değiştirdiğinde ne olur

Büyüme, tablo tanımı gerçekten büyüdüğü sürece kolay durumdur. Range'i A1:D25 olan bir tablonun altındaki 26. satıra değer yazmak, 26. satırı tablonun parçası yapmaz. Otomatik genişleme, biri tablonun yanına yazarken tetiklenen bir Excel düzenleme özelliğidir ve HotXLS üzerinden hücre yazmak onu tetiklemez. İçe aktarmadan sonra TXLSXTable.Range'i yeni kapsama ayarlayın ya da satırları tablonun içine TXLSXWorksheet.InsertRows ile ekleyin; o da Tables[i].Range'i sayfadaki diğer her başvuruyla birlikte kaydırır. Her iki durumda da [#Data] genişler ve tek bir formül bile değişmez

Daralma, başvuruların kırılabildiği yerdir ve HotXLS Excel'in kuralını izler. Bir tablo sütunu üzerinde DeleteCols, o sütunu adlandıran her yapılandırılmış başvuruyu #REF!'e yeniden yazar — hücre formüllerinde, koşullu biçimlerde, veri doğrulama kurallarında, grafik serilerinde ve tanımlı adlarda. Başlık satırını kaldıran DeleteRows tabloyu siler ve başvurularını aynı şekilde geçersiz kılar; toplamlar satırını kaldıran ise TotalsRowShown'ı temizler, artık [#Totals]'ün göstereceği bir satır yoktur. Sıradan A1 başvuruları aynı ayarlamadan geçer; ekleme ve silmede formül başvuru ayarlaması yazısında anlatılmıştır

TXLSXTables.DeleteByName ile TXLSXTable.Name üzerinden yeniden adlandırma yalnızca tablo modeli üzerinde çalışır. Eski adı anan formüller metinlerini korur; kodunuz bir tabloyu yeniden adlandırdığında o formülleri de aynı geçişte yeniden adlandırın

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Table: TXLSXTable;
  Row: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    if Book.Open('sales.xlsx') <> 1 then Exit;
    Sheet := Book.Sheets[1];  // çalışma kitabı sayfaları 1 tabanlıdır

    Table := Sheet.Tables.FindByName('SalesTable');
    if Table = nil then Exit;

    // ... içe aktarılan 20 satırı A26:D45'e yazın ...
    Table.Range := 'A1:D45';  // gövde artık 2. satırdan 45. satıra kadar uzanır

    Book.Recalculate;  // F2'deki SUM(SalesTable[Amount]) artık 44 satırı kapsar

    // Tablo gövdesi üzerinde recordset tarzı arama, 1 tabanlı satır sonucu, 0 = yok
    Row := Table.FindFirst(Sheet, 'Region', 'EMEA');
    while Row > 0 do
    begin
      Log(VarToStr(Sheet.Cells[Row, 4].Value));  // Log sizin kendi yordamınızdır
      Row := Table.FindNext(Sheet, 'Region', 'EMEA', Row);
    end;

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

FindFirst ile FindNext dizeleri büyük/küçük harfe duyarsız, sayıları değere göre karşılaştırır ve yalnızca gövde satırlarını tarar; böylece Region başlık metni asla bir eşleşme olarak döndürülmez

Geri yazma disiplini

SUM(SalesTable[Amount]) ile yüklenen bir çalışma kitabı SUM(SalesTable[Amount]) ile kaydedilir ve Lines[[#This Row],[Qty]] tutan bir hücre o metin sağlam halde geri gider. Çözümlenen adres, kullanıcının yazdığını asla ikame etmez. HotXLS yerine SUM($D$2:$D$25) kaydetseydi dosya Excel'de iyi görünerek açılır, sonra biri tabloyu genişlettiği ilk anda yeni satırları kapsamayı bırakırdı; nedenine geri izlenmesi en yavaş kırılma türü budur

Tablo tanımlarının kendileri — başlıksız tablolar ve tablo başına yorumlar dâhil — veri doğrulama, AutoFilter ve Excel tabloları yazısında kapsanan tablo modeli üzerinden gidiş-dönüş yapar. Pek çok hücre tek bir formül kalıbını paylaştığında XLSX onu bir paylaşılan formül olarak bir kez saklayabilir ve o hücrelerin genişletilmesi paylaşılan formül si genişletilmesi yazısında kapsanır. Excel'in kaydettiği hesaplanan bir sütun çoğunlukla öyle gelir; dolayısıyla tablo gövdesindeki bir yapılandırılmış başvuru, değerlendirilmeden önce iki yoldan da geçer: paylaşılan formül önce hücre başına metne genişler, sonra her hücre kendi satırını çözer

Burada kullanılan tablo modeli, Recalculate motoru ve çalışma sayfası ekleme ve silme çağrıları HotXLS'in parçalarıdır; HotXLS, Excel kurulumu olmaksızın Delphi ve C++Builder'dan XLS, XLSX ve ODS okur ve yazar. Tam API referansı HotXLS Delphi elektronik tablo bileşeni sayfasında durur