HotXLS ODS sąveika: formulės ir taisyklės, skaitomos Excel

Kad ODS failą teisingai skaitytų ir Excel, ir LibreOffice, HotXLS kiekvieną formulę rašo OpenFormula sintakse po deklaruota of: vardų tarpą, o kiekvieną reikšminį ar formulės sąlyginį formatą rašo du kartus: kaip <style:map> ant kiekvieno dengto langelio stiliaus – tai vienintelė forma, kurią skaito Excel 16, – ir kaip calcext:conditional-formats blokas, kuriuo pasitiki LibreOffice. Kiekviena programą kitai skirtąją pusę ignoruoja, tad failas, teisingai atrodantis vienoje jų, apie kitą nepasako nieko

Tas paskutinis sakinys – pamoka už šešis HotXLS leidimus tarp v2.384.55 ir v2.384.72. Kiekvienas sutvarkymas prasidėdavo nuo failo, kurį HotXLS parašydavo, atgal perskaitydavo be priekaištų, o viena iš dviejų tikrųjų programų suprasdavo klaidingai. Toliau – tai, ką kiekviena programa iš tikrųjų priima, žymėjimas, tenkinantis abi, ir HotXLS API kvietimai, kurie tai pagamina iš Delphi

Kodėl ODS failas vienoje programoje atrodo tvarkingas, kitoje – sugedęs?

ODS failas vienoje programoje atrodo tvarkingas, kitoje – sugedęs, nes Excel ir LibreOffice skaito skirtingas to paties paketo dalis. OpenDocument formulėms ir sąlyginiams formatams duoda daugiau nei vieną legalią rašybą, LibreOffice ant viršaus prideda savą plėtinių vardų tarpą, o kiekvienas vartotojas renkasi tą aibę, kurią yra įgyvendinęs. Rašytojas, išbandytas su vienu vartotoju, mielai susikaups ant žymėjimo, kurį kitas tyliai perskaitys neteisingai

Nė viena programa neskelbia klaidos. LibreOffice langeliuose, kurių formulių nesugebėjo išanalizuoti, rodo #VALUE!; Excel atidaro darbaknygę su sąlyginiais formatais, paprasčiausiai dingusiais, arba su formule, perrašyta į tai, kas vertinama į #NAME? arba konstantą 0. Rašytojas, apvalinantis savo išvestį, nieko iš to nemato. HotXLS į tuos spąstus įkrito su formulės vardų tarpų dalykas: jo skaitytuvas of: priešdėlį sutapdindavo kaip gryną tekstą, tad kiekvienas savasis apvalinimas praeidavo, o LibreOffice rodė #VALUE! kiekvienam formulės langeliui

Funkcionalumas	Skaito Excel 16	Skaito LibreOffice 26.2
Visas stulpelis, parašytas kaip A:A	Perskaito kaip A:(A)	Pakenčia
Visas stulpelis, parašytas kaip [.A:.A]	Taip	Taip
Sąlyginiai formatai <style:map> pavidalu	Taip, vienintelė skaitoma forma	Ignoruoja, kai yra calcext
Sąlyginiai formatai calcext:conditional-formats pavidalu	Ignoruoja	Taip, teikia pirmenybę
calcext reikšmės taisyklė su calcext:operator atributu	Ignoruoja	Importuoja kaip "lygu 0"
calcext formulės taisyklė, parašyta is-true-formula(...)	Ignoruoja	Importuoja kaip reikšmės palyginimą su 0

OpenFormula ODS faile: deklaruokite vardų tarpą, tada sutvarkykite sintaksę

ODS formulės langelį LibreOffice perskaitys tik tada, kai of: priešdėlis iš table:formula išsiresolvina į deklaruotą XML vardų tarpą. Priešdėlis nėra puošmena. of: susieja su urn:oasis:names:tc:opendocument:xmlns:of:1.2, o msoxl: – priešdėlis, kurį HotXLS naudoja formulėms, kurių OpenFormula vertėjas nemodeliuoja, – su http://schemas.microsoft.com/office/excel/formula. Iki v2.384.56 content.xml šaknis naudojo abu priešdėlius jų nedeklavusi, ir LibreOffice formulės gramatikos negalėjo atpažinti iš viso

<!-- Iki v2.384.56: priešdėlis naudotas, bet nesudeklaruotas; LibreOffice rodo #VALUE! -->
<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"/>

