LlosLab Software Development BlogTechnische notities voor ontwikkelaars van PDF-, Delphi- en documentsoftware.

Technisch artikel

Waarom Excel gecachte #SPILL!- en #CALC!-fouten negeert

HotXLS Delphi Component

Excel vertrouwt een gecachte #SPILL! of #CALC! alleen wanneer het werkboek het volledige moderne foutencontract voert: Rich Data-metadata waarvan de XLRICHVALUE-extension-URI de GUID in kleine letters is, een fileVersion van 7, een calcId van minstens 191029, en de microsoft.com:RD-calculatiefeature. HotXLS, de native Delphi XLSX-component, zendt alle vier uit zodra een opgeslagen cel een van die fouten bevat

Mist u een onderdeel, dan is de mislukking zelden luidruchtig. Soms weigert Excel 16 het bestand helemaal te openen. Vaker opent het prima en toont #VALUE! waar uw rekenengine #SPILL! produceerde, wat eruitziet als een rekenbug en u laat zoeken door formulecode die volstrekt correct is. Druk op F9 en de juiste fout verschijnt, dus het probleem wordt vaak gesloten als "works on my machine" totdat een klant het rapport opent met automatische berekening uit. Dit bericht loopt langs wat elk onderdeel doet, de GUID-casing-valkuil die eruitziet alsof hij onmogelijk kan tellen, en de array-tekstcache-regel die op precies dezelfde stille manier faalt

Waarom toont Excel #VALUE! in plaats van een gecachte #SPILL!?

Excel toont #VALUE! omdat dat letterlijk is wat de cel bewaart. Het legacy-fouttype in ECMA-376 SpreadsheetML heeft een vaste lijst foutliterals, en #SPILL! en #CALC! staan er niet op. Een cel met t="e" kan alleen #NULL!, #DIV/0!, #VALUE!, #REF!, #NAME?, #NUM! of #N/A zeggen, dus nieuwere Excel-versies schrijven #VALUE! als achterwaarts-compatibele fallback en hangen het echte fouttype op aan een value-metadata-index

Die index, het attribuut vm, wijst naar xl/metadata.xml, dat weer wijst naar een Rich Data-waarde die de werkelijke fout beschrijft. Oudere lezers zien #VALUE! en gaan door. Huidige Excel volgt de keten en toont de moderne fout, maar alleen als hij beslist dat de keten betrouwbaar is. Zo ziet een ge-spillde en geblokkeerde formule er op schijf uit:

<!-- sheet1.xml: legacy literal plus een value-metadata-verwijzing -->
<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 is #SPILL! -->
<rv s="0"><v>8</v><v>0</v><v>2</v></rv>

HotXLS schrijft fouttype 8 met kolom- en rij-offsets voor #SPILL!, en fouttype 13 zonder offsets voor #CALC!. De legacy-<v> blijft in beide gevallen met opzet #VALUE!, want dat is de waarde die elke lezer die Rich Data negeert hoort te zien

De GUID in kleine letters die Excel 16 werkelijk controleert

Het XLRICHVALUE-future-metadatablok moet de extension-URI {3e2802c4-a4d2-4d8b-9148-e3be6c30e623} in kleine letters gebruiken, teken voor teken. Zet de hex-letters in hoofdletters en de lokale Excel 16-build weigerde het werkboek normaal te openen, hoewel elk XML-part parseerde, elke relationship oploste en elk aantal en elke index klopten

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

Iedereen die met COM of de registry heeft gewerkt weet dat GUID's hoofdletteronafhankelijk vergeleken worden, en die gewoonte is precies wat hier bijt. We isoleerden het op de trage manier: bouwden een minimaal pakket dat Excel weigerde en wisselden dan één ding per keer. De content types vervangen fixte het niet. Een rich value types-part toevoegen fixte het niet. Namespace-prefixen wijzigen of de rich value data herschrijven fixte het niet. Dat ene attribuut uri in kleine letters zetten fixte het wél, en hetzelfde pakket opende daarna en toonde zowel #CALC! als #SPILL!. Behandel de extension-URI als een ondoorzichte string-literal, kopieer hem uit de formatdefinitie en haal hem nooit door een GUID-formatter die zijn eigen casing kiest

