Excel rifiuta di aprire un workbook XLSX la cui convalida dati lista tiene una lista inline più lunga di 255 caratteri, e questo vale anche per un file salvato da Excel stesso. HotXLS, il componente Excel nativo per Delphi e C++Builder, carica quelle liste e modella le regole lista tra fogli come estensioni x14, ma la cura durevole è la stessa ovunque: tenere le liste lunghe in un intervallo di worksheet o in un nome definito
La segnalazione di bug di solito arriva da un cliente, non dai vostri test. Un template fa crescere una colonna Region o Product il cui dropdown parte da otto voci e ora ne ha sessanta. Tutto continua a funzionare nella copia XLSB che qualcuno tiene su una share di rete. Poi un job di export scrive la stessa regola in un XLSX, ed Excel risponde con un prompt di riparazione o un rifiuto secco. Nel vostro codice non è cambiato niente tranne la lunghezza di una stringa, ed è esattamente il motivo per cui questo confine merita di essere capito prima che trovi voi
Dove si applica davvero il limite della lista a 255 caratteri?
Il limite si applica alla forma inline di una validazione lista in XLSX, la stringa quotata e separata da virgole memorizzata in formula1, ed Excel la impone all'apertura del file, non solo quando digitate la lista nel dialogo Data Validation. Testando direttamente contro Excel il risultato è stato netto: una lista da 253 caratteri si apriva, mentre liste da 260 e 421 caratteri venivano rifiutate. Il caso da 421 caratteri è quello istruttivo, perché veniva da un workbook scritto da Excel stesso. Excel scrive volentieri un file che Excel stesso non rileggerà
Lo schema qui non vi salva. ECMA-376 Part 1, §18.3.1.32 definisce l'elemento dataValidation e le sue formule senza enunciare un tetto di 255 caratteri, così un file può essere valido per lo schema ed essere comunque rifiutato. È un vincolo applicativo, e l'unico riferimento affidabile per esso è il comportamento di Excel stesso
HotXLS non impone quel tetto per voi. TXLSXWorksheet.AddListValidation avvolge le voci tra virgolette e le memorizza come date; non tronca né rifiuta una lista lunga, quindi una lista da 400 caratteri finisce nell'XLSX salvato esattamente come richiesta e Excel poi rifiuta il risultato. È una scelta deliberata, dato che accorciare una lista in silenzio corromperebbe dati in un modo che nessuno nota, ma mette il controllo di lunghezza dal vostro lato. Un piccolo helper lo tiene onesto:
const
// Excel ha aperto una lista di 253 caratteri e rifiutato quelle da 260 e 421.
// Tenete un margine invece di puntare al bordo esatto.
MaxInlineListChars = 250;
function InlineListLength(const AItems: array of WideString): Integer;
var
I: Integer;
begin
Result := 0;
for I := 0 to High(AItems) do
Inc(Result, Length(AItems[I]));
if Length(AItems) > 1 then
Inc(Result, High(AItems)); // virgole di separazione
end;Trattate i doppi apici incorporati come costanti due caratteri, perché AddListValidation li raddoppia per tenere valida la stringa formula
Perché XLSB può portare liste che XLSX non può
XLSB memorizza una lista inline come un singolo token stringa PtgStr la cui lunghezza è un campo a 16 bit, quindi il formato binario non ha un muro a 255 caratteri. Un campione nativo con 60 voci e 419 caratteri sta in un solo token e fa il round trip in Excel senza proteste. La stessa lista in XLSX è il caso da 421 caratteri di sopra
Un loader che applicasse il limite del dialogo di 255 caratteri a XLSB rifiuterebbe quel file. Il controllo dei token di validazione XLSB HotXLS delimita un token stringa a 32.767 caratteri invece, lo stesso tetto di una stringa di cella Excel. Due precisazioni meritano di essere dette chiare. Il loader tappa anche una formula di validazione completa a 16.384 byte, quindi il tetto pratico per una lista inline è più basso di quanto suggerisca il solo controllo del token. E le liste separate da caratteri NUL dentro un PtgStr cadono fuori dal profilo supportato, quindi vengono riportate come non supportate invece di essere tirate a indovinare
La trappola sta nella conversione. Una lista lunga perfettamente sana in XLSB diventa un file rifiutato nel momento in cui qualunque cosa, HotXLS incluso, salva quel workbook come XLSX. Se una pipeline converte file XLSB di clienti, eseguite il controllo di lunghezza su ogni regola lista prima del salvataggio XLSX, non dopo che un cliente lo segnala
Cosa succede alle liste che puntano a un altro foglio?
Excel memorizza una lista la cui sorgente sta su un worksheet diverso nell'estensione x14:dataValidations, non nel comune elemento dataValidations. La regola vive dentro l'extLst del worksheet sotto l'URI di estensione {CCE6A557-97BC-4b89-ADB6-D9C93CAAB3DF}, definito in [MS-XLSX], e il suo riferimento sorgente è un elemento xm:f tipo Lists!$A$2:$A$61. XLSB conserva la stessa regola nei suoi record di estensione
HotXLS fa il parsing di quelle regole estensione XLSX nella collezione tipizzata DataValidations e contrassegna ognuna con TXLSXDataValidation.IsExtension. Una lista tra fogli quindi compare accanto alle regole ordinarie con intervallo, tipo e formula leggibili, e sopravvive a modifiche, salvataggi e riaperture. Al salvataggio, lo writer tiene le regole estensione fuori dall'elemento ordinario e ricostruisce l'estensione x14 alla sua posizione di schema in coda al worksheet. Entrambe le formule sono conservate, quindi le regole numero intero, decimale, lunghezza testo, data e ora che usano between o notBetween conservano entrambi i confini
Il profilo tipizzato dell'estensione copre la regola intera. Oltre a type, operator, allowBlank, le due flag show, l'intervallo e entrambe le formule, una regola estensione conserva il titolo e il testo del prompt di input, il titolo e il testo del suo avviso di errore, lo stile dell'avviso, la modalità IME e l'impostazione hide-dropdown attraverso un caricamento e un risalvataggio, con le stesse regole di escaping di una validazione ordinaria
uses
SysUtils, lxHandleX, lxDiagnostics;
procedure AuditListRules(const FileName: WideString);
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
Dv: TXLSXDataValidation;
S, I: Integer;
begin
Book := TXLSXWorkbook.Create;
try
if Book.Open(FileName) <> 1 then
raise Exception.Create('Cannot open ' + FileName);
for S := 1 to Book.Sheets.Count do // Sheets[] è a base 1
begin
Sheet := Book.Sheets[S];
for I := 0 to Sheet.DataValidations.Count - 1 do
begin
Dv := Sheet.DataValidations[I];
if Dv.ValidationType <> xlsxDvList then
Continue;
if Dv.IsExtension then
Writeln(Sheet.Name, ' ', Dv.Range, ': x14 list from ', Dv.Formula1)
else if (Dv.Formula1 <> '') and (Dv.Formula1[1] = '"') and
(Length(Dv.Formula1) - 2 > MaxInlineListChars) then
Writeln(Sheet.Name, ' ', Dv.Range, ': inline list of ',
Length(Dv.Formula1) - 2, ' chars, refused by Excel as XLSX');
end;
end;
finally
Book.Free;
end;
end;Per leggere le voci dietro un dropdown, selezionare voci e controllare l'input digitato contro queste regole, vedete leggere voci dropdown e validare input con HotXLS; questo articolo resta sui formati di memorizzazione
Come Excel e HotXLS trattano i tipi di validazione sconosciuti
Excel apre un dataValidation il cui attributo type tiene un token non riconosciuto come regola di tipo none, ne conserva la formula, e al salvataggio la riscrive come un elemento dataValidation nudo senza attributo type, lasciando cadere l'attributo sconosciuto. Token simili arrivano da XML editato a mano, generatori di terze parti e funzionalità Office future, e la risposta di Excel è tolleranza, non rifiuto
HotXLS segue la stessa tolleranza. Invece di buttare la regola, cosa che la perderebbe al risalvataggio senza lasciare traccia, il reader la conserva come xlsxDvNone con le sue formule e il suo intervallo, e registra un diagnostico di warning per regola normalizzata con codice 1307, esposto come costante xlsDiagnosticValidationTypeNormalized in lxDiagnostics. Al salvataggio, una regola none viene scritta come elemento nudo, la stessa forma che produce Excel. Una regola none non valida niente, quindi il diagnostico è il vostro indizio che una regola che l'autore credeva attiva non restringe più l'input
for I := 0 to Book.Diagnostics.Count - 1 do
if Book.Diagnostics[I].Code = xlsDiagnosticValidationTypeNormalized then
Writeln('Untyped rule kept on sheet ', Book.Diagnostics[I].SheetName,
': ', Book.Diagnostics[I].Message);Perché Excel può rifiutare un workbook dopo un round trip?
Excel rifiuta un workbook risalvato quando un frammento estensione rigiocato usa un prefisso di namespace che il nuovo file non dichiara mai, perché il risultato non è XML ben formato. I frammenti estensione portano di routine attributi con prefisso come xr:uid, la cui dichiarazione xmlns:xr sta sulla radice del worksheet originale, non dentro il frammento. Copiate il frammento alla lettera dentro una radice di worksheet nuova priva di quella dichiarazione e le regole dei namespace XML sono già rotte prima che Excel guardi una sola cella
HotXLS gestisce questo nel passo di cattura. Mentre legge, scansiona ogni frammento rigiocato per prefissi di elementi e attributi che il frammento non dichiara da sé, risolve ogni prefisso al suo URI attraverso il reader, e scrive quelle dichiarazioni sulla radice del worksheet al momento del salvataggio. Il percorso di salvataggio in streaming costruisce la radice attraverso la stessa routine, così entrambe le modalità di salvataggio producono output ben formato, e un file risalvato si riapre in Excel con ogni regola di validazione presente, inclusa una lista x14 tra fogli rigiocata. Il quadro più ampio di come HotXLS trasporta contenuto extLst che non modella è trattato in round trip XLSX senza perdite per temi, extLst e calcChain, e le regole generali dietro il prompt di riparazione di Excel sono in perché Excel mostra un prompt di riparazione
Le liste lunghe stanno in un intervallo o in un nome definito
Il progetto robusto per qualunque lista che possa crescere è memorizzare le voci in celle del worksheet e puntare la validazione su di esse attraverso un nome definito. Una sorgente a intervallo non ha limite di 255 caratteri in nessuno dei due formati, le voci possono essere modificate senza toccare la regola, e un nome definito tiene la regola nel comune elemento dataValidation anche quando le celle stanno su un foglio separato, cosa che ogni consumatore del file sa leggere
// Lists è un foglio dedicato, per esempio Book.Sheets.Add('Lists')
function AddListRule(Book: TXLSXWorkbook; Target, Lists: TXLSXWorksheet;
ACol: Integer; const ARange, AName: WideString;
const AItems: array of WideString): Integer;
var
I: Integer;
ColRef: WideString;
begin
if InlineListLength(AItems) <= MaxInlineListChars then
begin
Result := Target.AddListValidation(ARange, AItems);
Exit;
end;
for I := 0 to High(AItems) do
Lists.WriteAutoValue(I + 1, ACol, AItems[I]); // riga e colonna a base 1
ColRef := Chr(Ord('A') + ACol - 1); // colonne A..Z
Book.DefinedNames.Add(AName, Lists.Name + '!$' + ColRef + '$1:$' +
ColRef + '$' + IntToStr(Length(AItems)));
// L'uguale iniziale '=' segna un riferimento; senza di esso il nome viene quotato come testo
Result := Target.AddListValidation(ARange, '=' + AName);
end;Quell'uguale iniziale conta. AddListValidation tratta un argomento che comincia con = come riferimento e toglie il segno, lascia un argomento contenente ! non quotato come intervallo, e quota tutto il resto come lista inline. Passate 'StatusList' senza il segno e ottenete un dropdown di una voce che recita alla lettera StatusList. Due punti minori: WriteAutoValue converte il testo come fa l'immissione di cella di Excel, quindi una voce tipo TRUE diventa un Boolean, e un nome foglio contenente spazi vuole apostrofi singoli nel riferimento
Per una regola che deve puntare dritta a un altro foglio senza un nome, impostate IsExtension := True sulla regola all'indice restituito da AddListValidation, e HotXLS la scrive come estensione x14, la stessa forma che usa Excel stesso. La strada del nome definito è più semplice e continua a funzionare per gli strumenti che non fanno mai il parsing delle estensioni, ecco perché è la raccomandazione predefinita qui
Tutto il comportamento descritto qui viaggia nell'HotXLS Delphi Excel component per Delphi, C++Builder e Lazarus, dove la pagina del prodotto elenca i tipi di validazione supportati, i formati file e il riferimento completo della diagnostica