LlosLab Software Development BlogTekniska anteckningar för utvecklare av PDF-, Delphi- och dokumentprogramvara.

Teknisk artikel

Varför Excel ignorerar cachade #SPILL!- och #CALC!-fel

HotXLS Delphi Component

Excel litar bara på ett cachat #SPILL! eller #CALC! när arbetsboken bär det fullständiga moderna felkontraktet: Rich Data-metadata vars XLRICHVALUE-extension-URI är den gemena GUID:en, en fileVersion på 7, en calcId på minst 191029, och beräkningsfunktionen microsoft.com:RD. HotXLS, den inbyggda Delphi XLSX-komponenten, emitterar alla fyra närhelst en sparad cell håller ett av de felen

Missar du någon del blir misslyckandet sällan högt. Ibland vägrar Excel 16 öppna filen alls. Oftare öppnas den fint och visar #VALUE! där din kalkylmotor producerade #SPILL!, vilket ser ut som en beräkningsbugg och skickar dig på jakt i formelkod som är helt korrekt. Tryck F9 och rätt fel dyker upp, så problemet tenderar att stängas som "fungerar på min maskin" tills en kund öppnar rapporten med automatisk beräkning avstängd. Det här inlägget går igenom vad varje del gör, GUID-versalfällan som ser ut som om den inte kan spela roll, och regeln för arraytextcache som fallerar på exakt samma tysta sätt

Varför visar Excel #VALUE! i stället för en cachad #SPILL!?

Excel visar #VALUE! eftersom det är bokstavligen vad cellen lagrar. Den äldre feltypen i ECMA-376 SpreadsheetML har en fast lista av felleteraler, och #SPILL! och #CALC! finns inte på den. En cell med t="e" kan bara säga #NULL!, #DIV/0!, #VALUE!, #REF!, #NAME?, #NUM! eller #N/A, så nyare Excel-versioner skriver #VALUE! som bakåtkompatibel reserv och hänger den verkliga feltypen på ett värde-metaindex

Det indexet, attributet vm, pekar in i xl/metadata.xml, som pekar in i ett Rich Data-värde som beskriver det verkliga felet. Äldre läsare ser #VALUE! och går vidare. Aktuell Excel följer kedjan och visar det moderna felet, men bara om den bedömer att kedjan är pålitlig. Så här ser en spill-blockerad formel ut på disk:

<!-- sheet1.xml: gammal literal plus en value-metadata-pekare -->
<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 är #SPILL! -->
<rv s="0"><v>8</v><v>0</v><v>2</v></rv>

HotXLS skriver feltyp 8 med kolumn- och radoffset för #SPILL!, och feltyp 13 utan offset för #CALC!. Den äldre <v>:en stannar som #VALUE! i båda fallen med flit, eftersom det är värdet varje läsare som ignorerar Rich Data ska få se

Den gemena GUID:en som Excel 16 faktiskt kontrollerar

Future-metadatablocket XLRICHVALUE måste använda extension-URI:n {3e2802c4-a4d2-4d8b-9148-e3be6c30e623} med gemena bokstäver, tecken för tecken. Versal de hexadecimala bokstäverna och den lokala Excel 16-bygget vägrade öppna arbetsboken normalt, fast varje XML-del parsades, varje relation löstes, och varje antal och index stämde

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

Alla som arbetat med COM eller registret vet att GUID:er jämförs skiftlägesokänsligt, och just den vanan biter här. Vi isolerade det på det långsamma sättet: byggde ett minimipaket som Excel avvisade och bytte sedan en sak i taget. Att byta ut content types fixade det inte. Att lägga till en rich value types-del fixade det inte. Att ändra namnrymdsprefix eller skriva om rich value-data fixade det inte. Att göra det enda uri-attributet gemena bokstäver fixade det, och samma paket öppnade och visade sedan både #CALC! och #SPILL!. Behandla extension-URI:n som en opak strängliteral, kopiera den från formatdefinitionen och skicka den aldrig genom en GUID-formaterare som väljer egen versalhantering

