HotXLS evaluerer nu strukturerede tabelreferencer, så =SUM(Table1[Amount]) producerer et tal i stedet for at blive sprunget over. Resolveren håndterer Table[Column], Table[[Column]], kolonnespænd såsom Table[[Q1]:[Q4]], og elementspecifikationerne [#Data], [#All], [#Headers] og [#Totals], hvor hver løses mod projektmappens tabelmodel ved parsetid, mens den originale formeltekst rundtures verbatim
Én form er bevidst fraværende, og det er den, folk rammer først. Current-row-forkortelsen [@Column] understøttes ikke, af en strukturel grund, det er værd at forstå frem for blindt at omgå
Hvorfor er en struktureret reference ikke bare et interval med et venligt navn?
Fordi et defineret navn fastfryser en adresse, og en tabelreference gør ikke. Skriv DataBlock som et navn, der peger på Sheet1!$A$2:$D$100, og det forbliver det rektangel, indtil noget omskriver det. Skriv Sales[Amount], og det betyder "Amount-kolonnen i Sales-tabellen", uanset hvad den tabels udstrækning måtte være, når formlen evalueres. Tilføj tyve rækker til tabellen, og summen dækker dem; der er ingen reference at justere, fordi der aldrig var en adresse i formlen til at begynde med
Den symbolske kvalitet er præcis grunden til, at referencen ikke kan løses ved strengsubstitution. Resolveren skal finde tabellen ved navn i projektmappen, slå kolonnen op ved dens overskriftstekst, beslutte, hvilke rækker den anmodede elementspecifikation dækker, og producere et konkret rektangel. HotXLS gør dette under formelkompilering gennem tabelmodellen, hvilket er grunden til, at en formel, skrevet før tabellen vokser, stadig evalueres mod tabellens nuværende udstrækning
Grammatikken, HotXLS løser
Den understøttede specifikationsgrammatik dækker et enkelt rektangulært resultat og er værd at angive præcist, fordi Excels dokumentation præsenterer en langt større flade, end de fleste motorer implementerer. HotXLS accepterer [Col] og den parenteserede variant [[Col]], de bare elementspecifikationer [#Data], [#All], [#Headers] og [#Totals], den kombinerede form [[#Data],[Col]], et spænd inden for en elementspecifikation som [[#Data],[Col1]:[Col2]], og et almindeligt spænd [Col1]:[Col2]
Det, det sæt giver dig, er enhver referenceform, der producerer én sammenhængende blok: en kolonne, en række af tilstødende kolonner, en krops-eller-overskrift-inklusiv skive af begge. Ikke-tilstødende foreninger og multi-area-resultater ligger uden for det. Når en reference ikke kan løses, bevarer formlen den tidligere spring-uden-værdi-adfærd frem for at erstatte med et gæt, så en uløselig reference aldrig bliver til et plausibelt forkert tal
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);
// ... write the header row and 24 data rows ...
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;Hvorfor er current-row-formen udelukket med vilje?
[@Column] og [#This Row] betyder "cellen i den kolonne på den række, hvor denne formel bor". Værdien afhænger derfor af den evaluerende celles position, ikke kun af tabellen. Det er en anden slags reference: ikke et rektangel, kompileren kan løse én gang, men en per-celle-løsning, der skal gøres om for hver række, formlen optager
HotXLS returnerer False fra tabel-interval-resolveren for de former, hvilket dirigerer dem ind i spring-uden-værdi-stien. Formelteksten bevares og skrives tilbage uændret, så en projektmappe, der bruger [@Amount], åbner korrekt i Excel efter en rundtur gennem din applikation; kun den HotXLS-beregnede værdi er fraværende. Givet valget mellem en fraværende værdi og en værdi, beregnet mod den forkerte række, er fravær den, du kan opdage
Den praktiske workaround er mekanisk: i en projektmappe, du genererer, skal du skrive den tilsvarende A1-stil relative reference, hvilket er, hvad Excel internt gemmer alligevel for en stor del af tabel-scopet logik. I en projektmappe, du blot behandler, lad formlen være, og læs den cachede værdi, Excel allerede gemte, hvilket er, hvad en indlæs-og-rapportér-pipeline normalt ønsker
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
// Recordset-agtigt opslag over tabelkroppen, 1-baseret rækkeresultat
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;Hvad sker der, når tabellen ændrer form
Strukturerede referencer invalideres frem for lydløst at blive omdirigeret, når det, de navngiver, forsvinder. Slet en kolonne, og formler, der refererer til den kolonne, invalideres, som Excel invaliderer dem; slet eller omdøb tabellen, og referencer til den håndteres på samme måde. Dette er den korrekte adfærd, og det spejler almindelig referencejustering, beskrevet i formelreferencejustering ved indsæt og slet, hvor motorens job er at holde formler ærlige frem for at få dem til at se gyldige ud
Rækkevækst er det modsatte tilfælde og kræver slet ingen justering. Fordi referencen navngiver tabellen frem for et rektangel, udvider tilføjelse af rækker inden for tabellens interval, hvad [#Data] dækker, uden at røre en eneste formel. Det er den egenskab, der gør tabeller værd at bruge i en rapportskabelon: totalrækken bliver ved med at summere alt, importen producerede, uanset hvor mange rækker det viste sig at være
Rundtur-disciplin
HotXLS beholder den originale formeltekst. En projektmappe, indlæst med SUM(SalesTable[Amount]), gemmes med SUM(SalesTable[Amount]), ikke med den løste SUM(D2:D25). Dette betyder mere, end det måske ser ud til: en bruger, der åbner dit output i Excel, forventer at se den formel, de skrev, og en løst adresse ville lydløst konvertere en selvvedligeholdende model til en skrøbelig en, der stopper med at dække nye rækker
To relaterede muligheder fuldender billedet. Selve tabeldefinitionerne, inklusive overskriftsløse tabeller og per-tabel-kommentarer, rundtures gennem tabelmodellen beskrevet i datavalidering, AutoFilter og Excel-tabeller. Og når mange celler deler ét mønster, gemmer XLSX dem én gang som en delt formel, som udfoldes og genudsendes, som dækket i delt formel si-udfoldelse. Strukturerede referencer inde i delte formler går gennem begge stier, så begge skal opføre sig, og det gør de
HotXLS læser og skriver XLS, XLSX og ODS fra Delphi og C++Builder uden nogen Excel-installation og uden Office-automatisering, idet formler evalueres i dens egen motor. Tabelmodellen, formelmotoren og genberegnings-API'en er dokumenteret på HotXLS' produktside for Delphi-regnearkskomponenten