HotXLS teraz vyhodnocuje štruktúrované referencie na tabuľky, takže =SUM(Table1[Amount]) vyprodukuje číslo namiesto toho, aby sa preskočil. Resolver zvláda Table[Column], Table[[Column]], rozsahy stĺpcov ako Table[[Q1]:[Q4]] a špecifikátory položiek [#Data], [#All], [#Headers] a [#Totals], pričom každý rieši voči modelu tabuliek zošita už pri parsovaní, zatiaľ čo pôvodný text vzorca sa zapíše späť doslovne
Jedna forma zámerne chýba, a je to práve tá, na ktorú ľudia narazia ako na prvú. Skratka aktuálneho riadku [@Column] nie je podporovaná, a to zo štrukturálneho dôvodu, ktorý stojí za pochopenie namiesto slepého obchádzania
Prečo štruktúrovaná referencia nie je len rozsah s priateľským menom?
Pretože definovaný názov zamrazí adresu, a referencia na tabuľku nie. Napíšte DataBlock ako názov ukazujúci na Sheet1!$A$2:$D$100 a zostane tým obdĺžnikom, kým to niečo neprepíše. Napíšte Sales[Amount] a znamená to „stĺpec Amount tabuľky Sales“, nech je rozsah tejto tabuľky akýkoľvek práve v čase vyhodnotenia vzorca. Pridajte do tabuľky dvadsať riadkov a súčet ich pokryje; niet žiadnu referenciu na úpravu, pretože vo vzorci od začiatku nikdy nebola žiadna adresa
Práve táto symbolická povaha je dôvodom, prečo sa referencia nedá vyriešiť substitúciou reťazcov. Resolver musí nájsť tabuľku podľa mena v zošite, vyhľadať stĺpec podľa textu jeho hlavičky, rozhodnúť, ktoré riadky pokrýva požadovaný špecifikátor položky, a vyprodukovať konkrétny obdĺžnik. HotXLS to robí počas kompilácie vzorca cez model tabuliek, a preto sa vzorec napísaný pred rastom tabuľky aj tak vyhodnotí voči aktuálnemu rozsahu tabuľky
Gramatika, ktorú HotXLS rieši
Podporovaná gramatika špecifikátorov pokrýva jeden obdĺžnikový výsledok a stojí za to ju uviesť presne, pretože dokumentácia Excelu prezentuje oveľa väčší rozsah, než väčšina enginov implementuje. HotXLS akceptuje [Col] a variant so zátvorkami [[Col]], holé špecifikátory položiek [#Data], [#All], [#Headers] a [#Totals], kombinovanú formu [[#Data],[Col]], rozsah vnútri špecifikátora položky ako [[#Data],[Col1]:[Col2]], a jednoduchý rozsah [Col1]:[Col2]
Táto množina vám dáva každý tvar referencie, ktorý vyprodukuje jeden súvislý blok: stĺpec, sériu susediacich stĺpcov, výrez len tela alebo výrez vrátane hlavičky pre ktorýkoľvek z nich. Nesusediace zjednotenia a viacoblastné výsledky sú mimo nej. Keď sa referencia nedá vyriešiť, vzorec si zachová predchádzajúce správanie preskočenia bez hodnoty namiesto dosadenia odhadu, takže nevyriešiteľná referencia sa nikdy nezmení na vierohodne vyzerajúce nesprávne číslo
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);
// ... zapíšte hlavičkový riadok a 24 riadkov dát ...
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;Prečo je forma aktuálneho riadku vylúčená zámerne?
[@Column] a [#This Row] znamenajú „bunka tohto stĺpca na riadku, kde tento vzorec žije“. Hodnota preto závisí od pozície vyhodnocovanej bunky, nielen od tabuľky. To je iný druh referencie: nie obdĺžnik, ktorý dokáže kompilátor vyriešiť raz, ale riešenie na úrovni jednotlivej bunky, ktoré sa musí zopakovať pre každý riadok, ktorý vzorec zaberá
HotXLS pre tieto formy vráti z resolvera rozsahu tabuľky False, čo ich nasmeruje na cestu preskočenia bez hodnoty. Text vzorca sa zachová a zapíše späť nezmenený, takže zošit, ktorý používa [@Amount], sa po prechode cez vašu aplikáciu v Exceli otvorí správne; chýba iba hodnota vypočítaná HotXLS. Pri voľbe medzi chýbajúcou hodnotou a hodnotou vypočítanou voči nesprávnemu riadku je neprítomnosť tá, ktorú viete odhaliť
Praktický workaround je mechanický: v zošite, ktorý generujete, napíšte ekvivalentnú relatívnu referenciu v štýle A1, čo je aj tak to, čo Excel interne ukladá pre veľkú časť logiky viazanej na tabuľku. V zošite, ktorý len spracúvate, nechajte vzorec na pokoji a čítajte cachovanú hodnotu, ktorú už Excel uložil — čo je zvyčajne to, čo chce pipeline typu načítaj-a-reportuj
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
// Vyhľadávanie v štýle recordsetu nad telom tabuľky, riadok od 1
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;Čo sa stane, keď tabuľka zmení tvar
Štruktúrované referencie sa pri zmiznutí veci, ktorú pomenúvajú, znehodnotia, namiesto toho, aby sa ticho prepojili na niečo iné. Vymažte stĺpec a vzorce odkazujúce na tento stĺpec sa znehodnotia rovnako, ako ich znehodnocuje Excel; vymažte alebo premenujte tabuľku a referencie na ňu sa spracujú rovnako. Toto je správne správanie a zrkadlí bežnú úpravu referencií opísanú v článku úprava referencií vzorcov pri vkladaní a mazaní, kde je úlohou enginu udržať vzorce čestné, nie udržať ich vyzerať platné
Rast riadkov je opačný prípad a nepotrebuje žiadnu úpravu vôbec. Pretože referencia pomenúva tabuľku, nie obdĺžnik, pridanie riadkov vnútri rozsahu tabuľky rozšíri to, čo pokrýva [#Data], bez toho, aby sa dotklo čo i len jedného vzorca. Práve táto vlastnosť robí tabuľky užitočnými v šablóne reportu: riadok súčtov naďalej sčítava všetko, čo import vyprodukoval, nech je počet riadkov akýkoľvek
Disciplína spätného zápisu
HotXLS zachováva pôvodný text vzorca. Zošit načítaný s SUM(SalesTable[Amount]) sa uloží s SUM(SalesTable[Amount]), nie s vyriešeným SUM(D2:D25). Toto je dôležitejšie, než sa môže zdať: používateľ, ktorý otvorí váš výstup v Exceli, očakáva, že uvidí vzorec, ktorý napísal, a vyriešená adresa by ticho zmenila samoudržiavajúci sa model na krehký, ktorý prestane pokrývať nové riadky
Obraz dopĺňajú dve súvisiace schopnosti. Samotné definície tabuliek, vrátane tabuliek bez hlavičky a komentárov na úrovni tabuľky, prežijú spätný zápis cez model tabuliek opísaný v článku validácia dát, AutoFilter a tabuľky Excelu. A keď veľa buniek zdieľa jeden vzor, XLSX ich uloží raz ako zdieľaný vzorec, ktorý sa rozbaľuje a znovu vypisuje, ako je opísané v článku rozbaľovanie si zdieľaného vzorca. Štruktúrované referencie vnútri zdieľaných vzorcov prechádzajú oboma cestami, takže sa musia správne správať obe, a aj sa správajú
HotXLS číta a zapisuje XLS, XLSX a ODS z Delphi a C++Builder bez nainštalovaného Excelu a bez akejkoľvek automatizácie Office, pričom vzorce vyhodnocuje vo vlastnom engine. API pre model tabuliek, formulový engine a prepočet sú zdokumentované na stránke HotXLS Delphi spreadsheet component