LlosLab Software Development BlogTechnische Notizen für PDF-, Delphi- und Dokumentensoftware-Entwickler.

Technischer Artikel

Warum Excel gecachte #SPILL!- und #CALC!-Fehler ignoriert

HotXLS Delphi Component

Excel vertraut einem gecachten #SPILL! oder #CALC! nur, wenn die Mappe den vollständigen Modern-Error-Vertrag trägt: Rich-Data-Metadaten, deren XLRICHVALUE-Extension-URI die kleingeschriebene GUID ist, ein fileVersion von 7, ein calcId von mindestens 191029 und das Calculation-Feature microsoft.com:RD. HotXLS, die native Delphi-XLSX-Komponente, emittiert alle vier, sobald eine gespeicherte Zelle einen dieser Fehler hält

Fehlt ein Baustein, ist das Versagen selten laut. Manchmal verweigert Excel 16 das Öffnen der Datei komplett. Meist öffnet sie sich aber sauber und zeigt #VALUE!, wo Ihre Berechnungs-Engine #SPILL! produzierte – das sieht nach einem Berechnungs-Bug aus und schickt Sie auf die Jagd durch Formelcode, der völlig korrekt ist. Drücken Sie F9, erscheint der richtige Fehler; der Fall wird also gern als „läuft auf meiner Maschine“ geschlossen, bis ein Kunde den Report mit ausgeschalteter automatischer Berechnung öffnet. Dieser Post geht durch, was jeder Baustein tut, die GUID-Casing-Falle, die unmöglich relevant aussehen kann, und die Array-Text-Cache-Regel, die auf exakt dieselbe leise Weise failt

Warum zeigt Excel #VALUE! statt eines gecachten #SPILL!?

Excel zeigt #VALUE!, weil das buchstäblich das ist, was die Zelle speichert. Der Legacy-Error-Typ in ECMA-376 SpreadsheetML hat eine feste Liste von Fehlerliteralen, und #SPILL! und #CALC! stehen nicht darauf. Eine Zelle mit t="e" kann nur #NULL!, #DIV/0!, #VALUE!, #REF!, #NAME?, #NUM! oder #N/A sagen; neuere Excel-Versionen schreiben deshalb #VALUE! als rückwärtskompatiblen Fallback und hängen den echten Fehlertyp an einen Value-Metadata-Index

Dieser Index, das vm-Attribut, zeigt in xl/metadata.xml, das wiederum in einen Rich-Data-Wert zeigt, der den tatsächlichen Fehler beschreibt. Ältere Reader sehen #VALUE! und gehen weiter. Aktuelles Excel folgt der Kette und zeigt den modernen Fehler, aber nur, wenn es die Kette für vertrauenswürdig befindet. So sieht eine gespillte und blockierte Formel auf der Platte aus:

<!-- sheet1.xml: Legacy-Literal plus Value-Metadata-Zeiger -->
<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 ist #SPILL! -->
<rv s="0"><v>8</v><v>0</v><v>2</v></rv>

HotXLS schreibt Fehlertyp 8 mit Spalten- und Zeilen-Offsets für #SPILL! und Fehlertyp 13 ohne Offsets für #CALC!. Das Legacy-<v> bleibt in beiden Fällen absichtlich #VALUE!, denn das ist der Wert, den jeder Reader, der Rich Data ignoriert, sehen soll

Die kleingeschriebene GUID, die Excel 16 tatsächlich prüft

Der XLRICHVALUE-Future-Metadata-Block muss die Extension-URI {3e2802c4-a4d2-4d8b-9148-e3be6c30e623} kleingeschrieben benutzen, Zeichen für Zeichen. Wer die Hex-Buchstaben großschreibt, dem verweigert der lokale Excel-16-Build das normale Öffnen der Mappe, obwohl jedes XML-Part parste, jede Beziehung auflöste und jede Anzahl und jeder Index stimmte

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

Wer mit COM oder der Registry gearbeitet hat, weiß, dass GUIDs case-insensitiv verglichen werden – und genau diese Gewohnheit beißt hier. Wir haben es auf die langsame Weise isoliert: ein minimales Paket gebaut, das Excel ablehnte, dann eine Sache nach der anderen getauscht. Die Content-Types zu ersetzen fixte es nicht. Ein Rich-Value-Types-Part hinzuzufügen fixte es nicht. Namespace-Präfixe zu ändern oder die Rich-Value-Daten umzuschreiben fixte es nicht. Das eine uri-Attribut kleinzuschreiben fixte es, und dasselbe Paket öffnete danach und zeigte sowohl #CALC! als auch #SPILL!. Behandeln Sie die Extension-URI als opakes String-Literal, kopieren Sie sie aus der Formatdefinition und schicken sie nie durch einen GUID-Formatter, der sein eigenes Casing wählt