<!-- Nuo v2.384.56: abu formulės vardų tarpai deklaruoti ant šaknies -->
<office:document-content
    xmlns:of="urn:oasis:names:tc:opendocument:xmlns:of:1.2"
    xmlns:msoxl="http://schemas.microsoft.com/office/excel/formula" ...>

Sutvarkius vardų tarpą, pati išraiška vis tiek turi būti teisėta OpenFormula, kaip apibrėžta OpenDocument 1.3 Part 4. Spąstai – tos vietos, kur Excel sintaksė ir OpenFormula atrodo panašiai, bet nėra tos pačios:

• Langelio nuorodos skliaustuose su tašku priekyje, o $ ženklai yra nuorodos dalis: [.$A$1] ir [.A$1:.$B2] yra teisėta OpenFormula. Iki v2.384.55 HotXLS rašytojas išmesdavo kiekvieną $, tad absoliučios nuorodos grįždavo relatyviomis ir klysdavo tik tada, kai kas nors nukopijuodavo langelį
• Visi stulpeliai ir eilutės turi naudoti skliaustuotą formą [.A:.A], [.$A:.$B], [.1:.1], [.$1:.$2]. Nuogą of:=SUM(A:A) LibreOffice pakenčia, bet Excel 16 ją atidaro kaip =SUM(A:(A)) su #NAME?, o eilučių nuorodas ir $A:$B paverčia konstanta 0. HotXLS skliaustuotą formą rašo nuo v2.384.65
• Funkcijų argumentus skiria ;, o ne ,
• Nuorodų sąjungos naudoja ~ operatorių: Excel AREAS((A1,B2)) virsta AREAS(([.A1]~[.B2])). Tą kablelį išvertus į ;, vienas sąjungos argumentas virsta dviem argumentais
• Inline masyvai stulpelius skiria ;, o eilutes – |: Excel {1,2;3,4} virsta {1;2|3;4}. Iki v2.384.55 HotXLS gamindavo {1;2;3;4} – vieną keturių reikšmių eilutę

