XLSX-Arbeitsblatt in Delphi mit HotXLS duplizieren

Sie haben ein Blatt genau richtig gebaut. Das Kopfband ist verbunden, die Spaltenbreiten passen zu den Daten, die obersten zwei Zeilen sind fixiert, Druckbereich und Ränder sind für einen sauberen A4-Export gesetzt, und der Reiter ist eingefärbt, damit die Finanzabteilung ihn findet. Jetzt braucht der Bericht zwölf davon, eines pro Region, jedes ausgehend vom selben Layout. Dieses Blatt zwölfmal im Code neu aufzubauen ist der Weg, auf dem sich subtile Abweichungen einschleichen: Region 7 bekommt eine um einen Punkt schmalere Spalte, Region 11 verliert die Fixierung, und niemand bemerkt es, bis das PDF auf dem Schreibtisch eines Managers landet. Was Sie eigentlich wollen, ist die programmatische Version von Excels Rechtsklick, Verschieben oder kopieren, Kopie erstellen: das fertige Blatt nehmen und unabhängige Duplikate davon ausstanzen

Die XLSX-Engine in HotXLS, einer nativen Delphi- und C++Builder-Bibliothek, die Excel-Dateien liest und schreibt, ohne Excel selbst zu automatisieren, konnte bereits Blätter verschieben, Blätter löschen und Zellbereiche über Blätter hinweg kopieren. Was sie bis v2.91.0 nicht konnte, war ein ganzes Arbeitsblatt in einem Aufruf zu klonen. Diese Version fügt zwei Einstiegspunkte hinzu: TXLSXWorksheet.CopyFrom, das den Zustand auf Blattebene von einem Arbeitsblatt auf ein anderes kopiert, und TXLSXSheets.Duplicate, das ein neues Blatt hinzufügt und CopyFrom für Sie ausführt. Das Interessante ist nicht, dass es Dinge kopiert. Es ist die bewusst gezogene Linie zwischen dem, was tief kopiert wird, und dem, was nicht, und warum diese Linie dort liegt, wo sie liegt

Ein Aufruf, um ein fertiges Blatt zu klonen

Die übergeordnete Operation ist Duplicate. Übergeben Sie ihr den 1-basierten Index des Quellblatts, und sie gibt ein brandneues Arbeitsblatt zurück, das Layout und Daten des Originals spiegelt. Die Indexkonvention entspricht Items[] auf der XLSX-Seite, sodass Blatt eins den Index 1 hat, nicht 0; übergeben Sie einen Index außerhalb des Bereichs, erhalten Sie nil statt einer Exception, derselbe Fehlervertrag, den der Rest der XLSX-Blattsammlung verwendet

var
  Book: TXLSXWorkbook;
  Template, Copy: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Template := Book.Sheets.Add('Template');
    Template.Cells[1, 1].Value := 'Quarterly Statement';
    Template.Range['A1:C1'].Merge;
    Template.ColWidth[1] := 18;
    Template.FreezePanes(2, 1);          // oberste Zeile + erste Spalte fixieren
    Template.TabColorIsAuto := False;
    Template.TabColor := $FF1F4E79;

    // Klonen mit explizitem Namen...
    Copy := Book.Sheets.Duplicate(1, 'Region-North');
    // ...oder den Standardnamen im Excel-Stil wählen lassen.
    Copy := Book.Sheets.Duplicate(1);    // -> "Template (2)"

    Book.SaveAs('regions.xlsx');
  finally
    Book.Free;
  end;
end;

