HotXLS metin karşılaştırma: Delphi'de Excel word sort sırası

HotXLS Delphi Component, iki metin değerini v2.384.67'den beri Excel 16'nın yaptığı gibi karşılaştırır: büyük-küçük harf duyarsız olarak, Windows kullanıcı locale'inin "word sort" düzeninde; bu da CompareStringW'nin NORM_IGNORECASE bayrağıyla döndürdüğü şeydir. Kısa çizgi ile kesme işareti ilk geçişte atlanır ve yalnızca eşitlik bozar; dolayısıyla ="a-b">"ab" TRUE'dur, öteki noktalama ise rakamlardan ve harflerden önce sıralanır; dolayısıyla ="a~b"<"ab" da TRUE'dur. Artık aynı sıra karşılaştırma operatörlerini, > / < ölçütlerini, aralık sıralamasını ve VLOOKUP'u sürüyor

Kimse "collation uyuşmazlığı" başlıklı bir hata bildirmez. Bildirilen şey şudur: COUNTIF(A:A,">M") sunucuda Excel'dekinden iki satır fazla sayıyor; raporlama servisinin sıraladığı fiyat listesi X-100'ü Excel'in koymayacağı bir yere koyuyor; ya da sütunda abc bariz dururken VLOOKUP("ABC",...) #N/A döndürüyor. Üçü de aynı sorudan çıkar: iki operand da metinken hangisi küçük? Excel'in kesin bir yanıtı var, çoğu Delphi kodunun verdiği yanıt o değil; v2.384.67 öncesinde HotXLS de hangi kod yolu sorduysa üç farklı yanıttan birini veriyordu

Excel iki metin string'ini karşılaştırmak için hangi kuralı kullanır?

Excel metni, büyük-küçük harfi yok sayarak kullanıcı locale'inin word sort'u ile karşılaştırır. Word sort, Windows NLS karşılaştırma fonksiyonlarının varsayılan collation'ıdır: harfler kod noktalarına göre değil dilsel sıralarına göre kıyaslanır, aksanlı harfler temel harflerinin yanında durur ve iki karakter özel işlem görür. Kısa çizgi - ile kesme işareti ' ilk geçişte yok sayılır; dolayısıyla co-op ile coop birbirinin yanına düşer ve string'lerin gerisi eşitlendiğinde varlıkları sırayı belirler. Öteki her noktalama işareti anlamlıdır ve rakamlardan önce sıralanır; rakamlar da harflerden önce sıralanır

Tablo, bunun pratikte ne anlama geldiğini; bir Delphi geliştiricisinin en çok uzanacağı iki karşılaştırmanın yanında gösteriyor. Excel sütunu, Excel 16'nın IF(A<B,...) için döndürdüğü hükümleri taşır; HotXLS bunları v2.384.67'den beri aynen üretir

A vs B	Excel 16 / HotXLS	CompareStr (ordinal)	CompareText
"a-b" vs "ab"	büyük	küçük	küçük
"a'b" vs "ab"	büyük	küçük	küçük
"a~b" vs "ab"	küçük	büyük	büyük
"a_b" vs "ab"	küçük	küçük	büyük
"ab" vs "AB"	eşit	büyük	eşit
"é" vs "f"	küçük	büyük	büyük
"Z" vs "f"	büyük	küçük	büyük

İki sonuç kaçırması kolaydır. Birincisi, kısa çizginin eşitlik-bozma rolü ="a-b"="ab"'nin FALSE olmasını gerektirir: string'ler sıralamada birbirine çok yakın komşudur ama eşit değildir. İkincisi, eşitlik büyük-küçük harfi tümüyle yok sayar; dolayısıyla ab, AB ve Ab, herhangi bir karşılaştırma için aynı anahtardır. 20 test kelimesini Excel'in Range.Sort'uyla sıralamak şunu verir: 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 grubunun içinde yok sayılan karakterin konumu karar verir

Excel'in metin sırası nasıl tespit edildi?

