HotXLS utvärderar nu strukturerade tabellreferenser, så =SUM(Table1[Amount]) ger ett tal i stället för att hoppas över. Upplösaren hanterar Table[Column], Table[[Column]], kolumnintervall som Table[[Q1]:[Q4]], och postspecifikationerna [#Data], [#All], [#Headers] och [#Totals], och löser var och en mot arbetsbokens tabellmodell vid tolkningstillfället medan den ursprungliga formeltexten går ordagrant tur och retur
En form saknas medvetet, och det är den folk stöter på först. Kortformen för aktuell rad, [@Column], stöds inte, av ett strukturellt skäl värt att förstå snarare än att kringgå blint
Varför är en strukturerad referens inte bara ett intervall med ett vänligt namn?
Därför att ett definierat namn fryser en adress medan en tabellreferens inte gör det. Skriv DataBlock som ett namn som pekar på Sheet1!$A$2:$D$100 och det förblir den rektangeln tills något skriver om det. Skriv Sales[Amount] och det betyder ”Amount-kolumnen i Sales-tabellen”, vad tabellens utsträckning än råkar vara när formeln utvärderas. Lägg till tjugo rader i tabellen och summan täcker dem; det finns ingen referens att justera eftersom det aldrig fanns någon adress i formeln från början
Den symboliska egenskapen är precis varför referensen inte kan lösas genom strängsubstitution. Upplösaren måste hitta tabellen efter namn i arbetsboken, slå upp kolumnen efter dess rubriktext, avgöra vilka rader den begärda postspecifikationen täcker, och producera en konkret rektangel. HotXLS gör detta under formelkompileringen genom tabellmodellen, vilket är varför en formel skriven innan tabellen växer ändå utvärderas mot tabellens aktuella utsträckning
Grammatiken HotXLS löser
Den stödda specifikationsgrammatiken täcker ett enda rektangulärt resultat och är värd att ange precist, eftersom Excels dokumentation presenterar en betydligt större yta än de flesta motorer implementerar. HotXLS accepterar [Col] och den inhägnade varianten [[Col]], de bara postspecifikationerna [#Data], [#All], [#Headers] och [#Totals], den kombinerade formen [[#Data],[Col]], ett intervall inuti en postspecifikation som [[#Data],[Col1]:[Col2]], och ett vanligt intervall [Col1]:[Col2]
Det den mängden ger dig är varje referensform som producerar ett enda sammanhängande block: en kolumn, en följd av intilliggande kolumner, en enbart-kropp- eller rubrik-inkluderande skiva av endera. Icke-intilliggande unioner och flerområdesresultat ligger utanför den. När en referens inte kan lösas behåller formeln det tidigare hoppa-över-utan-värde-beteendet i stället för att ersätta med en gissning, så en olöslig referens blir aldrig ett plausibelt fel 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);
// ... skriv rubrikraden och 24 datarader ...
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;Varför utesluts formen för aktuell rad medvetet?
[@Column] och [#This Row] betyder ”cellen i den kolumnen på raden där den här formeln lever”. Värdet beror därför på den utvärderande cellens position, inte bara på tabellen. Det är en annan sorts referens: inte en rektangel kompilatorn kan lösa en gång, utan en per-cell-upplösning som måste göras om för varje rad formeln upptar
HotXLS returnerar False från tabellintervallsupplösaren för de formerna, vilket dirigerar dem in i hoppa-över-utan-värde-vägen. Formeltexten bevaras och skrivs tillbaka oförändrad, så en arbetsbok som använder [@Amount] öppnas korrekt i Excel efter en tur och retur genom din applikation; bara det HotXLS-beräknade värdet saknas. Givet valet mellan ett frånvarande värde och ett värde beräknat mot fel rad är frånvaro det du kan upptäcka
Den praktiska lösningen är mekanisk: i en arbetsbok du genererar, skriv den motsvarande A1-relativa referensen, vilket är vad Excel ändå lagrar internt för en stor del av den tabellbundna logiken. I en arbetsbok du bara bearbetar, lämna formeln orörd och läs det cachade värde Excel redan lagrade, vilket är vad en läs-och-rapportera-pipeline vanligtvis vill ha
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-liknande uppslag över tabellkroppen, 1-baserat radresultat
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;Vad händer när tabellen ändrar form
Strukturerade referenser ogiltigförklaras i stället för att tyst peka om när det de namnger försvinner. Ta bort en kolumn och formler som refererar till den kolumnen ogiltigförklaras på samma sätt som Excel ogiltigförklarar dem; ta bort eller döp om tabellen och referenser till den hanteras på samma sätt. Det här är korrekt beteende och det speglar vanlig referensjustering, som beskrivs i formelreferensjustering vid infogning och borttagning, där motorns uppgift är att hålla formler ärliga snarare än att få dem att se giltiga ut
Radtillväxt är det motsatta fallet och behöver ingen justering alls. Eftersom referensen namnger tabellen snarare än en rektangel breddar tillagda rader inom tabellens intervall det [#Data] täcker utan att röra en enda formel. Det är egenskapen som gör tabeller värda att använda i en rapportmall: totalsummeraden fortsätter summera allt importen producerade, oavsett hur många rader det blev
Tur-och-retur-disciplin
HotXLS behåller den ursprungliga formeltexten. En arbetsbok inläst med SUM(SalesTable[Amount]) sparas med SUM(SalesTable[Amount]), inte med den upplösta SUM(D2:D25). Det spelar större roll än det kan verka: en användare som öppnar din utdata i Excel förväntar sig att se formeln de skrev, och en upplöst adress skulle tyst förvandla en självunderhållande modell till en skör en som slutar täcka nya rader
Två besläktade funktioner kompletterar bilden. Själva tabelldefinitionerna, inklusive rubrikslösa tabeller och per-tabell-kommentarer, går tur och retur genom tabellmodellen som beskrivs i datavalidering, AutoFilter och Excel-tabeller. Och när många celler delar ett mönster lagrar XLSX dem en gång som en delad formel, vilken expanderas och sänds ut igen enligt beskrivningen i delad-formel-si-expansion. Strukturerade referenser inuti delade formler går genom båda vägarna, så båda måste bete sig korrekt, och det gör de
HotXLS läser och skriver XLS, XLSX och ODS från Delphi och C++Builder utan Excel-installation och utan Office-automatisering, och utvärderar formler i sin egen motor. Tabellmodellen, formelmotorn och omräknings-API:et dokumenteras på sidan för HotXLS Delphi kalkylarkskomponent