Сравнение текста HotXLS: порядок word sort Excel в Delphi

HotXLS Delphi Component сравнивает два текстовых значения так, как это делает Excel 16, начиная с v2.384.67: без учёта регистра, в порядке «word sort» локали Windows пользователя — то, что возвращает CompareStringW с флагом NORM_IGNORECASE. Дефисы и апострофы пропускаются в первом проходе и лишь разрешают ничью, так что ="a-b">"ab" даёт TRUE, а прочая пунктуация сортируется раньше цифр и букв, так что ="a~b"<"ab" тоже TRUE. Тем же порядком теперь движутся операторы сравнения, критерии > / <, сортировка диапазонов и VLOOKUP

Никто не заводит баг с заголовком «несовпадение коллации». В отчётах сказано, что COUNTIF(A:A,">M") насчитывает на сервере на две строки больше, чем в Excel, что прайс-лист, отсортированный службой отчётности, кладёт X-100 не туда, куда положил бы Excel, или что VLOOKUP("ABC",...) возвращает #N/A, хотя в столбце явно есть abc. Все три — из одного вопроса: когда оба операнда текстовые, кто из них меньше? У Excel ответ точный, и это не тот ответ, что даёт большинство Delphi-кода, а до v2.384.67 HotXLS давал три разных ответа в зависимости от того, какой кодовый путь спрашивал

По какому правилу Excel сравнивает две текстовые строки?

Excel сравнивает текст по word sort локали пользователя, игнорируя регистр. Word sort — коллация по умолчанию у функций сравнения Windows NLS: буквы сопоставляются по своему языковому порядку, а не по кодовым точкам, буквы с диакритикой сидят рядом с базовой буквой, и два символа получают особое обращение. Дефис - и апостроф ' в первом проходе игнорируются, так что co-op и coop оказываются рядом, и лишь когда остальное строки свяжет ничьей, их присутствие решает порядок. Всякая другая пунктуация значима и сортируется раньше цифр, а цифры — раньше букв

Таблица показывает, что это значит на практике, рядом с двумя сравнениями, к которым Delphi-разработчик скорее всего потянется. Столбец Excel держит вердикты, которые Excel 16 вернул для IF(A<B,...) и которые HotXLS воспроизводит с v2.384.67

A против B	Excel 16 / HotXLS	CompareStr (ординарное)	CompareText
"a-b" vs "ab"	больше	меньше	меньше
"a'b" vs "ab"	больше	меньше	меньше
"a~b" vs "ab"	меньше	больше	больше
"a_b" vs "ab"	меньше	меньше	больше
"ab" vs "AB"	равно	больше	равно
"é" vs "f"	меньше	больше	больше
"Z" vs "f"	больше	меньше	больше

Два следствия легко упустить. Первое: роль дефиса как разрешителя ничьих означает, что ="a-b"="ab" даёт FALSE — строки в сортировке соседи, но не равны. Второе: равенство игнорирует регистр целиком, так что ab, AB и Ab для любого сравнения — один и тот же ключ. Сортировка 20 тестовых слов через Range.Sort Excel даёт a b, a.b, a_b, a~b, a0, a1b, ab / AB / Ab, ab-, a'b, a-b, -ab, ab1, abc, b, e, é, f, Z; внутри группы ab решает позиция игнорируемого символа

Как был установлен текстовый порядок Excel?

Текстовый порядок Excel устанавливали измерением, а не документацией, потому что документация Excel коллацию не называет. Тест сгенерировал 4000 случайных пар строк из ASCII-пунктуации, цифр, обоих регистров букв, пробелов, é, ß, ä, китайских иероглифов, полноширинных форм и неразрывного пробела, длиной от 0 до 4, причём половина пар строилась как почти совпадения друг с другом. Excel 16 вычислял IF(A<B,-1,IF(A=B,0,1)) для каждой пары, а вердикты сверялись с API сравнения Windows с разными наборами флагов

