Teknik Makale

HotXLS'te Excel word sort: Delphi metin karşılaştırması

Excel'de ="a-b">"ab" TRUE döndürür; ="a~b"<"ab" de TRUE döndürür. Aynı çiftleri Delphi'nin CompareText'ine verdiğinizde iki yanıt da tersine döner; çünkü kod noktası düzeninde kısa çizgi harflerin altında, tilda ise üstünde yer alır. Excel metni, Windows kullanıcı locale'inin büyük-küçük harf duyarsız "word sort"u ile karşılaştırır; burada kısa çizgi ve kesme işareti yalnızca eşitlik bozar, öteki tüm noktalama işaretleri rakamlardan ve harflerden önce gelir

Belirtiler çoğu zaman collation'dan söz etmez. Sunucuda hesaplanan bir COUNTIF(A:A,">M"), Excel'deki aynı formülün bulduğundan iki satır fazla sayar. Raporlama servisinin sıraladığı bir fiyat listesi X-100'ü Excel'in koymayacağı bir yere koyar. VLOOKUP("ABC",...), sütunda açıkça abc dururken #N/A döndürür. Üçü de tek bir soruya indirgenir: iki taraf da metinken hangisi küçük? Excel'in net bir yanıtı vardır ve Delphi kodunun varsayılan olarak verdiği yanıt çoğu zaman o değildir

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

Excel iki metin değerini, geçerli Windows kullanıcı locale'inin word sort'u ile, büyük-küçük harfi yok sayarak karşılaştırır. Word sort, Windows NLS karşılaştırma fonksiyonlarının varsayılan collation'ıdır: harfler kod noktasına göre değil dilbilgisel sıraya göre kıyaslanır; dolayısıyla é, e ile f arasına düşer ve büyük-küçük harf birbirine katlanır. İki karakter özel işlem görür. Kısa çizgi - ile kesme işareti ' ilk turda atlanır; bu yüzden co-op ile coop yan yana düşer ve geri kalan karakterler eşitlendiğinde sırayı onların varlığı ve konumu belirler. Öteki tüm noktalama tam ağırlıkla sayılır ve rakamlardan, rakamlar da harflerden önce sıralanır

Tablo, IF(A<B,...) için Excel'in verdiği kararı, bir Delphi geliştiricisinin en önce yazması muhtemel iki karşılaştırmayla yan yana koyar

A vs BExcel / HotXLSCompareStr (ordinal)CompareText
"a-b" vs "ab"büyükküçükküçük
"a'b" vs "ab"büyükküçükküçük
"a~b" vs "ab"küçükbüyükbüyük
"a_b" vs "ab"küçükküçükbüyük
"ab" vs "AB"eşitbüyükeşit
"é" vs "f"küçükbüyükbüyük
"Z" vs "f"büyükküçükbüyük

İki sonucu kaçırmak kolaydır. Kısa çizgi yalnızca eşitlik bozduğundan ="a-b"="ab" FALSE'tur: iki string sıralamada bitişik komşudur ama eşit değildir. Ve harf durumu tamamen yok sayıldığından ab, AB ve Ab, her karşılaştırma operatörü için tek anahtardır. İngilizce kullanıcı locale'inde 20 çetrefilli kelimelik bir sütun şöyle sıralanır: 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 sırayı atlanan karakterin konumu belirler: sondaki kısa çizgi önce, baştaki en sona

HotXLS word sort şeması: 20 test kelimesinin tamamını a b, a.b, a_b ve a~b'den a0 ile a1b'ye, ardından AB ile Ab'li ab grubuna, a-b ile a'b gibi kısa çizgi ve kesme işareti varyantlarına, abc, b, e, e-aksanlı, f ve Z'ye kadar sıralar; noktalamanın rakamlardan, rakamların harflerden önce geldiğini ve büyük-küçük harfin yok sayıldığını gösterir
Noktalama ve boşluk rakamlardan önce, rakamlar harflerden önce sıralanır, büyük-küçük harf katlanır ve kısa çizgiyle kesme işareti yalnızca eşitlik bozar; a-b'nin ab'nin yanına düşüp yine de büyük karşılaştırmasının nedeni budur

Collation'ı Excel'de kendiniz doğrulamak

Excel'in dokümantasyonu kullandığı collation'ı hiç adıyla anmaz; kendi makinenizde emin olmanın güvenilir yolu, bir yığın string çiftini Excel'e kararlaştırtıp verdiği kararları CompareStringW'nin farklı bayrak setleri altındaki kararlarıyla kıyaslamaktır. Çiftleri iki sütuna koyun, Excel'e her satır için =IF(A1<B1,-1,IF(A1=B1,0,1)) hesaplatın, aynı çiftleri API'den geçirin. Üretilmeye değer çiftler; ASCII noktalaması, rakamlar, iki harf durumu, boşluklar, é, ß, ä gibi aksanlı harfler, CJK karakterleri, tam genişlikli biçimler ve bölünemez boşluk içerir; tek karakter farkla ayrılan near-miss çiftleri bol bol olsun. Sözlükten seçilmiş rastgele kelimeler zayıf bir örnektir; düz harfler bu yazıdaki her kural altında aynı sıralanır

