LlosLab Software Development BlogTekniske noter til PDF-, Delphi- og dokumentsoftwareudviklere.

Teknisk artikel

Hvorfor Excel ignorerer cachede #SPILL!- og #CALC!-fejl

HotXLS Delphi Component

Excel stoler kun på et cached #SPILL! eller #CALC!, når arbejdsbogen bærer den fulde moderne fejlkontrakt: Rich Data-metadata, hvis XLRICHVALUE extension-URI er den lowercase GUID, en fileVersion på 7, en calcId på mindst 191029 og microsoft.com:RD-beregningsfunktionen. HotXLS, den native Delphi XLSX-komponent, udsender alle fire, når en gemt celle holder en af de fejl

Misser man bare én del, er fejlen sjældent højlydt. Nogle gange nægter Excel 16 slet at åbne filen. Oftere åbner den fint og viser #VALUE!, hvor ens beregningsmotor frembragte #SPILL!, hvilket ligner en beregningsbug og sender en på jagt gennem formularkode, der er helt korrekt. Tryk F9, og den rigtige fejl viser sig, så problemet tenderer mod at blive lukket som "virker på min maskine", til en kunde åbner rapporten med automatisk beregning slået fra. Dette opslag gennemgår, hvad hver del gør, GUID-casing-fælden, der ligner noget, der umuligt kan betyde noget, og array-tekstcache-reglen, der fejler på præcis samme stille måde

Hvorfor viser Excel #VALUE! i stedet for en cached #SPILL!?

Excel viser #VALUE!, for det er bogstaveligt, hvad cellen gemmer. Den legacy fejltype i ECMA-376 SpreadsheetML har en fast liste over fejl-literaler, og #SPILL! og #CALC! er ikke på den. En celle med t="e" kan kun sige #NULL!, #DIV/0!, #VALUE!, #REF!, #NAME?, #NUM! eller #N/A, så nyere Excel-versioner skriver #VALUE! som den bagudkompatible fallback og hænger den rigtige fejltype på et value-metadata-indeks

Indekset, vm-attributten, peger ind i xl/metadata.xml, som peger ind i en Rich Data-værdi, der beskriver den faktiske fejl. Ældre readers ser #VALUE! og går videre. Aktuel Excel følger kæden og viser den moderne fejl, men kun hvis den beslutter, at kæden er troværdig. Sådan ser en spillet-og-blokeret formel ud på disken:

<!-- sheet1.xml: legacy literal plus en value-metadata-pointer -->
<c r="A1" t="e" cm="1" vm="1">
  <f t="array" ref="A1">_xlfn.SEQUENCE(3)</f>
  <v>#VALUE!</v>
</c>

<!-- xl/richData/rdrichvaluestructure.xml -->
<s t="_error">
  <k n="errorType" t="i"/>
  <k n="colOffset" t="i"/>
  <k n="rwOffset" t="i"/>
</s>

<!-- xl/richData/rdrichvalue.xml: errorType 8 er #SPILL! -->
<rv s="0"><v>8</v><v>0</v><v>2</v></rv>

HotXLS skriver fejltype 8 med kolonne- og række-offsets for #SPILL! og fejltype 13 uden offsets for #CALC!. Den legacy <v> forbliver #VALUE! i begge tilfælde med vilje, for det er værdien, enhver reader, der ignorerer Rich Data, formodes at se

Den lowercase GUID, som Excel 16 faktisk tjekker

XLRICHVALUE-future-metadata-blokken skal bruge extension-URI'en {3e2802c4-a4d2-4d8b-9148-e3be6c30e623} i lowercase, tegn for tegn. Gør man hex-bogstaverne store, nægtede den lokale Excel 16-build at åbne arbejdsbogen normalt, selvom hver XML-part parsede, hver relationship opløstes, og hvert antal og indeks stemte

<futureMetadata name="XLRICHVALUE" count="1">
  <bk>
    <extLst>
      <ext uri="{3e2802c4-a4d2-4d8b-9148-e3be6c30e623}">
        <xlrd:rvb i="0"/>
      </ext>
    </extLst>
  </bk>
</futureMetadata>
<valueMetadata count="1">
  <bk><rc t="2" v="0"/></bk>
</valueMetadata>

