HotXLS: выпадающие списки и валидация ввода на Delphi

HotXLS читает выпадающие списки проверки данных Excel прямо из Delphi-кода. TryGetCellDropdownItems возвращает строки-кандидаты для ячейки, SelectCellDropdownItem записывает одну из них по индексу или по тексту, а ValidateCellInput сообщает, пройдёт ли набранное значение по правилу ячейки, до того как что-либо сохранено. Те же методы есть у TXLSWorksheet для классического XLS и у TXLSXWorksheet для XLSX, и оба берут номера строки и столбца с 1

Типичный триггер — шаблон, который вы не проектировали. Кто-то из бизнеса отгружает книгу со столбцом Status, запертым на Draft, Review и Final, столбцом Channel, питающимся диапазоном на скрытом листе, и столбцом количества, принимающим только 1–100. Вашему импорту, веб-форме или пакетному заполнителю теперь приходится уважать эти правила, а правила живут в книге, не в вашем коде. Захардкодить разрешённые значения рядом с шаблоном — верный способ, чтобы они разъехались при первой же правке списка в Excel

Почему записи кода игнорируют проверку данных Excel?

Проверка данных — метаданные, прикреплённые к диапазону, а проводить её — работа того редактора, в который печатает пользователь. В XLSX правило — элемент dataValidation (ECMA-376 Part 1, §18.3.1.32) с типом, оператором, formula1 и formula2, подсказкой и предупреждением об ошибке. В классическом XLS та же информация сидит в записи DV, описанной в [MS-XLS]. Ни один формат не помечает саму ячейку, поэтому ничто не мешает программе сохранить значение, ломающее правило. Excel ведёт себя так же: он проверяет набранное, а вне-диапазонное значение, пришедшее иным путём, всплывает, только когда кто-то запускает «Обвести неверные данные»

HotXLS следует этой модели намеренно. Присваивание вроде Sheet.Cells[3, 2].Value := 250 сохраняет 250 под правилом 1–100 без возражений — ровно как делает бандловская демка DataValidation, доказывая пункт. Проверка — opt-in, и остаток статьи о том, как включать её в правильном месте

Чтение пунктов выпадающего списка за ячейкой

TryGetCellDropdownItems(ARow, ACol, AItems, AShowDropDown) возвращает True, только когда ячейка покрыта правилом типа list, и заполняет AItems массивом TArray<WideString> кандидатов. HotXLS разрешает все четыре формы записи источника списка, встречающиеся на практике, поэтому вызывающему не приходится парсить formula1 руками:

• Закавыченный inline-список вроде "Draft,Review,Final", разбитый по запятым
• Inline-список с пунктами, разделёнными символами #0, — форма, встречающаяся в правилах классического XLS
• Диапазон на том же листе или на другом, вроде =$G$2:$G$10 или =Lists!$A$2:$A$7, вычисленный до текущих значений ячеек
• Именованный диапазон вроде =ProductList, указывающий на один из таких диапазонов

Объединённые ячейки тоже обработаны. Спросите любую ячейку внутри объединения — HotXLS найдёт правило в якоре объединения, там, где Excel держит и правило, и значение

Флаг AShowDropDown заслуживает пометки, потому что форматы файлов хранят его наоборот. В XLSX showDropDown="1" на самом деле прячет стрелку в ячейке, а в BIFF8 эквивалентный бит — флаг «спрятать комбо». HotXLS переворачивает оба при чтении, поэтому AShowDropDown = True означает то, что говорит: Excel показал бы стрелку. Правило списка со спрятанной стрелкой всё равно ограничивает ввод, поэтому TryGetCellDropdownItems возвращает пункты в обоих случаях

uses
  SysUtils, lxHandleX;

procedure DumpChannelList(const FileName: WideString);
var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Items: TArray<WideString>;
  ShowArrow: Boolean;
  I: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    if Book.Open(FileName) <> 1 then
      raise Exception.Create('Cannot open ' + FileName);
    Sheet := Book.Sheets[1];
    // F2: строка 2, столбец 6, оба с 1
    if Sheet.TryGetCellDropdownItems(2, 6, Items, ShowArrow) then
    begin
      Writeln(Length(Items), ' items, arrow visible: ', BoolToStr(ShowArrow, True));
      for I := 0 to High(Items) do
        Writeln('  [', I, '] ', Items[I]);
    end
    else
      Writeln('F2 has no list validation');
  finally
    Book.Free;
  end;
end;

Если нужны только строки, GetCellDropdownItems(ARow, ACol) возвращает массив напрямую и даёт пустой массив, когда список не применим. TryGetCellValidationPrompt возвращает заголовок и текст входного сообщения — удобно, когда хочется показать собственную подсказку автора шаблона рядом с полем веб-формы

Выбор пункта по индексу или по тексту

