Následovník sdíleného vzorce v XLSX nenese žádný text vzorce. Jeho element <f t="shared" si="N"/> ukazuje na master buňku někde jinde v listu a čtenář musí text sestavit znovu posunutím master vzorce o rozdíl řádku a sloupce. HotXLS Component pro Delphi a C++Builder provádí toto rozbalení v okamžiku otevření, takže každý následovník hlásí kompletní vzorec
Pokud jste někdy načetli reálný XLSX v knihovně třetí strany a zjistili jste, že sloupec s tisícem vzorců má text přesně v jedné buňce a prázdné řetězce ve zbylých 999, potkali jste tuto funkci ze špatné strany. Nic není poškozené. Soubor dělá to, co mu ECMA-376 dovoluje, a čtenář prostě přestal v bodě, kde přestalo XML
Proč je buňka sdíleného vzorce prázdná?
Protože formát záměrně ukládá vzorec jen jednou. V ECMA-376 Part 1 a ISO/IEC 29500-1 nese element <f> (§18.3.1.40) atribut t typu ST_CellFormulaType, a hodnota shared znamená, že se tato buňka účastní skupiny identifikované atributem si. Přesně jedna buňka ve skupině, master, nese také atribut ref udávající rozsah, na který se skupina vztahuje, a jen tato buňka nese text vzorce jako obsah elementu. Každá další buňka ve skupině je následovník. Opakuje t="shared" a stejné si, a její obsah elementu je prázdný. Excel zapisuje tyto skupiny agresivně, protože vyplnění dolů (fill-down) přes sloupec s 200 000 řádky se zhroutí z 200 000 řetězců vzorců na jeden řetězec plus 199 999 drobných zástupných elementů. Úspora je reálná a cena za ni padá celá na čtenáře: bez rozbalení nemá následovník sám o sobě žádný význam
Posun je překlad, nikoli kopie textu
HotXLS rozřeší následovníka tak, že najde master zaregistrovaný pod stejným si, spočítá deltu řádku a sloupce od ukotvení masteru k aktuální buňce a přeloží každou referenci v master vzorci o tuto deltu. Relativní rozměry se posouvají, absolutní ne, a smíšené reference posouvají jen svou neabsolutní polovinu. Řetězcové literály se úplně přeskakují, takže vzorec, který náhodou obsahuje text "A1", ponechá tento text nezměněný v každém následovníkovi
const
// xl/worksheets/sheet1.xml, trimmed to the interesting cells
SheetXml: WideString=
'<row r="1"><c r="A1"><v>1</v></c>'+
'<c r="B1"><f t="shared" si="4" ref="B1:B3">'+
'A1+$A$1+A$1+$A1+"A1"+SUM(A1:A2)</f><v>7</v></c></row>'+
'<row r="2"><c r="B2"><f t="shared" si="4"/><v>8</v></c></row>'+
'<row r="3"><c r="B3"><f t="shared" si="4"></f><v>9</v></c></row>';
var
Wb: TXLSXWorkbook;
Sh: TXLSXWorksheet;
begin
Wb:= TXLSXWorkbook.Create;
try
Wb.Open(FileName);
Sh:= Wb.Sheets[1];
// Master, verbatim
// B1 -> A1+$A$1+A$1+$A1+"A1"+SUM(A1:A2)
// Follower one row down: relative row moves, absolute row frozen,
// the mixed A$1 keeps its row, and the literal stays a literal
// B2 -> A2+$A$1+A$1+$A2+"A1"+SUM(A2:A3)
ShowMessage(Sh.Cells[2, 2].Formula);
finally
Wb.Free;
end;
end;Atribut ref je brána, nikoli ozdoba. Následovník, jehož souřadnice spadají mimo použitelný rozsah masteru, se nerozbaluje, protože soubor by tím dělal tvrzení, které skupina nepodporuje. Podobně, když by posun vytlačil referenci nad řádek jedna nebo nalevo od sloupce A, HotXLS pro tento token vydá #REF!, místo aby jej potichu ořízl, což je to, co by pro stejnou úpravu vyprodukoval sám Excel. Tento překlad je blízký příbuzný, nikoli totéž, co přepis referencí, který se děje při vkládání nebo mazání řádků. Ta cesta má svá vlastní pravidla o tom, co dělá rozsah, když jej úprava přeřízne, a je popsána samostatně v článku o úpravě formulářových referencí při vkládání a mazání. Sdílené rozbalení je jednodušší: je to čistý offset od známého ukotvení, aplikovaný jednou, v okamžiku parsování
Které tvary referencí musí posunovač pokrýt?
Všechny, jinak je rozbalení v přestrojení chybou se ztrátou dat. Naivní posunovač, který rozumí jen A1 a A1:B2, poškodí nebo zahodí exotičtější tvary, a reálné sešity jich jsou plné. Překladač sdílených vzorců HotXLS rozpozná celou rodinu A1 dřív, než rozhodne, co posunout. Externí reference na sešit jako [Book.xlsx]Sheet1!A1 a 3D reference jako Sheet1:Sheet3!A1 ponechávají svůj prefix nedotčený, zatímco koncová reference buňky se posouvá. Uvozovkovaná jména listů přežívají, včetně ošklivého případu, kdy je list doslova pojmenovaný A1, takže 'A1'!A1 posune jen část za vykřičníkem. Celý sloupec A:A posouvá svůj sloupcový rozměr a nic víc; celý řádek 1:1 posouvá svůj řádkový rozměr a nic víc; $A:$A se nehýbe vůbec. Strukturované tabulkové reference jako Table[A1] zůstávají nedotčené, protože část v hranatých závorkách je název sloupce, nikoli souřadnice
// One master, expanded two columns to the right and zero rows down.
// Master D1: A1+A:A+$A:$A
// F1 : C1+C:C+$A:$A
//
// One master, expanded three rows down and zero columns across.
// Master A1: B1+$C$1+"A1"+A:A+1:1+'Data'!A1+LOG10(A1)+Table[A1]+'A1'!A1
// A3 : B3+$C$1+"A1"+A:A+3:3+'Data'!A3+LOG10(A3)+Table[A1]+'A1'!A3
//
// Note what did NOT move in the second line: the absolute $C$1, the
// string literal "A1", the whole column A:A under a pure row delta,
// the function name LOG10, and the structured reference Table[A1]Tichou pastí jsou tu názvy funkcí. Skener tokenů, který zabírá písmena následovaná číslicemi, ochotně přepíše LOG10 na LOG11 o řádek níž. HotXLS vyžaduje hranici reference před kandidátským tokenem i za ním, takže identifikátor, který pokračuje písmenem, číslicí, podtržítkem, tečkou nebo otevírací závorkou, není referencí na buňku. Pokud pracujete v druhé rodině notace, stejný problém s hranicí se projevuje jinak, a článek o notaci R1C1 popisuje, kde se oba modely rozcházejí
Proč samouzavírající se element f pohltí následující hodnotu?
Protože samouzavírající se element nevyprodukuje žádnou událost konce elementu. Toto je jednoznačně nejdražší chyba v celé této funkci a není specifická pro žádný konkrétní XML parser. V TXMLReader vyvolá <f t="shared" si="4"/> přesně jednu událost Element s IsEmptyElement nastaveným na True, a nikdy nevyvolá odpovídající EndElement. Parser, který uzavírá svůj stav zachytávání vzorce jen na EndElement, tedy zůstává uvnitř vzorce, a další text, který uvidí, což je uložený výsledek uvnitř <v>, se připojí k bufferu vzorce. Ještě horší je, že stav přežije hranici buňky, takže dalšímu buňce, která vlastní skutečné <f>, je její text vzorce pohlcen předchozí buňkou. Oprava spočívá v tom, ukončit stav vzorce přímo na události Element vždy, když je IsEmptyElement True, a spustit tam celé rozřešení následovníka místo čekání. To znamená přečíst t, si, ref, aca a ca z atributů, aplikovat sdílené rozbalení, zapsat na buňku atributy přepočtu a vyčistit sdílený stav, to vše uvnitř větve, která obsluhuje prázdný element. Všimněte si, že formát povoluje oba zápisy, <f t="shared" si="4"/> i <f t="shared" si="4"></f>, a druhý z nich EndElement skutečně vyvolá. Správný čtenář musí zacházet s oběma stejně, a proto HotXLS pokrývá oba zápisy ve stejném regresním souboru
Řídké, neseřazené hodnoty si a fronta čekajících
Atribut si je souborem dodané neznaménkové celé číslo, nikoli pozice v poli, kterou kontrolujete vy. Nic ve schématu nevyžaduje, aby sdílené indexy byly husté, začínaly na nule nebo se objevovaly ve vzestupném pořadí, a nic nebrání nepřátelskému nebo prostě podivnému souboru v tom, aby použil si="4294967290" na první buňce. Nastavení velikosti vyhledávacího pole podle nejvyššího pozorovaného si je proto primitivum pro vyčerpání paměti, nikoli optimalizace. HotXLS místo toho drží cestu otevírání sešitu na seřazené řídké tabulce: sdílené skupiny se registrují pod svým celočíselným klíčem v seřazeném TStringList, což dělá z vyhledávání binární prohledávání přes tolik skupin, kolik jich skutečně existuje, bez vztahu k číselné velikosti indexů. Pořadí je druhou polovinou problému. Master obvykle předchází svým následovníkům v pořadí dokumentu, ale to je konvence, nikoli pravidlo, takže jakýkoli následovník, který nemůže rozřešit své si v okamžiku, kdy je parsován, jde do fronty čekajících. Když list skončí, fronta se přehraje proti nyní kompletní tabulce a pozdní mastery rozřeší své sirotky. Buňky, které nikdy nenajdou master, ponechávají prázdný vzorec, což je poctivý výsledek pro soubor, který odkazuje na skupinu, kterou nikdy nedefinoval
Rozbalení sdílených vzorců bez načtení sešitu
Streamovací čtenáři čelí stejnému požadavku s mnohem přísnějším paměťovým rozpočtem a řeší jej tabulkou lokální pro daný list. TXLSDirectReader i TXLSRowCursor oba rozbalují následovníky na kompletní vzorce po jednotlivých buňkách, přičemž zachovávají svoje chování s omezenou pamětí a projekcí, takže vás průchod pouze vpřed přes 300MB list stále vybaví reálným textem vzorce
var
Reader: TXLSDirectReader;
Cursor: TXLSRowCursor;
begin
// Projection: only rows 2..3, only column A. The master lives in row 1,
// outside the projection, and is still parsed so the followers resolve
Reader:= TXLSDirectReader.Create;
try
Reader.FirstRow:= 2;
Reader.LastRow:= 3;
Reader.IncludeColumn(1);
Reader.OnCell:= HandleCell; // Cell.Formula is fully expanded here
Reader.ReadFile(FileName);
finally
Reader.Free;
end;
// Forward-only row traversal, same expansion
Cursor:= TXLSRowCursor.Create;
try
Cursor.Open(FileName);
if Cursor.FindFirst then
repeat
if Cursor.CellCount > 0 then
WriteLn(Cursor.RowIndex, ': ', Cursor.Cells[0].Formula);
until not Cursor.FindNext;
finally
Cursor.Free;
end;
end;Z tohoto návrhu vyplývají dvě omezení. Za prvé, projekce nemůže nikdy přeskočit master. Filtr řádků nastavený pomocí FirstRow a LastRow, nebo filtr sloupců sestavený pomocí IncludeColumn, může vynechat vydání master buňky vašemu callbacku, ale parser přesto musí zaznamenat jeho si, souřadnice ukotvení, použitelný rozsah a text vzorce, jinak se každý následovník uvnitř projekce rozřeší na nic. Bezpečné přeskočit je jen práce na straně následovníka, tedy posun a dekódování hodnoty. Za druhé, tabulka je pro každý list zvlášť a její životnost se musí spravovat explicitně: TXLSRowCursor drží jednu instanci po dobu průchodu listem a čistí ji při restartu, přepnutí listu, konci souboru, výjimce a zavření, takže skupina definovaná na listu jedna nikdy nemůže uniknout do listu dva. Protože je streamovací cesta horkým cyklem, používá celočíselnou hash tabulku s otevřeným adresováním místo seřazené tabulky řetězců, což se vyhne převodu celého čísla na řetězec u každé buňky
Co se stane při uložení a kde jsou hranice
Jakmile je následovník rozbalen, je to obyčejný vzorec, a HotXLS jej zapíše zpět jako nezávislý element <f> bez t="shared" a bez si. Round-trip je stabilní a uložené výsledky <v> přežívají, ale výstup je u silně sdíleného listu větší než vstup a seskupení, které vytvořil Excel, se při uložení nerekonstruuje. Pokud vám na bajtové věrnosti sdílených skupin záleží víc než na reálném textu vzorce v každé buňce, toto je kompromis, který přijímáte. Strana XLS je mimochodem jiná: záznam BIFF8 SHRFMLA má vlastní kódování a vlastní writer, s přepínačem sdílené skupiny na sešitu
Dvě příbuzné věci výslovně nejsou sdílené vzorce, přestože sdílejí element <f>. Starší CSE maticové vzorce používají t="array" s ref pokrývajícím ukotvený rozsah, a dynamická pole používají stejný zápis t="array", ale identifikují se atributem cm, který se řetězí přes cellMetadata na záznam XLDAPR. Zacházet s buňkou přelivu dynamického pole jako se sdíleným nebo CSE následovníkem je skutečná chyba správnosti a toto rozlišení je popsáno v článku o dynamických polích a vzorcích s přelivem. Čtěte tyto tři případy jako tři parsery, které náhodou sdílejí jméno tagu, a kód zůstane poctivý
Zde popsané rozbalení sdílených vzorců, streamovací čtenáři a překladač referencí jsou součástí HotXLS Excel component pro Delphi a C++Builder; produktová stránka obsahuje kompletní referenci API pro vzorce a přímé čtení, včetně výše použitých vlastností projekce