HotXLSs AddCopy-Methode kopiert ein Arbeitsblatt von einer Excel-Arbeitsmappe in eine andere, indem sie jede Formel auf diesem Blatt in A1-Stil-Text dekompiliert und den Text innerhalb der Ziel-Arbeitsmappe neu kompiliert, statt den kompilierten Formelbaum direkt zu kopieren, weil Diagrammserien-Referenzen, Rich-Text-Font-Indizes und Nummerierungen externer Links jeweils unabhängig innerhalb jeder Arbeitsmappendatei vergeben werden
Der Fehlschlag zeigt sich in genau der Arbeitsmappe, die man erwarten würde: ein Monatsabschluss-Job, der aus dem Report jeder Filiale ein Blatt zieht und an eine Zusammenfassungsdatei anhängt. Öffnet man das Ergebnis, plottet ein Zwischensummen-Diagramm völlig andere Zahlen einer anderen Filiale, eine Notiz, die in der Quelle fett und rot war, ist wieder schlichter schwarzer Text, und eine Formel, die früher einen Steuersatz aus einer begleitenden Nachschlage-Arbeitsmappe zog, zeigt jetzt eine eingefrorene Zahl, die niemand erklären kann. Nichts wirft hier eine Exception — die Datei öffnet sich, die Zahlen sehen plausibel aus, und der Schaden sitzt dort, bis jemand ein Diagramm mit dem falschen Titel daneben bemerkt
Warum kann AddCopy nicht einfach den kompilierten Formelbaum kopieren?
AddCopy kann den kompilierten Formelbaum nicht unverändert verschieben, weil eine kompilierte BIFF-Formel kein eigenständiger Text ist — sie ist eine Sequenz von Tokens, und mehrere dieser Tokens sind kleine Ganzzahlen, die sich nur innerhalb der Arbeitsmappe korrekt auflösen, die sie erzeugt hat. Eine 3D-Referenz wie Sheet2!A1:A10 trägt einmal kompiliert nicht mehr den wörtlichen Namen Sheet2; sie trägt ein Feld, das die BIFF-Spezifikation ixti nennt (HotXLS hält denselben Wert im eigenen kompilierten Baum unter dem Feldnamen FExternID), einen Index in die private EXTERNSHEET-Tabelle dieser Arbeitsmappe, nummeriert je nachdem, in welcher Reihenfolge genau diese Arbeitsmappe ihre Blätter registriert hat. Verschiebt man das Token unverändert in eine Arbeitsmappe, deren EXTERNSHEET-Tabelle in anderer Reihenfolge aufgebaut wurde, bedeutet Index 3 nicht mehr Sheet2 — er bedeutet, welches Blatt auch immer dort Slot 3 belegt, und Excel hat keine Möglichkeit, den Fehler zu markieren, denn was das Dateiformat betrifft, ist die Formel vollkommen wohlgeformt. Genau diesen Fehlschlag zu vermeiden ist der Zweck von TXLSWorksheets.AddCopy: aus der eigenen Blattsammlung entweder der Quell- oder der Ziel-Arbeitsmappe in Delphi- oder C++Builder-Code aufgerufen, kopiert es ein Arbeitsblatt — Zellwerte, Formate, Formeln, Diagramme, Kommentare, Verbindungen, Seiteneinrichtung und mehr — aus einer Quell-Arbeitsmappe, die die sein kann oder nicht sein kann, auf der Sie es aufrufen, und hängt das Ergebnis unter einem von Ihnen gewählten Namen oder einer disambiguierten Kopie des Originalnamens an das Ziel an
var
Summary, Branch: IXLSWorkbook; // interface-counted: do not Free
begin
Summary := TXLSWorkbook.Create;
Branch := TXLSWorkbook.Create;
Branch.Open('branch-east.xls');
// Appends a copy of Branch's first sheet onto Summary, renamed to
// stay unique inside the destination workbook
Summary.Sheets.AddCopy(Branch.Sheets[1], 'East Detail');
Summary.SaveAs('consolidated.xls');
end;Die Lösung: zu Text dekompilieren, im Ziel neu kompilieren
HotXLS löst das Indexierungsproblem, indem es den kompilierten Baum selbst nie die Arbeitsmappengrenze überqueren lässt. Für jede Formelzelle bei einem arbeitsmappenübergreifenden Kopiervorgang dekompiliert AddCopy die Quellformel in denselben A1-Stil-Text, den ein Benutzer in Excels Formelleiste sehen würde, und übergibt diesen Text dann der Ziel-Arbeitsmappe, die ihn von Grund auf mit ihren eigenen Tabellen zu einem Baum zurückparst — eine blattqualifizierte Referenz wie Data!D2:D100 ist an diesem Punkt nur ein String, und ein String bedeutet in jeder Arbeitsmappe dasselbe, sodass, falls das Ziel bereits ein Blatt namens Data hat, die Referenz korrekt aufgelöst wird, ganz ohne Index-Übersetzung, weil nie ein roher Index unterwegs war, den es zu übersetzen gäbe. HotXLS bezahlt für diese Rundreise nur, wenn es sein muss: Das Kopieren eines Blatts innerhalb derselben Arbeitsmappe nimmt einen billigeren Pfad, bei dem der kompilierte Baum einfach im Speicher dupliziert wird, da jeder Index darin bereits dort gültig ist, wo er bleibt, und der Text-Umweg läuft nur, sobald AddCopy erkennt, dass Quelle und Ziel tatsächlich unterschiedliche Arbeitsmappen-Instanzen sind. Es lohnt sich auch, präzise zu sein, was dieses Umschreiben nicht ist. Es hat nichts mit dem Zeilen- und Spaltenverschieben zu tun, das läuft, wenn man Zeilen innerhalb eines einzelnen Blatts einfügt oder löscht, was ein begleitender Artikel ausführlich behandelt — jene Engine schreibt A1-Text an Ort und Stelle um, um Zellen zu verfolgen, die sich um ein paar Zeilen nach oben oder unten innerhalb einer Arbeitsmappe bewegt haben, während diese hier läuft, wenn eine Formel die Arbeitsmappe, die sie kompiliert hat, überhaupt verlässt, wo verschobene Zeilen nicht das Problem sind und arbeitsmappenprivate Nummerierung es ist
// Conceptually, this is what AddCopy does for each formula cell: turn
// the compiled tree back into text using the source workbook's own
// tables, then let the destination workbook parse that text back into
// a tree using its own tables, from scratch
FormulaText := SourceBook.GetUnCompiledFormula(SourceFormula, Row, Col, SourceSheetID);
DestFormula := DestBook.GetCompiledFormula(FormulaText, DestSheetID);Was, wenn das Ziel dieses Blatt oder diesen Namen noch nicht hat?
AddCopys Rekompilierung gelingt nur, wenn die Ziel-Arbeitsmappe bereits alles hat, worauf der Formeltext verweist, und die zwei Lücken, die in der Praxis auftauchen, sind ein gleichnamiges Blatt, das in diesem Batch noch nicht herüberkopiert wurde, und ein arbeitsmappenweit definierter Name, der im Ziel noch nie existiert hat. HotXLS wirft keine Exception, wenn die Rekompilierung mitten in einem Blattkopiervorgang fehlschlägt — die Value-Zuweisung der Zelle speichert stattdessen still den Formeltext als schlichten String, ein bewusster, inspizierbarer Fehlermodus statt eines stillen, denn eine Formelzelle, die unerwartet wörtlichen Text wie =SUM(Q1!B2:B12) statt einer berechneten Zahl zeigt, ist das Zeichen dafür, dass etwas weiter oben in der Kopie nicht aufgelöst wurde. Bevor es aufgibt, versucht AddCopy eine Reparatur: Es durchläuft den Syntaxbaum der fehlgeschlagenen Formel und sammelt jede definierte-Namen-ID, die die Formel berührt, und für jeden arbeitsmappenweiten Namen, der in der Quelle existiert, aber noch nicht im Ziel, kopiert es den Namen herüber und rekompiliert denselben Text ein zweites Mal. Blattgebundene Namen liegen außerhalb dessen, was diese Reparatur beheben kann, da ein Name, der nur für Formeln auf einem Blatt der Quell-Arbeitsmappe sichtbar ist, keinen gleichwertigen Slot hat, in den er migrieren könnte, und ein Ziel, das bereits einen gleich geschriebenen Namen besitzt, wird unangetastet gelassen statt überschrieben, unter der Annahme, dass ein Name, den der Aufrufer absichtlich vorab erstellt hat, der gewünschte ist. Innerhalb einer einzelnen Arbeitsmappe läuft die Namenssuche einer blattübergreifenden Formel automatisch von Blattbereich zu Arbeitsmappenbereich hinauf, der Mechanismus, den HotXLSs Artikel zu definierten Namen und blattübergreifenden Formeln behandelt; das Überqueren einer echten Arbeitsmappengrenze entfernt dieses Sicherheitsnetz vollständig, und ein Name muss absichtlich herübergetragen werden, oder die davon abhängige Formel degradiert zu Text
Diagrammserien-Referenzen brauchen dieselbe Lösung, aber einen anderen Codepfad
Eine HotXLS-Diagrammserie, die einen Zellbereich plottet, trifft genau auf dasselbe Nummerierungsproblem wie eine gewöhnliche Zellformel, weil die Datenbereichs-Referenz eines Diagramms ebenfalls ein kompilierter Formel-Token-Stream ist — die BIFF-Spezifikation nennt den Datensatz, der sie trägt, BRAI ([MS-XLS] Abschnitt 2.4.51) —, aber AddCopy kann das nicht beheben, indem es den normalen Diagramm-Lade-Pfad wiederverwendet, weil genau dieser Pfad den Fehler erzeugt. Wenn ein Diagramm-Datensatz beim gewöhnlichen Öffnen einer Datei von der Festplatte geparst wird, wird sein Formelbaum aufgebaut, indem die rohen Bytes durch welche Rechner-Instanz auch immer das Parsen gerade durchführt, übersetzt werden; speist man die rohen BRAI-Bytes eines Quell-Diagramms stattdessen durch den gewöhnlichen Datensatz-Loader der Ziel-Arbeitsmappe, wird das in diesen Bytes eingebettete ixti gegen die EXTERNSHEET-Tabelle des Ziels aufgelöst, sodass die Serie still auf welches Blatt auch immer diesen Slot dort belegt zeigt — dieselbe Fehlerklasse wie das unveränderte Kopieren des kompilierten Baums einer Zelle, nur schwerer zu bemerken, weil niemand Diagrammserien-Formeln so liest, wie man Zellformeln liest. HotXLS umgeht die Falle stattdessen mit einem dedizierten Klon-Pfad: TXLSCustomChart.AssignFrom kopiert die eigenen Nicht-Formel-Header-Bytes jedes Diagramm-Datensatzes wörtlich, und baut dann den angehängten Bereich über dieselbe Dekompilier-und-Rekompilier-Primitive wieder auf, die für gewöhnliche Zellen verwendet wird, sodass der neue Baum von Grund auf gegen die EXTERNSHEET-Tabelle des Ziels konstruiert wird, statt im Nachhinein dagegen neu interpretiert zu werden
Dasselbe Nummerierungsproblem, ein Font-Index nach dem anderen
Nicht jede arbeitsmappenlokale Zahl innerhalb eines Diagramms oder einer Rich-Text-Zelle ist eine Formel, und ein Font-Index ist dasselbe Problem im Miniaturformat. Rich-Text-Runs, zusammen mit zwei weiteren Diagramm-Datensatztypen, die eine Beschriftung oder Achsenschrift tragen, speichern eine Font-Referenz als rohen Ganzzahl-Index in die eigene Font-Tabelle der besitzenden Arbeitsmappe, und dieser Index bedeutet in der Tabelle einer anderen Arbeitsmappe nichts — er könnte dort genauso gut auf eine völlig andere Schriftart, Größe oder Farbe zeigen. HotXLS löst das nach Wert statt nach Zahl: Es schlägt die tatsächlichen Font-Attribute an diesem Index in der Quelltabelle nach, findet oder erzeugt einen passenden Eintrag in der Font-Tabelle des Ziels und schreibt den gespeicherten Index um, damit er auf diesen neuen Slot zeigt. Eine Formateigenheit macht das Nachschlagen selbst fummelig — der dateibasierte Index überspringt Slot 4, eine Nummerierungslücke, die [MS-XLS] Abschnitt 2.5.339 dokumentiert, sodass der Code den Index vor dem Vergleichen der Fonts um eins nach unten und vor dem Schreiben des Ergebnisses wieder um eins nach oben verschieben muss
// The file-numbered font index skips slot 4 (MS-XLS section 2.5.339);
// shift into the in-memory slot, migrate the font by value if the
// destination differs, then shift back before writing the result
if Ifnt >= 5 then
Dec(Ifnt);
if DestFonts.Key[Ifnt] <> SourceFonts.Key[Ifnt] then
Ifnt := DestFonts.SetKey(0, SourceFonts.Key[Ifnt]);
if Ifnt >= 4 then
Inc(Ifnt);Was passiert mit einer Formel, die bereits außerhalb der Arbeitsmappe verweist?
Eine Formel, die schon vor Ihrem Aufruf von AddCopy in eine dritte Arbeitsmappe hineinreicht, ist der eine Fall, den der Text-Rundgang nicht tragen kann, weil HotXLSs eigener Formel-zu-Text-Dekompiler absichtlich keinen [Book]Sheet!-Klammertext für eine externe Referenz synthetisiert, und der Compiler am anderen Ende diese Syntax auch nicht als Eingabe akzeptiert — sodass dieser eine Fall über einen zweiten Mechanismus läuft, der Text überhaupt nie anfasst. Wenn die oben beschriebene Namensmigrations-Reparatur eine Zelle immer noch als String zurücklässt und die Quell-Arbeitsmappe einen echten Dateinamen hat, wechselt AddCopy die Strategie: Es kopiert den kompilierten Formelbaum selbst tief, statt seinen Text, und übergibt die Kopie dann an einen dedizierten Neuverknüpfungs-Durchlauf, RebindExternRefsInTree, der ihn Knoten für Knoten durchläuft. Für jede Bereichsreferenz, die er findet, löst dieser Durchlauf den EXTERNSHEET-Eintrag der Quelle zurück in ein Paar Blattnamen auf und registriert, oder verwendet wieder, einen gleichwertigen Eintrag in den eigenen Tabellen für externe Referenzen des Ziels, und erzeugt einen brandneuen Link zu einer externen Arbeitsmappe, falls das Ziel diese Quelldatei noch nie referenziert hat
Hier ist das arbeitsmappenlokale Nummerierungsproblem am wörtlichsten, denn ein externer Referenz-Token bündelt drei separate Koordinaten in ein einziges Feld, und jede davon ist privat zur Arbeitsmappe, die sie geschrieben hat: welche externe Arbeitsmappe, ein Slot in der eigenen Liste externer Bücher des Ziels, vergeben in welcher Reihenfolge auch immer diese Arbeitsmappe sie registriert hat; welches Blatt innerhalb der eigenen Blattliste dieser externen Arbeitsmappe, gespeichert als 1-basierter Index, spezifisch begrenzt auf das externe Buch, eine völlig andere Nummerierungsdomäne als die eigenen internen Blatt-IDs des Ziels; und der Zellbereich selbst, schlichte Zeilen- und Spaltenkoordinaten, die keine Übersetzung brauchen, weil sie nie arbeitsmappenrelativ waren. Bekommt man eines der ersten beiden falsch, öffnet Excel die Datei trotzdem, zeigt trotzdem eine Formel, und wertet sie ohne Beanstandung gegen die falschen externen Zellen aus. Eine Knotenart besiegt selbst diese Neuverknüpfung auf Baumebene: eine Referenz zu einem definierten Namen, ein Index in die eigene private Namenstabelle der Arbeitsmappe genau so, wie ein Blattindex privat zu seiner eigenen EXTERNSHEET ist, ohne gleichwertige Reparatur auf Baumebene verfügbar — in dem Moment, in dem der Neuverknüpfungs-Durchlauf irgendwo im Baum auf eine Namensreferenz trifft, gibt er die gesamte Formel auf, statt eine teilweise korrekte zu schreiben. Selbst wenn die Neuverknüpfung gelingt, zeigt die Zielzelle keine frisch neu berechnete Zahl; sie zeigt den Wert, den die Quellzelle zum Kopierzeitpunkt bereits hatte, gehalten in einem zwischengespeicherten Slot, genauso wie Excel selbst den zuletzt bekannten Wert jeder externen Referenz zwischenspeichert, bis man die Links ausdrücklich aktualisiert, was der richtige Standard ist, denn eine Neuberechnung über einen lebenden Link in eine andere Datei ist genau die Art von Operation, die man einmal, absichtlich, auslösen möchte, nicht bei jedem Öffnen
Was dieses Design kostet
AddCopys Dekompilier-und-Rekompilier-Maschinerie ist nicht kostenlos, und die Kosten sollte man einplanen, bevor man einen großen Konsolidierungs-Job skriptet, nicht danach. Das Kopieren eines Blatts innerhalb derselben Arbeitsmappe nimmt den billigen Pfad, eine schlichte Duplizierung des kompilierten Baums im Speicher, da jeder Index darin bereits in der Arbeitsmappe gültig ist, in der er bleibt; ein arbeitsmappenübergreifendes Kopieren bezahlt stattdessen für ein echtes Parsen bei jeder Formelzelle, dekompilieren zu Text und diesen Text dann wieder von Grund auf kompilieren, und während der Unterschied auf einem Blatt mit ein paar Dutzend Formeln nicht messenswert ist, sollte eine Quell-Arbeitsmappe mit Zehntausenden Formelzellen, kopiert als ein Blatt unter Dutzenden in einem Batch-Job, erwarten, dass die Rekompilierung die Laufzeit dominiert, nicht die Datei-E/A drumherum. Die Kopierreihenfolge zählt aus einem zweiten Grund über die Geschwindigkeit hinaus: Eine Formel, die auf ein Blatt verweist, das AddCopy in diesem Batch noch nicht erreicht hat, scheitert bei der Rekompilierung aus demselben Grund wie eine Formel, die auf ein tatsächlich nicht existierendes Blatt verweist, sodass ein Job, der Blatt B kopiert, bevor die davon abhängige Blatt-A-Formel kopiert wird, diese Formel genau wie oben beschrieben degradieren sieht, String-Text oder ein External-Link-Fallback, der genau zurück auf die Quelldatei zeigt, aus der er gerade kam. Und weil jede Quell-Arbeitsmappe in einem Konsolidierungs-Batch meist unabhängig verfasst wurde, lohnt es sich, ausdrücklich auf den einen Fehlermodus zu testen, vor dem keine einzelne Quelldatei je hätte warnen können — fünf Filial-Arbeitsmappen, die jeweils die Zahlen einer Partner-Filiale summieren, können sich innerhalb der Zusammenfassungs-Arbeitsmappe zu einer echten zirkulären Referenz kombinieren, ohne dass irgendeine einzelne Quelldatei je eine enthielt, ein Zyklus, der erst existiert, sobald jedes Blatt am selben Ort gelandet ist und die Neuberechnung über die kombinierte Menge läuft
Arbeitsmappenübergreifendes Arbeitsblattkopieren ist Standardverhalten von AddCopy in der HotXLS Delphi Excel-Komponente für Delphi und C++Builder; die Produktseite trägt die vollständige Arbeitsblatt- und Arbeitsmappen-API-Referenz, einschließlich des hier beschriebenen Diagramm-, Rich-Text- und External-Reference-Verhaltens