Alle, der har arbejdet med COM eller registreringsdatabasen, ved, at GUID'er sammenlignes case-insensitivt, og den vane er præcis det, der bider her. Vi isolerede det på den langsomme måde: byggede en minimal pakke, Excel afviste, og byttede så én ting ad gangen. At udskifte content types fixede det ikke. At tilføje en rich value types-part fixede det ikke. At ændre navnerums-præfikser eller omskrive rich value data fixede det ikke. At gøre den enkelte uri-attribut lowercase fixede det, og samme pakke åbnede og viste så både #CALC! og #SPILL!. Behandl extension-URI'en som en opak strengliteral, kopier den fra formatdefinitionen, og lad den aldrig køre gennem en GUID-formatter, der vælger sin egen casing

Der er en praktisk konsekvens for enhver, der skriver diagnostiske pakker i hånden. Lav redigeringerne med en XML-parser, ikke med en global teksterstatning over serialiseret XML. En generel substitution rettet mod ét token kan korruptere entiteter som &gt; et andet sted i part'en, og så debugger man en ødelagt probe i stedet for det oprindelige spørgsmål

Hvad behøver Excel ellers for at læse en cached moderne fejl?

Metadata-parts og deres relationships er nødvendige men ikke tilstrækkelige. Med en perfekt gyldig Rich Data-kæde på plads ignorerede Excel stadig den cachede fejl, til workbook-part'en erklærede sig som kommende fra en moderne beregningsmotor. HotXLS skriver tre erklæringer ind i workbook.xml, når en gemt celle holder #SPILL! eller #CALC!:

<fileVersion appName="xl" lastEdited="7" lowestEdited="7"/>
...
<calcPr calcId="191029"/>
<extLst>
  <ext uri="...">
    <calcFeatures>
      <feature name="microsoft.com:RD"/>
    </calcFeatures>
  </ext>
</extLst>

fileVersion-elementet er almindelig ECMA-376 Part 1 workbook-metadata, og lastEdited / lowestEdited på 7 fortæller Excel, at filen sidst blev gemt af en applikationsgeneration, der forstår Rich Data. calcId på calcPr identificerer beregningsmotor-versionen, og 191029 er minimum, vi fandt Excel acceptere for disse caches. microsoft.com:RD-posten i calculation features-extensionen, i http://schemas.microsoft.com/office/spreadsheetml/2018/calcfeatures-navnerummet, erklærer, at motoren, der frembragte de cachede værdier, evaluerede med Rich Data-semantik. At give kun subtypen, kun types-part'en eller kun andre præfikser og relationship-stier ændrede ikke udfaldet. Alle tre erklæringer skulle være til stede sammen

Tre detaljer i, hvordan HotXLS anvender dem, er værd at kende, hvis man sætter disse værdier selv:

  • TXLSXWorkbook.CalcId overskrives ikke. Writeren hæver den udsendte værdi til 191029 kun i outputtet, så en kalder, der konfigurerede en højere CalcId, beholder den, og in-memory-egenskaben forbliver præcis, hvad man tildelte
  • Eksisterende calculation features og urelaterede workbook-extensions, der kom med en åbnet fil, bevares, ikke erstattes
  • Writeren tjekker for en eksisterende microsoft.com:RD-funktion, før den tilføjer én, så gem af samme arbejdsbog gentagne gange aldrig stabler duplikerede erklæringer
var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('forecast.xlsx');
    Sheet := Book.Sheets[1];
    // A1 spiller tre rækker; A2 er optaget, så resultatet er #SPILL!
    Sheet.Range['A1'].SetDynamicArrayFormula('=SEQUENCE(3)');
    Sheet.Cells[2, 1].Value := 'blocker';
    Book.Recalculate;
    // En højere CalcId overlever; writeren hæver den kun til 191029, hvis lavere
    Book.CalcId := 191029;
    Book.SaveAs('forecast-out.xlsx');   // rich error parts skrevet automatisk
  finally
    Book.Free;
  end;
end;

Man bygger aldrig Rich Data-parts selv. Før skrivning scanner TXLSXWorkbook hver celle for #SPILL!- eller #CALC!-værdier, opretter eller udvider xl/metadata.xml og de to rich value-parts og dropper forældede value-metadata-links fra celler, hvis fejl siden er ændret eller ryddet. Den eksplicitte CalcId-tildeling ovenfor er valgfri og kun vist for at gøre pointen, at den respekteres

Array-tekstcacher hører hjemme i t="str", ikke shared strings

Samme stille fejl viser sig for tekstresultater inde i arrays. HotXLS gemmer den cachede tekst af en formelcelle og af hver follower-celle i et CSE- eller dynamisk array-range som t="str" med strengliteralen i <v>. At skrive de caches som shared strings med t="s" får Excel til at vise #VALUE! i array-cellerne, når den læser den cachede fil

