HotXLS list validation: limit 255 znaků a rozšíření x14

Excel odmítá otevřít XLSX sešit, jehož list data validation drží inline seznam delší než 255 znaků, a platí to i pro soubor, který si Excel uložil sám. HotXLS, nativní Excel komponenta pro Delphi a C++Builder, ty seznamy načítá a pravidla seznamů napříč listy modeluje jako rozšíření x14, ale trvalá oprava je všude tatáž: dlouhé seznamy držte v oblasti worksheetu nebo v definovaném názvu

Chybové hlášení obvykle přichází od zákazníka, ne z vašich testů. Šablona dorostla sloupec Region nebo Product, jehož dropdown měl osm položek a teď jich má šedesát. Všechno pořád funguje v XLSB kopii, kterou si někdo drží na sdíleném disku. Pak exportní job zapíše totéž pravidlo do XLSX a Excel odpoví promptem na opravu nebo ostře odmítnutím. Ve vašem kódu se nezměnilo nic kromě délky jednoho řetězce, a přesně proto stojí za to tahle hranice pochopit, dřív než vás najde

Kde vlastně platí limit seznamu 255 znaků?

Limit platí pro inline formu list validation v XLSX, uvozovkami oddělený řetězec s čárkami uložený v formula1, a Excel ho vynucuje při otevírání souboru, ne jen když seznam píšete do dialogu Data Validation. Přímé testování proti Excelu dalo čistý výsledek: seznam o 253 znacích se otevřel, zatímco seznamy o 260 a 421 znacích byly odmítnuty. Ten 421znakový případ je poučný, protože přišel ze sešitu, který si Excel sám autorizoval. Excel napíše soubor, který neumí přečíst zpět

Schema vás tady nezachrání. ECMA-376 Part 1, §18.3.1.32 definuje element dataValidation a jeho vzorce, aniž by udával strop 255 znaků, takže soubor může být schema-validní a přesto odmítnutý. To je aplikační omezení a jediný spolehlivý referenční bod je chování samotného Excelu

HotXLS vám ten strop nevynucuje. TXLSXWorksheet.AddListValidation oblete položky do uvozovek a uloží je tak, jak přišly; dlouhý seznam ani nezkrátí, ani neodmítne, takže 400znakový seznam putuje do uloženého XLSX přesně podle žádosti a Excel pak výsledek odmítne. To je záměrná volba, protože tichým useknutím seznamu by se poškodila data způsobem, který si nikdo nevšimne, ale kontrolu délky to přenáší na vás. Malý helper ji drží při zemi:

const
  // Excel otevřel seznam o 253 znacích a odmítl 260 a 421 znaků.
  // Držte si rezervu místo míření přesně na hranu.
  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));  // oddělující čárky
end;

Vložené dvojité uvozovky počítejte jako dva znaky, protože je AddListValidation zdvojuje, aby vzorcový řetězec zůstal validní

Proč XLSB nese seznamy, které XLSX unést neumí

XLSB ukládá inline seznam jako jediný string token PtgStr, jehož délka je 16bitové pole, takže binární formát nemá zeď 255 znaků. Nativní vzorek se 60 položkami a 419 znaky žije v jednom tokenu a v Excelu projde tam a zpět bez námitek. Týž seznam v XLSX je ten 421znakový případ výše

Loader, který by aplikoval limit dialogu 255 znaků i na XLSB, by tenhle soubor odmítl. Kontrola validačních tokenů XLSB v HotXLS ohraničuje string token na 32 767 znaků, tutéž mez jako řetězec buňky Excelu. Dvě kvalifikace stojí za přímé vyjádření. Loader taky zaškrtne úplný validační vzorec na 16 384 bajtů, takže praktický strop pro jeden inline seznam je nižší, než naznačuje samotná kontrola tokenu. A seznamy oddělené znaky NUL uvnitř PtgStr leží mimo podporovaný profil, takže se hlásí jako nepodporované místo hádání

Past sedí v konverzi. Dlouhý seznam, který je v XLSB naprosto zdravý, se stane odmítnutým souborem v okamžiku, kdy cokoliv, HotXLS nevyjímaje, uloží ten sešit jako XLSX. Pokud pipeline konvertuje XLSB soubory zákazníků, provádějte kontrolu délky u každého pravidla seznamu před uložením XLSX, ne po tom, co ji nahlásí zákazník

Co se stane se seznamy mířícími na jiný list?

Excel ukládá seznam, jehož zdroj leží na jiném worksheetu, v rozšíření x14:dataValidations, ne v obyčejném elementu dataValidations. Pravidlo bydlí uvnitř extLst worksheetu pod URI rozšíření {CCE6A557-97BC-4b89-ADB6-D9C93CAAB3DF}, definovaným v [MS-XLSX], a jeho odkaz na zdroj je element xm:f jako Lists!$A$2:$A$61. XLSB drží totéž pravidlo ve vlastních záznamech rozšíření

HotXLS parsuje ta XLSX pravidla rozšíření do typované kolekce DataValidations a každé označí příznakem TXLSXDataValidation.IsExtension. Seznam napříč listy se proto ukazuje vedle obyčejných pravidel s čitelnou oblastí, typem i vzorcem a přežije editace, uložení i znovunačtení. Při ukládání writer drží pravidla rozšíření mimo obyčejný element a znovu postaví rozšíření x14 na své schema pozici na ocase worksheetu. Oba vzorce se zachovávají, takže pravidla celé číslo, desetinné číslo, délka textu, datum a čas používající between nebo notBetween si drží obě meze

