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
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
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
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