<!-- Korrekt: literal tekst-cache på en array-follower -->
<c r="B3" t="str"><v>Northwind</v></c>

<!-- Forkert: shared-string-indeks, Excel viser #VALUE! fra cachen -->
<c r="B3" t="s"><v>17</v></c>

Denne er let at få forkert, for t="s" er det rigtige valg for almindelige værdiceller, og de fleste writers router hver streng gennem shared string-tabellen som standard. Fælden er i verifikationen. Genberegn i Excel, og array-cellerne viser den korrekte tekst, hvilket beviser, at formlen er fin, og beviser intet om cachen, man skrev. Åbner ens brugere rapporter med manuel beregning, eller læser et downstream-værktøj cachede værdier uden at beregne, er shared string-versionen simpelthen i stykker

Formel-lagringssyntaks: indledende lighedstegn, kommaer og _xlws

En relateret oprydning lander i samme writer: formler, der går ind i OOXML, gemmes i filgrammatikken, ikke den grammatik, en bruger tastede. HotXLS beholder den tekst, man tildelte Formula, til round-trip-visning, men den serialiserede formel dropper det indledende =, bruger kommaer mellem funktionsargumenter og lader array-konstant række-separatorer og citeret tekst være. FILTER og SORT gemmes med _xlfn._xlws.-præfikset, som Excel forventer til de worksheet-only-funktioner, mens andre future-funktioner som SEQUENCE får det almindelige _xlfn.-præfiks

// Tastet med semikolon-separatorer, gemt som
//   _xlfn._xlws.FILTER(A2:A50,C2:C50>1000)
Sheet.Range['E2'].SetDynamicArrayFormula('=FILTER(A2:A50;C2:C50>1000)');
Book.Recalculate;   // ingen række over 1000 giver #CALC!, gemt med Rich Data

Rækkefølge betyder noget inde i writeren. XML-escaping kører efter syntaks-normaliseringen, aldrig før, af samme grund som de diagnostiske pakker behøver en parser: en separator-omskrivning anvendt på allerede escaped tekst ville lemlæste entiteter som &gt; i sammenligningsoperatorer

Hvordan verificerer man en cached fejl uden at narre sig selv?

Den eneste troværdige test er at åbne filen i Excel med beregning allerede sat til manuel, læse cellerne og først derefter genberegne. Excel arver beregningsmoden fra den første arbejdsbog åbnet i en session, så vores acceptance-harness opretter først sin egen seed-arbejdsbog, skifter den til manuel og åbner så filen under test. Prober, der sprang seed-trinnet over, beviste kun, at filen åbnede og beregnede korrekt. Det, de mærkede som cached, var allerede genberegnet

Den skel er hele artiklen i miniature. En moderne fejlcache er korrekt, når Excel viser den, før nogen beregning kører, og hvert krav ovenfor, fra lowercase GUID til t="str"-tekst-followers, blev fundet ved at tjekke den tilstand frem for den genberegnede. Til baggrunden for, hvordan HotXLS størrelsesbestemmer og ejer spill ranges fra starten, se dynamic array spill-formler i Delphi med HotXLS. Er symptomet en reparationsdialog frem for en forkert fejl, er pakke-niveau-reglerne i hvorfor Excel reparerer en gyldig XLSX det bedre udgangspunkt

FAQ

Behøver HotXLS nogen indstilling for at gemme #SPILL! og #CALC! korrekt?

Nej. HotXLS opdager #SPILL!- og #CALC!-værdier ved gem-tidspunktet og skriver Rich Data-parts, fileVersion, minimum calcId og microsoft.com:RD-funktionen automatisk

Hvorfor indeholder celle-XML'en stadig #VALUE!?

Den legacy SpreadsheetML-fejltype har ingen literal for #SPILL! eller #CALC!, så #VALUE! er den bagudkompatible fallback. Aktuel Excel læser den rigtige fejl gennem vm-value-metadata-linket

Sænker HotXLS en CalcId, jeg har sat selv?

Nej. TXLSXWorkbook.CalcId hæves kun til 191029 i den skrevne fil, når en moderne fejl er til stede, og en højere værdi, man konfigurerede, beholdes som den er

HotXLS læser og skriver XLSX, XLSM og legacy XLS fra Delphi og C++Builder med sin egen beregningsmotor, dynamiske arrays inkluderet. Detaljer og trial-download finder man på HotXLS Delphi spreadsheet component-siden