HotXLS-lijstvalidatie: 255-tekengrens en x14-extensies

Excel weigert een XLSX-workbook te openen waarvan de list-datavalidatie een inline lijst langer dan 255 tekens bevat, en dat geldt zelfs voor een bestand dat Excel zelf opslaat. HotXLS, de native Excel-component voor Delphi en C++Builder, laadt die lijsten en modelleert cross-sheet-lijstregels als x14-extensies, maar de duurzame fix is overal dezelfde: bewaar lange lijsten in een werkbladbereik of een gedefinieerde naam

De bugmelding komt meestal van een klant, niet uit je eigen tests. Een template krijgt een Region- of Product-kolom waarvan de dropdown begon met acht items en er inmiddels zestig heeft. Alles werkt nog prima in de XLSB-kopie die iemand op een share bewaart. Dan schrijft een exportklus dezelfde regel weg naar een XLSX en antwoordt Excel met een reparatieprompt of een botte weigering. Er is niets in je code veranderd behalve de lengte van één string, en dat is precies waarom deze grens het begrijpen waard is voordat hij jou vindt

Waar geldt de 255-tekengrens voor lijsten eigenlijk?

De grens geldt voor de inline vorm van een lijstvalidatie in XLSX, de geciteerde, door komma's gescheiden string in formula1, en Excel handhaaft haar bij het openen van het bestand, niet alleen wanneer je de lijst typt in het Data Validation-dialog. Direct tegen Excel getest gaf een heldere uitslag: een lijst van 253 tekens opende, terwijl lijsten van 260 en 421 tekens werden geweigerd. Het geval van 421 tekens is het leerzame, want het kwam uit een workbook dat Excel zelf had gemaakt. Excel schrijft dus een bestand weg dat Excel zelf niet terugleest

Het schema redt je hier niet. ECMA-376 Part 1, §18.3.1.32 definieert het dataValidation-element en zijn formules zonder een plafon van 255 tekens te noemen, dus een bestand kan schema-geldig zijn en toch worden geweigerd. Dat is een applicatiebeperking en de enige betrouwbare referentie daarvoor is het eigen gedrag van Excel

HotXLS handhaaft dat plafon niet voor je. TXLSXWorksheet.AddListValidation zet de items tussen aanhalingstekens en slaat ze op zoals aangeleverd; hij kapt niet af en weigert niet, dus een lijst van 400 tekens gaat precies zoals gevraagd de opgeslagen XLSX in en Excel weigert daarna het resultaat. Dat is een bewuste keuze, want stilletjes een lijst inkorten corrupteert data op een manier die niemand opmerkt, maar het zet de lengtecontrole aan jouw kant. Een kleine hulpfunctie houdt het eerlijk:

const
  // Excel opende een lijst van 253 tekens en weigerde 260 en 421 tekens.
  // Houd een marge aan in plaats van op de exacte rand te mikken.
  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));  // scheidende komma's
end;

Reken ingesloten dubbele aanhalingstekens als twee tekens, want AddListValidation verdubbelt ze om de formulestring geldig te houden

Waarom XLSB lijsten kan dragen die XLSX niet aankan

XLSB bewaart een inline lijst als één PtgStr-stringtoken waarvan de lengte een 16-bit veld is, dus het binaire formaat kent geen muur van 255 tekens. Een native voorbeeld met 60 items en 419 tekens leeft in één token en roundtript zonder mopperen in Excel. Dezelfde lijst in XLSX is het hierboven genoemde geval van 421 tekens

Een loader die de 255-tekengrens uit het dialog op XLSB toepaste, zou dat bestand weigeren. De XLSB-validatietokencontrole van HotXLS begrenst een stringtoken in plaats daarvan op 32.767 tekens, hetzelfde plafon als een Excel-celstring. Twee kanttekeningen zijn het openlijk vermelden waard. De loader kapwt ook een complete validatieformule op 16.384 bytes, dus het praktische plafon voor één inline lijst ligt lager dan de tokencontrole alleen doet vermoeden. En lijsten die binnen een PtgStr door NUL-tekens worden gescheiden vallen buiten het ondersteunde profiel, dus ze worden als niet-ondersteund gemeld in plaats van ergens op te gokken

De valkuil zit in de conversie. Een lange lijst die in XLSB volkomen gezond is, wordt een geweigerd bestand op het moment dat er iets — HotXLS incluis — dat workbook als XLSX opslaat. Converteert een pipeline XLSB-bestanden van klanten, draai dan de lengtecontrole over elke lijstregel vóór de XLSX-opslag, niet pas nadat een klant het meldt

Wat gebeurt er met lijsten die naar een ander werkblad wijzen?

Excel bewaart een lijst waarvan de bron op een ander werkblad staat in de x14:dataValidations-extensie, niet in het gewone dataValidations-element. De regel leeft binnen de extLst van het werkblad onder de extensie-URI {CCE6A557-97BC-4b89-ADB6-D9C93CAAB3DF}, gedefinieerd in [MS-XLSX], en zijn bronverwijzing is een xm:f-element zoals Lists!$A$2:$A$61. XLSB bewaart dezelfde regel in zijn eigen extensierecords

HotXLS parset die XLSX-extensieregels in de getypeerde DataValidations-collectie en merkt elk aan met TXLSXDataValidation.IsExtension. Een cross-sheet-lijst komt daardoor naast gewone regels te staan, met zijn bereik, type en formule leesbaar, en hij overleeft bewerkingen, opslagen en heropeningen. Bij het opslaan houdt de writer extensieregels buiten het gewone element en bouwt de x14-extensie op zijn schemapositie aan de staart van het werkblad opnieuw op. Beide formules worden bewaard, dus regels voor geheel getal, decimaal, tekstlengte, datum en tijd die between of notBetween gebruiken, houden beide grenzen

