Un seguidor de fórmula compartida en XLSX no lleva texto de fórmula. Su elemento <f t="shared" si="N"/> apunta a una celda maestra en otro lugar de la hoja, y el lector debe reconstruir el texto desplazando la fórmula maestra según la diferencia de fila y columna. HotXLS Component para Delphi y C++Builder realiza esa expansión al abrir el fichero, así que cada seguidor reporta una fórmula completa
Si alguna vez has cargado un XLSX del mundo real en una librería de terceros y has visto que una columna de mil fórmulas tiene texto en exactamente una celda y cadenas vacías en las otras 999, has topado con esta característica desde el lado equivocado. Nada está corrupto. El fichero está haciendo lo que ECMA-376 permite, y el lector simplemente se detuvo en el punto donde se detuvo el XML
¿Por qué la celda de fórmula compartida está vacía?
Porque el formato almacena deliberadamente la fórmula una sola vez. En ECMA-376 Parte 1 e ISO/IEC 29500-1, el elemento <f> (§18.3.1.40) lleva un atributo t del tipo ST_CellFormulaType, y el valor shared significa que esta celda participa en un grupo identificado por el atributo si. Exactamente una celda del grupo, la maestra, lleva además un atributo ref que da el rango al que se aplica el grupo, y solo esa celda lleva el texto de la fórmula como contenido del elemento. Cualquier otra celda del grupo es un seguidor. Repite t="shared" y el mismo si, y su contenido de elemento está vacío. Excel escribe estos grupos de forma agresiva, porque un relleno hacia abajo sobre una columna de 200.000 filas colapsa de 200.000 cadenas de fórmula a una cadena más 199.999 pequeños elementos marcador. El ahorro es real y el coste recae enteramente en el lector: sin expansión, el seguidor no tiene significado por sí solo
El desplazamiento es una traducción, no una copia de texto
HotXLS resuelve un seguidor localizando el maestro registrado bajo el mismo si, calculando el delta de fila y columna desde el anclaje del maestro hasta la celda actual, y traduciendo cada referencia de la fórmula maestra según ese delta. Las dimensiones relativas se mueven, las dimensiones absolutas no, y las referencias mixtas mueven solo su mitad no absoluta. Los literales de cadena se saltan por completo, así que una fórmula que contiene el texto "A1" conserva ese texto sin cambios en cada seguidor
const
// xl/worksheets/sheet1.xml, trimmed to the interesting cells
SheetXml: WideString=
'<row r="1"><c r="A1"><v>1</v></c>'+
'<c r="B1"><f t="shared" si="4" ref="B1:B3">'+
'A1+$A$1+A$1+$A1+"A1"+SUM(A1:A2)</f><v>7</v></c></row>'+
'<row r="2"><c r="B2"><f t="shared" si="4"/><v>8</v></c></row>'+
'<row r="3"><c r="B3"><f t="shared" si="4"></f><v>9</v></c></row>';
var
Wb: TXLSXWorkbook;
Sh: TXLSXWorksheet;
begin
Wb:= TXLSXWorkbook.Create;
try
Wb.Open(FileName);
Sh:= Wb.Sheets[1];
// Master, verbatim
// B1 -> A1+$A$1+A$1+$A1+"A1"+SUM(A1:A2)
// Follower one row down: relative row moves, absolute row frozen,
// the mixed A$1 keeps its row, and the literal stays a literal
// B2 -> A2+$A$1+A$1+$A2+"A1"+SUM(A2:A3)
ShowMessage(Sh.Cells[2, 2].Formula);
finally
Wb.Free;
end;
end;El atributo ref es una puerta, no decoración. Un seguidor cuyas coordenadas caen fuera del rango aplicable del maestro no se expande, porque el fichero en ese caso está haciendo una afirmación que el grupo no respalda. Del mismo modo, cuando un desplazamiento empujaría una referencia por encima de la fila uno o a la izquierda de la columna A, HotXLS emite #REF! para ese token en lugar de recortarlo silenciosamente, que es lo que el propio Excel produciría para la misma edición. Esta traducción es prima cercana, pero no lo mismo, de la reescritura de referencias que ocurre al insertar o eliminar filas. Esa ruta tiene sus propias reglas sobre qué le pasa a un rango cuando una edición lo atraviesa, y se describe por separado en el artículo sobre el ajuste de referencias de fórmula al insertar y eliminar. La expansión compartida es más simple: es un desplazamiento puro desde un anclaje conocido, aplicado una vez, en el momento del análisis
¿Qué formas de referencia debe cubrir el desplazador?
Todas, o la expansión es un bug de pérdida de datos disfrazado. Un desplazador ingenuo que solo entiende A1 y A1:B2 corromperá o descartará las formas más exóticas, y los libros reales están llenos de ellas. El traductor de fórmula compartida de HotXLS reconoce toda la familia A1 antes de decidir qué mover. Las referencias a libros externos como [Book.xlsx]Sheet1!A1 y las referencias 3D como Sheet1:Sheet3!A1 conservan intacto su prefijo mientras la referencia de celda final se desplaza. Los nombres de hoja entrecomillados sobreviven, incluido el caso desagradable en el que la hoja se llama literalmente A1, así que 'A1'!A1 desplaza solo la parte tras el signo de exclamación. La columna completa A:A mueve su dimensión de columna y nada más; la fila completa 1:1 mueve su dimensión de fila y nada más; $A:$A no se mueve en absoluto. Las referencias de tabla estructurada como Table[A1] se dejan intactas, porque la parte entre corchetes es un nombre de columna, no una coordenada
// One master, expanded two columns to the right and zero rows down.
// Master D1: A1+A:A+$A:$A
// F1 : C1+C:C+$A:$A
//
// One master, expanded three rows down and zero columns across.
// Master A1: B1+$C$1+"A1"+A:A+1:1+'Data'!A1+LOG10(A1)+Table[A1]+'A1'!A1
// A3 : B3+$C$1+"A1"+A:A+3:3+'Data'!A3+LOG10(A3)+Table[A1]+'A1'!A3
//
// Note what did NOT move in the second line: the absolute $C$1, the
// string literal "A1", the whole column A:A under a pure row delta,
// the function name LOG10, and the structured reference Table[A1]Los nombres de función son la trampa silenciosa aquí. Un escáner de tokens que capture letras seguidas de dígitos reescribirá alegremente LOG10 como LOG11 una fila más abajo. HotXLS exige un límite de referencia antes de un token candidato y después de él, así que un identificador que continúa en una letra, dígito, guion bajo, punto, o un paréntesis de apertura no es una referencia de celda. Si estás trabajando en la otra familia de notación, el mismo problema de límites aparece de forma distinta, y el artículo sobre notación R1C1 cubre dónde divergen los dos modelos
¿Por qué un elemento f autocerrado se traga el siguiente valor?
Porque un elemento autocerrado no produce ningún evento de fin de elemento. Este es el bug más costoso de toda la característica, y no es específico de ningún parser XML en particular. En TXMLReader, <f t="shared" si="4"/> lanza exactamente un evento Element con IsEmptyElement a True, y nunca lanza el EndElement correspondiente. Un parser que cierra su estado de captura de fórmula solo en EndElement por tanto se queda dentro de la fórmula, y el siguiente texto que ve, que es el resultado en caché dentro de <v>, se añade al búfer de la fórmula. Peor aún, el estado sobrevive al límite de celda, así que la siguiente celda que posee un <f> real ve su texto de fórmula absorbido por la celda anterior. La corrección consiste en terminar el estado de fórmula en el propio evento Element siempre que IsEmptyElement sea True, y ejecutar allí toda la resolución del seguidor en lugar de esperar. Eso significa leer t, si, ref, aca y ca de los atributos, aplicar la expansión compartida, escribir los atributos de recálculo en la celda, y limpiar el estado compartido, todo dentro de la rama que maneja el elemento vacío. Nótese que el formato permite ambas grafías, <f t="shared" si="4"/> y <f t="shared" si="4"></f>, y la segunda sí lanza un EndElement. Un lector correcto tiene que manejar el par de forma idéntica, razón por la cual HotXLS cubre ambas grafías en el mismo fichero de regresión
Valores si dispersos, desordenados, y la cola pendiente
El atributo si es un entero sin signo proporcionado por el fichero, no una posición de array que tú controlas. Nada en el esquema exige que los índices compartidos sean densos, que empiecen en cero, o que aparezcan en orden ascendente, y nada impide que un fichero hostil o simplemente extraño use si="4294967290" en la primera celda. Dimensionar un array de búsqueda a partir del mayor si observado es por tanto un primitivo de agotamiento de memoria, no una optimización. HotXLS mantiene la ruta de apertura del libro sobre una tabla dispersa ordenada en su lugar: los grupos compartidos se registran bajo su clave entera en una TStringList ordenada, lo que convierte la búsqueda en una búsqueda binaria sobre cuantos grupos existan realmente, sin relación con el tamaño numérico de los índices. El orden es la segunda mitad del problema. Un maestro normalmente precede a sus seguidores en el orden del documento, pero eso es una convención más que una regla, así que cualquier seguidor que no pueda resolver su si en el momento en que se analiza va a una cola pendiente. Cuando la hoja termina, la cola se reproduce contra la tabla ya completa, y los maestros tardíos resuelven a sus huérfanos. Las celdas que nunca encuentran un maestro conservan una fórmula vacía, que es el resultado honesto para un fichero que referencia un grupo que nunca definió
Expandir fórmulas compartidas sin cargar el libro
Los lectores en streaming se enfrentan al mismo requisito bajo un presupuesto de memoria mucho más ajustado, y lo resuelven con una tabla local a la hoja. TXLSDirectReader y TXLSRowCursor ambos expanden los seguidores en fórmulas completas por celda mientras preservan su comportamiento de memoria acotada y de proyección, así que un pase de solo avance sobre una hoja de 300 MB sigue entregándote texto de fórmula real
var
Reader: TXLSDirectReader;
Cursor: TXLSRowCursor;
begin
// Projection: only rows 2..3, only column A. The master lives in row 1,
// outside the projection, and is still parsed so the followers resolve
Reader:= TXLSDirectReader.Create;
try
Reader.FirstRow:= 2;
Reader.LastRow:= 3;
Reader.IncludeColumn(1);
Reader.OnCell:= HandleCell; // Cell.Formula is fully expanded here
Reader.ReadFile(FileName);
finally
Reader.Free;
end;
// Forward-only row traversal, same expansion
Cursor:= TXLSRowCursor.Create;
try
Cursor.Open(FileName);
if Cursor.FindFirst then
repeat
if Cursor.CellCount > 0 then
WriteLn(Cursor.RowIndex, ': ', Cursor.Cells[0].Formula);
until not Cursor.FindNext;
finally
Cursor.Free;
end;
end;De ese diseño se derivan dos restricciones. Primero, la proyección nunca puede saltarse el maestro. Un filtro de fila establecido con FirstRow y LastRow, o un filtro de columna construido con IncludeColumn, puede evitar emitir la celda maestra a tu callback, pero el parser todavía tiene que registrar su si, coordenadas de anclaje, rango aplicable, y texto de fórmula, de lo contrario cada seguidor dentro de la proyección resuelve a nada. Solo el trabajo del lado del seguidor, el desplazamiento y la decodificación del valor, es seguro de omitir. Segundo, la tabla es por hoja y su ciclo de vida debe gestionarse explícitamente: TXLSRowCursor mantiene una instancia durante la duración de un pase de hoja y la limpia al reiniciar, al cambiar de hoja, al llegar al fin de fichero, ante una excepción, y al cerrar, así que un grupo definido en la hoja uno nunca puede filtrarse a la hoja dos. Como la ruta en streaming es un bucle caliente, usa un hash de enteros de direccionamiento abierto en lugar de la tabla de cadenas ordenada, lo que evita una conversión de entero a cadena por celda
Qué ocurre al guardar, y dónde están los límites
Una vez que un seguidor ha sido expandido es una fórmula ordinaria, y HotXLS lo vuelve a escribir como un elemento <f> independiente sin t="shared" ni si. El ciclo de ida y vuelta es estable y los resultados en caché de <v> sobreviven, pero la salida es mayor que la entrada para una hoja fuertemente compartida, y la agrupación que Excel creó no se reconstruye al guardar. Si la fidelidad a nivel de byte de los grupos compartidos te importa más que tener texto de fórmula real en cada celda, este es el intercambio que estás aceptando. El lado XLS es distinto, por cierto: el registro SHRFMLA de BIFF8 tiene su propia codificación y su propio escritor, con un interruptor de grupo compartido en el libro
Dos cosas relacionadas explícitamente no son fórmulas compartidas aunque comparten el elemento <f>. Las fórmulas de array CSE heredadas usan t="array" con un ref que cubre el rango anclado, y los arrays dinámicos usan la misma grafía t="array" pero se identifican mediante un atributo cm que enlaza a través de cellMetadata con un registro XLDAPR. Tratar una celda de derrame de array dinámico como un seguidor compartido o CSE es un auténtico bug de corrección, y la separación se cubre en el artículo sobre fórmulas de array dinámico y derrame. Lee los tres casos como tres parsers que resulta que comparten un nombre de etiqueta, y el código se mantiene honesto
La expansión de fórmula compartida, los lectores en streaming, y el traductor de referencias descritos aquí se incluyen como parte del componente Excel HotXLS para Delphi y C++Builder; la página del producto incluye la referencia completa de la API de fórmulas y lectura directa, incluidas las propiedades de proyección usadas más arriba