XLSX Shared Formula si-ekspansjon i Delphi: fallgruver

En shared-formula-følger i XLSX bærer ingen formeltekst. <f t="shared" si="N"/>-elementet dens peker på en mestercelle et annet sted i arket, og leseren må gjenoppbygge teksten ved å skifte mesterformelen med rad- og kolonneforskjellen. HotXLS Component for Delphi og C++Builder gjør den ekspansjonen ved åpningstidspunktet, slik at hver følger rapporterer en komplett formel

Hvis du noensinne har lastet en ekte XLSX i et tredjepartsbibliotek og oppdaget at en kolonne med tusen formler har tekst i akkurat én celle og tomme strenger i de andre 999, har du møtt denne funksjonen fra feil side. Ingenting er korrupt. Filen gjør det ECMA-376 lar den gjøre, og leseren stoppet ganske enkelt der XML-en stoppet

Hvorfor er den delte formelcellen tom?

Fordi formatet bevisst lagrer formelen én gang. I ECMA-376 Part 1 og ISO/IEC 29500-1 bærer <f>-elementet (§18.3.1.40) et t-attributt av typen ST_CellFormulaType, og verdien shared betyr at denne cellen deltar i en gruppe identifisert av si-attributtet. Nøyaktig én celle i gruppen, mesteren, bærer også et ref-attributt som gir rekkevidden gruppen gjelder for, og bare den cellen bærer formelteksten som elementinnhold. Hver annen celle i gruppen er en følger. Den gjentar t="shared" og samme si, og elementinnholdet dens er tomt. Excel skriver disse gruppene aggressivt, fordi en fyll-ned over en 200 000-rads kolonne kollapser fra 200 000 formelstrenger til én streng pluss 199 999 små plassholder-elementer. Besparelsen er reell og kostnaden lander helt på leseren: uten ekspansjon har følgeren ingen mening på egen hånd

Skiftet er en oversettelse, ikke en tekstkopi

HotXLS løser opp en følger ved å lokalisere mesteren registrert under samme si, beregne rad- og kolonnedeltaet fra mesterforankringen til gjeldende celle, og oversette hver referanse i mesterformelen med det deltaet. Relative dimensjoner beveger seg, absolutte dimensjoner gjør ikke det, og blandede referanser beveger bare sin ikke-absolutte halvdel. Strengliteraler hoppes helt over, så en formel som tilfeldigvis inneholder teksten "A1" beholder den teksten uendret i hver følger

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+&quot;A1&quot;+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;

ref-attributtet er en port, ikke pynt. En følger hvis koordinater faller utenfor mesterens gjeldende rekkevidde ekspanderes ikke, fordi filen da gjør et krav gruppen ikke støtter. På samme måte, når et skift ville presse en referanse over rad én eller til venstre for kolonne A, utsteder HotXLS #REF! for det tokenet fremfor å stille klemme det, som er det Excel selv ville produsert for samme redigering. Denne oversettelsen er en nær fetter av, men ikke det samme som, referanseomskrivingen som skjer når du setter inn eller sletter rader. Den veien har sine egne regler for hva en rekkevidde gjør når en redigering kutter gjennom den, og det er beskrevet separat i artikkelen om formelreferansejustering ved innsetting og sletting. Delt ekspansjon er enklere: det er en ren forskyvning fra en kjent forankring, anvendt én gang, ved parse-tidspunktet

Hvilke referanseformer må skifteren dekke?

Alle sammen, ellers er ekspansjonen en datatapsfeil i forkledning. En naiv skifter som bare forstår A1 og A1:B2 vil korrumpere eller droppe de mer eksotiske formene, og ekte arbeidsbøker er fulle av dem. HotXLS-oversetteren for delte formler gjenkjenner hele A1-familien før den bestemmer hva som skal flyttes. Eksterne arbeidsbok-referanser som [Book.xlsx]Sheet1!A1 og 3D-referanser som Sheet1:Sheet3!A1 beholder prefikset sitt intakt mens den etterfølgende cellereferansen skifter. Siterte arknavn overlever, inkludert det slemme tilfellet der arket bokstavelig talt heter A1, så 'A1'!A1 skifter bare delen etter utropstegnet. Hel-kolonne A:A beveger kolonnedimensjonen sin og ingenting annet; hel-rad 1:1 beveger raddimensjonen sin og ingenting annet; $A:$A beveger seg ikke i det hele tatt. Strukturerte tabellreferanser som Table[A1] lates urørt, fordi den klammeparentesen er et kolonnenavn, ikke en koordinat

// 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]

Funksjonsnavn er den stille fellen her. En tokenskanner som griper bokstaver etterfulgt av sifre vil glatt skrive om LOG10 til LOG11 én rad ned. HotXLS krever en referansegrense før et kandidattoken og etter det, så en identifikator som fortsetter inn i en bokstav, siffer, understrek, punktum, eller en åpningsparentes er ikke en cellereferanse. Hvis du arbeider i den andre notasjonsfamilien, dukker samme grenseproblem opp annerledes, og R1C1-notasjonsartikkelen dekker hvor de to modellene divergerer

Hvorfor sluker et selvlukkende f-element den neste verdien?