Bayrak setleri birbirinden belirgin biçimde farklı sonuçlar verir:

  • Yalnız NORM_IGNORECASE, LOCALE_USER_DEFAULT ile, düz word sort'tur ve Excel'in izlediği kural budur
  • SORT_STRINGSORT eklemek string sort'a geçirir; kısa çizgiyle kesme işareti tam ağırlıklı sıradan simge olur ve a-b / ab tarzı her çift ters yönde çıkar
  • NORM_IGNOREWIDTH eklemek, tam genişlikli harfi yarım genişlikli biçimiyle eşitler; Excel bu ikisini ayrı tutar

Bir tuzak da test düzeneksinin kendisinde oturur. Excel, hücreye başa yazılan kesme işaretini "metin olarak sakla" öneki sayar; dolayısıyla girdisi tamamen ' olan bir hücre boş kalır ve bu yolla kurulmuş her çift, collation'la hiç ilgisi olmayan bir uyuşmazlık bildirir. Böyle çiftleri formülde string literaliyle kurun (="'") ya da tamamen atlayın

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

CompareText ile ordinal karşılaştırma, Excel'in sırasını şünden ıskalar: UTF-16 kod birimlerini kıyaslarlar ve kod noktası düzeni noktalamayı alfabenin çeşitli yerlerine serper. Kısa çizgi U+002D, kesme işareti U+0027'dir; ikisi de her harfin altındadır, dolayısıyla ordinal karşılaştırma "a-b"'yi "ab"'den küçük sayar; oysa Excel kısa çizgiyi eşitlik bozucu tutar. Tilda U+007E ile her harfin üstündedir; bu da "a~b"'yi büyütür, Excel'in tam tersi. CompareText üstüne ikinci bir bozulma daha ekler: yalnız a..z aralığını büyük harfe katlar, sonra kod birimlerini kıyaslar; alt çizgi (U+005F) büyük harf ile küçük harf aralıklarının arasına düştüğünden, katlama "a_b"'yi "ab"'nin altından üstüne taşır. Ne é'nin e ile f arasına gittiğini ikisi de bilmez

HotXLS karşılaştırma şeması: kod noktası düzenini Excel word sort'la karşıt koyar; ordinal karşılaştırma kesme işaretini, kısa çizgiyi ve alt çizgiyi harflerin çevresinde 0x27, 0x2D ve 0x5F'e yerleştirir, böylece a-b - ab küçük çıkar; word sort ise noktalamayı rakamların ve harflerin önüne iter ve yalnızca kısa çizgiyle kesme işaretini eşitlik-bozucu sayar
Kod noktaları noktalamayı harflerin etrafına serper; dolayısıyla ordinal ve ASCII katlamalı karşılaştırmalar hükümleri ters çevirir; word sort noktalamayı rakamların önüne taşır, kısa çizgiyle kesme işaretini eşitlik-bozucu rütbesine indirir

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

  • CompareStr, string < operatörü ve TComparer<string>.Default ordinal ve 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, CompareString(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...) çağırır; Excel'le uyuşan çağrı budur. Varsayılanlarıyla (UseLocale True, CaseSensitive False) sıralı bir TStringList de AnsiCompareText'ten geçer ve Excel'le aynı fikirdedir
  • POSIX hedeflerinde Delphi RTL'si AnsiCompareText'i bir ICU collator'üne yollar; farklı bir algoritmadır, noktalama kuralları da farklıdır. Free Pascal'ın Windows'taki AnsiCompareText'i ANSI kod sayfasına çevirip CompareStringA çağırır; o kod sayfasının temsil edemediği her karakter kaybolur

Demek ki locale duyarlı RTL fonksiyonları Windows'ta bugünkü gerçeklemeleri yüzünden doğru çalışıyor; sözleşmeden değil. Excel'le uyuşması gereken kod, API çağrısını açıkça yapmalı ve bunu belirtmeli. Asıl zor problem, bir formül motorunda her kod yolunun elinin altındaki karşılaştırmayı kapmasıdır: = ve < operatörlerinde büyük harfe çevirip kod noktası kıyası, COUNTIF ölçütünün > / < kolunda harf duyarlı Variant kıyası, VLOOKUP içinde başka bir tane (VLOOKUP("ABC",A1:A20,1,FALSE)'un abc'yi bulamaması tam da buradan gelir), aralık sıralamasında ise locale çağrısı. Her yol tek başına savunulabilir. Bir arada, aynı sütun üzerinde üç ayrı düzen üretirler

