HotXLS ODS interop: Excel'in okuduğu formüller ve kurallar

Hem Excel'in hem LibreOffice'in doğru okuduğu bir ODS dosyası üretmek için HotXLS her formülü, bildirilmiş bir of: namespace'i altında OpenFormula söz dizimiyle yazar ve her value ya da formula koşullu biçimini iki kez yazar: kapsanan her hücrenin style'ı üzerinde bir <style:map> olarak — Excel 16'nın okuduğu tek biçim budur — ve bir calcext:conditional-formats bloğu olarak — LibreOffice'in güvendiği biçim budur. Her uygulama öbürüne ayrılan yarımı yok sayar; dolayısıyla birinde doğru görünen bir dosya, öbürü hakkında hiçbir şey kanıtlamaz

Son cümle, v2.384.55 ile v2.384.72 arasındaki altı HotXLS sürümünün dersidir. Her düzeltme, HotXLS'in yazdığı, kendisi kusursuz geri okuduğu ama iki hedef uygulamadan birinin yanlış aldığı bir dosyayla başladı. Aşağıdakiler, her uygulamanın gerçekte kabul ettiği şey, ikisini de tatmin eden işaretleme ve bunu Delphi'den üreten HotXLS API çağrılarıdır

Bir ODS dosyası bir uygulamada düzgün, öbüründe bozuk neden görünür?

Bir ODS dosyası bir uygulamada düzgün, öbüründe bozuk görünür, çünkü Excel ile LibreOffice aynı paketin farklı kısımlarını okur. OpenDocument, formüllere ve koşullu biçimlere birden fazla meşru yazılış tanır; LibreOffice üstüne kendi uzantı namespace'ini ekler ve her tüketici kendi gerçeklediği alt kümeyi seçer. Yalnızca bir tükiciye karşı test edilmiş bir yazıcı, öbürünün sessizce yanlış okuduğu işaretlemeye gönül rahatlığıyla yakınsar

Hiçbir uygulama hata bildirmez. LibreOffice, ayrıştıramadığı formüllü hücrelerde #VALUE! gösterir; Excel çalışma kitabını koşullu biçimler ya tamamen yokken açar ya da bir formül, #NAME?'e ya da 0 sabitine değerlenen bir şeye yeniden yazılmışken açar. Kendi çıktısını round-trip eden bir yazıcı bunların hiçbirini görmez. HotXLS tam o tuzağa, formül namespace'inde düştü: okuyucusu of: önekini düz metin olarak eşliyordu; dolayısıyla her öz round-trip geçerken LibreOffice her formül hücresinde #VALUE! gösteriyordu

Özellik	Excel 16 okur	LibreOffice 26.2 okur
A:A diye yazılmış bütün sütun	A:(A) diye yanlış okunur	Toler edilir
[.A:.A] diye yazılmış bütün sütun	Evet	Evet
<style:map> içindeki koşullu biçimler	Evet, okuduğu tek biçim	calcext varken yok sayılır
calcext:conditional-formats içindeki koşullu biçimler	Yok sayılır	Evet, tercih edilir
calcext:operator attribute'lu calcext value kuralı	Yok sayılır	"0'a eşit" diye ithal edilir
is-true-formula(...) yazılmış calcext formula kuralı	Yok sayılır	0 ile bir value karşılaştırması diye ithal edilir

ODS'te OpenFormula: namespace'i bildirin, sonra söz dizimini doğru yapın

ODS'te bir formül hücresi, table:formula'daki of: öneki bildirilmiş bir XML namespace'ine çözülmedikçe LibreOffice tarafından okunabilir değildir. Önek süs değildir. of:, urn:oasis:names:tc:opendocument:xmlns:of:1.2'ye eşlenir; HotXLS'in OpenFormula çevirmeninin modellemediği formüller için kullandığı msoxl: ise http://schemas.microsoft.com/office/excel/formula'ya eşlenir. v2.384.56 öncesinde content.xml kökü, ikisini de bildirmeden iki öneki de kullanıyordu ve LibreOffice formül dil bilgisini hiç tanıyamıyordu

