Un classeur sauvegardé par Excel 4.0 en 1992 commence par les octets 09 04 06 00, tandis que chaque .xls à partir d'Excel 5.0 commence par la signature de fichier composé D0 CF 11 E0. Un lecteur qui vérifie cette signature rejette le fichier plus ancien avant de lire une seule cellule, et un lecteur qui saute la vérification et suppose du BIFF8 analyse le premier enregistrement comme quelque chose qu'il n'est pas. Les deux échecs viennent de la même hypothèse : que chaque .xls a un conteneur. Les fichiers Excel 2.0, 3.0 et 4.0 n'en ont aucun, si bien que le flux d'enregistrements lui-même doit dire quelle génération l'a écrit, et les enregistrements de cellule doivent être décodés avec la disposition de cette génération
Ces fichiers continuent d'apparaître, ce qui est la seule raison pour laquelle tout cela compte. Les archives d'ingénierie, les magasins de conservation de dossiers, les exports de laboratoires venant d'instruments dont le logiciel de contrôle date du début des années 1990, et les systèmes comptables de longue durée ont tous laissé derrière eux des classeurs BIFF2 à BIFF4. Les builds actuels d'Excel bloquent plusieurs de ces formats par défaut via leurs réglages File Block, si bien que le jeu de données se trouve souvent dans un dossier que personne ne peut ouvrir avec les outils sur son bureau
Comment un classeur antérieur à OLE se fait connaître
HotXLS reconnaît un classeur antérieur à OLE dès son premier enregistrement, à l'intérieur du même appel TXLSWorkbook.Open qui charge tout autre .xls. Chaque .xls à partir d'Excel 5.0 est un fichier composé OLE2, un petit système de fichiers à l'intérieur d'un fichier, le classeur résidant dans un flux nommé Workbook ou Book ; l'analyser commence par analyser ce conteneur, comme décrit dans le format binaire de fichier composé en Pascal
BIFF2 à BIFF4 sautent tout cela. Le fichier commence directement par un enregistrement BOF, et le numéro d'enregistrement de ce BOF code la génération : $0009 pour BIFF2, $0209 pour BIFF3 et $0409 pour BIFF4. Le mot de version à l'intérieur du corps, sur lequel s'appuient les formats ultérieurs, est ignoré par ces générations, si bien que l'id d'enregistrement est le seul signal digne de confiance. Avant de s'engager sur la voie brute, la couche de flux de HotXLS vérifie encore deux choses : le corps du BOF doit faire de quatre à six octets, et le mot de type de sous-flux doit être $0010 pour une feuille de calcul, $0020 pour un graphique ou $0040 pour une feuille macro. Tout le reste fait renvoyer un code d'erreur à Open
Ces vérifications comptent plus qu'on ne le pense. Deux octets $09 $00 au décalage zéro ne sont pas rares dans des données binaires quelconques, et un dossier de quarantaine de .xls non identifiés tend à contenir des exports de texte renommés, des copies tronquées et le fichier occasionnel d'un programme entièrement différent. Trois champs indépendants d'accord entre eux sont un signal bien plus fort qu'un seul
Trois générations, trois structures d'enregistrement
C'est au niveau des enregistrements de cellule que les générations divergent le plus visiblement. BIFF2 occupe un bloc contigu de numéros d'enregistrement bas, de $0001 à $0005, pour les cellules vides, entières, numériques, d'étiquette et booléen-ou-erreur. Chacun de ces corps porte un champ d'attribut de trois octets là où les versions ultérieures mettent un index de format étendu de deux octets, si bien que chaque valeur se trouve un octet plus loin dans l'enregistrement. L'enregistrement INTEGER de BIFF2 est sa propre bizarrerie : une valeur non signée de 16 bits sans partie fractionnaire, un type d'enregistrement que BIFF3 a abandonné au profit de RK
BIFF3 et BIFF4 abandonnent les numéros bas et réutilisent les ids et dispositions d'enregistrements BIFF5 : $0201, $0203, $0204, $0205 et $027E, chacun avec un index XF de deux octets. Cette réutilisation provoque une défaillance spécifique et facile à mal diagnostiquer. Un enregistrement LABEL BIFF3 ou BIFF4 est structurellement identique à son équivalent BIFF5 : ligne, colonne, index de format, puis un compte de caractères de deux octets au décalage 6. Appliquez cette disposition à une étiquette BIFF2 et le compte s'assemble depuis le dernier octet d'attribut et la vraie longueur d'un octet, si bien qu'une étiquette de cinq caractères revendique au moins 1,280 caractères et le lecteur sort de la fin de l'enregistrement. Dans l'autre sens, la disposition BIFF2 sur un enregistrement BIFF3, et la longueur est lue depuis l'octet haut du compte, qui vaut zéro pour toute chaîne courte. Rien ne se lève dans les deux sens. Le classeur revient avec des étiquettes vides ou des ordures plausibles, et HotXLS évite les deux en dispatchant sur l'id d'enregistrement conjointement avec la génération prise du BOF
Le champ d'attribut BIFF2 cache encore un piège. Son premier octet porte l'index de format dans ses six bits bas, et la valeur $3F signifie "le vrai index est trop grand pour six bits, regardez l'enregistrement IXFE précédent". HotXLS suit le IXFE le plus récent et résout l'échappatoire avant d'attribuer son style à la cellule, si bien qu'une cellule dont l'index de format demande plus de six bits reçoit quand même sa propre mise en forme
Le texte dans les trois générations est une chaîne d'octets plate sans Unicode nulle part. L'encodage vient de l'enregistrement CODEPAGE du fichier, qui dans ces fichiers se trouve à l'intérieur de l'unique flux de feuille de calcul parce qu'il n'y a pas de flux au niveau classeur pour l'accueillir. HotXLS décode les étiquettes et les résultats de chaîne via cette page de code et retombe sur Windows-1252 quand l'enregistrement est absent, ce qui couvre le défaut occidental avec lequel la plupart de ces fichiers ont été écrits. Un classeur japonais ou d'Europe centrale qui n'a pas d'enregistrement CODEPAGE se décodera donc en texte latin, et la seule correction est de savoir d'où vient le fichier
En ouvrir un depuis Delphi
La détection se produit à l'intérieur d'Open, si bien que le code appelant est le même que pour tout classeur. La boucle ci-dessous parcourt la plage utilisée et imprime chaque cellule deux fois quand elle porte une formule : l'expression traduite et la valeur telle qu'Excel l'afficherait via le format de nombre de la cellule
uses
SysUtils, Variants, lxHandle;
var
Book: TXLSWorkbook;
Sheet: TXLSWorksheet;
Cell: IXLSRange;
R, C: Integer;
begin
Book := TXLSWorkbook.Create;
try
try
if Book.Open('archive\1992-inventory.xls') <> 1 then
begin
Writeln('not a readable workbook - quarantine for manual review');
Exit;
end;
except
// Open lève quand le fichier est en réalité un paquet ZIP,
// typiquement un .xlsx que quelqu'un a renommé en .xls
on E: Exception do
begin
Writeln('rejected: ', E.Message);
Exit;
end;
end;
Sheet := Book.Sheets[1]; // Sheets[] est indexé à un
for R := Sheet.UsedRange.FirstRow + 1 to Sheet.UsedRange.LastRow + 1 do
for C := Sheet.UsedRange.FirstCol + 1 to Sheet.UsedRange.LastCol + 1 do
begin
Cell := Sheet.Cells[R, C];
if VarIsEmpty(Cell.Value) or VarIsNull(Cell.Value) then
Continue;
if Cell.HasFormula then
Writeln(Format('R%dC%d %s -> %s',
[R, C, VarToStr(Cell.Formula), Cell.FormattedText]))
else
Writeln(Format('R%dC%d %s', [R, C, Cell.FormattedText]));
end;
finally
Book.Free;
end;
end;
Surveillez l'arithmétique d'index. Les bornes d'UsedRange sont indexées à zéro alors que la collection de feuilles et l'accès aux cellules sont indexés à un, une incohérence que l'API garde pour la compatibilité avec le code existant. Oubliez l'ajustement et la boucle audite le mauvais rectangle sans rien signaler d'inhabituel. Les fichiers que cette voie accepte portent un unique sous-flux de feuille de calcul, de graphique ou de feuille macro, si bien que Sheets[1] est la seule feuille que vous trouverez ; un classeur lié Excel 4.0 (type BOF $0100) est hors de cet ensemble et Open renvoie un code d'erreur. Les précontrôles économiques qui évitent de charger un fichier du tout sont couverts dans l'inspection légère de classeurs
Les fichiers protégés par mot de passe suivent la même règle du flux unique. L'enregistrement FILEPASS apparaît juste après le BOF dans le flux de la feuille de calcul, où HotXLS le ramasse et enclenche la désobfuscation XOR héritée qu'utilisaient ces générations ; passez le mot de passe par la surcharge Open(FileName, APassWord)
Que devient une formule dans un fichier BIFF2 à BIFF4 ?
HotXLS lit les deux moitiés d'une formule héritée : le résultat en cache qu'Excel a stocké à son dernier calcul, et le flux de jetons, qui est traduit vers le modèle de calcul courant pendant le chargement. Les enregistrements de formule partagent une numérotation parallèle sur les trois générations, $0006, $0206 et $0406, mais les corps diffèrent exactement aux endroits où un analyseur BIFF8 trébucherait
Le résultat en cache commence à l'octet 7 en BIFF2, à cause du champ d'attribut, et à l'octet 6 en BIFF3 et BIFF4. Les résultats non numériques utilisent un mot marqueur $FFFF dans les deux derniers octets de cet emplacement de huit octets, avec un discriminateur dans le premier octet : 0 pour une chaîne, 1 pour un booléen, 2 pour un code d'erreur, 3 pour un vide. Un résultat de chaîne ne tient pas du tout dans l'emplacement, si bien que le texte arrive dans l'enregistrement suivant, $0007 en BIFF2 avec une longueur d'un octet ou $0207 en BIFF3 et BIFF4 avec une longueur de deux octets
Le flux de jetons est l'endroit où une implémentation naïve produit du non-sens. BIFF2 préfixe l'expression avec une longueur d'un octet au décalage 16 et démarre les jetons à 17 ; BIFF3 et BIFF4 utilisent une longueur de deux octets et démarrent à 18. À l'intérieur du flux, les jetons de fonction portent un index de fonction d'un octet avant BIFF5 et un index de deux octets à partir de BIFF5. Donnez des jetons BIFF4 à un traducteur BIFF8 inchangé et chaque appel de fonction consomme un octet de trop, si bien que le résultat n'est pas une formule légèrement fausse mais une formule sans rapport. Le traducteur de HotXLS prend la version BIFF en paramètre et lit chaque jeton avec les largeurs de cette génération
Avoir les deux moitiés pose une question pratique : quel nombre entre dans le classeur migré ? La valeur en cache est ce que les utilisateurs du fichier ont vu, imprimé et signé en 1992. Un recalcul sur un moteur moderne calcule ce que la formule signifie aujourd'hui, et le comportement des fonctions n'est pas resté immobile pendant trois décennies. Pour une migration d'archive, la valeur en cache est le registre, alors gardez-la et ne faites tourner un recalcul que comme contre-vérification qui vous dit quelles cellules méritent un œil humain :
// Contre-vérification : comparez les résultats stockés à un recalcul frais.
// Ce helper et son tableau CachedText sont votre propre code
procedure CompareWithRecalc(Book: TXLSWorkbook; Sheet: TXLSWorksheet);
var
CachedText: array of array of string;
Rows, Cols, R, C: Integer;
begin
Rows := Sheet.UsedRange.LastRow + 1;
Cols := Sheet.UsedRange.LastCol + 1;
SetLength(CachedText, Rows + 1, Cols + 1);
for R := 1 to Rows do
for C := 1 to Cols do
if Sheet.Cells[R, C].HasFormula then
CachedText[R, C] := VarToStr(Sheet.Cells[R, C].Value);
Book.Recalculate;
for R := 1 to Rows do
for C := 1 to Cols do
if Sheet.Cells[R, C].HasFormula and
(VarToStr(Sheet.Cells[R, C].Value) <> CachedText[R, C]) then
Writeln(Format('R%dC%d stored=%s now=%s formula=%s',
[R, C, CachedText[R, C], VarToStr(Sheet.Cells[R, C].Value),
VarToStr(Sheet.Cells[R, C].Formula)]));
end;
Exécutez cela sur une copie, ou avant la sauvegarde, puisque Recalculate remplace les résultats stockés en mémoire. Un rapport vide est le résultat que vous voulez ; un rapport court pointe en général vers des fonctions volatiles telles que NOW() ou vers des références que l'auteur original a laissées pendantes
Mise en forme, dates et le piège du format Standard
HotXLS convertit les enregistrements de mise en forme de ces générations vers le même modèle de classeur que produit un .xls moderne. Les enregistrements FONT (avec l'enregistrement FONTCOLOR BIFF2 séparé intégré), les chaînes FORMAT, et les enregistrements XF de chaque génération, avec leurs motifs de remplissage, styles de bordure et index de couleurs hérités, deviennent des polices, formats de nombre et styles de cellules ordinaires que vous pouvez lire et éditer via Font, NumberFormat et les autres propriétés de plage
Les dates profitent directement. Quand une cellule numérique porte un format de nombre de date, le lecteur applique aussi le drapeau DATEMODE du classeur, si bien qu'un classeur sur système 1904 atterrit sur la même échelle TDateTime qu'un classeur sur système 1900, et FormattedText montre la date comme Excel le faisait. Les règles derrière le décalage de 1462 jours sont couvertes dans les numéros de série de date, le système 1904 et les formats de nombre
Le piège, c'est la colonne qui porte des dates sous un format Standard. Pas mal de classeurs des années 1990 ont été remplis par des macros ou des imports qui écrivaient des numéros de série sans se soucier de les formater, et le fichier ne donne aucun signal que 33604 signifie un jour de janvier 1992 plutôt qu'un numéro de pièce. Ces cellules reviennent exactement comme stockées, sans correction du système de dates. Décidez par colonne, à partir de ce que vous savez de la source, et convertissez ces colonnes vous-même :
// ColumnHoldsDates est votre propre table, bâtie à partir de la connaissance du
// système source ; le fichier hérité ne peut pas répondre à cette question pour vous.
// Serial est un Double local ; Cell et C viennent du premier listing
if ColumnHoldsDates(C) and (Pos('d', LowerCase(Cell.NumberFormat)) = 0) then
begin
// Numéro de série au format Standard : pas encore de correction du système de dates
Serial := Cell.Value;
if Book.Date1904 then
Serial := Serial + 1462;
Writeln(DateToStr(TDateTime(Serial)))
end
else
Writeln(Cell.FormattedText);
Le test Pos('d', ...) est délibérément grossier et ne sert qu'à sauter les cellules qui portent déjà un jeton de jour dans leur format ; un contrôle plus strict contre votre propre liste de formats de date connus vaut les lignes supplémentaires si l'archive est grande
Écrire la copie migrée
Un classeur ouvert depuis un fichier BIFF2 à BIFF4 se sauvegarde via les rédacteurs BIFF5/BIFF8 ou XLSX, puisqu'il n'y a aucune raison de produire de nouveaux fichiers dans un format que l'Excel actuel bloque. Book.SaveAs('migrated\1992-inventory.xls', xlExcel97) écrit une copie BIFF8 qui porte les valeurs, les formules traduites et les styles convertis, et tout outil ultérieur du pipeline peut l'ouvrir sans traitement particulier. Gardez l'original à côté : si une cellule soulève une question dans deux ans, les octets de 1992 sont la seule autorité
La lecture héritée, l'écriture moderne, le recalcul et la mise en forme vivent tous dans une seule bibliothèque pour Delphi et C++Builder ; la couverture de formats est listée sur la page du composant tableur HotXLS Delphi