Ein XLSX-Arbeitsblatt in Delphi mit HotXLS duplizieren

Sie haben ein Arbeitsblatt genau richtig aufgebaut. Der Kopfzeilenbereich ist verbunden, die Spaltenbreiten passen zu den Daten, die ersten beiden Zeilen sind fixiert, der Druckbereich und die Ränder sind für einen sauberen A4-Export eingestellt und die Registerkarte ist farbig markiert, damit die Finanzabteilung sie leicht finden kann. Nun benötigt der Bericht zwölf dieser Blätter, eines pro Region, die alle auf demselben Layout basieren. Das zwölffache Neuerstellen dieses Arbeitsblatts im Code führt oft zu subtilen Abweichungen: Region 7 erhält eine um einen Punkt schmalere Spalte, Region 11 verliert die Fixierung, und niemand bemerkt es, bis die PDF-Datei auf dem Schreibtisch des Managers landet. Was Sie eigentlich wollen, ist die programmgesteuerte Version des Excel-Rechtsklicks „Verschieben oder kopieren“, „Kopie erstellen“: Nehmen Sie das fertige Blatt und erzeugen Sie unabhängige Duplikate

Die XLSX-Engine in HotXLS, einer nativen Delphi- und C++Builder-Bibliothek, die Excel-Dateien ohne Excel-Automatisierung liest und schreibt, konnte bereits Blätter verschieben, Blätter löschen und Zellbereiche über Blätter hinweg kopieren. Was sie bis zur Version v2.91.0 nicht tun konnte, war das Klonen eines gesamten Arbeitsblatts in einem einzigen Aufruf. 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 daran ist nicht, dass es Dinge kopiert. Es ist die bewusste Grenze, die zwischen dem gezogen wird, was tiefkopiert wird und was nicht, und warum diese Grenze genau dort liegt, wo sie ist

Ein Aufruf zum Klonen eines fertigen Blatts

Die übergeordnete Operation ist Duplicate. Übergeben Sie ihr den 1-basierten Index des Quellblatts, und sie gibt ein brandneues Arbeitsblatt zurück, das das Layout und die Daten des Originals widerspiegelt. Die Indexkonvention entspricht Items[] auf der XLSX-Seite, sodass das erste Blatt Index 1 und nicht 0 hat. Wenn Sie einen Index außerhalb des Bereichs übergeben, erhalten Sie nil anstelle einer Ausnahme – derselbe Fehler-Kontrakt, den auch der Rest der XLSX-Blättersammlung 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);          // freeze top row + first column
    Template.TabColorIsAuto := False;
    Template.TabColor := $FF1F4E79;

    // Clone with an explicit name...
    Copy := Book.Sheets.Duplicate(1, 'Region-North');
    // ...or let it pick the Excel-style default name.
    Copy := Book.Sheets.Duplicate(1);    // -> "Template (2)"

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

Zwei Dinge in diesem Snippet verdienen Beachtung. Erstens erwartet FreezePanes seine Argumente zeilenweise, FreezePanes(ARow, ACol), sodass es sich an der Indizierung von Cells[Row, Col] ausrichtet; das Duplikat erbt die exakte Fixierung. Zweitens heißt die Methode Duplicate und nicht etwa Copy, was keine reine Stilpräferenz ist. 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 diese innerhalb von Methodenkörpern überschatten und genau die Art von Namenskonflikten erzeugen, die einen sechs Monate später einholen. Duplicate umgeht das gesamte Problem und lässt sich am Aufrufort verständlich lesen

Der Standardname folgt der Excel-eigenen Regel

Wenn Sie die Überladung mit einem Argument aufrufen oder einen leeren Namensstring übergeben, wird das neue Blatt nach der Quelle mit dem Suffix (2) benannt, und das Suffix erhöht sich, bis der Name eindeutig ist. Wenn Sie das Blatt Template einmal duplizieren, erhalten Sie Template (2); duplizieren Sie es erneut, erhalten Sie Template (3), da Template (2) bereits belegt ist. Dies spiegelt die Namen wider, die Excel bei seinem eigenen Befehl „Kopie erstellen“ erzeugt, sodass eine von Ihrem Code erstellte Arbeitsmappe so aussieht, wie es ein Benutzer von einer manuell duplizierten Arbeitsmappe erwarten würde. Die Eindeutigkeitsprüfung läuft gegen die aktive Blättersammlung, was bedeutet, dass sie auch manuell erstellte Namen überspringt und nicht nur Namen aus früheren Duplizierungen

Wenn Sie ein Blatt pro Region oder Monat erstellen, nutzen Sie stattdessen die Überladung mit explizitem Namen. Ein vorhersehbares Schema wie Region-North, Region-South ist später leichter anzusprechen als eine Reihe von Suffixen wie (2), (3), und es hält Ihre definierten Namen und blattübergreifenden Formeln lesbar