Bei zwei Dingen in diesem Ausschnitt lohnt es sich, innezuhalten. Erstens nimmt FreezePanes seine Argumente zeilenweise zuerst, FreezePanes(ARow, ACol), sodass es zur Cells[Row, Col]-Indizierung passt; das Duplikat erbt genau dieselbe Fixierungsteilung. Zweitens heißt die Methode Duplicate und nicht das naheliegendere Copy, und das ist keine Stilvorliebe. Copy ist eine Standardroutine in der Unit System, die ständig für Strings und dynamische Arrays verwendet wird. Eine Methode namens Copy auf einer Klasse würde sie innerhalb von Methodenrümpfen verdecken und genau die Art von Auflösungsmehrdeutigkeit schaffen, die einen sechs Monate später beißt. Duplicate umgeht das ganze Problem und liest sich an der Aufrufstelle korrekt

Der Standardname folgt Excels eigener Regel

Wenn Sie die Überladung mit einem Argument aufrufen oder eine leere Namenszeichenkette übergeben, wird das neue Blatt nach der Quelle mit dem Suffix (2) benannt, und das Suffix wird hochgezählt, bis der Name eindeutig ist. Duplizieren Sie das Blatt Template einmal, erhalten Sie Template (2); duplizieren Sie es erneut, erhalten Sie Template (3), weil Template (2) bereits vergeben ist. Das spiegelt die Namen wider, die Excel mit seinem eigenen Befehl Kopie erstellen erzeugt, sodass eine von Ihrem Code erzeugte Arbeitsmappe so aussieht, wie ein Benutzer eine von Hand duplizierte erwarten würde. Die Eindeutigkeitsprüfung läuft gegen die lebende Blattsammlung, was bedeutet, dass sie auch Namen überspringt, die Sie manuell erstellt haben, nicht nur solche aus früheren Duplikationen

Wenn Sie ein Blatt pro Region oder pro Monat erzeugen, stützen Sie sich stattdessen auf die Überladung mit explizitem Namen. Ein vorhersehbares Schema Region-North, Region-South lässt sich später leichter adressieren als eine Kette von (2)-, (3)-Suffixen, und es hält Ihre definierten Namen und blattübergreifenden Formeln lesbar

Was CopyFrom tief kopiert

Unter der Haube fügt Duplicate das Blatt hinzu und ruft dann CopyFrom(ASource) auf, das Sie auch direkt aufrufen können, wenn Sie auf ein bereits erstelltes Blatt klonen möchten. CopyFrom sichert sich vorab gegen die zwei degenerierten Fälle ab: Kopieren von nil oder Kopieren eines Blatts auf sich selbst, beides kehrt sofort zurück und tut nichts. Alles danach ist die Kopie selbst, und sie ist bewusst breit angelegt

Die Zelldaten kommen zuerst. CopyFrom fragt die Quelle nach ihrem UsedRange, dem engen Begrenzungsrechteck befüllter Zellen und verbundener Bereiche, und nutzt die vorhandene CopyRangeTo-Maschinerie erneut, um jeden Wert, jede Formel und jeden Stilindex pro Zelle ab A1 in das Ziel zu übertragen. Über den Zellen spielt es die vollständige Ebene des Zustands auf Blattebene nach, die eine Vorlage fertig aussehen lässt:

• Verbundene Bereiche, nach Koordinaten neu erstellt, damit das Banner dasselbe Rechteck überspannt
• Spaltenbreiten und Zeilenhöhen, plus die Listen für ausgeblendete, eingeklappte und Gliederungsebenen, wörtlich kopiert, damit Zeilen und Spalten mit Nicht-Standardwerten exakt ausgerichtet sind
• Fixierte Fenster und der Ansichtszustand: Zoomstufe, Anzeige von Gitternetzlinien und Nullwerten, Rechts-nach-links-Richtung und der Ansichtstyp
• Schutzzustand mit seinen Berechtigungsoptionen pro Aktion, damit eine gesperrte Vorlage auf dieselbe Weise gesperrt bleibt
• Der gesamte Seiteneinrichtungsblock: Ränder, Ausrichtung, Papierformat, Skalierung und Anpassen an Seite, Druckbereich, Drucktitel, Kopf- und Fußzeilen sowie die Flags für Gitternetzlinien- und Überschriftendruck
• Der AutoFilter-Bereich, die Reiterfarbe und die Sichtbarkeit des Blatts

