Walidacja list HotXLS: limit 255 znaków i rozszerzenia x14

Excel odmawia otwarcia skoroszytu XLSX, którego walidacja danych listy trzyma listę inline dłuższą niż 255 znaków, i to obowiązuje nawet dla pliku, który sam Excel zapisał. HotXLS, natywny komponent Excel dla Delphi i C++Builder, ładuje te listy i modeluje reguły list międzyarkuszowych jako rozszerzenia x14, ale trwała naprawa jest wszędzie ta sama: trzymaj długie listy w zakresie arkusza albo nazwie zdefiniowanej

Zgłoszenie buga zwykle przychodzi od klienta, nie z twoich testów. Szablon dorośnie do kolumny Region albo Product, której dropdown zaczynał od ośmiu pozycji, a ma teraz sześćdziesiąt. Wszystko nadal działa w kopii XLSB, którą ktoś trzyma na dysku sieciowym. Potem zadanie eksportu zapisuje tę samą regułę do XLSX, a Excel odpowiada promptem naprawy albo gołąm odmową. Nic w twoim kodzie się nie zmieniło poza długością jednego stringa — właśnie dlatego tę granicę warto zrozumieć, zanim ona znajdzie ciebie

Gdzie faktycznie obowiązuje limit listy 255 znaków?

Limit dotyczy formy inline walidacji listy w XLSX, czyli cytowanego stringa rozdzielanego przecinkami, zapisanego w formula1, i Excel egzekwuje go przy otwieraniu pliku, nie tylko przy wpisywaniu listy w oknie Data Validation. Testy wprost przeciw Excelowi dały czysty wynik: lista 253 znaków się otworzyła, a listy 260 i 421 znaków zostały odmówione. Przypadek 421 znaków jest pouczający, bo pochodził ze skoroszytu, który sam Excel wygenerował. Excel napisze plik, którego sam nie przeczyta

Schemat tu cię nie uratuje. ECMA-376 Part 1, §18.3.1.32, definiuje element dataValidation i jego formuły bez podawania sufitu 255 znaków, więc plik może być ważny względem schematu i nadal odrzucony. To ograniczenie aplikacyjne, a jedyną niezawodną referencją jest samo zachowanie Excela

HotXLS tego sufitu nie egzekwuje za ciebie. TXLSXWorksheet.AddListValidation owija pozycje w cudzysłowy i zapisuje je tak, jak podano; ani nie ucina, ani nie odrzuca długiej listy, więc lista 400 znaków trafia do zapisanego XLSX dokładnie tak, jak zażądano, a Excel potem odmawia wyniku. To celowa decyzja, bo ciche przycinanie listy psuje dane w sposób, którego nikt nie zauważy, ale przerzuca kontrolę długości na twoją stronę. Mały helper trzyma ją w ryzach:

const
  // Excel otworzył listę 253 znaków i odmówił 260 oraz 421.
  // Trzymaj margines zamiast celować w samą krawędź.
  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));  // przecinki rozdzielające
end;

Traktuj osadzone podwójne cudzysłowy jako kosztujące dwa znaki, bo AddListValidation je podwaja, żeby string formuły pozostał ważny

Dlaczego XLSB uniesie listy, których XLSX nie uniesie

XLSB zapisuje listę inline jako pojedynczy token stringa PtgStr, którego długość jest polem 16-bitowym, więc format binarny nie ma muru 255 znaków. Natywna próbka z 60 pozycjami i 419 znakami mieszka w jednym tokenie i przechodzi rundę w Excelu bez protestu. Ta sama lista w XLSX to wyżej opisany przypadek 421 znaków

Loader stosujący limit 255 znaków z okna dialogowego do XLSB odrzuciłby taki plik. Kontrola tokena walidacji XLSB w HotXLS ogranicza token stringa do 32 767 znaków, tego samego sufitu co string komórki Excela. Dwa zastrzeżenia warto powiedzieć wprost. Loader kapuje też kompletną formułę walidacji na 16 384 bajtach, więc praktyczny sufit dla jednej listy inline jest niższy, niż sugeruje sama kontrola tokena. I listy rozdzielane znakami NUL wewnątrz PtgStr wypadają poza wspierany profil, więc są raportowane jako niewspierane zamiast być zgadywane

