Interop של ODS ב-HotXLS: נוסחאות וכללים ש-Excel קורא

כדי להפיק קובץ ODS שגם Excel וגם LibreOffice קוראים נכון, HotXLS כותב כל נוסחה בתחביר OpenFormula תחת מרחב שמות מוצהר של of:, וכותב כל conditional format של ערך או נוסחה פעמיים: בתור <style:map> על הסגנון של כל תא מכוסה, שהוא הצורה היחידה ש-Excel 16 קורא, ובתור בלוק calcext:conditional-formats, שהוא הצורה ש-LibreOffice סומך עליה. כל יישום מתעלם מהחצי שמיועד לשני, ולכן קובץ שמוצג נכון באחד מהם לא מוכיח דבר על השני

המשפט האחרון הזה הוא הלקח מאחורי שש מהדורות של HotXLS בין v2.384.55 ל-v2.384.72. כל תיקון התחיל בקובץ ש-HotXLS כתב, קרא בחזרה בצורה מושלמת, ואחד משני היישומים היעד קרא שגוי. ההמשך הוא מה שכל יישום באמת מקבל, הסימון שמשביע את שניהם, וקריאות ה-API של HotXLS שמפיקות אותו מ-Delphi

מדוע קובץ ODS נראה תקין ביישום אחד ושבור בשני?

קובץ ODS נראה תקין ביישום אחד ושבור בשני כי Excel ו-LibreOffice קוראים חלקים שונים של אותה חבילה. OpenDocument נותן לנוסחאות ול-conditional formats יותר מאיות חוקי אחד, LibreOffice מוסיף מרחב שמות של הרחבות משלו מעל זה, וכל צרכן בוחר את תת-הקבוצה שהוא מממש. כותב שנבדק מול צרכן אחד בלבד יתכנס בשמחה אל סימון שהשני מקריא שגוי בשקט

אף יישום לא מדווח על שגיאה. LibreOffice מציג #VALUE! בתאים שאת הנוסחאות שלהם הוא לא הצליח לפרסר; Excel פותח את חוברת העבודה כשה-conditional formats פשוט נעדרים, או עם נוסחה ששוכתבה למשהו שמחושב ל-#NAME? או לקבוע 0. כותב שעושה round-trip לפלט של עצמו לא רואה אף אחד מאלה. HotXLS נפל בדיוק במלכודת הזאת עם מרחב השמות של הנוסחאות: הקורא שלו התאים את הקידומת of: בתור טקסט רגיל, ולכן כל round trip עצמי עבר בזמן ש-LibreOffice הציג #VALUE! בכל תא נוסחה

יכולת	Excel 16 קורא	LibreOffice 26.2 קורא
עמודה שלמה שנכתבה בתור A:A	מתפרש בטעות בתור A:(A)	סובלני
עמודה שלמה שנכתבה בתור [.A:.A]	כן	כן
Conditional formats בתור <style:map>	כן, הצורה היחידה שהוא קורא	מתעלם כש-calcext קיים
Conditional formats בתור calcext:conditional-formats	מתעלם	כן, מועדף
כלל ערך של calcext עם תכונת calcext:operator	מתעלם	מיובא בתור "שווה ל-0"
כלל נוסחה של calcext שנאיית is-true-formula(...)	מתעלם	מיובא בתור השוואת ערך עם 0

OpenFormula ב-ODS: מצהירים את מרחב השמות, ואז מדייקים בתחביר

תא נוסחה ב-ODS קריא ל-LibreOffice רק כשהקידומת of: ב-table:formula נפתרת למרחב שמות XML מוצהר. הקידומת אינה קישוט. of: ממופה אל urn:oasis:names:tc:opendocument:xmlns:of:1.2, ו-msoxl:, הקידומת ש-HotXLS משתמש בה עבור נוסחאות שהמתרגם שלו ל-OpenFormula לא ממדל, ממופה אל http://schemas.microsoft.com/office/excel/formula. לפני v2.384.56 השורש של ה-content.xml השתמש בשתי הקידומות בלי להצהיר עליהן, ו-LibreOffice לא יכל לזהות בכלל את דקדוק הנוסחאות

<!-- לפני v2.384.56: קידומת בשימוש, מעולם לא הוצהרה; LibreOffice מציג #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"/>

