Порівняння тексту 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. Цей самий порядок тепер рухає оператори порівняння, критерії > / <, сортування range і VLOOKUP

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

Яке правило Excel уживає, щоб порівняти два текстові рядки?

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

Таблиця показує, що це означає на практиці, поруч із двома порівняннями, до яких найімовірніше дотягнеться Delphi-розробник. Стовпець Excel тримає вердикти, які повернув Excel 16 для IF(A<B,...), — їх HotXLS відтворює від v2.384.67

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

Два наслідки легко пропустити. Перший: роль дефіса як тай-брейка означає, що ="a-b"="ab" — FALSE: рядки — близькі сусіди в сортуванні, але не рівні. Другий: рівність повністю ігнорує регістр, тож ab, AB і Ab — той самий key для будь-якого порівняння. Сортування 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 не називає collation. Тест згенерував 4 000 випадкових пар рядків з ASCII пунктуації, цифр, обох регістрів, пробілів, é, ß, ä, китайських символів, full-width форм і нерозривного пробілу, з довжинами від 0 до 4, і половина пар була побудована як near-miss одна до одної. Excel 16 обчислив IF(A<B,-1,IF(A=B,0,1)) для кожної пари, а вердикти звірялися з Windows comparison API з різними наборами прапорців

• NORM_IGNORECASE сам-один (усталений word sort, локаль користувача): жодної справжньої розбіжності. Єдині 7 відмінностей — клітинки, чиїм цілим вмістом був ', який Excel споживає як text prefix символ, тож це були артефакти вибірки, а не відмінності collation
• NORM_IGNORECASE разом із SORT_STRINGSORT: 41 розбіжність. String sort трактує дефіс та апостроф як звичайні символи — це рівно та поведінка, якої в Excel немає
• Доданий NORM_IGNOREWIDTH: неправильно в інший бік, бо він зрівнює full-width і half-width форми тієї самої літери, а Excel тримає їх окремо

Друга, відібрана вручну перевірка порівняла всі 190 пар із 20 підступних слів і результат Range.Sort Excel над тим самим стовпцем. Обидва збіглися з plain NORM_IGNORECASE word sort, і ті 190 вердиктів плюс відсортований порядок тепер — частина regression suite HotXLS, що проганяється через обидва engines: classic TXLSWorkbook та XLSX-нативний TXLSXWorkbook

Чому CompareText і ordinal порівняння помиляються?

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

Звичні Delphi-інструменти падають по обидва боки межі:

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

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

Що змінилося в HotXLS v2.384.67?

Від v2.384.67 порівняння текст-проти-тексту у calculation і sorting шляхах HotXLS йдуть через одну функцію, XlsCompareText у lxStandard.pas, яка викликає CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...) і віднімає CSTR_EQUAL. Викликачі — шість операторів порівняння, поелементні порівняння в array формулах, гілки >, <, >= і <= критеріїв на кшталт COUNTIF та database функцій, VLOOKUP і HLOOKUP (exact та approximate), допоміжні функції впорядкування за dynamic-array функціями та XLOOKUP / XMATCH, а також сортування range обох engines. Пропустити сортування range через ту саму функцію — гарантія, що sort order і comparison order більше не розсинхронізуються, а це важливо, бо approximate VLOOKUP по тексту має сенс лише коли стовпець відсортовано в тому порядку, в якому lookup порівнює

uses
  System.Variants, lxHandleX;

var
  Book: TXLSXWorkbook;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Sheets.Add('Data');  // Calculate обчислює проти активного sheet
    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 застосовується лише коли обидва операнди — текст. Wildcard зіставлення теж окреме: критерій на кшталт "a*" чи "=ab" — це перевірка шаблоном чи на рівність, покрита в посібнику з Excel wildcard у COUNTIF, MATCH і DSUM, а обговорювана тут collation вирішує тільки оператори впорядкування

