Валідація даних, автофільтр та таблиці аркушів у Delphi з HotXLS

Три можливості HotXLS ділять один аркуш, але працюють із цілком різними об’єктами, і халепа починається тоді, коли ви припускаєте, що вони роблять щось схоже. Перевірка даних чіпляє до діапазону правило, яке обмежує те, що користувач може туди набрати. Автофільтр чіпляє до області збережене означення критеріїв і змінює те, які рядки бачить глядач. Таблиця загортає діапазон у іменовану типізовану структуру зі смугастим оформленням. Одне обмежує ввід, друге записує подання, третє накидає схему. Жодне з них саме собою не рухає й одного значення клітинки, і саме автофільтр збиває людей з пантелику, бо слово натякає на дію, тоді як зберігається лише означення. Розуміння того, якого об’єкта торкається кожен виклик і коли ефект справді матеріалізується, і відрізняє книгу, що поводиться в Excel так само, як у ваших тестах, від тієї, що тихо з ними розходиться

Автофільтр зберігає означення, а не обрізає рядки

Автофільтр у збереженому файлі — це запис критеріїв. Приховування рядків відбувається пізніше, коли Excel відкриває книгу й обчислює критерії щодо даних. HotXLS сумлінно записує цей запис і нічого не обрізає: кожен відфільтрований вами рядок фізично лишається у файлі. Конвеєр, що застосовує фільтр, аби прибрати відхилені замовлення, а потім читає книгу назад, побачить їх усі, разом із відхиленими, і код буде правильним з погляду API та хибним з погляду того, що уявляв собі автор. На аркуші XLSX SetAutoFilter оголошує відфільтровану область, а AddAutoFilterColumn чіпляє критерії до одного її стовпця. Коли серверному коду потрібен фактичний результат — кількість рядків для підсумку чи пересилання лише збіжних рядків, — бібліотека обчислює критерії за вас, а не вдає, ніби файл змінився:

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  R, Visible: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('orders.xlsx');
    Sheet := Book.Sheets[0];

    Sheet.SetAutoFilter('A1:E500');
    // Ідентифікатор стовпця 3 = четвертий стовпець УСЕРЕДИНІ діапазону фільтра (зсув від нуля)
    Sheet.AddAutoFilterColumn(3, xlsxAfOpGreaterOrEqual, '1000');

    Visible := 0;
    for R := 2 to 500 do
      if Sheet.AutoFilterRowVisible(R) then
        Inc(Visible);
    // Visible тепер збігається з тим, що Excel покаже після відкриття файлу

    Book.SaveAs('orders-filtered.xlsx');
  finally
    Book.Free;
  end;
end;

AutoFilterRowVisible відповідає для окремого рядка, а PreviewAutoFilterRows проходить усю область через зворотний виклик, коли збіжний набір потрібен вам за один прохід. Є випадок, де жодне з двох не буде правильною відповіддю: якщо вимога така, що виключених рядків у файлі не має бути взагалі, тобто йдеться про вирізання заради приватності, а не про подання, — видаляйте рядки геть. Фільтр там неправильний інструмент, бо будь-який одержувач знімає його одним клацанням, і дані, які ви мали намір приховати, знову на екрані

Ідентифікатор стовпця — це зсув, а не номер стовпця

Коментар у фрагменті вище позначає пастку, яка коштує в цьому API найбільше часу на зневадження. AddAutoFilterColumn визначає свою ціль позицією від нуля всередині діапазону фільтра, а не стовпцем аркуша. Для фільтра на A1:E500 дві системи нумерації випадково відрізняються на одиницю, а це рівно той різновид майже-влучання, що переживає швидкий тест і ламається тієї миті, коли колега відфільтрує інший стовпець. Для фільтра, що починається зі стовпця C, ідентифікатор 0 означає стовпець C, і розбіжність стає очевидною швидко. Коли діапазон фільтра обчислюється під час виконання, виводьте ідентифікатор стовпця з тієї самої змінної, що побудувала рядок діапазону, і ніколи — з константи стовпця аркуша. Кожен стовпець приймає другу умову через перевантаження, що бере два оператори, два критерії та сполучник and/or, і це віддзеркалює діалог власного фільтра в Excel. Фасад XLS покриває ту саму територію через SetAutoFilter плюс ApplyAutoFilter, чиї параметри критеріїв і оператора дотримуються старіших домовленостей у стилі COM і нумерують поле з одиниці. Зміна фасаду означає зміну бази індексації, тож місце виклику заслуговує на коментар, який каже, котра з них тут діє

Правила перевірки — це контракт, за яким редагують ваші користувачі

З трьох можливостей саме перевірка єдина активно обмежує майбутній ввід, і вона заслуговує на найбільшу увагу в проєктуванні книг, які йдуть назовні на заповнення й повертаються на обробку. Варіант зі списком бере на себе більшу частину цієї роботи:

var
  Idx: Integer;
begin
  Idx := Sheet.AddListValidation('C2:C500', 'New,Approved,Blocked');
  Sheet.DataValidations[Idx].SetPrompt('Status',
    'Pick one of the listed states');
  Sheet.DataValidations[Idx].SetError('Invalid status',
    'Type or paste only listed values', xlsxDvErrStop);
  Sheet.DataValidations[Idx].AllowBlank := False;

  // Кількості: цілі числа, нуль або більше
  Sheet.AddWholeNumberValidation('D2:D500', xlsxDvOpGreaterOrEqual, '0');
end;