HotXLS'te formüllerin, aramaların ve sıralamanın arkasında tek karşılaştırma

HotXLS, hesaplama ve sıralama kodundaki metin-metne her karşılaştırmayı tek bir fonksiyondan geçirir: lxStandard.pas'taki XlsCompareText, CompareStringW(LOCALE_USER_DEFAULT, NORM_IGNORECASE, ...) çağırıp CSTR_EQUAL'i çıkarır. Onu kullananlar: altı karşılaştırma operatörü, dizi formüllerinde eleman bazlı karşılaştırmalar, COUNTIF tarzı ölçütlerin ve veritabanı fonksiyonlarının >, <, >= ve <= kolları, tam ve yaklaşık kipte VLOOKUP ile HLOOKUP, dinamik dizi fonksiyonlarının ve XLOOKUP / XMATCH'in arkasındaki sıralama yardımcıları, ayrıca TXLSWorksheet ve TXLSXWorksheet üzerindeki SortRange. Aralık sıralamasını aynı fonksiyona bağlamak tasarımın belirleyici noktasıydı. Metin üzerinde yaklaşık VLOOKUP, ancak sütun aramanın kıyasladığı düzende sıralıysa anlamlı sonuç verir; tek fonksiyon paylaşımı bu iki düzenin birbirinden kopmasını imkânsız kılar

HotXLS yönlendirme şeması: altı karşılaştırma operatöründen ve COUNTIF tarzı ölçütlerden VLOOKUP, HLOOKUP, XLOOKUP'a ve iki motorun aralık sıralamasına kadar her metin karşılaştırma yolunu gösterir ve hepsi XlsCompareText'te birleşir; o, CompareStringW'yi LOCALE_USER_DEFAULT ve NORM_IGNORECASE ile çağırır ve 1, 2, 3'ü -1, 0, 1'e haritalar
Operatörler, ölçütler, aramalar ve sıralama tek fonksiyonu paylaşır; dolayısıyla Excel'in gördüğü düzen ile HotXLS'in sıraladığı düzen ayrışamaz; API 1, 2 ya da 3 döndürür ve sıfır, küçükten değil başarısızlıktan söz eder

Kuralı Delphi'den görmenin en hızlı yolu TXLSXWorkbook.Calculate'tir; aktif sayfaya karşı bir formül string'ini değerlendirir ve sonucu Variant olarak döndürü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;

Word sort, iki taraf da metin olduktan sonra devreye girer. Karışık tipler sabit bir sıralamayı izler: her sayı her metin değerinin altında, her metin değeri her boolean'ın altındadır; ayrıntı karşılaştırma zincirleri, boş operandlar ve SUMIF yazısındadır. "a*" ya da "=ab" gibi joker ölçütler ise kendine kuralları olan örüntü ve eşitlik testleridir; onlar COUNTIF, MATCH ve DSUM'da Excel jokerleri kılavuzunda işlenir, burada konuştuğumuz collation yalnızca sıralama operatörlerine hükmeder

Sonraki örnek, 20 kelimeyi bir sayfanın A sütununa yükler, üç ölçüt sayımı ve bir arama yapar, sonra sütunu TXLSXWorksheet.SortRange ile sıralar. Beklenen sayımlar, yukarıda gösterilen sıralı düzenden doğrudan çıkar: on bir kelime ab grubundan sonra, on bir kelime a-b'den önce sıralanır; >=AB için ab grubunun üç üyesi de on birli gruba katılır

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 düzeninden beklenen: 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 bulur: arama harf duyarsız kıyaslar, tıpkı Excel gibi
    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;

SortRange kararlı bir merge sort çalıştırır; dolayısıyla kıyasça eşit olan ab, AB ve Ab, sıralamadan önceki göreli düzenlerini korur. Boş hücreler, anahtar artan da azalan da olsa alta gider; Excel de onları oraya koyar

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

Excel'in metin düzenini çalışma kitabı dışında yeniden üretmek için CompareStringW'yi LOCALE_USER_DEFAULT ve NORM_IGNORECASE ile çağırın; SORT_STRINGSORT ile NORM_IGNOREWIDTH'i eklemeyin. Dönüş değerine dikkat. API işaretli bir sonuç döndürmez; CSTR_LESS_THAN (1), CSTR_EQUAL (2) ya da CSTR_GREATER_THAN (3) döndürür, çağrı başarısızsa 0. İkiden çıkarmak alışılmış negatif / sıfır / pozitif kuralını verir; ama önce 0 durumu ele alınmalı, yoksa başarısız çağrı -2'ye dönüşür ve "küçük" diye okunur

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;

