Articolo tecnico

Timestamp dei documenti Excel in Delphi: FILETIME, UTC e DST

Una cartella di lavoro generata alle 09:30 su una macchina di build a Shanghai si apre in Excel, e File > Info riporta che è stata creata alle 17:30. Il test unitario che ha prodotto il file è passato: assegnava 09:30, salvava, riapriva e rileggeva 09:30 dritto. Entrambe le letture vengono dagli stessi byte. Il file contiene 09:30 marcate come UTC, Excel onora il marcatore e aggiunge otto ore, e il test non ha mai guardato il marcatore. I timestamp dei documenti nei file Excel sono UTC su disco, il valore con cui lavora un programma è ora locale, e ogni conversione tra i due deve usare le regole di ora legale della data della marcatura stessa

L'errore di un giorno che un round trip non vede

In un file .xls le ore di creazione e salvataggio sono valori FILETIME: un conteggio a 64 bit di tick da 100 nanosecondi dal 1601-01-01 UTC ([MS-DTYP] §2.3.3). Un TDateTime Delphi conta i giorni dal 1899-12-30, la stessa origine seriale trattata in seriali di date Excel in Delphi e i sistemi 1900 vs 1904. Convertire tra i due serve una costante, il numero di giorni tra le due epoche, e quel numero è 109205. C'è un modo rapido di controllarlo senza un calendario. L'epoca Unix è 25569 come TDateTime e 134774 giorni dopo l'epoca FILETIME, e 25569 + 109205 = 134774

Contate i giorni a mano includendo entrambe le date finali, e ottenete 109206. Uno scrittore e un lettore costruiti su quella costante sono d'accordo tra loro alla perfezione. Ogni marcatura di creazione e salvataggio va nel file un giorno tardi e esce un giorno presto, così un test salva-e-riapri vede esattamente il valore che aveva assegnato, mentre Excel, o qualsiasi lettore che usi l'epoca corretta, mostra domani

const
  // giorni dall'epoca FILETIME (1601-01-01) all'epoca TDateTime (1899-12-30)
  // verifica: 25569 + 109205 = 134774, l'epoca Unix in giorni FILETIME
  FileTimeDayBias = 109205;

function UtcDateTimeToFileTimeTicks(UtcStamp: TDateTime): Int64;
begin
  // Arrotondate prima a millisecondi interi, poi scalate a tick da 100 ns.
  // Scalare il Double direttamente a tick trasforma 04:00 in 03:59:59.9999
  Result := Round((UtcStamp + FileTimeDayBias) * 86400000.0) * 10000;
end;
Cronologia HotXLS dell'epoca FILETIME 1601-01-01, dell'epoca TDateTime 1899-12-30 e dell'epoca Unix 1970, con il bias di 109205 giorni dietro UtcDateTimeToFileTimeTicks e come un bias di 109206 scriverebbe ogni marcatura CreatedDate un giorno tardi rileggendola un giorno presto
Lo schema di UtcDateTimeToFileTimeTicks tiene il bias dove una costante sbagliata si cancella da sola — un test simmetrico salva-e-riapri vede il valore assegnato mentre il riquadro Info di Excel mostra domani

Anche il commento sull'arrotondamento in quello schema conta. Una frazione di TDateTime è un Double binario, e moltiplicarlo direttamente per 864.000.000.000 tick al giorno spinge l'errore di rappresentazione nelle cifre basse, così una marcatura di 04:00 esatte si rilegge come 03:59:59.9999 e si mostra come 03:59. HotXLS arrotonda a millisecondi interi e solo poi scala a tick. I valori in punto sopravvivono. Lo schema prende un input UTC di proposito; il passo del fuso orario arriva dopo ed è la metà più difficile

Quali ID di proprietà SummaryInformation contengono le date?

