Una tabla Excel cuya columna Amount sostiene =[@Qty]*[@Price] no guarda [@Qty] en ninguna parte del archivo guardado. Descomprima el libro y el elemento <f> en el XML de la hoja muestra lo que Excel realmente escribe: la grafía larga calificada por tabla Lines[[#This Row],[Qty]]*Lines[[#This Row],[Price]], porque la gramática de fórmulas en ECMA-376 Part 1, §18.17 tiene la palabra clave #This Row y ningún @. Una celda de resumen más abajo sostiene 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 que sepa dónde se sienta la tabla, qué fila carga los encabezados y en qué fila vive la propia fórmula
Ese último dato es el incómodo. Una referencia de columna puede convertirse en un rectángulo una vez por tabla. Una referencia de fila actual no; significa una celda distinta para cada fila a la que se copia
¿Por qué una referencia estructurada no es solo un rango con nombre amable?
Un nombre definido congela una dirección y una referencia de tabla no. Apunte 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 la fórmula se evalúa. Agregue veinte filas a la tabla y la suma las cubre; no hay ninguna referencia que ajustar porque la fórmula jamás sostuvo una dirección para empezar
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, comparadas sin sensibilidad a mayúsculas), empareja la columna contra el texto de encabezado almacenado 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á al momento del recálculo, una fórmula escrita antes de que la tabla creciera todavía evalúa contra la tabla más grande
La gramática del rectángulo: columnas, intervalos y especificadores de elemento
Toda forma de tabla completa que HotXLS evalúa produce un 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 intervalo dentro de un especificador de elemento como [[#Data],[Col1]:[Col2]], y un intervalo plano [[Col1]:[Col2]]. Un intervalo puede escribirse de derecha a izquierda; [[Q2]:[Q1]] se resuelve a las mismas dos columnas que [[Q1]:[Q2]]
La banda de filas que cada especificador selecciona sigue dos banderas de la tabla. TXLSXTable.HasHeaderRow decide si la primera fila de Range es un encabezado, 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, encabezado y totales incluidos. Pida [#Headers] en una tabla sin fila de encabezado, [#Totals] en una tabla sin fila de totales, o una 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, un hash, una arroba o un apóstrofo siguen la regla de escape de Excel: cada uno de esos caracteres se antepone con un apóstrofo dentro de la referencia, así que un encabezado llamado Unit [USD] se escribe Sales[Unit '[USD']]. Las formas de columna única y de fila actual decodifican esos escapes antes de emparejar. Los intervalos y las formas combinadas [#Data],[Col] se resuelven contra tablas en la hoja propia de la fórmula, así que una hoja de resumen que necesita un intervalo sobre la tabla de otra hoja queda 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 fila de encabezado, las filas 2 a 25 son el cuerpo, y no hay fila de totales, así que SalesTable[#All] se extiende por 25 filas mientras SalesTable[Amount] se extiende por 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);
// ... escriba la fila de encabezados 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 esa distribución: F5 es 25 y F6 es 4, un conteo por celda de encabezado. F2 a F4 dependen de los datos que usted escriba
¿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 un encabezado que contiene 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 del cuerpo y del intervalo de columnas de una tabla en la misma hoja, esa tabla suministra la columna, y dentro de una tabla así [@[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 se refiere al nombre, que es el mismo comportamiento que tendría una fórmula en línea
Mantenga las fórmulas de fila actual en las filas del propio cuerpo de la tabla, y califíquelas con el nombre de la tabla cuando el código las genere. Las referencias calificadas sobreviven a ser movidas, se leen de la misma manera que Excel las guarda, y una fórmula en las filas propias de la tabla es el único caso donde Excel y cualquier otro consumidor del archivo están de acuerdo sobre 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);
// ... escriba los encabezados y 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]';
// Contraverificación bajo la tabla: total de la 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;
La verificación en D15 es barata y vale dejarla en un reporte generado mientras se está construyendo. Una fórmula por fila que levantara la fila equivocada haría que los dos totales discreparan, y la diferencia aparece como un número distinto de cero en una celda que cualquiera puede leer
Qué pasa cuando la tabla cambia de forma
Crecer es el caso fácil, siempre que la definición de la tabla realmente crezca. Escribir un valor en la fila 26 debajo de una tabla cuyo Range es A1:D25 no convierte la fila 26 en parte de la tabla. La auto-expansión es una característica de edición de Excel disparada por alguien escribiendo junto a la tabla, y escribir celdas a través de HotXLS no la dispara. Fije TXLSXTable.Range a la nueva extensión tras una importación, o inserte las filas dentro de la tabla con TXLSXWorksheet.InsertRows, que desplaza Tables[i].Range junto con cada otra referencia de la hoja. De cualquier manera [#Data] se ensancha y ni una fórmula cambia
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 encabezado elimina 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 renombre esas fórmulas en la misma pasada cuando su 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 1-based
Table := Sheet.Tables.FindByName('SalesTable');
if Table = nil then Exit;
// ... escriba 20 filas importadas en A26:D45 ...
Table.Range := 'A1:D45'; // el cuerpo ahora corre de la fila 2 a la fila 45
Book.Recalculate; // SUM(SalesTable[Amount]) en F2 ahora cubre 44 filas
// Búsqueda estilo recordset sobre el cuerpo de la tabla, resultado de fila 1-based, 0 = ninguna
Row := Table.FindFirst(Sheet, 'Region', 'EMEA');
while Row > 0 do
begin
Log(VarToStr(Sheet.Cells[Row, 4].Value)); // Log es su propia rutina
Row := Table.FindNext(Sheet, 'Region', 'EMEA', Row);
end;
Book.SaveAs('sales.xlsx');
finally
Book.Free;
end;
end;
FindFirst y FindNext comparan cadenas sin sensibilidad a mayúsculas y números por valor, y escanean solo las filas del cuerpo, así que el texto de encabezado Region jamás se devuelve como coincidencia
Disciplina de round trip
Un libro cargado con SUM(SalesTable[Amount]) se guarda con SUM(SalesTable[Amount]), y una celda que sostiene Lines[[#This Row],[Qty]] sale de vuelta con ese texto intacto. La dirección resuelta jamás reemplaza lo que el usuario escribió. Si HotXLS guardara SUM($D$2:$D$25) en cambio, el archivo se abriría en Excel viéndose bien y luego dejaría de cubrir filas nuevas la primera vez que alguien extendiera la tabla, que es el tipo de rotura más lento de rastrear hasta su causa
Las definiciones de tablas en persona, incluidas las tablas sin encabezado y los comentarios por tabla, hacen round trip por el modelo de tablas cubierto en validación de datos, AutoFilter y tablas Excel. Cuando muchas celdas comparten un patrón de fórmula, XLSX puede almacenarla 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 a menudo llega así, así que una referencia estructurada en el cuerpo de una tabla pasa por ambas rutas 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 de Recalculate y las llamadas de inserción y eliminación de hoja usadas aquí son 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