Er is een praktische gevolgtrekking voor iedereen die diagnostische pakketten met de hand schrijft. Doe de bewerkingen met een XML-parser, niet met een globale tekstvervanging over geserialiseerde XML. Een algemene substitutie gericht op één token kan entiteiten zoals &gt; elders in het part beschadigen, en dan zit u een kapotte sonde te debuggen in plaats van de oorspronkelijke vraag

Wat heeft Excel verder nodig om een gecachte moderne fout te lezen?

De metadata-parts en hun relationships zijn noodzakelijk maar niet voldoende. Met een volstrekt geldige Rich Data-keten aanwezig, negeerde Excel de gecachte fout nog steeds totdat het workbookpart verklaarde van een moderne rekenengine af te komen. HotXLS schrijft drie declaraties in workbook.xml zodra een opgeslagen cel #SPILL! of #CALC! bevat:

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

Het element fileVersion is gewone ECMA-376 Part 1-werkboekmetadata, en lastEdited / lowestEdited van 7 vertellen Excel dat het bestand voor het laatst is opgeslagen door een applicatiegeneratie die Rich Data begrijpt. De calcId op calcPr identificeert de versie van de rekenengine, en 191029 is het minimum dat we Excel voor deze caches zagen accepteren. De microsoft.com:RD-entry in de calculation-features-extensie, in de namespace http://schemas.microsoft.com/office/spreadsheetml/2018/calcfeatures, verklaart dat de engine die de gecachte waarden produceerde met Rich Data-semantiek evalueerde. Alleen het subtype meegeven, alleen het types-part, of alleen andere prefixes en relationship-paden veranderde de uitkomst niet. Alle drie de declaraties moesten samen aanwezig zijn

Drie details in hoe HotXLS ze toepast zijn het weten waard als u deze waarden zelf instelt:

  • TXLSXWorkbook.CalcId wordt niet overschreven. De schrijver verhoogt de uitgezonden waarde alleen in de uitvoer naar 191029, dus een aanroeper die een hogere CalcId configureerde houdt die, en de property in het geheugen blijft exact wat u toewees
  • Bestaande calculation features en ongerelateerde werkboekextensies die met een geopend bestand binnenkwamen, worden behouden in plaats van vervangen
  • De schrijver controleert op een bestaande microsoft.com:RD-feature voordat hij er een toevoegt, dus hetzelfde werkboek herhaaldelijk opslaan stapelt nooit dubbele declaraties
var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('forecast.xlsx');
    Sheet := Book.Sheets[1];
    // A1 loopt drie rijen over; A2 is bezet, dus het resultaat is #SPILL!
    Sheet.Range['A1'].SetDynamicArrayFormula('=SEQUENCE(3)');
    Sheet.Cells[2, 1].Value := 'blocker';
    Book.Recalculate;
    // Een hogere CalcId overleeft; de schrijver verhoogt hem alleen naar 191029 als hij lager is
    Book.CalcId := 191029;
    Book.SaveAs('forecast-out.xlsx');   // rich error-parts automatisch weggeschreven
  finally
    Book.Free;
  end;
end;

U bouwt de Rich Data-parts nooit zelf. Vóór het schrijven scant TXLSXWorkbook elke cel op #SPILL!- of #CALC!-waarden, maakt xl/metadata.xml en de twee rich value-parts aan of breidt ze uit, en gooit verlopen value-metadata-verwijzingen weg van cellen waarvan de fout inmiddels veranderde of gewist werd. De expliciete CalcId-toewijzing hierboven is optioneel en alleen getoond om het punt te maken dat hij gerespecteerd wordt

Array-tekstcaches horen in t="str", niet in shared strings

Dezelfde stille mislukking toont zich voor tekstresultaten binnen arrays. HotXLS slaat de gecachte tekst van een formulecel, en van elke follower-cel in een CSE- of dynamic array-bereik, op als t="str" met de letterlijke string in <v>. Die caches als shared strings wegschrijven met t="s" laat Excel #VALUE! tonen in de arraycellen wanneer hij het gecachte bestand leest

<!-- Correct: letterlijke tekstcache op een array-follower -->
<c r="B3" t="str"><v>Northwind</v></c>

<!-- Fout: shared-string-index, Excel toont #VALUE! uit de cache -->
<c r="B3" t="s"><v>17</v></c>

