Три функции HotXLS делят один рабочий лист, но работают с совершенно разными объектами, и проблемы начинаются, как только вы предполагаете, что они делают что-то похожее. Проверка данных прикрепляет к диапазону правило, ограничивающее то, что пользователь может в него ввести. AutoFilter прикрепляет к области сохранённое определение критериев и меняет, какие строки показывает средство просмотра. Таблица оборачивает диапазон в именованную типизированную структуру с полосатым оформлением. Одна ограничивает ввод, другая записывает представление, третья навязывает схему. Ни одна из них сама по себе не перемещает ни одного значения ячейки, и AutoFilter в особенности вводит в заблуждение, потому что само слово подразумевает действие, тогда как оно хранит лишь определение. Знание того, какого объекта касается каждый вызов и когда эффект действительно материализуется, — это и есть то, что отличает книгу, ведущую себя в Excel так же, как в ваших тестах, от той, что тихо расходится с ожиданиями
AutoFilter хранит определение, а не обрезает строки
AutoFilter в сохранённом файле — это запись критериев. Скрытие строк происходит позже, когда 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');
// Id столбца 3 = четвёртый столбец ВНУТРИ диапазона фильтра (смещение с 0)
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 проходит по всей области через callback, когда вам нужен набор совпадений за один проход. Есть случай, где ни то ни другое не подходит: если требование состоит в том, что исключённые строки вообще не должны существовать в файле — это вырезание в целях приватности, а не представление, — удаляйте строки напрямую. Фильтр здесь не тот инструмент, потому что любой получатель снимает его одним щелчком, и данные, которые вы хотели скрыть, снова на экране
Id столбца — это смещение, а не номер столбца
Комментарий в приведённом выше фрагменте отмечает ловушку, которая отнимает больше всего времени на отладку в этом API. AddAutoFilterColumn определяет свою цель по позиции с отсчётом от 0 внутри диапазона фильтра, а не по столбцу рабочего листа. Для фильтра на A1:E500 обе системы нумерации случайно отличаются на единицу, а это ровно тот вид почти-совпадения, который переживает быструю проверку и ломается в тот момент, когда коллега фильтрует другой столбец. Для фильтра, начинающегося со столбца C, id 0 означает столбец C, и несовпадение становится очевидным быстро. Когда диапазон фильтра вычисляется во время выполнения, выводите id столбца из той же переменной, что построила строку диапазона, никогда — из константы столбца рабочего листа. Каждый столбец принимает второе условие через перегрузку с двумя операторами, двумя критериями и связкой and/or, что зеркалит диалог настраиваемого фильтра в Excel. Фасад XLS покрывает ту же территорию через SetAutoFilter плюс ApplyAutoFilter, чьи параметры критериев и оператора следуют более старым соглашениям в стиле COM и нумеруют поле с 1. Переключение фасадов означает переключение базы индексации, так что место вызова заслуживает комментария о том, какая база используется
Правила проверки — это контракт, по которому редактируют ваши пользователи
Из трёх функций проверка данных — единственная, что активно ограничивает будущий ввод, и именно она заслуживает наибольшего внимания при проектировании книг, которые уходят на заполнение и возвращаются на обработку. Основную часть этой работы несёт вариант со списком:
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, — и зафиксируйте эту инверсию там, где живёт это решение
Таблицы дают диапазону схему и имя
Таблица рабочего листа, в терминах Excel — ListObject, оборачивает диапазон в имя, типизированные столбцы, полосатое оформление и поддержку структурированных ссылок. Именно эта функция делает сгенерированную книгу законченной на вид, как только пользователи начинают её сортировать и расширять. Создание симметрично между фасадами: 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