Excel se niega a abrir un libro XLSX cuya validación de datos de lista contenga una lista inline de más de 255 caracteres, y eso se mantiene incluso para un archivo que Excel guardó él mismo. HotXLS, el componente Excel nativo para Delphi y C++Builder, carga esas listas y modela las reglas de lista entre hojas como extensiones x14, pero el arreglo duradero es el mismo en todas partes: guarda las listas largas en un rango de hoja o en un nombre definido
El bug report suele llegar de un cliente, no de tus tests. Una plantilla le añade una columna Región o Producto cuyo desplegable empezó con ocho entradas y ahora tiene sesenta. Todo sigue funcionando en la copia XLSB que alguien mantiene en una unidad compartida. Luego un job de exportación escribe la misma regla en un XLSX, y Excel responde con un prompt de reparación o una negativa redonda. Nada en tu código cambió salvo la longitud de una cadena, que es exactamente por qué conviene entender esta frontera antes de que te encuentre
¿Dónde se aplica realmente el límite de lista de 255 caracteres?
El límite se aplica a la forma inline de una validación de lista en XLSX, la cadena entrecomillada y separada por comas guardada en formula1, y Excel lo hace cumplir al abrir el archivo, no solo cuando tecleas la lista en el diálogo de Validación de datos. Probar contra Excel directamente dio un resultado limpio: una lista de 253 caracteres abrió, mientras que listas de 260 y 421 caracteres fueron rechazadas. El caso de 421 caracteres es el instructivo, porque vino de un libro que el propio Excel había escrito. Excel escribe un archivo que Excel no va a releer
El esquema no te salva aquí. ECMA-376 Parte 1, §18.3.1.32 define el elemento dataValidation y sus fórmulas sin declarar un techo de 255 caracteres, así que un archivo puede ser válido para el esquema y aun así ser rechazado. Es una restricción de aplicación, y la única referencia fiable para ella es el propio comportamiento de Excel
HotXLS no hace cumplir ese techo por ti. TXLSXWorksheet.AddListValidation envuelve los elementos en comillas y los almacena tal cual; ni trunca ni rechaza una lista larga, así que una lista de 400 caracteres entra en el XLSX guardado exactamente como se pidió y Excel luego rechaza el resultado. Es una elección deliberada, porque recortar una lista en silencio corrompería datos de una forma que nadie nota, pero pone el check de longitud de tu lado. Un helper pequeño lo mantiene honesto:
const
// Excel abrió una lista de 253 caracteres y rechazó las de 260 y 421.
// Deja margen en vez de apuntar al borde exacto.
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)); // comas separadoras
end;Trata las comillas dobles incrustadas como si costaran dos caracteres, porque AddListValidation las duplica para mantener válida la cadena de fórmula
Por qué XLSB puede llevar listas que XLSX no puede
XLSB almacena una lista inline como un único token de cadena PtgStr cuya longitud es un campo de 16 bits, así que el formato binario no tiene muro de 255 caracteres. Una muestra nativa con 60 elementos y 419 caracteres vive en un token y hace ida y vuelta en Excel sin queja. La misma lista en XLSX es el caso de 421 caracteres de arriba
Un loader que aplicara el límite de 255 caracteres del diálogo a XLSB rechazaría ese archivo. El check de tokens de validación XLSB de HotXLS acota un token de cadena en 32.767 caracteres en cambio, el mismo techo que una cadena de celda de Excel. Dos matizaciones conviene enunciarlas sin rodeos. El loader además acota una fórmula de validación completa en 16.384 bytes, así que el techo práctico para una lista inline es menor de lo que el check de token solo sugiere. Y las listas separadas por caracteres NUL dentro de un PtgStr caen fuera del perfil soportado, así que se reportan como no soportadas en vez de andar adivinando
La trampa vive en la conversión. Una lista larga perfectamente sana en XLSB se convierte en un archivo rechazado en el momento en que cualquier cosa, HotXLS incluido, guarda ese libro como XLSX. Si una pipeline convierte archivos XLSB de clientes, corre el check de longitud sobre cada regla de lista antes del guardado XLSX, no cuando un cliente lo reporte
¿Qué pasa con las listas que apuntan a otra hoja?
Excel guarda una lista cuya fuente vive en otra hoja de cálculo en la extensión x14:dataValidations, no en el elemento dataValidations corriente. La regla vive dentro del extLst de la hoja bajo el URI de extensión {CCE6A557-97BC-4b89-ADB6-D9C93CAAB3DF}, definido en [MS-XLSX], y su referencia de fuente es un elemento xm:f tipo Lists!$A$2:$A$61. XLSB conserva la misma regla en sus propios registros de extensión
HotXLS parsea esas reglas de extensión XLSX a la colección tipada DataValidations y marca cada una con TXLSXDataValidation.IsExtension. Una lista entre hojas aparece por tanto junto a las reglas corrientes con su rango, tipo y fórmula legibles, y sobrevive a ediciones, guardados y reaperturas. Al guardar, el writer mantiene las reglas de extensión fuera del elemento corriente y reconstruye la extensión x14 en su posición de esquema al final de la hoja. Ambas fórmulas se conservan, así que las reglas de número entero, decimal, longitud de texto, fecha y hora que usan between o notBetween conservan ambos límites
El perfil tipado de extensión cubre la regla entera. Además de type, operator, allowBlank, las dos banderas show, el rango y ambas fórmulas, una regla de extensión conserva el título y texto de su prompt de entrada, el título y texto de su alerta de error, el estilo de alerta, el modo IME y el ajuste de ocultar el desplegable a través de una carga y reguardado, con las mismas reglas de escape que una validación corriente
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[] es 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 leer los elementos detrás de un desplegable, seleccionar entradas y comprobar la entrada tecleada contra estas reglas, véase leer elementos desplegables y validar entrada con HotXLS; este artículo se queda con los formatos de almacenamiento
Cómo tratan Excel y HotXLS los tipos de validación desconocidos
Excel abre un dataValidation cuyo atributo type contiene un token no reconocido como una regla de tipo none, conserva su fórmula, y al guardar lo reescribe como un elemento dataValidation pelado sin atributo type, soltando el atributo desconocido. Esos tokens vienen de XML editado a mano, generadores de terceros y funciones futuras de Office, y la respuesta de Excel es tolerancia, no rechazo
HotXLS sigue la misma tolerancia. En vez de soltar la regla, que la perdería al reguardar sin dejar rastro, el lector la conserva como xlsxDvNone con sus fórmulas y rango, y anota un diagnóstico de advertencia por regla normalizada con código 1307, expuesto como la constante xlsDiagnosticValidationTypeNormalized en lxDiagnostics. Al guardar, una regla none se escribe como el elemento pelado, la misma forma que produce Excel. Una regla none no valida nada, así que el diagnóstico es tu señal de que una regla que el autor creía activa ya no restringe la 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 qué Excel puede rechazar un libro tras una ida y vuelta?
Excel rechaza un libro reguardado cuando un fragmento de extensión reproducido usa un prefijo de namespace que el archivo nuevo jamás declara, porque el resultado no es XML bien formado. Los fragmentos de extensión llevan con regularidad atributos con prefijo como xr:uid, cuya declaración xmlns:xr vive en la raíz de la hoja original, no dentro del fragmento. Copia el fragmento literal a una raíz de hoja nueva que carece de esa declaración y las reglas de namespaces de XML quedan rotas antes de que Excel mire una sola celda
HotXLS maneja esto en el paso de captura. Al leer, escanea cada fragmento reproducido buscando prefijos de elementos y atributos que el fragmento no declara por sí mismo, resuelve cada prefijo a su URI a través del lector, y escribe esas declaraciones en la raíz de la hoja al guardar. La ruta de guardado en streaming construye la raíz con la misma rutina, así que ambos modos de guardado producen salida bien formada, y un archivo reguardado reabre en Excel con todas las reglas de validación presentes, incluida una lista x14 entre hojas reproducida. La imagen más amplia de cómo HotXLS transporta contenido extLst que no modela queda cubierta en idas y vueltas XLSX sin pérdida para themes, extLst y calcChain, y las reglas generales detrás del prompt de reparación de Excel están en por qué Excel muestra un prompt de reparación
Las listas largas van en un rango o un nombre definido
El diseño robusto para cualquier lista que pueda crecer es almacenar los elementos en celdas de hoja y apuntar la validación a ellas mediante un nombre definido. Una fuente de rango no tiene límite de 255 caracteres en ninguno de los dos formatos, los elementos se pueden editar sin tocar la regla, y un nombre definido mantiene la regla en el elemento dataValidation corriente incluso cuando las celdas viven en una hoja aparte, cosa que cualquier consumidor del archivo puede leer
// Lists es una hoja dedicada, p. ej. 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]); // fila y columna base-1
ColRef := Chr(Ord('A') + ACol - 1); // columnas A..Z
Book.DefinedNames.Add(AName, Lists.Name + '!$' + ColRef + '$1:$' +
ColRef + '$' + IntToStr(Length(AItems)));
// El '=' inicial marca una referencia; sin él el nombre se entrecomilla como texto
Result := Target.AddListValidation(ARange, '=' + AName);
end;Ese signo igual inicial importa. AddListValidation trata un argumento que empieza por = como una referencia y le quita el signo, deja sin entrecomillar un argumento que contiene ! como rango, y entrecomilla todo lo demás como lista inline. Pásale 'StatusList' sin el signo y obtienes un desplegable de un elemento que literalmente lee StatusList. Dos puntos menores: WriteAutoValue convierte texto como lo hace la entrada de celda de Excel, así que un elemento tipo TRUE se vuelve Boolean, y un nombre de hoja con espacios necesita comillas simples en la referencia
Para una regla que deba apuntar directo a otra hoja sin nombre, pon IsExtension := True en la regla del índice que devolvió AddListValidation, y HotXLS la escribe como la extensión x14, la misma forma que usa el propio Excel. La ruta del nombre definido es más sencilla y sigue funcionando para herramientas que jamás parsean extensiones, que es por lo que es la recomendación por defecto aquí
Todo el comportamiento descrito aquí se distribuye en el componente Excel HotXLS para Delphi para Delphi, C++Builder y Lazarus, donde la página del producto lista los tipos de validación soportados, los formatos de archivo y la referencia completa de diagnósticos