Technischer Artikel

Strukturierte Excel-Tabellenverweise in Delphi (HotXLS)

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

Diagramm, wie HotXLS in Delphi den strukturierten Verweis SalesTable[Amount] auflöst, indem es die Tabelle per Namen findet, den Amount-Spaltenkopf matcht und den #Data-Spezifizierer anwendet, um einen konkreten Bereich zu erhalten
HotXLS kompiliert Table[Column]-Verweise beim Parsen gegen das Tabellenmodell und macht daraus den aktuellen konkreten Bereich, also folgt die Formel der Tabelle weiterhin, während sie wächst

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

Karte der strukturierten Verweis-Grammatik, die HotXLS in Delphi akzeptiert: welche Tabellenregionen die Formen #Data, #Headers, #Totals und #All abdecken und wie die geklammerten Schreibweisen auf dieselben Rechtecke mappen
Jede tabellenweite Form deckt ein zusammenhängendes Band der Tabelle ab, und geklammerte Varianten lösen exakt wie ihre schlichten Schreibweisen auf

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

Flussdiagramm, wie HotXLS in Delphi die Current-Row-Form [@Amount] gegen die Zeile der auswertenden Zelle auflöst, während der Formeltext unverändert round-trippt, sodass Excel nach dem Speichern dieselbe Referenz liest
Current-Row-Referenzen werden je Zelle gegen die Zeile aufgelöst, auf der die Formel sitzt, und die gespeicherte Datei trägt weiterhin den originalen Verweistext für Excel zum Lesen

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