• Один NORM_IGNORECASE (дефолтный word sort, локаль пользователя): ни одного настоящего расхождения. Единственные 7 отличий были у ячеек, чьё всё содержимое — ', который Excel съедает как текстовый префиксный символ, так что это были артефакты выборки, а не различия коллации
• NORM_IGNORECASE с SORT_STRINGSORT: 41 расхождение. String sort трактует дефис и апостроф как обычные символы — ровно то поведение, которого у Excel нет
• Добавка NORM_IGNOREWIDTH: неверно по-другому, потому что она делает полноширинные и полуширинные формы одной буквы равными при сравнении, а Excel их разделяет

Вторая, отобранная вручную проверка сравнила все 190 пар из 20 каверзных слов и результат Range.Sort Excel на том же столбце. Оба сошлись с простым word sort на NORM_IGNORECASE, и эти 190 вердиктов плюс отсортированный порядок теперь входят в регрессионный набор HotXLS, прогоняемый через оба движка — классический TXLSWorkbook и нативный XLSX TXLSXWorkbook

Почему CompareText и ординарное сравнение ошибаются?

CompareText и ординарное сравнение ошибаются с порядком Excel, потому что сравнивают кодовые единицы UTF-16, а порядок кодовых точек раскидывает пунктуацию в произвольных местах относительно букв. Дефис — U+002D, апостроф — U+0027, оба ниже любой буквы, поэтому ординарное сравнение объявляет "a-b" меньшим, чем "ab", вместо того чтобы трактовать дефис как разрешителя ничьей. Тильда U+007E сидит выше любой буквы, так что "a~b" выходит большим — противоположно Excel. CompareText в Delphi RTL поднимает в верхний регистр только a..z и затем сравнивает кодовые единицы, что добавляет второе искажение: подчёркивание U+005F лежит между заглавными и строчными буквами, так что подъём в верхний регистр передвигает "a_b" из положения ниже "ab" в положение выше него. Ни одна из функций не знает, что é положено стоять между e и f

Обычные инструменты Delphi падают по обе стороны черты:

• CompareStr, строковый оператор < и TComparer<string>.Default (который зовёт CompareStr) ординарны и чувствительны к регистру, так что TArray.Sort<string> без компаратора ставит Z раньше f
• CompareText и SameText ординарны после свёртки регистра, ограниченной ASCII
• AnsiCompareText и WideCompareText в Delphi RTL на Windows зовут CompareString(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...) — тот же вызов, что совпадает с Excel. Отсортированный TStringList с его дефолтами (UseLocale True, CaseSensitive False) идёт через AnsiCompareText и потому тоже согласен с Excel
• На POSIX-целях Delphi RTL направляет AnsiCompareText через коллатор ICU — другой алгоритм с другими правилами пунктуации, а AnsiCompareText у Free Pascal на Windows зовёт CompareStringA после конверсии в ANSI-кодовую страницу, теряя всякий символ, который она не может представить

Так что функции RTL, учитывающие локаль, верны на Windows по реализации, а не по контракту, и коду, которому нужен порядок Excel, лучше делать вызов API явно. Внутри HotXLS была та же смесь. Операторы сравнения поднимали обе строки в верхний регистр и сравнивали кодовые точки, ветки > / < функций критериев пользовались чувствительным к регистру сравнением Variant из Delphi, а VLOOKUP / HLOOKUP сопоставляли текст тем же чувствительным к регистру сравнением Variant — оттого VLOOKUP("ABC",A1:A20,1,FALSE) и не мог найти abc. Сортировка диапазонов уже пользовалась WideCompareText. Три пути, три порядка

Что изменилось в HotXLS v2.384.67?

