HotXLS: лимит списка 255 символов и расширения x14

Excel отказывается открывать книгу XLSX, чья проверка данных списка держит inline-список длиннее 255 символов, — и это верно даже для файла, который Excel сохранил сам. HotXLS, нативный Excel-компонент для Delphi и C++Builder, грузит такие списки и моделирует межлистовые правила списков как расширения x14, но прочная починка везде одна: держите длинные списки в диапазоне листа или именованном диапазоне

Баг-репорт обычно приходит от клиента, а не из ваших тестов. Шаблон оброс столбцом Region или Product, чей выпадающий список начинался с восьми пунктов, а дорос до шестидесяти. В XLSX-копии, которую кто-то держит на сетевом диске, всё по-прежнему работает. Потом джоба экспорта пишет то же правило в XLSX, и Excel отвечает диалогом восстановления или плоским отказом. В вашем коде не изменилось ничего, кроме длины одной строки, — ровно потому эту границу стоит понять раньше, чем она найдёт вас

Где реально действует лимит списка в 255 символов?

Лимит действует на inline-форму проверки списка в XLSX — закавыченную строку через запятую, сохранённую в formula1, — и Excel проводит его при открытии файла, а не только при наборе списка в диалоге проверки данных. Прямая проверка против Excel дала чистый результат: список в 253 символа открылся, списки в 260 и 421 символ были отвергнуты. Случай 421 символа — самый показательный, потому что пришёл он из книги, которую Excel сам же и создал. Excel пишет файл, который Excel сам не прочитает

Схема здесь не спасает. ECMA-376 Part 1, §18.3.1.32 определяет элемент dataValidation и его формулы, не называя потолка в 255 символов, поэтому файл может быть схемно-валидным и всё равно отвергнутым. Это прикладное ограничение, и единственная надёжная справка по нему — поведение самого Excel

HotXLS этот потолок за вас не проводит. TXLSXWorksheet.AddListValidation заворачивает пункты в кавычки и сохраняет как дали: ни обрезки, ни отказа для длинного списка, поэтому список в 400 символов уходит в сохранённый XLSX ровно как запрошено, а Excel потом отвергает результат. Выбор сознательный: молчаливая подрезка списка портила бы данные так, что никто не заметил бы, но проверка длины ложится на вашу сторону. Небольшой хелпер держит её честной:

const
  // Excel открыл список в 253 символа и отверг 260 и 421.
  // Держите запас, а не цельтесь в точный край.
  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));  // разделяющие запятые
end;

Считайте встроенные двойные кавычки стоящими два символа: AddListValidation удваивает их, сохраняя валидность строки формулы

Почему XLSB несёт списки, которые XLSX не может

XLSB хранит inline-список как один строковый токен PtgStr, чья длина — 16-битное поле, поэтому у бинарного формата нет стены в 255 символов. Нативный пример с 60 пунктами и 419 символами живёт в одном токене и гоняет туда-обратно в Excel без жалоб. Тот же список в XLSX — тот самый случай 421 символа выше

Загрузчик, применяющий к XLSB лимит диалога в 255 символов, отверг бы такой файл. Проверка токенов валидации XLSB в HotXLS ограничивает строковый токен 32 767 символами — тем же потолком, что у строки ячейки Excel. Две оговорки стоит сказать прямо. Загрузчик также ограничивает полную формулу валидации 16 384 байтами, так что практический потолок одного inline-списка ниже, чем обещает одна лишь проверка токена. И списки, разделённые NUL-символами внутри PtgStr, вне поддерживаемого профиля: они сообщаются как неподдерживаемые вместо угадывания

Ловушка сидит в конверсии. Длинный список, здоровый в XLSB, становится отвергнутым файлом в момент, когда что угодно — включая HotXLS — сохраняет книгу как XLSX. Если конвейер конвертирует клиентские XLSB, гоняйте проверку длины по каждому правилу списка до сохранения XLSX, а не после репорта клиента

Что со списками, указывающими на другой лист?

Excel хранит список с источником на другом листе в расширении x14:dataValidations, а не в обычном элементе dataValidations. Правило живёт внутри extLst листа под URI расширения {CCE6A557-97BC-4b89-ADB6-D9C93CAAB3DF}, определённым в [MS-XLSX], а его ссылка на источник — элемент xm:f вроде Lists!$A$2:$A$61. XLSB держит то же правило в собственных записях расширений

HotXLS парсит правила расширений XLSX в типизированную коллекцию DataValidations и помечает каждое флагом TXLSXDataValidation.IsExtension. Межлистовый список поэтому виден рядом с обычными правилами — с читаемыми диапазоном, типом и формулой — и переживает правки, сохранения и переоткрытия. При сохранении писатель держит правила расширений вне обычного элемента и пересобирает расширение x14 на его схемной позиции в хвосте листа. Обе формулы сохраняются, поэтому правила whole-number, decimal, text-length, date и time с between или notBetween держат обе границы