SelectCellDropdownItem записывает пункт списка в ячейку и возвращает False, когда у ячейки нет правила списка или запрошенного пункта не существует. Целочисленная перегрузка берёт индекс с 0 в массив, который вернула TryGetCellDropdownItems. Текстовая перегрузка ищет точное совпадение, затем откатывается к регистронезависимому, поэтому 'review' всё равно выберет Review

Обе перегрузки пишут через WriteAutoValue, а не через сырое строковое присваивание. Для списков с числами из диапазона это существенно: пункты возвращаются строками вроде '3', но сохранённое значение — число 3, и именно его хранит Excel, когда пользователь выбирает по стрелке, и его же ждёт любая формула, ссылающаяся на ячейку. Демка DataValidation проверяет ровно это после сохранения и повторного открытия в XLS, XLSX и XLSB

// Те же вызовы на TXLSWorksheet (XLS) и TXLSXWorksheet (XLSX)
if not Sheet.SelectCellDropdownItem(2, 1, 'Review') then
  Writeln('A2: "Review" is not in the list');

if not Sheet.SelectCellDropdownItem(2, 6, 3) then  // 4-й пункт, с 0
  Writeln('F2: index 3 is out of range');

Как проверить набранный ввод до записи?

ValidateCellInput(ARow, ACol, AText) испытывает строку-кандидата по правилу, покрывающему ячейку, и никогда ячейку не меняет. Возвращает True, когда правило не применимо или кандидат проходит. Строка проходит через ту же конверсию текст-в-значение, что использует WriteAutoValue, поэтому проверка и итоговая запись видят одно значение. На стороне XLSX конверсия — XlsxAutoTextValue: она распознаёт TRUE/FALSE, литералы ошибок, ISO-даты, даты m/d/yyyy и d.m.yyyy и числа в инвариантном формате, а 0123 держит текстом, как Excel. Движок классического XLS имеет собственный конвертер, зеркалящий его собственный WriteAutoValue

Два спутника достраивают набор. ValidateCellValue(ARow, ACol) проверяет значение, уже сохранённое в ячейке, и считает пустые ячейки валидными. TryGetCellValidationError возвращает заголовок, текст и стиль предупреждения об ошибке правила и возвращает False, когда правило вообще не показывает предупреждение. Тип стиля у каждого движка свой, так что сопоставляйте его с классом листа: TXLSDvErrorStyle из lxDataCheck для TXLSWorksheet, TXLSXDvErrorStyle для TXLSXWorksheet

Одно поведение придётся воспроизвести самому: Excel не проверяет пустой ввод и всё, что начинается с =. HotXLS применяет этот пропуск в пути правок вьюера, а не внутри ValidateCellInput, поэтому серверный шлюз должен сделать то же перед вызовом:

uses
  lxDataCheck, lxHandle;

// Возвращает True, когда AText можно записать, зеркаля стили предупреждений Excel
function AcceptTypedValue(Sheet: TXLSWorksheet; ARow, ACol: Integer;
  const AText: WideString; out AMessage: WideString): Boolean;
var
  Title, Text: WideString;
  Style: TXLSDvErrorStyle;
begin
  AMessage := '';
  Result := True;
  // Excel пропускает проверку для очищенных ячеек и формул
  if (AText = '') or (AText[1] = '=') then
    Exit;
  // Предупреждение не настроено: Excel пропускает что угодно
  if not Sheet.TryGetCellValidationError(ARow, ACol, Title, Text, Style) then
    Exit;
  if Sheet.ValidateCellInput(ARow, ACol, AText) then
    Exit;
  AMessage := Title + ': ' + Text;
  // Stop отвергает; Warning и Information оставляют решение вызывающему
  Result := Style <> xlsDvErrStop;
end;

// Вызывающий (Msg: WideString): проверить, затем сохранить той же конверсией
if AcceptTypedValue(Sheet, 2, 2, '42', Msg) then
  Sheet.WriteAutoValue(2, 2, '42');

Пара ValidateCellInput плюс WriteAutoValue — и есть смысл примера. Проверяйте одной конверсией, а сохраняйте другой — и рано или поздно значение пройдёт проверку числом, а в ячейку ляжет текстом

Закавыченные пункты списка и ловушка экранирования классического XLS

Inline-пункты списка с двойными кавычками — лёгкий способ потерять правило молча. Inline-список хранится как строка формулы, а строка формулы экранирует встроенную " удвоением. Заверните пункты в кавычки, не удвоив внутренние, — и список вроде He said "hi",No выдаст формулу, которая не компилируется. Исключения не будет: правило просто не создаётся, и выпадающий список уходит вместе с ним. TXLSWorksheet.AddListValidation и его XLSX-собрат удваивают встроенные кавычки, поэтому такие пункты выживают целыми

