HotXLS ahora evalúa referencias de tabla estructurada, así que =SUM(Table1[Amount]) produce un número en lugar de omitirse. El resolutor maneja Table[Column], Table[[Column]], rangos de columna como Table[[Q1]:[Q4]], y los especificadores de elemento [#Data], [#All], [#Headers] y [#Totals], resolviendo cada uno contra el modelo de tablas del libro de trabajo en el momento del análisis, mientras el texto original de la fórmula se preserva textualmente en el round-trip
Una forma está deliberadamente ausente, y es la que la gente encuentra primero. La abreviatura de fila actual [@Column] no está soportada, por una razón estructural que vale la pena entender en lugar de simplemente evitar 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. Escriba DataBlock como un nombre que apunta a Sheet1!$A$2:$D$100 y permanecerá 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 la fórmula. Agregue 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 exactamente la razón por la que la referencia no puede resolverse por sustitución de cadenas. El resolutor tiene que encontrar la tabla por nombre en el libro de trabajo, 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 mediante el modelo de tablas, razón por la cual una fórmula escrita antes de que la tabla crezca todavía se evalúa contra la extensión actual de la tabla
La gramática que resuelve HotXLS
La gramática de especificadores soportada cubre un único resultado rectangular y vale la pena enunciarla con precisión, porque la documentación de Excel presenta una superficie mucho más amplia de la que la mayoría de los motores implementa. HotXLS acepta [Col] y la variante entre corchetes [[Col]], los especificadores de elemento sueltos [#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 le da es cada forma de referencia que produce un solo bloque contiguo: una columna, una serie de columnas adyacentes, un fragmento del cuerpo o que incluye el encabezado de cualquiera de las dos. Las uniones no adyacentes y los resultados multiárea quedan fuera. Cuando una referencia no puede resolverse, la fórmula conserva el comportamiento previo de omitir-sin-valor en lugar de sustituir una suposición, así que una referencia irresoluble nunca se convierte en un número incorrecto 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 las 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é la forma de fila actual está excluida a propósito?
[@Column] y [#This Row] significan "la celda de esa columna en la fila donde vive esta fórmula". El valor, por lo tanto, depende de la posición de la celda que evalúa, no solo de la tabla. Ese es un tipo distinto de referencia: no un rectángulo que el compilador pueda resolver una vez, sino una resolución por celda que hay que rehacer para cada fila que la fórmula ocupa
HotXLS devuelve False desde el resolutor de rango de tabla para esas formas, lo cual las dirige hacia la ruta de omitir-sin-valor. El texto de la fórmula se preserva y se escribe de vuelta sin cambios, así que un libro de trabajo que usa [@Amount] abre correctamente en Excel después de un round-trip por su aplicación; solo el valor calculado por HotXLS está ausente. Dado a elegir entre un valor ausente y un valor calculado contra la fila equivocada, la ausencia es la que usted puede detectar
El workaround práctico es mecánico: en un libro de trabajo que usted genera, escriba la referencia relativa equivalente en estilo A1, que es lo que Excel almacena internamente de todos modos para gran parte de la lógica con alcance de tabla. En un libro de trabajo 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 un pipeline de carga y reporte
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 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é sucede 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 esa columna se invalidan de la misma forma en que Excel las invalida; elimine o renombre la tabla y las referencias a ella se manejan igual. Este es el comportamiento correcto y refleja el ajuste ordinario de referencias, descrito en ajuste de referencias de fórmula al insertar y eliminar, donde el trabajo del motor es mantener honestas las fórmulas, no mantenerlas con apariencia de válidas
El crecimiento de filas es el caso opuesto y no necesita ningún ajuste. Como la referencia nombra a la tabla en lugar de a un rectángulo, agregar filas dentro del rango de la tabla amplía lo que [#Data] cubre sin tocar una sola fórmula. Esa es la propiedad que hace que valga la pena usar tablas en una plantilla de reporte: la fila de totales sigue sumando todo lo que produjo la importación, sin importar cuántas filas resultaron ser
Disciplina de round-trip
HotXLS conserva el texto original de la fórmula. Un libro de trabajo 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 parece: un usuario que abre su salida en Excel espera ver la fórmula que escribió, y una dirección resuelta convertiría silenciosamente un modelo autosuficiente en uno frágil que deja de cubrir filas nuevas
Dos capacidades relacionadas completan el panorama. Las definiciones de tabla mismas, incluyendo tablas sin encabezado y comentarios por tabla, se preservan a través del modelo de tablas descrito en validación de datos, AutoFilter y tablas de Excel. Y cuando muchas celdas comparten un patrón, XLSX las almacena una sola vez como una fórmula compartida, que se expande y reemite como se cubre en expansión si de fórmula compartida. Las referencias estructuradas dentro de fórmulas compartidas pasan por ambas rutas, así que ambas necesitan comportarse bien, y 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 fórmulas en su propio motor. El modelo de tablas, el motor de fórmulas y la API de recálculo están documentados en la página del componente de hojas de cálculo HotXLS Delphi