С v2.384.67 сравнения текста с текстом в вычислительных и сортировочных путях HotXLS идут через одну функцию, XlsCompareText в lxStandard.pas, которая зовёт CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...) и вычитает CSTR_EQUAL. Звонящие — шесть операторов сравнения, поэлементные сравнения в формулах массивов, ветки >, <, >= и <= критериев в духе COUNTIF и функций баз данных, VLOOKUP и HLOOKUP (точные и приближённые), помощники упорядочивания за функциями динамических массивов и XLOOKUP / XMATCH, а также сортировка диапазонов обоих движков. Маршрутизация сортировки диапазонов через ту же функцию гарантирует, что порядок сортировки и порядок сравнения больше не разъедутся, а это важно, потому что приближённый VLOOKUP по тексту осмыслен, лишь когда столбец отсортирован в том порядке, в каком поиск сравнивает

uses
  System.Variants, lxHandleX;

var
  Book: TXLSXWorkbook;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Sheets.Add('Data');  // Calculate вычисляет на активном листе
    Writeln(VarToStr(Book.Calculate('="a-b">"ab"')));   // True: дефис лишь разрешает ничью
    Writeln(VarToStr(Book.Calculate('="a-b"="ab"')));   // False: ничья разрешена, не равны
    Writeln(VarToStr(Book.Calculate('="a~b"<"ab"')));   // True: пунктуация первой
    Writeln(VarToStr(Book.Calculate('="ABC"="abc"')));  // True: регистр игнорируется
  finally
    Book.Free;
  end;
end;

Сравнения разных типов — отдельное правило, и оно не менялось: всякое число ниже всякого текстового значения, а всякое текстовое значение ниже всякого boolean, как описано в статье о цепочках сравнения, пустых операндах и SUMIF. Word sort вступает в силу, лишь когда оба операнда текстовые. Соответствие wildcards тоже отдельно: критерий вроде "a*" или "=ab" — это проверка шаблона или равенства, разобранная в руководстве по wildcards Excel в COUNTIF, MATCH и DSUM, а обсуждаемая здесь коллация решает только операторы упорядочивания

Следующий пример грузит 20 тестовых слов в столбец, сортирует его через TXLSXWorksheet.SortRange и проверяет счётчик критерия и поиск. Счётчики — те, что Excel 16 вернул для того же столбца

const
  Words: array [0..19] of string = ('ab', 'a-b', 'a~b', 'a_b', 'AB', 'a b',
    'ab1', 'ab-', '-ab', 'abc', 'a''b', 'Ab', 'b', 'a.b', 'a1b', 'a0',
    #$00E9, 'e', 'f', 'Z');
var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  i: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Words');
    for i := 0 to High(Words) do
      Sheet.Cells[i + 1, 1].Value := WideString(Words[i]);

    // Excel 16 на том же столбце: 11, 11, 14
    Writeln(VarToStr(Book.Calculate('=COUNTIF(A1:A20,">ab")')));
    Writeln(VarToStr(Book.Calculate('=COUNTIF(A1:A20,"<a-b")')));
    Writeln(VarToStr(Book.Calculate('=COUNTIF(A1:A20,">=AB")')));

    // До v2.384.67 было #N/A: поиск сравнивал с учётом регистра
    Sheet.Cells[1, 3].Formula := '=VLOOKUP("ABC",A1:A20,1,FALSE)';
    Book.Recalculate;
    Writeln(VarToStr(Sheet.Cells[1, 3].Value));                // abc

    // Один ключевой столбец, по возрастанию: a b, a.b, a_b, a~b, a0, a1b, ab, AB, Ab, ...
    Sheet.SortRange(1, 1, 20, 1, [1], [False]);
    for i := 1 to 20 do
      Writeln(VarToStr(Sheet.Cells[i, 1].Value));
  finally
    Book.Free;
  end;
end;

TXLSXWorksheet.SortRange использует стабильную сортировку слиянием, так что ab, AB и Ab, сравнивающиеся как равные, сохраняют относительный порядок, который был у них до сортировки. Пустые ячейки уходят в конец в обоих направлениях, как в Excel

Как повторить порядок сортировки Excel в собственном Delphi-коде?

Чтобы повторить текстовый порядок Excel в собственном Delphi-коде, зовите CompareStringW с LOCALE_USER_DEFAULT и NORM_IGNORECASE и не добавляйте SORT_STRINGSORT или NORM_IGNOREWIDTH. Возвращаемое значение — не знаковый результат сравнения: API возвращает CSTR_LESS_THAN (1), CSTR_EQUAL (2) или CSTR_GREATER_THAN (3), а при сбое вызова — 0. Вычтите 2, чтобы получить привычную конвенцию отрицательное / ноль / положительное, и сперва проверяйте 0, потому что сбой, принятый за результат, становится -2 — беззвучным «меньше»

uses
  Winapi.Windows, System.SysUtils, System.Generics.Defaults,
  System.Generics.Collections;

// Текстовый порядок Excel: word sort локали пользователя, без учёта регистра
function ExcelCompareText(const A, B: string): Integer;
var
  R: Integer;
begin
  R := CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE,
    PWideChar(A), Length(A), PWideChar(B), Length(B));
  if R = 0 then
    RaiseLastOSError;          // 0 — это сбой, а не результат сравнения
  Result := R - CSTR_EQUAL;    // 1/2/3 становятся -1/0/1
