Validação de listas HotXLS: limite de 255 caracteres e x14

O Excel recusa abrir uma pasta de trabalho XLSX cuja validação de dados de lista guarde uma lista inline com mais de 255 caracteres, e isso vale até para um ficheiro 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 folhas como extensões x14, mas a correção duradoura é a mesma em todo o lado: mantenha listas longas num intervalo da folha de cálculo ou num nome definido

O relatório de bug costuma chegar de um cliente, não dos seus testes. Um template ganha uma coluna Region ou Product cujo dropdown começou com oito entradas e agora tem sessenta. Tudo continua a funcionar na cópia XLSB que alguém mantém numa drive partilhada. Depois um trabalho de exportação escreve a mesma regra num XLSX, e o Excel responde com um prompt de reparação ou uma recusa seca. Nada no seu código mudou além do comprimento de uma string, que é precisamente porque este limite merece ser compreendido antes de o encontrar

Onde se aplica de facto o limite de lista de 255 caracteres?

O limite aplica-se à forma inline de uma validação de lista em XLSX, a string entre aspas separada por vírgulas guardada no formula1, e o Excel impõe-no ao abrir o ficheiro, não só quando digita a lista no diálogo Data Validation. Testar contra o Excel diretamente deu um resultado limpo: uma lista de 253 caracteres abriu, enquanto listas de 260 e 421 caracteres foram recusadas. O caso de 421 caracteres é o instrutivo, porque veio de uma pasta de trabalho que o próprio Excel tinha escrito. O Excel escreve um ficheiro que o Excel não volta a ler

O schema não o salva aqui. O ECMA-376 Parte 1, §18.3.1.32 define o elemento dataValidation e as suas fórmulas sem enunciar um teto de 255 caracteres, por isso um ficheiro pode ser válido pelo schema e ainda assim ser recusado. É uma restrição de aplicação, e a única referência fiável para ela é o comportamento do próprio Excel

O HotXLS não impõe esse teto por si. O TXLSXWorksheet.AddListValidation envolve os itens em aspas e guarda-os como dados; nem trunca nem rejeita uma lista longa, por isso uma lista de 400 caracteres entra no XLSX salvo exatamente como pedida e o Excel depois recusa o resultado. É uma escolha deliberada, já que cortar uma lista silenciosamente corromperia dados de uma forma que ninguém nota, mas põe a verificação de comprimento do seu lado. Um pequeno helper mantém-no honesto:

const
  // O Excel abriu uma lista de 253 caracteres e recusou 260 e 421.
  // Mantenha uma margem em vez de mirar o limite exato.
  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 duplica-as para manter a string de fórmula válida

Porque o XLSB consegue carregar listas que o XLSX não consegue

O XLSB guarda uma lista inline como um único token de string PtgStr cujo comprimento é um campo de 16 bits, por isso 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 queixas. A mesma lista em XLSX é o caso de 421 caracteres acima

Um loader que aplicasse o limite de 255 caracteres do diálogo ao XLSB rejeitaria esse ficheiro. A verificação de token de validação XLSB do HotXLS limita um token de string a 32.767 caracteres, o mesmo teto de uma string de célula Excel. Duas qualificações merecem ser enunciadas claramente. O loader também capitula uma fórmula de validação completa em 16.384 bytes, por isso o teto prático para uma lista inline é mais baixo do que só a verificação de token sugere. E listas separadas por caracteres NUL dentro de um PtgStr ficam fora do perfil suportado, por isso são reportadas como não suportadas em vez de adivinhadas

A armadilha assenta na conversão. Uma lista longa perfeitamente saudável em XLSB torna-se um ficheiro recusado no momento em que qualquer coisa, HotXLS incluído, salva essa pasta de trabalho como XLSX. Se um pipeline converte ficheiros XLSB de clientes, corra a verificação de comprimento em cada regra de lista antes da gravação XLSX, não depois de um cliente reportar

Que acontece às listas que apontam para outra folha?

O Excel guarda uma lista cuja fonte assenta numa folha de cálculo diferente na extensão x14:dataValidations, não no elemento dataValidations comum. A regra vive dentro do extLst da folha sob o URI de extensão {CCE6A557-97BC-4b89-ADB6-D9C93CAAB3DF}, definido em [MS-XLSX], e a sua referência de fonte é um elemento xm:f como Lists!$A$2:$A$61. O XLSB mantém a mesma regra nos seus próprios registos de extensão

