Текстово сравнение в HotXLS: word sort редът на Excel

HotXLS Delphi Component сравнява две текстови стойности както Excel 16 от v2.384.67 насам: без отчитане на регистъра, в „word sort“ реда на Windows локала на потребителя — това, което CompareStringW връща с флага NORM_IGNORECASE. Тиретата и апострофите се прескачат в първия проход и решават само при равенство, така че ="a-b">"ab" е TRUE, докато останалата пунктуация се сортира пред цифрите и буквите, така че ="a~b"<"ab" също е TRUE. Същият ред сега управлява операторите за сравнение, критериите с > / <, сортирането на диапазони и VLOOKUP

Никой не пусне bug с заглавие „несъответствие на collation“. Докладите казват, че COUNTIF(A:A,">M") брои с два реда повече на сървъра, отколкото в Excel, че ценова листа, сортирана от reporting service-а, слага 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: буквите се сравняват по своя лингвистичен ред, а не по кодовите си точки, ударените букви седят до базовата си буква, а два знака получават специално отношение. Тирето - и апострофът ' се игнорират в първия проход, така че 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 са един и същ ключ, доколкото всяко сравнение се отнася. Сортирането на 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 и половината двойки, построени като едва разминаващи се една с друга. Excel 16 изчисли IF(A<B,-1,IF(A=B,0,1)) за всяка двойка, а присъдите бяха сверени срещу API-то за сравнение на Windows с различни набори флагове

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

Вторa, ръчно подбрана проверка сравни всичките 190 двойки, изтеглени от 20 хитри думи, и резултата от Range.Sort на Excel върху същата колона. И двете се съгласиха с чистия word sort с NORM_IGNORECASE, а тези 190 присъди плюс сортираният ред сега са част от regression suite-а на HotXLS, минаващ през и двата двигателя — класическия TXLSWorkbook и XLSX-нативния TXLSXWorkbook

Защо CompareText и ordinal сравнението грешат?

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

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

• CompareStr, string операторът < и TComparer<string>.Default (който вика CompareStr) са ordinal и с отчитане на регистъра, така че TArray.Sort<string> без comparer слага Z пред f
• CompareText и SameText са ordinal след сгъване на регистъра само за 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 collator — различен алгоритъм с различни правила за пунктуация — а AnsiCompareText на Free Pascal под Windows вика CompareStringA след конверсия към ANSI кодовата страница, което губи всеки знак, който страницата не може да представи

Значи locale-съзнаващите RTL функции са верни под Windows по имплементация, не по договор, а код, който има нужда от реда на Excel, е по-добре изрично да вика API-то. HotXLS имаше същата смесица отвътре. Операторите за сравнение upper-case-ваха и двата низа и сравняваха кодови точки, > / < клоновете на функциите за критерии ползваха case-sensitive Variant сравнението на Delphi, а VLOOKUP / HLOOKUP съвпадаха текста със същото case-sensitive 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. Повикващите са шестте оператора за сравнение, сравненията по елементи в array формулите, >, <, >= и <= клоновете на COUNTIF-подобните критерии и database функциите, VLOOKUP и HLOOKUP (точни и приблизителни), подреждащите помощници зад dynamic-array функциите и 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 се прилага само щом и двата операнда са текст. Съвпадението с wildcard също е отделно: критерий като "a*" или "=ab" е шаблон или тест за равенство, разгледан в ръководството за wildcard в COUNTIF, MATCH и DSUM, а collation-ът, обсъден тук, решава само подреждащите оператори

Следващият пример зарежда 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")')));

    // Беше #N/A преди v2.384.67: търсенето сравняваше с отчитане на регистъра
    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 ползва стабилно merge сортиране, така че 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 на потребителския locale, без регистър
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 — например part numbers, където X-100 и X100 са различни кодове и трябва да се сортират по кодова точка. TXLSXWorksheet.SortRange има overload, който приема 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;   // попълнен лист, редове 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 прескача собствената си обработка на празни клетки и подава суровите ключови стойности, така че comparer-ът трябва да се справи с Null. За низходящ ключ HotXLS обръща знака на каквото comparer-ът върне, което мести и празните клетки най-отгоре, освен ако comparer-ът не го предвиди. Имайте предвид, че колона, сортирана така, вече не е в реда, който приблизителният VLOOKUP на Excel или binary-search XLOOKUP очакват; капаните на тези режими върху данни, сортирани в друг ред, са разгледани в ръководството за binary search режимите на XLOOKUP и XMATCH

Защо една и съща работна книга може да се сортира различно на друга машина?

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

Три практически последици следват за генерирането от страна на сървъра:

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

Платформената граница е проста. HotXLS е Windows библиотека, строена за Win32 и Win64 с Delphi и C++Builder и за win32 / win64 цели с Lazarus и Free Pascal, и всички тези build-ове викат същото CompareStringW. Няма отделна не-Windows collation пътека. Единственият fallback е при неуспешно повикване на API-то: ако CompareStringW върне 0, XlsCompareText сравнява upper-case-натите низове по кодови единици, вместо да вдига 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 arrays, SortRange в двата двигателя
• Непокрити от това правило: смесените типове (число < текст < boolean) и wildcard критериите — те имат собствени правила
• В Delphi код: CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...), проверете за 0, извадете CSTR_EQUAL; избягвайте CompareText, CompareStr и TComparer<string>.Default, когато резултатът трябва да съвпада с Excel
• Резултатите зависят от локала на акаунта, изпълняващ кода — в Excel и в HotXLS еднакво

Обикновените думи се сортират еднакво при всяко правило, така че само кодовете с тире, пунктуацията и ударените имена излагат грешен collation. HotXLS вече дава отговора на Excel за всички тях и в двата двигателя, XLS и XLSX. Подробности за лицензирането, поддържаните версии на Delphi и C++Builder и пробното изтегляне са на страницата на HotXLS Delphi Excel component