<!-- מאז v2.384.56: שני מרחבי השמות של הנוסחאות מוצהרים על השורש -->
<office:document-content
    xmlns:of="urn:oasis:names:tc:opendocument:xmlns:of:1.2"
    xmlns:msoxl="http://schemas.microsoft.com/office/excel/formula" ...>

עם מרחב השמות מתוקן, הביטוי עצמו עדיין חייב להיות OpenFormula תקף, כפי שהוגדר ב-OpenDocument 1.3 חלק 4. המוקשים הם המקומות שבהם התחביר של Excel ו-OpenFormula נראים דומים אבל אינם זהים:

• הפניות תאים מסוגרות ועם נקודה פותחת, והסמנים של $ חלק מההפניה: [.$A$1] ו-[.A$1:.$B2] הם OpenFormula תקף. לפני v2.384.55 הכותב של HotXLS השמיט כל $, ולכן הפניות אבסולוטיות חזרו יחסיות והשתבשו רק ברגע שמישהו העתיק את התא
• עמודות ושורות שלמות חייבות להשתמש בצורה המסוגרת [.A:.A], [.$A:.$B], [.1:.1], [.$1:.$2]. of:=SUM(A:A) חשוף סובלני על ידי LibreOffice, אבל Excel 16 פותח אותו בתור =SUM(A:(A)) עם #NAME?, והופך הפניות שורות ו-$A:$B לקבוע 0. HotXLS כותב את הצורה המסוגרת מאז v2.384.65
• ארגומנטים של פונקציות מופרדים ב-;, לא ב-,
• איחודי הפניות משתמשים באופרטור ~: ה-AREAS((A1,B2)) של Excel נהיה AREAS(([.A1]~[.B2])). לתרגם את הפסיק הזה ל-; במקום זאת פירושו להפוך ארגומנט איחוד אחד לשני ארגומנטים
• מערכים inline מפרידים עמודות ב-; ושורות ב-|: ה-{1,2;3,4} של Excel נהיה {1;2|3;4}. לפני v2.384.55 HotXLS הפיק {1;2;3;4}, שורה אחת של ארבעה ערכים

