Excel se niega a abrir un workbook XLSX cuya validación de datos de lista contiene una lista inline de más de 255 caracteres, y eso se mantiene incluso para un archivo que el propio Excel guardó. 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 durable es el mismo en todas partes: conserve las listas largas en un rango de worksheet o en un nombre definido
El reporte de bug suele llegar de un cliente, no de sus pruebas. Una plantilla le crece una columna Region o Product cuyo desplegable arrancó con ocho entradas y ahora tiene sesenta. Todo sigue funcionando en la copia XLSB que alguien conserva en una unidad compartida. Luego un trabajo de exportación escribe la misma regla en un XLSX, y Excel responde con un diálogo de reparación o una negativa seca. Nada en su código cambió salvo la longitud de una cadena, que es exactamente por qué vale la pena entender esta frontera antes de que ella lo encuentre
¿Dónde aplica en realidad el límite de 255 caracteres de la lista?
El límite aplica a la forma inline de una validación de lista en XLSX, la cadena entre comillas separada por comas guardada en formula1, y Excel la hace cumplir al abrir el archivo, no solo cuando usted teclea 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 las de 260 y 421 caracteres fueron rechazadas. El caso de 421 caracteres es el instructivo, porque vino de un workbook que el propio Excel había creado. Excel escribe un archivo que Excel mismo no va a releer
El esquema no lo salva aquí. ECMA-376 Parte 1, §18.3.1.32 define el elemento dataValidation y sus fórmulas sin enunciar un techo de 255 caracteres, así que un archivo puede ser válido según el esquema y aun así ser rechazado. Esa es una restricción de la aplicación, y la única referencia confiable para ella es el comportamiento del propio Excel
HotXLS no le hace cumplir ese techo. TXLSXWorksheet.AddListValidation envuelve los elementos en comillas y los guarda tal cual; ni trunca ni rechaza una lista larga, así que una lista de 400 caracteres entra al 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 deja la verificación de longitud de su lado. Un helper pequeño la mantiene honesta:
const
// Excel abrió una lista de 253 caracteres y rechazó las de 260 y 421.
// Guarde un 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;Trate las comillas dobles incrustadas como un costo de dos caracteres, porque AddListValidation las duplica para mantener válida la cadena de fórmula
Por qué XLSB puede llevar listas que XLSX no
XLSB guarda 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 sobrevive a ida y vuelta en Excel sin quejas. 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. La verificación 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 salvedades valen enunciarlas sin rodeos. El loader también 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 la verificación de token sola sugiere. Y las listas separadas por caracteres NUL dentro de un PtgStr quedan fuera del perfil soportado, así que se reportan como no soportadas en vez de adivinarse
La trampa está en la conversión. Una lista larga perfectamente sana en XLSB se vuelve un archivo rechazado en el momento en que cualquier cosa, HotXLS incluido, guarde ese workbook como XLSX. Si un pipeline convierte archivos XLSB de clientes, corra la verificación de longitud sobre cada regla de lista antes del guardado XLSX, no después de que un cliente lo reporte
¿Qué pasa con las listas que apuntan a otra hoja?
Excel guarda una lista cuya fuente está en un worksheet distinto en la extensión x14:dataValidations, no en el elemento ordinario dataValidations. La regla vive dentro del extLst del worksheet 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 como 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 en la colección tipada DataValidations y marca cada una con TXLSXDataValidation.IsExtension. Una lista entre hojas aparece por tanto junto a las reglas ordinarias 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 ordinario y reconstruye la extensión x14 en su posición de esquema al final del worksheet. 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 su título y texto de mensaje de entrada, su título y texto de alerta de error, el estilo de alerta, el modo IME y la opción de ocultar el desplegable a través de una carga y re-guardado, con las mismas reglas de escape que una validación 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[] 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 verificar la entrada tecleada contra estas reglas, vea leer elementos de 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 desnudo sin atributo type, descartando el atributo desconocido. Tales 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 descartar la regla, lo que la perdería al re-guardar sin rastro, el lector la conserva como xlsxDvNone con sus fórmulas y rango, y registra 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 desnudo, la misma forma que produce Excel. Una regla none no valida nada, así que el diagnóstico es su 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 workbook tras un viaje de ida y vuelta?
Excel rechaza un workbook re-guardado cuando un fragmento de extensión re-reproducido usa un prefijo de espacio de nombres que el archivo nuevo nunca declara, porque el resultado no es XML bien formado. Los fragmentos de extensión suelen llevar atributos con prefijo como xr:uid, cuya declaración xmlns:xr vive en la raíz del worksheet original, no dentro del fragmento. Copie el fragmento textualmente a una raíz de worksheet nueva que carece de esa declaración y las reglas de espacios de nombres XML quedan rotas antes de que Excel mire una sola celda
HotXLS maneja esto en el paso de captura. Mientras lee, escanea cada fragmento re-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 del worksheet al momento de guardar. La ruta de guardado en streaming construye la raíz por la misma rutina, así que ambos modos de guardado producen salida bien formada, y un archivo re-guardado reabre en Excel con cada regla de validación presente, incluida una lista x14 entre hojas re-reproducida. El panorama más amplio de cómo HotXLS transporta contenido extLst que no modela está cubierto en viajes de ida y vuelta XLSX sin pérdida para themes, extLst y calcChain, y las reglas generales detrás del diálogo de reparación de Excel están en por qué Excel muestra un diálogo de reparación
Las listas largas pertenecen a un rango o a un nombre definido
El diseño robusto para cualquier lista que pueda crecer es guardar los elementos en celdas del worksheet y apuntar la validación hacia ellas mediante un nombre definido. Una fuente de rango no tiene límite de 255 caracteres en ningún formato, los elementos se pueden editar sin tocar la regla, y un nombre definido mantiene la regla en el elemento ordinario dataValidation incluso cuando las celdas están en una hoja aparte, que todo 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 cita como texto
Result := Target.AddListValidation(ARange, '=' + AName);
end;Ese signo igual inicial importa. AddListValidation trata un argumento que empieza con = como una referencia y le quita el signo, deja un argumento que contiene ! sin comillas como rango, y cita todo lo demás como lista inline. Pase 'StatusList' sin el signo y obtiene un desplegable de un elemento que literalmente dice StatusList. Dos puntos menores: WriteAutoValue convierte texto como lo hace la entrada de celda de Excel, así que un elemento como TRUE se vuelve un 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, ponga IsExtension := True en la regla del índice que devolvió AddListValidation, y HotXLS la escribe como la extensión x14, la misma forma que el propio Excel usa. La ruta del nombre definido es más simple y sigue funcionando para herramientas que nunca parsean extensiones, razón por la 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