Τύποι array στο HotXLS: γιατί η Excel βάζει @ και #VALUE!

Το Excel 365 εισάγει @ σε έναν τύπο όπως =SUM(A1:B1*{10,100}) και εμφανίζει #VALUE! όταν το αρχείο τον αποθηκεύει ως συνηθισμένο τύπο, γιατί τότε η Excel εφαρμόζει implicit intersection της παλιάς γενιάς σε κάθε τελεστέο τελεστή. Από το v2.384.68, το HotXLS Delphi Component αποθηκεύει αυτούς τους τύπους array-operator όπως ακριβώς η Excel 365: ως single-cell dynamic-array τύπους στο XLSX και ως array τύπους ενός κελιού στο XLS

Το σύμπτωμα επιβιώνει και από code review. Το Delphi service σας γράφει ένα workbook, το HotXLS το ξαναϋπολογίζει και κρατά στην cache 210 για τον =SUM(A1:B1*{10,100}), και ο πελάτης το ανοίγει σε Excel 16 και βλέπει =SUM(@A1:B1*@{10,100}) στη formula bar και #VALUE! στο κελί. Τίποτα στο αρχείο δεν είναι κατεστραμμένο. Αυτό που λείπει είναι τα metadata που λένε στην Excel ότι ο τύπος γράφτηκε υπό κανόνες dynamic-array, και χωρίς αυτά η Excel γυρνά στο evaluation model πριν τα dynamic arrays

Γιατί η Excel 365 βάζει @ σε τύπο που το HotXLS υπολόγισε σωστά;

Η Excel 365 βάζει @ επειδή ένας τύπος χωρίς dynamic-array σήμανση είναι κατ’ ορισμό legacy τύπος, και οι legacy τύποι συρρικνώνουν ένα multi-cell range σε ένα κελί όπου ένας τελεστής περιμένει μονή τιμή. Αυτή η συρρίκνωση είναι το implicit intersection: η Excel παίρνει το κελί του range που μοιράζεται τη σειρά του τύπου (για κάθετο range) ή τη στήλη του (για οριζόντιο range), και αν δεν υπάρχει τέτοιο κελί το αποτέλεσμα είναι #VALUE!. Η Excel 365 κρατά αυτή τη σημασία για τύπους παλιάς στυλ και εμφανίζει @ για να κάνει τη συρρίκνωση ορατή

Βάλτε =SUM(A1:B1*{10,100}) στο E5 και το legacy διάβασμα γίνεται προφανές. Το A1:B1 είναι οριζόντιο range, ο τύπος κάθεται στη στήλη E, το range δεν έχει κελί στη στήλη E, οπότε το @A1:B1 είναι #VALUE! και όλο το SUM το κληρονομεί. Κάτω από κανόνες dynamic-array το ίδιο κείμενο πολλαπλασιάζει στοιχείο προς στοιχείο, 1 × 10 + 2 × 100, και δίνει 210. Η formula engine του HotXLS αποτιμάει με τον dynamic-array τρόπο από τα releases v2.384.61 και v2.384.63· απλώς το file format δεν το έλεγε. Με το A1:B2 να κρατά 1, 2, 3 και 4, αυτοί είναι οι δοκιμαστικοί τύποι και όσα εμφανίζει η Excel 16:

Τύπος	Αποτέλεσμα HotXLS	Excel 16, αποθηκευμένος ως σκέτος τύπος	Αποθήκευση από v2.384.68
=SUM(A1:B1*{10,100})	210	#VALUE!	Dynamic array, η Excel δείχνει 210
=SUM((A1:B2>2)*1)	2	Implicit intersection, λάθος ή σφάλμα	Dynamic array, η Excel δείχνει 2
=SUMPRODUCT((A1:B2>2)*1)	2	Implicit intersection, λάθος ή σφάλμα	Dynamic array, η Excel δείχνει 2
=MAX(A1:B2-1)	3	Implicit intersection, λάθος ή σφάλμα	Dynamic array, η Excel δείχνει 3
=SUM(A1:B2)	10	10	Σκέτος τύπος, αμετάβλητος