HotXLS zaten uses satırındaysa lxStandard.XlsCompareText herkese açıktır ve aynı düzeni döndürür. İkisi yalnızca başarısız API çağrısında ayrışır: yukarıdaki fonksiyon raise eder, XlsCompareText ise string'leri büyük harfe çevirip kod birimi düzeyinde kıyaslayarak sürer; böylece bir yeniden hesaplama yarım yolda kesilmez. Bağımsız bir sıralama için raise etmek daha iyidir; sessizce yanlış bir düzen, bir istisnadan daha zor izlenir

TArray.Sort kararlı değildir; dolayısıyla ab ile AB gibi eşit anahtarlar her iki sırada da çıkabilir. Eşit anahtarların özgün sırası önemliyse, bir indeks dizisini sıralayın ve özgün konumu ikincil anahtar yapın

Bazen bir sütunun bilerek Excel'in düzenini yok sayması gerekir. Klasik örnek parça numaralarıdır: X-100 ile X100 farklı kodlardır ve kod noktasına göre sıralanmalıdır. SortRange'in TXLSSortCompareEvent alan bir overload'u vardır; function(const Left, Right: Variant): Integer of object imzalı bu metot, gömülü karşılaştırmanın yerine kullanılı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 SortRange ham anahtar değerlerini olduğu gibi geçer ve boş yerleşimini size bırakır; yukarıdaki comparer ilk iş Null'u ele aldığı için. Azalan anahtarda sıralama, comparer'ın döndürdüğünü ters çevirir; o yönde boş hücreler, comparer önlem almadıkça en üste çıkar. Bu yolla sıralanmış bir sütun, artık yaklaşık VLOOKUP'un ya da binary-search XLOOKUP'un beklediği düzende değildir; farklı düzende sıralanmış veride bu kiplerde ne ters gider, XLOOKUP ve XMATCH binary search kipleri kılavuzunda işlenir

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

Bir çalışma kitabı başka makinede farklı sıralanabilir; çünkü Excel'in metin düzeni Windows kullanıcı locale'ine bağlıdır ve HotXLS bu bağımlılığı bilerek izler. Word sort dile özgüdür. Örneğin İsveççe collation, ä'yı z'den sonra koyar; İngilizce ve Almanca ise onu a'nın yanında tutar. Dolayısıyla Stockholm'deki bir meslektaşın yeniden hesapladığı dosya, Chicago'daki masaüstündeki aynı dosyadan farklı bir COUNTIF(...,">y") döndürebilir. HotXLS, aynı makinede Excel'le birebir aynı sonucu versin diye LOCALE_USER_DEFAULT geçirir. Sabit bir locale'e çakmak daha düzgün görünür ama HotXLS'i, ayarı farklı her makinede Excel'le çelişkiye sokar

Sunucu tarafı üretim için bunun üç pratik sonucu var:

  • Sayılan locale, prosesin altında koştuğu hesabın locale'idir. Bir Windows servisinin ya da IIS uygulama havuzunun bölgesel ayarları geliştiricinin masaüstünden farklı olabilir; IDE'de görülen sonuçlar, production'ın hesaplayacağı sonuçlar olmak zorunda değildir
  • Dosyaya yazılan önbelleklenmiş formül sonuçları, üreten makinenin locale'ini yansıtır. Excel kendi locale'iyle yeniden hesaplar; dosya başka yerde açılıp yeniden hesaplandığında bir değer değişebilir. Excel kendi dosyalarında da aynı şekilde davranır
  • Locale'ler çoğunlukla aksanlı harflerde, bazı dillerin tek harf saydığı harf çiftlerinde ve Latin olmayan alfabelerde ayrışır. Düz İngilizce kelimelerden kurulu test verisi farkı asla ortaya çıkarmaz; örnek veriye Ångström ve Öberg gibi birkaç isim koyun

Excel'le uyuşan yol Windows'takidir: Delphi, C++Builder, Lazarus ve Free Pascal ile derlenen her Win32 ve Win64 build, aynı CompareStringW'yi çağırır. Build Windows dışı bir platformu hedeflediğinde XlsCompareText, RTL'nin WideCompareText'ini kullanır; onun düzeni o platformun kendi collation kütüphanesinden gelir

Sıradan kelimeler her kural altında aynı sıralanır; yanlış bir collation bu yüzden testten sağ çıkarak, kısa çizgili parça kodları, noktalama ya da aksanlı müşteri adları gelene dek yaşar. HotXLS bu girdilerde Excel'in yanıtını verir, XLS ve XLSX motorlarının ikisinde de, karşılaştırma operatörlerinden SortRange'e kadar. Desteklenen Delphi ve C++Builder sürümleri, lisanslama ve deneme indirmesi HotXLS Delphi Excel component sayfasındadır