Dupliquer une feuille XLSX dans Delphi avec HotXLS

Vous avez construit une feuille exactement comme il faut. Le bandeau d'en-tête est fusionné, les largeurs de colonnes correspondent aux données, les deux premières lignes sont figées, la zone d'impression et les marges sont réglées pour une exportation A4 nette, et l'onglet est coloré pour que l'équipe financière le retrouve. Maintenant, le rapport en réclame douze, une par région, chacune partant de la même mise en page. Recréer cette feuille en code douze fois, c'est laisser s'installer de légers écarts: la région 7 se retrouve avec une colonne un peu plus étroite, la région 11 perd le figé des volets, et personne ne s'en rend compte avant que le PDF n'arrive sur le bureau d'un responsable. Ce que vous voulez vraiment, c'est la version programmatique du clic droit d'Excel, Déplacer ou copier, Créer une copie: prendre la feuille terminée et en produire des doublons indépendants

Le moteur XLSX de HotXLS, une bibliothèque Delphi et C++Builder native qui lit et écrit les fichiers Excel sans automatiser Excel lui-même, savait déjà déplacer des feuilles, supprimer des feuilles et copier des plages de cellules d'une feuille à l'autre. Ce qu'il ne pouvait pas faire avant la v2.91.0, c'était cloner une feuille entière en un seul appel. Cette version ajoute deux points d'entrée: TXLSXWorksheet.CopyFrom, qui copie l'état au niveau de la feuille d'une feuille source vers une autre, et TXLSXSheets.Duplicate, qui ajoute une nouvelle feuille et exécute CopyFrom pour vous. La partie intéressante n'est pas qu'il copie des choses. C'est la ligne délibérée entre ce qui est copié en profondeur et ce qui ne l'est pas, et la raison pour laquelle cette ligne se trouve là

Un seul appel pour cloner une feuille terminée

L'opération de haut niveau est Duplicate. Donnez-lui l'indice basé sur 1 de la feuille source et elle renvoie une nouvelle feuille de calcul qui reproduit la mise en page et les données de l'original. La convention d'indexation correspond à Items[] sur le côté XLSX, donc la feuille un est à l'indice 1, pas 0; passez un indice hors plage et vous obtenez nil au lieu d'une exception, selon le même contrat d'échec que le reste de la collection de feuilles XLSX utilise

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);          // fige la première ligne + la première colonne
    Template.TabColorIsAuto := False;
    Template.TabColor := $FF1F4E79;

    // Clone avec un nom explicite...
    Copy := Book.Sheets.Duplicate(1, 'Region-North');
    // ...ou laisse choisir le nom par défaut de style Excel.
    Copy := Book.Sheets.Duplicate(1);    // -> "Template (2)"

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

Deux points de cet extrait méritent qu'on s'y attarde. D'abord, FreezePanes prend ses arguments ligne d'abord, FreezePanes(ARow, ACol), donc il s'aligne sur l'indexation de Cells[Row, Col] ; la copie hérite exactement du même découpage de figement. Deuxièmement, la méthode s'appelle Duplicate et non Copy, et ce n'est pas une question de style. Copy est une routine standard dans l'unité System, utilisée constamment pour les chaînes et les tableaux dynamiques. Une méthode appelée Copy sur une classe l'écraserait à l'intérieur du corps des méthodes et créerait exactement le genre d'ambiguïté de résolution qui vous rattrape six mois plus tard. Duplicate contourne entièrement le problème et se lit correctement à l'appel

Le nom par défaut suit la règle d'Excel

Quand vous appelez la surcharge à un argument ou que vous passez une chaîne de nom vide, la nouvelle feuille est nommée d'après la source avec un (2)suffixe, et ce suffixe augmente jusqu'à ce que le nom soit unique. Dupliquez la Template feuille une fois et vous obtenez Template (2); dupliquez-la encore et vous obtenez Template (3), parce que Template (2) est déjà pris. Cela reflète les noms qu'Excel génère avec sa propre commande Créer une copie, de sorte qu'un classeur produit par votre code ressemble à ce qu'un utilisateur attendrait d'une duplication manuelle. Le contrôle d'unicité s'effectue sur la collection de feuilles active, ce qui signifie qu'il saute aussi les noms que vous avez créés manuellement, pas seulement ceux issus de duplications précédentes

