Een Excel-tabel waarvan de kolom Amount =[@Qty]*[@Price] bevat, slaat [@Qty] nergens op in het opgeslagen bestand. Pak het werkboek uit en het element <f> in de sheet-XML laat zien wat Excel werkelijk schrijft: de lange, tabelgekwalificeerde spelling Lines[[#This Row],[Qty]]*Lines[[#This Row],[Price]], want de grammatica van de formuletaal in ECMA-376 Part 1, §18.17 heeft een trefwoord #This Row en geen @. Een samenvattende cel verder naar beneden bevat SUM(Lines[Amount]). Geen van beide tekenreeksen bevat ook maar één celadres, dus een engine die alleen A1-referenties parst heeft niets te rekenen totdat hij weet waar de tabel zit, welke rij de koppen draagt en op welke rij de formule zelf woont
Dat laatste stukje informatie is het onhandige. Een kolomreferentie kan één keer per tabel in een rechthoek worden omgezet. Een huidige-rijreferentie niet; hij betekent een andere cel voor elke rij waar hij naar wordt gekopieerd
Waarom is een gestructureerde referentie niet gewoon een bereik met een vriendelijke naam?
Een gedefinieerde naam bevriest een adres, en een tabelreferentie niet. Wijs de naam DataBlock naar Sheet1!$A$2:$D$100 en hij blijft die rechthoek tot iets hem herschrijft. Sales[Amount] betekent "de kolom Amount van de tabel Sales", wat de omvang van de tabel ook toevallig is op het moment dat de formule wordt geëvalueerd. Voeg twintig rijen aan de tabel toe en de som dekt ze mee; er is geen referentie bij te stellen want de formule heeft nooit een adres vastgehouden
HotXLS resolveert deze referenties elke keer dat hij een formule compileert voor evaluatie. TXLSXWorkbook zoekt de tabel op naam in het tabelmodel van het werkboek (TXLSXTable.Name of DisplayName, hoofdletterongevoelig vergeleken), matcht de kolom tegen de koptekst die in TXLSXTable.Columns is bewaard, past de itemspecificator toe om de rijband te kiezen, en reikt de compiler een absoluut bereik aan zoals 'Sales'!$D$2:$D$25. De herschreven tekst bestaat alleen voor die compilatie. De cel houdt de formule precies zoals hij geschreven was, en omdat de opzoekactie tegen TXLSXTable.Range loopt zoals die op herberekenmoment is, evalueert een formule die vóór de groei van de tabel werd geschreven nog steeds tegen de grotere tabel
De rechthoekgrammatica: kolommen, spans en itemspecificatoren
Elke tabelbrede vorm die HotXLS evalueert levert één aaneengesloten blok op. De geaccepteerde spellingen zijn [Col] en zijn tussen-haakjes-tweeling [[Col]], de kale itemspecificatoren [#Data], [#All], [#Headers] en [#Totals], de gecombineerde vorm [[#Data],[Col]], een span binnen een itemspecificator zoals [[#Data],[Col1]:[Col2]], en een kaal span [[Col1]:[Col2]]. Een span mag van rechts naar links worden geschreven; [[Q2]:[Q1]] resolveert naar dezelfde twee kolommen als [[Q1]:[Q2]]
De rijband die elke specificator kiest volgt twee vlaggen op de tabel. TXLSXTable.HasHeaderRow beslist of de eerste rij van Range een kop is, en TXLSXTable.TotalsRowShown beslist of de laatste rij een totalenrij is. Een kale kolomreferentie en [#Data] dekken de rijen tussen die twee. [#All] neemt de volledige Range, kop en totalen inbegrepen. Vraag [#Headers] op een tabel zonder koprij, [#Totals] op een tabel zonder totalenrij, of een kolom van een tabel waarvan de body geen rijen heeft, en de referentie wordt #REF!. Dat matcht wat Excel voor hetzelfde verzoek toont, en het houdt een onmogelijke referentie tegen stilletjes te veranderen in een leeg bereik dat tot nul sommeert
Kolomnamen die haakjes, een hekje, een apenstaartje of een apostrof bevatten volgen de escape-regel van Excel: elk zo'n teken krijgt binnen de referentie een apostrof als prefix, dus een kop die Unit [USD] heet wordt geschreven als Sales[Unit '[USD']]. De vormen van één kolom en huidige rij decoderen die escapes vóór het matchen. Spans en de gecombineerde vormen [#Data],[Col] resolven tegen tabellen op het eigen blad van de formule, dus een samenvattingsblad dat een span over de tabel van een ander blad nodig heeft, wordt beter geholpen met een kale kolomreferentie per kolom
Het voorbeeld hieronder bouwt een verkooptabel van 24 rijen en plaatst vijf samenvattingsformules ernaast in F2 tot F6, op hetzelfde blad als de tabel. Rij 1 is de koprij, rijen 2 tot 25 zijn de body, en er is geen totalenrij, dus SalesTable[#All] beslaat 25 rijen terwijl SalesTable[Amount] er 24 beslaat
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 koprij en 24 datarijen ...
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;
Verwachte waarden voor die indeling: F5 is 25 en F6 is 4, één telling per kopcel. F2 tot F4 hangen af van de data die u schrijft
Hoe weet [@Column] welke rij hij moet lezen?
Een huidige-rijreferentie neemt zijn rij uit de cel die wordt geëvalueerd. Wanneer Recalculate een formule bereikt die Lines[@Qty] bevat in rij 7, resolveert HotXLS hem naar de kolom Qty van rij 7; dezelfde formulertekst in rij 8 leest rij 8. De lange spelling die Excel op schijf schrijft, Lines[[#This Row],[Qty]], wordt als dezelfde referentie behandeld, en Lines[@[Unit Price]] voor een kop met een spatie ook
De niet-gekwalificeerde vorm [@Qty], zonder tabelnaam ervoor, heeft nog één feit nodig: bij welke tabel hij hoort. HotXLS beantwoordt dat op positie. Zit de evaluerende cel binnen de bodyrijen en het kolomspan van een tabel op hetzelfde blad, dan levert die tabel de kolom, en binnen zo'n tabel wordt [@[Q1]:[Q2]] een bereik van één rij over die kolommen. Overal anders blijft de referentie onresolved. Een gedefinieerde naam waarvan de formule een huidige-rijreferentie gebruikt, pakt zijn rij uit de cel die naar de naam verwijst, wat hetzelfde gedrag is dat een formule inline zou krijgen
Houd huidige-rijformules op de eigen bodyrijen van de tabel, en kwalificeer ze met de tabelnaam wanneer uw code ze genereert. Gekwalificeerde referenties overleven een verhuizing, ze lezen zoals Excel ze opslaat, en een formule op de eigen rijen van de tabel is het ene geval waarin Excel en elke andere consument van het bestand het eens zijn over wat @ betekent
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);
// ... schrijf de koprij en artikelen, aantallen en prijzen in rijen 2..13 ...
// Eén formulertekst, twaalf verschillende resultaten: elke cel leest zijn eigen rij
for Row := 2 to 13 do
Sheet.Cells[Row, 4].Formula := 'Lines[@Qty]*Lines[@Price]';
// Kruiscontrole onder de tabel: kolomtotaal tegen een herberekend product
Sheet.Cells[15, 4].Formula :=
'SUM(Lines[Amount])-SUMPRODUCT(Lines[Qty],Lines[Price])';
Book.Recalculate;
// Verwacht: D15 evalueert naar 0 wanneer elke Amount-cel matcht met Qty * Price
Book.SaveAs('orders.xlsx');
finally
Cols.Free;
Book.Free;
end;
end;
De controle in D15 is goedkoop en het is de moeite waard hem in een gegenereerd rapport te laten staan terwijl het wordt gebouwd. Een per-rijformule die de verkeerde rij pakte, zou de twee totalen laten uiteenlopen, en het verschil verschijnt als een getal ongelijk nul in een cel die iedereen kan lezen
Wat gebeurt er wanneer de tabel van vorm verandert
Groei is het makkelijke geval, mits de tabeldefinitie echt meegroeit. Een waarde in rij 26 schrijven onder een tabel waarvan Range A1:D25 is, maakt rij 26 geen deel van de tabel. Auto-expansie is een bewerkingsfeature van Excel die wordt getriggerd door iemand die naast de tabel typt, en cellen schrijven via HotXLS zet hem niet af. Stel TXLSXTable.Range na een import in op de nieuwe omvang, of voeg de rijen binnen de tabel in met TXLSXWorksheet.InsertRows, wat Tables[i].Range meeverschuift met elke andere referentie op het blad. Hoe dan ook verbreedt [#Data] en verandert er geen enkele formule
Krimpen is waar referenties kunnen breken, en HotXLS volgt de regel van Excel. DeleteCols over een tabelkolom herschrijft elke gestructureerde referentie die die kolom benoemt naar #REF!, in celformules, voorwaardelijke opmaak, datavalidatieregels, diagramseries en gedefinieerde namen. DeleteRows dat de koprij verwijdert, verwijdert de tabel en maakt zijn referenties op dezelfde manier ongeldig, en een verwijdering van de totalenrij maakt TotalsRowShown leeg, waarna [#Totals] geen rij meer heeft om naar te wijzen. Gewone A1-referenties gaan door dezelfde aanpassing, beschreven in formulereferentieaanpassing bij invoegen en verwijderen
TXLSXTables.DeleteByName en hernoemen via TXLSXTable.Name werken alleen op het tabelmodel. Formules die de oude naam noemen houden hun tekst, dus hernoem die formules in dezelfde doorgang wanneer uw code een tabel hernoemt
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]; // werkbladen zijn 1-based
Table := Sheet.Tables.FindByName('SalesTable');
if Table = nil then Exit;
// ... schrijf 20 geïmporteerde rijen in A26:D45 ...
Table.Range := 'A1:D45'; // de body loopt nu van rij 2 tot rij 45
Book.Recalculate; // SUM(SalesTable[Amount]) in F2 dekt nu 44 rijen
// Recordset-achtige opzoekactie over de tabelbody, rijresultaat 1-based, 0 = geen
Row := Table.FindFirst(Sheet, 'Region', 'EMEA');
while Row > 0 do
begin
Log(VarToStr(Sheet.Cells[Row, 4].Value)); // Log is uw eigen routine
Row := Table.FindNext(Sheet, 'Region', 'EMEA', Row);
end;
Book.SaveAs('sales.xlsx');
finally
Book.Free;
end;
end;
FindFirst en FindNext vergelijken tekenreeksen hoofdletterongevoelig en getallen op waarde, en ze scannen alleen de bodyrijen, dus de koptekst Region wordt nooit als match teruggegeven
Round-trip-discipline
Een werkboek dat met SUM(SalesTable[Amount]) wordt geladen, wordt met SUM(SalesTable[Amount]) opgeslagen, en een cel met Lines[[#This Row],[Qty]] gaat eruit met die tekst intact. Het geresolveerde adres vervangt nooit wat de gebruiker schreef. Zou HotXLS in plaats daarvan SUM($D$2:$D$25) opslaan, dan zou het bestand in Excel prima openen en daarna stoppen met het dekken van nieuwe rijen zodra iemand de tabel uitbreidt, en dat is het traagste soort breuk om terug te herleiden naar zijn oorzaak
Tabeldefinities zelf, inclusief tabellen zonder kop en per-tabel opmerkingen, round-trippen door het tabelmodel dat in datavalidatie, AutoFilter en Excel-tabellen wordt behandeld. Wanneer veel cellen één formulepatroon delen, kan XLSX het één keer bewaren als een gedeelde formule, en de uitbreiding van die cellen wordt behandeld in de uitbreiding van gedeelde formules (si). Een berekende kolom die Excel opslaat komt vaak op die manier binnen, dus een gestructureerde referentie in een tabelbody passeert beide routes voordat hij wordt geëvalueerd: de gedeelde formule breidt eerst uit naar per-cel tekst, daarna resolveert elke cel zijn eigen rij
Het tabelmodel, de Recalculate-engine en de hier gebruikte insert- en delete-aanroepen op werkbladniveau maken deel uit van HotXLS, dat XLS, XLSX en ODS leest en schrijft vanuit Delphi en C++Builder zonder Excel-installatie. De volledige API-referentie staat op de HotXLS Delphi-spreadsheetcomponentpagina