HotXLS ahora evalúa las referencias estructuradas de tabla, así que =SUM(Table1[Amount]) produce un número en lugar de omitirse. El resolutor gestiona Table[Column], Table[[Column]], rangos de columnas como Table[[Q1]:[Q4]], y los especificadores de elemento [#Data], [#All], [#Headers] y [#Totals], resolviendo cada uno contra el modelo de tabla del libro en el momento del análisis mientras el texto original de la fórmula se conserva íntegro
Una forma está ausente de manera deliberada, y es la que la gente encuentra primero. La forma abreviada de fila actual [@Column] no está soportada, por una razón estructural que merece la pena entender en lugar de rodearla a ciegas
¿Por qué una referencia estructurada no es solo un rango con un nombre amigable?
Porque un nombre definido congela una dirección y una referencia de tabla no lo hace. Escriba DataBlock como un nombre que apunta a Sheet1!$A$2:$D$100 y seguirá siendo ese rectángulo hasta que algo lo reescriba. Escriba Sales[Amount] y significará "la columna Amount de la tabla Sales", sea cual sea la extensión de esa tabla en el momento en que se evalúe. Añada veinte filas a la tabla y la suma las cubrirá; no hay ninguna referencia que ajustar porque nunca hubo una dirección en la fórmula para empezar
Esa cualidad simbólica es precisamente por lo que la referencia no se puede resolver por sustitución de cadenas. El resolutor tiene que encontrar la tabla por nombre en el libro, buscar la columna por el texto de su encabezado, decidir qué filas cubre el especificador de elemento solicitado y producir un rectángulo concreto. HotXLS hace esto durante la compilación de la fórmula a través del modelo de tabla, y por eso una fórmula escrita antes de que la tabla crezca sigue evaluándose contra la extensión actual de la tabla
La gramática que resuelve HotXLS
La gramática de especificación soportada cubre un único resultado rectangular y merece la pena enunciarla con precisión, porque la documentación de Excel presenta una superficie mucho mayor de la que implementan la mayoría de los motores. HotXLS acepta [Col] y la variante entre corchetes [[Col]], los especificadores de elemento simples [#Data], [#All], [#Headers] y [#Totals], la forma combinada [[#Data],[Col]], un rango dentro de un especificador de elemento como [[#Data],[Col1]:[Col2]], y un rango simple [Col1]:[Col2]
Lo que ese conjunto ofrece es toda forma de referencia que produce un bloque contiguo único: una columna, una serie de columnas adyacentes, una porción solo del cuerpo o con encabezado incluido de cualquiera de las dos. Las uniones no adyacentes y los resultados de varias áreas quedan fuera. Cuando una referencia no se puede resolver, la fórmula conserva el comportamiento previo de omitirse sin valor en lugar de sustituir una conjetura, así que una referencia irresoluble nunca se convierte en un número erróneo plausible
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);
// ... escribir la fila de encabezado y 24 filas de datos ...
Sheet.Cells[27, 4].Formula := 'SUM(SalesTable[Amount])';
Sheet.Cells[28, 4].Formula := 'SUM(SalesTable[[Q1]:[Q2]])';
Sheet.Cells[29, 4].Formula := 'COUNTA(SalesTable[[#Data],[Region]])';
Sheet.Cells[30, 4].Formula := 'ROWS(SalesTable[#All])';
Book.Recalculate;
Book.SaveAs('sales.xlsx');
finally
Cols.Free;
Book.Free;
end;
end;¿Por qué se excluye a propósito la forma de fila actual?
[@Column] y [#This Row] significan "la celda de esa columna en la fila donde vive esta fórmula". El valor depende por tanto de la posición de la celda que evalúa, no solo de la tabla. Es un tipo de referencia distinto: no un rectángulo que el compilador pueda resolver una vez, sino una resolución por celda que hay que repetir para cada fila que ocupe la fórmula
HotXLS devuelve False desde el resolutor de rangos de tabla para esas formas, lo que las encamina hacia la ruta de omisión sin valor. El texto de la fórmula se preserva y se escribe de vuelta sin cambios, así que un libro que usa [@Amount] se abre correctamente en Excel después de pasar por su aplicación; solo está ausente el valor calculado por HotXLS. Ante la elección entre un valor ausente y un valor calculado contra la fila equivocada, la ausencia es la que se puede detectar
La solución práctica es mecánica: en un libro que usted genera, escriba la referencia relativa equivalente en estilo A1, que es de todos modos lo que Excel almacena internamente para gran parte de la lógica con ámbito de tabla. En un libro que usted simplemente procesa, deje la fórmula tal cual y lea el valor en caché que Excel ya almacenó, que es lo que suele querer una canalización de carga e informe
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];
Table := Sheet.Tables.FindByName('SalesTable');
if Table <> nil then
begin
// Búsqueda estilo recordset sobre el cuerpo de la tabla, resultado de fila con base 1
Row := Table.FindFirst(Sheet, 'Region', 'EMEA');
while Row > 0 do
begin
Log(VarToStr(Sheet.Cells[Row, 4].Value));
Row := Table.FindNext(Sheet, 'Region', 'EMEA', Row);
end;
end;
finally
Book.Free;
end;
end;Qué ocurre cuando la tabla cambia de forma
Las referencias estructuradas se invalidan en lugar de redirigirse en silencio cuando lo que nombran desaparece. Elimine una columna y las fórmulas que se refieren a ella se invalidan tal como las invalida Excel; elimine o renombre la tabla y las referencias a ella se tratan del mismo modo. Este es el comportamiento correcto y refleja el ajuste ordinario de referencias, descrito en el ajuste de referencias de fórmula al insertar y eliminar, donde el trabajo del motor es mantener honestas las fórmulas y no mantenerlas con apariencia de válidas
El crecimiento de filas es el caso contrario y no necesita ningún ajuste. Como la referencia nombra la tabla en lugar de un rectángulo, añadir filas dentro del rango de la tabla amplía lo que cubre [#Data] sin tocar una sola fórmula. Esa es la propiedad que hace que valga la pena usar tablas en una plantilla de informe: la fila de totales sigue sumando todo lo que produjo la importación, sean las que sean las filas resultantes
Disciplina de ida y vuelta
HotXLS conserva el texto de fórmula original. Un libro cargado con SUM(SalesTable[Amount]) se guarda con SUM(SalesTable[Amount]), no con la dirección resuelta SUM(D2:D25). Esto importa más de lo que pueda parecer: un usuario que abre su salida en Excel espera ver la fórmula que escribió, y una dirección resuelta convertiría en silencio un modelo autosuficiente en uno frágil que dejaría de cubrir las filas nuevas
Dos capacidades relacionadas completan el panorama. Las propias definiciones de tabla, incluidas las tablas sin encabezado y los comentarios por tabla, hacen el ciclo completo a través del modelo de tabla descrito en la validación de datos, AutoFilter y las tablas de Excel. Y cuando muchas celdas comparten un mismo patrón, XLSX las almacena una sola vez como fórmula compartida, que se expande y reemite tal como se cubre en la expansión si de fórmula compartida. Las referencias estructuradas dentro de fórmulas compartidas pasan por ambas rutas, así que ambas deben comportarse bien, y así lo hacen
HotXLS lee y escribe XLS, XLSX y ODS desde Delphi y C++Builder sin instalación de Excel y sin automatización de Office, evaluando las fórmulas en su propio motor. El modelo de tabla, el motor de fórmulas y la API de recálculo están documentados en la página de HotXLS para Delphi