Egy megosztott formula-követő XLSX-ben nem hordoz formulaszöveget. A <f t="shared" si="N"/> eleme egy mester cellára mutat máshol a munkalapon, és az olvasónak a szöveget a mester formula sor- és oszlopkülönbséggel való eltolásával kell újraépítenie. A HotXLS Component Delphihez és C++Builderhez ezt a kibontást megnyitáskor végzi, így minden követő teljes formulát jelent
Ha valaha betöltöttél egy valós XLSX-et egy harmadik féltől származó könyvtárban, és azt találtad, hogy egy ezer formulából álló oszlop pontosan egy cellában tartalmaz szöveget, a másik 999-ben üres sztringeket, ennek a funkciónak a rossz oldaláról találkoztál vele. Semmi nem sérült. A fájl azt teszi, amit az ECMA-376 megenged neki, és az olvasó egyszerűen megállt azon a ponton, ahol az XML megállt
Miért üres a megosztott formula cellája?
Mert a formátum szándékosan egyszer tárolja a formulát. Az ECMA-376 1. részben és az ISO/IEC 29500-1-ben az <f> elem (§18.3.1.40) egy t attribútumot hordoz ST_CellFormulaType típussal, és a shared érték azt jelenti, hogy ez a cella egy si attribútum által azonosított csoportban vesz részt. Pontosan egy cella a csoportban, a mester, egy ref attribútumot is hordoz, amely megadja azt a tartományt, amelyre a csoport vonatkozik, és csak ez a cella hordozza a formulaszöveget elemtartalomként. A csoport minden más cellája követő. Megismétli a t="shared"-t és ugyanazt az si-t, és az elemtartalma üres. Az Excel agresszíven ír ilyen csoportokat, mert egy 200 000 soros oszlop lefelé kitöltése 200 000 formulasztringből egy sztringgé és 199 999 apró helykitöltő elemmé zsugorodik. A megtakarítás valós, és a költsége teljesen az olvasóra hárul: kibontás nélkül a követőnek önmagában nincs jelentése
Az eltolás fordítás, nem szövegmásolás
A HotXLS úgy oldja fel a követőt, hogy megkeresi a mestert, amely ugyanazon si alatt van regisztrálva, kiszámítja a sor- és oszlop-deltát a mester-horgonytól az aktuális celláig, és lefordítja a mester formula minden hivatkozását ezzel a deltával. A relatív dimenziók mozognak, az abszolút dimenziók nem, és a vegyes hivatkozások csak a nem-abszolút felüket mozgatják. A sztring-literálok teljesen ki vannak hagyva, így egy formula, amely véletlenül tartalmazza az "A1" szöveget, ezt a szöveget változatlanul tartja meg minden követőben
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;A ref attribútum egy kapu, nem díszítés. Egy követő, amelynek koordinátái a mester alkalmazható tartományán kívül esnek, nincs kibontva, mert a fájl ekkor olyan állítást tesz, amelyet a csoport nem támogat. Hasonlóképpen, amikor egy eltolás egy hivatkozást az 1. sor fölé vagy az A oszloptól balra tolna, a HotXLS #REF!-et bocsát ki arra a tokenre ahelyett, hogy csendben szorítaná, ami az, amit maga az Excel produkálna ugyanarra a szerkesztésre. Ez a fordítás közeli rokona, de nem azonos azzal a hivatkozás-átírással, amely akkor történik, amikor sorokat illesztesz be vagy törölsz. Annak az útvonalnak megvannak a saját szabályai arról, mit tesz egy tartomány, amikor egy szerkesztés átvágja, és ezt külön a a formula-hivatkozás beillesztés és törlés közbeni módosításáról szóló cikk írja le. A megosztott kibontás egyszerűbb: tiszta eltolás egy ismert horgonytól, egyszer alkalmazva, elemzéskor
Mely hivatkozás-alakokat kell fednie az eltolónak?
Mindegyiket, vagy a kibontás egy álcázott adatvesztési hiba. Egy naiv eltoló, amely csak az A1-et és az A1:B2-t érti, megrongálja vagy eldobja az egzotikusabb formákat, és a valódi munkafüzetek tele vannak velük. A HotXLS megosztott-formula fordító felismeri a teljes A1 családot, mielőtt eldöntené, mit mozgasson. A külső munkafüzet-hivatkozások, mint a [Book.xlsx]Sheet1!A1, és a 3D hivatkozások, mint a Sheet1:Sheet3!A1, megtartják előtagjukat érintetlenül, míg a záró cellahivatkozás elmozdul. Az idézőjelbe tett munkalapnevek túlélnek, beleértve azt a csúnya esetet, amikor a munkalap szó szerint A1-nek van elnevezve, így a 'A1'!A1 csak a felkiáltójel utáni részt tolja el. A teljes-oszlop A:A az oszlopdimenzióját mozgatja, és semmi mást; a teljes-sor 1:1 a sordimenzióját mozgatja, és semmi mást; a $A:$A egyáltalán nem mozdul. A strukturált táblahivatkozások, mint a Table[A1], érintetlenül maradnak, mert a szögletes zárójelben lévő rész egy oszlopnév, nem koordináta
// 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]A függvénynevek a csendes csapda itt. Egy token-pásztázó, amely betűket ragad meg, amelyeket számjegyek követnek, szívesen átírná a LOG10-et LOG11-re egy sorral lejjebb. A HotXLS egy hivatkozás-határt igényel egy jelölt token előtt és után, így egy azonosító, amely betűbe, számjegybe, aláhúzásjelbe, pontba vagy egy nyitó zárójelbe folytatódik, nem cellahivatkozás. Ha a másik jelölési családban dolgozol, ugyanez a határprobléma másképp jelenik meg, és az R1C1 jelölésről szóló cikk tárgyalja, hol tér el a két modell
Miért nyeli el egy önzáró f elem a következő értéket?
Mert egy önzáró elem nem produkál végelem-eseményt. Ez az egész funkció legdrágább hibája, és nem specifikus egyetlen XML elemzőre sem. A TXMLReader-ben a <f t="shared" si="4"/> pontosan egy Elem-eseményt vált ki, IsEmptyElement True-ra állítva, és sosem váltja ki a megfelelő EndElement-et. Egy elemző, amely a formula-rögzítő állapotát csak az EndElement-en zárja, ezért a formulán belül marad, és a következő szöveg, amit lát, ami a <v>-n belüli gyorsítótárazott eredmény, hozzáfűződik a formulapufferhez. Ami rosszabb, az állapot túléli a cellahatárt, így a következő cella, amely egy valódi <f>-t birtokol, a formulaszövegét az előző cella elnyeli. A javítás az, hogy a formula-állapotot magánál az Elem-eseménynél fejezzük be, valahányszor az IsEmptyElement True, és hogy a teljes követő-feloldás ott fusson le, ahelyett hogy várna. Ez azt jelenti, hogy a t, si, ref, aca és ca attribútumokat olvassuk, alkalmazzuk a megosztott kibontást, kiírjuk az újraszámítási attribútumokat a cellára, és töröljük a megosztott állapotot, mindezt abban az ágban, amely az üres elemet kezeli. Vedd figyelembe, hogy a formátum mindkét írásmódot engedélyezi, a <f t="shared" si="4"/>-t és a <f t="shared" si="4"></f>-t, és a második tényleg kivált egy EndElement-et. Egy helyes olvasónak azonosan kell kezelnie a párt, ez az oka, hogy a HotXLS mindkét írásmódot lefedi ugyanabban a regressziós fájlban
Ritka, rendezetlen si értékek és a függőben lévő sor
A si attribútum egy fájl által megadott előjel nélküli egész, nem egy tömbpozíció, amit te vezérelsz. Semmi a sémában nem követeli meg, hogy a megosztott indexek sűrűek legyenek, nullától kezdjenek, vagy növekvő sorrendben jelenjenek meg, és semmi nem akadályozza meg egy ellenséges vagy csupán furcsa fájlt, hogy si="4294967290"-et használjon az első cellán. Egy keresőtábla méretezése a legnagyobb megfigyelt si-ből ezért memória-kimerítési primitíva, nem optimalizáció. A HotXLS a munkafüzet-megnyitási útvonalon egy rendezett ritka táblát tart ehelyett: a megosztott csoportok az egész kulcsuk alatt vannak regisztrálva egy rendezett TStringList-ben, ami bináris keresést csinál a keresésből azon a néhány csoporton, amennyi ténylegesen létezik, minden kapcsolat nélkül az indexek numerikus méretétől. A sorrend a probléma második fele. Egy mester normál esetben megelőzi a követőit dokumentum-sorrendben, de ez konvenció, nem szabály, így minden követő, amely nem tudja feloldani az si-jét abban a pillanatban, amikor elemzésre kerül, egy függőben lévő sorba kerül. Amikor a munkalap véget ér, a sor lejátszásra kerül a most már teljes tábla ellen, és a késői mesterek feloldják az árváikat. A cellák, amelyek sosem találnak mestert, üres formulát tartanak meg, ami az őszinte kimenet egy olyan fájlnál, amely egy sosem definiált csoportra hivatkozik
Megosztott formulák kibontása a munkafüzet betöltése nélkül
A streamelő olvasók ugyanezzel a követelménnyel néznek szembe egy sokkal szűkebb memóriabüdzsé alatt, és egy munkalap-lokális táblával oldják meg. A TXLSDirectReader és a TXLSRowCursor egyaránt teljes per-cella formulákká bontja ki a követőket, miközben megőrzi a korlátozott-memóriájú és vetítési viselkedésüket, így egy előre-csak áthaladás egy 300 MB-os munkalapon még mindig valódi formulaszöveget ad
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;Két korlátozás következik ebből a tervezésből. Először, a vetítés sosem hagyhatja ki a mestert. Egy sorszűrő, amelyet a FirstRow és LastRow állít be, vagy egy oszlopszűrő, amelyet az IncludeColumn épít, kihagyhatja a mester-cella kibocsátását a callbacknek, de az elemzőnek még mindig rögzítenie kell az si-jét, horgony-koordinátáit, alkalmazható tartományát és formulaszövegét, különben a vetítésen belüli minden követő üresre oldódik. Csak a követő-oldali munka, az eltolás és az érték-dekódolás biztonságos kihagyni. Másodszor, a tábla munkalaponkénti, és élettartamát explicit módon kell kezelni: a TXLSRowCursor egy példányt tart egy munkalap-áthaladás időtartamára, és törli azt újraindításkor, munkalapváltáskor, fájl végén, kivételkor és záráskor, így egy az egyik munkalapon definiált csoport sosem szivároghat a másikra. Mivel a streamelő útvonal egy forró ciklus, egy nyílt-címzésű egész-hash-t használ a rendezett sztring-tábla helyett, ami elkerül egy egész-sztring konverziót cellánként
Mi történik mentéskor, és hol vannak a határok
Miután egy követő kibontásra került, hétköznapi formula, és a HotXLS visszaírja azt egy önálló <f> elemként t="shared" és si nélkül. A round-trip stabil, és a gyorsítótárazott <v> eredmények túlélik, de a kimenet nagyobb, mint a bemenet egy erősen megosztott munkalapnál, és az Excel által létrehozott csoportosítás nem épül újra mentéskor. Ha a megosztott csoportok bájtszintű hűsége fontosabb számodra, mint hogy minden cellában valódi formulaszöveg legyen, ez az a csere, amit elfogadsz. Az XLS oldal más, mellesleg: a BIFF8 SHRFMLA rekordnak saját kódolása és saját írója van, saját megosztott-csoport kapcsolóval a munkafüzeten
Két dolog kifejezetten nem megosztott formula, még ha meg is osztják az <f> elemet. A régi CSE tömbformulák t="array"-t használnak egy ref-fel, amely lefedi a horgonyzott tartományt, és a dinamikus tömbök ugyanazt a t="array" írásmódot használják, de egy cm attribútummal azonosítottak, amely a cellMetadata-n keresztül egy XLDAPR rekordba láncol. Egy dinamikus tömb-kifolyó (spill) cella kezelése megosztott vagy CSE követőként valódi korrektségi hiba, és a szétválasztást a a dinamikus tömb és kifolyó formulákról szóló cikk tárgyalja. Olvasd a három esetet mint három elemzőt, amelyek véletlenül osztoznak egy tag-néven, és a kód őszinte marad
Az itt leírt megosztott-formula kibontás, a streamelő olvasók és a hivatkozás-fordító a HotXLS Excel komponens részeként érkezik Delphihez és C++Builderhez; a termékoldal tartalmazza a teljes formula- és közvetlen-olvasási API-referenciát, beleértve a fent használt vetítési tulajdonságokat