Excel'in metin sırası ölçümle tespit edildi, dokümantasyonla değil; çünkü Excel'in dokümantasyonu collation'ı adıyla anmaz. Test; ASCII noktalaması, rakamlar, her iki harf durumu, boşluklar, é, ß, ä, Çince karakterler, tam genişlik formları ve bölünemez boşluktan 4.000 rastgele string çifti üretti; uzunluklar 0 ile 4 arasında ve çiftlerin yarısı birbirine çok yakın olacak şekilde kuruldu. Excel 16 her çift için IF(A<B,-1,IF(A=B,0,1)) değerlendirdi ve hükümler, farklı bayrak setleriyle Windows karşılaştırma API'siyle eşleştirildi

• Yalnız NORM_IGNORECASE (varsayılan word sort, kullanıcı locale'i): gerçek bir uyuşmazlık yok. Tek 7 fark, içeriği tamamen ' olan hücrelerdi; Excel bunları metin önek karakteri olarak tüketir, dolayısıyla bunlar collation farkı değil örnekleme artefaktıydı
• NORM_IGNORECASE artı SORT_STRINGSORT: 41 uyuşmazlık. String sort, kısa çizgiyle kesme işaretini sıradan simgeler sayar; tam olarak Excel'in taşımadığı davranış
• NORM_IGNOREWIDTH eklemek: başka türlü yanlış; aynı harfin tam genişlik ve yarım genişlik formlarını eşit kıyaslatır, Excel ise onları ayrı tutar

İkinci, elle seçilmiş bir kontrol, 20 çetrefilli kelimeden çekilen 190 çiftin tamamını ve Excel'in Range.Sort sonucunu aynı sütunda karşılaştırdı. İkisi de sade NORM_IGNORECASE word sort'uyla anlaştı; o 190 hüküm artı sıralı düzen artık HotXLS regresyon paketinin parçası ve hem klasik TXLSWorkbook motoru hem XLSX yerli TXLSXWorkbook motoru üzerinden koşturuluyor

CompareText ile ordinal karşılaştırma neden yanlış yapar?

CompareText ile ordinal karşılaştırma, Excel'in sırasını şünden yanlış alır: UTF-16 kod birimlerini kıyaslarlar ve kod noktası düzeni, noktalamayı harflere göre keyfi yerlere koyar. Kısa çizgi U+002D, kesme işareti U+0027'dir; ikisi de her harfin altındadır, dolayısıyla ordinal bir karşılaştırma kısa çizgiyi eşitlik-bozucu saymak yerine "a-b"'yi "ab"'den küçük bulur. Tilde U+007E her harfin üstündedir; dolayısıyla "a~b" büyük çıkar, Excel'in tam tersi. Delphi RTL'sindeki CompareText yalnızca a..z'yi büyük harfe katlar sonra kod birimlerini kıyaslar; bu da ikinci bir çarpıtmayla gelir: alt çizgi U+005F, büyük harf ile küçük harflerin arasına düşer; dolayısıyla büyük harfe katlamak "a_b"'yi "ab"'nin altından alıp üstüne taşır. Ne fonksiyon é'nin e ile f arasına ait olduğunu bilir

Alışılmış Delphi araçları çizginin iki yanına da düşer:

• CompareStr, string < operatörü ve TComparer<string>.Default (CompareStr çağırır) ordinal ve büyük-küçük harf duyarlıdır; dolayısıyla comparersız TArray.Sort<string> Z'yi f'den önce koyar
• CompareText ve SameText, yalnız-ASCII harf katlamasından sonra ordinaldır
• Windows'ta Delphi RTL'sindeki AnsiCompareText ve WideCompareText, Excel'le eşleşen çağrının aynısı olan CompareString(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...)'i çağırır. Varsayılanlarıyla (UseLocale True, CaseSensitive False) sıralı bir TStringList AnsiCompareText'ten geçer ve dolayısıyla o da Excel'le anlaşır
• POSIX hedeflerinde Delphi RTL'si AnsiCompareText'i bir ICU collator'üne yönlendirir; o, farklı noktalama kuralları olan başka bir algoritmadır. Free Pascal'ın Windows'taki AnsiCompareText'i ise ANSI kod sayfasına çevirdikten sonra CompareStringA çağırır; o sayfanın temsil edemediği her karakteri kaybeder

Demek ki locale duyarlı RTL fonksiyonları Windows'ta sözleşmeyle değil gerçeklemeyle doğru; Excel'in sırasını gerektiren kod en iyisini yapıp API çağrısını açıkça yapsın. HotXLS'in içi de aynı karışımdı. Karşılaştırma operatörleri iki string'i de büyük harfe katlayıp kod noktalarını kıyaslıyordu; ölçüt fonksiyonlarının > / < kolları Delphi'nin büyük-küçük harf duyarlı Variant karşılaştırmasını kullanıyordu; VLOOKUP / HLOOKUP da metni aynı harf duyarlı Variant karşılaştırmasıyla eşliyordu — VLOOKUP("ABC",A1:A20,1,FALSE)'un abc'yi bulamamasının nedeni buydu. Aralık sıralaması çoktan WideCompareText kullanıyordu. Üç yol, üç sıra

HotXLS v2.384.67'de ne değişti?

v2.384.67'den beri HotXLS'in hesaplama ve sıralama yollarındaki metin-metne karşılaştırmalar tek bir fonksiyondan geçer: lxStandard.pas'taki XlsCompareText; o da CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...) çağırıp CSTR_EQUAL çıkarır. Çağıranlar: altı karşılaştırma operatörü, dizi formüllerinde eleman bazında karşılaştırmalar, COUNTIF tarzı ölçütlerin ve database fonksiyonlarının >, <, >= ve <= kolları, VLOOKUP ve HLOOKUP (tam ve yaklaşık), dinamik dizi fonksiyonlarının ve XLOOKUP / XMATCH'in ardındaki sıralama yardımcıları ve iki motorun aralık sıralaması. Aralık sıralamasını aynı fonksiyondan geçirmek, sıralama düzeni ile karşılaştırma düzeninin bir daha ayrışamayacağını garanti eder; bu önemlidir çünkü metinde yaklaşık VLOOKUP yalnızca sütun, aramanın kıyasladığı düzende sıralandığında anlamlıdır

uses
  System.Variants, lxHandleX;

var
  Book: TXLSXWorkbook;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Sheets.Add('Data');  // Calculate aktif sayfaya karşı değerlendirir
    Writeln(VarToStr(Book.Calculate('="a-b">"ab"')));   // True: kısa çizgi yalnızca eşitlik bozar
    Writeln(VarToStr(Book.Calculate('="a-b"="ab"')));   // False: eşitlik bozuldu, eşit değiller
    Writeln(VarToStr(Book.Calculate('="a~b"<"ab"')));   // True: noktalama önce
    Writeln(VarToStr(Book.Calculate('="ABC"="abc"')));  // True: harf durumu yok sayıldı
  finally
    Book.Free;
  end;
end;

Çapraz tip karşılaştırmalar ayrı bir kuraldır ve değişmedi: her sayı her metin değerinin, her metin değeri her boolean'ın altındadır; ayrıntısı karşılaştırma zincirleri, boş operandlar ve SUMIF yazısında. Word sort yalnızca iki operand da metin olduğunda uygulanır. Wildcard eşlemesi de ayrıdır: "a*" ya da "=ab" gibi bir ölçüt bir örüntü ya da eşitlik testidir; COUNTIF, MATCH ve DSUM'da Excel wildcard'ları rehberinde işlenir ve burada konuşulan collation yalnızca sıralama operatörlerine karar verir

Sonraki örnek, 20 test kelimesini bir sütuna yükler, TXLSXWorksheet.SortRange ile sıralar ve bir ölçüt sayımı ile bir arama dener. Sayılar, Excel 16'nın aynı sütun için döndürdükleridir

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

    // Aynı sütunda 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 öncesinde #N/A'ydı: arama harf duyarlı kıyaslıyordu
    Sheet.Cells[1, 3].Formula := '=VLOOKUP("ABC",A1:A20,1,FALSE)';
    Book.Recalculate;
    Writeln(VarToStr(Sheet.Cells[1, 3].Value));                // abc

    // Tek anahtar sütun, artan: 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 kararlı bir merge sort kullanır; dolayısıyla eşit kıyaslanan ab, AB ve Ab, sıralama öncesi göreli düzenlerini korur. Boş hücreler her iki yönde de sona gider, Excel'deki gibi

Kendi Delphi kodumda Excel'in sıralama düzenini nasıl eşlerim?

Kendi Delphi kodunuzda Excel'in metin düzenini eşlemek için CompareStringW'yi LOCALE_USER_DEFAULT ve NORM_IGNORECASE ile çağırın; SORT_STRINGSORT ya da NORM_IGNOREWIDTH eklemeyin. Dönüş değeri işaretli bir karşılaştırma sonucu değildir: API CSTR_LESS_THAN (1), CSTR_EQUAL (2) ya da CSTR_GREATER_THAN (3) döndürür; çağrı başarısız olursa 0. Alışılmış negatif / sıfır / pozitif kuralına inmek için 2 çıkarın ve önce 0'ı test edin; çünkü sonuç sanılan bir başarısızlık -2 olur, sessiz bir "küçük"tür

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

// Excel'in metin düzeni: kullanıcı-locale word sort, harf duyarsız
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 başarısızlıktır, karşılaştırma sonucu değil
  Result := R - CSTR_EQUAL;    // 1/2/3, -1/0/1 olur
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 (eşit, herhangi bir sırada), a-b, -ab, abc
end;

TArray.Sort kararlı değildir; dolayısıyla ab ile AB gibi eşit kıyaslanan anahtarlar herhangi bir sırada çıkabilir; eşit anahtarların özgün sırası önemliyse bir indeks dizisini özgün konumu ikincil anahtar yaparak sıralayın. Tersi durum da baş gelir: bazen bir sütun Excel'in düzenini izlememelidir; örneğin X-100 ile X100'ün ayrı kodlar olduğu ve kod noktasına göre sıralanması gereken parça numaraları gibi. TXLSXWorksheet.SortRange'in bir TXLSSortCompareEvent alan bir overload'u vardır — imzası function(const Left, Right: Variant): Integer of object olan bir metot — ve gömülü karşılaştırma yerine onu kullanır

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
  // Özel bir comparer boş hücreleri de alır (Null olarak): yerleştirmeyi siz yapın
  if VarIsNull(Left) or VarIsNull(Right) then
    Exit(Ord(VarIsNull(Left)) - Ord(VarIsNull(Right)));
  Result := CompareStr(VarToStr(Left), VarToStr(Right));   // ordinal, harf duyarlı
end;

var
  Sheet: TXLSXWorksheet;   // dolu bir sheet, satır 2..501, sütun A..D
  Order: TPartNumberOrder;
begin
  // ...
  Order := TPartNumberOrder.Create;
  try
    // A sütununa göre anahtarlı, artan
    Sheet.SortRange(2, 1, 501, 4, [1], [False], xlsSortByRows,
      xlsSortExcelLike, Order.Compare);
  finally
    Order.Free;
  end;
end;

Özel bir comparer verildiğinde HotXLS kendi boş işlemesini atlar ve ham anahtar değerlerini geçirir; dolayısıyla comparer Null ile başa çıkmak zorundadır. Azalan anahtar için HotXLS, comparer'ın döndürdüğünü tersine çevirir; comparer hesaba katmadıkça bu, boşları da en üste taşır. Bu şekilde sıralanmış bir sütunun, Excel'in yaklaşık VLOOKUP ya da binary-search XLOOKUP beklentisindeki düzende artık olmadığını aklınızda tutun; o modların başka düzende sıralanmış verideki tuzakları XLOOKUP ve XMATCH binary search modları rehberinde işlenir

Aynı çalışma kitabı başka bir makinede neden farklı sıralanabilir?

Aynı çalışma kitabı başka bir makinede farklı sıralanabilir; çünkü Excel'in metin düzeni Windows kullanıcı locale'ine bağlıdır ve HotXLS o bağımlılığı bilerek izler. Word sort dile özgüdür: İsveççe collation örnek olarak ä'yi z'den sonra koyar; İngilizce ile Almanca onu a'nın yanında tutar. Excel bunu altında koştuğu locale'den miras alır; dolayısıyla Stockholm'deki bir meslektaşın yeniden hesapladığı çalışma kitabı, Chicago'daki bir masaüstündeki aynı dosyadan farklı bir COUNTIF(...,">y") döndürebilir. HotXLS LOCALE_USER_DEFAULT geçirir ki sonuçları aynı makinede Excel'inkilere eşit olsun; sabit herhangi bir locale, HotXLS'i farklı ayarlı her makinede Excel'le uyuşmaz eder

Sunucu tarafı üretim için üç pratik sonuç çıkar:

• Sayılan locale, prosesin altında koştuğu hesabın locale'idir. Bir Windows servisi ya da IIS uygulama havuzu, geliştiricinin masaüstünden farklı bir bölgesel biçim kullanabilir; IDE'de gözlenen sonuçlar otomatik olarak üretimin hesaplayacağı şey değildir
• Dosyaya yazılan cache'lenmiş formül sonuçları, üreten makinenin locale'ini yansıtır. Excel kendi locale'iyle yeniden hesaplar; dolayısıyla dosya başka bir yerde açılıp yeniden hesaplandığında bir değer değişebilir; Excel'in davranışı budur, HotXLS artefaktı değil
• Locale'ler çoğunlukla aksanlı harflerde, bazı dillerin tek harf saydığı harf birleşimlerinde ve Latin olmayan yazı sistemlerinde ayrılırlar; dolayısıyla sade İngilizce kelimelerle sınırlı test verisi sorunu açığa vurmaz

Platform sınırı basittir. HotXLS bir Windows kütüphanesidir; Delphi ve C++Builder ile Win32 ve Win64 için, Lazarus ve Free Pascal ile win32 / win64 hedefleri için derlenir ve bu derlemelerin hepsi aynı CompareStringW'yi çağırır. Ayrı bir Windows-dışı collation yolu yoktur. Tek fallback başarısız bir API çağrısı içindir: CompareStringW 0 döndürürse XlsCompareText, yeniden hesaplamanın ortasında exception fırlatmak yerine büyük harfe katlanmış string'leri kod birimine göre kıyaslar; bu, hesaplamayı çalışır tutar ama artık Excel'in düzenini garanti etmez

Hızlı başvuru: HotXLS'te Excel metin karşılaştırması

• Kural: NORM_IGNORECASE'li kullanıcı-locale word sort; SORT_STRINGSORT yok, NORM_IGNOREWIDTH yok; HotXLS'te v2.384.67'den beri
• - ile ' yalnızca eşitlik bozar: ="a-b">"ab" TRUE, ="a-b"="ab" FALSE'tur
• Öteki noktalama rakamlardan önce, rakamlar harflerden önce sıralanır: ="a~b"<"ab" ve ="a0"<"ab" TRUE'dur
• Harf durumu asla önemli değildir: ="ABC"="abc" TRUE'dur ve VLOOKUP("ABC",...) abc'yi bulur
• Kapsanan yollar: karşılaştırma operatörleri, dizi karşılaştırmaları, > / < ölçütleri, VLOOKUP / HLOOKUP, dinamik dizi sıralaması, iki motorda da SortRange
• Bu kuralın kapsamadıkları: karışık tipler (sayı < metin < boolean) ve kendi kuralları olan wildcard ölçütleri
• Delphi kodunda: CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...), 0'ı denetleyin, CSTR_EQUAL çıkarın; sonuç Excel'le anlaşmak zorundayken CompareText, CompareStr ve TComparer<string>.Default'ten kaçının
• Sonuçlar, kodu koşan hesabın locale'ine bağlıdır; Excel'de de HotXLS'te de

Sıradan kelimeler her kural altında aynı sıralanır; dolayısıyla yanlış bir collation'ı yalnızca kısa çizgili kodlar, noktalama ve aksanlı adlar açığa vurur. HotXLS artık XLS ve XLSX motorlarının ikisinde de bunların hepsinde Excel'in yanıtını veriyor. Lisanslama, desteklenen Delphi ve C++Builder sürümleri ve deneme indirmesi ayrıntıları HotXLS Delphi Excel bileşeni sayfasında