Una tabla de Excel cuya columna Amount lleva =[@Qty]*[@Price] no guarda [@Qty] en ningún sitio del archivo guardado. Descomprime el libro y el elemento <f> del XML de hoja muestra lo que Excel escribe realmente: la grafía larga calificada por tabla Lines[[#This Row],[Qty]]*Lines[[#This Row],[Price]], porque la gramática de fórmulas de ECMA-376 Part 1, §18.17 tiene la palabra clave #This Row y ninguna @. Una celda de resumen más abajo lleva SUM(Lines[Amount]). Ninguna de las dos cadenas contiene una sola dirección de celda, así que un motor que solo parsea referencias A1 no tiene nada que calcular hasta saber dónde se sienta la tabla, qué fila lleva las cabeceras y en qué fila vive la propia fórmula
Esa última pieza de información es la incómoda. Una referencia de columna se puede convertir en un rectángulo una vez por tabla. Una referencia de fila actual no puede; significa una celda distinta para cada fila a la que se copia
¿Por qué una referencia estructurada no es solo un rango con un nombre amigable?
Un nombre definido congela una dirección y una referencia de tabla no. Apunta el nombre DataBlock a Sheet1!$A$2:$D$100 y se queda siendo ese rectángulo hasta que algo lo reescriba. Sales[Amount] significa «la columna Amount de la tabla Sales», sea cual sea la extensión de la tabla cuando se evalúe la fórmula. Añade veinte filas a la tabla y la suma las cubre; no hay referencia que ajustar porque la fórmula nunca llevó una dirección
HotXLS resuelve estas referencias cada vez que compila una fórmula para evaluarla. TXLSXWorkbook busca la tabla por nombre en el modelo de tablas del libro (TXLSXTable.Name o DisplayName, comparados sin distinguir mayúsculas), empareja la columna contra el texto de cabecera guardado en TXLSXTable.Columns, aplica el especificador de elemento para elegir la banda de filas y le entrega al compilador un rango absoluto como 'Sales'!$D$2:$D$25. El texto reescrito existe solo para esa compilación. La celda conserva la fórmula exactamente como se escribió, y como la búsqueda corre contra TXLSXTable.Range tal como esté en el momento del recálculo, una fórmula escrita antes de que la tabla creciera aún evalúa contra la tabla mayor
La gramática del rectángulo: columnas, tramos y especificadores de elemento
Toda forma de tabla completa que HotXLS evalúa produce un solo bloque contiguo. Las grafías aceptadas son [Col] y su gemela entre corchetes [[Col]], los especificadores de elemento desnudos [#Data], [#All], [#Headers] y [#Totals], la forma combinada [[#Data],[Col]], un tramo dentro de un especificador de elemento como [[#Data],[Col1]:[Col2]], y un tramo plano [[Col1]:[Col2]]. Un tramo puede escribirse de derecha a izquierda; [[Q2]:[Q1]] resuelve a las mismas dos columnas que [[Q1]:[Q2]]
La banda de filas que cada especificador selecciona sigue dos flags de la tabla. TXLSXTable.HasHeaderRow decide si la primera fila de Range es una cabecera, y TXLSXTable.TotalsRowShown decide si la última fila es una fila de totales. Una referencia de columna plana y [#Data] cubren las filas entre esas dos. [#All] toma el Range completo, cabecera y totales incluidos. Pide [#Headers] en una tabla sin fila de cabecera, [#Totals] en una tabla sin fila de totales, o la columna de una tabla cuyo cuerpo no tiene filas, y la referencia se convierte en #REF!. Eso coincide con lo que Excel muestra para la misma petición, y evita que una referencia imposible se convierta en silencio en un rango vacío que suma cero
Los nombres de columna que contienen corchetes, una almohadilla, una arroba o un apóstrofo siguen la regla de escape de Excel: cada uno de esos caracteres va precedido de un apóstrofo dentro de la referencia, así que una cabecera llamada Unit [USD] se escribe Sales[Unit '[USD']]. Las formas de columna única y de fila actual decodifican esos escapes antes de emparejar. Los tramos y las formas combinadas [#Data],[Col] resuelven contra tablas de la propia hoja de la fórmula, así que una hoja de resumen que necesite un tramo sobre la tabla de otra hoja sale mejor servida por una referencia de columna plana por columna
El ejemplo de abajo construye una tabla de ventas de 24 filas y coloca cinco fórmulas de resumen a su lado en F2 a F6, en la misma hoja que la tabla. La fila 1 es la cabecera, las filas 2 a 25 son el cuerpo, y no hay fila de totales, así que SalesTable[#All] abarca 25 filas mientras SalesTable[Amount] abarca 24
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
Cols: TStringList;
begin
Book := TXLSXWorkbook.Create;
Cols := TStringList.Create;
try
Sheet := Book.Sheets.Add('Sales');
Cols.Add('Region');
Cols.Add('Q1');
Cols.Add('Q2');
Cols.Add('Amount');
Sheet.Tables.Add('SalesTable', 'A1:D25', Cols);
// ... escribe la fila de cabecera y 24 filas de datos ...
Sheet.Cells[2, 6].Formula := 'SUM(SalesTable[Amount])';
Sheet.Cells[3, 6].Formula := 'SUM(SalesTable[[Q1]:[Q2]])';
Sheet.Cells[4, 6].Formula := 'COUNTA(SalesTable[[#Data],[Region]])';
Sheet.Cells[5, 6].Formula := 'ROWS(SalesTable[#All])';
Sheet.Cells[6, 6].Formula := 'COUNTA(SalesTable[#Headers])';
Book.Recalculate;
Book.SaveAs('sales.xlsx');
finally
Cols.Free;
Book.Free;
end;
end;
Valores esperados para ese layout: F5 es 25 y F6 es 4, un recuento por celda de cabecera. F2 a F4 dependen de los datos que escribas
¿Cómo sabe [@Column] qué fila leer?
Una referencia de fila actual toma su fila de la celda que se está evaluando. Cuando Recalculate llega a una fórmula que contiene Lines[@Qty] en la fila 7, HotXLS la resuelve a la columna Qty de la fila 7; el mismo texto de fórmula en la fila 8 lee la fila 8. La grafía larga que Excel escribe a disco, Lines[[#This Row],[Qty]], se trata como la misma referencia, y también Lines[@[Unit Price]] para una cabecera que contenga un espacio
La forma sin calificar [@Qty], sin nombre de tabla delante, necesita un dato más: a qué tabla pertenece. HotXLS lo responde por posición. Si la celda en evaluación se sienta dentro de las filas de cuerpo y el tramo de columnas de una tabla de la misma hoja, esa tabla aporta la columna, y dentro de tal tabla [@[Q1]:[Q2]] se convierte en un rango de una fila a través de esas columnas. En cualquier otro lugar la referencia se deja sin resolver. Un nombre definido cuya fórmula usa una referencia de fila actual toma su fila de la celda que refiere al nombre, que es el mismo comportamiento que obtendría una fórmula en línea
Mantén las fórmulas de fila actual en las filas de cuerpo de la propia tabla, y califícalas con el nombre de la tabla cuando el código las genere. Las referencias calificadas sobreviven a ser movidas, se leen igual que Excel las guarda, y una fórmula en las filas de la propia tabla es el único caso en que Excel y cualquier otro consumidor del archivo coinciden en qué significa @
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
Cols: TStringList;
Row: Integer;
begin
Book := TXLSXWorkbook.Create;
Cols := TStringList.Create;
try
Sheet := Book.Sheets.Add('Orders');
Cols.Add('Item');
Cols.Add('Qty');
Cols.Add('Price');
Cols.Add('Amount');
Sheet.Tables.Add('Lines', 'A1:D13', Cols);
// ... escribe la cabecera y los artículos, cantidades y precios en las filas 2..13 ...
// Un texto de fórmula, doce resultados distintos: cada celda lee su propia fila
for Row := 2 to 13 do
Sheet.Cells[Row, 4].Formula := 'Lines[@Qty]*Lines[@Price]';
// Contraste bajo la tabla: total de columna contra un producto recalculado
Sheet.Cells[15, 4].Formula :=
'SUM(Lines[Amount])-SUMPRODUCT(Lines[Qty],Lines[Price])';
Book.Recalculate;
// Esperado: D15 evalúa a 0 cuando cada celda Amount coincide con Qty * Price
Book.SaveAs('orders.xlsx');
finally
Cols.Free;
Book.Free;
end;
end;
El contraste en D15 es barato y merece la pena dejarlo en un informe generado mientras se está construyendo. Una fórmula por filas que tomara la fila equivocada haría que los dos totales discreparan, y la diferencia asoma como un número distinto de cero en una celda que cualquiera puede leer
Qué ocurre cuando la tabla cambia de forma
Crecer es el caso fácil, siempre que la definición de la tabla crezca de verdad. Escribir un valor en la fila 26 bajo una tabla cuyo Range es A1:D25 no convierte la fila 26 en parte de la tabla. La autoexpansión es una función de edición de Excel que se dispara cuando alguien escribe junto a la tabla, y escribir celdas a través de HotXLS no la enciende. Fija TXLSXTable.Range a la nueva extensión tras una importación, o inserta las filas dentro de la tabla con TXLSXWorksheet.InsertRows, que desplaza Tables[i].Range junto con todas las demás referencias de la hoja. De cualquiera de las dos maneras [#Data] se ensancha y no cambia ni una fórmula
Encoger es donde las referencias pueden romperse, y HotXLS sigue la regla de Excel. DeleteCols sobre una columna de tabla reescribe como #REF! toda referencia estructurada que nombre esa columna, en fórmulas de celda, formatos condicionales, reglas de validación de datos, series de gráficos y nombres definidos. Un DeleteRows que elimina la fila de cabecera borra la tabla e invalida sus referencias de la misma manera, y uno que elimina la fila de totales limpia TotalsRowShown, tras lo cual [#Totals] ya no tiene fila a la que apuntar. Las referencias A1 ordinarias pasan por el mismo ajuste, descrito en el ajuste de referencias de fórmulas al insertar y eliminar
TXLSXTables.DeleteByName y el renombrado vía TXLSXTable.Name trabajan solo sobre el modelo de tablas. Las fórmulas que mencionan el nombre viejo conservan su texto, así que renombra esas fórmulas en la misma pasada cuando tu código renombre una tabla
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
Table: TXLSXTable;
Row: Integer;
begin
Book := TXLSXWorkbook.Create;
try
if Book.Open('sales.xlsx') <> 1 then Exit;
Sheet := Book.Sheets[1]; // las hojas del libro son base 1
Table := Sheet.Tables.FindByName('SalesTable');
if Table = nil then Exit;
// ... escribe 20 filas importadas en A26:D45 ...
Table.Range := 'A1:D45'; // el cuerpo ahora va de la fila 2 a la 45
Book.Recalculate; // SUM(SalesTable[Amount]) en F2 ahora cubre 44 filas
// Búsqueda estilo recordset sobre el cuerpo de la tabla, fila resultado base 1, 0 = ninguna
Row := Table.FindFirst(Sheet, 'Region', 'EMEA');
while Row > 0 do
begin
Log(VarToStr(Sheet.Cells[Row, 4].Value)); // Log es una rutina tuya
Row := Table.FindNext(Sheet, 'Region', 'EMEA', Row);
end;
Book.SaveAs('sales.xlsx');
finally
Book.Free;
end;
end;
FindFirst y FindNext comparan cadenas sin distinguir mayúsculas y números por valor, y solo rastrean las filas del cuerpo, así que el texto de cabecera Region nunca se devuelve como coincidencia
Disciplina de ida y vuelta
Un libro cargado con SUM(SalesTable[Amount]) se guarda con SUM(SalesTable[Amount]), y una celda que lleva Lines[[#This Row],[Qty]] sale con ese texto intacto. La dirección resuelta jamás sustituye a lo que el usuario escribió. Si HotXLS guardara SUM($D$2:$D$25) en su lugar, el archivo se abriría en Excel pareciendo correcto y dejaría de cubrir filas nuevas la primera vez que alguien extendiera la tabla, que es el tipo de rotura más lenta de rastrear hasta su causa
Las propias definiciones de tabla, incluidas las tablas sin cabecera y los comentarios por tabla, viajan de ida y vuelta por el modelo de tablas cubierto en validación de datos, AutoFilter y tablas de Excel. Cuando muchas celdas comparten un patrón de fórmula, XLSX puede guardarlo una vez como fórmula compartida, y la expansión de esas celdas está cubierta en la expansión de fórmulas compartidas si. Una columna calculada guardada por Excel suele llegar así, así que una referencia estructurada en el cuerpo de una tabla pasa por ambos caminos antes de evaluarse: la fórmula compartida se expande primero a texto por celda, y luego cada celda resuelve su propia fila
El modelo de tablas, el motor Recalculate y las llamadas de inserción y eliminación de hojas usadas aquí forman parte de HotXLS, que lee y escribe XLS, XLSX y ODS desde Delphi y C++Builder sin instalación de Excel. La referencia completa de la API vive en la página del componente de hojas de cálculo HotXLS para Delphi