Het getypeerde extensieprofiel dekt de hele regel. Naast type, operator, allowBlank, de twee show-vlaggen, het bereik en beide formules bewaart een extensieregel door een laden en heropslaan heen zijn titel en tekst van de invoerprompt, zijn titel en tekst van de error alert, de alertstijl, de IME-modus en de hide-dropdown-instelling, met dezelfde escaperegels als een gewone validatie

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[] is 1-based
    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;

Voor het lezen van de items achter een dropdown, het kiezen van items en het toetsen van getypte invoer aan deze regels, zie dropdownitems lezen en invoer valideren met HotXLS; dit artikel blijft bij de opslagformaten

Hoe Excel en HotXLS met onbekende validatietypes omgaan

Excel opent een dataValidation waarvan het type-attribuut een onbekend token bevat als een regel van type none, houdt zijn formule bij en herschrijft hem bij het opslaan als een kaal dataValidation-element zonder type-attribuut, waarbij het onbekende attribuut valt. Zulke tokens komen uit met de hand bewerkte XML, generatoren van derden en toekomstige Office-functies, en het antwoord van Excel is tolerantie, geen weigering

HotXLS volgt dezelfde tolerantie. In plaats van de regel te laten vallen — wat haar bij een heropslag spoorloos zou doen verdwijnen — houdt de reader haar vast als xlsxDvNone met zijn formules en bereik, en legt per genormaliseerde regel één waarschuwingsdiagnose vast met code 1307, beschikbaar als de constante xlsDiagnosticValidationTypeNormalized in lxDiagnostics. Bij het opslaan wordt een none-regel weggeschreven als het kale element, dezelfde vorm die Excel produceert. Een none-regel valideert niets, dus de diagnose is je teken dat een regel waarvan de auteur dacht dat hij actief was, de invoer niet langer beperkt

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);

Waarom kan Excel een workbook weigeren na een roundtrip?

Excel weigert een opnieuw opgeslagen workbook wanneer een opgespeeld extensiefragment een namespace-prefix gebruikt die het nieuwe bestand nooit declareert, want het resultaat is geen welgevormde XML. Extensiefragmenten slepen routinematig prefixen als xr:uid mee, waarvan de xmlns:xr-declaratie op de originele werkbladroot staat, niet binnen het fragment. Kopieer het fragment letterlijk naar een nieuwe werkbladroot zonder die declaratie en de XML-namespaceregels zijn gebroken voordat Excel ook maar naar één cel kijkt

HotXLS pakt dit aan in de vastlegstap. Bij het lezen scant hij elk opgespeeld fragment op element- en attribuutprefixen die het fragment zelf niet declareert, lost elk prefix via de reader op naar zijn URI en schrijft die declaraties bij het opslaan op de werkbladroot. Het streaming-opslagpad bouwt de root door dezelfde routine, dus beide opslagmodi leveren welgevormde output op, en een opnieuw opgeslagen bestand opent in Excel met elke validatieregel aanwezig, inclusief een opgespeelde cross-sheet x14-lijst. Het bredere beeld van hoe HotXLS extLst-inhoud die hij niet modelleert meedraagt, wordt behandeld in verliesvrije XLSX-roundtrips voor themes, extLst en calcChain, en de algemene regels achter de reparatieprompt van Excel staan in waarom Excel een reparatieprompt toont

Lange lijsten horen in een bereik of een gedefinieerde naam

Het robuuste ontwerp voor elke lijst die kan groeien, is de items in werkbladcellen bewaren en de validatie via een gedefinieerde naam erop richten. Een bereikbron kent in beide formaten geen grens van 255 tekens, de items zijn te bewerken zonder de regel aan te raken, en een gedefinieerde naam houdt de regel in het gewone dataValidation-element, zelfs als de cellen op een apart werkblad staan, wat elke consument van het bestand kan lezen

// Lists is een eigen werkblad, bijv. 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]);   // 1-based rij, kolom
  ColRef := Chr(Ord('A') + ACol - 1);               // kolommen A..Z
  Book.DefinedNames.Add(AName, Lists.Name + '!$' + ColRef + '$1:$' +
    ColRef + '$' + IntToStr(Length(AItems)));
  // Het voorafgaande '=' markeert een verwijzing; zonder heet de naam als tekst geciteerd
  Result := Target.AddListValidation(ARange, '=' + AName);
end;

Dat voorafgaande gelijkteken telt. AddListValidation behandelt een argument dat met = begint als een verwijzing en schrapt het teken, laat een argument met ! ongeciteerd als bereik en citeert al het restant als inline lijst. Geef je 'StatusList' zonder het teken mee, dan krijg je een dropdown van één item die letterlijk StatusList leest. Nog twee kleinere punten: WriteAutoValue converteert tekst zoals de celinvoer van Excel dat doet, dus een item zoals TRUE wordt een Boolean, en een werkbladnaam met spaties heeft enkele aanhalingstekens in de verwijzing nodig

Moet een regel rechtstreeks naar een ander werkblad wijzen zonder naam, zet dan IsExtension := True op de regel op de index die AddListValidation teruggaf, en HotXLS schrijft hem als de x14-extensie, dezelfde vorm die Excel zelf gebruikt. De route via de gedefinieerde naam is simpeler en blijft werken voor tools die nooit extensies parsen, en daarom is ze hier de standaardaanbeveling

Al het hier beschreven gedrag wordt meegeleverd met de HotXLS Delphi Excel-component voor Delphi, C++Builder en Lazarus, waar de productpagina de ondersteunde validatietypes, bestandsformaten en de volledige diagnosereferentie opsomt