Was CopyFrom per Deep-Copy kopiert

Unter der Haube fügt Duplicate das Blatt hinzu und ruft CopyFrom(ASource) auf, was Sie auch direkt aufrufen können, wenn Sie auf ein bereits erstelltes Blatt klonen möchten. CopyFrom prüft vorab zwei Sonderfälle: Das Kopieren von nil oder das Kopieren eines Blatts auf sich selbst brechen sofort ab und tun nichts. Alles danach ist der eigentliche Kopiervorgang, und dieser ist bewusst breit gefächert

Die Zelldaten kommen zuerst. CopyFrom fragt die Quelle nach ihrer UsedRange ab, dem engen Begrenzungsrahmen der belegten Zellen und verbundenen Bereiche, und nutzt den vorhandenen Mechanismus CopyRangeTo, um jeden Wert, jede Formel und jeden zellspezifischen Stilindex ab A1 in das Ziel zu übertragen. Zusätzlich zu den Zellen überträgt die Funktion den gesamten Zustand auf Blattebene, der eine Vorlage fertig gestaltet aussehen lässt:

• Verbundene Bereiche, die nach Koordinaten neu erstellt werden, sodass das Banner dieselbe Fläche überspannt
• Spaltenbreiten und Zeilenhöhen sowie die Listen für ausgeblendete, reduzierte und Gliederungsebenen, die eins zu eins kopiert werden, damit Zeilen und Spalten mit abweichenden Werten exakt übereinstimmen
• Fixierte Fensterbereiche und der Ansichtszustand: Zoomstufe, Anzeige von Gitternetzlinien und Nullwerten, Rechts-nach-Links-Ausrichtung und der Ansichtstyp
• Schutzstatus mit seinen aktionsbezogenen Berechtigungsoptionen, sodass eine gesperrte Vorlage auf dieselbe Weise gesperrt bleibt
• Der gesamte Seiteneinrichtungs-Block: Ränder, Ausrichtung, Papierformat, Skalierung und Seite-anpassen, Druckbereich, Drucktitel, Kopf- und Fußzeilen sowie die Flags für Druckgitterlinien und Drucküberschriften
• Der AutoFilter-Bereich, die Registerkartenfarbe und die Sichtbarkeit des Blatts

Das Ergebnis ist ein Blatt, das sich identisch zu seiner Quelle drucken, filtern und präsentieren lässt. Und da die Zellen, Verbindungen und Dimensionslisten physisch auf dem neuen Blatt neu erstellt und nicht bloß referenziert werden, ist das Duplikat völlig unabhängig. Wenn Sie 999 in eine Zelle der Kopie schreiben, behält die Quelle ihren ursprünglichen Wert. Diese Unabhängigkeit ist die wichtigste Eigenschaft eines Klons, der für parallele Regionalberichte gedacht ist, und die mitgelieferte SheetCopy-Demo prüft dies explizit

Was flach kopiert bleibt und warum

Nun der ehrliche Teil. Diagramme, eingebettete Bilder, XLSX-Tabellen, Datenvalidierungen und Regeln zur bedingten Formatierung werden nicht kopiert. Dies ist eine dokumentierte, bewusste Einschränkung, kein Versehen, und es ist wichtig, die Gründe dafür zu verstehen, damit Sie darum herumplanen können, anstatt davon überrascht zu werden

Jede dieser Sammlungen enthält Identitäten und Referenzen, die eine einfache Feldkopie nicht überstehen. Ein Diagramm verweist auf einen Quelldatenbereich und besitzt eine Zeichnungsbeziehung im OOXML-Paket. Wenn Sie das Objekt kopieren, ohne die Beziehung und die Serienreferenzen neu zuzuordnen, erhalten Sie ein Diagramm, das sich auf die falschen Daten bezieht, oder ein Paket, das Excel als reparaturbedürftig meldet. Eine Tabelle hat einen Namen, der innerhalb der Arbeitsmappe eindeutig sein muss, eine Kopfzeile, die an bestimmte Spalten gebunden ist, und eine eigene automatisch generierte Beziehung. Bedingte Formate und Datenvalidierungen sind an Koordinatenbereiche gebunden und können im Fall von Validierungen über Formeln auf andere Bereiche verweisen. Das korrekte Tiefkopieren all dieser Elemente erfordert das Umschreiben von Referenzen und das Erzeugen neuer Identitäten, was eine komplexe Aufgabe mit echten Fehlerquellen ist. Es halbherzig zu tun, indem man das Objekt, aber nicht seine Referenzen kopiert, ist schlimmer als gar nicht zu kopieren: Es führt zu einer Datei, die sich mit einer Reparaturaufforderung öffnet und Inhalte stillschweigend verwirft. Daher kopiert die Engine die Dinge, die sie sauber kopieren kann, und überlässt die referenzabhängigen Sammlungen dem Aufrufer, der weiß, worauf das Ziel verweisen soll