<!-- v2.384.56 öncesinde: önek kullanılıyordu, hiç bildirilmiyordu; LibreOffice #VALUE! gösterir -->
<office:document-content xmlns:table="urn:oasis:names:tc:opendocument:xmlns:table:1.0" ...>
  <table:table-cell table:formula="of:=SUM([.A1:.A3])" office:value-type="float" office:value="245"/>

<!-- v2.384.56'dan beri: iki formül namespace'i de kökte bildirili -->
<office:document-content
    xmlns:of="urn:oasis:names:tc:opendocument:xmlns:of:1.2"
    xmlns:msoxl="http://schemas.microsoft.com/office/excel/formula" ...>

Namespace düzelince ifade kendisi hâlâ geçerli OpenFormula olmak zorundadır; OpenDocument 1.3 Part 4'te tanımlandığı gibi. Tuzaklar, Excel söz dizimi ile OpenFormula'nın benzer görünüp aynı olmadığı yerlerdir:

• Hücre referansları köşeli parantezli ve nokta önekli gelir ve $ işaretleri referansın parçasıdır: [.$A$1] ile [.A$1:.$B2] geçerli OpenFormula'dır. v2.384.55 öncesinde HotXLS yazıcısı her $'i düşürüyordu; mutlak referanslar göreli dönüyordu ve yalnızca biri hücreyi kopyalayınca yanlış gidiyordu
• Bütün sütunlar ve satırlar köşeli parantezli biçimi kullanmak zorundadır: [.A:.A], [.$A:.$B], [.1:.1], [.$1:.$2]. Çıplak bir of:=SUM(A:A)'yi LibreOffice toler eder ama Excel 16, =SUM(A:(A)) diye #NAME? ile açar; satır referanslarını ve $A:$B'yi 0 sabitine çevirir. HotXLS köşeli parantezli biçimi v2.384.65'ten beri yazar
• Fonksiyon argümanları ; ile ayrılır, , ile değil
• Referans birleşimleri ~ operatörünü kullanır: Excel'in AREAS((A1,B2))'si AREAS(([.A1]~[.B2])) olur. O virgülü ;'ye çevirmek ise bir birleşim argümanını iki argümana çevirir
• Inline diziler sütunları ; ile, satırları | ile ayırır: Excel'in {1,2;3,4}'ü {1;2|3;4} olur. v2.384.55 öncesinde HotXLS {1;2;3;4} üretiyordu; dört değerli tek bir satır

Zor kısım virgüldür, çünkü tek bir Excel karakteri üç anlam taşır. v2.384.55'ten beri HotXLS yazıcısı çeviri sırasında bir parantez yığını izler: bir addan hemen sonra gelen ( bir fonksiyon çağrısı açar, onun virgülleri ; olur; öteki her ( bir gruplama parantezidir, onun virgülleri ~ olur; ve {} içindeki virgüller dizi sütun ayraçlarıdır. Bununla ve namespace düzeltmesiyle LibreOffice 26.2, sekiz dizi ve birleşim test formülünün hepsini doğru değerlendirdi; birleşimler üzerinde INDEX ile AREAS dâhil

uses
  lxHandleX;

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Orders');
    Sheet.Cells[1, 1].Value := 120;
    Sheet.Cells[2, 1].Value := 80;
    Sheet.Cells[3, 1].Value := 45;
    Sheet.Cells[1, 2].Value := 0.2;

    // v2.384.65'ten beri of:=SUM([.A:.A]) diye yazılır
    Sheet.Cells[1, 4].Formula := 'SUM(A:A)';
    // of:=[.A1]*[.$B$1] diye yazılır; $ işaretleri v2.384.55'ten beri kalır
    Sheet.Cells[2, 4].Formula := 'A1*$B$1';

    Book.SaveAsODS('orders.ods');
  finally
    Book.Free;
  end;
end;