Pułapka siedzi w konwersji. Długa lista całkowicie zdrowa w XLSB staje się odmówionym plikiem w chwili, gdy cokolwiek — HotXLS włącznie — zapisze ten skoroszyt jako XLSX. Jeśli pipeline konwertuje pliki XLSB klientów, puszczaj kontrolę długości na każdej regule listy przed zapisem XLSX, nie po zgłoszeniu od klienta

Co się dzieje z listami wskazującymi na inny arkusz?

Excel zapisuje listę, której źródło siedzi na innym arkuszu, w rozszerzeniu x14:dataValidations, nie w zwykłym elemencie dataValidations. Reguła mieszka wewnątrz extLst arkusza pod URI rozszerzenia {CCE6A557-97BC-4b89-ADB6-D9C93CAAB3DF}, zdefiniowanym w [MS-XLSX], a jej referencja źródła to element xm:f jak Lists!$A$2:$A$61. XLSB trzyma tę samą regułę we własnych rekordach rozszerzeń

HotXLS parsuje te reguły rozszerzeń XLSX do typowanej kolekcji DataValidations i flaguje każdą przez TXLSXDataValidation.IsExtension. Lista międzyarkuszowa pokazuje się więc obok zwykłych reguł z czytelnym zakresem, typem i formułą i przeżywa edycje, zapisy i ponowne otwarcia. Przy zapisie writer trzyma reguły rozszerzeń poza zwykłym elementem i przebudowuje rozszerzenie x14 na jego pozycji schematowej u ogona arkusza. Obie formuły są zachowywane, więc reguły liczby-całkowitej, dziesiętne, długości-tekstu, daty i czasu używające between albo notBetween zachowują oba krańce

Typowany profil rozszerzenia pokrywa całą regułę. Poza type, operator, allowBlank, dwiema flagami show, zakresem i obiema formułami reguła rozszerzenia zachowuje tytuł i treść promptu wejściowego, tytuł i treść alertu błędu, styl alertu, tryb IME i ustawienie hide-dropdown przez ładowanie i ponowny zapis, z tymi samymi regułami escapowania co zwykła walidacja

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[] liczy 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;

Do czytania pozycji stojących za dropdown, wybierania pozycji i sprawdzania wpisywanych danych względem tych reguł służy czytanie pozycji dropdown i walidacja wejścia z HotXLS; ten artykuł zostaje przy formatach zapisu

Jak Excel i HotXLS traktują nieznane typy walidacji

Excel otwiera dataValidation, którego atrybut type trzyma nierozpoznany token, jako regułę typu none, zachowuje jej formułę, a przy zapisie przepisuje ją na goły element dataValidation bez atrybutu type, wyrzucając nieznany atrybut. Takie tokeny pochodzą z ręcznie edytowanego XML, generatorów firm trzecich i przyszłych funkcji Office, a odpowiedź Excela to tolerancja, nie odrzucenie

HotXLS stosuje tę samą tolerancję. Zamiast wyrzucać regułę, co straciłoby ją przy ponownym zapisie bez śladu, czytelnik trzyma ją jako xlsxDvNone z jej formułami i zakresem oraz zapisuje jedno diagnostyczne ostrzeżenie per znormalizowana reguła z kodem 1307, wystawione jako stała xlsDiagnosticValidationTypeNormalized w lxDiagnostics. Przy zapisie reguła none jest zapisywana jako goły element, ta sama forma, którą produkuje Excel. Reguła none niczego nie waliduje, więc diagnostyka to twój sygnał, że reguła, którą autor wierzył aktywną, już nie ogranicza danych wejściowych

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

Dlaczego Excel może odmówić skoroszytu po rundzie?

