Live-Formeln über Arbeitsmappen hinweg in Delphi mit HotXLS

HotXLS kann eine Formel wie =[Rates.xlsx]FX!B4*C10 gegen eine andere Arbeitsmappe auswerten, die im Speicher geöffnet ist, statt gegen einen veralteten zwischengespeicherten Wert. Sie registrieren die zweite Arbeitsmappe namentlich mit RegisterExternalWorkbook, und jede Neuberechnung liest die referenzierten Zellen direkt aus diesem Objekt. Heben Sie die Registrierung auf, fällt die Auswertung auf den externen Link-Cache zurück, den die Datei bereits mitführt

Das schließt eine spezifische und ärgerliche Lücke. Excel-Arbeitsmappen referenzieren einander routinemäßig: eine Kurstabelle, einen Kontenplan, eine von einem anderen Team gepflegte Preisliste. Wird eine solche Datei von einer Bibliothek statt von Excel geöffnet, lösen sich diese Referenzen normalerweise auf die Werte auf, die beim letzten Mal, als Excel neu berechnet hat, zwischengespeichert wurden, was Monate alt sein kann, und keine Menge an Neuberechnung behebt das, weil die Daten schlicht nicht da sind

Wie sieht eine mappenübergreifende Referenz innerhalb der Datei aus?

Eine externe Referenz wird nicht als der Text gespeichert, den Sie in der Bearbeitungsleiste sehen. Die Arbeitsmappe führt eine Tabelle unterstützender Mappen, jedes Formel-Token zeigt auf einen Index in dieser Tabelle, und ein separater Teil mit zwischengespeicherten Werten hält das zuletzt bekannte Ergebnis für jede referenzierte Zelle. Deshalb zeigt eine ohne ihre Begleitdateien geöffnete Datei trotzdem Zahlen

Das Parsen der Textform hat eigene Feinheiten. Der Lexer behandelt einen in Klammern gesetzten Mappennamen als Teil eines einzigen Bezeichners, sodass [Book.xlsx]Sheet!A1 zu einem Token wird statt zu einer unausgeglichenen Klammer gefolgt von einer Blattreferenz. Namen werden dann für die Suche normalisiert: Anführungszeichen, Pfadpräfixe, die Dateierweiterung und die numerische Indexform verschmelzen alle zu einem großgeschriebenen Basisnamen, sodass 'C:\Rates\[Rates.xlsx]FX'!B4 und [rates.xlsx]FX!B4 auf dieselbe registrierte Mappe auflösen

Eine Arbeitsmappe registrieren und neu berechnen

Die Registrierung erfolgt namentlich, und zwar mit dem Namen, der in den Formeln der referenzierenden Arbeitsmappe erscheint:

uses
  lxHandleX;

var
  Main, Rates: TXLSXWorkbook;
begin
  Main := TXLSXWorkbook.Create;
  Rates := TXLSXWorkbook.Create;
  try
    if (Main.Open('valuation.xlsx') <> 1) or
       (Rates.Open('rates.xlsx') <> 1) then
      Exit;

    // Formeln in valuation.xlsx, die [Rates.xlsx]... angeben, lesen nun
    // aus dem lebenden Objekt statt aus den zwischengespeicherten externen Werten
    Main.RegisterExternalWorkbook('Rates.xlsx', Rates);
    Main.Recalculate;

    Writeln(VarToStr(Main.Sheets[0].Cells[10, 3].Value));

    // Quelle ändern und erneut neu berechnen: der neue Wert fließt durch
    Rates.Sheets[0].Cells[4, 2].Value := 1.0842;
    Main.Recalculate;
    Writeln(VarToStr(Main.Sheets[0].Cells[10, 3].Value));
  finally
    Main.UnregisterExternalWorkbook('Rates.xlsx');
    Rates.Free;
    Main.Free;
  end;
end;

Die Registrierung hält eine schwache Referenz. HotXLS übernimmt nicht den Besitz der registrierten Arbeitsmappe und wird sie nicht freigeben, was bedeutet, dass die Lebensdauer in Ihrer Verantwortung liegt und die Reihenfolge wichtig ist: Heben Sie die Registrierung auf, bevor Sie die Quell-Arbeitsmappe freigeben, oder berechnen Sie zumindest nach dem Freigeben nicht mehr neu

Was passiert, wenn eine Mappe nicht registriert ist?

Die Auswertung fällt auf den in der Datei gespeicherten externen Link-Cache zurück, was genau dem Verhalten einer ohne ihre Begleitdateien geöffneten Arbeitsmappe entspricht. Dieser Fallback ist der Grund, warum das Aufheben der Registrierung sicher statt destruktiv ist

Das Verhalten nach dem Aufheben der Registrierung ist es wert, präzise formuliert zu werden, denn es überrascht Leute. Kann eine Referenz weder aus einer registrierten Mappe noch aus dem Cache aufgelöst werden, erzeugt die Auswertung einen Fehler, und dieser Fehler wird nicht in die Zelle zurückgeschrieben. Die Zelle behält ihren zuletzt berechneten Wert. Registrieren Sie die Quelle erneut und berechnen Sie neu, aktualisiert sich der Wert wieder