Çevirmenin modellemediği formüller, Excel metni aynen kalacak şekilde msoxl:='e düşer; msoxl bildiriminin de önemli olması neden böyle. Bugünkü yazıcıda o yol, Sheet2!A1 gibi sheet nitelemeli referansları ve yapılandırılmış tablo referanslarını kapsar. HotXLS, msoxl: formüllerini içe aktarımda geri okur; dolayısıyla kendi round-trip'i ifadeyi olduğu gibi korur ama başka bir uygulamanın onlara nasıl davrandığı yazıcının kontrolünün dışındadır. Tüketicilerinizin bel bağladığı bir formül msoxl: önekiyle çıkıyorsa, göndermeden önce dosyayı iki uygulamada da açın

Excel, yalnızca calcext olarak yazılmış koşullu biçimleri neden görmüyor?

Excel 16, calcext koşullu biçimlerini görmez; çünkü ODS koşullu biçimlerini yalnızca hücre stillerinin <style:map> çocuklarından okur ve calcext:conditional-formats bloğunu tümüyle yok sayar. İşi kesinleştiren deney kısadır: LibreOffice'in kaydettiği bir ODS alıp style:map elementlerini silin, Excel sıfır kural okur; bunun yerine calcext bloğunu silin, Excel hepsini okumaya devam eder. LibreOffice tersini yapar. calcext, LibreOffice'in uzantı namespace'idir, ODF standardının parçası değildir; bir calcext kuralı varken LibreOffice onu alır ve style:map'i yok sayar

v2.384.69 öncesinde HotXLS yalnızca calcext yazıyordu; dolayısıyla kusursuz vurgulamalı bir ODS dosyası, Excel'de hiçbir value kuralı ve hiçbir formula kuralı olmadan açılıyordu. HotXLS artık iki biçimi de yazıyor. style:map yarısı, OpenDocument şemasının (ODF 1.3 Part 3) koşul dil bilgisini kullanır; Excel 16 ile LibreOffice 26.2'nin ODS kaydederken ikisinin de ürettiği tam yazılışlarla:

<!-- Basitleştirildi. A1:A50'nin her hücresi için taşıyıcı style (iki value kuralı) -->
<style:style style:name="ce3" style:family="table-cell">
  <style:map style:condition="cell-content()&gt;100"
             style:apply-style-name="CF_Hit"
             style:base-cell-address="Orders.A1"/>
  <style:map style:condition="cell-content-is-between(1,10)"
             style:apply-style-name="CF_Low"
             style:base-cell-address="Orders.A1"/>
</style:style>

<!-- C1:C50'nin her hücresi için taşıyıcı style (tek formula kuralı) -->
<style:style style:name="ce4" style:family="table-cell">
  <style:map style:condition="is-true-formula(COUNTIF([.$C:.$C];[.C1])&gt;1)"
             style:apply-style-name="CF_Dup"
             style:base-cell-address="Orders.C1"/>
</style:style>

style:map'in püf noktası şudur: hücre stillerinde yaşar, dolayısıyla hücre başınadır. Kuralın aralığındaki her hücre, map'i tutan bir style taşımak zorundadır; boş hücreler dâhil, yoksa kural Excel'de o hücreyi basitçe kapsamaz. HotXLS her hücrenin var olan biçimlendirme style'ını kopyalar, map'leri ekler ve taşıyıcı stilleri, özgün style ile map metninin çiftine göre tekilleştirir; böylece aynı biçimlendirme taşıyan 500 hücrelik bir aralık yine tek bir style üretir. Yazıcı ayrıca yazılan tabloyu kuralın aralığına uzatır; yani kural içindeki boş kuyruk satırları atılmak yerine boşaltılır. v2.384.69'dan beri styles.xml ayrıca boş bir Default hücre style'ı taşır; dolayısıyla style:apply-style-name="Default"'in daima bir hedefi vardır

LibreOffice'in gerçekte kabul ettiği calcext yazılışı

LibreOffice bir calcext value kuralını yalnızca karşılaştırma operatörü değer metninin parçasıyken kabul eder; >3 ya da between(1,10) gibi. Bir formula kuralını ise yalnızca formula-is(...) yazılışlıyken kabul eder. İki nokta da HotXLS'e bir sürüme mal oldu; çünkü yanlış yazılışlar, hatasız ithal edilip sonra yanlış hücreleri eşleyen bir kural üretir