Det finns en praktisk följd för alla som skriver diagnostikpaket för hand. Gör redigeringarna med en XML-parser, inte med en global textersättning över serialiserad XML. En blankettersättning riktad mot en token kan förstöra entiteter som &gt; någon annanstans i delen, och sedan felsöker du en trasig prob i stället för den ursprungliga frågan

Vad annat kräver Excel för att läsa ett cachat modernt fel?

Metadadelarna och deras relationer är nödvändiga men inte tillräckliga. Med en helt giltig Rich Data-kedja på plats ignorerade Excel ändå det cachade felet tills arbetsboksdelen deklarerade sig som kommande från en modern kalkylmotor. HotXLS skriver tre deklarationer i workbook.xml närhelst en sparad cell håller #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>

Elementet fileVersion är ren ECMA-376 Part 1-arbetsboksmetadata, och lastEdited / lowestEdited på 7 talar om för Excel att filen senast sparades av en applikationsgeneration som förstår Rich Data. calcId på calcPr identifierar kalkylmotorns version, och 191029 är det minimum vi fann Excel acceptera för de här cacherna. Posten microsoft.com:RD i beräkningsfunktionernas extension, i namnrymden http://schemas.microsoft.com/office/spreadsheetml/2018/calcfeatures, deklarerar att motorn som producerade de cachade värdena utvärderade med Rich Data-semantik. Att leverera bara subtypen, bara types-delen, eller bara andra prefix och relationsvägar ändrade inte utfallet. Alla tre deklarationerna måste finnas tillsammans

Tre detaljer i hur HotXLS applicerar dem är värda att veta om du sätter värdena själv:

  • TXLSXWorkbook.CalcId skrivs inte över. Skrivaren höjer det emitterade värdet till 191029 bara i utdata, så en anropare som konfigurerat en högre CalcId behåller den, och egenskapen i minnet stannar exakt som du tilldelade den
  • Befintliga beräkningsfunktioner och orelaterade arbetsboksextensioner som kom in med en öppnad fil bevaras, inte ersätts
  • Skrivaren kontrollerar efter en befintlig microsoft.com:RD-funktion innan den lägger till en, så att spara samma arbetsbok upprepade gånger aldrig staplar dubblettdeklarationer
var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('forecast.xlsx');
    Sheet := Book.Sheets[1];
    // A1 spill:ar tre rader; A2 är upptagen, så resultatet är #SPILL!
    Sheet.Range['A1'].SetDynamicArrayFormula('=SEQUENCE(3)');
    Sheet.Cells[2, 1].Value := 'blocker';
    Book.Recalculate;
    // En högre CalcId överlever; skrivaren höjer bara till 191029 om lägre
    Book.CalcId := 191029;
    Book.SaveAs('forecast-out.xlsx');   // rich error-delar skrivs automatiskt
  finally
    Book.Free;
  end;
end;

Du bygger aldrig Rich Data-delarna själv. Innan skrivningen skannar TXLSXWorkbook varje cell efter #SPILL!- eller #CALC!-värden, skapar eller utökar xl/metadata.xml och de två rich value-delarna, och släpper inaktuella value-metadata-länkar från celler vars fel ändrats eller rensats sedan. Den explicita CalcId-tilldelningen ovan är valfri och visas bara för att visa att den respekteras

Arraytextcacher hör hemma i t="str", inte delade strängar

Samma tysta misslyckande dyker upp för textresultat inne i arrayer. HotXLS sparar den cachade texten för en formelcell, och för varje följarecell i ett CSE- eller dynamiskt arrayintervall, som t="str" med literalsträngen i <v>. Att skriva de cacherna som delade strängar med t="s" får Excel att visa #VALUE! i arraycellerna när den läser den cachade filen

<!-- Korrekt: literal textcache på en array-följare -->
<c r="B3" t="str"><v>Northwind</v></c>

<!-- Fel: delad-sträng-index, Excel visar #VALUE! ur cachen -->
<c r="B3" t="s"><v>17</v></c>