Für alle, die Diagnose-Pakete von Hand schreiben, gibt es eine praktische Konsequenz: Machen Sie die Änderungen mit einem XML-Parser, nicht mit globalem Text-Ersetzen über serialisiertes XML. Eine pauschale Ersetzung, die auf ein Token zielt, kann woanders im Part Entities wie &gt; korrumpieren, und dann debuggen Sie eine kaputte Sonde statt der ursprünglichen Frage

Was braucht Excel sonst noch, um einen gecachten modernen Fehler zu lesen?

Die Metadaten-Parts und ihre Beziehungen sind nötig, aber nicht hinreichend. Mit einer völlig gültigen Rich-Data-Kette an Bord ignorierte Excel den gecachten Fehler trotzdem, bis der Workbook-Part sich als aus einer modernen Berechnungs-Engine stammend deklarierte. HotXLS schreibt drei Deklarationen in workbook.xml, sobald eine gespeicherte Zelle #SPILL! oder #CALC! hält:

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

Das fileVersion-Element ist schlichtes ECMA-376-Part-1-Workbook-Metadatum, und lastEdited / lowestEdited von 7 sagen Excel, dass die Datei zuletzt von einer Anwendungsgeneration gespeichert wurde, die Rich Data versteht. Das calcId auf calcPr identifiziert die Berechnungs-Engine-Version, und 191029 ist das Minimum, das wir Excel für diese Caches akzeptieren sahen. Der microsoft.com:RD-Eintrag in der Calculation-Features-Extension, im Namespace http://schemas.microsoft.com/office/spreadsheetml/2018/calcfeatures, deklariert, dass die Engine, die die gecachten Werte produzierte, mit Rich-Data-Semantik ausgewertet hat. Nur den Subtypen zu liefern, nur den Types-Part oder nur andere Präfixe und Relationship-Pfade änderte das Ergebnis nicht. Alle drei Deklarationen mussten gemeinsam vorhanden sein

Drei Details, wie HotXLS sie anwendet, sind Wissen wert, wenn Sie diese Werte selbst setzen:

  • TXLSXWorkbook.CalcId wird nicht überschrieben. Der Writer hebt den emittierten Wert nur in der Ausgabe auf 191029, ein Aufrufer mit höher konfigurierter CalcId behält sie also, und die In-Memory-Property bleibt exakt das, was Sie zugewiesen haben
  • Bestehende Calculation-Features und unrelated Workbook-Extensions, die mit einer geöffneten Datei hereinkamen, werden erhalten, nicht ersetzt
  • Der Writer prüft auf ein vorhandenes microsoft.com:RD-Feature, bevor er eines hinzufügt; dasselbe Workbook wiederholt zu speichern stapelt also nie doppelte Deklarationen
var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('forecast.xlsx');
    Sheet := Book.Sheets[1];
    // A1 spillt drei Zeilen; A2 ist belegt, das Ergebnis ist also #SPILL!
    Sheet.Range['A1'].SetDynamicArrayFormula('=SEQUENCE(3)');
    Sheet.Cells[2, 1].Value := 'blocker';
    Book.Recalculate;
    // Ein höherer CalcId überlebt; der Writer hebt ihn nur auf 191029, wenn niedriger
    Book.CalcId := 191029;
    Book.SaveAs('forecast-out.xlsx');   // Rich-Error-Parts werden automatisch geschrieben
  finally
    Book.Free;
  end;
end;

Die Rich-Data-Parts bauen Sie nie selbst. Vor dem Schreiben scannt TXLSXWorkbook jede Zelle nach #SPILL!- oder #CALC!-Werten, legt xl/metadata.xml und die beiden Rich-Value-Parts an oder erweitert sie und wirft veraltete Value-Metadata-Links aus Zellen, deren Fehler inzwischen geändert oder geleert wurde. Die explizite CalcId-Zuweisung oben ist optional und dient nur der Demonstration, dass sie respektiert wird

Array-Text-Caches gehören in t="str", nicht in Shared Strings

Dasselbe leise Versagen zeigt sich bei Textergebnissen innerhalb von Arrays. HotXLS speichert den gecachten Text einer Formelzelle und jeder Follower-Zelle in einem CSE- oder Dynamic-Array-Range als t="str" mit dem literalen String in <v>. Diese Caches als Shared Strings mit t="s" zu schreiben lässt Excel beim Lesen der gecachten Datei in den Array-Zellen #VALUE! anzeigen

<!-- Korrekt: literaler Text-Cache auf einem Array-Follower -->
<c r="B3" t="str"><v>Northwind</v></c>

<!-- Falsch: Shared-String-Index, Excel zeigt #VALUE! aus dem Cache -->
<c r="B3" t="s"><v>17</v></c>