Η τελευταία σειρά μετράει όσο οι πρώτες τέσσερις. Ο SUM(A1:B2) περνάει ένα range απευθείας σε παράμετρο συνάρτησης που δέχεται references, οπότε κανένας τελεστής δεν βλέπει ποτέ multi-cell range και κανένα intersection δεν μπορεί να συμβεί. Η ίδια η Excel 365 αποθηκεύει εκείνον τον τύπο ως σκέτο τύπο, και το HotXLS κάνει το ίδιο

Πώς αποθηκεύει το HotXLS τους τύπους array-operator σε XLSX και XLS

Το HotXLS γράφει έναν τύπο array-operator στο XLSX ως dynamic array ενός κελιού: το στοιχείο <c> κουβαλά cm="1", ο τύπος είναι <f t="array" ref="E5">, και το package παίρνει xl/metadata.xml με τύπο metadata XLDAPR του οποίου το extension κρατά dynamicArrayProperties fDynamic="1". Το attribute cm είναι ευρετήριο με βάση το 1 μέσα στο block cellMetadata εκείνου του part, και η εγγραφή XLDAPR πίσω του είναι αυτή που λέει στην Excel «αποτίμησέ το υπό κανόνες dynamic-array». Είναι η ίδια δομή που γράφει η Excel 16 όταν πληκτρολογείτε τον ίδιο τύπο και αποθηκεύετε, και έτσι καθορίστηκε εξαρχής το target layout

Στο XLS δεν υπάρχει metadata part, οπότε το HotXLS χρησιμοποιεί τη μόνη δομή που έχει το BIFF8 για array evaluation: έναν array τύπο ενός κελιού. Το κελί παίρνει εγγραφή FORMULA της οποίας το token stream είναι ένα μόνο PtgExp που δείχνει στον εαυτό του, ακολουθούμενο από εγγραφή ARRAY ($0221) που κουβαλά τον πραγματικό parsed τύπο πάνω στο range του ενός κελιού. Η Excel 365 γράφει τους dynamic-array τύπους στο XLS με τον ίδιο τρόπο, και μια παλαιότερη έκδοση Excel που διαβάζει το αρχείο βλέπει έναν κλασικό array τύπο Ctrl+Shift+Enter

Δεν εμπλέκεται κανένα νέο API. Η σήμανση συμβαίνει όταν αναθέτετε τον τύπο μέσα από το κανονικό cell API, και στις δύο engines. Στην πλευρά XLSX αυτό είναι το TXLSXCell.Formula:

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Data');
    Sheet.Cells[1, 1].Value := 1;
    Sheet.Cells[1, 2].Value := 2;
    Sheet.Cells[2, 1].Value := 3;
    Sheet.Cells[2, 2].Value := 4;

    // Τελεστής πάνω σε range ή inline array: αποθηκεύεται ως dynamic array
    Sheet.Cells[5, 5].Formula := '=SUM(A1:B1*{10,100})';
    Sheet.Cells[6, 5].Formula := '=SUM((A1:B2>2)*1)';
    // Range που περνάει απευθείας σε συνάρτηση: μένει σκέτο <f>
    Sheet.Cells[7, 5].Formula := '=SUM(A1:B2)';

    if Book.Recalculate = lxOk then
      Writeln(VarToStr(Sheet.Cells[5, 5].Value));   // 210

    // Η ρίζα array κρατά το κείμενό της χωρίς το '=' στην αρχή
    Writeln(Sheet.Cells[5, 5].Formula);              // SUM(A1:B1*{10,100})

    Book.SaveAs('probe.xlsx');   // E5 και E6 παίρνουν cm="1" + t="array"
  finally
    Book.Free;
  end;
end;

Μετά τη μετατροπή, το TXLSXCell.Formula επιστρέφει το κείμενο χωρίς =, την ίδια μορφή που αποθηκεύει το TXLSXRange.SetDynamicArrayFormula, οπότε κώδικας που συγκρίνει strings τύπων μετά την ανάθεση πρέπει να κανονικοποιεί το = στην αρχή

Η classic engine ακολουθεί τον ίδιο κανόνα μέσω IXLSRange.Formula σε ένα κελί. Η ανάθεση του τύπου τον ανακατευθύνει εσωτερικά στη διαδρομή array ενός κελιού, οπότε το αποθηκευμένο XLS περιέχει το ζευγάρι FORMULA συν ARRAY:

var
  Wb: IXLSWorkbook;
  Sh: TXLSWorksheet;
begin
  Wb := TXLSWorkbook.Create;
  Sh := Wb.Sheets.Add;
  Sh.Range['A1', 'A1'].Value := 1;
  Sh.Range['B1', 'B1'].Value := 2;
  Sh.Range['A2', 'A2'].Value := 3;
  Sh.Range['B2', 'B2'].Value := 4;

  Sh.Range['E5', 'E5'].Formula := '=SUM(A1:B1*{10,100})';  // εγγραφή ARRAY
  Sh.Range['E6', 'E6'].Formula := '=MAX(A1:B2-1)';         // εγγραφή ARRAY
  Sh.Range['E7', 'E7'].Formula := '=SUM(A1:B2)';           // σκέτο FORMULA

  Writeln(VarToStr(Sh.Range['E5', 'E5'].Value));   // 210
  Writeln(VarToStr(Sh.Range['E6', 'E6'].Value));   // 3
  Wb.SaveAs('probe.xls');
end;

Αν στερεώνετε multi-cell αποτέλεσμα αντί για βαθμωτό άθροισμα, τα explicit APIs παραμένουν το σωστό εργαλείο: το SetArrayFormula για προκαθορισμένου μεγέθους ορθογώνιο, όπως περιγράφεται στο dynamic array spill formulas με το HotXLS, ή το TXLSXRange.SetDynamicArrayFormula όταν θέλετε τη dynamic-array σήμανση XLSX πάνω σε range που διαστασιολογείτε μόνοι σας. Η αυτόματη διαδρομή σε αυτό το άρθρο καλύπτει μόνο τύπους που μπαίνουν σε ένα κελί

Ποιους τύπους σημαδεύει το HotXLS ως dynamic arrays;

Το HotXLS σημαδεύει έναν τύπο μόνο όταν ένας τελεστής έχει subtree τελεστέου που παράγει array. Ο έλεγχος τρέχει πάνω στο compiled syntax tree, και ένας τελεστέος παράγει array αν είναι multi-cell range, inline σταθερά array, ή άλλη έκφραση τελεστή που έχει η ίδια τέτοιο τελεστέο. Οι παρενθέσεις είναι διαφανείς. Οι τελεστές που μετρούν είναι οι αριθμητικοί (+ - * / ^), η concatenation (&), οι έξι συγκρίσεις, τα unary συν και πλην, και το ποσοστό:

• A1:B1*{10,100}, (A1:B2>2)*1, --(B1:B2>0) και A1:B2-1 σημαδεύονται, όπου κι αν εμφανίζονται στον τύπο, συμπεριλαμβανομένου μέσα σε SUMPRODUCT
• SUM(A1:B2) και SUMPRODUCT(A1:A2,{1;10}) δεν σημαδεύονται, γιατί το range και το array πηγαίνουν απευθείας σε όρισμα συνάρτησης και κανένας τελεστής δεν τα αγγίζει
• A1*2 ή SUM(A1,B1)*2 δεν σημαδεύονται: τα single-cell references και τα αποτελέσματα συναρτήσεων είναι βαθμωτά για αυτόν τον έλεγχο

