Por qué Excel ignora los errores cacheados #SPILL! y #CALC!

Excel solo se fía de un #SPILL! o #CALC! cacheado cuando el workbook lleva el contrato completo de errores modernos: metadatos Rich Data cuyo URI de extensión XLRICHVALUE es el GUID en minúsculas, un fileVersion de 7, un calcId de al menos 191029 y la feature de cálculo microsoft.com:RD. HotXLS, el componente XLSX nativo para Delphi, emite los cuatro siempre que una celda guardada contiene uno de esos errores

Falla cualquier pieza y el fallo rara vez es ruidoso. A veces Excel 16 se niega siquiera a abrir el archivo. Lo más común es que abra bien y muestre #VALUE! donde tu motor de cálculo produjo #SPILL!, lo que parece un bug de cálculo y te manda a buscar a través de código de fórmulas perfectamente correcto. Pulsas F9 y aparece el error correcto, así que el problema tiende a cerrarse como "en mi máquina funciona" hasta que un cliente abre el informe con el cálculo automático apagado. Este post recorre qué hace cada pieza, la trampa de las mayúsculas del GUID que parece que no puede importar en absoluto, y la regla del cache de texto de arrays que falla exactamente del mismo modo silencioso

¿Por qué Excel muestra #VALUE! en lugar de un #SPILL! cacheado?

Excel muestra #VALUE! porque eso es literalmente lo que la celda guarda. El tipo de error legacy de SpreadsheetML en ECMA-376 tiene una lista fija de literales de error, y #SPILL! y #CALC! no están en ella. Una celda con t="e" solo puede decir #NULL!, #DIV/0!, #VALUE!, #REF!, #NAME?, #NUM! o #N/A, así que las versiones más nuevas de Excel escriben #VALUE! como fallback retrocompatible y cuelgan el tipo de error real de un índice de value-metadata

Ese índice, el atributo vm, apunta a xl/metadata.xml, que apunta a un Rich Data value que describe el error real. Los lectores antiguos ven #VALUE! y siguen. El Excel actual sigue la cadena y muestra el error moderno, pero solo si decide que la cadena es confiable. Esto es lo que parece en disco una fórmula desbordada y bloqueada:

<!-- sheet1.xml: literal legacy más un puntero de value-metadata -->
<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 es #SPILL! -->
<rv s="0"><v>8</v><v>0</v><v>2</v></rv>

HotXLS escribe el tipo de error 8 con desplazamientos de columna y fila para #SPILL!, y el tipo 13 sin desplazamientos para #CALC!. El <v> legacy se queda en #VALUE! en ambos casos a propósito, porque ese es el valor que se supone que ve cualquier lector que ignore Rich Data

El GUID en minúsculas que Excel 16 comprueba de verdad

El bloque de future-metadata XLRICHVALUE debe usar el URI de extensión {3e2802c4-a4d2-4d8b-9148-e3be6c30e623} en minúsculas, carácter por carácter. Pon en mayúsculas las letras hex y la build local de Excel 16 se negaba a abrir el workbook con normalidad, aunque cada parte XML parseaba, cada relationship resolvía y cada recuento e índice cuadraban

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

Cualquiera que haya trabajado con COM o el registro sabe que los GUID se comparan sin distinguir mayúsculas, y ese hábito es justo lo que muerde aquí. Lo aislamos por el camino lento: construimos un paquete mínimo que Excel rechazaba y fuimos cambiando una cosa cada vez. Sustituir los content types no lo arregló. Añadir una parte de rich value types no lo arregló. Cambiar prefijos de namespace o reescribir los rich value data no lo arregló. Poner en minúsculas ese único atributo uri lo arregló, y el mismo paquete abrió entonces y mostró tanto #CALC! como #SPILL!. Trata el URI de extensión como un string literal opaco, cópialo de la definición del formato y jamás lo pases por un formateador de GUID que elija sus propias mayúsculas

