HotXLS sada evaluira strukturirane referencije tablica, pa =SUM(Table1[Amount]) proizvodi broj umjesto da bude preskočen. Razrješivač obrađuje Table[Column], Table[[Column]], raspone stupaca poput Table[[Q1]:[Q4]] te specifikatore stavki [#Data], [#All], [#Headers] i [#Totals], razrješavajući svaki od njih prema modelu tablice radne knjige u trenutku parsiranja, dok se izvorni tekst formule doslovno vraća nepromijenjen
Jedan je oblik namjerno izostavljen, i upravo je onaj na koji ljudi prvi naiđu. Kratica trenutnog retka [@Column] nije podržana, iz strukturnog razloga koji vrijedi razumjeti, a ne slijepo zaobilaziti
Zašto strukturirana referenca nije samo raspon s prijateljskim imenom?
Zato što definirano ime zamrzava adresu, a referenca tablice ne. Napišite DataBlock kao ime koje pokazuje na Sheet1!$A$2:$D$100 i ono ostaje taj pravokutnik dok ga nešto ne prepiše. Napišite Sales[Amount] i to znači „stupac Amount tablice Sales”, koji god opseg ta tablica imala u trenutku kada se formula evaluira. Dodajte tablici dvadeset redaka i zbroj će ih pokriti; nema reference koju treba prilagoditi jer u formuli nikad nije ni postojala adresa
Upravo je ta simbolička kvaliteta razlog zašto se referenca ne može razriješiti zamjenom niza znakova. Razrješivač mora pronaći tablicu po imenu u radnoj knjizi, potražiti stupac po tekstu njegovog zaglavlja, odlučiti koje retke pokriva traženi specifikator stavke i proizvesti konkretan pravokutnik. HotXLS to čini tijekom kompajliranja formule kroz model tablice, zbog čega se formula napisana prije nego što je tablica narasla i dalje evaluira prema trenutnom opsegu tablice
Gramatika koju HotXLS razrješava
Podržana gramatika specifikacije pokriva jedan pravokutni rezultat i vrijedi je precizno navesti, jer Excelova dokumentacija predstavlja mnogo širu površinu nego što većina mehanizama implementira. HotXLS prihvaća [Col] i varijantu u dvostrukim zagradama [[Col]], gole specifikatore stavki [#Data], [#All], [#Headers] i [#Totals], kombinirani oblik [[#Data],[Col]], raspon unutar specifikatora stavke kao [[#Data],[Col1]:[Col2]], te obični raspon [Col1]:[Col2]
Taj vam skup daje svaki oblik reference koji proizvodi jedan susjedni blok: stupac, niz susjednih stupaca, isječak samo tijela ili isječak koji uključuje zaglavlje bilo kojeg od njih. Nesusjedne unije i rezultati s više područja izvan su tog skupa. Kada se referenca ne može razriješiti, formula zadržava prethodno ponašanje preskoči-bez-vrijednosti umjesto da zamijeni pogodak, tako da nerazrješiva referenca nikada ne postane uvjerljiv pogrešan broj
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);
// ... zapišite redak zaglavlja i 24 retka podataka ...
Sheet.Cells[27, 4].Formula := 'SUM(SalesTable[Amount])';
Sheet.Cells[28, 4].Formula := 'SUM(SalesTable[[Q1]:[Q2]])';
Sheet.Cells[29, 4].Formula := 'COUNTA(SalesTable[[#Data],[Region]])';
Sheet.Cells[30, 4].Formula := 'ROWS(SalesTable[#All])';
Book.Recalculate;
Book.SaveAs('sales.xlsx');
finally
Cols.Free;
Book.Free;
end;
end;Zašto je oblik trenutnog retka namjerno isključen?
[@Column] i [#This Row] znače „ćelija tog stupca na retku na kojem se ova formula nalazi”. Vrijednost stoga ovisi o poziciji ćelije koja se evaluira, ne samo o tablici. To je drugačija vrsta reference: nije pravokutnik koji kompajler može razriješiti jednom, već razrješavanje po ćeliji koje se mora ponoviti za svaki redak koji formula zauzima
HotXLS za te oblike vraća False iz razrješivača raspona tablice, što ih usmjerava u putanju preskoči-bez-vrijednosti. Tekst formule čuva se i zapisuje natrag nepromijenjen, pa se radna knjiga koja koristi [@Amount] ispravno otvara u Excelu nakon prolaska kroz vašu aplikaciju; nedostaje samo vrijednost izračunata od strane HotXLS-a. Kada birate između odsutne vrijednosti i vrijednosti izračunate za pogrešan redak, odsutnost je ona koju možete otkriti
Praktično zaobilaženje je mehaničko: u radnoj knjizi koju sami generirate, napišite ekvivalentnu relativnu referencu u A1 stilu, što Excel interno pohranjuje ionako za velik dio logike vezane uz tablicu. U radnoj knjizi koju samo obrađujete, ostavite formulu na miru i pročitajte predmemoriranu vrijednost koju je Excel već pohranio, što je obično ono što pipeline za učitavanje i izvještavanje želi
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];
Table := Sheet.Tables.FindByName('SalesTable');
if Table <> nil then
begin
// Pretraga u stilu recordseta nad tijelom tablice, rezultat retka indeksiran od 1
Row := Table.FindFirst(Sheet, 'Region', 'EMEA');
while Row > 0 do
begin
Log(VarToStr(Sheet.Cells[Row, 4].Value));
Row := Table.FindNext(Sheet, 'Region', 'EMEA', Row);
end;
end;
finally
Book.Free;
end;
end;Što se događa kada tablica promijeni oblik
Strukturirane reference se poništavaju umjesto da se tiho preusmjere kada stvar koju imenuju nestane. Obrišite stupac i formule koje referenciraju taj stupac poništavaju se na isti način na koji ih poništava Excel; obrišite ili preimenujte tablicu i reference na nju obrađuju se jednako. To je ispravno ponašanje i odražava uobičajeno prilagođavanje referenci, opisano u članku prilagođavanje referenci formula pri umetanju i brisanju, gdje je posao mehanizma održati formule iskrenima, a ne da izgledaju valjano
Rast broja redaka suprotan je slučaj i uopće ne zahtijeva prilagodbu. Budući da referenca imenuje tablicu, a ne pravokutnik, dodavanje redaka unutar raspona tablice širi ono što [#Data] pokriva, bez diranja ijedne formule. To je svojstvo zbog kojeg se tablice isplati koristiti u predlošku izvještaja: redak ukupnih vrijednosti nastavlja zbrajati sve što je uvoz proizveo, koliko god redaka to na kraju bilo
Disciplina kružnog ciklusa
HotXLS čuva izvorni tekst formule. Radna knjiga učitana s SUM(SalesTable[Amount]) sprema se sa SUM(SalesTable[Amount]), a ne s razriješenim SUM(D2:D25). To znači više nego što se čini: korisnik koji otvori vaš izlaz u Excelu očekuje vidjeti formulu koju je napisao, a razriješena adresa tiho bi pretvorila samoodrživi model u krhak koji prestaje pokrivati nove retke
Dvije povezane mogućnosti upotpunjuju sliku. Same definicije tablica, uključujući tablice bez zaglavlja i komentare po pojedinoj tablici, čuvaju se kroz kružni ciklus modela tablice opisanog u članku validacija podataka, AutoFilter i Excel tablice. A kada mnogo ćelija dijeli jedan obrazac, XLSX ih pohranjuje jednom kao dijeljenu formulu, koja se proširuje i ponovno emitira, kako je obrađeno u članku proširenje si dijeljene formule. Strukturirane reference unutar dijeljenih formula prolaze kroz obje putanje, pa se obje moraju ispravno ponašati, i ponašaju se
HotXLS čita i piše XLS, XLSX i ODS iz Delphija i C++Buildera bez instaliranog Excela i bez automatizacije Officea, evaluirajući formule u vlastitom mehanizmu. API za model tablice, mehanizam formula i ponovni izračun dokumentiran je na stranici HotXLS Delphi komponente za proračunske tablice