Τρία όρια είναι σκόπιμα. Πρώτον, η σήμανση συμβαίνει μόνο όταν ο τύπος μπαίνει μέσα από το API, δηλαδή TXLSXCell.Formula στην XLSX engine και ανάθεση Formula ή Value σε ένα κελί στην classic engine. Τύποι φορτωμένοι από αρχείο γράφονται πίσω ακριβώς όπως βρέθηκαν, γιατί ένας legacy τύπος από άλλον παραγωγό μπορεί να εξαρτάται σκόπιμα από implicit intersection. Δεύτερον, κείμενο που δεν περιέχει ούτε : ούτε { προσπερνάται χωρίς δεύτερο compile. Τρίτον, ένας τύπος που θα έκανε spill, όπως το =A1:B1*2 από μόνο του, σημαδεύεται ως dynamic array ενός κελιού, στερεωμένος εκεί που τον βάλατε. Το HotXLS δεν τον επεκτείνει, και η Excel θα απλώσει το αποτέλεσμα στα γειτονικά κελιά την επόμενη φορά που θα ξαναϋπολογίσει

Ο κανόνας τελεστέου εδώ είναι ο αδελφός του κανόνα argument-class που καλύπτει το implicit intersection για defined names στο HotXLS. Εκείνο το άρθρο αφορά παραμέτρους συναρτήσεων δηλωμένες ως value class· αυτό αφορά τελεστές, που στο legacy model απαιτούν πάντα τιμές

Τι άλλαξε στο calculation engine για να συμφωνούν τα αποτελέσματα

Η διόρθωση αποθήκευσης στο v2.384.68 στηρίζεται στο ότι η formula engine του HotXLS επέστρεφε ήδη τιμές Excel 365, που πήρε αρκετές προγενέστερες διορθώσεις και στις δύο engines. Η πιο ορατή ήταν το SUMPRODUCT: μέχρι το v2.384.61 δέχονταν μόνο δύο ή περισσότερα σκέτα ranges, οπότε το SUMPRODUCT((B1:B2>0)*1), το SUMPRODUCT(--(B1:B2>0)) και ακόμα το μονοόρισματο SUMPRODUCT(B1:B2) επέστρεφαν #N/A. Το HotXLS τώρα αποτιμάει ορίσματα έκφρασης στοιχείο προς στοιχείο με τους κανόνες της Excel:

• κάθε όρισμα πρέπει να έχει ακριβώς το ίδιο shape, με μια βαθμωτή τιμή να μετράει ως 1 × 1, αλλιώς το αποτέλεσμα είναι #VALUE!
• τιμή σφάλματος μέσα σε οποιοδήποτε όρισμα επιστρέφεται ως αποτέλεσμα
• τα κείμενα και τα λογικά στοιχεία μετρούν ως 0, οπότε ακόμα χρειάζεται (B1:B2>0)*1 ή -- για να γίνει το TRUE 1
• ορίσματα που είναι όλα σκέτα ranges κρατούν το αρχικό streaming loop, ώστε μεγάλα ranges να μην υλοποιούνται ως arrays

Η οικογένεια SUM (SUM, COUNT, AVERAGE, MIN, MAX, COUNTA) χρησιμοποιεί τον ίδιο element-wise evaluator όταν ένα όρισμα είναι έκφραση τελεστή πάνω σε range, οπότε ο =SUM((B1:B2>0)*1) μετράει και τις δύο σειρές αντί να κοιτάζει μόνο το πρώτο κελί. Το v2.384.62 έκανε τον τελεστή intersection με κενό να επιστρέφει το κοινό ορθογώνιο δύο references, με #NULL! όταν δεν τέμνονται, οπότε ο =SUM(A1:B2 B1:B2) είναι 6 και όχι 2, και το αποτέλεσμα μπορεί να τροφοδοτήσει παραμέτρους reference όπως ROWS και INDEX. Το v2.384.63 πρόσθεσε στον parser inline σταθερές array όπως {1,2;3,4} (κόμματα χωρίζουν στήλες, semicolons χωρίζουν σειρές) και unions references όπως (A1:B2,D4). Οι συγκρίσεις στοιχείο προς στοιχείο δίνουν επίσης σε κενό στοιχείο τον τύπο της άλλης πλευράς, FALSE απέναντι σε λογικό, όπερ ταιριάζει με τον βαθμωτό κανόνα του v2.384.53 που περιγράφεται στο comparison chains και κενά κελιά στο HotXLS

var
  V: Variant;
begin
  // Το Book είναι το TXLSXWorkbook από το πρώτο παράδειγμα,
  // το active sheet του κρατά A1:B2 = 1, 2, 3, 4
  V := Book.Calculate('=SUMPRODUCT((A1:B2>2)*1)');   // 2
  V := Book.Calculate('=SUMPRODUCT(A1:B2)');          // 10, μονό όρισμα
  V := Book.Calculate('=SUMPRODUCT(A1:A2,{1;10})');   // 31 = 1*1 + 3*10
  V := Book.Calculate('=SUM(A1:B2 B1:B2)');           // 6, κοινό range B1:B2
  V := Book.Calculate('=SUM((A1:B2,B1:B2))');         // 16, η επικάλυψη μετράει δύο φορές
  V := Book.Calculate('=ROWS({1,2,3;4,5,6})');        // 2
  V := Book.Calculate('=TRUE*1');                     // 1, ήταν -1 πριν το v2.384.61
end;

Το TXLSXWorkbook.Calculate αποτιμάει string τύπου πάνω στο active sheet χωρίς να το αποθηκεύει, γρήγορος τρόπος να τσεκάρετε τη συμπεριφορά της engine. Μια προσοχή για το @ καθαυτό: το HotXLS παραδοσιακά δεχόταν @ ανάμεσα σε δύο references ως binary intersection, και τώρα αποτιμάει εκείνη τη μορφή με αληθινή σημασιολογία intersection. Στην Excel 365 το @ είναι unary πρόθεμα implicit-intersection. Μην γράφετε @ μέσα σε κείμενο τύπου περιμένοντας τη σημασία της Excel· χρησιμοποιήστε κενό για intersection και αφήστε τους κανόνες αποθήκευσης παραπάνω να τακτοποιήσουν τη σημασιολογία dynamic-array

Γιατί η Excel αρνήθηκε να ανοίξει το αρχείο ή υπολόγισε λάθος τιμή;

Το να δεχτεί η Excel τη dynamic-array σήμανση πήρε τρεις διορθώσεις που κανένα test self-round-trip δεν θα έπιανε, γιατί το HotXLS διάβαζε σωστά τη δική του έξοδο σε κάθε περίπτωση. Κάθε μία βρέθηκε ανοίγοντας την έξοδο του HotXLS σε Excel 16 και αντικαθιστώντας μία μεταβλητή τη φορά:

1. Το GUID του extension πρέπει να είναι όλο πεζό. Το ext uri στο xl/metadata.xml πρέπει να είναι ακριβώς {bdbb8cdc-fa1e-496e-a857-3c3f30c029c3}. Ένα παλιό template του HotXLS το έγραφε με μικτά πεζά-κεφαλαία, και η Excel 16 αρνιόταν να ανοίξει ολόκληρο το package, όχι μόνο το κελί. Workbooks δημιουργημένα με TXLSXRange.SetDynamicArrayFormula πριν το v2.384.68 είχαν το ίδιο πρόβλημα
2. Το κείμενο της ρίζας array δεν κουβαλά = στην αρχή. Ο XLSX writer εκπέμπει το αποθηκευμένο κείμενο μιας ρίζας array verbatim μέσα στο <f>. Αν το μετατραπέν κελί κρατούσε το = του, το στοιχείο θα διάβαζε <f t="array" ref="E5">=SUM(...)</f>, που η Excel απορρίπτει κι αυτό στο άνοιγμα. Το HotXLS το αφαιρεί κατά τη μετατροπή, γι’ αυτό το TXLSXCell.Formula διαβάζεται πίσω χωρίς αυτό
3. Το Double(True) είναι -1 στο Delphi. Η μετατροπή Variant ακολουθεί τη σύμβαση COM όπου το TRUE είναι όλα τα bits σε 1, και το VarIsNumeric(True) επιστρέφει επίσης True. Πριν το v2.384.61 αυτό έκανε τον =TRUE*1 να επιστρέφει -1 και άφηνε τα λογικά στοιχεία array να ταξινομούνται ως αριθμοί, οπότε μια σύγκριση όπως (B1:B2>0)=TRUE πήγαινε στραβά. Το HotXLS τώρα ελέγχει για varBoolean πριν μεταχειριστεί Variant ως αριθμό στη βαθμωτή αριθμητική, την αριθμητική array και την ταξινόμηση στοιχείων array, και το TRUE μετράει ως 1

Operand classes του BIFF8: οι λεπτομέρειες σε επίπεδο byte για implementers του format

Στο BIFF8 κάθε token τελεστέου κουβαλά την operand class του στο ίδιο του το token byte, και η Excel εμπιστεύεται εκείνη την class περισσότερο από τη δομή του τύπου. Το [MS-XLS] ορίζει την class ως πεδίο PtgDataType δύο bits στα bits 5 και 6 του token: 1 για reference, 2 για value, 3 για array. Τα πέντε χαμηλά bits ονομάζουν το token, οπότε η ίδια area reference έχει τρεις γραφές:

Token	Reference class	Value class	Array class
PtgRef	$24	$44	$64
PtgArea	$25	$45	$65
PtgArray	$20	$40	$60

Το HotXLS έκανε λάθος τρία από αυτά σε διαφορετικά σημεία, και το καθένα παρήγαγε ξεχωριστό σύμπτωμα στην Excel ενώ διαβαζόταν σωστά πίσω στο HotXLS:

• Σταθερές array σε reference class. Ο encoder διάλεγε την class από το context, και οι παράμετροι SUM ή ROWS είναι reference class, οπότε ο =SUM({1,2}) γραφόταν με PtgArray ως $20. Η Excel εμφανίζει όλο τον τύπο ως =#N/A. Μια σταθερά array δεν μπορεί ποτέ να είναι reference, οπότε από το v2.384.63 το HotXLS γράφει array class $60 όπου το context ζητά reference
• Τελεστέοι σε value class των PtgIsect και PtgUnion. Οι δυαδικοί τελεστές έπαιρναν τελεστέους σε value class, που είναι σωστό για το * αλλά λάθος για τους τελεστές reference. Με areas $45 πριν από PtgIsect ($0F), η Excel διάβαζε τον =SUM(A1:B2 B1:B2) ως =SUM(@A1:B2 @B1:B2) και επέστρεφε #VALUE!. Από το v2.384.62 οι τελεστέοι των PtgIsect και PtgUnion ($10) γράφονται σε reference class, $25
• Τελεστέοι σε value class μέσα στην εγγραφή ARRAY. Η Excel εφαρμόζει implicit intersection ακόμα και μέσα σε array τύπο όταν ένας τελεστέος είναι σε value class. Το HotXLS έγραφε $45 εκεί, οπότε ο array τύπος ενός κελιού για τον =SUM(A1:B1*{10,100}) αποτιμόταν 10 στην Excel. Από το v2.384.68, το token stream μιας εγγραφής ARRAY προάγει κάθε reference σε value class και κάθε σταθερά array σε array class, $65 και $60, που είναι όσα γράφει η Excel

Ένας reader που αγνοεί τα bits class κάνει round-trip και τα τρία ευτυχισμένα, οπότε αν συντηρείτε δικό σας BIFF8 writer, συγκρίνετε τα bits class κάθε token τελεστέου με αρχείο αποθηκευμένο από Excel του ίδιου τύπου, όχι μόνο τους αριθμούς tokens

Σύντομη αναφορά

• Η Excel 365 δείχνει @ όταν ένας τελεστής σε έναν σκέτο, χωρίς σήμανση τύπο δέχεται multi-cell range ή inline array
• Το HotXLS v2.384.68 και μεταγενέστερο αποθηκεύει τέτοιους τύπους ως XLSX dynamic arrays ενός κελιού (cm="1", t="array", metadata XLDAPR) και ως XLS array τύπους ενός κελιού (FORMULA με PtgExp συν ARRAY $0221)
• Μετρούν μόνο τελεστέοι τελεστών· range που περνάει απευθείας σε όρισμα συνάρτησης μένει σκέτος τύπος
• Σημαδεύονται μόνο τύποι που μπαίνουν μέσω TXLSXCell.Formula ή του classic Formula / Value ενός κελιού· φορτωμένοι τύποι μένουν ανέγγιχτοι
• Το μετατραπέν κελί ρίζας διαβάζεται πίσω χωρίς το = στην αρχή
• Το GUID ext uri του dynamic-array πρέπει να είναι πεζό αλλιώς η Excel απορρίπτει το package
• Στο Delphi, το Double(True) είναι -1· τσεκάρετε varBoolean πριν αριθμητική μετατροπή
• BIFF8: σταθερές array ποτέ σε reference class, τελεστέοι PtgIsect / PtgUnion σε reference class, τελεστέοι εγγραφής ARRAY σε array class

Το HotXLS διαβάζει, γράφει και υπολογίζει workbooks XLS και XLSX εγγενώς από Delphi και C++Builder, και αποθηκεύει τους τύπους array-operator ώστε η Excel 365 να τους ανοίγει με τις ίδιες τιμές που υπολόγισε το HotXLS. Δείτε το HotXLS Delphi spreadsheet component για εκδόσεις, τεκμηρίωση και δοκιμαστική λήψη