Si vous générez une feuille par région ou par mois, appuyez-vous plutôt sur la surcharge avec nom explicite. Un schéma prévisible Region-North, Region-South est plus facile à adresser plus tard qu'une suite de suffixes (2), (3) et il garde lisibles vos noms définis et vos formules inter-feuilles

Ce que CopyFrom copie en profondeur

Sous le capot, Duplicate ajoute la feuille puis appelle CopyFrom(ASource), que vous pouvez aussi appeler directement quand vous voulez cloner vers une feuille que vous avez déjà créée. CopyFrom se protège d'emblée contre les deux cas dégénérés: copier depuis nil, ou copier une feuille sur elle-même, les deux renvoient immédiatement sans rien faire. Tout ce qui suit constitue la copie elle-même, et elle est délibérément large

Les données de cellules viennent en premier. CopyFrom demande à la source sa UsedRange, la boîte englobante étroite des cellules remplies et des régions fusionnées, et réutilise le mécanisme existant CopyRangeTo pour transporter chaque valeur, formule et index de style par cellule vers la cible en commençant à A1. Au-dessus des cellules, elle rejoue toute la couche d'état au niveau de la feuille qui donne à un modèle son aspect fini:

• Les plages fusionnées, recréées par coordonnées de sorte que le bandeau couvre le même rectangle
• Les largeurs de colonnes et hauteurs de lignes, ainsi que les listes de masquage, de réduction et de niveau de plan, copiées telles quelles pour que les lignes et colonnes non par défaut s'alignent exactement
• Les volets figés et l'état de la vue: niveau de zoom, affichage du quadrillage et des valeurs nulles, direction droite-à-gauche, et type de vue
• L'état de protection avec ses options de permission par action, de sorte qu'un modèle verrouillé reste verrouillé de la même façon
• Tout le bloc de mise en page: marges, orientation, taille du papier, mise à l'échelle et ajustement à la page, zone d'impression, titres d'impression, en-têtes et pieds de page, ainsi que les indicateurs d'impression du quadrillage et des titres
• La plage AutoFilter, la couleur de l'onglet, et la visibilité de la feuille

Le résultat est une feuille qui s'imprime, se filtre et se présente de façon identique à sa source. Et parce que les cellules, les fusions et les listes de dimensions sont physiquement recréées sur la nouvelle feuille plutôt qu'aliasées, le doublon est totalement indépendant. Écrivez 999 dans une cellule de la copie et la source conserve sa valeur d'origine; cette indépendance est la propriété la plus importante d'un clone destiné à des rapports régionaux parallèles, et la démo SheetCopy livrée l'affirme explicitement

Ce qu’il laisse superficiel, et pourquoi

Maintenant, la partie honnête. Les graphiques, images intégrées, tables XLSX, validations de données et règles de mise en forme conditionnelle ne sont pas copiés. C'est une limite documentée et délibérée, pas un oubli, et il vaut la peine d'en comprendre la logique pour l'anticiper plutôt que d'être pris au dépourvu

Chacune de ces collections porte une identité et des références qui ne survivent pas à une copie de champs naïve. Un graphique pointe vers une plage de données source et possède une relation de dessin dans le package OOXML; cloner l'objet sans remapper la relation et les références de séries produit un graphique qui se rend contre les mauvaises données, ou un package qu'Excel signale comme nécessitant une réparation. Une table a un nom qui doit être unique dans le classeur, une ligne d'en-tête liée à des colonnes spécifiques, et sa propre relation auto-générée. Les mises en forme conditionnelles et les validations de données s'attachent à des plages de coordonnées et, dans le cas des validations, peuvent référencer d'autres plages par formule. Copier correctement l'un de ces éléments en profondeur signifie réécrire des références et créer de nouvelles identités, ce qui est un travail réel avec de vrais modes d'échec. Le faire à moitié, en copiant l'objet mais pas ses références, est pire que ne pas copier du tout: cela produit un fichier qui s'ouvre avec une invite de réparation et perd silencieusement du contenu. Le moteur copie donc ce qu'il peut copier proprement et laisse à l'appelant, qui sait vers quoi la cible doit pointer, les collections porteuses de références

