Artículo técnico

Referencias estructuradas de tabla de Excel en HotXLS

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

Diagrama de HotXLS resolviendo la referencia estructurada SalesTable[Amount] en Delphi encontrando la tabla por nombre, emparejando el encabezado de columna Amount y aplicando el especificador #Data para obtener un rango concreto
HotXLS compila las referencias Table[Column] contra el modelo de tabla en tiempo de análisis y las convierte en el rango concreto actual, de modo que la fórmula sigue siguiendo a la tabla a medida que crece

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

Mapa de la gramática de referencias estructuradas que HotXLS acepta en Delphi, mostrando qué regiones de la tabla cubren las formas #Data, #Headers, #Totals y #All y cómo las grafías entre corchetes mapean a los mismos rectángulos
Toda forma de tabla completa cubre una única banda contigua de la tabla, y las variantes entre corchetes resuelven exactamente igual que sus grafías planas

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

Diagrama de flujo de HotXLS resolviendo la forma de fila actual [@Amount] en Delphi contra la fila de la celda en evaluación mientras el texto de la fórmula viaja intacto de ida y vuelta, de modo que Excel lee la misma referencia tras guardar
Las referencias de fila actual se resuelven por celda contra la fila en que se sienta la fórmula, y el archivo guardado sigue llevando el texto original de la referencia para que Excel lo lea

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