HotXLS evaluează acum referințe structurate de tabel, așa că =SUM(Table1[Amount]) produce un număr în loc să fie sărit. Rezolvatorul gestionează Table[Column], Table[[Column]], extinderi de coloane precum Table[[Q1]:[Q4]], și specificatorii de item [#Data], [#All], [#Headers] și [#Totals], rezolvând fiecare față de modelul de tabel al registrului de lucru la momentul parsării, în timp ce textul original al formulei se întoarce nemodificat
O formă este absentă în mod deliberat, și este cea pe care oamenii o întâlnesc primii. Prescurtarea current-row [@Column] nu este suportată, dintr-un motiv structural care merită înțeles, nu ocolit orbește
De ce nu este o referință structurată doar un interval cu un nume prietenos?
Pentru că un nume definit înghețe o adresă, iar o referință de tabel nu. Scrie DataBlock ca un nume care indică spre Sheet1!$A$2:$D$100 și rămâne acel dreptunghi până când ceva îl rescrie. Scrie Sales[Amount] și înseamnă „coloana Amount a tabelului Sales”, oricare ar fi întinderea acelui tabel în momentul în care formula este evaluată. Adaugă douăzeci de rânduri la tabel și suma le acoperă; nu există nicio referință de ajustat pentru că nu a existat niciodată o adresă în formulă pentru început
Acea calitate simbolică este exact de ce referința nu poate fi rezolvată prin substituție de string. Rezolvatorul trebuie să găsească tabelul după nume în registrul de lucru, să caute coloana după textul header-ului ei, să decidă ce rânduri acoperă specificatorul de item cerut, și să producă un dreptunghi concret. HotXLS face asta în timpul compilării formulei prin modelul de tabel, motiv pentru care o formulă scrisă înainte ca tabelul să crească încă se evaluează față de întinderea curentă a tabelului
Gramatica pe care o rezolvă HotXLS
Gramatica de specificatori suportată acoperă un singur rezultat dreptunghiular și merită afirmată precis, pentru că documentația Excel prezintă o suprafață mult mai mare decât implementează majoritatea motoarelor. HotXLS acceptă [Col] și varianta cu paranteze [[Col]], specificatorii de item simpli [#Data], [#All], [#Headers] și [#Totals], forma combinată [[#Data],[Col]], o extindere în interiorul unui specificator de item ca [[#Data],[Col1]:[Col2]], și o extindere simplă [Col1]:[Col2]
Ce oferă acel set este fiecare formă de referință care produce un bloc contiguu: o coloană, o serie de coloane adiacente, o felie doar-corp sau care include header-ul a oricăreia dintre acestea. Uniunile neadiacente și rezultatele multi-zonă sunt în afara lui. Când o referință nu poate fi rezolvată, formula păstrează comportamentul anterior de sărire fără valoare, în loc să substituie o presupunere, așa că o referință nerezolvabilă nu devine niciodată un număr greșit plauzibil
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);
// ... scrie rândul de header și cele 24 de rânduri de date ...
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;De ce este forma current-row exclusă intenționat?
[@Column] și [#This Row] înseamnă „celula acelei coloane pe rândul pe care trăiește această formulă”. Valoarea depinde deci de poziția celulei care evaluează, nu doar de tabel. Aceasta este un tip diferit de referință: nu un dreptunghi pe care compilatorul îl poate rezolva o dată, ci o rezolvare per-celulă care trebuie refăcută pentru fiecare rând pe care îl ocupă formula
HotXLS returnează False din rezolvatorul de interval de tabel pentru acele forme, ceea ce le direcționează pe traseul de sărire fără valoare. Textul formulei este păstrat și scris înapoi nemodificat, așa că un registru de lucru care folosește [@Amount] se deschide corect în Excel după un round-trip prin aplicația ta; doar valoarea calculată de HotXLS lipsește. Având de ales între o valoare absentă și o valoare calculată față de rândul greșit, absența este cea pe care o poți detecta
Soluția practică este mecanică: într-un registru de lucru pe care îl generezi tu, scrie referința relativă echivalentă în stil A1, care este oricum ce stochează Excel intern pentru o mare parte din logica cu scop de tabel. Într-un registru de lucru pe care doar îl procesezi, lasă formula neatinsă și citește valoarea din cache pe care Excel a stocat-o deja, ceea ce de obicei își dorește un pipeline de încărcare și raportare
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
// Căutare stil recordset peste corpul tabelului, rezultat de rând bazat pe 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;Ce se întâmplă când tabelul își schimbă forma
Referințele structurate sunt invalidate, nu redirecționate silențios, atunci când dispare lucrul pe care îl numesc. Șterge o coloană și formulele care se referă la acea coloană sunt invalidate în modul în care Excel le invalidează; șterge sau redenumește tabelul și referințele către el sunt tratate la fel. Acesta este comportamentul corect și oglindește ajustarea obișnuită de referință, descrisă în ajustarea referinței de formulă la inserare și ștergere, unde treaba motorului este să mențină formulele oneste, nu să le mențină aparent valide
Creșterea de rânduri este cazul opus și nu are nevoie de nicio ajustare. Pentru că referința numește tabelul, nu un dreptunghi, adăugarea de rânduri în interiorul intervalului tabelului lărgește ce acoperă [#Data] fără să atingă vreo formulă. Aceasta este proprietatea care face tabelele demne de folosit într-un șablon de raport: rândul de totaluri continuă să însumeze tot ce a produs importul, oricâte rânduri s-ar dovedi a fi
Disciplina round-trip
HotXLS păstrează textul original al formulei. Un registru de lucru încărcat cu SUM(SalesTable[Amount]) este salvat cu SUM(SalesTable[Amount]), nu cu adresa rezolvată SUM(D2:D25). Asta contează mai mult decât ar putea părea: un utilizator care deschide output-ul tău în Excel se așteaptă să vadă formula pe care a scris-o, iar o adresă rezolvată ar converti silențios un model care se întreține singur într-unul fragil care încetează să mai acopere rânduri noi
Două capabilități conexe completează imaginea. Definițiile de tabel însele, inclusiv tabelele fără header și comentariile per-tabel, fac round-trip prin modelul de tabel descris în validarea datelor, AutoFilter și tabelele Excel. Și când multe celule împart un tipar, XLSX le stochează o singură dată ca formulă partajată, care este expandată și reemisă așa cum este acoperit în expandarea si a formulei partajate. Referințele structurate din interiorul formulelor partajate trec prin ambele trasee, așa că ambele trebuie să se comporte, și se comportă
HotXLS citește și scrie XLS, XLSX și ODS din Delphi și C++Builder fără nicio instalare de Excel și fără automatizare Office, evaluând formulele în propriul său motor. Modelul de tabel, motorul de formule și API-ul de recalculare sunt documentate pe pagina componentei HotXLS Delphi spreadsheet