Типизированный профиль расширения покрывает правило целиком. Кроме type, operator, allowBlank, двух show-флагов, диапазона и обеих формул, правило расширения переживает цикл загрузки и пересохранения с заголовком и текстом входной подсказки, заголовком и текстом предупреждения об ошибке, стилем предупреждения, режимом IME и настройкой спрятанного выпадающего списка — с теми же правилами экранирования, что у обычной проверки

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

О чтении пунктов за выпадающим списком, выборе записей и проверке набранного ввода по этим правилам — в статье о чтении пунктов выпадающих списков и валидации ввода с HotXLS; эта статья остаётся при форматах хранения

Как Excel и HotXLS обращаются с неизвестными типами проверок

Excel открывает dataValidation, чей атрибут type держит нераспознанный токен, как правило типа none, сохраняет формулу, а при сохранении переписывает его голым элементом dataValidation без атрибута type, выбрасывая неизвестный атрибут. Такие токены приходят из ручного XML, сторонних генераторов и будущих фич Office, и ответ Excel — толерантность, а не отказ

HotXLS следует той же толерантности. Вместо выбрасывания правила — что потеряло бы его при пересохранении без следа — читатель хранит его как xlsxDvNone с формулами и диапазоном и записывает одну диагностическую пометку на нормализованное правило с кодом 1307, экспонируемым как константа xlsDiagnosticValidationTypeNormalized в lxDiagnostics. При сохранении правило none пишется голым элементом — той же формой, что выдаёт Excel. Правило none не проверяет ничего, поэтому диагностика — ваш знак того, что правило, которое автор считал активным, больше не ограничивает ввод

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

Почему Excel может отвергнуть книгу после круга?

Excel отвергает пересохранённую книгу, когда переигранный фрагмент расширения использует префикс пространства имён, который новый файл нигде не декларирует, — результат не является well-formed XML. Фрагменты расширений рутинно несут префиксованные атрибуты вроде xr:uid, чья декларация xmlns:xr сидит на корне исходного листа, а не внутри фрагмента. Скопируйте фрагмент дословно в корень нового листа без той декларации — правила XML-пространств имён сломаны ещё до того, как Excel взглянет на первую ячейку

HotXLS решает это на шаге захвата. При чтении он сканирует каждый переигранный фрагмент на префиксы элементов и атрибутов, которые фрагмент сам не декларирует, разрешает каждый префикс в URI через читателя и при сохранении выписывает те декларации на корень листа. Потоковый путь сохранения строит корень через ту же рутину, поэтому оба режима сохранения дают well-formed вывод, и пересохранённый файл переоткрывается в Excel со всеми правилами валидации на месте — включая переигранный межлистовый список x14. Более широкая картина того, как HotXLS переносит немоделированный контент extLst, — в статье о lossless-кругах XLSX для тем, extLst и calcChain, а общие правила за диалогом восстановления Excel — в разборе, почему Excel показывает восстановление

Длинные списки живут в диапазоне или именованном диапазоне

Прочная конструкция для любого списка, способного расти, — хранить пункты в ячейках листа и направлять проверку на них именованным диапазоном. У источника-диапазона нет лимита 255 символов ни в одном формате, пункты можно править, не трогая правило, а именованный диапазон держит правило в обычном элементе dataValidation, даже когда ячейки на отдельном листе, — читаемом любым потребителем файла

// Lists — отдельный лист, например 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
  ColRef := Chr(Ord('A') + ACol - 1);               // столбцы A..Z
  Book.DefinedNames.Add(AName, Lists.Name + '!$' + ColRef + '$1:$' +
    ColRef + '$' + IntToStr(Length(AItems)));
  // Ведущий '=' означает ссылку; без него имя закавычится как текст
  Result := Target.AddListValidation(ARange, '=' + AName);
end;

Тот ведущий знак равенства существенен. AddListValidation трактует аргумент с = как ссылку и отрезает знак, оставляет аргумент с ! незакавыченным как диапазон и закавычивает всё прочее как inline-список. Передайте 'StatusList' без знака — получите выпадающий список из одного пункта, буквально читающего StatusList. Ещё два мелких пункта: WriteAutoValue конвертирует текст, как ввод ячейки Excel, поэтому пункт вроде TRUE станет Boolean, а имя листа с пробелами требует одинарных кавычек в ссылке

Для правила, обязанного указывать прямо на другой лист без имени, выставьте IsExtension := True правилу на индексе, который вернул AddListValidation, — HotXLS запишет его как расширение x14, той же формой, которой пользуется сам Excel. Путь через именованный диапазон проще и продолжает работать для инструментов, никогда не парсящих расширения, — потому он здесь рекомендация по умолчанию

Всё описанное поведение поставляется в HotXLS Delphi Excel component для Delphi, C++Builder и Lazarus, где страница продукта перечисляет поддерживаемые типы проверок, форматы файлов и полную справку по диагностикам