Kablelis – sunkiausia dalis, nes vienas Excel simbolis neša tris reikšmes. Nuo v2.384.55 HotXLS rašytojas vertdamas seka skliaustų krūvą: ( iškart po vardo atveria funkcijos kvietimą, kurio kableliai tampa ;; bet koks kitas ( yra grupavimo skliaustas, kurio kableliai tampa ~; o kableliai {} viduje yra masyvo stulpelių skirtukai. Su tuo ir vardų tarpo sutvarkymu LibreOffice 26.2 teisingai įvertino visas aštuonias masyvo ir sąjungos bandomąsias formules, įskaitant INDEX ir AREAS virš sąjungų

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;

    // Rašoma kaip of:=SUM([.A:.A]) nuo v2.384.65
    Sheet.Cells[1, 4].Formula := 'SUM(A:A)';
    // Rašoma kaip of:=[.A1]*[.$B$1]; $ ženklai išgyvena nuo v2.384.55
    Sheet.Cells[2, 4].Formula := 'A1*$B$1';

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

Formulės, kurių vertėjas nemodeliuoja, atsitraukia į msoxl:= su nepakeistu Excel tekstu – todėl ir msoxl deklaracija svarbi. Dabartiniame rašytojane tas kelias apima lapo kvalifikuotas nuorodas, tokias kaip Sheet2!A1, ir struktūrines lentelių nuorodas. HotXLS msoxl: formules importuodamas skaito atgal, tad jo savas apvalinimas išraišką išlaiko nepaliestą, bet kaip ją traktuos kita programa – ne rašytojo rankose. Jei formulė, kuria remiasi jūsų vartotojai, išeina su msoxl: priešdėliu, prieš išleisdami atverkite failą abiejose programose

Kodėl Excel nemato sąlyginių formatų, parašytų vien calcext pavidalu?

Excel 16 calcext sąlyginių formatų nemato, nes ODS sąlyginius formatus skaito išimtinai iš langelių stilių vaikų <style:map>, o calcext:conditional-formats bloką ignoruoja visiškai. Eksperimentas, kuris tai užantspauduoja, trumpas: paimkite LibreOffice išsaugotą ODS, ištrinkite style:map elementus, ir Excel perskaitys nulį taisyklių; vietoj to ištrinkite calcext bloką, ir Excel vis tiek perskaitys visas. LibreOffice elgiasi atvirkščiai. calcext yra LibreOffice plėtinių vardų tarpas, ne ODF standarto dalis, ir kai calcext taisyklė yra, LibreOffice jos imasi, o style:map ignoruoja

Iki v2.384.69 HotXLS rašė vien calcext, tad ODS failas su puikiais paryškinimais Excel atsidarydavo be jokių reikšmių ir formulės taisyklių. HotXLS dabar rašo abi formas. style:map pusė naudoja OpenDocument schemos (ODF 1.3 Part 3) sąlygų gramatiką, su tiksliomis rašybomis, kurias ODS išsaugodamos gamina ir Excel 16, ir LibreOffice 26.2:

<!-- Supaprastinta. Nešiojo stilius kiekvienam A1:A50 langeliui (dvi reikšmių taisyklės) -->
<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>

<!-- Nešiojo stilius kiekvienam C1:C50 langeliui (viena formulės taisyklė) -->
<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>

Kibtumas su style:map tas, kad jis gyvena ant langelių stilių, tad yra vienam langeliui. Kiekvienas taisyklės diapazono langelis turi nešioti stilių su žemėlapiu, įskaitant tuščius, kitaip Excel to langelio taisyklė tiesiog nedengia. HotXLS kiekvieno langelio esamą formatavimo stilių nukopijuoja, prijungia žemėlapius ir nešiojuosius stilius diferencijuoja pagal originalaus stiliaus ir žemėlapio teksto porą, tad 500 vienodai suformatuotų langelių diapazonas vis tiek duoda vieną stilių. Rašytojas taip pat ištempią rašomą lentelę iki taisyklės diapazono, dėl ko tuščios uodeginės eilutės taisyklės viduje išvedamos, o ne išmetamos. Nuo v2.384.69 styles.xml taip pat neša tuščią Default langelio stilių, tad style:apply-style-name="Default" visada turi taikinį

calcext rašyba, kurią LibreOffice iš tikrųjų priima

LibreOffice calcext reikšmės taisyklę priima tik tada, kai palyginimo operatorius yra reikšmės teksto dalis, tokia kaip >3 arba between(1,10), o formulės taisyklę – tik kai ji parašyta formula-is(...). Abu punktai HotXLS kainavo po leidimą, nes neteisingos rašybos duoda taisyklę, kuri importuojasi be klaidos, o tada atitinka ne tuos langelius

Pirmoji klaida buvo calcext:operator atributas šalia calcext:value. Skaitosi natūraliai, bet jis išgalvotas: LibreOffice to atributo nežino, tad kiekvieną reikšmės taisyklę importuodavo kaip "lygu 0". Antroji – is-true-formula(...), style:map rašyba, įkišta į calcext sąlygą, kurią LibreOffice importuodavo taip pat kaip langelio reikšmės palyginimą su 0. Formulės pataisymas išėjo v2.384.66, reikšmės – v2.384.69:

<!-- Neteisinga: LibreOffice ignoruoja calcext:operator ir importuoja "lygu 0" -->
<calcext:condition calcext:apply-style-name="CF_Hit"
                   calcext:operator="greater-than" calcext:value="100"/>

<!-- Teisinga: operatorius keliauja reikšmės viduje -->
<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"/>

<!-- Teisinga: formulės taisyklės naudoja formula-is, relatyvios nuorodos su pagrindiniu langeliu -->
<calcext:condition calcext:apply-style-name="CF_Dup"
                   calcext:value="formula-is(COUNTIF([.$C:.$C];[.C1])&gt;1)"
                   calcext:base-cell-address=".C1"/>

Pagrindinis langelis yra tai, kas relatyvioms nuorodoms duoda prasmę. HotXLS kiekvieną taisyklę įtvirtina prie kairiojo viršutinio pirmosios diapazono srities langelio, tad formulė, parašyta C1, vertinama kaip C2, C3 ir taip toliau žemyn diapazonu – lygiai taip, kaip pačioje Excel sąlyginiame formatavime. Taisyklės išraiška eina per tą patį vertėją kaip langelių formulės, tad masyvai, sąjungos, visi stulpeliai ir $ ženklai išeina aukščiau aprašytomis formomis. Delphi pusėje taisykles pridedate tiksliai taip, kaip pridėtumėte .xlsx failui

uses
  lxHandleX;

procedure AddOrderHighlights(Book: TXLSXWorkbook; Sheet: TXLSXWorksheet);
var
  Idx: Integer;
  Opts: TODSExportOptions;
begin
  // Reikšmių taisyklės: style:map cell-content()>100 plius calcext reikšmė ">100"
  Idx := Sheet.AddConditionalFormat('A1:A50', xlsxCfOpGreaterThan, '100');
  Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($00C0C0FF); // BGR: šviesiai raudona

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

  // Formulės taisyklė Excel sintakse (kablelių skirtukai, relatyvi C1 atžvilgiu):
  // style:map is-true-formula(...) plius calcext formula-is(...)
  Idx := Sheet.AddCondFormatExpression('C1:C50', 'COUNTIF($C:$C,C1)>1');
  Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($00CCFFFF); // BGR: šviesiai geltona

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

ODS iš Excel ir LibreOffice skaitymas atgal į Delphi

Kai HotXLS atidaro ODS failą, jo skaitytuvas priima abu sąlyginių formatų dialektus ir abi calcext rašybas, o taisyklės nesuskaičiuoja du kartus, kai failas jos neša abiem formomis. Tikri failai atkeliauja iš trijų rašytojų, kiekvieno su savais įpročiais:

• Senas ir naujas calcext. Failai su calcext:operator atributu, įskaitant HotXLS iki v2.384.69 rašytus ODS, vis tiek eina per senąjį analizavimą. Formulės sąlygos atpažįstamos kaip formula-is(...) arba is-true-formula(...)
• Excel style:map rašyba. Excel sąlygų priekin deda of:, kaip of:cell-content-is-between(1,10), ir reikšmių taisyklėse praleidžia pagrindinį langelį. Abu dalykai priimami
• Tušti langeliai. Excel ir LibreOffice tuščių langelių žemėlapį deda ant stulpelio numatytojo stiliaus, o ne ant langelio, tad skaitytuvas prieš rinkdamas žemėlapius išspręndžia pakartotinių langelių stulpelio numatytuosius stilius
• Sričių atkūrimas. Žemėlapiai renkami vienam langeliui, tad perskaitęs lapą skaitytuvas langelius su ta pačia sąlyga ir pagrindiniu langeliu vėl sulipdo į diapazonus – pirmiausia per kiekvieną eilutę, tada žemyn sutampančiais stulpelių ruožais – ir išmeta bet kurią taisyklę, jau perskaitytą iš calcext

v2.384.72 pataisymas liečia skaičių stilius, ne taisykles. Excel 16 ir LibreOffice 26.2 General formatą rašo kaip skaičių stilių, kurio number:number elementas neturi number:decimal-places – tipiškai <number:number number:min-integer-digits="1"/>. HotXLS skaitytuvas trūkstamą skaičių laikė dviem fiksuotais dešimtainiais, tad kiekviena Default stiliaus reikšmė importuodavosi su 0.00, ir 1.5 atrodė kaip 1.50. Nuo v2.384.72 paprastas skaičiaus elementas be dešimtainių vietų, be mažiausio dešimtainių skaičiaus, be grupavimo ir su daugiausia vienu sveikuoju skaitmeniu susieja su General, o vienišas General palieka langelį apskritai be jokio skaičiaus formato. Aplink jį esantis tekstas išlieka, kaip General" kg", o grupuoti skaičiai laiko ankstesnįjį susiejimą, nes Excel grupuoto General formato neturi

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 indeksuotėjas skaičiuojamas nuo 1
    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;

    // Langelis Excel General stiliumi skaitosi atgal be jokio skaičiaus formato
    // nuo v2.384.72, vietoj '0.00'
    Writeln('A2 format: "', Sheet.Cells[2, 1].NumberFormat, '"');
  finally
    Book.Free;
  end;
end;

Taisyklių formulės grįžta Excel sintakse su kablelių skirtukais – tokia forma, kokią perduotumėte AddCondFormatExpression, tad HotXLS parašyta taisyklė atgal perskaito kaip identiška eilutė. Platesniam vaizdui, ką ODS importo kelias išlaiko ir išmeta, žiūrėkite vadovą HotXLS ODS atvėrimo ir išsaugojimo apvalinimas; o kaip importuojant išplečiamos pakartotinės Excel ir LibreOffice eilutės – ODS pakartotinės eilutės kaip eilučių aukščio ruožai

Kokios HotXLS ODS sąlyginių formatų sąveikos ribos?

Dvigubo žymėjimo požiūris dengia reikšmių palyginimo ir formulės taisykles – ir ten sustoja. Viskas kita vienpusė arba apskritai nerašoma:

• Spalvų skalės ir duomenų juostos rašomos tik kaip calcext elementai, tad LibreOffice jas rodo, o Excel ne
• Kitos taisyklių rūšys, tokios kaip piktogramų rinkiniai, teksto taisyklės, top-N, virš vidurkio ir dublių taisyklės, dabartiniame rašytoje ODS išvesties neturi. Teksto taisyklę dažniausiai galima performuluoti į formulės taisyklę, pavyzdžiui ISNUMBER(SEARCH("late",B2)) virš B2:B200, kuri tada pasiekia abi programas
• Viso stulpelio ir visos eilutės taisyklės, tokios kaip C:C, klojamos tik ant realiai rašomos lentelės srities, o ne ant visų 1 048 576 eilučių, tad Excel šias taisykles mato tik faile egzistuojančiuose langeliuose
• Failai vien su style:map. Kai faile nėra calcext bloko, HotXLS relatyvias nuorodas formulės taisyklėse aiškina nuo atkurto diapazono kairiojo viršutinio kampo, o ne slenkdamas nuo nurodytojo pagrindinio langelio
• LibreOffice persidengiančios taisyklės. Kai vieną langelį dengia kelios taisyklės, LibreOffice ant jo rašo tik pirmosios taisyklės žemėlapį. Tokių failų iš vien style:map iki galo neperskaitysi – dar viena priežastis, kodėl skaitytuvas, kai abu yra, teikia pirmenybę calcext

Proceso riba svarbiau už bet kurį iš šių. Defektai už šių leidimų praslydo pro apvalinimus, kurie rašė ODS ir skaitė jį atgal su HotXLS, o kai kurie būtų pralankę ir rankinę patikrą netinkamoje programoje: viso stulpelio formulės veikė LibreOffice, kol Excel rodė #NAME?, o nuo v2.384.66 formulės taisyklės veikė LibreOffice, kol Excel iki pat v2.384.69 rodė apskritai jokių taisyklių. Jei ODS sąveika – reikalavimas, priėmimo testas yra failo atvėrimas Excel ir LibreOffice bei to, ką kiekviena rodo, palyginimas. Ta pati drausmė taikoma ir stiliams, į kuriuos rodo taisyklės; HotXLS sąlyginis formatavimas ir stiliai – straipsnis, dengiantis, kaip paryškinimo stiliai apibrėžiami darbaknygės pusėje

Trumpa atmintinė: ODS, kurį skaito abi programos

• Deklaruokite xmlns:of ir xmlns:msoxl ant content.xml šaknies, kitaip LibreOffice kiekvienai formulei rodys #VALUE! (HotXLS nuo v2.384.56)
• Nuorodas rašykite kaip [.A1], išsaugokite kiekvieną $, o visus stulpelius ir eilutes rašykite kaip [.A:.A] ir [.1:.1] (nuo v2.384.55 ir v2.384.65)
• Argumentams naudokite ;, nuorodų sąjungoms ~, o tarp inline masyvo eilučių – |
• Kiekvieną reikšmės ar formulės taisyklę rašykite kaip <style:map> ant kiekvieno dengto langelio stiliaus Excel atveju ir kaip calcext sąlygą LibreOffice atveju (nuo v2.384.69)
• Calcext viduje operatorių dėkite į reikšmę (>3, between(1,10)), o formulės taisykles rašykite formula-is(...) su pagrindiniu langeliu (nuo v2.384.66 ir v2.384.69)
• Importo metu tikėkitės General skaičiaus stiliaus be number:decimal-places; HotXLS jį skaito kaip General nuo v2.384.72
• Kiekvieną naują eksporto profilį patvirtinkite atvėrę failą ir Excel, ir LibreOffice – niekada tik vienoje jų

HotXLS – natyvi Delphi ir C++Builder skaičiuoklės biblioteka, skaitanti ir rašanti XLS, XLSX ir ODS be įdiegto Excel ar LibreOffice; pilnas šaltinis, funkcijų sąrašas ir licencijavimas yra HotXLS Delphi skaičiuoklės komponento puslapyje