Diese Regel des Bewahrens-bei-Fehlschlag ist bewusst gewählt. Die Alternative, in dem Moment, in dem eine Quelle nicht verfügbar ist, in jede abhängige Zelle einen Fehlerwert zu schreiben, würde eine vorübergehend fehlende Kurstabelle in eine Arbeitsmappe voller Fehlermarkierungen verwandeln, die eine spätere erfolgreiche Neuberechnung reparieren müsste. Den letzten guten Wert zu behalten bedeutet, dass eine unterbrochene Pipeline ein Dokument hinterlässt, das immer noch sinnvoll lesbar ist

Die Weitergabe von Änderungen endet an der Arbeitsmappengrenze

Das Bearbeiten einer Zelle in der registrierten Quelle markiert abhängige Zellen in der Haupt-Arbeitsmappe nicht als geändert. Die inkrementelle Neuberechnung verfolgt Abhängigkeiten innerhalb einer Arbeitsmappe; sie beobachtet nicht über die Grenze hinweg. Excel verhält sich genauso, weshalb sich seine externen Referenzen beim Öffnen oder bei einer expliziten Aktualisierung aktualisieren, nicht kontinuierlich

Die praktische Konsequenz ist, dass das Ändern eines Quellwerts eine explizite Neuberechnung der referenzierenden Arbeitsmappe erfordert, wie im obigen Beispiel. Bearbeitet Ihre Anwendung beide Mappen in einer Schleife, berechnen Sie einmal am Ende neu statt nach jeder Bearbeitung; die unter inkrementelle Neuberechnung und der Abhängigkeitsgraph beschriebene inkrementelle Engine erledigt dann die minimale Arbeit innerhalb jeder Arbeitsmappe

Drei Formen, in die sich dieses Feature einfügt

Konsolidierung ist die naheliegendste. Eine übergeordnete Arbeitsmappe referenziert zwölf Monatsdateien; registrieren Sie alle zwölf, berechnen Sie einmal neu und lesen Sie die konsolidierten Zahlen, ohne jemals Excel zu öffnen. Der Registrierungsname für jede Mappe muss dem entsprechen, was die Formeln der übergeordneten Mappe tatsächlich angeben, was es wert ist, gegen den Formeltext zu prüfen, statt anzunehmen, dass er dem Dateinamen entspricht

Szenarioanalyse ist die zweite. Registrieren Sie eine Quelle, schreiben Sie einen Satz Annahmen hinein, berechnen Sie das abhängige Modell neu, zeichnen Sie die Ergebnisse auf und wiederholen Sie das. Da die Quelle ein lebendes Objekt ist, wird zwischen den Iterationen keine Datei geschrieben, und hundert Szenarien kosten hundert Neuberechnungen statt hundert Speicher-und-Neuladen-Zyklen

Validierung vor der Auslieferung ist die dritte und am wenigsten offensichtliche. Öffnen Sie eine Arbeitsmappe, registrieren Sie ihre Begleitmappen, berechnen Sie neu und vergleichen Sie die Ergebnisse mit den Werten, die die Datei bereits mitführte. Eine Abweichung bedeutet, dass die zwischengespeicherten Werte veraltet sind, ein Defekt, den es wert ist, vor der Auslieferung an einen Kunden abzufangen. Dieser Vergleich ist ein natürlicher Begleiter zum Arbeitsmappen-Diffing, beschrieben unter Diffing zweier Excel-Arbeitsmappen

Referenzhygiene, bevor Sie sich darauf verlassen

Führen Sie zunächst einen Formelanalysedurchlauf aus. Er meldet fehlende Funktionen, externe Referenzen und Komponenten zirkulärer Abhängigkeiten in einem Durchgang, was Ihnen genau zeigt, welche Mappennamen ein Dokument erwartet, bevor Sie nach den Dateien suchen:

var
  Issues: TArray<TXLSXFormulaIssue>;
  I: Integer;
begin
  Issues := Main.AnalyzeFormulaIssues;
  for I := 0 to High(Issues) do
    if Issues[I].Kind = xlsxFormulaIssueExternalReference then
      Writeln(Format('sheet %d R%dC%d needs [%s] (%s)',
        [Issues[I].Sheet, Issues[I].Row, Issues[I].Col,
         Issues[I].ExternalWorkbook, Issues[I].SourceFileName]));
end;

Zwei weitere Gewohnheiten sparen Zeit. Halten Sie den Registrierungsnamen kanonisch — registrieren Sie unter dem einfachen Dateinamen und lassen Sie die Normalisierung mit Anführungszeichen und pfadpräfixierten Varianten in den Formeln umgehen. Und behandeln Sie eine Arbeitsmappe, die Dateien referenziert, die Sie nicht bereitstellen können, als schreibgeschütztes Artefakt: Das Neuberechnen bewahrt die zwischengespeicherten Werte, aber jede Bearbeitung, die eine abhängige Zelle ändert, hinterlässt das Dokument in einem Zustand, in dem manche Werte aktuell und manche historisch sind, ohne dass irgendetwas in der Datei angibt, welche welche sind. Das Kopieren von Formeln zwischen Arbeitsmappen ist eine andere Operation mit eigenen Regeln zur Neuverknüpfung, behandelt unter mappenübergreifendes Kopieren und Formel-Neuverknüpfung, und definierte Bereiche fügen eine weitere Ebene hinzu, beschrieben unter definierte Namen und blattübergreifende Formeln

Live-Auswertung über Arbeitsmappen hinweg, inkrementelle Neuberechnung und Formelanalyse laufen für Delphi und C++Builder auf derselben Engine; die vollständige Funktionsliste finden Sie auf der HotXLS-Delphi-Tabellenkalkulationskomponentenseite