У TXLSWorksheet.AddListValidation есть также перегрузка с array of WideString, соответствующая перегрузке на TXLSXWorksheet. Удобно, но прочтите, что она делает: она склеивает пункты запятыми и передаёт результат строковой перегрузке. Пункт, содержащий запятую, превращается в два пункта, а на классическом движке строка источника с ! трактуется как ссылка с квалификацией листа, а не как inline-список. Для подписей вроде этих кладите значения в диапазон ячеек и направляйте правило на него

var
  Book: IXLSWorkbook;
  Sheet: TXLSWorksheet;
begin
  Book := TXLSWorkbook.Create;
  Sheet := Book.Sheets.Add;
  Sheet.AddListValidation('H2:H11', ['He said "hi"', 'Plain option']);
  // Убедитесь, что правило существует, прежде чем на него полагаться
  if Sheet.FindDataValidationAt(2, 8) = nil then
    raise Exception.Create('List rule for H2:H11 was not created');
  // ...
end;

После сохранения и повторного открытия TryGetCellDropdownItems на H2 вернёт He said "hi" с кавычками целыми — и в XLS, и в XLSX, XLSB и ODS

Доверить правила TXLSWorkbookViewer

Когда пользователи правят ячейки на экране, TXLSWorkbookViewer применяет те же проверки за вас. При Editable равном True и EnforceDataValidation в его умолчании True правка, набранная с нарушением правила с предупреждением об ошибке, проверяется до записи: Stop отвергает, Warning спрашивает, Information информирует. Вьюер также рисует стрелку выпадающего списка на активной ячейке, открывает список по щелчку или Alt+Down и показывает жёлтую входную подсказку под ячейкой. Детали самой сетки раскрыты в статье о построении собственной таблицы-сетки с TXLSWorkbookViewer

Выставленный в True CircleInvalidData обводит красными кружками сохранённые значения, нарушающие свои правила, — как одноимённая команда Excel. Он сканирует только видимую область, быстро отсекает большинство ячеек через FindDataValidationAt и никогда не обводит пустую ячейку

procedure TMainForm.FormCreate(Sender: TObject);
begin
  Viewer.Editable := True;
  Viewer.EnforceDataValidation := True;
  Viewer.CircleInvalidData := True;
  Viewer.OnValidationError := ViewerValidationError;
end;

procedure TMainForm.ViewerValidationError(Sender: TObject; Row, Col: Integer;
  const Text: WideString; Style: TXLSViewerValidationErrorStyle;
  const AlertTitle, AlertText: WideString;
  var Action: TXLSViewerValidationAction; var Handled: Boolean);
begin
  StatusBar.SimpleText := AlertTitle + ': ' + AlertText;
  if Style = xveStop then
    Action := xvaRetry    // переоткрыть редактор с отвергнутым текстом
  else
    Action := xvaAccept;
  Handled := True;        // подавить встроенный диалог
end;

Оставьте Handled в False — и вьюер покажет собственный диалог предупреждения, работающий и под VCL, и под LCL. Это умолчание кусается в автоматических тестах: тест, гоняющий правки без хендлера OnValidationError, выставляющего Handled, застрянет на модальном диалоге. Энумерация стилей вьюера — третий тип, TXLSViewerValidationErrorStyle с xveStop, xveWarning и xveInformation, отличный от двух энумераций листов

Что знать, прежде чем полагаться на валидацию ввода HotXLS?

Проверки точны для правил list, number, date, time и text-length, а несколько специфичных для движков деталей стоит знать:

• На листах классического XLS ValidateCellInput сообщает правила с произвольной формулой как валидные для набранных кандидатов: кандидат нельзя временно положить в BIFF-ячейку, не потревожив цепочку зависимостей. Проверяйте сохранённое значение через ValidateCellValue, вычисляющую произвольную формулу, или берите движок XLSX, вычисляющий произвольные правила и для кандидатов
• На классическом XLS сопоставление списка считает численно равные значения одним пунктом, поэтому 5 и 5.0 оба совпадут с пунктом 5
• Во вьюере вставленный контент пропускает проверку, как в Excel. У EditSelectedCell нет редактора для переоткрытия, поэтому Retry там ведёт себя как Cancel
• Простые присваивания Cells[...].Value никогда не проверяются — так задумано. Проверка происходит только там, где ваш код её просит

Создание самих правил, включая типы whole-number, date, text-length и custom, раскрыто в гиде HotXLS по проверке данных, AutoFilter и таблицам. Книги XLSB грузятся в TXLSXWorkbook, поэтому каждый показанный вызов TXLSXWorksheet работает на них без изменений, как описано в статье о чтении и записи бинарных книг XLSB на Delphi

Разрешение выпадающих списков, выбор пунктов и валидация ввода поставляются в HotXLS Delphi Excel component для Delphi, C++Builder и Lazarus — вместе с демками DataValidation и WorkbookViewer, прогоняющими каждый вызов этой статьи