Un classeur financier maintenu à la main accumule caches de tableaux croisés, slicers, mises en forme conditionnelles et des années de styles de cellules, mais le travail nocturne n'a qu'à échanger les chiffres d'un onglet de données. Un chargement et un enregistrement complets analysent, reconstruisent et dégonflent chaque partie à nouveau. Réécrire seulement cette partie de feuille de calcul et transporter chaque autre entrée zip intacte saute presque tout ce travail
Ce qui rend cela possible, c'est le format de package lui-même. Un fichier .xlsx est une archive zip de parties XML agencées par les Open Packaging Conventions (ECMA-376 Partie 2), et chaque entrée zip enregistre sa propre méthode de compression, son CRC-32 et sa taille compressée dans l'en-tête local et à nouveau dans le répertoire central. Une entrée peut donc passer d'une archive à une autre sans jamais être dégonflée, à condition que le copieur écrive ces trois valeurs avec les octets bruts. Le cache de tableau croisé n'a pas besoin d'être compris pour être préservé. Il a seulement besoin d'être copié
Pourquoi copier des octets compressés au lieu de réenregistrer le classeur ?
Copier au niveau compressé transforme l'essentiel du patch en une simple copie de flux. Dans HotXLS, TXLSDirectWriter.BeginPatch ouvre le package source en lecture seule et confie à TZipArchive.CopyCompressedFrom chaque entrée que le patch laisse tranquille, qui écrit les octets compressés, le CRC et les tailles de l'entrée source directement dans l'archive cible. Un chemin d'écriture conventionnel paie un dégonflage à l'entrée et une déflation à la sortie, et la déflation est la moitié coûteuse. Sur un classeur dont l'essentiel est un grand cache de tableau croisé plus quelques dizaines d'images intégrées, le temps part dans la feuille que vous écrivez réellement
La copie brute couvre les entrées stockées sans compression (méthode zip 0) et les entrées dégonflées (méthode 8). Une entrée qui utilise une autre méthode de compression est quand même transportée : le writer la lit à travers un flux décompressant et la réécrit, si bien que la partie atterrit dans la cible d'une manière ou d'une autre. Les entrées marqueurs de répertoire ne portent aucun contenu et sont sautées, puisque le writer produit les siennes. Une poignée d'entrées ne sont jamais copiées du tout, parce qu'elles sont régénérées ou éditées : la feuille cible et sa partie _rels, xl/calcChain.xml, xl/_rels/workbook.xml.rels et [Content_Types].xml, plus xl/workbook.xml quand une feuille est insérée
Remplacer une feuille sur place avec BeginPatch
La surcharge à deux arguments BeginPatch(FileName, SheetName) remplace une feuille existante et réécrit le résultat au même chemin. Elle résout le nom via le registre des feuilles dans xl/workbook.xml et la carte de relations qui lie chaque feuille à sa partie, compare les noms sans tenir compte de la casse, et lève une exception contenant « worksheet not found » quand rien ne correspond. De là, la feuille est écrite avec les mêmes appels AddSheet, AddRow et Write* que le writer en flux utilise pour les fichiers neufs
var
W: TXLSDirectWriter;
begin
W := TXLSDirectWriter.Create;
try
W.BeginPatch('monthly-dashboard.xlsx', 'Data'); // remplace sur place
W.AddSheet('Data'); // le nom est informatif en mode patch
W.AddRow(1);
W.WriteString(1, 'Region');
W.WriteString(2, 'Revenue');
W.AddRow(2);
W.WriteString(1, 'North');
W.WriteNumber(2, 184320.55);
W.AddRow(3);
W.WriteString(1, 'South');
W.WriteNumber(2, 97410.20);
W.AddRow(4);
W.WriteString(1, 'Total');
W.WriteFormula(2, 'SUM(B2:B3)'); // pas de = initial
W.Close;
finally
W.Free;
end;
end;
Deux détails de ce listing se lisent mal facilement. Le nom passé à AddSheet ne renomme rien : l'onglet garde le nom, le sheetId et la position qu'il a déjà dans xl/workbook.xml, donc AddSheet('Totals') écrirait quand même dans Data. Et un second AddSheet lève, parce qu'un patch écrit exactement une feuille de calcul. Rafraîchir trois onglets signifie trois patchs, dont chacun copie le package une fois de plus
La formule entre sans signe égal. WriteFormula place son texte directement dans l'élément <f> de la cellule, et ECMA-376 Partie 1 §18.3.1.40 y stocke les formules sans le « = » qu'Excel affiche dans la barre de formule ; écrire '=SUM(B2:B3)' met un signe égal littéral dans le XML. WriteFormula n'émet aussi aucun résultat en cache. Si quelque chose en aval lit les valeurs en cache au lieu de recalculer, WriteFormulaNumber écrit la formule avec le nombre dont vous savez déjà qu'elle s'y évalue
La surcharge sur place n'écrit jamais dans l'original pendant qu'elle travaille. Le package patché est construit dans un fichier voisin nommé d'après la source avec .hxlpatch.tmp en suffixe. Au Close, le descripteur source est libéré et le fichier en attente remplace l'original par un simple renommage. Si ce renommage échoue, Close lève une exception dont le message nomme les deux chemins, garde la copie en attente et laisse l'original où il était. Un processus tué avant le Close laisse l'original intact et un .hxlpatch.tmp égaré à côté, que le prochain BeginPatch sur ce fichier supprime avant de commencer
Ajouter une nouvelle feuille de calcul à une copie du modèle
La surcharge à quatre arguments BeginPatch(SourceFileName, TargetFileName, SheetName, InsertSheet) laisse le fichier source tranquille et écrit un nouveau package. Avec InsertSheet = False, elle remplace la feuille nommée dans la copie ; avec True, elle ajoute une nouvelle feuille sous ce nom et lève « worksheet already exists » si le modèle a déjà un onglet ainsi appelé
// template.xlsx reste intact ; output.xlsx reçoit un onglet de plus
W.BeginPatch('template.xlsx', 'output.xlsx', 'Extra', True);
W.AddSheet('Extra');
W.AddRow(1);
W.WriteString(1, 'Appended by the nightly job');
W.Close;
L'insertion est l'endroit où la comptabilité du package est éditée. Le writer scanne les entrées xl/worksheets/sheetN.xml existantes pour le numéro de partie le plus haut, les éléments <sheet> pour le sheetId le plus haut, et les relations du classeur pour le rId le plus haut, puis prend la valeur suivante de chacun. Le nouvel élément <sheet> va juste avant </sheets>, si bien que le nouvel onglet apparaît en dernier. L'attribut de relation réutilise le préfixe avec lequel le classeur source lie les feuilles, en général r:id mais sans obligation
Les types de relations suivent aussi le package source. Quand les relations du classeur utilisent déjà les espaces de noms ISO/IEC 29500 Strict sous purl.oclc.org, la nouvelle relation de feuille de calcul est écrite avec l'URI de type Strict ; un classeur Transitional reçoit la Transitional. Un classeur Strict patché ne se retrouve donc pas avec une relation Transitional parmi des Strict
Styles, chaînes et les parties qu'un patch laisse tranquilles
À l'intérieur d'un patch, tout ce que la nouvelle feuille pointe doit déjà exister dans le package source. Les chaînes sont donc écrites en ligne dans la feuille au lieu de l'être dans xl/sharedStrings.xml, parce que la table de chaînes partagées traverse octet pour octet et ne peut pas grandir. Positionner UseSharedStrings à True fait lever AddSheet en mode patch
La même règle change ce que signifie StyleIndex. Dans un patch, c'est une position dans la liste cellXfs du classeur source (ECMA-376 Partie 1 §18.8.10), et le writer vérifie seulement qu'elle se trouve entre 0 et 65534. Il ne peut pas dire si l'index 14 existe dans ce fichier particulier, donc un mauvais nombre va tout droit dans la sortie. La manière la plus fiable de trouver le bon est de dézipper la version courante de la feuille et de lire l'attribut s d'une cellule qui porte déjà le format voulu ; ce nombre est la position cellXfs. Excel peut ajouter des entrées à cette liste dès que quelqu'un formate des cellules dans le modèle, donc recherchez l'index à nouveau après chaque édition du modèle plutôt que de faire confiance à une constante écrite il y a un an
const
// position cellXfs d'un format de date dans CE modèle, tirée de l'attribut
// s="..." d'une cellule de date existante ; à revérifier après chaque édition du modèle
TemplateDateXf = 5;
begin
...
W.AddRow(5);
W.WriteString(1, 'Refreshed');
W.WriteDateTime(2, Now, TemplateDateXf); // index explicite : accepté
// W.WriteDateTime(2, Now); lève en mode patch : son style de date implicite
// vit dans une partie de styles que le patch n'écrit jamais
end;
Tout ce qui ajouterait de nouvelles parties de package est refusé. AddTable, AddChart, la famille AddImage, AddComment, AddDefinedName et AddCellStyle lèvent dès qu'ils sont appelés, et Close revérifie avant de valider, ce qui attrape aussi les mises en forme conditionnelles qui posent leur propre remplissage, couleur de police, gras ou italique, puisque celles-ci demandent de nouvelles entrées dans styles.xml. Un appel refusé vaut mieux qu'un package où la feuille référence une partie de tableau que personne n'a enregistrée
Le mode remplacement part aussi de rien pour la feuille. La partie de relations de l'ancienne feuille n'est pas copiée, donc un dessin, des commentaires ou des hyperliens externes attachés à la version précédente ne sont plus attachés au nouveau, et les largeurs de colonnes, volets figés, plages fusionnées et validations de données stockés dans l'ancien XML de feuille disparaissent aussi. Ceux qui vivent dans le XML de feuille peuvent être réécrits avec SetColumnWidth, FreezePanes, MergeCells, AddDataValidation et AddHyperlink. Un graphique ou une image ancrée sur l'onglet de données ne peut pas revenir par un patch, donc gardez les graphiques sur leur propre feuille et faites pointer leurs séries vers l'onglet de données
La chaîne de calcul est abandonnée dans les deux modes. xl/calcChain.xml liste les cellules de formules dans l'ordre où Excel les a évaluées la dernière fois ; après un remplacement ses entrées décrivent des cellules qui n'existent plus sous cette forme, et après une insertion les index de feuilles auxquels elle se réfère ont bougé. La partie est laissée dehors de la copie, et sa relation et son Override dans [Content_Types].xml sont retirés, si bien que le package ne référence pas une partie manquante. Excel reconstruit la chaîne au prochain recalcul
Quand la boucle de lignes lève à mi-chemin
Le destructeur de TXLSDirectWriter termine le package quand Close n'a jamais été appelé, donc un Close oublié ne laisse pas traîner un zip à moitié écrit. Comme il termine ce que le writer détient, une exception qui se déroule à travers Free sous la surcharge sur place valide quand même les lignes écrites jusque-là et les échange contre l'original. Une boucle qui va chercher et écrit des lignes, lève à mi-chemin et libère le writer dans un bloc finally finit exactement comme cela
Pour tout ce qui tourne sans surveillance, écrivez vers une cible explicite, ouvrez le résultat, vérifiez-le, et seulement alors remplacez le fichier actif vous-même. Une exception de la boucle de lignes laisse quand même Free terminer le fichier en attente, mais elle saute aussi l'échange, si bien que le classeur actif garde les chiffres d'hier au lieu de la moitié de ceux d'aujourd'hui
uses
Windows, SysUtils, Variants, lxDirectWrite, lxHandleX;
procedure RefreshDashboard;
const
Live = 'monthly-dashboard.xlsx';
Staged = 'monthly-dashboard.next.xlsx';
var
W: TXLSDirectWriter;
Check: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
begin
W := TXLSDirectWriter.Create;
try
W.BeginPatch(Live, Staged, 'Data', False);
W.AddSheet('Data');
WriteRefreshedRows(W); // votre propre boucle de lignes, pas une partie de HotXLS
W.Close;
finally
W.Free;
end;
Check := TXLSXWorkbook.Create;
try
if Check.Open(Staged) <> 1 then
raise Exception.Create('Staged workbook does not open');
Sheet := Check.Sheets.SheetByName('Data');
if (Sheet = nil) or (VarToStr(Sheet.Cells[2, 1].Value) <> 'North') then
raise Exception.Create('Data sheet did not come back as written');
finally
Check.Free;
end;
// API Windows, pas HotXLS : ne remplacez le fichier actif qu'après les vérifications
if not MoveFileEx(PChar(Staged), PChar(Live), MOVEFILE_REPLACE_EXISTING) then
RaiseLastOSError;
end;
TXLSXWorkbook.Open renvoie 1 en cas de succès, et Sheets comme Cells sont à base 1 : Check.Sheets[1] est le premier onglet et Cells[2, 1] est A2. Au-delà d'une valeur ponctuelle, deux contrôles structurels attrapent la plupart des erreurs. Check.Sheets.Count devrait égaler le compte du modèle après un remplacement et le dépasser d'un après une insertion, et un listage de l'archive depuis n'importe quel outil zip ne devrait montrer ni xl/calcChain.xml ni un nom d'entrée apparaissant deux fois. Les noms en double comptent parce que certains lecteurs prennent la dernière entrée portant un nom donné, ce qui fait que deux feuilles ont l'air d'avoir échangé leurs contenus
Patch, flux ou chargement du classeur ?
Le patch convient quand le classeur est grand, que le changement est confiné à une feuille, et que le reste du fichier doit survivre bit pour bit. C'est le mauvais outil quand le changement s'étend sur plusieurs feuilles, quand les nouvelles données ont besoin de formats que le modèle ne définit pas déjà, quand des graphiques ou des tableaux doivent être créés, ou quand le fichier est assez petit pour qu'un chargement et un enregistrement normaux ne coûtent rien de mesurable. Pour la génération en masse à partir de zéro, l'écrivain direct en flux convient mieux, et comme il partage les mêmes appels AddRow et Write*, déplacer du code entre les deux est surtout une affaire d'échanger BeginFile contre BeginPatch
Quand le travail exige réellement le modèle objet complet, le travail au niveau des feuilles à l'intérieur d'un classeur chargé est couvert dans la duplication de feuilles de calcul dans les packages XLSX. Et si le patch n'est sur la table que parce que le traitement du classeur entier est devenu lent, la performance des grands classeurs regarde où vont réellement le temps et la mémoire de chargement et d'enregistrement avant que vous vous engagiez dans une approche
L'écrivain de patch, l'écrivain en flux et le modèle objet complet TXLSXWorkbook arrivent tous dans une seule bibliothèque pour Delphi et C++Builder ; les éditions et la liste complète des fonctionnalités sont sur la page du composant tableur Delphi HotXLS