Наступний приклад завантажує 20 тестових слів у стовпець, сортує його через TXLSXWorksheet.SortRange і перевіряє критеріальний рахунок та lookup. Рахунки — ті самі, що повернув 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")')));

    // Було #N/A до v2.384.67: lookup порівнював з урахуванням регістру
    Sheet.Cells[1, 3].Formula := '=VLOOKUP("ABC",A1:A20,1,FALSE)';
    Book.Recalculate;
    Writeln(VarToStr(Sheet.Cells[1, 3].Value));                // abc

    // Один key-стовпець, за зростанням: 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 уживає стабільне merge sort, тож 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 — різні коди і мусять сортуватися за code point. 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
  // Власний comparer теж отримує порожні клітинки (як Null): розмістіть їх самі
  if VarIsNull(Left) or VarIsNull(Right) then
    Exit(Ord(VarIsNull(Left)) - Ord(VarIsNull(Right)));
  Result := CompareStr(VarToStr(Left), VarToStr(Right));   // ordinal, з регістром
end;

var
  Sheet: TXLSXWorksheet;   // заповнений sheet, рядки 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;

Коли подано власний comparer, HotXLS пропускає власну обробку порожніх клітинок і передає сирі key-значення, тож comparer мусить розібратися з Null. Для спадного ключа HotXLS змінює знак того, що поверне comparer, що також пересуває порожні нагору, якщо comparer про них не подбає. Майте на увазі: стовпець, відсортований так, більше не в тому порядку, якого чекають approximate VLOOKUP Excel чи binary-search XLOOKUP; підводні камені тих режимів на даних, відсортованих в іншому порядку, покриті в посібнику з binary search режимів XLOOKUP і XMATCH

Чому той самий workbook може сортуватися інакше на іншій машині?

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

Для server-side генерації звідси випливають три практичні наслідки:

• Локаль, що рахується, — локаль акаунта, під яким працює процес. Windows-сервіс чи IIS application pool можуть уживати інших регіональних форматів, ніж десктоп розробника, тож результати, побачені в IDE, не автоматично те, що порахує production
• Кешовані результати формул, записані у файл, відображають локаль машини-генератора. Excel перераховує зі своєю локаллю, тож значення може змінитися, коли файл відкриють деінде і перерахують; це поведінка Excel, а не артефакт HotXLS
• Локалі розходяться переважно на літерах з діакритикою, на комбінаціях літер, які деякі мови рахують однією літерою, і на не-латинських писемностях, тож тестові дані, обмежені простими англійськими словами, проблеми не виявлять

Платформна межа проста. HotXLS — Windows-бібліотека, збудована для Win32 і Win64 з Delphi та C++Builder і для win32 / win64 цілей з Lazarus і Free Pascal, і всі ці збірки викликають той самий CompareStringW. Окремого не-Windows collation шляху немає. Єдиний fallback — на провалений виклик API: якщо CompareStringW повертає 0, XlsCompareText порівнює підняті у верхній регістр рядки за code unit замість того, щоб піднімати exception посеред перерахунку, — це тримає обчислення живим, але вже не гарантує порядок 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
• Покриті шляхи: оператори порівняння, array порівняння, критерії > / <, VLOOKUP / HLOOKUP, впорядкування dynamic array, SortRange в обох engines
• Не покриті цим правилом: змішані типи (number < text < boolean) і wildcard критерії — у них свої правила
• У Delphi-коді: CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...), перевірка на 0, віднімання CSTR_EQUAL; уникайте CompareText, CompareStr і TComparer<string>.Default, коли результат мусить збігатися з Excel
• Результати залежать від локалі акаунта, під яким виконується код, — і в Excel, і в HotXLS

Звичайні слова сортуються однаково за будь-якого правила, тож лише дефісові коди, пунктуація та імена з діакритикою викривають неправильну collation. HotXLS тепер дає відповідь Excel на всіх них в обох engines, XLS і XLSX. Деталі ліцензування, підтримуваних версій Delphi і C++Builder та пробне завантаження — на сторінці HotXLS Delphi Excel component