HotXLS listevalidering: 255-tegn-grænsen og x14-udvidelser

Excel nægter at åbne en XLSX-workbook, hvis listevalidering indeholder en inline-liste på mere end 255 tegn, og det gælder selv for en fil, Excel selv har gemt. HotXLS, den native Excel-komponent til Delphi og C++Builder, loader de lister og modellerer cross-sheet listeregler som x14-udvidelser, men den holdbare løsning er den samme alle steder: opbevar lange lister i et worksheet-interval eller et defineret navn

Fejlrapporten kommer typisk fra en kunde, ikke fra dine tests. En skabelon får en Region- eller Product-kolonne, hvis dropdown startede med otte poster og nu har tres. Alt virker stadig i den XLSB-kopi, nogen holder på et share drive. Så skriver et eksportjob samme regel ind i en XLSX, og Excel svarer med en reparationsdialog eller et blankt afslag. Intet i din kode ændrede sig ud over længden af én streng, og det er præcis hvorfor denne grænse er værd at forstå, inden den finder dig

Hvor gælder grænsen på 255 tegn for lister faktisk?

Grænsen gælder inline-formen af en listevalidering i XLSX, den citerede, kommaadskilte streng gemt i formula1, og Excel håndhæver den ved åbning af filen, ikke kun når du taster listen i Data Validation-dialogen. Direkte test mod Excel gav et rent resultat: en liste på 253 tegn åbnede, mens lister på 260 og 421 tegn blev afvist. Tilfældet med 421 tegn er det oplysende, for det kom fra en workbook, Excel selv havde skabt. Excel vil skrive en fil, som Excel ikke vil læse tilbage

Schemat redder dig ikke her. ECMA-376 Part 1, §18.3.1.32 definerer dataValidation-elementet og dets formler uden at angive et loft på 255 tegn, så en fil kan være schema-gyldig og stadig blive afvist. Det er en programbegrænsning, og den eneste pålidelige reference for den er Excels egen adfærd

HotXLS håndhæver ikke det loft for dig. TXLSXWorksheet.AddListValidation pakker punkterne ind i anførselstegn og gemmer dem som givet; den hverken trunkerer eller afviser en lang liste, så en liste på 400 tegn ryger i den gemte XLSX præcis som ønsket, og Excel nægter så resultatet. Det er et bevidst valg, for lydløs beskæring af en liste ville korruptere data på en måde, ingen lægger mærke til, men det lægger længdetjekket over til dig. En lille hjælper holder det ærligt:

const
  // Excel åbnede en liste på 253 tegn og afviste 260 og 421 tegn.
  // Hold en margin i stedet for at sigte på den præcise kant.
  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));  // adskillende kommaer
end;

Behandl indlejrede dobbelte anførselstegn som kosterende to tegn, for AddListValidation fordobler dem for at holde formelstrengen gyldig

Hvorfor XLSB kan bære lister, som XLSX ikke kan

XLSB gemmer en inline-liste som et enkelt PtgStr-strengtoken, hvis længde er et 16-bit-felt, så det binære format har ingen mur på 255 tegn. En native prøve med 60 punkter og 419 tegn lever i ét token og round-tripper i Excel uden brok. Samme liste i XLSX er tilfældet med 421 tegn ovenfor

En loader, der anvendte dialogens 255-tegn-grænse på XLSB, ville afvise den fil. HotXLS XLSB-valideringstokentjekket afgrænser et strengtoken ved 32.767 tegn i stedet, samme loft som en Excel-cellestreng. To forbehold er værd at sige klart. Loaderen capper også en komplet valideringsformel ved 16.384 byte, så det praktiske loft for én inline-liste er lavere end tokentjekket alene antyder. Og lister adskilt af NUL-tegn inde i et PtgStr falder uden for den understøttede profil, så de rapporteres som ikke-understøttede i stedet for at blive gættet

Fælden sidder i konverteringen. En lang liste, der er rask i XLSB, bliver til en afvist fil, i samme øjeblik noget som helst — HotXLS inkluderet — gemmer den workbook som XLSX. Konverterer en pipeline kunde-XLSB-filer, så kør længdetjekket på hver listeregel før XLSX-gemningen, ikke efter en kunde rapporterer det

Hvad sker der med lister, der peger på et andet ark?

Excel gemmer en liste, hvis kilde ligger på et andet worksheet, i x14:dataValidations-udvidelsen, ikke i det almindelige dataValidations-element. Reglen bor inde i worksheetens extLst under udvidelses-URI'en {CCE6A557-97BC-4b89-ADB6-D9C93CAAB3DF}, defineret i [MS-XLSX], og dens kildereference er et xm:f-element som Lists!$A$2:$A$61. XLSB beholder samme regel i sine egne udvidelsesrecords

HotXLS parser de XLSX-udvidelsesregler ind i den typede DataValidations-samling og mærker hver med TXLSXDataValidation.IsExtension. En cross-sheet-liste optræder derfor ved siden af almindelige regler med sit interval, sin type og sin formel læsbare, og den overlever redigeringer, gemninger og genåbninger. Ved gemning holder writeren udvidelsesregler ude af det almindelige element og genopbygger x14-udvidelsen på sin skemposition ved halen af worksheetet. Begge formler beholdes, så hel-tal-, decimal-, tekstlængde-, dato- og klokkeslætsregler med between eller notBetween beholder begge grænser