Hay un corolario práctico para cualquiera que escriba paquetes de diagnóstico a mano. Haz las ediciones con un parser XML, no con un reemplazo global de texto sobre XML serializado. Una sustitución indiscriminada apuntada a un token puede corromper entidades como > en otras partes del archivo, y entonces estás depurando una sonda rota en lugar de la pregunta original

¿Qué más necesita Excel para leer un error moderno cacheado?

Las partes de metadatos y sus relationships son necesarias pero no suficientes. Con una cadena Rich Data perfectamente válida en su sitio, Excel seguía ignorando el error cacheado hasta que la parte del workbook se declaró como procedente de un motor de cálculo moderno. HotXLS escribe tres declaraciones en workbook.xml siempre que una celda guardada contiene #SPILL! o #CALC!:

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

El elemento fileVersion es metadato de workbook corriente de ECMA-376 Part 1, y un lastEdited / lowestEdited de 7 le dice a Excel que el archivo lo guardó por última vez una generación de aplicaciones que entiende Rich Data. El calcId de calcPr identifica la versión del motor de cálculo, y 191029 es el mínimo que encontramos que Excel aceptaba para estos caches. La entrada microsoft.com:RD en la extensión de features de cálculo, en el namespace http://schemas.microsoft.com/office/spreadsheetml/2018/calcfeatures, declara que el motor que produjo los valores cacheados evaluó con semántica Rich Data. Suministrar solo el subtipo, solo la parte de types, o solo prefijos y rutas de relationship distintos no cambió el resultado. Las tres declaraciones tenían que estar juntas

Tres detalles de cómo los aplica HotXLS merecen conocerse si fijas estos valores tú mismo:

• TXLSXWorkbook.CalcId no se sobreescribe. El escritor sube el valor emitido a 191029 solo en la salida, así que un caller que configuró un CalcId mayor lo conserva, y la propiedad en memoria se queda exactamente como la asignaste
• Las features de cálculo existentes y las extensiones de workbook ajenas que vinieron con un archivo abierto se conservan, no se sustituyen
• El escritor comprueba si ya existe una feature microsoft.com:RD antes de añadir una, así que guardar el mismo workbook repetidamente nunca apila declaraciones duplicadas

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('forecast.xlsx');
    Sheet := Book.Sheets[1];
    // A1 se desborda tres filas; A2 está ocupada, así que el resultado es #SPILL!
    Sheet.Range['A1'].SetDynamicArrayFormula('=SEQUENCE(3)');
    Sheet.Cells[2, 1].Value := 'blocker';
    Book.Recalculate;
    // Un CalcId mayor sobrevive; el escritor solo lo sube a 191029 si es menor
    Book.CalcId := 191029;
    Book.SaveAs('forecast-out.xlsx');   // las partes de error rich se escriben solas
  finally
    Book.Free;
  end;
end;

Tú nunca construyes las partes Rich Data. Antes de escribir, TXLSXWorkbook escanea cada celda en busca de valores #SPILL! o #CALC!, crea o extiende xl/metadata.xml y las dos partes de rich value, y suelta los enlaces de value-metadata rancios de las celdas cuyo error cambió o se limpió desde entonces. La asignación explícita de CalcId de arriba es opcional y solo se muestra para dejar claro que se respeta

Los caches de texto de array van en t="str", no en shared strings

El mismo fallo silencioso aparece con resultados de texto dentro de arrays. HotXLS guarda el texto cacheado de una celda de fórmula, y de cada celda follower en un rango CSE o de array dinámico, como t="str" con la cadena literal en <v>. Escribir esos caches como shared strings con t="s" hace que Excel muestre #VALUE! en las celdas del array cuando lee el archivo cacheado

<!-- Correcto: cache de texto literal en un follower de array -->
<c r="B3" t="str"><v>Northwind</v></c>

<!-- Mal: índice de shared string, Excel muestra #VALUE! desde el cache -->
<c r="B3" t="s"><v>17</v></c>