Das verwechselt sich leicht, denn t="s" ist die richtige Wahl für gewöhnliche Wertezellen, und die meisten Writer routen jeden String per Default durch die Shared-String-Tabelle. Die Falle sitzt in der Verifikation. In Excel neu zu berechnen lässt die Array-Zellen den korrekten Text zeigen – das beweist, dass die Formel in Ordnung ist, und beweist nichts über den Cache, den Sie geschrieben haben. Öffnen Ihre Nutzer Reports mit manueller Berechnung, oder liest ein nachgelagertes Tool gecachte Werte ohne zu rechnen, ist die Shared-String-Version schlicht kaputt

Formel-Speichersyntax: führendes Gleichheitszeichen, Kommas und _xlws

Eine verwandte Aufräumaktion landet im selben Writer: Formeln, die ins OOXML gehen, werden in der Datei-Grammatik gespeichert, nicht in der, die ein Nutzer getippt hat. HotXLS behält den Text, den Sie Formula zugewiesen haben, für den Roundtrip-Display, aber die serialisierte Formel lässt das führende = weg, nutzt Kommas zwischen Funktionsargumenten und lässt Array-Konstanten-Zeilenseparatoren und zitierten Text in Ruhe. FILTER und SORT werden mit dem _xlfn._xlws.-Präfix gespeichert, das Excel für diese nur-Worksheet-Funktionen erwartet, während andere Future-Functions wie SEQUENCE das schlichte _xlfn.-Präfix bekommen

// Mit Semikolon-Separatoren getippt, gespeichert als
//   _xlfn._xlws.FILTER(A2:A50,C2:C50>1000)
Sheet.Range['E2'].SetDynamicArrayFormula('=FILTER(A2:A50;C2:C50>1000)');
Book.Recalculate;   // keine Zeile über 1000 ergibt #CALC!, gespeichert mit Rich Data

Die Reihenfolge zählt im Writer. XML-Escaping läuft nach der Syntax-Normalisierung, nie davor, aus demselben Grund, aus dem die Diagnose-Pakete einen Parser brauchen: ein Separatoren-Rewrite auf bereits escapedem Text würde Entities wie &gt; in Vergleichsoperatoren verunstalten

Wie man einen gecachten Fehler verifiziert, ohne sich selbst zu täuschen

Der einzige vertrauenswürdige Test: die Datei in Excel öffnen, während die Berechnung bereits auf manuell steht, die Zellen lesen und erst dann neu berechnen. Excel erbt den Berechnungsmodus von der ersten Mappe, die in einer Session geöffnet wird; unser Acceptance-Harness erzeugt also zuerst eine eigene Seed-Mappe, schaltet sie auf manuell und öffnet dann die Datei unter Test. Sonden, die den Seed-Schritt übersprungen, bewiesen nur, dass die Datei sich öffnete und korrekt rechnete. Was auch immer sie als gecacht etikettierten, war bereits neu berechnet

Diese Unterscheidung ist der ganze Artikel im Kleinen. Ein moderner Fehlercache ist korrekt, wenn Excel ihn anzeigt, bevor irgendeine Berechnung läuft; und jede Anforderung von oben, von der kleingeschriebenen GUID bis zu t="str"-Text-Followern, wurde gefunden, indem dieser Zustand geprüft wurde statt der neu berechnete. Für den Hintergrund, wie HotXLS Spill-Ranges überhaupt bemisst und besitzt, siehe Dynamic-Array-Spill-Formeln in Delphi mit HotXLS. Ist Ihr Symptom eher ein Reparatur-Dialog als ein falscher Fehler, sind die Paket-Level-Regeln in warum Excel ein gültiges XLSX repariert der bessere Startpunkt

FAQ

Braucht HotXLS eine Einstellung, um #SPILL! und #CALC! korrekt zu speichern?

Nein. HotXLS erkennt #SPILL!- und #CALC!-Werte zur Speicherzeit und schreibt die Rich-Data-Parts, das fileVersion, das Minimum-calcId und das microsoft.com:RD-Feature automatisch

Warum enthält das Zellen-XML weiterhin #VALUE!?

Der Legacy-SpreadsheetML-Error-Typ hat kein Literal für #SPILL! oder #CALC!, #VALUE! ist also der rückwärtskompatible Fallback. Aktuelles Excel liest den echten Fehler über den vm-Value-Metadata-Link

Senkt HotXLS eine CalcId, die ich selbst setze?

Nein. TXLSXWorkbook.CalcId wird in der geschriebenen Datei nur auf 191029 gehoben, wenn ein moderner Fehler vorhanden ist; ein höherer, von Ihnen konfigurierter Wert bleibt, wie er ist

HotXLS liest und schreibt XLSX, XLSM und Legacy-XLS aus Delphi und C++Builder mit eigener Berechnungs-Engine, Dynamic Arrays eingeschlossen. Details und einen Trial-Download finden Sie auf der HotXLS Delphi spreadsheet component page