En pratique, cela signifie que le flux de travail pour un modèle plus riche est: dupliquer la feuille pour obtenir les cellules, la mise en page et la configuration d'impression, puis reconstruire le graphique, la table, les validations ou les mises en forme conditionnelles sur la copie avec la même API que celle utilisée pour les créer la première fois. Comme vous les recréez par rapport aux propres plages du doublon, les références sont correctes par construction. Pour un graphique qui lit A1:C10, ajoutez un nouveau graphique sur la copie pointant vers le A1:C10 de la copie; pour un AutoFilter que vous voulez actif, notez que la plage du filtre est bien reportée, donc vous n'avez qu'à réappliquer les critères de colonne. Les règles de mise en forme conditionnelle et de validation de données, vous les rajouteriez via les mêmes appels décrits dans l'article sur les cellules fusionnées et la mise en page des modèles de rapport, qui parcourt la table de fusion et le modèle de plage dont hérite la copie

Où la duplication s’insère dans une chaîne de reporting

La duplication de feuille est le complément naturel de la génération pilotée par jetons. L'approche ancrée sur des jetons dans le guide de génération de rapport pilotée par modèle en Delphi résout le problème d'écrire des données dans une mise en page que d'autres personnes éditent; la duplication résout le problème d'avoir besoin de cette mise en page plusieurs fois dans un même classeur. Combinez les deux et le schéma est propre: gardez une feuille Template immaculée avec ses jetons, ses fusions et sa configuration d'impression, puis pour chaque région ou période appelez Duplicate, remplissez les jetons du clone avec cette tranche de données, et passez à la suivante. Le modèle immaculé n'est jamais modifié, il reste donc une source fiable pour le clone suivant, et chaque feuille de sortie part d'une mise en page identique au bit près

Une remarque sur l'ordre des opérations évite toute une catégorie de confusion. Dupliquez la feuille avant d'y verser des données, pas après. Un modèle doit contenir de la structure et de la mise en forme, pas les chiffres du trimestre dernier, et cloner une feuille stylée vide signifie que chaque doublon démarre propre. Si vous dupliquez une feuille qui porte déjà des données, ces données suivent, car CopyFrom copie fidèlement la plage utilisée; c'est parfois ce que vous voulez, mais pour un rapport de diffusion, ce n'est généralement pas le cas

Une habitude de vérification rapide

Parce que la séparation entre copie profonde et copie superficielle est invisible tant que vous ne la cherchez pas, intégrez un contrôle en cinq lignes dans le travail plutôt que de faire confiance au fait que tout est bien passé. Après la duplication, relisez les signaux structurels que la copie est censée hériter et vérifiez qu'ils correspondent à la source

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)]));
// Prouve l'indépendance: modifie la copie, confirme que la source est intacte.
Copy.Cells[2, 2].Value := 999;
// Template.Cells[2, 2].Value reste ce qu'elle était.

Le nombre de fusions, une largeur de colonne, la ligne de figement et l'indicateur de couleur de l'onglet vous disent que la couche qui est copiée a bien été copiée. Par ailleurs, dans toute feuille qui portait un graphique, une table, des validations ou des mises en forme conditionnelles, traitez cela comme une liste à reconstruire sur la copie: leur absence est voulue, et la correction tient à quelques appels, pas à un rapport de bug. Ce modèle mental, en profondeur là où c'est sûr et en surface là où des références casseraient, résume entièrement la bonne façon d'utiliser cette fonctionnalité

La duplication de feuille et la CopyFrom copie de l'état de feuille décrite ici sont livrées en v2.91.0 du composant feuille de calcul Delphi natif HotXLS Delphi spreadsheet component, avec un exemple exécutable SheetCopy qui illustre le cycle de clonage et de mutation de bout en bout