Typovaný profil rozšíření pokrývá celé pravidlo. Kromě type, operator, allowBlank, obou show příznaků, oblasti i obou vzorců si pravidlo rozšíření při načtení a znovuuložení drží název i text vstupní výzvy, název i text chybového upozornění, styl upozornění, režim IME i nastavení hide-dropdown, se stejnými pravidly escapování jako obyčejná validation

Čtení položek za dropdownem, výběr položek a kontrolu zapsaného vstupu proti těmto pravidlům popisuje čtení dropdown položek a validace vstupu s HotXLS; tenhle článek zůstává u ukládacích formátů

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[] se čísluje od 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;

Jak Excel a HotXLS zacházejí s neznámými validačními typy

Excel otevře dataValidation, jejíž atribut type drší nerozpoznaný token, jako pravidlo typu none, nechá si jeho vzorec a při ukládání ho přepíše na holý element dataValidation bez atributu type, přičemž neznámý atribut zahodí. Takové tokeny přichází z ručně editovaného XML, od generátorů třetích stran a z budoucích funkcí Office a odpověď Excelu je tolerance, ne odmítnutí

HotXLS následuje tutéž toleranci. Místo zahodit pravidlo, což by ho při znovuuložení ztratilo beze stopy, si ho čtečka drží jako xlsxDvNone s jeho vzorci a oblastí a zaznamenává jedno varovné diagnostic na normalizované pravidlo s kódem 1307, vystavené jako konstanta xlsDiagnosticValidationTypeNormalized v lxDiagnostics. Při ukládání se pravidlo none zapisuje jako holý element, stejná forma, jakou produkuje Excel. Pravidlo none nevaliduje nic, takže diagnostic je vaše cue, že pravidlo, kterému autor věřil, že je aktivní, už vstup neomezuje

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

Proč může Excel odmítnout sešit po cestě tam a zpět?

Excel odmítá znovuuložený sešit, když přehraný fragment rozšíření používá namespace prefix, který nový soubor nikdy nedeklaruje, protože výsledek není well-formed XML. Fragmenty rozšíření běžně nesou atributy s prefixy jako xr:uid, jejíž deklarace xmlns:xr sedí na kořeni původního worksheetu, ne uvnitř fragmentu. Zkopírujte fragment doslova na kořen nového worksheetu bez té deklarace a pravidla XML namespace jsou rozbité, ještě než Excelovi do očí dojde jediná buňka

HotXLS tohle řeší ve fázi zachycení. Při čtení projde každý přehraný fragment po elementových a atributových prefixech, které sám nedeklaruje, každý prefix rozřeší na jeho URI přes čtečku a při ukládání zapíše ty deklarace na kořen worksheetu. Streaming save cesta staví kořen přes tutéž rutinu, takže oba režimy ukládání produkují well-formed výstup a znovuuložený soubor se v Excelu otevře se všemi pravidly validation, včetně přehraného x14 seznamu napříč listy. Širší souvislost toho, jak HotXLS nese obsah extLst, který nemodeluje, popisuje bezztrátové XLSX round-tripy pro theme, extLst a calcChain a obecná pravidla za Excelovým repair promptem jsou v proč Excel ukazuje repair prompt

Dlouhé seznamy patří do oblasti nebo do definovaného názvu

Robustní design pro jakýkoli seznam, který může růst, je uložit položky do buněk worksheetu a nasměrovat validation na ně přes definovaný název. Zdroj typu oblast nemá v žádném formátu limit 255 znaků, položky se dají editovat, aniž by se dotklo pravidlo, a definovaný název drží pravidlo v obyčejném elementu dataValidation, i když buňky sedí na samostatném listu, což přečte každý konzument souboru

// Lists je dedikovaný list, např. 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]);   // řádek od 1, sloupec
  ColRef := Chr(Ord('A') + ACol - 1);               // sloupce A..Z
  Book.DefinedNames.Add(AName, Lists.Name + '!$' + ColRef + '$1:$' +
    ColRef + '$' + IntToStr(Length(AItems)));
  // Úvodní '=' značí referenci; bez něj je název v uvozovkách jako text
  Result := Target.AddListValidation(ARange, '=' + AName);
end;

Ten úvodní rovníkáš má význam. AddListValidation bere argument začínající = jako referenci a znaménko sune dolů, argument obsahující ! nechává necitovaný jako oblast a všechno ostatní cituje jako inline seznam. Pošlete 'StatusList' bez znaménka a dostanete dropdown o jedné položce, který doslova hlásí StatusList. Dvě menší poznámky: WriteAutoValue konvertuje text tak, jako to dělá zadávání buněk v Excelu, takže položka jako TRUE se stane Booleanem, a název listu obsahující mezery potřebuje v referenci jednoduché uvozovky

Pro pravidlo, které musí mířit rovnou na jiný list bez názvu, nastavte IsExtension := True na pravidle na indexu, který vrátila AddListValidation, a HotXLS ho zapíše jako rozšíření x14, tutéž formu, kterou sám používá Excel. Cesta přes definovaný název je jednodušší a funguje dál i u nástrojů, které rozšíření nikdy neparují, a proto je tady defaultní doporučení

Všechno chování popsané tady dodává HotXLS Delphi Excel component pro Delphi, C++Builder a Lazarus, kde produktová stránka uvádí podporované validační typy, formáty souborů a kompletní referenci diagnostics