Fordi et selvlukkende element ikke produserer noen slutt-element-hendelse. Dette er den enkeltmest kostbare feilen i hele funksjonen, og den er ikke spesifikk for noen bestemt XML-parser. I TXMLReader utløser <f t="shared" si="4"/> nøyaktig én Element-hendelse med IsEmptyElement satt til True, og utløser aldri den tilhørende EndElement. En parser som lukker sin formel-fangende tilstand bare ved EndElement forblir derfor inne i formelen, og neste tekst den ser, som er det mellomlagrede resultatet inne i <v>, blir lagt til formelbufferen. Verre, tilstanden overlever cellegrensen, så neste celle som eier en ekte <f> får formelteksten sin absorbert av den forrige cellen. Fiksen er å avslutte formeltilstanden ved selve Element-hendelsen når som helst IsEmptyElement er True, og å kjøre hele følger-oppløsningen der fremfor å vente. Det betyr å lese t, si, ref, aca, og ca fra attributtene, anvende den delte ekspansjonen, skrive rekalkuleringsattributtene på cellen, og tømme den delte tilstanden, alt inne i grenen som håndterer det tomme elementet. Legg merke til at formatet tillater begge staveformene, <f t="shared" si="4"/> og <f t="shared" si="4"></f>, og den andre utløser faktisk en EndElement. En korrekt leser må håndtere paret identisk, og det er derfor HotXLS dekker begge staveformene i samme regresjonsfil

Sparsomme, uordnede si-verdier og ventekøen

si-attributtet er et fil-oppgitt usignert heltall, ikke en array-posisjon du kontrollerer. Ingenting i skjemaet krever at delte indekser er tette, starter på null, eller vises i stigende rekkefølge, og ingenting stopper en fiendtlig eller bare merkelig fil fra å bruke si="4294967290" på den første cellen. Å dimensjonere en oppslagsarray fra den største observerte si er derfor et minneuttømmings-primitiv, ikke en optimalisering. HotXLS holder arbeidsbok-åpne-veien på en sortert sparsom tabell i stedet: delte grupper registreres under sitt heltallsnøkkel i en sortert TStringList, som gjør oppslag til et binærsøk over uansett hvor mange grupper som faktisk finnes, uten sammenheng med den numeriske størrelsen på indeksene. Rekkefølge er den andre halvparten av problemet. En mester går normalt foran følgerne sine i dokumentrekkefølge, men det er en konvensjon fremfor en regel, så enhver følger som ikke kan løse opp sin si i det øyeblikket den parses går inn i en ventekø. Når arket er ferdig, spilles køen av mot den nå komplette tabellen, og de sene mestrene løser opp sine foreldreløse. Celler som aldri finner en mester beholder en tom formel, som er det ærlige utfallet for en fil som refererer en gruppe den aldri definerte

Å ekspandere delte formler uten å laste arbeidsboken

De strømmende leserne møter samme krav under et mye strammere minnebudsjett, og de løser det med en regneark-lokal tabell. TXLSDirectReader og TXLSRowCursor ekspanderer begge følgere til komplette per-celle-formler mens de bevarer sin begrensede-minne- og projeksjonsatferd, så en fremover-bare gjennomgang over et 300 MB-ark fortsatt gir deg ekte formeltekst

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;

To begrensninger faller ut av det designet. For det første kan projeksjon aldri hoppe over mesteren. Et radfilter satt med FirstRow og LastRow, eller et kolonnefilter bygget med IncludeColumn, kan hoppe over å utstede mestercellen til callbacken din, men parseren må fortsatt registrere si, forankringskoordinater, gjeldende rekkevidde, og formeltekst, ellers løser hver følger inne i projeksjonen opp til ingenting. Bare følger-siden av arbeidet, skiftet og verdi-dekodingen, er trygt å hoppe over. For det andre er tabellen per regneark og levetiden dens må håndteres eksplisitt: TXLSRowCursor holder én instans for varigheten av en arkgjennomgang og tømmer den ved omstart, arkbytte, filslutt, unntak, og lukking, så en gruppe definert på ark én kan aldri lekke inn i ark to. Fordi den strømmende veien er en het løkke, bruker den en open-addressing heltallshash fremfor den sorterte strengtabellen, noe som unngår en heltall-til-streng-konvertering per celle

Hva skjer ved lagring, og hvor grensene er

Når en følger er ekspandert er den en ordinær formel, og HotXLS skriver den tilbake som et uavhengig <f>-element uten t="shared" og uten si. Rundturen er stabil og de mellomlagrede <v>-resultatene overlever, men utdataen er større enn inputen for et tungt delt ark, og grupperingen Excel opprettet rekonstrueres ikke ved lagring. Hvis byte-nivå-troskap til de delte gruppene betyr mer for deg enn å ha ekte formeltekst i hver celle, er dette avveiningen du aksepterer. XLS-siden er forresten annerledes: BIFF8 SHRFMLA-posten har sin egen koding og sin egen skriver, med en delt-gruppe-bryter på arbeidsboken

To beslektede ting er eksplisitt ikke delte formler selv om de deler <f>-elementet. Eldre CSE-array-formler bruker t="array" med et ref som dekker den forankrede rekkevidden, og dynamiske arrayer bruker samme t="array"-staveform men identifiseres av et cm-attributt som kjeder gjennom cellMetadata til en XLDAPR-post. Å behandle en dynamisk-array-spill-celle som en delt eller CSE-følger er en genuin korrekthetsfeil, og skillet er dekket i artikkelen om dynamiske array- og spill-formler. Les de tre tilfellene som tre parsere som tilfeldigvis deler et tag-navn, og koden forblir ærlig

Den delte-formel-ekspansjonen, de strømmende leserne, og referanseoversetteren beskrevet her leveres som en del av HotXLS Excel-komponenten for Delphi og C++Builder; produktsiden inneholder den fullstendige formel- og direkte-les-API-referansen, inkludert projeksjonsegenskapene brukt over