Den här är lätt att få fel eftersom t="s" är rätt val för vanliga värdeceller, och de flesta skrivare dirigerar varje sträng genom den delade strängtabellen som standard. Fällan ligger i verifieringen. Räkna om i Excel och arraycellerna visar rätt text, vilket bevisar att formeln är fin och bevisar ingenting om cachen du skrev. Öppnar dina användare rapporter med manuell beräkning, eller läser ett nedströmsverktyg cachade värden utan att beräkna, är den delade-sträng-versionen helt enkelt trasig

Formellagringssyntax: inledande likhetstecken, kommatecken och _xlws

En relaterad städning landar i samma skrivare: formler som går in i OOXML lagras i filgrammatiken, inte i grammatiken en användare skrev. HotXLS behåller den text du tilldelade Formula för visning vid rundturer, men den serialiserade formeln tappar det inledande =, använder kommatecken mellan funktionsargument, och lämnar arraykonstantens radseparatorer och citerad text orörda. FILTER och SORT lagras med prefixet _xlfn._xlws. som Excel förväntar sig för de kalkylbladsfunktionerna, medan andra framtidsfunktioner som SEQUENCE får det enkla prefixet _xlfn. framför namnet

// Inmatad med semikolonseparatorer, lagrad som
//   _xlfn._xlws.FILTER(A2:A50,C2:C50>1000)
Sheet.Range['E2'].SetDynamicArrayFormula('=FILTER(A2:A50;C2:C50>1000)');
Book.Recalculate;   // ingen rad över 1000 ger #CALC!, sparad med Rich Data

Ordningen spelar roll inne i skrivaren. XML-escaping körs efter syntaxnormaliseringen, aldrig före, av samma skäl som diagnostikpaketen behöver en parser: en separatoromskrivning applicerad på redan escapad text skulle förstöra entiteter som &gt; i jämförelseoperatorer

Hur verifierar du ett cachat fel utan att lura dig själv?

Det enda pålitliga testet är att öppna filen i Excel med beräkningen redan satt till manuell, läsa cellerna, och först därefter räkna om. Excel ärver beräkningsläget från den första arbetsboken som öppnas i en session, så vår acceptanssvep skapar först en egen utsädesarbetsbok, växlar den till manuell, och öppnar sedan filen under test. Probbar som hoppade över utsädessteget bevisade bara att filen öppnades och räknade korrekt. Vad de än kallade cachat hade redan räknats om

Den skillnaden är hela artikeln i miniatyr. En modern felcache är korrekt när Excel visar den innan någon beräkning körs, och varje krav ovan, från den gemena GUID:en till t="str"-textföljare, hittades genom att kontrollera det tillståndet snarare än det omräknade. För bakgrunden till hur HotXLS storleksbestämmer och äger spillintervall från början, se dynamiska array-spillformler i Delphi med HotXLS. Är ditt symptom en reparationsprompt snarare än ett felaktigt fel är paketnivåreglerna i varför Excel reparerar en giltig XLSX den bättre utgångspunkten

FAQ

Behöver HotXLS någon inställning för att spara #SPILL! och #CALC! korrekt?

Nej. HotXLS detekterar #SPILL!- och #CALC!-värden vid sparningen och skriver Rich Data-delarna, fileVersion, minimum calcId och funktionen microsoft.com:RD automatiskt

Varför innehåller cell-XML:n fortfarande #VALUE!?

Den äldre SpreadsheetML-feltypen har ingen literal för #SPILL! eller #CALC!, så #VALUE! är den bakåtkompatibla reserven. Aktuell Excel läser det verkliga felet genom länken vm för värde-metadata

Sänker HotXLS en CalcId jag satt själv?

Nej. TXLSXWorkbook.CalcId höjs bara till 191029 i den skrivna filen när ett modernt fel finns, och ett högre värde du konfigurerat behålls som det är

HotXLS läser och skriver XLSX, XLSM och äldre XLS från Delphi och C++Builder med sin egen kalkylmotor, dynamiska arrayer inkluderade. Detaljer och nedladdning av provversionen finns på sidan för HotXLS Delphi spreadsheet component