הפסיק הוא החלק הקשה, כי תו אחד של Excel נושא שלוש משמעויות. מאז v2.384.55 הכותב של HotXLS עוקב אחרי מחסנית סוגריים בזמן התרגום: ( ישירות אחרי שם פותח קריאת פונקציה, שהפסיקים שלה נהיים ;; כל ( אחר הוא סוגריים של קיבוץ, שהפסיקים שלו נהיים ~; ופסיקים בתוך {} הם מפרידי עמודות של מערך. עם זה ועם תיקון מרחב השמות, LibreOffice 26.2 חישב נכון את כל שמונה נוסחאות הבדיקה של מערכים ואיחודים, INDEX ו-AREAS מעל איחודים כלולים

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;

    // נכתב בתור of:=SUM([.A:.A]) מאז v2.384.65
    Sheet.Cells[1, 4].Formula := 'SUM(A:A)';
    // נכתב בתור of:=[.A1]*[.$B$1]; ה-$ שורדים מאז v2.384.55
    Sheet.Cells[2, 4].Formula := 'A1*$B$1';

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

נוסחאות שהמתרגם לא ממדל נופלות בחזרה אל msoxl:= עם טקסט ה-Excel ללא שינוי, ולכן ההצהרה על msoxl חשובה גם כן. בכותב הנוכחי הנתיב הזה כולל הפניות ממוסגרות גיליון כמו Sheet2!A1 והפניות טבלה מובנות. HotXLS קורא נוסחאות msoxl: בחזרה בייבוא, ולכן ה-round trip שלו עצמו שומר על הביטוי שלם, אבל איך יישום אחר מתייחס אליהן הוא מחוץ לשליטת הכותב. אם נוסחה שהצרכנים שלכם תלויים בה יוצאת עם הקידומת msoxl:, פתחו את הקובץ בשני היישומים לפני המשלוח

מדוע Excel לא רואה conditional formats שנכתבו רק בתור calcext?

Excel 16 לא רואה conditional formats של calcext כי הוא קורא אותם ב-ODS אך ורק מבני <style:map> של סגנונות תאים ומתעלם לגמרי מהבלוק calcext:conditional-formats. הניסוי שמכריע זאת קצר: לוקחים ODS שנשמר על ידי LibreOffice, מוחקים את האלמנטים style:map, ו-Excel קורא אפס כללים; מוחקים במקום זאת את בלוק ה-calcext, ו-Excel עדיין קורא את כולם. LibreOffice מתנהג הפוך. calcext הוא מרחב השמות של ההרחבות של LibreOffice, לא חלק מהתקן ODF, וכשכלל calcext קיים LibreOffice לוקח אותו ומתעלם מה-style:map

לפני v2.384.69 HotXLS כתב רק calcext, ולכן קובץ ODS עם הדגשות תקינות לגמרי נפתח ב-Excel בלי כללי ערך ובלי כללי נוסחה כלל. HotXLS כותב עכשיו את שתי הצורות. החצי של ה-style:map משתמש בדקדוק התנאי של סכימת OpenDocument (ODF 1.3 חלק 3), עם האיותים המדויקים שגם Excel 16 וגם LibreOffice 26.2 מפיקים כשהם שומרים ODS:

<!-- מפושט. סגנון נשא עבור כל תא של A1:A50 (שני כללי ערך) -->
<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 (כלל נוסחה אחד) -->
<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 היא שהוא חי על סגנונות תאים, ולכן הוא פר תא. כל תא בטווח של הכלל חייב לשאת סגנון שמחזיק את ה-map, תאים ריקים כלולים, או שהכלל פשוט לא מכסה את התא הזה ב-Excel. HotXLS מעתיק את סגנון העיצוב הקיים של כל תא, מוסיף את ה-maps, ומדה-דופליקט סגנונות נשא לפי הזוג של הסגנון המקורי וטקסט ה-map, ולכן טווח של 500 תאים עם עיצוב זהה עדיין מפיק סגנון אחד. הכותב גם מרחיב את הטבלה שנכתבת אל טווח הכלל, מה שאומר ששורות זנב ריקות בתוך כלל נפלטות במקום להיזרק. מאז v2.384.69 ה-styles.xml גם נושא סגנון תא ריק של Default, כך של-style:apply-style-name="Default" תמיד יש מטרה

איות ה-calcext ש-LibreOffice באמת מקבל

LibreOffice מקבל כלל ערך של calcext רק כשאופרטור ההשוואה חלק מטקסט הערך, כמו >3 או between(1,10), וכלל נוסחה רק כשהוא מאוית formula-is(...). שתי הנקודות עלו ל-HotXLS מהדורה, כי האיותים השגויים מפיקים כלל שמיובא בלי שגיאה ואז תואם את התאים השגויים

הטעות הראשונה הייתה תכונת calcext:operator לצד calcext:value. היא נקראת טבעית, אבל היא מומצאת: LibreOffice לא מכיר את התכונה הזאת, ולכן ייבא כל כלל ערך בתור "שווה ל-0". השנייה הייתה לשים את is-true-formula(...), האיות של ה-style:map, בתוך תנאי calcext, ש-LibreOffice ייבא גם כן בתור השוואת ערך-תא עם 0. תיקון הנוסחה יצא ב-v2.384.66 ותיקון הערך ב-v2.384.69:

<!-- שגוי: LibreOffice מתעלם מ-calcext:operator ומייבא "שווה ל-0" -->
<calcext:condition calcext:apply-style-name="CF_Hit"
                   calcext:operator="greater-than" calcext:value="100"/>

<!-- נכון: האופרטור נוסע בתוך הערך -->
<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"/>

<!-- נכון: כללי נוסחה משתמשים ב-formula-is, הפניות יחסיות עוגנות בתא הבסיס -->
<calcext:condition calcext:apply-style-name="CF_Dup"
                   calcext:value="formula-is(COUNTIF([.$C:.$C];[.C1])&gt;1)"
                   calcext:base-cell-address=".C1"/>

תא הבסיס הוא מה שנותן להפניות היחסיות את משמעותן. HotXLS עוגן כל כלל בתא השמאלי-עליון של אזור הטווח הראשון שלו, ולכן נוסחה שנכתבה עבור C1 מחושבת בתור C2, C3 וכן הלאה במורד הטווח, בדיוק כפי שהיא עושה בעיצוב המותנה של Excel עצמו. ביטוי הכלל עובר דרך אותו מתרגם כמו נוסחאות תאים, ולכן מערכים, איחודים, עמודות שלמות וסמני $ יוצאים בצורות שתוארו למעלה. בצד Delphi מוסיפים כללים בדיוק כפי שהייתם מוסיפים עבור קובץ .xlsx

uses
  lxHandleX;

procedure AddOrderHighlights(Book: TXLSXWorkbook; Sheet: TXLSXWorksheet);
var
  Idx: Integer;
  Opts: TODSExportOptions;
begin
  // כללי ערך: style:map cell-content()>100 בתוספת ערך calcext ">100"
  Idx := Sheet.AddConditionalFormat('A1:A50', xlsxCfOpGreaterThan, '100');
  Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($00C0C0FF); // BGR: אדום בהיר

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

  // כלל נוסחה בתחביר Excel (מפרידי פסיק, יחסי ל-C1):
  // style:map is-true-formula(...) בתוספת calcext formula-is(...)
  Idx := Sheet.AddCondFormatExpression('C1:C50', 'COUNTIF($C:$C,C1)>1');
  Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($00CCFFFF); // BGR: צהוב בהיר

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

קריאת ODS מ-Excel ומ-LibreOffice בחזרה אל Delphi

כש-HotXLS פותח קובץ ODS, הקורא שלו מקבל את שני הניבים של ה-conditional formats ואת שני איותי ה-calcext, והוא לא סופר כלל פעמיים כשהקובץ נושא אותו בשתי הצורות. קבצים אמיתיים מגיעים משלושה כותבים, כל אחד עם ההרגלים שלו:

• calcext ישן וחדש. קבצים עם תכונת calcext:operator, כולל ODS שנכתבו על ידי HotXLS לפני v2.384.69, עדיין עוברים את הפרסור המורשת. תנאי נוסחה מזוהים בתור formula-is(...) או is-true-formula(...)
• איות ה-style:map של Excel. Excel מקדים תנאים ב-of:, כמו of:cell-content-is-between(1,10), ומשמיט את תא הבסיס בכללי ערך. שניהם מתקבלים
• תאים ריקים. Excel ו-LibreOffice שניהם שמים את ה-map עבור תאים ריקים על סגנון ברירת המחדל של העמודה ולא על תא, ולכן הקורא פותר סגנונות ברירת מחדל של עמודות עבור תאים חוזרים לפני איסוף ה-maps
• בניית טווחים מחדש. Maps נאספים לפי תא, ולכן אחרי שגיליון נקרא הקורא ממזג תאים שחולקים את אותו תנאי ותא בסיס בחזרה לטווחים, קודם על פני כל שורה ואז במורד מוטות עמודות תואמים, ומשליך כל כלל שכבר נקרא מ-calcext

תיקון v2.384.72 נוגע לסגנונות מספר, לא לכללים. Excel 16 ו-LibreOffice 26.2 שניהם כותבים את הפורמט הכללי בתור סגנון מספר שהאלמנט number:number שלו אינו מחזיק number:decimal-places, בדרך כלל <number:number number:min-integer-digits="1"/>. הקורא של HotXLS התייחס לספירה החסרה בתור שתי ספרות עשרוניות קבועות, ולכן כל ערך בסגנון ה-Default יובא עם 0.00 ו-1.5 הוצג בתור 1.50. מאז v2.384.72 אלמנט מספר רגיל בלי מקומות עשרוניים, בלי מינימום עשרוניים, בלי קיבוץ ועם ספרת שלמה אחת לכל היותר ממופה אל General, ו-General בודד משאיר את התא בלי פורמט מספר בכלל. טקסט סביבו נשמר, כמו ב-General" kg", ומספרים מקובצים שומרים על המיפוי הקודם כי ל-Excel אין פורמט General מקובץ

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 מתחיל מ-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;

    // תא בסגנון ה-General של Excel נקרא בחזרה בלי פורמט מספר
    // מאז v2.384.72, במקום '0.00'
    Writeln('A2 format: "', Sheet.Cells[2, 1].NumberFormat, '"');
  finally
    Book.Free;
  end;
end;

נוסחאות כללים חוזרות בתחביר Excel עם מפרידי פסיק, אותה צורה שהייתם מעבירים אל AddCondFormatExpression, ולכן כלל שנכתב על ידי HotXLS נקרא בחזרה בתור המחרוזת הזהה. לתמונה הרחבה יותר של מה שנתיב ייבוא ה-ODS שומר ומשליך, ראו את מדריך ה-open וה-save של ODS ב-HotXLS; לגבי איך שורות חוזרות של Excel ו-LibreOffice מתרחבות בייבוא, ראו את שורות חוזרות של ODS בתור מוטות גובה שורה

מהם הגבולות של interop ה-conditional formats של ODS ב-HotXLS?

הגישה של הסימון הכפול מכסה כללי השוואת ערך וכללי נוסחה, ועוצרת שם. כל השאר חד-צדדי או לא נכתב בכלל:

• Color scales ו-data bars נכתבים רק בתור אלמנטי calcext, ולכן LibreOffice מציג אותם ו-Excel לא
• סוגי כללים אחרים, כמו icon sets, כללי טקסט, top-N, מעל-הממוצע וכללי כפילויות, אין להם פלט ODS בכותב הנוכחי. כלל טקסט אפשר בדרך כלל לנסח מחדש בתור כלל נוסחה, למשל ISNUMBER(SEARCH("late",B2)) מעל B2:B200, שאז מגיע אל שני היישומים
• כללי עמודה שלמה ושורה שלמה כמו C:C נפרשים רק מעל אזור הטבלה שנכתב בפועל, ולא מעל כל 1,048,576 השורות, ולכן Excel רואה את הכללים האלה רק על תאים שקיימים בקובץ
• קבצים עם style:map בלבד. כשלקובץ אין בלוק calcext, HotXLS מפרש הפניות יחסיות בכללי נוסחה מהפינה השמאלית-עליונה של הטווח הבנוי מחדש, ולא בהזזה מתא הבסיס שצוין
• כללים חופפים מ-LibreOffice. כשתא אחד מכוסה בכמה כללים, LibreOffice כותב עליו רק את ה-map של הכלל הראשון. קבצים כאלה לא ניתנים לקריאה מלאה מ-style:map לבדו, וזו סיבה נוספת שהקורא מעדיף calcext כששניהם קיימים

הגבול של התהליך חשוב יותר מכל אלה. הפגמים מאחורי המהדורות האלה עברו round trips שכתבו ODS וקראו אותו בחזרה עם HotXLS, וחלקם היו עוברים גם בדיקה ידנית ביישום הלא נכון: נוסחאות עמודה שלמה עבדו ב-LibreOffice בזמן ש-Excel הציג #NAME?, ומ-v2.384.66 כללי נוסחה עבדו ב-LibreOffice בזמן ש-Excel עדיין לא הציג כללים בכלל עד v2.384.69. אם interop של ODS הוא דרישה, בדיקת הקבלה היא פתיחת הקובץ ב-Excel וב-LibreOffice והשוואת מה שכל אחד מציג. אותה משמעת חלה על הסגנונות שאליהם הכללים מצביעים; המאמר על עיצוב מותנה וסגנונות של HotXLS מכסה איך סגנונות הדגשה מוגדרים בצד חוברת העבודה

עזר זריז: ODS ששני היישומים קוראים

• מצהירים xmlns:of ו-xmlns:msoxl על שורש ה-content.xml, או ש-LibreOffice מציג #VALUE! עבור כל נוסחה (HotXLS מאז v2.384.56)
• כותבים הפניות בתור [.A1], שומרים על כל $, וכותבים עמודות ושורות שלמות בתור [.A:.A] ו-[.1:.1] (מאז v2.384.55 ו-v2.384.65)
• משתמשים ב-; לארגומנטים, ב-~ לאיחודי הפניות, וב-| בין שורות של מערך inline
• כותבים כל כלל ערך או נוסחה בתור <style:map> על הסגנון של כל תא מכוסה עבור Excel, ובתור תנאי calcext עבור LibreOffice (מאז v2.384.69)
• ב-calcext, שמים את האופרטור בתוך הערך (>3, between(1,10)) ומאייתים כללי נוסחה formula-is(...) עם תא בסיס (מאז v2.384.66 ו-v2.384.69)
• מצפים לסגנון מספר General בלי number:decimal-places בייבוא; HotXLS קורא אותו בתור General מאז v2.384.72
• מאמתים כל פרופיל ייצוא חדש בפתיחת הקובץ גם ב-Excel וגם ב-LibreOffice, לעולם לא באחד מהם בלבד

HotXLS היא ספריית גיליונות ילידית ל-Delphi ול-C++Builder שקוראת וכותבת XLS, XLSX ו-ODS בלי Excel או LibreOffice מותקנים; המקור המלא, רשימת התכונות והרישוי נמצאים ב-דף רכיב הגיליונות HotXLS ל-Delphi