O HotXLS analisa essas regras de extensão XLSX na coleção tipada DataValidations e marca cada uma com TXLSXDataValidation.IsExtension. Uma lista entre folhas aparece portanto ao lado das regras comuns com o seu intervalo, tipo e fórmula legíveis, e sobrevive a edições, gravações e reaberturas. Na gravação, o writer mantém as regras de extensão fora do elemento comum e reconstrói a extensão x14 na sua posição de schema na cauda da folha de cálculo. Ambas as fórmulas são mantidas, por isso regras de número inteiro, decimal, comprimento de texto, data e hora que usem between ou notBetween mantêm ambos os limites

O perfil tipado de extensão cobre a regra inteira. Além de type, operator, allowBlank, as duas flags show, o intervalo e ambas as fórmulas, uma regra de extensão mantém o título e texto do seu prompt de entrada, o título e texto do seu alerta de erro, o estilo de alerta, o modo IME e a definição de esconder o dropdown através de uma carga e regravação, 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[] é 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 um dropdown, escolher entradas e verificar a entrada digitada contra estas regras, veja ler itens dropdown e validar entrada com o HotXLS; este artigo fica pelos 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 sua fórmula, e na gravação reescreve-a como um elemento dataValidation nu sem atributo type, largando o atributo desconhecido. Tais tokens vêm de XML editado à mão, geradores de terceiros e funcionalidades futuras do Office, e a resposta do Excel é tolerância, não rejeição

O HotXLS segue a mesma tolerância. Em vez de largar a regra, o que a perderia na regravação sem rasto, o reader mantém-na como xlsxDvNone com as suas fórmulas e intervalo, e registra um diagnóstico de aviso por regra normalizada com código 1307, exposto como a constante xlsDiagnosticValidationTypeNormalized em lxDiagnostics. Na gravação, uma regra none é escrita como o elemento nu, a mesma forma que o Excel produz. Uma regra none não valida nada, por isso o diagnóstico é o seu sinal de que uma regra que o autor achava ativa já não restringe 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);

Porque pode o Excel recusar uma pasta de trabalho após uma ida e volta?

O Excel recusa uma pasta de trabalho regravada quando um fragmento de extensão replicado usa um prefixo de namespace que o novo ficheiro nunca declara, porque o resultado não é XML bem formado. Fragmentos de extensão rotineiramente trazem atributos com prefixo como xr:uid, cuja declaração xmlns:xr assenta na raiz da folha original, não dentro do fragmento. Copie o fragmento verbatim para uma raiz de folha nova que não tenha essa declaração e as regras de namespace XML quebram-se antes de o Excel olhar para uma única célula

O HotXLS trata isto no passo de captura. Durante a leitura, varre cada fragmento replicado à procura de prefixos de elementos e atributos que o fragmento não declara ele próprio, resolve cada prefixo ao seu URI através do reader, e escreve essas declarações na raiz da folha na hora da gravação. O caminho de gravação streaming constrói a raiz através da mesma rotina, por isso ambos os modos de gravação produzem saída bem formada, e um ficheiro regravado reabre no Excel com cada regra de validação presente, incluindo uma lista x14 entre folhas replicada. O quadro mais alargado de como o HotXLS transporta 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 reparação do Excel estão em porque o Excel mostra um prompt de reparação

Listas longas pertencem a um intervalo ou a um nome definido

O desenho robusto para qualquer lista que possa crescer é guardar os itens em células da folha de cálculo e apontar a validação para eles através de um nome definido. Uma fonte de intervalo não tem limite de 255 caracteres em nenhum dos formatos, os itens podem ser editados sem tocar na regra, e um nome definido mantém a regra no elemento dataValidation comum mesmo quando as células assentam numa folha separada, que qualquer consumidor do ficheiro consegue ler

// Lists é uma folha 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, coluna 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 que começa por = como referência e corta o sinal, deixa um argumento contendo ! sem aspas como intervalo, e cita tudo o resto como lista inline. Passe 'StatusList' sem o sinal e obtém um dropdown de um item que literalmente lê StatusList. Dois pontos menores: o WriteAutoValue converte texto à maneira da entrada de células do Excel, por isso um item como TRUE torna-se um Boolean, e um nome de folha contendo espaços precisa de aspas simples na referência

Para uma regra que tem de apontar direto para outra folha sem nome, ponha IsExtension := True na regra no índice que o AddListValidation devolveu, e o HotXLS escreve-a como a extensão x14, a mesma forma que o próprio Excel usa. O caminho do nome definido é mais simples e continua a funcionar para ferramentas que nunca analisam extensões, e é por isso que é a recomendação por omissão aqui

Todo o comportamento descrito aqui viaja 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 ficheiro e a referência completa de diagnósticos