Excel solo confía en un #SPILL! o #CALC! cacheado cuando el workbook carga 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 cada vez que una celda guardada carga uno de esos errores
Si falta cualquier pieza, la falla rara vez es ruidosa. A veces Excel 16 se niega a abrir el archivo directamente. Más a menudo abre bien y muestra #VALUE! donde su motor de cálculo produjo #SPILL!, lo que parece un bug de cálculo y lo manda a buscar a través de código de fórmulas que está perfectamente correcto. Presione F9 y el error correcto aparece, así que el problema tiende a cerrarse como "en mi máquina funciona" hasta que un cliente abre el reporte con el cálculo automático apagado. Este post recorre qué hace cada pieza, la trampa de la capitalización del GUID que parece que no puede importar, y la regla del cache de texto de arreglos que falla exactamente de la misma forma silenciosa
¿Por qué Excel muestra #VALUE! en lugar de un #SPILL! cacheado?
Excel muestra #VALUE! porque eso es literalmente lo que la celda almacena. El tipo de error legado en SpreadsheetML de 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 el fallback retrocompatible y cuelgan el tipo de error real de un índice de metadatos de valor
Ese índice, el atributo vm, apunta a xl/metadata.xml, que apunta a un Rich Data value que describe el error real. Los lectores viejos ven #VALUE! y siguen. El Excel actual sigue la cadena y muestra el error moderno, pero solo si decide que la cadena es confiable. Así se ve en disco una fórmula derramada y bloqueada:
<!-- sheet1.xml: literal legado más un apuntador 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 error type 8 con offsets de columna y fila para #SPILL!, y el error type 13 sin offsets para #CALC!. El <v> legado 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 realmente revisa
El bloque future-metadata de XLRICHVALUE debe usar el URI de extensión {3e2802c4-a4d2-4d8b-9148-e3be6c30e623} en minúsculas, carácter por carácter. Ponga en mayúsculas las letras hex y el build local de Excel 16 se negó a abrir el workbook de forma normal, aunque cada parte XML parseaba, cada relationship se resolvía, y cada conteo 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>Todos los que han trabajado con COM o con el registro saben que los GUID se comparan sin distinguir mayúsculas, y ese hábito es exactamente lo que muerde aquí. Lo aislamos por el camino lento: armamos un paquete mínimo que Excel rechazaba, y luego cambiamos una cosa a la vez. Reemplazar los content types no lo arregló. Agregar una parte de rich value types no lo arregló. Cambiar prefijos de namespace o reescribir los rich value data no lo arregló. Pasar a minúsculas ese único atributo uri lo arregló, y el mismo paquete entonces abrió y mostró tanto #CALC! como #SPILL!. Trate el URI de extensión como un string literal opaco, cópielo de la definición del formato, y nunca lo pase por un formateador de GUID que elija su propia capitalización
Hay un corolario práctico para cualquiera que escriba paquetes de diagnóstico a mano. Haga las ediciones con un parser XML, no con un reemplazo de texto global sobre XML serializado. Una sustitución general apuntada a un solo token puede corromper entidades como > en otras partes del part, y entonces usted está 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 lugar, Excel todavía ignoraba el error cacheado hasta que la parte del workbook se declaró como proveniente de un motor de cálculo moderno. HotXLS escribe tres declaraciones en workbook.xml cada vez que una celda guardada carga #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 metadatos de workbook llanos de ECMA-376 Parte 1, y un lastEdited / lowestEdited de 7 le dice a Excel que el archivo fue guardado por última vez por una generación de aplicación 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 acepta para estos caches. La entrada microsoft.com:RD en la extensión de calculation features, en el namespace http://schemas.microsoft.com/office/spreadsheetml/2018/calcfeatures, declara que el motor que produjo los valores cacheados evaluó con semántica de Rich Data. Entregar solo el subtipo, solo la parte de types, o solo prefijos y rutas de relationship distintos no cambió el resultado. Las tres declaraciones tuvieron que estar presentes juntas
Tres detalles de cómo HotXLS los aplica valen conocerlos si usted fija estos valores por su cuenta:
TXLSXWorkbook.CalcIdno se sobreescribe. El escritor sube el valor emitido a 191029 solo en la salida, así que un caller que configuró unCalcIdmayor lo conserva, y la propiedad en memoria queda exactamente como usted la asignó- Las calculation features existentes y las extensiones de workbook no relacionadas que vinieron con un archivo abierto se preservan, no se reemplazan
- El escritor revisa si ya existe una feature
microsoft.com:RDantes de agregar 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 derrama 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 rich error se escriben automáticamente
finally
Book.Free;
end;
end;Usted nunca construye las partes de Rich Data por su cuenta. 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 tira los enlaces de value-metadata viejos 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 arreglos van en t="str", no en shared strings
La misma falla silenciosa aparece con los resultados de texto dentro de arreglos. HotXLS guarda el texto cacheado de una celda de fórmula, y de cada celda seguidora en un rango CSE o dynamic array, 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 arreglo cuando lee el archivo cacheado
<!-- Correcto: cache de texto literal en un seguidor de arreglo -->
<c r="B3" t="str"><v>Northwind</v></c>
<!-- Incorrecto: í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 los escritores enruta cada cadena por la shared string table por defecto. La trampa está en la verificación. Recalcule en Excel y las celdas del arreglo muestran el texto correcto, lo que prueba que la fórmula está bien y no prueba nada sobre el cache que usted escribió. Si sus usuarios abren reportes con cálculo manual, o una herramienta aguas abajo lee valores cacheados sin calcular, la versión de shared-string está simplemente rota
Sintaxis de almacenamiento de fórmulas: igual 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 un usuario escribió. HotXLS conserva el texto que usted asignó a Formula para el despliegue de round-trip, pero la fórmula serializada tira el = inicial, usa comas entre los argumentos de función, y deja tranquilos los separadores de fila de constantes de arreglo y el texto entre comillas. FILTER y SORT se almacenan con el prefijo _xlfn._xlws. que Excel espera para esas funciones exclusivas de worksheet, mientras que otras future functions como SEQUENCE reciben el prefijo plano _xlfn. antes del nombre
// 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; // ninguna fila sobre 1000 da #CALC!, se guarda con Rich DataEl 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 sobre texto ya escapado destrozaría entidades como > en los operadores de comparación
Cómo verificar un error cacheado sin engañarse
La única prueba 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 de la semilla solo probaron que el archivo abría y calculaba correctamente. Lo que etiquetaban como cacheado ya había sido recalculado
Esa distinción es el artículo entero en miniatura. Un cache de error moderno es correcto cuando Excel lo muestra antes de que corra cualquier cálculo, y cada requisito de arriba, desde el GUID en minúsculas hasta los seguidores de texto t="str", se encontró revisando ese estado y no el recalculado. Para el trasfondo de cómo HotXLS dimensiona y es dueño de los rangos de derrame en primer lugar, vea fórmulas dynamic array con spill en Delphi con HotXLS. Si su 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 el mejor punto de partida
Preguntas frecuentes
¿HotXLS necesita algún ajuste para guardar #SPILL! y #CALC! correctamente?
No. HotXLS detecta los valores #SPILL! y #CALC! al momento de guardar y escribe las partes de Rich Data, el fileVersion, el calcId mínimo y la feature microsoft.com:RD automáticamente
¿Por qué el XML de la celda todavía contiene #VALUE!?
El tipo de error legado de SpreadsheetML no tiene literal para #SPILL! ni para #CALC!, así que #VALUE! es el fallback retrocompatible. El Excel actual lee el error real a través del enlace de value-metadata vm
¿HotXLS bajará un CalcId que yo mismo fijé?
No. TXLSXWorkbook.CalcId solo se sube a 191029 en el archivo escrito cuando hay un error moderno presente, y un valor mayor que usted configuró se conserva tal cual
HotXLS lee y escribe XLSX, XLSM y XLS legado desde Delphi y C++Builder con su propio motor de cálculo, dynamic arrays incluidos. Detalles y descarga de prueba están en la página del componente de hojas de cálculo HotXLS para Delphi