HotXLS evalueert nu gestructureerde tabelverwijzingen, dus =SUM(Table1[Amount]) produceert een getal in plaats van overgeslagen te worden. De resolver behandelt Table[Column], Table[[Column]], kolomspans zoals Table[[Q1]:[Q4]], en de itemspecificaties [#Data], [#All], [#Headers] en [#Totals], en lost elk daarvan op tegen het tabelmodel van het werkboek tijdens het parseren, terwijl de originele formuletekst letterlijk behouden blijft
Eén vorm ontbreekt met opzet, en dat is degene die mensen als eerste tegenkomen. De huidige-rij-afkorting [@Column] wordt niet ondersteund, om een structurele reden die het waard is om te begrijpen in plaats van blindweg te omzeilen
Waarom is een gestructureerde verwijzing niet gewoon een bereik met een vriendelijke naam?
Omdat een gedefinieerde naam een adres bevriest en een tabelverwijzing dat niet doet. Schrijf DataBlock als een naam die naar Sheet1!$A$2:$D$100 wijst, en het blijft die rechthoek totdat iets het herschrijft. Schrijf Sales[Amount] en het betekent "de kolom Amount van de tabel Sales", wat de omvang van die tabel ook maar is op het moment dat de formule geëvalueerd wordt. Voeg twintig rijen toe aan de tabel en de som dekt ze; er is niets aan te passen omdat er nooit een adres in de formule stond om mee te beginnen
Die symbolische aard is precies waarom de verwijzing niet opgelost kan worden door stringvervanging. De resolver moet de tabel bij naam vinden in het werkboek, de kolom opzoeken bij haar koptekst, bepalen welke rijen de gevraagde itemspecificatie dekt, en een concrete rechthoek produceren. HotXLS doet dit tijdens formulecompilatie via het tabelmodel, wat verklaart waarom een formule geschreven vóórdat de tabel groeit, nog altijd evalueert tegen de huidige omvang van de tabel
De grammatica die HotXLS oplost
De ondersteunde specificatiegrammatica dekt één rechthoekig resultaat en is het waard om precies te benoemen, want Excels documentatie presenteert een veel groter oppervlak dan de meeste engines implementeren. HotXLS accepteert [Col] en de vorm met haakjes [[Col]], de kale itemspecificaties [#Data], [#All], [#Headers] en [#Totals], de gecombineerde vorm [[#Data],[Col]], een span binnen een itemspecificatie als [[#Data],[Col1]:[Col2]], en een gewone span [Col1]:[Col2]
Wat die verzameling u geeft, is elke verwijzingsvorm die één aaneengesloten blok oplevert: een kolom, een reeks aangrenzende kolommen, een deel dat alleen de body of ook de kop bevat. Niet-aangrenzende verenigingen en resultaten met meerdere gebieden vallen erbuiten. Wanneer een verwijzing niet opgelost kan worden, behoudt de formule het bestaande sla-over-zonder-waarde-gedrag in plaats van een gok te vervangen, zodat een onoplosbare verwijzing nooit een aannemelijk verkeerd getal wordt
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);
// ... schrijf de kopregel en 24 datarijen ...
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;Waarom is de huidige-rijvorm bewust uitgesloten?
[@Column] en [#This Row] betekenen "de cel van die kolom op de rij waar deze formule staat". De waarde hangt dus af van de positie van de evaluerende cel, niet alleen van de tabel. Dat is een ander soort verwijzing: geen rechthoek die de compiler eenmalig kan oplossen, maar een per-cel-oplossing die voor elke rij waarop de formule voorkomt opnieuw uitgevoerd moet worden
HotXLS geeft False terug uit de tabelbereik-resolver voor die vormen, wat ze naar het sla-over-zonder-waarde-pad leidt. De formuletekst blijft bewaard en wordt ongewijzigd teruggeschreven, dus een werkboek dat [@Amount] gebruikt, opent correct in Excel na een round-trip door uw applicatie; alleen de door HotXLS berekende waarde ontbreekt. Bij de keuze tussen een ontbrekende waarde en een waarde berekend tegen de verkeerde rij, is afwezigheid degene die u kunt detecteren
De praktische omweg is mechanisch: schrijf in een werkboek dat u zelf genereert de equivalente relatieve A1-verwijzing, wat Excel intern toch al opslaat voor een groot deel van de tabel-scoped logica. In een werkboek dat u slechts verwerkt, laat u de formule met rust en leest u de gecachte waarde die Excel al opgeslagen heeft, wat een load-and-report-pijplijn meestal wil
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
// Opzoeken in recordset-stijl over de tabelbody, 1-based rijresultaat
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;Wat er gebeurt wanneer de tabel van vorm verandert
Gestructureerde verwijzingen worden ongeldig gemaakt in plaats van stilzwijgend opnieuw gericht wanneer het ding dat ze benoemen verdwijnt. Verwijder een kolom en formules die naar die kolom verwijzen, worden ongeldig gemaakt zoals Excel ze ongeldig maakt; verwijder of hernoem de tabel en verwijzingen ernaar worden op dezelfde manier behandeld. Dit is het juiste gedrag en het spiegelt gewone verwijzingsaanpassing, beschreven in formuleverwijzingsaanpassing bij invoegen en verwijderen, waar de taak van de engine is om formules eerlijk te houden in plaats van ze er alleen geldig te laten uitzien
Rijgroei is het tegenovergestelde geval en heeft helemaal geen aanpassing nodig. Omdat de verwijzing de tabel benoemt in plaats van een rechthoek, verbreedt het toevoegen van rijen binnen het bereik van de tabel wat [#Data] dekt, zonder ook maar één formule aan te raken. Dat is de eigenschap die tabellen de moeite waard maakt in een rapportsjabloon: de totaalrij blijft alles optellen wat de import opleverde, hoeveel rijen dat ook bleken te zijn
Round-trip-discipline
HotXLS behoudt de originele formuletekst. Een werkboek geladen met SUM(SalesTable[Amount]) wordt opgeslagen met SUM(SalesTable[Amount]), niet met het opgeloste SUM(D2:D25). Dit is belangrijker dan het lijkt: een gebruiker die uw output in Excel opent, verwacht de formule te zien die hij schreef, en een opgelost adres zou een zelfonderhoudend model stilzwijgend omzetten in een broos model dat stopt met het dekken van nieuwe rijen
Twee verwante mogelijkheden maken het plaatje compleet. Tabeldefinities zelf, inclusief tabellen zonder kop en per-tabel-opmerkingen, gaan door een round-trip via het tabelmodel beschreven in gegevensvalidatie, AutoFilter en Excel-tabellen. En wanneer veel cellen één patroon delen, slaat XLSX ze eenmaal op als een gedeelde formule, die uitgebreid en opnieuw uitgevoerd wordt zoals behandeld in shared-formula-si-expansie. Gestructureerde verwijzingen binnen gedeelde formules doorlopen beide paden, dus beide moeten zich gedragen, en dat doen ze
HotXLS leest en schrijft XLS, XLSX en ODS vanuit Delphi en C++Builder zonder installatie van Excel en zonder Office-automatisering, en evalueert formules in zijn eigen engine. Het tabelmodel, de formule-engine en de herberekenings-API staan gedocumenteerd op de HotXLS Delphi spreadsheetcomponentpagina