Техническая статья

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

В Excel ="a-b">"ab" возвращает TRUE, и ="a~b"<"ab" тоже возвращает TRUE. Скормите те же пары Delphi-функции CompareText — и оба ответа перевернутся: в порядке кодовых точек дефис стоит ниже букв, а тильда выше. Excel сравнивает текст с учётом нечувствительного к регистру «word sort» из локали пользователя Windows, где дефисы и апострофы лишь разрешают ничьи, а вся остальная пунктуация сортируется раньше цифр и букв

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

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

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

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

A против BExcel / HotXLSCompareStr (ординарное)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 неудобных слов сортируется в 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 позиция пропущенного символа решает: завершающий дефис первым, начальный последним

Схема word sort HotXLS, ранжирующая все 20 тестовых слов от a b, a.b, a_b и a~b через a0 и a1b, затем группу ab с AB и Ab, варианты с дефисом и апострофом вроде a-b и a'b, до abc, b, e, e с диакритикой, f и Z; пунктуация раньше цифр, цифры раньше букв, регистр игнорируется
Пунктуация и пробел сортируются раньше цифр, цифры — раньше букв, регистр схлопывается, а дефис с апострофом лишь разрешают ничьи; потому a-b ложится рядом с ab, но всё же сравнивается как большее

Как самому проверить коллацию против Excel

Документация Excel ни разу не называет коллацию, которой пользуется, так что надёжный способ убедиться на своей машине — дать Excel рассудить пачку строковых пар и сверить его вердикты с CompareStringW под разными наборами флагов. Положите пары в два столбца, заставьте Excel вычислить =IF(A1<B1,-1,IF(A1=B1,0,1)) для каждой строки и прогоните те же пары через API. Стоящие пары смешивают ASCII-пунктуацию, цифры, оба регистра, пробелы, буквы с диакритикой вроде é, ß и ä, CJK-символы, полноширинные формы и неразрывный пробел, причём с изрядным числом почти-совпадений, различающихся одним символом. Случайные слова из словаря — плохая выборка: обычные буквы сортируются одинаково при всех правилах из этой статьи

Наборы флагов дают заметно разные результаты:

  • Один NORM_IGNORECASE с LOCALE_USER_DEFAULT — обычный word sort, именно ему следует Excel
  • Добавление SORT_STRINGSORT переключает на string sort, где дефис и апостроф становятся обычными символами с полным весом, и каждая пара вроде a-b / ab выходит наоборот
  • Добавление NORM_IGNOREWIDTH делает полноширинную букву равной её полуширинной форме, а Excel держит эти две отдельно

Одна ловушка сидит в самом тестовом каркасе. Excel трактует апостроф в начале ячейки как префикс «хранить как текст», поэтому ячейка, чей весь ввод — ', оказывается пустой, и любая пара, собранная так, сообщает о несовпадении, к коллации отношения не имеющем. Собирайте такие пары строковыми литералами в формуле (="'") или пропускайте

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

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

Схема сравнения HotXLS, противопоставляющая порядок кодовых точек и word sort Excel: ординарное сравнение ставит апостроф, дефис и подчёркивание на 0x27, 0x2D и 0x5F среди букв, поэтому a-b против ab выходит как меньшее, а word sort двигает пунктуацию перед цифрами и буквами и трактует как разрешителей ничьих только дефис и апостроф
Кодовые точки раскидывают пунктуацию вокруг букв, поэтому ординарное сравнение и сравнение со свёрткой ASCII переворачивают вердикты; word sort выносит пунктуацию перед цифрами и понижает дефис с апострофом до разрешителей ничьих

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

  • CompareStr, строковый оператор < и TComparer<string>.Default ординарны и чувствительны к регистру, так что 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 конвертирует строку в ANSI-кодовую страницу и зовёт CompareStringA, теряя всякий символ, который она не может представить

