Una fórmula compartida seguidora en XLSX no lleva texto de fórmula. Su elemento <f t="shared" si="N"/> apunta a una celda maestra en algún otro lugar de la hoja, y el lector debe reconstruir el texto desplazando la fórmula maestra por la diferencia de fila y columna. HotXLS Component para Delphi y C++Builder hace esa expansión en el momento de apertura, así que cada seguidora reporta una fórmula completa
Si alguna vez cargaste un XLSX del mundo real en una librería de terceros y encontraste que una columna de mil fórmulas tiene texto en exactamente una celda y cadenas vacías en las otras 999, te encontraste con esta característica desde el lado equivocado. Nada está corrupto. El archivo está haciendo lo que ECMA-376 le permite hacer, y el lector simplemente se detuvo en el punto donde el XML se detuvo
Por qué la celda de fórmula compartida está vacía
Porque el formato deliberadamente almacena 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 de tipo ST_CellFormulaType, y el valor shared significa que esta celda participa en un grupo identificado por el atributo si. Exactamente una celda en el grupo, la maestra, también lleva un atributo ref que da el rango al que aplica el grupo, y solo esa celda lleva el texto de la fórmula como contenido del elemento. Cada otra celda en el grupo es una seguidora. 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 elementos marcador de posición diminutos. El ahorro es real y el costo cae por completo sobre el lector: sin expansión, la seguidora no tiene ningún significado por sí sola
El desplazamiento es una traducción, no una copia de texto
HotXLS resuelve una seguidora localizando a la maestra registrada bajo el mismo si, calculando el delta de fila y columna desde el ancla de la maestra hasta la celda actual, y traduciendo cada referencia en la fórmula maestra por 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 omiten por completo, así que una fórmula que da la casualidad de contener el texto "A1" mantiene ese texto sin cambios en cada seguidora
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 compuerta, no decoración. Una seguidora cuyas coordenadas caen fuera del rango aplicable de la maestra no se expande, porque el archivo estaría entonces 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 en silencio, 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 cuando insertas o eliminas filas. Esa ruta tiene sus propias reglas sobre qué hace 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 durante la inserción y eliminación. La expansión compartida es más simple: es un desplazamiento puro desde un ancla conocida, aplicado una sola vez, en el momento del análisis
Qué formas de referencia debe cubrir el desplazador
Todas ellas, o la expansión es un error 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órmulas compartidas 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 mantienen su prefijo intacto mientras la referencia de celda final se desplaza. Los nombres de hoja entre comillas sobreviven, incluido el caso complicado donde la hoja se llama literalmente A1, así que 'A1'!A1 desplaza solo la parte después del 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 estructuradas 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 captura letras seguidas de dígitos reescribirá alegremente LOG10 como LOG11 una fila hacia 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 hacia una letra, dígito, guion bajo, punto, o un paréntesis de apertura no es una referencia de celda. Si trabajas en la otra familia de notación, el mismo problema de límites aparece de forma distinta, y el artículo sobre la 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 error más costoso de toda la característica, y no es específico de ningún analizador XML en particular. En TXMLReader, <f t="shared" si="4"/> dispara exactamente un evento Element con IsEmptyElement en True, y nunca dispara el EndElement correspondiente. Un analizador que cierra su estado de captura de fórmula solo en EndElement, por lo tanto, se queda dentro de la fórmula, y el siguiente texto que ve, que es el resultado en caché dentro de <v>, se anexa al buffer de fórmula. Peor aún, el estado sobrevive al límite de celda, así que la siguiente celda que posee un <f> real tiene su texto de fórmula absorbido por la celda anterior. La corrección es terminar el estado de fórmula en el propio evento Element siempre que IsEmptyElement sea True, y ejecutar ahí toda la resolución de la seguidora 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 sobre la celda, y limpiar el estado compartido, todo dentro de la rama que maneja el elemento vacío. Nota que el formato permite ambas grafías, <f t="shared" si="4"/> y <f t="shared" si="4"></f>, y la segunda sí dispara un EndElement. Un lector correcto tiene que manejar el par de forma idéntica, que es por qué HotXLS cubre ambas grafías en el mismo archivo de regresión
Valores si dispersos, sin orden, y la cola pendiente
El atributo si es un entero sin signo suministrado por el archivo, no una posición de arreglo que controlas. Nada en el esquema exige que los índices compartidos sean densos, que comiencen en cero, o que aparezcan en orden ascendente, y nada impide que un archivo hostil o simplemente extraño use si="4294967290" en la primera celda. Dimensionar un arreglo de búsqueda a partir del mayor si observado es, por lo 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 un TStringList ordenado, lo que convierte la búsqueda en una búsqueda binaria sobre la cantidad de grupos que realmente existan, sin relación con el tamaño numérico de los índices. El orden es la segunda mitad del problema. Una maestra normalmente precede a sus seguidoras en el orden del documento, pero eso es una convención en lugar de una regla, así que cualquier seguidora 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 las maestras tardías resuelven a sus huérfanas. Las celdas que nunca encuentran una maestra conservan una fórmula vacía, que es el resultado honesto para un archivo que referencia un grupo que nunca definió
Expandir fórmulas compartidas sin cargar el libro
Los lectores de streaming enfrentan el mismo requisito bajo un presupuesto de memoria mucho más ajustado, y lo resuelven con una tabla local a la hoja de trabajo. TXLSDirectReader y TXLSRowCursor ambos expanden las seguidoras en fórmulas completas por celda mientras preservan su comportamiento de memoria acotada y proyección, así que un recorrido de una sola dirección sobre una hoja de 300 MB igual te entrega 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 desprenden dos restricciones. Primero, la proyección nunca puede saltarse la maestra. Un filtro de fila configurado con FirstRow y LastRow, o un filtro de columna construido con IncludeColumn, puede omitir la emisión de la celda maestra hacia tu callback, pero el analizador igual tiene que registrar su si, las coordenadas de ancla, el rango aplicable, y el texto de la fórmula, de lo contrario cada seguidora dentro de la proyección resuelve a nada. Solo el trabajo del lado de la seguidora, el desplazamiento y la decodificación del valor, es seguro de omitir. Segundo, la tabla es por hoja de trabajo y su ciclo de vida tiene que gestionarse explícitamente: TXLSRowCursor mantiene una instancia durante la duración de un recorrido de hoja y la limpia al reiniciar, al cambiar de hoja, al llegar al fin de archivo, ante una excepción, y al cerrar, así que un grupo definido en la hoja uno nunca puede filtrarse hacia la hoja dos. Como la ruta de 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é sucede al guardar, y dónde están los límites
Una vez que una seguidora ha sido expandida es una fórmula ordinaria, y HotXLS la escribe de vuelta como un elemento <f> independiente sin t="shared" y sin si. El ciclo de ida y vuelta es estable y los resultados <v> en caché sobreviven, pero la salida es más grande 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 trueque 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 arreglo CSE heredadas usan t="array" con un ref que cubre el rango anclado, y los arreglos dinámicos usan la misma grafía t="array" pero se identifican mediante un atributo cm que encadena a través de cellMetadata hasta un registro XLDAPR. Tratar una celda de derrame (spill) de arreglo dinámico como una seguidora compartida o CSE es un error de corrección genuino, y la separación se cubre en el artículo sobre fórmulas de arreglo dinámico y derrame. Lee los tres casos como tres analizadores que dan la casualidad de compartir un nombre de etiqueta, y el código se mantiene honesto
La expansión de fórmulas compartidas, los lectores de streaming, y el traductor de referencias descritos aquí se incluyen como parte del componente Excel HotXLS para Delphi y C++Builder; la página de producto incluye la referencia completa de la API de fórmulas y lectura directa, incluidas las propiedades de proyección usadas arriba