Валидиране на данни, AutoFilter и таблици в работни листове в Delphi с HotXLS

Три функции в HotXLS споделят един работен лист, но работят с напълно различни обекти, и проблемите започват, когато приемете, че правят сходни неща. Валидирането на данни прикрепя правило към диапазон, което ограничава какво потребителят може да въвежда в него. AutoFilter прикрепя дефиниция на критерии към област и променя кои редове се показват. Таблицата обвива диапазон в наименувана структура с определени стилове. Едното ограничава въвеждането, другото записва изглед, третото налага схема. Никое от тях не премества стойност на клетка само по себе си, а AutoFilter често подвежда разработчиците, тъй като името му подсказва действие, докато той съхранява само дефиниция. Разбирането кой обект се променя при всяко извикване е ключът към стабилната работа

Практически контекст

AutoFilter записва дефиниция на критерии в записания файл. Скриването на редовете се случва по-късно, когато Excel отвори книгата и приложи тези критерии. HotXLS записва този запис без промяна: всеки филтриран ред физически присъства във файла. Задача, която прилага филтър за отхвърляне на поръчки и след това чете файла обратно, ще види всички поръчки, включително отхвърлените. В 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');
    // Column id 3 = fourth column INSIDE the filter range (0-based offset)
    Sheet.AddAutoFilterColumn(3, xlsxAfOpGreaterOrEqual, '1000');

    Visible := 0;
    for R := 2 to 500 do
      if Sheet.AutoFilterRowVisible(R) then
        Inc(Visible);
    // Visible now matches what Excel will show after opening the file

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

Методът AutoFilterRowVisible отговаря за конкретен ред, а PreviewAutoFilterRows обхожда целия диапазон чрез колбек функция. Ако изискването е изключените редове физически да не присъстват във файла, ги изтрийте директно. Филтърът не е подходящ инструмент за защита на чувствителни данни, тъй като всеки получател може да го премахне с един клик

Идентификаторът на колоната е отместване, а не номер на колона

Коментарът в кода по-горе показва честа грешка. AddAutoFilterColumn идентифицира целта по нейния индекс (базиран на 0) спрямо диапазона на филтъра, а не по колоната в работния лист. При филтър върху A1:E500 тези две системи се различават с единица, което лесно може да бъде пропуснато при тестове. При филтър, започващ от колона C, индекс 0 означава колона C. Когато диапазонът се изчислява в движение, генерирайте индекса на колоната на базата на същата променлива, а не като константа. Всяка колона приема и второ условие през метода с два оператора, което съответства на диалога за персонализиран филтър в 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;

  // Quantities: whole numbers, zero or more
  Sheet.AddWholeNumberValidation('D2:D500', xlsxDvOpGreaterOrEqual, '0');
end;

Освен списъци и цели числа, същата група поддържа десетични числа, дати, времена, дължина на текста и формули чрез AddCustomValidation, а общият метод AddDataValidation предоставя пълната матрица за правила. Стилът на грешката е важен: xlsxDvErrStop отхвърля некоректните данни; предупрежденията и информационните стилове ги записват след потвърждение. Избирайте ги според това дали четящият код може да допусне грешни стойности. Имайте предвид, че валидирането в Excel работи при въвеждане, но поставянето на данни (paste) заобикаля правилата, така че четящият код винаги трябва да прави повторна проверка. Освен това, правилата важат за зададения диапазон - добавянето на редове извън него ги оставя без защита. Пишете данните първо, а след това оразмерявайте правилата

Наследената фасада предлага същите семейства правила с една ергономична разлика. XLS методите AddWholeNumberValidation, AddDecimalValidation, AddDateValidation, AddTimeValidation, AddTextLengthValidation и AddCustomValidation връщат директно обекта TDataValidation, а не индекс, така че настройването на подсказка и грешка се продължава от върнатата препратка вместо чрез търсене. Енумерацията на операторите, включително xlsDvBetween, xlsDvGreaterThan и останалите, отразява XLSX набора, затова кодът за изграждане на правила се пренася между фасадите с изключение на тази разлика във връщаната стойност. Текстът на подсказката заслужава толкова внимание, колкото и правилото. Падащ списък, който отхвърля вход с празно съобщение, учи потребителите да пишат на IT; такъв, който посочва допустимите състояния, ги учи да поправят клетката

Обърната стойност при падащите списъци

Всеки, който е преглеждал суровия XML на OOXML валидация, се е сблъсквал с обърнатия атрибут showDropDown: в ISO/IEC 29500 стойност true означава „скрий стрелката на падащия списък“(обратното на името). HotXLS коригира това вътрешно, така че свойството ShowDropDown работи логично, като стойност true показва списъка. Внимавайте при одит на суровия 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 за обобщени изгледи от полета за редове, колони и данни, което напомня, че „таблиците“ в стария формат обхващат повече от OOXML ListObject. Наименувайте таблиците като изгледи в база данни. Код, който чете Orders[Amount] чрез структурирана препратка, преживява пренареждането на колони, което нарушава позиционния код

Два навика спестяват проблеми при почистване. Excel изисква имената на таблиците да бъдат уникални за цялата книга, така че при генериране на листове по региони ползвайте схеми като Orders_EMEA вместо да повтаряте Orders. Дублирането води до диалогов прозорец за грешка при отваряне в Excel. Другата подробност е свързана с реда за общи суми: когато е активен, той се разполага под диапазона от данни, така че добавянето на редове по метода „последен ред плюс едно“ще ги запише в сумарния ред. Следете обхвата на данните отделно от този на таблицата

Тези три функции се съчетават успешно при форми за въвеждане на данни. Таблицата определя областта за редактиране, валидирането контролира колоните, а филтърът улеснява потребителя. Подготовката на данните за таблицата е разгледано в експортиране на резултати от бази данни в Excel, а формулите, обработващи тези данни, ползват дефинирани имена за стабилни референции

Валидирането, филтрите и таблиците превръщат табличната мрежа в малко приложение. Пълната документация за тези обекти е достъпна на продуктовата страница на HotXLS Component