Das Ergebnis ist ein Blatt, das identisch zu seiner Quelle druckt, filtert und präsentiert. Und weil die Zellen, Verbünde und Dimensionslisten auf dem neuen Blatt physisch neu erstellt statt als Alias referenziert werden, ist das Duplikat vollständig unabhängig. Schreiben Sie 999 in eine Zelle der Kopie, und die Quelle behält ihren ursprünglichen Wert; diese Unabhängigkeit ist die wichtigste Eigenschaft eines Klons, der für parallele Regionalberichte gedacht ist, und das mitgelieferte SheetCopy-Demo prüft sie ausdrücklich per Assertion

Was flach bleibt, und warum

Nun der ehrliche Teil. Diagramme, eingebettete Bilder, XLSX-Tabellen, Datenüberprüfungen und Regeln für bedingte Formatierung werden nicht kopiert. Das ist eine dokumentierte, bewusste Grenze, kein Versehen, und es lohnt sich, die Begründung zu verstehen, damit Sie darum herum planen können, statt davon überrascht zu werden

Jede dieser Sammlungen trägt Identität und Referenzen, die eine naive Feldkopie nicht überstehen. Ein Diagramm zeigt auf einen Quelldatenbereich und besitzt eine Zeichnungsbeziehung im OOXML-Paket; das Objekt zu klonen, ohne die Beziehung und die Reihenreferenzen neu zuzuordnen, erzeugt ein Diagramm, das gegen die falschen Daten rendert, oder ein Paket, das Excel als reparaturbedürftig markiert. Eine Tabelle hat einen Namen, der innerhalb der Arbeitsmappe eindeutig sein muss, eine an bestimmte Spalten gebundene Kopfzeile und ihre eigene automatisch erzeugte Beziehung. Bedingte Formatierungen und Datenüberprüfungen hängen an Koordinatenbereichen und können im Fall der Überprüfung per Formel auf andere Bereiche verweisen. Irgendetwas davon korrekt tief zu kopieren bedeutet, Referenzen umzuschreiben und frische Identitäten zu prägen, was echte Arbeit mit echten Fehlermodi ist. Es halb zu tun, indem man das Objekt, aber nicht seine Referenzen kopiert, ist schlimmer als gar nicht zu kopieren: Es ergibt eine Datei, die sich mit einer Reparaturaufforderung öffnet und stillschweigend Inhalt verwirft. Also kopiert die Engine die Dinge, die sie sauber kopieren kann, und überlässt die referenztragenden Sammlungen dem Aufrufer, der weiß, worauf das Ziel zeigen soll

In der Praxis bedeutet das für eine reichhaltigere Vorlage folgenden Arbeitsablauf: Duplizieren Sie das Blatt, um Zellen, Layout und Druckeinrichtung zu erhalten, und bauen Sie dann Diagramm, Tabelle, Überprüfungen oder bedingte Formatierungen auf der Kopie mit derselben API neu auf, mit der Sie sie beim ersten Mal erstellt haben. Weil Sie sie gegen die eigenen Bereiche des Duplikats neu erstellen, kommen die Referenzen konstruktionsbedingt korrekt heraus. Für ein Diagramm, das A1:C10 liest, fügen Sie auf der Kopie ein neues Diagramm hinzu, das auf das A1:C10 der Kopie zeigt; für einen AutoFilter, den Sie aktiv haben möchten, beachten Sie, dass der Filter-Bereich sehr wohl übernommen wird, sodass Sie nur die Spaltenkriterien erneut anwenden. Die Regeln für bedingte Formatierung und Datenüberprüfung würden Sie über dieselben Aufrufe erneut hinzufügen, die in dem Artikel über verbundene Zellen und das Layout von Berichtsvorlagen beschrieben sind, der die Verbundtabelle und das Bereichsmodell durchgeht, die die Kopie erbt