Este es fácil de hacer mal porque t="s" es la elección correcta para celdas de valor ordinarias, y la mayoría de escritores enrutan cada cadena por la tabla de shared strings por defecto. La trampa está en la verificación. Recalculas en Excel y las celdas del array muestran el texto correcto, lo que demuestra que la fórmula está bien y no demuestra nada sobre el cache que escribiste. Si tus usuarios abren informes con cálculo manual, o una herramienta posterior lee valores cacheados sin calcular, la versión de shared string está simplemente rota

Sintaxis de almacenamiento de fórmulas: equals inicial, comas y _xlws

Una limpieza relacionada aterriza en el mismo escritor: las fórmulas que van a OOXML se almacenan en la gramática del archivo, no en la gramática que escribió un usuario. HotXLS conserva el texto que asignaste a Formula para mostrarlo en el round trip, pero la fórmula serializada suelta el = inicial, usa comas entre argumentos de función y deja quietos los separadores de fila de constantes de array y el texto entrecomillado. FILTER y SORT se almacenan con el prefijo _xlfn._xlws. que Excel espera para esas funciones exclusivas de worksheet, mientras que otras funciones futuras como SEQUENCE llevan el prefijo _xlfn. simple

// Escrito con separadores de punto y coma, se almacena como
//   _xlfn._xlws.FILTER(A2:A50,C2:C50>1000)
Sheet.Range['E2'].SetDynamicArrayFormula('=FILTER(A2:A50;C2:C50>1000)');
Book.Recalculate;   // sin filas sobre 1000 da #CALC!, se guarda con Rich Data

El orden importa dentro del escritor. El escape XML corre después de la normalización de sintaxis, nunca antes, por la misma razón de que los paquetes de diagnóstico necesitan un parser: una reescritura de separadores aplicada a texto ya escapado destrozaría entidades como > en los operadores de comparación

Cómo verificar un error cacheado sin engañarte

El único test confiable es abrir el archivo en Excel con el cálculo ya puesto en manual, leer las celdas y solo entonces recalcular. Excel hereda el modo de cálculo del primer workbook abierto en una sesión, así que nuestro harness de aceptación primero crea su propio workbook semilla, lo cambia a manual y luego abre el archivo bajo prueba. Las sondas que se saltaban el paso semilla solo demostraban que el archivo abría y calculaba correctamente. Lo que etiquetaban como cacheado ya se había recalculado

Esa distinción es todo el artículo en miniatura. Un cache de error moderno es correcto cuando Excel lo muestra antes de que corra cálculo alguno, y cada requisito de arriba, desde el GUID en minúsculas hasta los followers de texto t="str", se encontró comprobando ese estado y no el recalculado. Para el trasfondo de cómo HotXLS dimensiona y es dueño de los rangos de spill en primer lugar, véase fórmulas de spill de array dinámico en Delphi con HotXLS. Si tu síntoma es un aviso de reparación en lugar de un error equivocado, las reglas a nivel de paquete de por qué Excel repara un XLSX válido son mejor punto de partida

FAQ

¿Necesita HotXLS algún ajuste para guardar #SPILL! y #CALC! correctamente?

No. HotXLS detecta los valores #SPILL! y #CALC! al guardar y escribe las partes Rich Data, el fileVersion, el calcId mínimo y la feature microsoft.com:RD automáticamente

¿Por qué el XML de la celda sigue conteniendo #VALUE!?

El tipo de error legacy de SpreadsheetML no tiene literal para #SPILL! ni #CALC!, así que #VALUE! es el fallback retrocompatible. El Excel actual lee el error real a través del enlace de value-metadata vm

¿Bajará HotXLS un CalcId que yo fije?

No. TXLSXWorkbook.CalcId solo se sube a 191029 en el archivo escrito cuando hay un error moderno presente, y un valor mayor que hayas configurado se conserva tal cual

HotXLS lee y escribe XLSX, XLSM y XLS legacy desde Delphi y C++Builder con su propio motor de cálculo, arrays dinámicos incluidos. Detalles y descarga de prueba en la página del HotXLS Delphi spreadsheet component