HotXLS-listvalidering: 255-teckensgräns och x14-tillägg

Excel vägrar öppna en XLSX-arbetsbok vars listdatavalidering håller en inline-lista längre än 255 tecken, och det gäller även för en fil Excel sparat själv. HotXLS, den nativa Excel-komponenten för Delphi och C++Builder, laddar de listorna och modellerar listregler över arken som x14-tillägg, men den varaktiga fixen är samma överallt: håll långa listor i ett kalkylbladsområde eller ett definierat namn

Bugrapporten kommer oftast från en kund, inte från dina tester. En mall får en Region- eller Product-kolumn vars dropdown började på åtta poster och nu har sextio. Allting funkar fortfarande i XLSB-kopian någon har på en delad enhet. Sedan skriver ett exportjobb samma regel till en XLSX, och Excel svarar med en reparationsdialog eller ett blankt vägran. Ingenting i din kod ändrades utom längden på en sträng, vilket är precis varför den gränsen är värd att förstå innan den hittar dig

Var gäller egentligen 255-teckenslistgränsen?

Gränsen gäller inline-formen av en listvalidering i XLSX, den citerade, kommaavskilda strängen lagrad i formula1, och Excel driver den när filen öppnas, inte bara när du skriver listan i Data Validation-dialogen. Tester mot Excel direkt gav ett rent resultat: en 253-teckens lista öppnades, medan 260-teckens och 421-teckens listor avvisades. 421-teckenfallet är det lärorika, för det kom från en arbetsbok Excel själv författat. Excel skriver en fil som Excel inte läser tillbaka

Schemat räddar dig inte här. ECMA-376 Part 1, §18.3.1.32 definierar dataValidation-elementet och dess formler utan att ange något 255-teckenstak, så en fil kan vara schemagiltig och ändå avvisas. Det är ett applikationsvillkor, och den enda pålitliga referensen för det är Excels eget beteende

HotXLS driver inte den taket åt dig. TXLSXWorksheet.AddListValidation sveper in posterna i citattecken och lagrar dem som givna; den trunkerar varken eller avvisar en lång lista, så en 400-teckens lista går in i den sparade XLSX:en exakt som begärt och Excel vägrar sedan resultatet. Det är ett medvetet val, eftersom att tyst klippa en lista skulle korrumpera data på ett sätt ingen märker, men det lägger längdkontrollen på din sida. En liten hjälpare håller den ärlig:

const
  // Excel öppnade en 253-teckens lista och avvisade 260 och 421 tecken.
  // Håll en marginal i stället för att sikta på den exakta kanten.
  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));  // skiljande kommatecken
end;

Behandla inbäddade dubbla citattecken som kostande två tecken, för AddListValidation dubblar dem för att hålla formelsträngen giltig

Varför kan XLSB bära listor som XLSX inte kan?

XLSB lagrar en inline-lista som en enda PtgStr-strängtoken vars längd är ett 16-bitarsfält, så det binära formatet har ingen 255-teckensmur. Ett nativt exempel med 60 poster och 419 tecken bor i en token och tar en roundtrip i Excel utan klagan. Samma lista i XLSX är 421-teckenfallet ovan

En laddare som tillämpade dialogens 255-teckensgräns på XLSB skulle avvisa den filen. HotXLS:s XLSB-valideringstokenkontroll avgränsar en strängtoken vid 32 767 tecken i stället, samma tak som en Excel-cellsträng. Två förbehåll är värda att säga rakt ut. Laddaren takar också en komplett valideringsformel vid 16 384 byte, så det praktiska taket för en inline-lista är lägre än tokenkontrollen ensam antyder. Och listor skilda av NUL-tecken inuti en PtgStr faller utanför den stödda profilen, så de rapporteras som ostödda i stället för att gissas fram

Fällan sitter i konverteringen. En lång lista som är helt frisk i XLSB blir en avvisad fil i samma stund vad som helst, HotXLS inkluderat, sparar den arbetsboken som XLSX. Om en pipeline konverterar kund-XLSB-filer, kör längdkontrollen på varje listregel före XLSX-sparningen, inte efter att en kund rapporterat det

Vad händer med listor som pekar på ett annat ark?

Excel lagrar en lista vars källa ligger på ett annat kalkylblad i x14:dataValidations-tillägget, inte i det vanliga dataValidations-elementet. Regeln bor inuti kalkylbladets extLst under tilläggs-URI:n {CCE6A557-97BC-4b89-ADB6-D9C93CAAB3DF}, definierad i [MS-XLSX], och dess källreferens är ett xm:f-element som Lists!$A$2:$A$61. XLSB behåller samma regel i sina egna tilläggsposter

HotXLS parsar de XLSX-tilläggsreglerna till den typade DataValidations-samlingen och flaggar var och en med TXLSXDataValidation.IsExtension. En lista över arken syns därför bredvid vanliga regler med sitt område, typ och formel läsbara, och den överlever ändringar, sparningar och återöppningar. Vid sparning håller skrivaren tilläggsregler utanför det vanliga elementet och bygger x14-tillägget på nytt på sin schemaposition i kalkylbladets svans. Båda formlerna behålls, så heltals-, decimal-, textlängd-, datum- och tidsregler som använder between eller notBetween behåller båda gränserna

