O Excel se recusa a abrir uma pasta de trabalho XLSX cuja validação de dados de lista guarda uma lista inline com mais de 255 caracteres, e isso vale até para um arquivo que o próprio Excel salvou. O HotXLS, o componente Excel nativo para Delphi e C++Builder, carrega essas listas e modela regras de lista entre planilhas como extensões x14, mas a correção duradoura é a mesma em todo lugar: mantenha listas longas num range de planilha ou num defined name
O relatório de bug costuma chegar de um cliente, não dos seus testes. Um template ganha uma coluna Region ou Product cuja lista suspensa começou com oito entradas e agora tem sessenta. Tudo continua funcionando na cópia XLSB que alguém guarda num drive compartilhado. Aí um job de exportação grava a mesma regra num XLSX, e o Excel responde com um prompt de reparo ou uma recusa seca. Nada no seu código mudou exceto o comprimento de uma string, que é exatamente por que vale entender essa fronteira antes de ela te encontrar
Onde o limite de 255 caracteres de lista realmente se aplica?
O limite se aplica à forma inline de uma validação de lista no XLSX, a string entre aspas separada por vírgulas armazenada no formula1, e o Excel o impõe ao abrir o arquivo, não só quando você digita a lista no diálogo Data Validation. Testar contra o Excel direto deu um resultado limpo: uma lista de 253 caracteres abriu, enquanto listas de 260 e de 421 caracteres foram recusadas. O caso de 421 caracteres é o instrutivo, porque veio de uma pasta de trabalho que o próprio Excel escreveu. O Excel grava um arquivo que o Excel não vai reler
O schema não te salva aqui. O ECMA-376 Part 1, §18.3.1.32, define o elemento dataValidation e as fórmulas dele sem declarar um teto de 255 caracteres, então um arquivo pode ser válido pelo schema e ainda assim ser recusado. Essa é uma restrição de aplicação, e a única referência confiável para ela é o comportamento do próprio Excel
O HotXLS não impõe esse teto por você. O TXLSXWorksheet.AddListValidation envolve os itens em aspas e os armazena como dados; ele nem trunca nem recusa uma lista longa, então uma lista de 400 caracteres entra no XLSX salvo exatamente como pedida e o Excel depois recusa o resultado. Essa é uma escolha deliberada, já que aparar uma lista em silêncio corromperia dados de um jeito que ninguém nota, mas põe a checagem de comprimento do seu lado. Um helper pequeno mantém tudo honesto:
const
// O Excel abriu uma lista de 253 caracteres e recusou 260 e 421.
// Mantenha uma margem em vez de mirar na borda exata.
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)); // vírgulas separadoras
end;Trate aspas duplas embutidas como custando dois caracteres, porque o AddListValidation as duplica para manter a string de fórmula válida
Por que o XLSB pode carregar listas que o XLSX não pode
O XLSB armazena uma lista inline como um único token de string PtgStr cujo comprimento é um campo de 16 bits, então o formato binário não tem parede de 255 caracteres. Uma amostra nativa com 60 itens e 419 caracteres vive num único token e faz ida e volta no Excel sem reclamar. A mesma lista no XLSX é o caso de 421 caracteres acima
Um loader que aplicasse o limite de 255 caracteres do diálogo ao XLSB recusaria esse arquivo. A checagem de token de validação XLSB do HotXLS limita um token de string a 32.767 caracteres em vez disso, o mesmo teto de uma string de célula do Excel. Duas qualificações valem ser ditas com clareza. O loader também limita uma fórmula de validação completa a 16.384 bytes, então o teto prático para uma lista inline é menor do que a checagem de token sozinha sugere. E listas separadas por caracteres NUL dentro de um PtgStr ficam fora do perfil suportado, então são reportadas como não suportadas em vez de chutadas
A armadilha fica na conversão. Uma lista longa que está perfeitamente saudável no XLSB vira um arquivo recusado no momento em que qualquer coisa, HotXLS incluído, salva essa pasta de trabalho como XLSX. Se um pipeline converte arquivos XLSB de clientes, rode a checagem de comprimento em toda regra de lista antes do save para XLSX, não depois de um cliente reclamar
O que acontece com listas que apontam para outra planilha?
O Excel armazena uma lista cuja fonte fica numa planilha diferente na extensão x14:dataValidations, não no elemento comum dataValidations. A regra vive dentro do extLst da planilha sob o URI de extensão {CCE6A557-97BC-4b89-ADB6-D9C93CAAB3DF}, definido no [MS-XLSX], e a referência de fonte dela é um elemento xm:f como Lists!$A$2:$A$61. O XLSB mantém a mesma regra nos próprios records de extensão dele
O HotXLS parseia essas regras de extensão XLSX na coleção tipada DataValidations e marca cada uma com TXLSXDataValidation.IsExtension. Uma lista entre planilhas então aparece ao lado das regras comuns com o range, o tipo e a fórmula legíveis, e ela sobrevive a edições, saves e reaberturas. No save, o writer mantém regras de extensão fora do elemento comum e reconstrói a extensão x14 na posição de schema dela, na cauda da planilha. As duas fórmulas são mantidas, então regras de número inteiro, decimal, comprimento de texto, data e hora usando between ou notBetween mantêm os dois limites
O perfil tipado de extensão cobre a regra inteira. Além de type, operator, allowBlank, as duas flags de show, o range e as duas fórmulas, uma regra de extensão mantém o título e o texto do prompt de entrada, o título e o texto do alerta de erro, o estilo do alerta, o modo IME e a configuração hide-dropdown através de uma carga e resave, com as mesmas regras de escaping de uma validação comum
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[] tem 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;Para ler os itens por trás de uma lista suspensa, selecionar entradas e conferir a entrada digitada contra essas regras, veja ler itens de listas suspensas e validar entrada com o HotXLS; este artigo fica nos formatos de armazenamento
Como o Excel e o HotXLS tratam tipos de validação desconhecidos
O Excel abre um dataValidation cujo atributo type guarda um token não reconhecido como uma regra de tipo none, mantém a fórmula dele, e no save o reescreve como um elemento dataValidation puro sem atributo type, descartando o atributo desconhecido. Tais tokens vêm de XML editado à mão, geradores de terceiros e recursos futuros do Office, e a resposta do Excel é tolerância, não rejeição
O HotXLS segue a mesma tolerância. Em vez de descartar a regra, o que a perderia no resave sem rastro nenhum, o reader a mantém como xlsxDvNone com as fórmulas e o range dela, e registra um diagnóstico de aviso por regra normalizada com código 1307, exposto como a constante xlsDiagnosticValidationTypeNormalized no lxDiagnostics. No save, uma regra none é gravada como o elemento puro, a mesma forma que o Excel produz. Uma regra none não valida nada, então o diagnóstico é o seu sinal de que uma regra que o autor acreditava ativa não restringe mais a entrada
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);Por que o Excel pode recusar uma pasta de trabalho após uma ida e volta?
O Excel recusa uma pasta de trabalho resalva quando um fragmento de extensão re-executado usa um prefixo de namespace que o novo arquivo nunca declara, porque o resultado não é XML bem formado. Fragmentos de extensão costumam carregar atributos com prefixo como xr:uid, cuja declaração xmlns:xr fica na raiz da planilha original, não dentro do fragmento. Copie o fragmento verbatim para uma raiz de planilha nova que não tem essa declaração e as regras de namespace XML quebram antes de o Excel olhar uma única célula
O HotXLS lida com isso na etapa de captura. Ao ler, ele varre cada fragmento re-executado procurando prefixos de elemento e atributo que o fragmento não declara por si, resolve cada prefixo para o URI dele pelo reader, e grava essas declarações na raiz da planilha na hora do save. O caminho de save streaming constrói a raiz pela mesma rotina, então os dois modos de save produzem saída bem formada, e um arquivo resalvo reabre no Excel com toda regra de validação presente, incluindo uma lista x14 entre planilhas re-executada. O quadro mais amplo de como o HotXLS carrega conteúdo extLst que não modela está coberto em idas e voltas XLSX lossless para themes, extLst e calcChain, e as regras gerais por trás do prompt de reparo do Excel estão em por que o Excel mostra um prompt de reparo
Listas longas pertencem a um range ou a um defined name
O design robusto para qualquer lista que possa crescer é armazenar os itens em células de planilha e apontar a validação para elas através de um defined name. Uma fonte de range não tem limite de 255 caracteres em nenhum dos formatos, os itens podem ser editados sem tocar a regra, e um defined name mantém a regra no elemento comum dataValidation mesmo quando as células ficam numa planilha separada, que todo consumidor do arquivo consegue ler
// Lists é uma planilha dedicada, ex.: 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]); // linha e coluna com base 1
ColRef := Chr(Ord('A') + ACol - 1); // colunas A..Z
Book.DefinedNames.Add(AName, Lists.Name + '!$' + ColRef + '$1:$' +
ColRef + '$' + IntToStr(Length(AItems)));
// O '=' inicial marca uma referência; sem ele o nome é citado como texto
Result := Target.AddListValidation(ARange, '=' + AName);
end;Esse sinal de igual inicial importa. O AddListValidation trata um argumento começando com = como referência e remove o sinal, deixa um argumento contendo ! sem aspas como range, e cita todo o resto como lista inline. Passe 'StatusList' sem o sinal e você recebe uma lista suspensa de um item que literalmente lê StatusList. Dois pontos menores: o WriteAutoValue converte texto do jeito que a entrada de célula do Excel faz, então um item como TRUE vira Boolean, e um nome de planilha contendo espaços precisa de aspas simples na referência
Para uma regra que precisa apontar direto para outra planilha sem nome, defina IsExtension := True na regra no índice que o AddListValidation devolveu, e o HotXLS a grava como a extensão x14, a mesma forma que o próprio Excel usa. A rota de defined name é mais simples e continua funcionando para ferramentas que nunca parseiam extensões, por isso é a recomendação padrão aqui
Todo o comportamento descrito aqui vem no HotXLS Delphi Excel component para Delphi, C++Builder e Lazarus, onde a página do produto lista os tipos de validação suportados, os formatos de arquivo e a referência completa de diagnósticos