Un lavoro di normalizzazione massiva di fogli di calcolo è composto da tre problemi che indossano un solo cappotto. Avete un archivio di formati misti: .xls dell'era BIFF, .xlsx moderni, una spruzzata di .ods da qualche esperimento con LibreOffice e una manciata di file che nessuno riesce ad aprire perché la password se ne è andata con un ex dipendente. L'obiettivo è convertire tutto in XLSX e CSV. La versione di quel lavoro che quasi tutti scrivono è un ciclo che apre ogni file e lo salva con una nuova estensione, e funziona fino al momento in cui qualcuno chiede quali file hanno perso i grafici, hanno lasciato per strada le macro o non si sono mai aperti. Il ciclo non ha risposta, perché la sola conversione non tiene traccia di nulla. Un banco di lavoro sì: prima inventaria, poi converte, poi verifica, e le tre fasi devono condividere informazioni perché qualcosa di tutto ciò sia affidabile
Assemblare quel banco di lavoro in Delphi o C++Builder significa collegare quattro capacità di HotXLS, nessuna delle quali richiede Excel installato da qualche parte nella pipeline. Ci sono due motori nativi, una facciata BIFF8 per .xls e una facciata OOXML per .xlsx e .ods. Ci sono chiamate di sondaggio economiche che leggono i metadati senza analizzare l'intero file. Ci sono contatori di verifica per foglio che vi dicono cosa contenga davvero una cartella di lavoro. E c'è una matrice di conversione con un profilo di fedeltà documentato per ogni percorso. Il lavoro sta nel sapere dove ciascuna di queste ha uno spigolo vivo, perché ognuna ne ha, e gli spigoli sono esattamente ciò che trasforma un batch notturno pulito in un incidente del lunedì mattina
Sondate prima di caricare: nomi dei fogli e rilevamento della cifratura
Aprire una cartella di lavoro da 200 MB solo per scoprire che è cifrata spreca minuti per file, e moltiplicato su un archivio grande spreca giorni. Entrambe le facciate espongono GetSheetNames, che legge i metadati dei fogli senza popolare la cartella di lavoro. L'implementazione BIFF scansiona solo i record BoundSheet in testa al flusso; l'implementazione OOXML legge solo workbook.xml dentro lo zip. Accanto a essa, CanReadEncrypted rileva un contenitore cifrato senza tentare la decifratura:
var
Probe: TXLSXWorkbook;
Names: TStringList;
begin
Names := TStringList.Create;
Probe := TXLSXWorkbook.Create;
try
if Probe.CanReadEncrypted(FileName) then
begin
Writeln(FileName + ': encrypted container - route to manual handling');
Exit;
end;
if Probe.GetSheetNames(FileName, Names) <= 0 then
Writeln(FileName + ': unreadable - quarantine')
else
Writeln(Format('%s: %d sheet(s), first "%s"',
[FileName, Names.Count, Names[0]]));
finally
Probe.Free;
Names.Free;
end;
end;Due dettagli operativi rendono economico questo ciclo. GetSheetNames non azzera né popola l'istanza della cartella di lavoro, quindi un solo oggetto di sondaggio può classificare migliaia di file senza essere ricreato. E la versione della stessa chiamata sulla facciata XLS comprende anche i pacchetti .xlsx, il che ne fa un comodo sondaggio unico quando non ci si può fidare delle estensioni dei file, come raramente si può in un archivio così vecchio. Lo smistamento prima del caricamento merita una trattazione a sé; la meccanica dell'ispezione leggera sta nel nostro articolo sull'elenco dei fogli e l'ispezione leggera delle cartelle di lavoro
Contare cosa contiene davvero una cartella di lavoro
Una volta che un file supera lo smistamento, la passata di verifica decide il suo percorso di conversione. La facciata XLSX espone un contatore per ogni famiglia di funzionalità che pesa su una decisione di fedeltà: celle unite, grafici, immagini, formattazioni condizionali, convalide dei dati, tabelle, collegamenti ipertestuali e commenti, più flag a livello di cartella per macro, protezione e formato di origine. Il percorso di conversione di un file dipende quasi interamente da quali di questi tornino diversi da zero
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
I: Integer;
begin
Book := TXLSXWorkbook.Create;
try
if Book.Open(FileName) <> 1 then Exit;
for I := 0 to Book.Sheets.Count - 1 do
begin
Sheet := Book.Sheets[I];
Writeln(Format('%s: cells=%d merges=%d charts=%d cf=%d dv=%d protected=%s',
[Sheet.Name, Sheet.Cells.Count, Sheet.MergedCells.Count,
Sheet.Charts.Count, Sheet.ConditionalFormats.Count,
Sheet.DataValidations.Count, BoolToStr(Sheet.IsProtected, True)]));
end;
if Book.HasVbaProject then
Writeln(' contains VBA project - macro policy applies');
if Book.ExternalLinks.Count > 0 then
Writeln(Format(' %d external link(s)', [Book.ExternalLinks.Count]));
finally
Book.Free;
end;
end;Leggete Cells.Count tenendo a mente un avvertimento. L'archivio delle celle è sparso, quindi il numero conta le celle istanziate, non l'area rettangolare dell'intervallo usato. Un foglio con un valore in A1 e un altro in ZZ9999 riporta due celle, non il milione abbondante che sta in mezzo. La scansione equivalente sul lato BIFF usa i confini di UsedRange insieme a ForEachCell, e porta con sé lo scarto di uno che fa inciampare quasi tutti la prima volta: UsedRange.FirstRow e i suoi fratelli sono in base 0, mentre Cells.Item[Row, Col] è in base 1. Un attraversamento che dimentica di aggiungere uno a ciascun confine verifica il rettangolo sbagliato e non lo dice mai
Due leve tagliano il costo di una passata di sola verifica su file legacy grandi. Impostare _DisableGraphics a vero prima di aprire un .xls salta del tutto l'analisi del livello di disegno OfficeArt, il che fa risparmiare tempo reale su cartelle dense di forme. È però rigorosamente un'ottimizzazione in sola lettura: salvare da un'istanza aperta in quel modo perderebbe i disegni che non ha mai analizzato, quindi il flag appartiene solo ai percorsi che non riscriveranno mai il file. Quando la verifica ha bisogno del contenuto cella per cella anziché dei conteggi, la callback ForEachCell percorre direttamente le celle popolate e aggira il sovraccarico di Variant per accesso che le proprietà indicizzate delle celle pagano a ogni lettura, e che si accumula in fretta su milioni di celle
Normalizzate presto i codici di ritorno incoerenti
Le chiamate di I/O di HotXLS segnalano gli errori con risultati interi anziché con eccezioni, e le convenzioni non sono uniformi in tutta la API. La maggior parte delle chiamate di apertura e salvataggio restituisce 1 in caso di successo e -1 in caso di fallimento. GetSheetNames restituisce il numero di fogli, oppure -1 con l'elenco svuotato. SaveAsHTML di XLSX rompe di nuovo lo schema e restituisce 0 per il successo, -1 per un indice di foglio fuori intervallo. Un banco di lavoro che verifica = 1 ovunque classificherà male in silenzio le chiamate che segnalano il successo in altro modo, e uno che verifica <> -1 ingoierà quelle che falliscono con un codice diverso
La regola che sopravvive al contatto con l'intera API è più stretta di quanto sembri: trattate <= 0 come fallimento per le chiamate che restituiscono conteggi, controllate il valore di successo documentato per ogni routine di salvataggio che usate davvero, e mettete entrambe le cose dietro una piccola funzione di controllo dei risultati, così che la convenzione viva in un solo punto. Le pipeline batch falliscono molto più spesso per un lento accumularsi di codici di ritorno non controllati che per un qualche esotico difetto del parser, e il costo di sbagliare qui si paga quarantamila file dopo, quando nessuno ricorda più quali conversioni siano riuscite
La matrice di conversione e dove ogni strada perde dati
Le due facciate si dividono il lavoro di conversione. TXLSXWorkbook apre XLSX, ODS e CSV, e salva XLSX, ODS, CSV, HTML, RTF e XLSX cifrato con AES. TXLSWorkbook apre e salva BIFF, ed esporta HTML, RTF e CSV. La cosa utile è che ogni percorso arriva con un profilo di fedeltà documentato, non con una vaga promessa di correttezza, così potete decidere in anticipo quali strade siano sicure per quali file
L'esportazione CSV scrive UTF-8 con BOM, fine riga CRLF e quotatura secondo RFC 4180. Quello che non fa è valutare le formule: una cella che contiene =SUM(...) viene esportata come testo letterale della formula, quindi un foglio di formule diventa un foglio di stringhe se prima non calcolate i valori. L'esportazione HTML produce una sola tabella, con colspan e rowspan al posto delle celle unite e gli stili di base incorporati. L'esportazione RTF ha un limite più netto: non riesce a estendere le celle unite fra colonne, quindi le celle di continuazione di un'unione escono vuote. L'importazione ODS è leggera di proposito, secondo la documentazione della libreria stessa. Passano i valori scalari e i risultati di formula in cache; non passano stili, espressioni di formula ODF vive e disegni. Questo conta nel momento in cui l'archivio contiene veri file OpenDocument governati da OASIS ODF 1.3, dove qualsiasi cosa vicina a una conversione visivamente fedele richiede più di quanto questo percorso di importazione sia stato costruito per portare, ed è la passata di verifica a dirvi che quei file esistono prima che il batch li appiattisca in silenzio
SaveXLSWorkbookAsXLSX è un ponte per i dati, non per il layout
La facciata BIFF non può scrivere OOXML direttamente, quindi il passaggio da .xls a .xlsx corre attraverso la funzione SaveXLSWorkbookAsXLSX nella unit lxXlsxExport. Vale la pena dichiarare senza giri di parole la fedeltà di quel ponte, perché il nome promette più di quanto mantenga. Copia valori, formule, formati numerici, colori di riempimento, attributi principali dei font, larghezze delle colonne e impostazioni di vista come le griglie. Non copia bordi, intervalli uniti, commenti, grafici o formattazioni condizionali. Per una normalizzazione a livello di dati, dove i sistemi a valle analizzeranno il risultato e nessuno guarda la formattazione, è esattamente quanto basta e non si perde nulla di cui qualcuno abbia bisogno. Per un report direzionale formattato, destinato a essere letto da una persona, non basta, ed è proprio qui che i contatori di verifica si guadagnano il posto: un file che la verifica ha segnalato come portatore di grafici e formattazioni condizionali dovrebbe essere instradato a una coda manuale, non attraverso un ponte che li lascerebbe cadere entrambi senza una parola
var
Legacy: IXLSWorkbook; // riferimento a interfaccia: non chiamate Free
Modern: TXLSXWorkbook;
begin
if SameText(ExtractFileExt(FileName), '.xls') then
begin
Legacy := TXLSWorkbook.Create;
if Legacy.Open(FileName) <= 0 then Exit;
if SaveXLSWorkbookAsXLSX(Legacy,
ChangeFileExt(FileName, '.xlsx')) <= 0 then
Writeln('bridge failed: ' + FileName);
end
else
begin
Modern := TXLSXWorkbook.Create;
try
Modern.StreamingWrite := True; // scrive l'XML del foglio nello zip
if Modern.Open(FileName) = 1 then
Modern.SaveAsCSV(ChangeFileExt(FileName, '.csv'), 0, ',');
finally
Modern.Free;
end;
end;
end;Il ciclo qui sopra mostra anche la leva di throughput sul lato OOXML. Impostare StreamingWrite a vero fa scorrere l'XML dei fogli direttamente dentro il pacchetto di output anziché prepararlo come una sola stringa gigante in memoria, il che è la differenza fra un'esecuzione tranquilla e un crash per esaurimento di memoria quando i file arrivano a centinaia di migliaia di righe. Dimensionamento e comportamento in memoria di quella modalità hanno una trattazione a sé nel nostro articolo sulle scritture a flusso per i lavori batch server. Un'altra proprietà conta per un batch che voglia usare tutti i core: nessuna delle due facciate è sicura fra thread, ma nessuna condivide stato globale, quindi lo schema supportato per la conversione parallela è un'istanza di cartella di lavoro per thread di lavoro, senza alcun blocco fra di esse
I file con password, e cosa farne
I file bloccati dell'archivio si dividono nettamente per formato, e la divisione decide dove finiscono. La cifratura dei .xls legacy, che sia RC4, RC4 su CryptoAPI o la vecchia offuscazione XOR, è leggibile: passate la password a Open e il file si converte come qualsiasi altro. I pacchetti .xlsx cifrati sono un'altra storia. HotXLS li rileva con CanReadEncrypted ma non riesce a decifrarli, quindi l'unica mossa onesta è instradarli a una coda dove una persona apre e risalva ciascuno in Excel prima che rientri nella pipeline. Vale la pena progettare in anticipo per quell'asimmetria, perché i file XLSX cifrati sono quelli con maggiori probabilità di essere i documenti a cui qualcuno tiene davvero
Chiudere il cerchio con la verifica
La terza fase è quella che si salta, e saltarla è ciò che trasforma una conversione massiva in una responsabilità. Nessun percorso di salvataggio in HotXLS valuta le formule. Excel ricalcola quando apre un file, quindi una conversione da XLSX a XLSX resta corretta, ma una destinazione CSV riceve il testo della formula letteralmente a meno che la pipeline non esegua prima Calculate sulle celle e ne riscriva i risultati. Saperlo in anticipo è la differenza fra un CSV pieno di numeri e un CSV pieno di stringhe =SUM(...) che nessuno nota finché un'importazione a valle non ci si strozza
La verifica in sé è abbastanza economica da non lasciare scuse per ometterla. Riaprite ogni file convertito con la stessa libreria, rieseguite i contatori di verifica e confrontateli con i numeri precedenti alla conversione che la passata di inventario ha già registrato. Un conteggio di fogli calato, un conteggio di grafici andato a zero dove la sorgente ne aveva tre, un conteggio di celle precipitato: ciascuno è una perdita silenziosa intercettata al costo di una seconda apertura. Aggiungete un controllo a campione a occhio in Excel o LibreOffice e la combinazione intercetta la stragrande maggioranza dei danni da conversione prima della consegna. È tutta qui la ragione per cui la fase di inventario alimenta la fase di verifica. Senza i numeri di partenza, i numeri di arrivo non dimostrano nulla
Un banco di lavoro che verifica prima trasforma una conversione massiva rischiosa in un processo misurabile, con una corsia di quarantena per i file che non possono passare puliti. Tutte le chiamate di sondaggio, conteggio e conversione mostrate qui fanno parte di HotXLS Delphi Component, che le esegue nativamente nel processo senza automazione di Excel