end;

var
  Keys: TArray<string>;
begin
  Keys := ['abc', 'a-b', 'AB', 'a~b', '-ab', 'ab'];
  TArray.Sort<string>(Keys, TComparer<string>.Construct(
    function(const L, R: string): Integer
    begin
      Result := ExcelCompareText(L, R);
    end));
  // a~b, ab / AB (равны, в любом порядке), a-b, -ab, abc
end;

TArray.Sort нестабилен, так что ключи, сравнивающиеся как равные, скажем ab и AB, могут выйти в любом порядке; если исходный порядок равных ключей важен, сортируйте массив индексов с исходной позицией как вторичным ключом. Встречается и обратный случай: иногда столбец не должен следовать порядку Excel, например артикулы, где X-100 и X100 — разные коды и должны сортироваться по кодовым точкам. У TXLSXWorksheet.SortRange есть перегрузка, принимающая TXLSSortCompareEvent — метод с сигнатурой function(const Left, Right: Variant): Integer of object, — и пользующаяся им вместо встроенного сравнения

uses
  System.SysUtils, System.Variants, lxStandard, lxHandleX;

type
  TPartNumberOrder = class
    function Compare(const Left, Right: Variant): Integer;
  end;

function TPartNumberOrder.Compare(const Left, Right: Variant): Integer;
begin
  // Собственный компаратор тоже получает пустые ячейки (как Null): разместите их сами
  if VarIsNull(Left) or VarIsNull(Right) then
    Exit(Ord(VarIsNull(Left)) - Ord(VarIsNull(Right)));
  Result := CompareStr(VarToStr(Left), VarToStr(Right));   // ординарное, с учётом регистра
end;

var
  Sheet: TXLSXWorksheet;   // заполненный лист, строки 2..501, столбцы A..D
  Order: TPartNumberOrder;
begin
  // ...
  Order := TPartNumberOrder.Create;
  try
    // ключ по столбцу A, по возрастанию
    Sheet.SortRange(2, 1, 501, 4, [1], [False], xlsSortByRows,
      xlsSortExcelLike, Order.Compare);
  finally
    Order.Free;
  end;
end;

Когда подан собственный компаратор, HotXLS пропускает собственную обработку пустых ячеек и передаёт сырые значения ключей, так что компаратор обязан разбираться с Null. Для нисходящего ключа HotXLS меняет знак того, что вернул компаратор, что тоже сдвигает пустые ячейки наверх, если компаратор этого не учитывает. Помните: столбец, отсортированный так, больше не лежит в порядке, которого ждут приближённый VLOOKUP Excel или XLOOKUP с бинарным поиском; грабли этих режимов на данных, отсортированных в другом порядке, разобраны в руководстве по режимам бинарного поиска XLOOKUP и XMATCH