Den typede udvidelsesprofil dækker hele reglen. Ud over type, operator, allowBlank, de to show-flag, intervallet og begge formler beholder en udvidelsesregel sin inputprompt-titel og -tekst, sin fejlalert-titel og -tekst, alert-stilen, IME-tilstanden og hide-dropdown-indstillingen gennem en indlæsning og resave, med de samme escaping-regler som en almindelig 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[] er 1-baseret
    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;

Til at læse punkterne bag en dropdown, vælge poster og tjekke tastet input mod disse regler, se læsning af dropdown-punkter og validering af input med HotXLS; denne artikel bliver ved lagerformaterne

Hvordan Excel og HotXLS behandler ukendte valideringstyper

Excel åbner en dataValidation, hvis type-attribut indeholder et ukendt token, som en regel af typen none, beholder dens formel og omskriver ved gemning til et bart dataValidation-element uden type-attribut og dropper den ukendte attribut. Sådanne tokens kommer fra håndredigeret XML, tredjepartsgeneratorer og fremtidige Office-features, og Excels svar er tolerance, ikke afvisning

HotXLS følger samme tolerance. I stedet for at droppe reglen, hvilket ville miste den ved resave uden spor, beholder læseren den som xlsxDvNone med sine formler og sit interval og noterer én advarselsdiagnostik pr. normaliseret regel med kode 1307, eksponeret som konstanten xlsDiagnosticValidationTypeNormalized i lxDiagnostics. Ved gemning skrives en none-regel som det bare element, samme form som Excel producerer. En none-regel validerer ingenting, så diagnostikken er dit hint om, at en regel, forfatteren troede var aktiv, ikke længere begrænser input

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

Hvorfor kan Excel afvise en workbook efter en round trip?

Excel afviser en resavet workbook, når et genafspillet udvidelsesfragment bruger et namespace-præfiks, som den nye fil aldrig deklarerer, for resultatet er ikke velformet XML. Udvidelsesfragmenter bærer rutinemæssigt præfiksede attributter som xr:uid, hvis xmlns:xr-deklaration sidder på det oprindelige worksheet-rod, ikke inde i fragmentet. Kopier fragmentet ordret ind i et nyt worksheet-rod uden den deklaration, og XML-namespace-reglerne er brudt, før Excel kigger på en eneste celle

HotXLS håndterer det i capture-trinnet. Under læsning scanner den hvert genafspillet fragment for element- og attributpræfikser, fragmentet ikke selv deklarerer, opløser hvert præfiks til sin URI gennem læseren og skriver de deklarationer på worksheet-roden ved gemmetid. Den streaming save-vej bygger roden gennem samme rutine, så begge save-tilstande producerer velformet output, og en resavet fil genåbner i Excel med hver valideringsregel til stede, inklusiv en genafspillet cross-sheet x14-liste. Det bredere billede af, hvordan HotXLS bærer extLst-indhold, den ikke modellerer, er dækket i tabsfri XLSX round-trips for themes, extLst og calcChain, og de generelle regler bag Excels reparationsdialog er i hvorfor Excel viser en reparationsdialog

Lange lister hører hjemme i et interval eller et defineret navn

Det robuste design for enhver liste, der kan vokse, er at gemme punkterne i worksheet-celler og pege valideringen på dem gennem et defineret navn. En intervalkilde har ingen grænse på 255 tegn i nogen af formaterne, punkterne kan redigeres uden at røre reglen, og et defineret navn holder reglen i det almindelige dataValidation-element, selv når cellerne ligger på et separat ark, hvilket enhver forbruger af filen kan læse

// Lists er et dedikeret ark, fx 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-baseret række, kolonne
  ColRef := Chr(Ord('A') + ACol - 1);               // kolonner A..Z
  Book.DefinedNames.Add(AName, Lists.Name + '!$' + ColRef + '$1:$' +
    ColRef + '$' + IntToStr(Length(AItems)));
  // Det ledende '=' markerer en reference; uden det citeres navnet som tekst
  Result := Target.AddListValidation(ARange, '=' + AName);
end;

Det ledende lighedstegn betyder noget. AddListValidation behandler et argument, der starter med =, som en reference og fjerner tegnet, lader et argument med ! stå uciteret som et interval og citerer alt andet som en inline-liste. Giver du 'StatusList' uden tegnet, får du en dropdown med ét punkt, der bogstaveligt læser StatusList. To mindre punkter: WriteAutoValue konverterer tekst, som Excels celleindtastning gør, så et punkt som TRUE bliver en Boolean, og et arknavn med mellemrum behøver enkelte anførselstegn i referencen

Til en regel, der skal pege direkte på et andet ark uden et navn, sæt IsExtension := True på reglen ved det indeks, AddListValidation returnerede, og HotXLS skriver den som x14-udvidelsen, samme form som Excel selv bruger. Defineret-navn-vejen er enklere og virker videre for værktøjer, der aldrig parser udvidelser, og derfor er den default-anbefalingen her

Al adfærden beskrevet her følger med i HotXLS Delphi Excel component til Delphi, C++Builder og Lazarus, hvor productsiden lister de understøttede valideringstyper, filformater og den fulde diagnostikreference