HotXLS wertet jetzt strukturierte Tabellenverweise aus, sodass =SUM(Table1[Amount]) eine Zahl liefert, statt übersprungen zu werden. Der Resolver behandelt Table[Column], Table[[Column]], Spaltenspannen wie Table[[Q1]:[Q4]] sowie die Item-Spezifizierer [#Data], [#All], [#Headers] und [#Totals], jeweils zur Parse-Zeit gegen das Tabellenmodell des Arbeitsbuchs aufgelöst, während der ursprüngliche Formeltext wortgetreu erhalten bleibt
Eine Form fehlt bewusst, und es ist die, auf die man als Erstes stößt. Die Current-Row-Kurzform [@Column] wird nicht unterstützt, aus einem strukturellen Grund, den zu verstehen sich lohnt, statt ihn blind zu umgehen
Warum ist ein strukturierter Verweis nicht einfach ein Bereich mit einem freundlichen Namen?
Weil ein definierter Name eine Adresse einfriert und ein Tabellenverweis das nicht tut. Schreibt man DataBlock als Namen, der auf Sheet1!$A$2:$D$100 zeigt, bleibt es dieses Rechteck, bis irgendetwas es neu schreibt. Schreibt man Sales[Amount], bedeutet das "die Amount-Spalte der Sales-Tabelle", was auch immer der Umfang dieser Tabelle gerade ist, wenn die Formel ausgewertet wird. Fügt man zwanzig Zeilen zur Tabelle hinzu, deckt die Summe sie ab; es gibt keinen Verweis anzupassen, weil von Anfang an keine Adresse in der Formel stand
Diese symbolische Eigenschaft ist genau der Grund, warum der Verweis nicht durch String-Ersetzung aufgelöst werden kann. Der Resolver muss die Tabelle im Arbeitsbuch nach Namen finden, die Spalte nach ihrem Kopfzeilentext nachschlagen, entscheiden, welche Zeilen der angeforderte Item-Spezifizierer abdeckt, und daraus ein konkretes Rechteck erzeugen. HotXLS tut das während der Formelkompilierung über das Tabellenmodell, weshalb eine vor dem Wachstum der Tabelle geschriebene Formel weiterhin gegen den aktuellen Umfang der Tabelle auswertet
Die Grammatik, die HotXLS auflöst
Die unterstützte Spezifizierer-Grammatik deckt ein einziges rechteckiges Ergebnis ab und ist es wert, präzise formuliert zu werden, weil Excels Dokumentation eine weit größere Oberfläche darstellt, als die meisten Engines implementieren. HotXLS akzeptiert [Col] und die geklammerte Variante [[Col]], die reinen Item-Spezifizierer [#Data], [#All], [#Headers] und [#Totals], die kombinierte Form [[#Data],[Col]], eine Spanne innerhalb eines Item-Spezifizierers als [[#Data],[Col1]:[Col2]] sowie eine reine Spanne [Col1]:[Col2]
Was dieses Set bietet, ist jede Verweisform, die einen einzigen zusammenhängenden Block ergibt: eine Spalte, eine Folge benachbarter Spalten, ein Ausschnitt nur des Körpers oder mit Kopfzeile von beidem. Nicht benachbarte Vereinigungen und Ergebnisse mit mehreren Bereichen liegen außerhalb davon. Kann ein Verweis nicht aufgelöst werden, behält die Formel das bisherige Skip-without-value-Verhalten, statt eine Vermutung einzusetzen, sodass ein nicht auflösbarer Verweis nie zu einer plausiblen falschen Zahl wird
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);
// ... Kopfzeile und 24 Datenzeilen schreiben ...
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;Warum ist die Current-Row-Form absichtlich ausgeschlossen?
[@Column] und [#This Row] bedeuten "die Zelle dieser Spalte in der Zeile, in der diese Formel steht". Der Wert hängt daher von der Position der auswertenden Zelle ab, nicht allein von der Tabelle. Das ist eine andere Art von Verweis: kein Rechteck, das der Compiler einmal auflösen kann, sondern eine Pro-Zelle-Auflösung, die für jede Zeile, die die Formel belegt, erneut erfolgen muss
HotXLS liefert für diese Formen False aus dem Table-Range-Resolver, was sie in den Skip-without-value-Pfad leitet. Der Formeltext bleibt erhalten und wird unverändert zurückgeschrieben, sodass ein Arbeitsbuch, das [@Amount] verwendet, nach einem Roundtrip durch die eigene Anwendung korrekt in Excel öffnet; nur der von HotXLS berechnete Wert fehlt. Zwischen einem fehlenden Wert und einem gegen die falsche Zeile berechneten Wert ist Fehlen dasjenige, das man erkennen kann
Der praktische Workaround ist mechanisch: In einem selbst erzeugten Arbeitsbuch schreibt man den äquivalenten A1-Verweis relativ, was Excel für einen Großteil der tabellenbezogenen Logik ohnehin intern speichert. In einem nur verarbeiteten Arbeitsbuch lässt man die Formel unangetastet und liest den von Excel bereits gespeicherten Cache-Wert, was eine Load-and-Report-Pipeline meist ohnehin will
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
// Recordset-artige Suche über den Tabellenkörper, 1-basiertes Zeilenergebnis
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;Was passiert, wenn die Tabelle die Form ändert
Strukturierte Verweise werden ungültig gemacht, statt still auf etwas anderes umgebogen zu werden, wenn das, worauf sie benannt sind, verschwindet. Löscht man eine Spalte, werden Formeln, die auf diese Spalte verweisen, ungültig, so wie Excel sie ungültig macht; löscht oder benennt man die Tabelle um, wird ebenso mit Verweisen darauf verfahren. Das ist das korrekte Verhalten, und es spiegelt die gewöhnliche Verweisanpassung, die in Formelverweisanpassung beim Einfügen und Löschen beschrieben wird, wo die Aufgabe der Engine ist, Formeln ehrlich zu halten, statt sie bloß gültig aussehen zu lassen
Zeilenwachstum ist der umgekehrte Fall und braucht überhaupt keine Anpassung. Weil der Verweis die Tabelle benennt statt eines Rechtecks, erweitert das Anhängen von Zeilen innerhalb des Tabellenbereichs, was [#Data] abdeckt, ohne dass eine einzige Formel angefasst wird. Genau diese Eigenschaft macht Tabellen für eine Berichtsvorlage lohnend: Die Summenzeile summiert weiterhin alles, was der Import erzeugt hat, egal wie viele Zeilen das am Ende waren
Roundtrip-Disziplin
HotXLS behält den ursprünglichen Formeltext. Ein mit SUM(SalesTable[Amount]) geladenes Arbeitsbuch wird mit SUM(SalesTable[Amount]) gespeichert, nicht mit der aufgelösten Form SUM(D2:D25). Das zählt mehr, als es scheinen mag: Ein Nutzer, der die eigene Ausgabe in Excel öffnet, erwartet, die geschriebene Formel zu sehen, und eine aufgelöste Adresse würde ein selbsterhaltendes Modell still in ein brüchiges verwandeln, das aufhört, neue Zeilen abzudecken
Zwei verwandte Fähigkeiten vervollständigen das Bild. Die Tabellendefinitionen selbst, einschließlich kopfzeilenloser Tabellen und Kommentare pro Tabelle, gehen durch das in Datenvalidierung, AutoFilter und Excel-Tabellen beschriebene Tabellenmodell im Roundtrip. Und wenn viele Zellen ein Muster teilen, speichert XLSX sie einmal als geteilte Formel, die expandiert und neu ausgegeben wird, wie in Shared-Formula-si-Expansion behandelt. Strukturierte Verweise innerhalb geteilter Formeln durchlaufen beide Pfade, also müssen sich beide korrekt verhalten, und das tun sie
HotXLS liest und schreibt XLS, XLSX und ODS aus Delphi und C++Builder ohne installiertes Excel und ohne Office-Automatisierung und wertet Formeln in seiner eigenen Engine aus. Das Tabellenmodell, die Formel-Engine und die Recalculation-API sind auf der HotXLS Delphi Spreadsheet-Komponente-Seite dokumentiert