Eine Gesamtsumme, die Excel als 37 zeigt, kann nach dem Öffnen und Speichern der .xls-Datei durch ein Programm, das keine einzige Zelle anfasst, als 67 zurückkommen. Die zusätzlichen 30 sind nicht zufällig. Es ist exakt die Summe der beiden Gruppenzwischensummen, 10 und 20, die im Bereich liegen, den die Gesamtsumme abdeckt. Damit sich diese Zahl bewegt, müssen zwei Dinge zusammen schiefgehen: das Speichern muss jede Formel auf dem Weg raus neu berechnen, und was auch immer neu berechnet, muss vergessen, dass ein SUBTOTAL andere SUBTOTAL-Zellen überspringt. Entfernt man eines der beiden, überlebt die Datei, weshalb das erste Problem unbemerkt hinter dem zweiten sitzen kann
Warum ändert das Speichern einer XLS-Datei einen Formelwert?
Ein Speichern ändert einen Formelwert, wenn der Writer die Zahl berechnet, die er neben jeder Formel ablegt, statt die geladene zu kopieren. Jeder BIFF8-Formula-Record trägt ein acht Byte langes FormulaValue-Feld ([MS-XLS] §2.4.127, Wertlayout in §2.5.133), das das Ergebnis hält, das Excel für diese Zelle zuletzt angezeigt hat. Ein Writer muss etwas in diese acht Bytes schreiben, und der kürzeste Codepfad ist, jede Formel an die Berechnungs-Engine zu reichen. Sobald das passiert, ist jedes Speichern eine vollständige Neuberechnung, die niemand bestellt hat, und jede Stelle, an der diese Engine mit Excel uneins ist, wird zu einer geänderten Zahl in einer Datei, die der Nutzer für unangetastet hält
Die SUBTOTAL-Abweichung ist ein gutes Beispiel, weil die Spreadsheet-Regel einfach ist und die Falle in Delphis Variant-Typisierung liegt. Excel definiert jede SUBTOTAL-Funktionsnummer als ignorierend gegenüber Zellen, deren eigene Formel ein weiteres SUBTOTAL ist. Für ein Sheet, dessen Detailzeilen sich zu 37 addieren (die Zeilen unter zwei Gruppen mit Zwischensummen 10 und 20, plus 7 weitere außerhalb jeder Gruppe), ist die Gesamtsumme 37. Eine Engine, die die verschachtelten Zwischensummen übersieht, zählt die 10 und die 20 ein zweites Mal und landet bei 67. Sie zu überspringen heißt meistens, eine kurze Prüfung über den Formeltext, und die naheliegende Delphi-Version kompiliert, läuft und matcht nie etwas:
// Ein Helfer, den Sie selbst schreiben, zum Beispiel beim Scannen eines Sheets
// nach Formeln, die ein umschließendes SUBTOTAL überspringen muss
function IsNestedSubtotal(const F: Variant): Boolean;
begin
// Der verlockende Test gelingt auf Unicode-Delphi nie: ein String,
// der einem Variant zugewiesen wird, ist varUString, nicht varOleStr
// Result := (VarType(F) = varOleStr) and ...
Result := False;
if VarIsStr(F) then // akzeptiert varString, varOleStr und varUString
Result := SameText(Copy(F, 1, 9), 'SUBTOTAL(') or
SameText(Copy(F, 1, 10), '=SUBTOTAL(') or
SameText(Copy(F, 1, 10), 'AGGREGATE(') or
SameText(Copy(F, 1, 11), '=AGGREGATE(');
end;
// Verwendung mit HotXLS (Sheets, Zeilen und Spalten sind 1-basiert):
// if IsNestedSubtotal(Book.Sheets[1].Cells[12, 4].Formula) then ...VarIsStr ist der Test, den der HotXLS-Evaluator auf dieselbe Frage anwendet, und er behandelt AGGREGATE-Zellen ebenfalls als von einem umschließenden Subtotal ausgeschlossen, so wie Excel. Die Korrektur des Prädikats ist aber nicht die ganze Geschichte. Mit korrekter Prüfung schreibt das neu berechnende Speichern die 37 nur, weil die Engine in dieser einen Datei zufällig mit Excel übereinstimmt. Die nächste Formel, die die Engine leicht anders behandelt, wandert wieder. Die Regeln, welche Zellen SUBTOTAL und AGGREGATE überspringen, versteckte Zeilen eingeschlossen, behandelt der Artikel zu SUBTOTAL- und AGGREGATE-Versteckt-Zeilen. Mit Cache-first-Speichern geht eine Datei, die Sie nie hat rechnen lassen, mit 37 in der Zelle zurück auf die Platte, ganz gleich, was die Engine daraus gemacht hätte
Was ein klassisches XLS-Speichern in jeden Formula-Record schreibt
HotXLS speichert eine BIFF8-Mappe Cache-first: Für jede Formelzelle schreibt TXLSWorkbook.SaveAs den gecachten Wert, wann immer TryGetCachedFormulaValue einen melden würde, und lässt den Evaluator nur laufen, wenn nicht. Der Cache-Zustand entscheidet. Eine Zelle im Zustand xlfcsLoaded (der Wert kam aus der Quelldatei) oder xlfcsCalculated (HotXLS hat ihn in dieser Sitzung berechnet) bekommt ihr CacheInfo.Value Byte für Byte geschrieben. Eine Zelle im Zustand xlfcsMissing oder xlfcsInvalidated wird zur Save-Zeit evaluiert. Das entspricht der Art, wie Excel ein Speichern behandelt: ein Schnappschuss dessen, was jede Zelle zeigt – im manuellen Berechnungsmodus womöglich stark veraltet –, der trotzdem treu geschrieben wird
Der Evaluationspfad bleibt aus guten Gründen. Eine Formel, die Sie im Code über Cells[Row, Col].Formula zuweisen, hat noch keinen Cache, und das Zuweisen einer neuen Formel an eine geladene Zelle verschiebt sie nach xlfcsInvalidated, weil das alte Ergebnis zum alten Ausdruck gehört. Beide werden während des Speicherns berechnet, also öffnet eine von Grund auf generierte Mappe in Excel mit berechneten Zahlen statt Nullen. Kann selbst der Evaluator keinen Wert liefern (nicht unterstützte Funktion, kaputter externer Link), speichert der Writer eine Null und setzt fAlwaysCalc, Bit 0 der Record-Flags in §2.4.127, sodass Excel die Zelle beim Öffnen neu berechnet, statt dem Platzhalter zu vertrauen
Ein Property sieht verwandt aus und ist es nicht. TXLSWorkbook.RecalcOnSave spiegelt Excels Option „Arbeitsmappe vor dem Speichern neu berechnen“ aus dem CalcSaveRecalc-Record ([MS-XLS] §2.4.37). Es ist nur für Excel relevant, nur im manuellen Berechnungsmodus, und HotXLS schreibt es einfach in die Datei. SaveAs konsultiert es nicht, wenn es entscheidet, was in FormulaValue landet. Der Roundtrip unten ist der schnellste Weg, den Vertrag an einer Ihrer eigenen Dateien zu sehen:
procedure CheckRoundTrip(const Source, Target: string;
Row, Col: Integer);
var
Book: TXLSWorkbook;
Before, After: TXLSFormulaCacheInfo;
begin
Book := TXLSWorkbook.Create;
try
if Book.Open(Source) <> 1 then
raise Exception.Create('Cannot open ' + Source);
// Sheet, Zeile und Spalte sind 1-basiert
if not Book.TryGetCachedFormulaValue(1, Row, Col, Before) then
raise Exception.Create('Cell carries no usable cached value');
if Book.SaveAs(Target) <> 1 then // gecachte Zellen werden wie gespeichert geschrieben
raise Exception.Create('Save failed');
finally
Book.Free;
end;
Book := TXLSWorkbook.Create;
try
Book.Open(Target);
Book.TryGetCachedFormulaValue(1, Row, Col, After);
// Erwartet: After.Value gleich Before.Value, und After.State ist
// wieder xlfcsLoaded, weil es aus der gespeicherten Datei kam
Writeln(VarToStr(Before.Value), ' -> ', VarToStr(After.Value));
finally
Book.Free;
end;
end;Die Lese-Seite dieses Vertrags – was jeder Zustand bedeutet und warum ein gecachter Blank oder False trotzdem als Wert zählt – beschreibt Read Excel Cached Formula Values in Delphi Without Recalc. Führen Sie den Check oben auf der Gesamtsummenzelle einer Mappe mit verschachtelten Zwischensummen aus, und beide Zeilen drucken dieselbe Zahl
Wo bewahrt die Wurzel einer BIFF Shared Formula ihren Cache-Wert?
In seinem eigenen Formula-Record, wie jede andere Formelzelle auch, aber der Ausdruck kommt einen Record später, und diese Lücke ist der Ort, an dem ein nachlässiger Reader den Cache verliert. BIFF8 speichert eine Shared Formula als ShrFmla-Record ([MS-XLS] §2.4.260), direkt hinter dem Formula-Record der Zelle oben links der Gruppe. Jede Mitgliedszelle, die Wurzel eingeschlossen, trägt ein rgce aus einem einzelnen PtgExp-Token (§2.5.198): das Byte $01, gefolgt von Zeile und Spalte der Wurzel. Follower-Zellen sind einfach, denn wenn sie erscheinen, existiert der Ausdruck der Wurzel bereits, und ihr eigenes FormulaValue sitzt in ihrem eigenen Record. Die Wurzel ist es nicht. Wenn ihr Formula-Record gelesen wird, hat sie einen gecachten Wert von 37 und noch keinen Ausdruck
Einen Ausdruck auf einer Zelle zu installieren löscht ihren Cache, und das muss so bleiben, denn eine ersetzte Formel macht das alte Ergebnis tatsächlich bedeutungslos. Blind auf den ShrFmla-Record angewendet wirft dieselbe Regel die 37 der Wurzel im Moment weg, in dem der Ausdruck ankommt, TryGetCachedFormulaValue meldet die Wurzel als xlfcsMissing, und das Speichern fällt ausgerechnet für die Zelle auf den Evaluator zurück, die ein Nutzer am ehesten ansieht. Der HotXLS-Reader hält den dekodierten Wert der Wurzel, während er auf den ShrFmla-Record wartet, installiert den Ausdruck und schreibt dann den geladenen Wert mit Zustand xlfcsLoaded zurück. Array-Formeln (der Array-Record, §2.4.4) folgen derselben Record-Reihenfolge und bekommen dieselbe Behandlung. Die String-Form des Caches hatte dieses Problem nie, weil ihr Text in einem separaten String-Record ankommt und über Zeile und Spalte der Zelle zugeordnet wird, also nie seine Position im Stream zählt
Wer auch die OOXML-Seite behandelt, dem erklärt der Artikel zur XLSX-Shared-Formula-si-Expansion, warum das Paketformat keine Record-Ordnungs-Lücke kennt, aber eigene Expansions-Fallen mitbringt
Shared-Formula-Follower besitzen eine verschobene Kopie des Ausdrucks
Der Ausdruck in einem ShrFmla-Record ist relativ zur Wurzelzelle geschrieben, also braucht jeder Follower seine eigene Kopie, verschoben um seinen Abstand zur Wurzel. Wortgetreu kopiert gibt eine Gruppe mit Wurzel B1 und =A1*3 dieselbe =A1*3 an B2 und B3 weiter. HotXLS gibt jedem Follower eine Kopie, deren relative Referenzen um den Zeilen- und Spaltenabstand des Followers versetzt sind, sodass B2 ein echtes =A2*3 besitzt und B3 =A3*3
Das ist auch der Grund, warum ein schlichter Roundtrip nicht sagen kann, ob die Follower-Ausdrücke stimmen. Follower behalten ihre geladenen Werte durch ein Speichern, also würde eine wortgetreue Kopie durchgehen. Ein Check, der etwas beweist, muss einen Input ändern und neu berechnen. Die Routine unten schreibt eine Shared-Gruppe mit eingeschaltetem UseSharedFormulas (der Default ist False, was einen Formula-Record pro Zelle schreibt), öffnet die Datei erneut, ändert A2 und beobachtet B2:
procedure CheckSharedFollower(const FileName: string);
var
Book: TXLSWorkbook;
Sheet: TXLSWorksheet;
Info: TXLSFormulaCacheInfo;
Row: Integer;
begin
Book := TXLSWorkbook.Create;
try
Sheet := Book.Sheets.Add;
for Row := 1 to 3 do
begin
Sheet.Cells[Row, 1].Value := Row * 2; // 2, 4, 6
Sheet.Cells[Row, 2].Formula := '=A' + IntToStr(Row) + '*3';
end;
Book.UseSharedFormulas := True; // B1:B3 werden eine ShrFmla-Gruppe
Book.SaveAs(FileName); // noch keine Caches: evaluiert zu 6, 12, 18
finally
Book.Free;
end;
Book := TXLSWorkbook.Create;
try
Book.Open(FileName);
Book.Sheets[1].Cells[2, 1].Value := 10; // den Input des Followers ändern
Book.TryGetCachedFormulaValue(1, 2, 2, Info);
Writeln('B2 before: ', VarToStr(Info.Value)); // Erwartet: 12, weiterhin geladen
Book.Recalculate;
Book.TryGetCachedFormulaValue(1, 2, 2, Info);
Writeln('B2 after: ', VarToStr(Info.Value)); // Erwartet: 30, aus =A2*3
finally
Book.Free;
end;
end;Ein Follower, der =A1*3 wiederverwendet hätte, würde in der zweiten Zeile 6 drucken. Die erste Zeile zählt genauso: Sie zeigt, dass das Ändern eines Literals den geladenen Wert der abhängigen Formel in place lässt, bis etwas neu berechnet
Was Cache-first-Speichern Ihnen überlässt
Cache-first-Speichern bewahrt, was geladen wurde. Es verfolgt nicht, ob das Geladene noch stimmt. Das Ändern einer Zelle, von der eine Formel abhängt, markiert den Abhängigkeitsgraphen für die nächste Neuberechnung, aber die abhängige Zelle behält ihren xlfcsLoaded-Wert, und SaveAs schreibt diese veraltete Zahl, außer Sie rufen zuerst Recalculate auf oder lesen die Value der Zelle, was die Formel berechnet und sie nach xlfcsCalculated verschiebt. Recalculate gibt die Anzahl der Formelzellen zurück, die es nicht auswerten konnte, also lohnt es sich, ein Ergebnis ungleich null vor dem Speichern zu loggen
In der üblichen Pipeline öffnet ein Programm eine Mappe, die jemand anderes gebaut hat, füllt ein paar Labels oder einen Header ein und speichert. In dieser Datei sollte sich kein Wert ändern, und mit Cache-first-Speichern ändert sich keiner. Ein Programm, das Inputs bearbeitet, besitzt stattdessen den Neuberechnungsschritt, und dieser Schritt gehört in einen sichtbaren Recalculate-Aufruf neben den Änderungen, wo jeder, der den Code liest, ihn sehen kann. Excel macht denselben Trade im manuellen Berechnungsmodus. Der XLSX-Writer hat seine eigene Policy, RecalcBeforeSave mit den Modi xrmManual, xrmIfDirty und xrmAlways, gesetzt an der XLSX-Workbook-Klasse und unabhängig von allem oben
Aus dem Design folgen zwei Grenzen. Der Cache-Pfad hilft nur Zellen im Zustand xlfcsLoaded oder xlfcsCalculated, also zahlt ein Generator, der tausende neue Formeln schreibt und nie evaluiert, beim Speichern weiterhin eine Auswertung pro Zelle. Und der Vertrag regelt, was ein Speichern schreibt, nicht, was eine explizite Neuberechnung berechnet: Eine unangetastete Datei behält die Zahlen, die Excel gespeichert hat, während ein Recalculate die Ergebnisse des HotXLS-Evaluators schreibt. Vergleichen Sie beide an ein paar repräsentativen Mappen, bevor Sie eine Pipeline auf neu berechnete Speicherungen umstellen
Die HotXLS Delphi Spreadsheet Component liest und schreibt klassische BIFF8- und XLSX-Mappen aus Delphi und C++Builder, ohne Excel oder OLE-Automation installiert. Ihre Produktseite dokumentiert die Workbook-, Cached-Value- und Recalculation-Aufrufe, die in diesen Beispielen benutzt werden