Den typade tilläggsprofilen täcker hela regeln. Förutom type, operator, allowBlank, de två show-flaggorna, området och båda formlerna behåller en tilläggsregel sin inmatningsprompts titel och text, sin felvarnings titel och text, varningsstilen, IME-läget och hide-dropdown-inställningen genom en laddning och omsparning, med samma escape-regler som en vanlig validering

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[] är 1-baserad
    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;

För att läsa posterna bakom en dropdown, välja poster och kontrollera skriven inmatning mot de reglerna, se att läsa dropdownposter och validera inmatning med HotXLS; den här artikeln stannar hos lagringsformaten

Hur behandlar Excel och HotXLS okända valideringstyper?

Excel öppnar en dataValidation vars type-attribut håller en oigenkänd token som en regel av typ none, behåller dess formel, och skriver vid sparning om den som ett bart dataValidation-element utan type-attribut, och släpper det okända attributet. Sådana tokens kommer från handredigerad XML, tredjepartsgeneratorer och framtida Office-funktioner, och Excels svar är tolerans, inte avvisning

HotXLS följer samma tolerans. I stället för att släppa regeln, vilket skulle tappa den vid omsparning utan spår, behåller läsaren den som xlsxDvNone med sina formler och område, och registrerar en varningsdiagnostik per normaliserad regel med kod 1307, exponerad som konstanten xlsDiagnosticValidationTypeNormalized i lxDiagnostics. Vid sparning skrivs en none-regel som det barta elementet, samma form Excel producerar. En none-regel validerar ingenting, så diagnosen är din ledtråd till att en regel författaren trodde var aktiv inte längre begränsar inmatning

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

Varför kan Excel avvisa en arbetsbok efter en roundtrip?

Excel avvisar en omsparad arbetsbok när ett uppspelat tilläggsfragment använder ett namnrymdsprefix som den nya filen aldrig deklarerar, för resultatet inte är välformad XML. Tilläggsfragment bär rutinmässigt prefixade attribut som xr:uid, vars xmlns:xr-deklaration sitter på kalkylbladsroten från början, inte inuti fragmentet. Kopiera fragmentet ordagrant till en ny kalkylbladsrot som saknar den deklarationen och XML-namnrymdsreglerna är brutna innan Excel tittar på en enda cell

HotXLS hanterar det här i fångststeget. Under läsningen skannar den varje uppspelat fragment efter element- och attributprefix fragmentet inte deklarerar självt, löser varje prefix till dess URI via läsaren, och skriver de deklarationerna på kalkylbladsroten vid sparning. Den strömmande sparvägen bygger roten via samma rutin, så båda sparlägena producerar välformad utdata, och en omsparad fil öppnas på nytt i Excel med varje valideringsregel på plats, inklusive en uppspelad lista över arken som x14. Den bredare bilden av hur HotXLS bär extLst-innehåll den inte modellerar tas upp i förlustfria XLSX-roundtrips för themes, extLst och calcChain, och de allmänna reglerna bakom Excels reparationsdialog finns i varför Excel visar en reparationsdialog

Långa listor hör hemma i ett område eller ett definierat namn

Den robusta designen för vilken lista som helst som kan växa är att lagra posterna i kalkylbladsceller och peka valideringen mot dem via ett definierat namn. En områdeskälla har ingen 255-teckensgräns i något av formaten, posterna kan redigeras utan att röra regeln, och ett definierat namn behåller regeln i det vanliga dataValidation-elementet även när cellerna ligger på ett separat ark, vilket varje konsument av filen kan läsa

// Lists är ett eget ark, t.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]);   // 1-baserad rad, kolumn
  ColRef := Chr(Ord('A') + ACol - 1);               // kolumner A..Z
  Book.DefinedNames.Add(AName, Lists.Name + '!$' + ColRef + '$1:$' +
    ColRef + '$' + IntToStr(Length(AItems)));
  // Det inledande '=' markerar en referens; utan det citatas namnet som text
  Result := Target.AddListValidation(ARange, '=' + AName);
end;

Det inledande likhetstecknet spelar roll. AddListValidation behandlar ett argument som börjar med = som en referens och strippar tecknet, lämnar ett argument som innehåller ! ociterat som ett område, och citerar allt annat som en inline-lista. Skicka 'StatusList' utan tecknet och du får en enpost-dropdown som bokstavligen lyder StatusList. Två mindre punkter: WriteAutoValue konverterar text som Excels cellinmatning gör, så en post som TRUE blir en Boolean, och ett arknamn med mellanslag behöver enkla citattecken i referensen

För en regel som måste peka rakt på ett annat ark utan ett namn, sätt IsExtension := True på regeln vid indexet AddListValidation returnerade, och HotXLS skriver den som x14-tillägget, samma form Excel själv använder. Vägen via definierat namn är enklare och fortsätter funka för verktyg som aldrig parsar tillägg, vilket är varför det är standardrekommendationen här

Allt beteende som beskrivs här följer med HotXLS Delphi Excel-komponenten för Delphi, C++Builder och Lazarus, där produktsidan listar de stödda valideringstyperna, filformaten och hela diagnostikreferensen