Il property set \005SummaryInformation definito da [MS-OLEPS] tiene l'ora di creazione sotto l'ID di proprietà $0C (PIDSI_CREATE_DTM), l'ora di ultimo salvataggio sotto $0D (PIDSI_LASTSAVE_DTM) e il tempo totale di editing sotto $0A (PIDSI_EDITTIME). L'ID subito dopo la marcatura di salvataggio, $0E, è PIDSI_PAGECOUNT. Metteteci un'ora di salvataggio ed Excel non ha una data di salvataggio da mostrare, più una proprietà conteggio pagine con dentro un FILETIME. HotXLS scrive la marcatura di salvataggio sotto $0D, e quando legge un file in cui $0D manca ma $0E porta un VT_FILETIME, prende quel valore come ora di ultimo salvataggio. Una stringa o qualsiasi altro tipo seduto sotto uno di questi ID in un file malformato viene saltato anziché letto male come data. Per un'occhiata agli stream grezzi stessi, vedete leggere file compound OLE2 in Delphi senza COM IStorage

PIDSI_EDITTIME è la pecora nera. È tipizzato VT_FILETIME ma contiene una durata: il conteggio grezzo dei tick da 100 ns trascorsi, senza epoca aggiunta. Trattatelo come gli altri due, convertendo EditTimeMinutes / 1440 nell'aritmetica dell'epoca, e 125 minuti di editing diventano un valore lungo circa 299 anni. HotXLS scrive il conteggio di tick puro (minuti × 600.000.000). Su Windows il lettore riconosce anche la codifica a forma di data dalla dimensione, dato che nessuna sessione di editing reale copre tre secoli: qualsiasi edit time di 109206 giorni o più ha quell'offset sottratto prima che EditTimeMinutes venga riempito

Mappa HotXLS del property set 005SummaryInformation dove PIDSI_CREATE_DTM a $0C contiene l'ora di creazione, PIDSI_LASTSAVE_DTM a $0D la marcatura di salvataggio, PIDSI_EDITTIME a $0A una durata grezza anziché una data, e $0E PIDSI_PAGECOUNT, uno slot per conteggio pagine che non deve contenere timestamp
PIDSI_EDITTIME è la trappola dentro la trappola — tipizzato VT_FILETIME ma con tick trascorsi senza epoca, quindi trattarlo come una data trasforma 125 minuti di editing in circa 299 anni, cosa che un controllo di lettura basato sulla dimensione riconosce

Impostare i timestamp .xls con TXLSWorkbook

TXLSWorkbook.CreatedDate e TXLSWorkbook.LastSavedDate prendono valori TDateTime locali, e HotXLS li converte in FILETIME UTC quando la cartella di lavoro viene salvata. RevisionNumber e EditTimeMinutes stanno nello stesso stream. Un valore zero significa «lascia fuori questa proprietà», che è anche il modo in cui eliminate tutte e quattro: azzeratele e il prossimo salvataggio non ne scrive nessuna. HotXLS non timbra Now in LastSavedDate da sé. Ciò che assegnate è ciò che va nel file, e questo è il predefinito giusto per una libreria. Un job batch che rigenera i report dell'anno scorso di solito vuole le date dell'anno scorso, e un timbro automatico le sovrascriverebbe in silenzio

var
  Book: TXLSWorkbook;
begin
  Book := TXLSWorkbook.Create;
  try
    Book.Title := 'Q3 settlement';
    // i valori API sono ora locale; il file memorizza FILETIME UTC
    Book.CreatedDate := EncodeDate(2026, 1, 15) + EncodeTime(9, 30, 0, 0);
    Book.LastSavedDate := Now;     // scritto come assegnato, mai timbrato automaticamente
    Book.RevisionNumber := 7;
    Book.EditTimeMinutes := 125;   // una durata, memorizzata come tick grezzi
    if Book.SaveAs('settlement.xls') <> 1 then
      raise Exception.Create('Save failed');
  finally
    Book.Free;
  end;
end;

Aprire un file riempie le stesse quattro proprietà al contrario, convertite nell'ora locale della macchina che legge. Un servizio che gira in UTC e un desktop a Tokyo riporteranno valori CreatedDate diversi per la stessa cartella di lavoro, nove ore di distanza, ed entrambi sono corretti. Se due sistemi devono confrontare le marcature, convertite in UTC da parte vostra prima del confronto, o archiviate il valore UTC accanto