In der Praxis bedeutet dies, dass der Arbeitsablauf für eine komplexere Vorlage wie folgt aussieht: Duplizieren Sie das Blatt, um die Zellen, das Layout und die Seiteneinrichtung zu erhalten, und erstellen Sie dann das Diagramm, die Tabelle, die Validierungen oder die bedingten Formate auf der Kopie mit derselben API neu, mit der Sie sie beim ersten Mal erstellt haben. Da Sie diese für die eigenen Bereiche des Duplikats neu erstellen, sind die Referenzen konstruktionsbedingt korrekt. Für ein Diagramm, das A1:C10 liest, fügen Sie ein neues Diagramm auf der Kopie hinzu, das auf A1:C10 der Kopie verweist. Für einen AutoFilter, den Sie aktiv haben möchten, beachten Sie, dass der Filter-Bereich übernommen wird, sodass Sie nur die Spaltenkriterien erneut anwenden müssen. Die Regeln für bedingte Formatierung und Datenvalidierung fügen Sie über dieselben Aufrufe wieder hinzu, die in dem Artikel über verbundene Zellen und Berichtsvorlagen-Layout beschrieben sind, der die Verbindungstabelle und das Bereichsmodell durchläuft, das die Kopie erbt

Wo die Duplizierung in eine Berichterstellungs-Pipeline passt

Die Duplizierung von Arbeitsblättern ist die natürliche Ergänzung zur platzhaltergesteuerten Generierung. Der tokenbasierte Ansatz in dem Leitfaden zur vorlagenbasierten Berichterstellung in Delphi löst das Problem, Daten in ein Layout zu schreiben, das andere Personen bearbeiten; die Duplizierung löst das Problem, dieses Layout mehrfach in einer Arbeitsmappe zu benötigen. Wenn Sie beides kombinieren, ist das Muster klar: Behalten Sie ein unberührtes Template-Blatt mit seinen Token, Verbindungen und Seiteneinstellungen. Rufen Sie dann für jede Region oder Periode Duplicate auf, füllen Sie die Token des Klons mit diesem Datenteil und fahren Sie fort. Die unberührte Vorlage wird niemals verändert, sodass sie eine zuverlässige Quelle für den nächsten Klon bleibt, und jedes Ausgabeblatt startet mit einem Byte-für-Byte identischen Layout

Ein Hinweis zur Reihenfolge erspart Ihnen eine Menge Verwirrung. Duplizieren Sie das Blatt, bevor Sie Daten hineinschreiben, nicht danach. Eine Vorlage sollte Struktur und Formatierung enthalten, nicht die Zahlen des letzten Quartals. Das Klonen eines leeren, gestylten Blatts sorgt dafür, dass jedes Duplikat sauber startet. Wenn Sie ein Blatt duplizieren, das bereits Daten enthält, werden diese mitkopiert, da CopyFrom die UsedRange getreu kopiert. Das ist zwar gelegentlich gewollt, für einen verteilten Bericht jedoch meist nicht

Eine schnelle Gewohnheit zur Überprüfung

Da die Unterscheidung zwischen Deep-Copy und Shallow-Copy unsichtbar ist, bis man danach sucht, sollten Sie eine kurze Überprüfung in Ihre Aufgabe einbauen, anstatt darauf zu vertrauen, dass alles übertragen wurde. Lesen Sie nach dem Duplizieren die strukturellen Signale aus, die der Klon erben soll, und stellen Sie sicher, 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)]));
// Prove independence: mutate the copy, confirm the source is untouched.
Copy.Cells[2, 2].Value := 999;
// Template.Cells[2, 2].Value is still whatever it was.

Die Anzahl verbundener Zellen, eine Spaltenbreite, die fixierte Zeile und das Flag für die Registerkartenfarbe zeigen Ihnen, ob die kopierte Ebene tatsächlich übertragen wurde. Behandeln Sie Diagramme, Tabellen, Validierungen oder bedingte Formate auf jedem Blatt als Liste der auf der Kopie neu zu erstellenden Elemente: Ihr Fehlen ist beabsichtigt, und die Lösung besteht aus wenigen Aufrufen, nicht in einem Fehlerbericht. Dieses mentale Modell – tiefkopieren, wo es sicher ist, und flachkopieren, wo Referenzen brechen würden – beschreibt genau, wie Sie dieses Feature optimal nutzen

Die Duplizierung von Arbeitsblättern und die hier beschriebene Eigenschaftskopie CopyFrom werden in Version v2.91.0 der nativen HotXLS Delphi spreadsheet component ausgeliefert, zusammen mit einem ausführbaren SheetCopy-Beispiel, das den Klon- und Änderungsprozess vollständig demonstriert