Wo die Duplikation in eine Berichtspipeline passt

Die Arbeitsblattduplikation ist der natürliche Begleiter der platzhaltergesteuerten Generierung. Der Token-verankerte Ansatz in dem Leitfaden zur vorlagengesteuerten Berichtsgenerierung in Delphi löst das Problem, Daten in ein Layout zu schreiben, das andere Leute bearbeiten; die Duplikation löst das Problem, dieses Layout viele Male in einer Arbeitsmappe zu brauchen. Kombinieren Sie beides, und das Muster ist sauber: Behalten Sie ein unberührtes Template-Blatt mit seinen Tokens, Verbünden und seiner Druckeinrichtung, rufen Sie dann für jede Region oder Periode Duplicate auf, füllen Sie die Tokens des Klons mit diesem Datenausschnitt und machen Sie weiter. Die unberührte Vorlage wird nie verändert, bleibt also eine zuverlässige Quelle für den nächsten Klon, und jedes Ausgabeblatt beginnt mit einem Byte für Byte identischen Layout

Ein Hinweis zur Reihenfolge erspart eine ganze Klasse von Verwirrung. Duplizieren Sie das Blatt, bevor Sie Daten hineinschütten, nicht danach. Eine Vorlage sollte Struktur und Formatierung enthalten, nicht die Zahlen des letzten Quartals, und ein leeres formatiertes Blatt zu klonen bedeutet, dass jedes Duplikat sauber beginnt. Wenn Sie ein Blatt duplizieren, das bereits Daten trägt, kommen diese Daten mit, weil CopyFrom den benutzten Bereich getreu kopiert; gelegentlich ist das gewünscht, aber für einen Fan-out-Bericht ist es das gewöhnlich nicht

Eine schnelle Prüfgewohnheit

Weil die Trennung zwischen tiefer und flacher Kopie unsichtbar ist, bis man danach sucht, bauen Sie eine Fünf-Zeilen-Prüfung in den Job ein, statt darauf zu vertrauen, dass alles herübergekommen ist. Lesen Sie nach dem Duplizieren die strukturellen Signale zurück, die der Klon erben soll, und prüfen Sie per Assertion, dass sie mit der Quelle übereinstimmen

Copy := Book.Sheets.Duplicate(1, 'Region-North');
WriteLn(Format('merged=%d  colA=%.1f  freezeRow=%d  tabAuto=%d',
  [Copy.MergedCells.Count, Copy.ColWidth[1],
   Copy.FreezeRow, Integer(Copy.TabColorIsAuto)]));
// Unabhängigkeit beweisen: die Kopie ändern, bestätigen, dass die Quelle unberührt bleibt.
Copy.Cells[2, 2].Value := 999;
// Template.Cells[2, 2].Value ist weiterhin, was es vorher war.

Die Verbundanzahl, eine Spaltenbreite, die Fixierungszeile und das Reiterfarben-Flag sagen Ihnen, dass die Ebene, die tatsächlich kopiert wird, es auch wirklich geschafft hat. Behandeln Sie getrennt davon in jedem Blatt, das ein Diagramm, eine Tabelle, Überprüfungen oder bedingte Formatierungen trug, diese als Neuaufbauliste auf der Kopie: Ihr Fehlen ist beabsichtigt, und die Abhilfe sind ein paar Aufrufe, kein Fehlerbericht. Dieses mentale Modell, tief wo es sicher ist und flach wo Referenzen brechen würden, ist die ganze Geschichte, wie man diese Funktion gut nutzt

Die Arbeitsblattduplikation und die hier beschriebene CopyFrom-Kopie des Blattzustands werden mit v2.91.0 der nativen HotXLS-Tabellenkalkulationskomponente für Delphi ausgeliefert, zusammen mit einem lauffähigen SheetCopy-Beispiel, das den Zyklus aus Klonen und Ändern von Anfang bis Ende durchspielt