Perché le date XLSX sbagliano esattamente l'offset del fuso?

Le date XLSX escono sbagliate dell'offset del fuso quando uno scrittore timbra l'ora locale etichettandola UTC. In docProps/core.xml, dcterms:created e dcterms:modified sono valori W3CDTF sotto il modello delle core properties di ECMA-376 Part 2, e la forma consueta finisce con Z. Formattare Now e attaccare una Z è la one-liner ovvia. Su una macchina UTC+8 trasforma 09:30 in 09:30:00Z, ed Excel su quella stessa macchina mostra 17:30 — l'esempio iniziale, alla lettera. Le proprietà data personalizzate archiviate come vt:filetime seguono la stessa regola, così i FILETIME binari nei .xls. HotXLS converte su tutti e tre i percorsi prima di scrivere: TXLSXWorkbook.Created, TXLSXWorkbook.Modified e le date aggiunte tramite CustomProperties.AddDate sono locali dal lato API e UTC con una Z nel file

Quale funzione Windows faccia la conversione conta più di quanto ci si aspetti. LocalFileTimeToFileTime applica l'offset in vigore adesso. Convertite una marcatura di gennaio a luglio in qualsiasi fuso con ora legale e atterra con un'ora di scarto. HotXLS usa TzSpecificLocalTimeToSystemTime in scrittura e SystemTimeToTzSpecificLocalTime in lettura, ed entrambe scelgono ora solare o legale dalla data che si sta convertendo, non da oggi. Un valore non impostato di zero passa intatto in entrambe le direzioni, così una proprietà vuota non diventa mai una data 30 dicembre 1899 spostata di qualche ora

Percorsi di conversione da locale a UTC di HotXLS per una marcatura 17:00 CET di gennaio salvata a luglio: LocalFileTimeToFileTime applica l'offset di ora legale di oggi e atterra con un'ora di scarto a 15:00Z, mentre TzSpecificLocalTimeToSystemTime sceglie l'offset dalla data della marcatura stessa e scrive il corretto 16:00Z
L'offset del fuso appartiene alla data della marcatura, non alle regole attuali della macchina — una API Windows sceglie il lato giusto di un cambio di ora legale, l'altra sposta in silenzio di un'ora le marcature di gennaio convertite a luglio
var
  Book: TXLSXWorkbook;
begin
  Book := TXLSXWorkbook.Create;
  try
    if Book.Open('report-template.xlsx') <> 1 then
      raise Exception.Create('Template not available');
    Book.Created := EncodeDate(2026, 7, 1) + EncodeTime(9, 30, 0, 0);
    Book.Modified := Now;
    Book.CustomProperties.AddDate('ApprovedOn',
      EncodeDate(2026, 1, 20) + EncodeTime(17, 0, 0, 0));
    if Book.SaveAs('report.xlsx') <> 1 then
      raise Exception.Create('Save failed');
    // Su una macchina impostata su Central European Time, core.xml ora contiene
    // <dcterms:created xsi:type="dcterms:W3CDTF">2026-07-01T07:30:00Z</dcterms:created>
    // (UTC+2 a luglio), mentre ApprovedOn viene scritto come 16:00Z (UTC+1 a gennaio)
  finally
    Book.Free;
  end;
end;

Fuori da Windows la stessa conversione passa per il supporto fusi orari della RTL. In Delphi è TTimeZone.Local, e le due ore impacciate dell'anno lì ricevono una risposta fissa: un'ora locale dentro il varco del cambio a primavera non esiste, quindi viene scritta invariata, e un'ora autunnale che capita due volte viene presa come l'occorrenza in ora solare

Leggere marcature W3CDTF scritte da altri generatori

