Eine Excel-Tabelle, deren Amount-Spalte =[@Qty]*[@Price] hält, speichert [@Qty] nirgendwo in der gespeicherten Datei. Entpackt man die Arbeitsmappe, zeigt das <f>-Element im Sheet-XML, was Excel tatsächlich schreibt: die lange, tabellenqualifizierte Schreibweise Lines[[#This Row],[Qty]]*Lines[[#This Row],[Price]], denn die Formelgrammatik in ECMA-376 Part 1, §18.17 hat ein #This Row-Schlüsselwort und kein @. Eine Summenzelle weiter unten hält SUM(Lines[Amount]). Keiner der beiden Strings enthält eine einzige Zelladresse, also hat eine Engine, die nur A1-Referenzen parst, nichts zu rechnen, bis sie weiß, wo die Tabelle sitzt, welche Zeile die Kopfzeile trägt und auf welcher Zeile die Formel selbst lebt
Dies letztes Informationsstück ist das unbequeme. Eine Spalten-Referenz lässt sich einmal je Tabelle in ein Rechteck umwandeln. Eine Current-Row-Referenz nicht; sie bedeutet eine andere Zelle für jede Zeile, zu der sie kopiert wird
Warum ist ein strukturierter Verweis nicht einfach ein Bereich mit einem freundlichen Namen?
Ein definierter Name friert eine Adresse ein, und ein Tabellenverweis tut das nicht. Zeigt der Name DataBlock auf Sheet1!$A$2:$D$100, bleibt er dieses Rechteck, bis etwas ihn umschreibt. Sales[Amount] bedeutet "die Amount-Spalte der Tabelle Sales", was auch immer die Ausdehnung der Tabelle gerade ist, wenn die Formel ausgewertet wird. Fügt man der Tabelle zwanzig Zeilen hinzu, deckt die Summe sie ab; es gibt keine Referenz anzupassen, denn die Formel hielt von Anfang an keine Adresse
HotXLS löst diese Referenzen bei jedem Kompilieren einer Formel zur Auswertung auf. TXLSXWorkbook schlägt die Tabelle im Tabellenmodell der Arbeitsmappe nach (TXLSXTable.Name oder DisplayName, groß-/kleinschreibungsunabhängig verglichen), matcht die Spalte gegen den in TXLSXTable.Columns gespeicherten Kopftext, wendet den Item-Spezifizierer an, um das Zeilenband zu wählen, und reicht dem Compiler einen absoluten Bereich wie 'Sales'!$D$2:$D$25 weiter. Der umgeschriebene Text existiert nur für diese Kompilierung. Die Zelle behält die Formel exakt so, wie sie geschrieben wurde, und weil der Lookup gegen TXLSXTable.Range läuft, wie es zum Neuberechnungszeitpunkt steht, wertet eine vor dem Wachstum der Tabelle geschriebene Formel weiterhin gegen die größere Tabelle aus
Die Rechteck-Grammatik: Spalten, Spannen und Item-Spezifizierer
Jede tabellenweite Form, die HotXLS auswertet, ergibt einen zusammenhängenden Block. Die akzeptierten Schreibweisen sind [Col] und ihr geklammerter Zwilling [[Col]], die bloßen Item-Spezifizierer [#Data], [#All], [#Headers] und [#Totals], die kombinierte Form [[#Data],[Col]], eine Spanne innerhalb eines Item-Spezifizierers wie [[#Data],[Col1]:[Col2]] und eine schlichte Spanne [[Col1]:[Col2]]. Eine Spanne darf rechts nach links geschrieben sein; [[Q2]:[Q1]] löst zu denselben zwei Spalten auf wie [[Q1]:[Q2]]
Das Zeilenband, das jeder Spezifizierer wählt, folgt zwei Flags der Tabelle. TXLSXTable.HasHeaderRow entscheidet, ob die erste Zeile von Range eine Kopfzeile ist, und TXLSXTable.TotalsRowShown entscheidet, ob die letzte Zeile eine Summenzeile ist. Eine schlichte Spalten-Referenz und [#Data] decken die Zeilen zwischen beiden ab. [#All] nimmt die volle Range, Kopf- und Summenzeile eingeschlossen. Fragt man [#Headers] an einer Tabelle ohne Kopfzeile ab, [#Totals] an einer ohne Summenzeile oder eine Spalte einer Tabelle, deren Körper keine Zeilen hat, wird die Referenz zu #REF!. Das deckt mit dem, was Excel für dieselbe Anfrage zeigt, und es hält eine unmögliche Referenz davon ab, still zu einem leeren Bereich zu werden, der null summiert
Spaltennamen, die Klammern, ein Hash, ein At-Zeichen oder einen Apostroph enthalten, folgen Excels Escape-Regel: Jedes solche Zeichen erhält innerhalb der Referenz einen vorangestellten Apostroph, also wird ein Kopf namens Unit [USD] als Sales[Unit '[USD']] geschrieben. Die Einzelspalten- und Current-Row-Formen dekodieren diese Escapes vor dem Matching. Spannen und die kombinierten [#Data],[Col]-Formen lösen gegen Tabellen auf dem eigenen Blatt der Formel auf, also ist ein Summen-Blatt, das eine Spanne über die Tabelle eines anderen Blatts braucht, mit einer schlichten Spalten-Referenz je Spalte besser bedient
Das Beispiel unten baut eine 24-Zeilen-Verkaufstabelle und platziert fünf Summenformeln daneben in F2 bis F6, auf demselben Blatt wie die Tabelle. Zeile 1 ist die Kopfzeile, die Zeilen 2 bis 25 sind der Körper, und es gibt keine Summenzeile, also spannt SalesTable[#All] 25 Zeilen, während SalesTable[Amount] 24 spannt
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[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;
Erwartete Werte für dieses Layout: F5 ist 25 und F6 ist 4, eine Zählung je Kopfzelle. F2 bis F4 hängen von den Daten ab, die man schreibt
Woher weiß [@Column], welche Zeile zu lesen ist?
Eine Current-Row-Referenz nimmt ihre Zeile aus der Zelle, die gerade ausgewertet wird. Erreicht Recalculate eine Formel mit Lines[@Qty] in Zeile 7, löst HotXLS sie zur Qty-Spalte von Zeile 7 auf; derselbe Formeltext in Zeile 8 liest Zeile 8. Die lange Schreibweise, die Excel auf die Platte schreibt, Lines[[#This Row],[Qty]], wird als dieselbe Referenz behandelt, und Lines[@[Unit Price]] für einen Kopf mit Leerzeichen ebenfalls
Die unqualifizierte Form [@Qty], ohne Tabellennamen davor, braucht eine weitere Tatsache: zu welcher Tabelle sie gehört. HotXLS beantwortet das über die Position. Sitzt die auswertende Zelle innerhalb der Körperzeilen und der Spaltenspanne einer Tabelle auf demselben Blatt, liefert diese Tabelle die Spalte, und innerhalb einer solchen Tabelle wird [@[Q1]:[Q2]] zu einem Ein-Zeilen-Bereich über diese Spalten. Überall sonst bleibt die Referenz unaufgelöst. Ein definierter Name, dessen Formel eine Current-Row-Referenz verwendet, nimmt seine Zeile aus der Zelle, die auf den Namen verweist, dasselbe Verhalten, das eine Formel inline bekäme
Halten Sie Current-Row-Formeln auf den eigenen Körperzeilen der Tabelle und qualifizieren Sie sie mit dem Tabellennamen, wenn der Code sie erzeugt. Qualifizierte Referenzen überstehen das Verschobenwerden, sie lesen sich so, wie Excel sie speichert, und eine Formel auf den eigenen Zeilen der Tabelle ist der eine Fall, in dem Excel und jeder andere Verbraucher der Datei darüber einig sind, was @ bedeutet
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);
// ... Kopfzeile sowie Artikel, Mengen und Preise in den Zeilen 2..13 schreiben ...
// Ein Formeltext, zwölf verschiedene Ergebnisse: jede Zelle liest ihre eigene Zeile
for Row := 2 to 13 do
Sheet.Cells[Row, 4].Formula := 'Lines[@Qty]*Lines[@Price]';
// Gegenprobe unter der Tabelle: Spaltensumme gegen ein neu berechnetes Produkt
Sheet.Cells[15, 4].Formula :=
'SUM(Lines[Amount])-SUMPRODUCT(Lines[Qty],Lines[Price])';
Book.Recalculate;
// Erwartet: D15 evaluiert zu 0, wenn jede Amount-Zelle zu Qty * Price passt
Book.SaveAs('orders.xlsx');
finally
Cols.Free;
Book.Free;
end;
end;
Die Probe in D15 ist billig und lohnt sich, in einem generierten Report zu bleiben, solange er gebaut wird. Eine Zeilenformel, die die falsche Zeile erwischt hätte, ließe die beiden Summen auseinanderlaufen, und die Differenz zeigt sich als eine Zahl ungleich null in einer Zelle, die jeder lesen kann
Was passiert, wenn die Tabelle die Form ändert
Wachstum ist der einfache Fall, sofern die Tabellendefinition tatsächlich wächst. Einen Wert in Zeile 26 unter eine Tabelle zu schreiben, deren Range A1:D25 ist, macht Zeile 26 nicht zum Teil der Tabelle. Auto-Erweiterung ist ein Excel-Editier-Feature, ausgelöst davon, dass jemand neben der Tabelle tippt, und Zellen über HotXLS zu schreiben setzt es nicht ab. TXLSXTable.Range nach einem Import auf die neue Ausdehnung setzen, oder die Zeilen innerhalb der Tabelle mit TXLSXWorksheet.InsertRows einfügen, was Tables[i].Range zusammen mit jeder anderen Referenz auf dem Blatt verschiebt. So oder so weitet sich [#Data], und keine einzige Formel ändert sich
Schrumpfen ist der Fall, in dem Referenzen brechen können, und HotXLS folgt Excels Regel. DeleteCols über eine Tabellenspalte schreibt jede strukturierte Referenz, die diese Spalte nennt, zu #REF! um, in Zellformeln, bedingten Formatierungen, Datenvalidierungsregeln, Diagrammserien und definierten Namen. DeleteRows, das die Kopfzeile entfernt, löscht die Tabelle und erklärt ihre Referenzen auf dieselbe Weise für ungültig, und eines, das die Summenzeile entfernt, löscht TotalsRowShown, wonach [#Totals] keine Zeile mehr hat, auf die es zeigen könnte. Gewöhnliche A1-Referenzen durchlaufen dieselbe Anpassung, beschrieben in Formelverweisanpassung beim Einfügen und Löschen
TXLSXTables.DeleteByName und Umbenennen über TXLSXTable.Name arbeiten nur am Tabellenmodell. Formeln, die den alten Namen nennen, behalten ihren Text, also benennen Sie diese Formeln im selben Durchgang um, wenn Ihr Code eine Tabelle umbenennt
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]; // Arbeitsblätter der Mappe sind 1-basiert
Table := Sheet.Tables.FindByName('SalesTable');
if Table = nil then Exit;
// ... 20 importierte Zeilen nach A26:D45 schreiben ...
Table.Range := 'A1:D45'; // der Körper läuft nun von Zeile 2 bis Zeile 45
Book.Recalculate; // SUM(SalesTable[Amount]) in F2 deckt nun 44 Zeilen ab
// Recordset-artiger Lookup über den Tabellenkörper, 1-basiertes Zeilenergebnis, 0 = keiner
Row := Table.FindFirst(Sheet, 'Region', 'EMEA');
while Row > 0 do
begin
Log(VarToStr(Sheet.Cells[Row, 4].Value)); // Log ist Ihre eigene Routine
Row := Table.FindNext(Sheet, 'Region', 'EMEA', Row);
end;
Book.SaveAs('sales.xlsx');
finally
Book.Free;
end;
end;
FindFirst und FindNext vergleichen Strings groß-/kleinschreibungsunabhängig und Zahlen dem Wert nach, und sie durchlaufen nur die Körperzeilen, also wird der Kopftext Region nie als Treffer zurückgegeben
Roundtrip-Disziplin
Eine Arbeitsmappe, geladen mit SUM(SalesTable[Amount]), wird mit SUM(SalesTable[Amount]) gespeichert, und eine Zelle mit Lines[[#This Row],[Qty]] geht mit diesem Text unversehrt wieder hinaus. Die aufgelöste Adresse ersetzt nie, was der Nutzer schrieb. Würde HotXLS stattdessen SUM($D$2:$D$25) speichern, öffnete sich die Datei in Excel tadellos und hörte dann beim ersten Mal, das jemand die Tabelle erweiterte, auf, neue Zeilen abzudecken — die langsamste Art von Bruch, die sich zurück zu ihrer Ursache verfolgen lässt
Die Tabellendefinitionen selbst, einschließlich kopfloser Tabellen und Kommentare je Tabelle, round-trippen durch das Tabellenmodell, das in Datenvalidierung, AutoFilter und Excel-Tabellen behandelt wird. Wenn viele Zellen ein Formelmuster teilen, kann XLSX es einmal als Shared Formula speichern, und die Expansion dieser Zellen ist in Shared-Formula-si-Expansion behandelt. Eine von Excel gespeicherte berechnete Spalte kommt oft so an, also durchläuft eine strukturierte Referenz in einem Tabellenkörper beide Pfade, bevor sie ausgewertet wird: Die Shared Formula expandiert zuerst zu Text je Zelle, dann löst jede Zelle ihre eigene Zeile auf
Das Tabellenmodell, die Recalculate-Engine und die hier verwendeten Einfüge- und Lösch-Aufrufe des Arbeitsblatts sind Teil von HotXLS, das XLS, XLSX und ODS aus Delphi und C++Builder ohne Excel-Installation liest und schreibt. Die vollständige API-Referenz steht auf der HotXLS-Delphi-Spreadsheet-Komponentenseite