Почему одна и та же книга может сортироваться по-разному на другой машине?

Одна и та же книга может сортироваться по-разному на другой машине, потому что текстовый порядок Excel зависит от локали пользователя Windows, и HotXLS нарочно следует этой зависимости. Word sort зависит от языка: шведская коллация, например, ставит ä после z, тогда как английская и немецкая держат её рядом с a. Excel наследует это от локали, под которой работает, так что книга, пересчитанная стокгольмским коллегой, может вернуть другой COUNTIF(...,">y"), чем тот же файл на десктопе в Чикаго. HotXLS передаёт LOCALE_USER_DEFAULT, чтобы его результаты равнялись результатам Excel на той же машине; любая фиксированная локаль сделала бы HotXLS расходящимся с Excel на каждой машине с иной настройкой

Для серверной генерации из этого следует три практических вывода:

• Локаль, которая имеет значение, — локаль учётной записи, под которой работает процесс. Служба Windows или пул приложений IIS может пользоваться другим региональным форматом, чем десктоп разработчика, так что результаты, наблюдённые в IDE, — не автоматически то, что посчитает прод
• Закэшированные результаты формул, записанные в файл, отражают локаль генерирующей машины. Excel пересчитывает по своей локали, так что значение может измениться, когда файл откроют в другом месте и пересчитают; это поведение Excel, а не артефакт HotXLS
• Локали расходятся по большей части на буквах с диакритикой, на буквосочетаниях, которые некоторые языки считают одной буквой, и на не-латинских письменностях, так что тестовые данные из одних английских слов проблему не вскроют

Платформенная граница проста. HotXLS — библиотека для Windows, собираемая для Win32 и Win64 с Delphi и C++Builder и для целей win32 / win64 с Lazarus и Free Pascal, и все эти сборки зовут один и тот же CompareStringW. Отдельного не-Windows пути коллации нет. Единственный фоллбэк — на случай сбойного вызова API: если CompareStringW вернул 0, XlsCompareText сравнивает поднятые в верхний регистр строки по кодовым единицам вместо того, чтобы бросить исключение посреди пересчёта, — вычисление продолжает идти, но порядок Excel больше не гарантирован

Шпаргалка: сравнение текста Excel в HotXLS

• Правило: word sort локали пользователя с NORM_IGNORECASE, без SORT_STRINGSORT, без NORM_IGNOREWIDTH, в HotXLS с v2.384.67
• - и ' лишь разрешают ничьи: ="a-b">"ab" даёт TRUE, а ="a-b"="ab" — FALSE
• Прочая пунктуация сортируется раньше цифр, цифры раньше букв: ="a~b"<"ab" и ="a0"<"ab" дают TRUE
• Регистр никогда не важен: ="ABC"="abc" даёт TRUE, а VLOOKUP("ABC",...) находит abc
• Покрытые пути: операторы сравнения, сравнения массивов, критерии > / <, VLOOKUP / HLOOKUP, упорядочивание динамических массивов, SortRange в обоих движках
• Не покрыты этим правилом: смешанные типы (число < текст < boolean) и критерии wildcards — у них свои правила
• В Delphi-коде: CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...), проверяйте 0, вычитайте CSTR_EQUAL; избегайте CompareText, CompareStr и TComparer<string>.Default, когда результат обязан совпадать с Excel
• Результаты зависят от локали учётной записи, под которой идёт код, — и в Excel, и в HotXLS

Обычные слова сортируются одинаково при всяком правиле, так что неверную коллацию вскрывают только коды с дефисами, пунктуация и имена с диакритикой. HotXLS теперь даёт ответ Excel по всем ним в обоих движках, XLS и XLSX. Детали лицензирования, поддерживаемых версий Delphi и C++Builder и пробная загрузка — на странице HotXLS Delphi Excel component page