Excel scrive Z, altri generatori non sempre lo fanno, e un lettore che controlla solo una Z al carattere 20 sbaglia due forme comuni. 2026-07-01T01:30:00.5Z ha una frazione di mezzo, e 2026-07-01T09:30:00+08:00 ha un offset esplicito. Un parser ingenuo legge entrambe come ora locale senza correzioni. Il lettore HotXLS per Created, Modified e le proprietà personalizzate con valore data accetta secondi frazionari di qualsiasi lunghezza, Z, e offset +hh:mm / -hh:mm; converte l'istante dall'offset dato in UTC e poi in ora locale. Una marcatura solo data come 2026-07-01 si legge come quella data a mezzanotte, e una marcatura che non si analizza affatto torna come zero

L'unico caso che le specifiche lasciano aperto è un'ora senza alcun marcatore di fuso, come 2026-07-01T09:30:00. Il profilo W3CDTF richiede un indicatore di fuso ogni volta che è presente un'ora, e ECMA-376 Part 2 non dà regole per una marcatura che lo omette. HotXLS legge un tale valore come ora locale, invariato. Trattarlo come UTC sarebbe stato altrettanto difendibile sulla carta, ma la fonte più probabile di una marcatura senza fuso è uno strumento che ha scritto ora locale lasciando fuori il marcatore, e spostare quei valori muoverebbe ogni timestamp di quello strumento dell'offset del fuso

Nessun lettore può salvare l'errore opposto. Una marcatura scritta come ora locale e con una Z addosso sembra esattamente una corretta, quindi HotXLS, Excel e qualsiasi altro lettore che segua il formato la spostano dell'offset del fuso. Solo la codifica dell'edit time e il piazzamento a $0E nei .xls hanno una firma che un lettore può rilevare

Testare contro byte che il vostro codice non ha scritto

Ogni trappola qui sopra passa un controllo salva-poi-riapri, perché uno sbaglio simmetrico è invisibile a un test simmetrico. Il riferimento deve venire da un altro posto. Per .xlsx, decomprimete il file salvato e fate assert sul testo letterale di dcterms:created; per .xls, il pacchetto olefile di Python legge lo stream SummaryInformation in modo indipendente con get_metadata(), riporta l'edit time in secondi e tratta $0E come conteggio pagine, il che rende entrambe le trappole .xls visibili in una volta. L'altra direzione è una cartella di lavoro salvata da Excel stesso, letta attraverso l'API e confrontata con ciò che File > Info mostra

uses
  System.SysUtils, System.DateUtils;

procedure CheckExcelReference;
var
  Book: TXLSXWorkbook;
  Expected: TDateTime;
begin
  // Il valore che File > Info di Excel mostra per questo file sulla macchina di test
  Expected := EncodeDate(2026, 1, 20) + EncodeTime(17, 0, 0, 0);
  Book := TXLSXWorkbook.Create;
  try
    if Book.Open('excel-saved-reference.xlsx') <> 1 then
      raise Exception.Create('Reference file missing');
    // Controllo vostro, non è un'API HotXLS: concedete un secondo per l'arrotondamento
    if Abs(Book.Created - Expected) > OneSecond then
      raise Exception.CreateFmt('Created reads as %s',
        [DateTimeToStr(Book.Created)]);
  finally
    Book.Free;
  end;
end;

Eseguite quella suite su una macchina impostata su un fuso ben lontano da UTC, con una data di test su ciascun lato di un cambio di ora legale. Un build agent lasciato su UTC nasconde completamente l'errore di offset del fuso, e una singola data di test estiva nasconde quello di LocalFileTimeToFileTime; 20 gennaio e 1 luglio in Central European Time li intercettano entrambi

I sistemi di record, gli indici di ricerca e le tracce di audit ordinano per queste marcature, e una data fuori di un giorno o di otto ore fa più danno di una mancante, perché nessuno la mette in discussione. Con il componente foglio di calcolo HotXLS Delphi, la costante dell'epoca, gli ID di proprietà e la conversione UTC consapevole dell'ora legale sono gestiti sia per .xls sia per .xlsx, così il codice applicativo assegna e legge ordinari valori TDateTime locali. Per il resto del property set, dal titolo e autore alle proprietà personalizzate, il pezzo gemello su impostare le proprietà dei documenti Excel in Delphi copre i campi rimanenti