Так что locale-зависимые функции RTL на Windows оказываются правы просто потому, что так они реализованы сегодня. Код, который обязан соглашаться с Excel, должен звать API явно и говорить об этом. Более сложная проблема всплывает в любом движке формул, где каждый путь кода хватает ближайшее сравнение: верхний регистр и кодовые точки в операторах = и <, чувствительное к регистру Variant-сравнение в ветке > / < критериев COUNTIF, ещё одно внутри VLOOKUP (ровно поэтому VLOOKUP("ABC",A1:A20,1,FALSE) не находит abc) и locale-вызов в сортировке диапазона. Каждый путь по отдельности оправдан. Вместе они дают три разных порядка над одним столбцом

Одно сравнение за формулами, поисками и сортировкой в HotXLS

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

Схема маршрутизации HotXLS, показывающая всякий путь сравнения текста — от шести операторов сравнения и критериев в духе COUNTIF через VLOOKUP, HLOOKUP, XLOOKUP и сортировку диапазонов обоих движков — сходящийся на XlsCompareText, который зовёт CompareStringW с LOCALE_USER_DEFAULT и NORM_IGNORECASE и отображает 1, 2, 3 в -1, 0, 1
Операторы, критерии, поиски и сортировка делят одну функцию, так что порядок, который видит Excel, и порядок, которым сортирует HotXLS, не разъедутся; API возвращает 1, 2 или 3, а ноль означает сбой, а не «меньше»

Быстрее всего увидеть правило из Delphi можно через TXLSXWorkbook.Calculate, который вычисляет строку формулы на активном листе и возвращает результат как Variant

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;

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

Следующий пример загружает 20 слов в столбец A листа, проверяет три счётчика критериев и поиск, затем сортирует столбец через TXLSXWorksheet.SortRange. Ожидаемые счётчики напрямую следуют из показанного выше порядка сортировки: одиннадцать слов сортируются после группы ab, одиннадцать — раньше a-b, а три члена группы ab присоединяются к одиннадцати для >=AB

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]);

    // Ожидаемо из порядка word sort: 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")')));

    // Находит abc: поиск сравнивает без учёта регистра, как и Excel
    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;

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

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

Чтобы воспроизвести текстовый порядок Excel вне книги, зовите 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;

Если HotXLS уже в uses, lxStandard.XlsCompareText публичен и возвращает тот же порядок. Различие двух вариантов только в сбойном вызове API: функция выше бросает исключение, тогда как XlsCompareText откатывается к сравнению поднятых в верхний регистр строк по кодовым единицам, чтобы пересчёт не прерывался на полпути. Для автономной сортировки лучше бросать исключение: беззвучно неправильный порядок выслеживать труднее, чем исключение

TArray.Sort не стабилен, поэтому равные ключи вроде ab и AB могут выйти в любом порядке. Когда исходный порядок равных ключей важен, сортируйте массив индексов и используйте исходную позицию как вторичный ключ

Иногда столбец должен нарочно игнорировать порядок Excel. Классический случай — артикулы: X-100 и X100 — разные коды и должны сортироваться по кодовым точкам. У 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;

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

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

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

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

  • Локаль, которая имеет значение, принадлежит учётной записи, под которой работает процесс. У службы Windows или пула приложений IIS региональные настройки могут отличаться от десктопа разработчика, так что результаты, видимые в IDE, не обязаны совпадать с тем, что посчитает производство
  • Кэшированные результаты формул, записанные в файл, отражают локаль машины-генератора. Excel пересчитывает своей локалью, так что значение может измениться, когда файл откроют и пересчитают в другом месте; Excel ведёт себя так же со своими файлами
  • Локали расходятся в основном на буквах с диакритикой, на парах букв, которые некоторые языки считают одной буквой, и на не-латинских письменностях. Тестовые данные из обычных английских слов разницу никогда не покажут, так что положите в выборку пару имён вроде Ångström и Öberg

Совпадающий с Excel путь — Windows: каждая сборка Win32 и Win64, собрана ли она Delphi, C++Builder или Lazarus с Free Pascal, зовёт тот же CompareStringW. Когда сборка нацелена на не-Windows платформу, XlsCompareText использует WideCompareText из RTL, чей порядок берётся из коллационной библиотеки самой платформы

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