Una cartella di lavoro amministrata a mano raccoglie cache pivot, slicer, formattazioni condizionali e anni di stili di cella, eppure il job notturno deve solo sostituire i numeri su una scheda dati. Un caricamento e salvataggio completo parsa, ricostruisce e sgonfia di nuovo ogni parte. Riscrivere solo quella parte di foglio di lavoro e portarsi dietro ogni altra voce zip intatta salta quasi tutto quel lavoro
Ciò che lo rende possibile è il formato del pacchetto stesso. Un file .xlsx è un archivio zip di parti XML disposte secondo le Open Packaging Conventions (ECMA-376 Part 2), e ogni voce zip registra il proprio metodo di compressione, CRC-32 e dimensione compressa nell'header locale e di nuovo nella central directory. Una voce può quindi spostarsi da un archivio a un altro senza mai venire sgonfiata, purché chi copia scriva quei tre valori insieme ai byte grezzi. La cache pivot non ha bisogno di essere capita per essere preservata. Le basta essere copiata
Perché copiare byte compressi invece di risalvare la cartella di lavoro?
Copiare al livello compresso trasforma la maggior parte della patch in una semplice copia di stream. In HotXLS, TXLSDirectWriter.BeginPatch apre il pacchetto sorgente in sola lettura e affida a TZipArchive.CopyCompressedFrom ogni voce che la patch lascia in pace, e quella scrive i byte compressi, il CRC e le dimensioni della voce sorgente direttamente nell'archivio di destinazione. Un percorso di scrittura convenzionale paga un'inflate all'ingresso e una deflate all'uscita, e la deflate è la metà costosa. Su una cartella di lavoro il cui grosso è una grande cache pivot più qualche dozzina di immagini incorporate, il tempo va nel foglio che stai davvero scrivendo
La copia grezza copre le voci conservate senza compressione (metodo zip 0) e le voci sgonfiate (metodo 8). Una voce che usa un altro metodo di compressione viene comunque portata dietro: lo scrittore la legge attraverso uno stream di decompressione e la riscrive, così la parte arriva a destinazione lo stesso. Le voci marcatore di directory non contengono contenuto e vengono saltate, dato che lo scrittore produce le proprie. Una manciata di voci non viene mai copiata, perché rigenerata o modificata: il foglio di destinazione e la sua parte _rels, xl/calcChain.xml, xl/_rels/workbook.xml.rels e [Content_Types].xml, più xl/workbook.xml quando viene inserito un foglio
Sostituire un foglio sul posto con BeginPatch
L'overload a due argomenti BeginPatch(FileName, SheetName) sostituisce un foglio di lavoro esistente e riscrive il risultato sullo stesso percorso. Risolve il nome attraverso il registro dei fogli in xl/workbook.xml e la mappa delle relazioni che lega ogni foglio alla sua parte, confronta i nomi senza distinzione tra maiuscole e minuscole, e solleva un'eccezione contenente "worksheet not found" quando non trova corrispondenze. Da lì il foglio viene scritto con le stesse chiamate AddSheet, AddRow e Write* che lo scrittore in streaming usa per i file nuovi
var
W: TXLSDirectWriter;
begin
W := TXLSDirectWriter.Create;
try
W.BeginPatch('monthly-dashboard.xlsx', 'Data'); // sostituisce sul posto
W.AddSheet('Data'); // il nome è informativo in modalità 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)'); // senza '=' iniziale
W.Close;
finally
W.Free;
end;
end;
Due dettagli di quel listato si leggono male facilmente. Il nome passato a AddSheet non rinomina niente: la scheda tiene il nome, il sheetId e la posizione che ha già in xl/workbook.xml, quindi AddSheet('Totals') scriverebbe comunque in Data. E un secondo AddSheet solleva un'eccezione, perché una patch scrive esattamente un foglio di lavoro. Aggiornare tre schede significa tre patch, ognuna delle quali copia il pacchetto un'altra volta
La formula entra senza segno di uguale. WriteFormula mette il suo testo direttamente nell'elemento <f> della cella, e ECMA-376 Part 1 §18.3.1.40 conserva lì le formule senza il segno "=" che Excel mostra nella barra della formula; scrivere '=SUM(B2:B3)' mette un segno di uguale letterale nell'XML. WriteFormula non emette nemmeno un risultato in cache. Se qualcosa a valle legge i valori in cache invece di ricalcolare, WriteFormulaNumber scrive la formula insieme al numero di cui già sai quanto valuta
L'overload in place non scrive mai nell'originale mentre lavora. Il pacchetto patchato viene costruito in un file gemello chiamato come la sorgente con .hxlpatch.tmp aggiunto in coda. Al Close l'handle della sorgente viene rilasciato e il file preparato sostituisce l'originale attraverso una singola rename. Se quella rename fallisce, Close solleva un'eccezione il cui messaggio nomina entrambi i percorsi, conserva la copia preparata e lascia l'originale dov'era. Un processo ucciso prima del Close lascia l'originale intatto e un .hxlpatch.tmp vagante accanto, che il prossimo BeginPatch su quel file cancella prima di partire
Aggiungere un nuovo foglio di lavoro a una copia del template
L'overload a quattro argomenti BeginPatch(SourceFileName, TargetFileName, SheetName, InsertSheet) lascia in pace il file sorgente e scrive un nuovo pacchetto. Con InsertSheet = False sostituisce il foglio nominato nella copia; con True accoda un nuovo foglio di lavoro sotto quel nome e solleva "worksheet already exists" se il template ha già una scheda con quel nome
// template.xlsx resta intatto; output.xlsx prende una scheda in più
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'inserimento è dove la contabilità del pacchetto viene modificata. Lo scrittore scandisce le voci xl/worksheets/sheetN.xml esistenti per il numero di parte più alto, gli elementi <sheet> per il sheetId più alto, e le relazioni della cartella di lavoro per il rId più alto, poi prende il valore successivo di ognuno. Il nuovo elemento <sheet> entra poco prima di </sheets>, così la nuova scheda compare per ultima. L'attributo della relazione riusa il prefisso con cui la cartella di lavoro sorgente lega i fogli, che di solito è r:id ma non è obbligato a esserlo
Anche i tipi di relazione seguono il pacchetto sorgente. Quando le relazioni della cartella di lavoro usano già i namespace ISO/IEC 29500 Strict sotto purl.oclc.org, la nuova relazione del foglio di lavoro viene scritta con l'URI di tipo Strict; una cartella di lavoro Transitional prende quello Transitional. Una cartella di lavoro Strict patchata quindi non finisce con una relazione Transitional in mezzo alle Strict
Stili, stringhe e le parti che una patch lascia in pace
Dentro una patch, tutto ciò a cui il nuovo foglio punta deve già esistere nel pacchetto sorgente. Le stringhe vengono quindi scritte in linea nel foglio invece che in xl/sharedStrings.xml, perché la tabella delle stringhe condivise passa oltre byte per byte e non può crescere. Impostare UseSharedStrings a True fa sollevare un'eccezione ad AddSheet in modalità patch
La stessa regola cambia il significato di StyleIndex. In una patch è una posizione nella lista cellXfs della cartella di lavoro sorgente (ECMA-376 Part 1 §18.8.10), e lo scrittore controlla solo che stia tra 0 e 65534. Non può dire se l'indice 14 esista in quel file particolare, quindi un numero sbagliato finisce dritto nell'output. Il modo più affidabile per trovare quello giusto è decomprimere la versione attuale del foglio e leggere l'attributo s su una cella che porta già il formato che vuoi; quel numero è la posizione nel cellXfs. Excel può aggiungere voci a quella lista ogni volta che qualcuno formatta celle nel template, quindi cerca di nuovo l'indice dopo ogni modifica del template invece di fidarti di una costante scritta un anno fa
const
// posizione nel cellXfs di un formato data in QUESTO template, presa dall'
// attributo s="..." di una cella data esistente; riverifica dopo le modifiche al template
TemplateDateXf = 5;
begin
...
W.AddRow(5);
W.WriteString(1, 'Refreshed');
W.WriteDateTime(2, Now, TemplateDateXf); // indice esplicito: accettato
// W.WriteDateTime(2, Now); solleva un'eccezione in modalità patch: il suo stile data implicito
// sta in una parte degli stili che la patch non scrive mai
end;
Qualsiasi cosa aggiungerebbe nuove parti al pacchetto viene rifiutata. AddTable, AddChart, la famiglia AddImage, AddComment, AddDefinedName e AddCellStyle sollevano un'eccezione appena vengono chiamate, e Close ricontrolla prima di confermare, il che becca anche le formattazioni condizionali che impostano propri riempimenti, colori del font, grassetto o corsivo, dato che quelle richiedono nuove voci in styles.xml. Una chiamata rifiutata è meglio di un pacchetto in cui il foglio riferisce una parte di tabella che nessuno ha registrato
La modalità di sostituzione parte anche dal nulla per il foglio. La parte di relazione del vecchio foglio non viene copiata, quindi un disegno, commenti o collegamenti ipertestuali esterni attaccati alla versione precedente non sono più attaccati a quella nuova, e larghezze di colonna, riquadri bloccati, intervalli uniti e convalida dati conservati nel vecchio XML del foglio spariscono anch'essi. Quelli che vivono nell'XML del foglio si possono riscrivere con SetColumnWidth, FreezePanes, MergeCells, AddDataValidation e AddHyperlink. Un grafico o un'immagine ancorati alla scheda dati non possono tornare attraverso una patch, quindi tieni i grafici su un foglio proprio e punta le loro serie alla scheda dati
La catena di calcolo viene buttata in entrambe le modalità. xl/calcChain.xml elenca le celle con formula nell'ordine in cui Excel le ha valutate l'ultima volta; dopo una sostituzione le sue voci descrivono celle che non esistono più in quella forma, e dopo un inserimento gli indici di foglio a cui si riferisce si sono spostati. La parte viene lasciata fuori dalla copia, e la sua relazione e il suo Override in [Content_Types].xml vengono rimossi, così il pacchetto non riferisce una parte mancante. Excel ricostruisce la catena al prossimo ricalcolo
Quando il ciclo di righe solleva un'eccezione a metà
Il distruttore di TXLSDirectWriter completa il pacchetto quando Close non è mai stata chiamata, così un Close dimenticato non lascia in giro uno zip mezzo scritto. Siccome completa qualsiasi cosa lo scrittore tenga, un'eccezione che si propaga attraverso Free sotto l'overload in place conferma comunque le righe scritte fin lì e le sostituisce all'originale. Un ciclo che recupera e scrive righe, solleva un'eccezione a metà e libera lo scrittore in un blocco finally finisce esattamente così
Per qualsiasi cosa giri senza supervisione, scrivi su una destinazione esplicita, apri il risultato, controllalo, e solo dopo sostituisci tu il file live. Un'eccezione dal ciclo di righe lascia comunque che Free completi il file preparato, ma salta anche lo scambio, così la cartella di lavoro live conserva i numeri di ieri invece di metà di quelli di oggi
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); // il tuo ciclo di righe, non fa parte di 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, non HotXLS: sostituisci il file live solo dopo i controlli
if not MoveFileEx(PChar(Staged), PChar(Live), MOVEFILE_REPLACE_EXISTING) then
RaiseLastOSError;
end;
TXLSXWorkbook.Open restituisce 1 in caso di successo, e sia Sheets sia Cells sono in base 1: Check.Sheets[1] è la prima scheda e Cells[2, 1] è A2. Oltre a un valore spot, due controlli strutturali beccano la maggior parte degli errori. Check.Sheets.Count deve essere uguale al conteggio del template dopo una sostituzione e superarlo di uno dopo un inserimento, e un elenco dell'archivio da qualunque strumento zip non dovrebbe mostrare né xl/calcChain.xml né nomi di voce che compaiono due volte. I nomi duplicati contano perché alcuni lettori prendono l'ultima voce con un dato nome, il che fa sembrare due fogli come se il loro contenuto fosse stato scambiato
Patch, stream o caricamento della cartella di lavoro?
La patch ci sta quando la cartella di lavoro è grande, la modifica è confinata a un foglio, e il resto del file deve sopravvivere bit per bit. È lo strumento sbagliato quando la modifica copre più fogli, quando i nuovi dati hanno bisogno di formati che il template non definisce già, quando vanno creati grafici o tabelle, o quando il file è abbastanza piccolo che un normale caricamento e salvataggio non costa nulla di misurabile. Per la generazione in blocco da zero, lo scrittore diretto in streaming è più adatto, e siccome condivide le stesse chiamate AddRow e Write*, spostare codice tra i due è per lo più questione di scambiare BeginFile con BeginPatch
Quando il job ha davvero bisogno del modello a oggetti completo, il lavoro a livello di foglio dentro una cartella di lavoro caricata è trattato in duplicare fogli di lavoro nei pacchetti XLSX. E se la patch è sul tavolo solo perché l'elaborazione dell'intera cartella di lavoro è diventata lenta, le prestazioni delle cartelle di lavoro grandi guarda dove vanno davvero tempo e memoria di caricamento e salvataggio prima che tu ti impegni con un approccio
Lo scrittore di patch, lo scrittore in streaming e il modello a oggetti completo TXLSXWorkbook arrivano tutti in una sola libreria per Delphi e C++Builder; edizioni e lista completa delle funzionalità sono nella pagina del componente foglio di calcolo Delphi HotXLS