İlk hata, calcext:value'un yanına bir calcext:operator attribute'u konmasıydı. Doğal okunur ama uydurmadır: LibreOffice o attribute'u bilmez; dolayısıyla her value kuralını "0'a eşit" diye ithal etti. İkincisi, style:map yazılışı olan is-true-formula(...)'yu bir calcext koşuluna koymaktı; LibreOffice bunu da 0 ile bir hücre-değeri karşılaştırması diye ithal etti. Formula düzeltmesi v2.384.66'da, value düzeltmesi v2.384.69'da çıktı:

<!-- Yanlış: LibreOffice calcext:operator'ı yok sayar ve "0'a eşit" ithal eder -->
<calcext:condition calcext:apply-style-name="CF_Hit"
                   calcext:operator="greater-than" calcext:value="100"/>

<!-- Doğru: operatör değerin içinde seyahat eder -->
<calcext:condition calcext:apply-style-name="CF_Hit"
                   calcext:value="&gt;100" calcext:base-cell-address=".A1"/>
<calcext:condition calcext:apply-style-name="CF_Low"
                   calcext:value="between(1,10)" calcext:base-cell-address=".A1"/>

<!-- Doğru: formula kuralları formula-is kullanır, göreli referanslar taban hücreye sabitli -->
<calcext:condition calcext:apply-style-name="CF_Dup"
                   calcext:value="formula-is(COUNTIF([.$C:.$C];[.C1])&gt;1)"
                   calcext:base-cell-address=".C1"/>

Taban hücre, göreli referanslara anlamlarını veren şeydir. HotXLS her kuralı, ilk aralık alanının sol üst hücresine sabitler; dolayısıyla C1 için yazılmış bir formül, aralık boyunca C2, C3 diye değerlenir; tıpkı Excel'in kendi koşullu biçimlendirmesinde olduğu gibi. Kural ifadesi, hücre formülleriyle aynı çevirmenden geçer; dolayısıyla diziler, birleşimler, bütün sütunlar ve $ işaretleri yukarıda tanımlanan biçimlerde çıkar. Delphi tarafında kuralları tam olarak bir .xlsx dosyası için ekler gibi eklersiniz

uses
  lxHandleX;

procedure AddOrderHighlights(Book: TXLSXWorkbook; Sheet: TXLSXWorksheet);
var
  Idx: Integer;
  Opts: TODSExportOptions;
begin
  // Value kuralları: style:map cell-content()>100 artı calcext değeri ">100"
  Idx := Sheet.AddConditionalFormat('A1:A50', xlsxCfOpGreaterThan, '100');
  Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($00C0C0FF); // BGR: açık kırmızı

  Idx := Sheet.AddConditionalFormat('A1:A50', xlsxCfOpBetween, '1', '10');
  Sheet.ConditionalFormats[Idx].Style.SetFontBold(True);

  // Excel söz diziminde formula kuralı (virgül ayraçlar, C1'e göreli):
  // style:map is-true-formula(...) artı calcext formula-is(...)
  Idx := Sheet.AddCondFormatExpression('C1:C50', 'COUNTIF($C:$C,C1)>1');
  Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($00CCFFFF); // BGR: açık sarı

  Opts := TODSExportOptions.Create;
  try
    Opts.Generator := 'OrderExport 3.1';
    Book.SaveAsODS('orders.ods', Opts);
  finally
    Opts.Free;
  end;
end;

Excel ve LibreOffice'ten gelen ODS'i Delphi'ye geri okumak

HotXLS bir ODS dosyası açtığında okuyucusu her iki koşullu biçim lehçesini ve iki calcext yazılışını da kabul eder; dosya bir kuralı iki biçimde de taşıdığında onu iki kez saymaz. Gerçek dosyalar üç yazıcıdan gelir, her birinin kendi huylarıyla:

• Eski ve yeni calcext. calcext:operator attribute'lu dosyalar — v2.384.69 öncesi HotXLS'in yazdığı ODS'ler dâhil — hâlâ legacy ayrıştırmadan geçer. Formula koşulları, formula-is(...) ya da is-true-formula(...) olarak tanınır
• Excel'in style:map yazılışı. Excel koşulları of: ile önekler — of:cell-content-is-between(1,10) gibi — ve value kurallarında taban hücreyi atlar. İkisi de kabul edilir
• Boş hücreler. Excel ile LibreOffice, boş hücrelerin map'ini bir hücreye değil sütun varsayılan style'ına koyar; dolayısıyla okuyucu, map'leri toplamadan önce yinelenen hücreler için sütun varsayılan stillerini çözer
• Bölge yeniden kurma. Map'ler hücre başına toplanır; dolayısıyla bir sheet okunduktan sonra okuyucu, aynı koşulu ve taban hücreyi paylaşan hücreleri aralıklara geri birleştirir — önce her satır boyunca, sonra örtüşen sütun açılımları boyunca aşağı — ve calcext'ten çoktan okunmuş her kuralı atar

v2.384.72 düzeltmesi kuralları değil sayı stillerini ilgilendirir. Excel 16 ile LibreOffice 26.2, General biçimini number:number elementi hiçbir number:decimal-places taşımayan bir sayı style'ı olarak yazar; tipik hâli <number:number number:min-integer-digits="1"/>'dir. HotXLS okuyucusu eksik sayıyı iki sabit ondalık sayıyordu; dolayısıyla Default style'ındaki her değer 0.00 ile ithal ediliyor ve 1.5, 1.50 görünüyor. v2.384.72'den beri ondalık yeri olmayan, en az ondalık olmayan, gruplaması olmayan ve en çok bir tam basamağı olan sade bir sayı elementi General'e eşlenir; tek başına bir General ise hücreyi hiçbir sayı biçimi olmadan bırakır. Etrafındaki metin korunur, General" kg" gibi; gruplanmış sayılar önceki eşlemeyi korur, çünkü Excel'in gruplanmış bir General biçimi yoktur

uses
  SysUtils, lxCondFormat, lxHandleX;

procedure DumpOdsRules(const FileName: string);
var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Rule: TXLSXConditionalFormat;
  I: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    if Book.Open(FileName) <= 0 then
      raise Exception.Create('cannot open ' + FileName);
    if Book.SourceFormat <> xlsxOpenDocumentSpreadsheet then
      raise Exception.Create('not an ODS package');

    Sheet := Book.Sheets[1]; // Sheets indeksleyicisi 1 tabanlıdır
    for I := 0 to Sheet.ConditionalFormats.Count - 1 do
    begin
      Rule := Sheet.ConditionalFormats[I];
      case Rule.Kind of
        cfkCellIs:
          Writeln(Rule.Range, ' value rule ', Ord(Rule.Op), ' ',
            Rule.Formula1, ' ', Rule.Formula2);
        cfkExpression:
          Writeln(Rule.Range, ' formula rule ', Rule.Formula1);
      end;
    end;

    // Excel'in General style'ındaki bir hücre, v2.384.72'den beri
    // '0.00' yerine hiçbir sayı biçimi olmadan geri okunur
    Writeln('A2 format: "', Sheet.Cells[2, 1].NumberFormat, '"');
  finally
    Book.Free;
  end;
end;

Kural formülleri, virgül ayraçlı Excel söz dizimiyle döner; AddCondFormatExpression'a geçireceğiniz biçimin aynısıdır, dolayısıyla HotXLS'in yazdığı bir kural aynı string olarak geri okunur. ODS içe aktarma yolunun neyi tutup neyi attığının daha geniş resmi için HotXLS ODS aç ve kaydet round-trip rehberine; Excel ile LibreOffice'ten gelen yinelenen satırların içe aktarımda nasıl genişletildiği için satır yüksekliği koşuları olarak ODS yinelenen satırları yazısına bakın

HotXLS ODS koşullu biçim interop'unun sınırları nelerdir?

Çift işaretleme yaklaşımı value karşılaştırma kurallarını ve formula kurallarını kapsar ve orada durur. Geri kalan her şey tek taraflıdır ya da hiç yazılmaz:

• Renk ölçekleri ve data bar'lar yalnızca calcext elementleri olarak yazılır; dolayısıyla LibreOffice gösterir, Excel göstermez
• Öteki kural türleri — icon setleri, metin kuralları, top-N, ortalama üstü ve yineleme kuralları gibi — bugünkü yazıcıda ODS çıktısı vermez. Bir metin kuralı çoğunlukla bir formula kuralı olarak yeniden ifade edilebilir; örneğin B2:B200 üzerinde ISNUMBER(SEARCH("late",B2)); o zaman iki uygulamaya da ulaşır
• C:C gibi bütün sütun ve bütün satır kuralları yalnızca gerçekten yazılmış tablo alanına serilir, 1.048.576 satırın tamamına değil; dolayısıyla Excel bu kuralları yalnızca dosyada var olan hücrelerde görür
• Yalnızca style:map taşıyan dosyalar. Dosyada calcext bloğu yokken HotXLS, formula kurallarındaki göreli referansları yeniden kurulan aralığın sol üst köşesinden yorumlar; bildirilen taban hücreden kaydırarak değil
• LibreOffice'ten gelen örtüşen kurallar. Bir hücreyi birkaç kural kapsadığında LibreOffice üzerine yalnızca ilk kuralın map'ini yazar. Böyle dosyalar yalnızca style:map'den tam okunamaz; okuyucunun ikisi de varken calcext'i tercih etmesinin bir nedeni daha budur

Süreç sınırı bunların hepsinden daha önemlidir. Bu sürümlerin arkasındaki kusurlar, ODS yazıp HotXLS ile geri okuyan round-trip'lerden sızdı geçti; bazıları yanlış uygulamada elle yapılan bir kontrolden de geçerdi: bütün sütun formülleri LibreOffice'te çalışırken Excel #NAME? gösteriyordu; v2.384.66'dan itibaren formula kuralları LibreOffice'te çalışırken Excel, v2.384.69'a dek hiç kural göstermiyordu. ODS interop bir gereklilikse kabul testi, dosyayı Excel'de ve LibreOffice'te açıp ikisinin gösterdiklerini karşılaştırmaktır. Aynı disiplin, kuralların işaret ettiği stillere de uygulanır; HotXLS koşullu biçimlendirme ve stiller yazısı, vurgulama stillerinin çalışma kitabı tarafında nasıl tanımlandığını işler

Hızlı başvuru: iki uygulamanın da okuduğu ODS

• content.xml kökünde xmlns:of ile xmlns:msoxl'i bildirin, yoksa LibreOffice her formül için #VALUE! gösterir (HotXLS'te v2.384.56'dan beri)
• Referansları [.A1] diye yazın, her $'i koruyun ve bütün sütunlarla satırları [.A:.A] ile [.1:.1] diye yazın (v2.384.55 ve v2.384.65'ten beri)
• Argümanlar için ;, referans birleşimleri için ~, inline dizi satırları arasında | kullanın
• Her value ya da formula kuralını Excel için kapsanan her hücrenin style'ı üzerinde bir <style:map> olarak, LibreOffice için bir calcext koşulu olarak yazın (v2.384.69'dan beri)
• calcext'te operatörü değerin içine koyun (>3, between(1,10)) ve formula kurallarını bir taban hücreyle formula-is(...) diye yazın (v2.384.66 ve v2.384.69'dan beri)
• İçe aktarımda number:decimal-places taşımayan bir General sayı style'ına hazırlıklı olun; HotXLS onu v2.384.72'den beri General diye okur
• Her yeni dışa aktarma profilini, dosyayı hem Excel'de hem LibreOffice'te açarak doğrulayın; asla yalnızca birinde değil

HotXLS, Excel ya da LibreOffice kurulu olmadan XLS, XLSX ve ODS okuyup yazan yerli bir Delphi ve C++Builder spreadsheet kütüphanesidir; tam kaynak, özellik listesi ve lisanslama HotXLS Delphi spreadsheet component sayfasında