Deze is makkelijk verkeerd te doen, want t="s" is de juiste keuze voor gewone waardecellen, en de meeste schrijvers sturen elke string standaard door de shared string table. De valkuil zit in de verificatie. Herbereken in Excel en de arraycellen tonen de juiste tekst, wat bewijst dat de formule goed is en niets bewijst over de cache die u schreef. Openen uw gebruikers rapporten met handmatige berekening, of leest een stroomafwaartse tool gecachte waarden zonder te berekenen, dan is de shared-string-versie simpelweg kapot

Formuleopslagsyntaxis: voorloopgelijkteken, komma's en _xlws

Een verwante opruiming landt in dezelfde schrijver: formules die OOXML ingaan worden bewaard in de bestandsgrammatica, niet in de grammatica die een gebruiker typte. HotXLS houdt de tekst die u aan Formula toewees aan voor de round-trip-weergave, maar de geserialiseerde formule laat het voorloopteken = vallen, gebruikt komma's tussen functieargumenten en laat rij-scheidingstekens in array-constanten en gequote tekst met rust. FILTER en SORT worden bewaard met het prefix _xlfn._xlws. dat Excel verwacht voor die worksheet-only-functies, terwijl andere future functions zoals SEQUENCE het kale prefix _xlfn. krijgen

// Getypt met puntkomma-scheidingstekens, opgeslagen als
//   _xlfn._xlws.FILTER(A2:A50,C2:C50>1000)
Sheet.Range['E2'].SetDynamicArrayFormula('=FILTER(A2:A50;C2:C50>1000)');
Book.Recalculate;   // geen rij boven 1000 geeft #CALC!, opgeslagen met Rich Data

De volgorde telt binnen de schrijver. XML-escaping draait na de syntaxisnormalisatie, nooit ervoor, om dezelfde reden dat de diagnostische pakketten een parser nodig hebben: een scheidingsteken-herschrijving toegepast op al ge-escape-te tekst zou entiteiten zoals &gt; in vergelijkingsoperatoren verminken

Hoe u een gecachte fout verifieert zonder uzelf voor de gek te houden

De enige betrouwbare test is het bestand openen in Excel met de berekening al op handmatig gezet, de cellen lezen en pas daarna herberekenen. Excel erft de berekeningsmodus van het eerste werkboek dat in een sessie wordt geopend, dus onze acceptance-harness maakt eerst een eigen seed-werkboek aan, zet dat op handmatig en opent dan het bestand onder test. Sondes die de seed-stap oversloegen bewezen alleen dat het bestand opende en correct berekende. Wat zij als gecacht labelden, was allang herberekend

Dat onderscheid is het hele artikel in het klein. Een moderne foutencache is correct wanneer Excel hem toont voordat er überhaupt berekend wordt, en elke eis hierboven — van de GUID in kleine letters tot t="str"-tekst-followers — is gevonden door die status te controleren in plaats van de herberekende. Voor de achtergrond over hoe HotXLS spill ranges in de eerste plaats afmett en beheert, zie dynamic array spill-formules in Delphi met HotXLS. Is uw symptoom een reparatieprompt in plaats van een verkeerde fout, dan zijn de pakketniveau-regels in waarom Excel een geldige XLSX repareert het betere startpunt

FAQ

Heeft HotXLS een instelling nodig om #SPILL! en #CALC! correct op te slaan?

Nee. HotXLS detecteert #SPILL!- en #CALC!-waarden bij het opslaan en schrijft de Rich Data-parts, de fileVersion, de minimale calcId en de microsoft.com:RD-feature automatisch

Waarom bevat de cel-XML nog steeds #VALUE!?

Het legacy-SpreadsheetML-fouttype heeft geen literal voor #SPILL! of #CALC!, dus #VALUE! is de achterwaarts-compatibele fallback. Huidige Excel leest de echte fout via de vm-value-metadata-link

Verlaagt HotXLS een CalcId die ik zelf instel?

Nee. TXLSXWorkbook.CalcId wordt alleen in het weggeschreven bestand verhoogd naar 191029 wanneer een moderne fout aanwezig is, en een hogere waarde die u configureerde blijft ongewijzigd

HotXLS leest en schrijft XLSX, XLSM en legacy XLS vanuit Delphi en C++Builder met zijn eigen rekenengine, dynamic arrays inbegrepen. Details en een proefdownload staan op de HotXLS Delphi spreadsheet component-pagina