Excel odmawia ponownie zapisanego skoroszytu, gdy odtworzony fragment rozszerzenia używa prefiksu przestrzeni nazw, którego nowy plik nigdzie nie deklaruje, bo wynik nie jest poprawnym składniowo XML-em. Fragmenty rozszerzeń rutynowo niosą atrybuty z prefiksami jak xr:uid, którego deklaracja xmlns:xr siedzi na pierwotnym korzeniu arkusza, nie wewnątrz fragmentu. Skopiuj fragment dosłownie do nowego korzenia arkusza pozbawionego tej deklaracji, a reguły przestrzeni nazw XML są złamane, zanim Excel zobaczy choć jedną komórkę

HotXLS ogarnia to w kroku przechwytywania. Przy czytaniu skanuje każdy odtworzony fragment za prefiksami elementów i atrybutów, których fragment sam nie deklaruje, rozwiązuje każdy prefiks do jego URI przez czytnik i zapisuje te deklaracje na korzeniu arkusza w czasie zapisu. Ścieżka zapisu strumieniowego buduje korzeń przez tę samą procedurę, więc oba tryby zapisu produkują poprawny składniowo wynik, a ponownie zapisany plik otwiera się w Excelu z każdą regułą walidacji na miejscu, łącznie z odtworzoną międzyarkuszową listą x14. Szerszy obraz tego, jak HotXLS niesie zawartość extLst, której nie modeluje, opisuje bezstratne rundy XLSX dla theme, extLst i calcChain, a ogólne reguły za promptem naprawy Excela są w dlaczego Excel pokazuje prompt naprawy

Długie listy należą do zakresu albo nazwy zdefiniowanej

Wytrzymały projekt dla każdej listy, która może urosnąć, to zapisanie pozycji w komórkach arkusza i skierowanie walidacji na nie przez nazwę zdefiniowaną. Źródło z zakresu nie ma limitu 255 znaków w żadnym formacie, pozycje da się edytować bez dotykania reguły, a nazwa zdefiniowana trzyma regułę w zwykłym elemencie dataValidation nawet, gdy komórki siedzą na osobnym arkuszu, co każdy konsument pliku umie przeczytać

// Lists to dedykowany arkusz, np. 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]);   // wiersz i kolumna od 1
  ColRef := Chr(Ord('A') + ACol - 1);               // kolumny A..Z
  Book.DefinedNames.Add(AName, Lists.Name + '!$' + ColRef + '$1:$' +
    ColRef + '$' + IntToStr(Length(AItems)));
  // Wiodący = oznacza referencję; bez niego nazwa jest cytowana jako tekst
  Result := Target.AddListValidation(ARange, '=' + AName);
end;

Ten wiodący znak równości ma znaczenie. AddListValidation traktuje argument zaczynający się od = jako referencję i zdejmuje znak, zostawia argument zawierający ! niecytowany jako zakres, a wszystko inne cytuje jako listę inline. Przekaż 'StatusList' bez znaku, a dostaniesz jednoelementowy dropdown czytający dosłownie StatusList. Dwa mniejsze punkty: WriteAutoValue konwertuje tekst tak, jak robi to wpisywanie komórki Excela, więc pozycja jak TRUE staje się Booleanem, a nazwa arkusza zawierająca spacje potrzebuje pojedynczych cudzysłowów w referencji

Dla reguły, która musi wskazywać wprost na inny arkusz bez nazwy, ustaw IsExtension := True na regule pod indeksem zwróconym przez AddListValidation, a HotXLS zapisze ją jako rozszerzenie x14, tę samą formę, której sam używa Excel. Droga nazwy zdefiniowanej jest prostsza i nadal działa dla narzędzi, które nigdy nie parsują rozszerzeń, dlatego jest tu rekomendacją domyślną

Całe zachowanie opisane tutaj płynie w komponencie HotXLS Delphi Excel dla Delphi, C++Builder i Lazarus, gdzie strona produktu wylicza wspierane typy walidacji, formaty plików i pełną referencję diagnostyki