Крім списків і цілих чисел, та сама родина покриває десяткові числа, дати, час, довжину тексту й довільні формули через AddCustomValidation, а узагальнений AddDataValidation відкриває повну матрицю типів і операторів для конструкторів правил, керованих конфігурацією. Стиль помилки важить більше, ніж підказує його назва. xlsxDvErrStop відхиляє поганий ввід навідруб; стилі попередження та інформації пропускають значення після одного клацання. Обирайте його для кожного стовпця залежно від того, чи витримає код, що читає книгу назад, значення поза правилом. Дві межі варто зазначити в тексті підказки або в README, який ви постачаєте разом із файлом. Перевірка в Excel стереже набирання з клавіатури, але вставляння блоку поверх перевіреного діапазону прослизає повз правило, тож будь-який код, що читає ці дані назад, мусить перевіряти їх знову, а не довіряти клітинкам. І правило покриває буквально той діапазон, який ви йому передали, а отже, чіпляння перевірки до того, як ви знаєте остаточну кількість рядків, лишає дописаний хвіст без охорони. Спершу запишіть дані, а тоді розмірте правила під фактичну протяжність

Спадковий фасад пропонує ті самі родини правил з однією ергономічною відмінністю. Творці з боку XLS, а саме AddWholeNumberValidation, AddDecimalValidation, AddDateValidation, AddTimeValidation, AddTextLengthValidation і AddCustomValidation, повертають об’єкт TDataValidation напряму, а не індекс, тож налаштування підказки й помилки чіпляється ланцюжком до поверненого посилання, а не до пошуку. Перелічення операторів (xlsDvBetween, xlsDvGreaterThan і решта) віддзеркалює набір XLSX, тож код побудови правил переноситься між фасадами, якщо не рахувати цієї різниці в стилі повернення. Сам текст підказки заслуговує на стільки ж роздумів, скільки й правило. Спадний список, що відхиляє ввід порожньою скринькою помилки, привчає користувачів писати листи в ІТ; той, що називає дозволені стани, привчає їх виправити клітинку й рушати далі

Одна зміна полярності, яку бібліотека бере на себе

Кожен, хто читав руками XML перевірки в OOXML, зустрічав інвертований атрибут showDropDown: в ISO/IEC 29500 значення true означає «сховати стрілку спадного списку», тобто протилежне до того, як читається назва. HotXLS перевертає це всередині, тож властивість ShowDropDown на правилі перевірки означає те, що каже, і true показує спадний список. Обпектися можна лише в один спосіб — змішавши рівні істини: задавати властивість із коду, поки колега переглядає збережений XML і «виправляє» атрибут, що видається йому перевернутим. Вирішіть, що є авторитетним для інструментів рецензування — властивість чи сирий XML, — і запишіть цю інверсію там, де живе це рішення

Таблиці дають діапазону схему та ім’я

Таблиця аркуша, ListObject у термінах Excel, загортає діапазон в ім’я, типізовані стовпці, смугасте оформлення та підтримку структурованих посилань. Саме ця можливість робить згенеровану книгу завершеною на відчуття, щойно користувачі починають сортувати й розширювати її. Створення симетричне в обох фасадах: AddTable бере ім’я, діапазон і список стовпців:

var
  Cols: TStringList;
begin
  Cols := TStringList.Create;
  try
    Cols.CommaText := 'OrderId,Customer,Status,Amount,Owner';
    Sheet.AddTable('Orders', 'A1:E500', Cols);
  finally
    Cols.Free;
  end;
end;

З боку XLSX отриманий об’єкт таблиці відкриває StyleName (вбудована родина TableStyleMedium2 та її родичі), перемикачі смуг і прапорець рядка підсумків, тож застосування фірмового оформлення — це присвоєння властивості, а не ручний прохід форматування. У спадкових файлах .xls той самий виклик записує табличні записи BIFF8, а фасад також пропонує AddPivotTable для зведених подань, побудованих із полів рядків, стовпців і даних, — нагадування, що «таблиці» у старішому форматі сягають далі, ніж ListObject із OOXML. Іменуйте таблиці так, як іменуєте подання бази даних. Код нижче за течією, що читає Orders[Amount] структурованим посиланням, переживає ту зміну порядку стовпців, яка ламає позиційний код

Дві домовленості заощаджують прибирання згодом. Excel вимагає, щоб імена таблиць були унікальними в межах усієї книги, тож генератору, що видає по аркушу на регіон, потрібна схема на кшталт Orders_EMEA, а не повторне використання Orders. Дублікат не падає під час запису; він виринає діалогом відновлення, коли користувач відкриває файл, — а це найгірше місце, щоб його виявити. Друга домовленість стосується рядка підсумків: коли він увімкнений, то сидить прямо під діапазоном даних, тож будь-який код, що згодом дописує за принципом «останній використаний рядок плюс один», пише в смугу підсумків, а не після неї. Відстежуйте протяжність даних окремо від протяжності таблиці — і дописування приземлятиметься там, де ви очікуєте

Ці три можливості природно складаються в результатах для введення даних. Таблиця визначає редаговану область, перевірка обмежує стовпці, у які набирають користувачі, а заздалегідь заданий фільтр рятує одержувача від перших кількох клацань. Є справедливий аргумент за те, щоб постачати вже застосований фільтр, аби книга відкривалася зосередженою на важливих рядках, — доки ви пам’ятаєте, що виключені рядки досі у файлі й допитливий одержувач може їх виявити. Ефективне заведення результатів запитів на аркуш, тобто верхня половина цього конвеєра, розглянуте у статті експорт результатів із бази даних до Excel із Delphi, а книгам, де формули підсумовують перевірені дані, стануть у пригоді визначені імена для стабільних посилань між аркушами

Перевірка, фільтри й таблиці — це різниця між постачанням сітки значень і постачанням маленького застосунку. Повний довідник правил, фільтрів і таблиць — на сторінці продукту HotXLS Delphi Component