HotXLS teď vyhodnocuje strukturované odkazy na tabulky, takže =SUM(Table1[Amount]) vyprodukuje číslo místo toho, aby se přeskočil. Resolver zvládá Table[Column], Table[[Column]], rozsahy sloupců jako Table[[Q1]:[Q4]] a specifikátory položek [#Data], [#All], [#Headers] a [#Totals], přičemž každý z nich řeší proti modelu tabulek sešitu v době parsování, zatímco původní text vzorce se při zápisu a zpětném čtení zachová doslovně
Jedna forma záměrně chybí, a je to přesně ta, na kterou lidé narazí jako první. Zkrácený zápis aktuálního řádku [@Column] není podporovaný, a to ze strukturálního důvodu, který stojí za pochopení, ne za slepé obcházení
Proč strukturovaný odkaz není jen rozsah s přívětivým jménem?
Protože definovaný název zmrazuje adresu, zatímco odkaz na tabulku ne. Zapíšete-li DataBlock jako název ukazující na Sheet1!$A$2:$D$100, zůstane tímto obdélníkem, dokud ho něco nepřepíše. Zapíšete-li Sales[Amount], znamená to „sloupec Amount tabulky Sales", ať už je rozsah této tabulky v okamžiku vyhodnocení vzorce jakýkoli. Přidáte-li do tabulky dvacet řádků, součet je pokryje; není co upravovat, protože ve vzorci od začátku žádná adresa nebyla
Právě tato symbolická povaha je důvod, proč odkaz nelze vyřešit substitucí řetězců. Resolver musí najít tabulku podle jména v sešitu, vyhledat sloupec podle textu jeho záhlaví, rozhodnout, které řádky pokrývá požadovaný specifikátor položky, a vyprodukovat konkrétní obdélník. HotXLS to dělá během kompilace vzorce přes model tabulek, a proto se vzorec napsaný před tím, než tabulka narostla, i tak vyhodnocuje proti aktuálnímu rozsahu tabulky
Gramatika, kterou HotXLS řeší
Podporovaná gramatika specifikátoru pokrývá jediný obdélníkový výsledek a stojí za to ji vyjádřit přesně, protože dokumentace Excelu prezentuje mnohem širší plochu, než kterou implementuje většina enginů. HotXLS přijímá [Col] i variantu v hranatých závorkách [[Col]], holé specifikátory položek [#Data], [#All], [#Headers] a [#Totals], kombinovanou formu [[#Data],[Col]], rozsah uvnitř specifikátoru položky jako [[#Data],[Col1]:[Col2]] a obyčejný rozsah [Col1]:[Col2]
Tato sada vám dává každý tvar odkazu, který vyprodukuje jeden souvislý blok: sloupec, řadu sousedících sloupců, výřez zahrnující jen tělo nebo i záhlaví u kteréhokoli z nich. Nesousedící sjednocení a výsledky z více oblastí jsou mimo ni. Když odkaz nelze vyřešit, vzorec si zachovává předchozí chování „přeskočit bez hodnoty" místo dosazení odhadu, takže nevyřešitelný odkaz se nikdy nestane věrohodně vyhlížejícím špatným číslem
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);
// ... zapište záhlaví a 24 řádků dat ...
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;Proč je forma aktuálního řádku záměrně vyloučena?
[@Column] a [#This Row] znamenají „buňka daného sloupce na řádku, kde tento vzorec žije". Hodnota tedy závisí na pozici vyhodnocované buňky, ne jen na tabulce. To je jiný druh odkazu: ne obdélník, který kompilátor vyřeší jednou, ale řešení po jednotlivých buňkách, které se musí opakovat pro každý řádek, na kterém vzorec sedí
HotXLS pro tyto formy vrátí z resolveru rozsahu tabulky False, což je nasměruje na cestu „přeskočit bez hodnoty". Text vzorce se zachová a zapíše zpět beze změny, takže sešit, který používá [@Amount], se po průchodu vaší aplikací v Excelu otevře správně; chybí jen hodnota vypočtená HotXLS. Máte-li na výběr mezi chybějící hodnotou a hodnotou spočítanou proti špatnému řádku, chybějící hodnotu dokážete odhalit
Praktický obchvat je mechanický: v sešitu, který generujete, zapište ekvivalentní relativní odkaz ve stylu A1, což si Excel interně ukládá stejně pro velkou část logiky vázané na tabulku. V sešitu, který jen zpracováváte, nechte vzorec být a přečtěte si cachovanou hodnotu, kterou Excel už uložil, což je obvykle to, co chce pipeline typu načíst a nahlásit
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
// vyhledávání ve stylu recordsetu nad tělem tabulky, výsledek řádku je 1-based
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;Co se stane, když tabulka změní tvar
Strukturované odkazy se zneplatní, ne tiše přesměrují, když věc, kterou pojmenovávají, zmizí. Smažete-li sloupec, vzorce odkazující na tento sloupec se zneplatní stejným způsobem jako v Excelu; smažete-li nebo přejmenujete tabulku, odkazy na ni se řeší stejně. Toto je správné chování a zrcadlí běžnou úpravu odkazů popsanou v článku o úpravě odkazů ve vzorcích při vkládání a mazání, kde je úkolem enginu udržet vzorce poctivé, ne udržet je vypadající platně
Růst počtu řádků je opačný případ a nepotřebuje žádnou úpravu vůbec. Protože odkaz pojmenovává tabulku, ne obdélník, přidání řádků dovnitř rozsahu tabulky rozšíří to, co pokrývá [#Data], aniž by se dotklo jediného vzorce. Právě tato vlastnost dělá tabulky užitečné v šabloně reportu: řádek celkových součtů dál sčítá všechno, co import vyprodukoval, ať už to bylo řádků kolikkoli
Disciplína zápisu a zpětného čtení
HotXLS zachovává původní text vzorce. Sešit načtený s SUM(SalesTable[Amount]) se uloží s SUM(SalesTable[Amount]), ne s vyřešeným SUM(D2:D25). To má větší význam, než se může zdát: uživatel, který otevře váš výstup v Excelu, očekává, že uvidí vzorec, který napsal, a vyřešená adresa by tiše proměnila samoudržovací model v křehký model, který přestane pokrývat nové řádky
Obraz doplňují dvě související vlastnosti. Samotné definice tabulek, včetně tabulek bez záhlaví a komentářů pro jednotlivé tabulky, se zapisují a zpětně čtou přes model tabulek popsaný v článku o validaci dat, AutoFilter a tabulkách Excelu. A když si mnoho buněk sdílí jeden vzor, XLSX je uloží jednou jako sdílený vzorec, který se rozbaluje a znovu vydává, jak popisuje článek o rozbalení si sdíleného vzorce. Strukturované odkazy uvnitř sdílených vzorců procházejí oběma cestami, takže se obě musí chovat správně, a taky se chovají
HotXLS čte a zapisuje XLS, XLSX a ODS z Delphi a C++Builder bez instalace Excelu a bez jakékoli automatizace Office a vzorce vyhodnocuje ve vlastním enginu. Model tabulek, engine vzorců a API pro přepočet jsou zdokumentované na stránce HotXLS Delphi spreadsheet component