Σημειογραφία τύπων R1C1 στο Delphi με το HotXLS

Το HotXLS μεταγλωττίζει φόρμουλες γραμμένες σε σημειογραφία αναφοράς R1C1 μέσω της TXLSCalculator.GetCompiledFormulaR1C1, η οποία δέχεται μορφές όπως R2C3 (απόλυτη), R[-1]C[2] (σχετική μετατόπιση) και RC (το τρέχον κελί), τις μετατρέπει σε σημειογραφία A1 ως προς τη γραμμή και τη στήλη του κελιού στο οποίο βρίσκεται η φόρμουλα, και τροφοδοτεί το αποτέλεσμα στον ίδιο μεταγλωττιστή που χειρίζεται τις συνηθισμένες φόρμουλες A1. Για κώδικα Delphi και C++Builder που δημιουργεί την ίδια φόρμουλα σε εκατοντάδες γραμμές, αυτή και μόνη η μέθοδος εξαλείφει ολόκληρη μια κατηγορία σφαλμάτων συνένωσης συμβολοσειρών (string-concatenation)

Αυτή η κατηγορία σφαλμάτων είναι γνώριμη σε όποιον έχει γεμίσει προγραμματιστικά μια στήλη. Επαναλαμβάνετε τον βρόχο πάνω στις γραμμές, και για κάθε γραμμή κατασκευάζετε μια συμβολοσειρά φόρμουλας A1 με Format('D%d*E%d', [Row, Row]). Κάθε επανάληψη ενσωματώνει αριθμούς γραμμών μέσα στο κείμενο, και οι αριθμοί γραμμών είναι το μόνο τμήμα που αλλάζει. Αν κάνετε λάθος τη μετατόπιση έστω μία φορά, αναμείξετε έναν μετρητή βρόχου με βάση το 0 με αριθμούς γραμμών A1 που βασίζονται στο 1, ή μετατοπίσετε το μπλοκ δεδομένων κατά μία γραμμή επικεφαλίδας, τότε κάθε φόρμουλα στη στήλη δείχνει μία γραμμή λάθος. Τίποτα δεν εμφανίζει σφάλμα· οι αριθμοί είναι απλώς λανθασμένοι. Η φόρμουλα που πραγματικά εννοούσατε, «πολλαπλασίασε τα δύο κελιά στα αριστερά μου», δεν αναφέρει καθόλου αριθμό γραμμής, και η σημειογραφία R1C1 σάς επιτρέπει να τη γράψετε έτσι

Τι είναι η σημειογραφία R1C1 και πότε πρέπει να τη χρησιμοποιείτε;

Η σημειογραφία R1C1 προσδιορίζει τα κελιά με αριθμό γραμμής και στήλης αντί για γράμμα στήλης συν αριθμό γραμμής, και σημειώνει τις σχετικές αναφορές ως ρητές μετατοπίσεις από το κελί της φόρμουλας. Το R2C3 είναι το απόλυτο κελί στη γραμμή 2, στήλη 3, το οποίο η A1 γράφει ως $C$2. Το R[-1]C[2] βρίσκεται μία γραμμή πάνω και δύο στήλες δεξιά από όπου κι αν βρίσκεται η φόρμουλα. Το RC είναι το ίδιο το κελί της φόρμουλας. Οι μετατοπίσεις σε αγκύλες είναι η ουσία: μια σχετική αναφορά στη R1C1 διαβάζεται το ίδιο ανεξάρτητα από το ποιο κελί τη φιλοξενεί, ενώ η απόδοση της ίδιας αναφοράς σε A1 αλλάζει σε κάθε γραμμή

Η σημειογραφία δικαιολογεί την ύπαρξή της σε ένα ακριβώς σενάριο, και είναι ένα σύνηθες σενάριο: τη δημιουργία προτύπων, όπου η ίδια σχετική φόρμουλα πρέπει να τοποθετηθεί σε κάθε γραμμή μιας περιοχής δεδομένων. Στην A1 πρέπει να αποδίδετε εκ νέου το κείμενο της φόρμουλας για κάθε γραμμή. Στη R1C1 το κείμενο είναι σταθερό. Αυτό είναι επίσης πιο κοντά στον τρόπο με τον οποίο σκέφτονται εσωτερικά οι μορφές αρχείων υπολογιστικών φύλλων: οι εγγραφές κοινόχρηστων φορμουλών (shared-formula) αποθηκεύουν τις σχετικές αναφορές ως μετατοπίσεις γραμμής και στήλης από το κελί υποδοχής, οπότε μια συμβολοσειρά A1 ανά γραμμή είναι κάτι που ο κώδικάς σας συνθέτει μόνο και μόνο για να το αποσυνθέσει ξανά ο αναλυτής (parser) πίσω σε μετατοπίσεις. Η R1C1 παρακάμπτει αυτή τη διαδρομή μετ' επιστροφής. Για διαδραστικές φόρμουλες που συντάσσονται από ανθρώπους, η A1 παραμένει η φυσική επιλογή, γι' αυτό και παραμένει η προεπιλογή παντού στο HotXLS

Πώς μεταγλωττίζει το HotXLS μια φόρμουλα R1C1;

Το HotXLS εκθέτει αυτή τη λειτουργία σε δύο επίπεδα, που προστέθηκαν στην v2.175.0. Η TXLSCalculator.GetCompiledFormulaR1C1(UncompiledFormula: String; SheetID, CurRow, CurCol: Integer): TXLSCompiledFormula είναι αυτή που καλεί ο περισσότερος κώδικας: επιστρέφει μια μεταγλωττισμένη φόρμουλα έτοιμη για αξιολόγηση, ακριβώς όπως η αδελφή της A1, η GetCompiledFormula, αλλά με δύο επιπλέον παραμέτρους που ονομάζουν τη γραμμή και τη στήλη, με βάση το 0, του κελιού στο οποίο ανήκει η φόρμουλα. Από κάτω της, η TXLSFormula.GetCompiledR1C1 παράγει το ακατέργαστο συντακτικό δέντρο, και μια αυτόνομη συνάρτηση, η R1C1ToA1(const AFormula: String; CurRow, CurCol: Integer): String, εκτελεί την πραγματική μετατροπή σημειογραφίας. Η ροή επεξεργασίας είναι σκόπιμα απλή: μεταφράζει το κείμενο R1C1 σε ισοδύναμο κείμενο A1 χρησιμοποιώντας τις συντεταγμένες του κελιού υποδοχής, και έπειτα μεταγλωττίζει το κείμενο A1 μέσω της υπάρχουσας μηχανής — της ίδιας μηχανής που επιλύει καθορισμένα ονόματα και αναφορές μεταξύ φύλλων και αποστέλλει κλήσεις σε προσαρμοσμένες συναρτήσεις φύλλου εργασίας

Επειδή η μετατροπή γίνεται πριν από τη μεταγλώττιση, όλα όσα ακολουθούν συμπεριφέρονται σαν να είχατε γράψει εσείς οι ίδιοι τη φόρμουλα A1. Η R1C1ToA1('R2C3', 4, 3) επιστρέφει '$C$2' ανεξάρτητα από το κελί υποδοχής, επειδή και οι δύο συντεταγμένες είναι απόλυτες. Η R1C1ToA1('SUM(R[-3]C[0]:R[-1]C[0])', 4, 3) — μια φόρμουλα που βρίσκεται στο D5, αφού τα CurRow = 4 και CurCol = 3 είναι με βάση το 0 — επιστρέφει 'SUM(D2:D4)': οι μετατοπίσεις επιλύθηκαν ως προς τη γραμμή 5, στήλη D, και εκδόθηκαν ως απλές σχετικές αναφορές A1. Τα εύρη δεν χρειάζονται ειδική μεταχείριση· η άνω-κάτω τελεία περνάει αυτούσια και κάθε άκρο μετατρέπεται ανεξάρτητα

// Η φόρμουλα βρίσκεται στο D5: CurRow = 4, CurCol = 3 (και τα δύο με βάση το 0)
S := R1C1ToA1('R2C3', 4, 3);
// S = '$C$2'  (απόλυτη γραμμή και στήλη)

S := R1C1ToA1('SUM(R[-3]C[0]:R[-1]C[0])', 4, 3);
// S = 'SUM(D2:D4)'  (οι μετατοπίσεις επιλύθηκαν ως προς το D5)

S := R1C1ToA1('ROUND(R[-1]C[0], 2)', 4, 3);
// S = 'ROUND(D4, 2)'  (το R στο ROUND παραμένει ανέγγιχτο)

Συμπλήρωση μιας στήλης με μία σχετική φόρμουλα

Το όφελος φαίνεται στον βρόχο. Συγκρίνετε την έκδοση A1, η οποία αποδίδει εκ νέου το κείμενο της φόρμουλας σε κάθε επανάληψη, με την έκδοση R1C1, όπου η φόρμουλα είναι σταθερή και μόνο οι συντεταγμένες υποδοχής μετακινούνται. Και οι δύο μεταγλωττίζονται μέσω της TXLSCalculator και αξιολογούνται με την GetValue· ο υπολογιστής (calculator) δέχεται μια επανάκληση (callback) παρόχου κελιών κατά την κατασκευή, ώστε ο αξιολογητής να μπορεί να αντλεί τιμές κελιών από την πηγή δεδομένων σας

// Στυλ A1: διαφορετική συμβολοσειρά φόρμουλας για κάθε γραμμή
for Row := 1 to 500 do
begin
  FormulaText := Format('D%d*E%d', [Row + 1, Row + 1]);  // γραμμές A1 με βάση το 1
  Compiled := Calc.GetCompiledFormula(FormulaText, 0);
  // ... αξιολόγηση, αποθήκευση, απελευθέρωση ...
end;

const
  AmountFormula = 'RC[-2]*RC[-1]';  // δύο κελιά αριστερά, ίδια γραμμή
var
  Calc: TXLSCalculator;
  Compiled: TXLSCompiledFormula;
  Value: Variant;
  Row: Integer;
begin
  Calc := TXLSCalculator.Create(nil, Provider.GetValue);
  try
    for Row := 1 to 500 do
    begin
      Compiled := Calc.GetCompiledFormulaR1C1(AmountFormula, 0, Row, 5);
      try
        if Calc.GetValue(0, Compiled, Row, 5, Value, 1) = lxOk then
          StoreResult(Row, 5, Value);
      finally
        Compiled.Free;
      end;
    end;
  finally
    Calc.Free;
  end;
end;

Ο βρόχος R1C1 δεν έχει καθόλου αριθμητική γραμμών μέσα στο κείμενο της φόρμουλας. Το 'RC[-2]*RC[-1]' σημαίνει «ίδια γραμμή, δύο στήλες αριστερά, επί ίδια γραμμή, μία στήλη αριστερά» τόσο στη γραμμή 2 όσο και στη γραμμή 500, και αν το μπλοκ δεδομένων μετακινηθεί αργότερα προς τα κάτω κατά μία γραμμή επικεφαλίδας, η σταθερά της φόρμουλας δεν αλλάζει — αλλάζουν μόνο τα όρια του βρόχου. Η έκδοση A1 έχει δύο σημεία όπου μπορεί να γίνει λάθος η προσαρμογή + 1· η έκδοση R1C1 έχει μηδέν

Ποιες μορφές R1C1 δέχεται ο μετατροπέας;

Ο μετατροπέας R1C1ToA1 αναγνωρίζει τις μορφές που τεκμηριώθηκαν για την v2.175.0: R[n]C[m] για σχετικές μετατοπίσεις προς οποιαδήποτε κατεύθυνση, RnCm για απόλυτη γραμμή-και-στήλη, R[-n]C[m] με αρνητικές μετατοπίσεις, γυμνό RC για το ίδιο το κελί της φόρμουλας, και εύρη όπως R1C1:R3C3 ή R[-1]C:R[1]C. Τα τμήματα γραμμής και στήλης αναλύονται ανεξάρτητα, οπότε λειτουργούν εξίσου καλά και μικτές μορφές όπως το R[1]C3 — σχετική γραμμή, απόλυτη στήλη — και τα γράμματα δεν κάνουν διάκριση πεζών-κεφαλαίων, οπότε το r[-1]c[2] μεταγλωττίζεται ακριβώς όπως το κεφαλαίο δίδυμό του. Τα τμήματα σε αγκύλες γίνονται μη αγκυρωμένες (σχετικές) συντεταγμένες A1· οι γυμνοί αριθμοί γίνονται απόλυτες συντεταγμένες αγκυρωμένες με $

Το ενδιαφέρον ερώτημα είναι πώς ο μετατροπέας αποφεύγει να αλλοιώσει οτιδήποτε άλλο στη φόρμουλα, δεδομένου ότι τα R και C είναι συνηθισμένα γράμματα. Ο κανόνας διάκρισής του βασίζεται σε token: ένα R θεωρείται αρχή αναφοράς μόνο όταν δεν προηγείται άλλο γράμμα, και ο υποψήφιος πρέπει στη συνέχεια να αναλυθεί πλήρως — προαιρετικό τμήμα γραμμής, υποχρεωτικό C, προαιρετικό τμήμα στήλης — διαφορετικά το κείμενο επαναφέρεται ανέπαφο. Γι' αυτό το ROUND(R[-1]C[0], 2) μετατρέπει μόνο την εσωτερική αναφορά: το R στο ROUND ακολουθείται από O αντί για ψηφίο, αγκύλη ή C, οπότε η ανάλυση αποτυγχάνει και το όνομα της συνάρτησης περνάει αυτούσιο. Η ίδια λογική προστατεύει το ROW(), και τα ονόματα συναρτήσεων που ξεκινούν με C δεν είναι ποτέ υποψήφια, αφού μόνο το R ξεκινά μια αναφορά. Οι σημειώσεις έκδοσης της v2.175.0 αναφέρουν ότι παραλείπονται επίσης οι κυριολεκτικές συμβολοσειρές (string literals) και τα αναγνωριστικά· παρ' όλα αυτά, αν μια συμβολοσειρά σε εισαγωγικά μέσα στη φόρμουλά σας τυχαίνει να περιέχει κείμενο με ακριβώς το σχήμα μιας αναφοράς R1C1, αξίζει να ελέγξετε μία φορά το μεταγλωττισμένο αποτέλεσμα πριν το εμπιστευτείτε σε παραγωγικό περιβάλλον

Λεπτομέρειες βάσεων συντεταγμένων και αγκύρωσης που αξίζει να γνωρίζετε

Δύο συμβάσεις συναντώνται μέσα σε αυτό το API, και το να τις κρατάτε ξεκάθαρες αποφεύγει τη μοναδική πραγματική παγίδα. Οι παράμετροι CurRow και CurCol της GetCompiledFormulaR1C1 είναι με βάση το 0, ακολουθώντας το API του υπολογιστή, ενώ οι αριθμοί μέσα στην ίδια τη σημειογραφία είναι με βάση το 1, ταιριάζοντας με ό,τι εμφανίζει το Excel: το R2C3 είναι γραμμή 2, στήλη 3, δηλαδή $C$2, όχι $D$3. Αν ο μετρητής του βρόχου σας είναι ήδη με βάση το 0, τον περνάτε απευθείας ως CurRow· το + 1 βρίσκεται μέσα στον μετατροπέα, όχι στον δικό σας κώδικα

Μια ακόμη λεπτομέρεια έχει σημασία αν η μεταγλωττισμένη φόρμουλα θα επιζήσει του κελιού για το οποίο μεταγλωττίστηκε. Όταν παραλείπετε ένα τμήμα γραμμής ή στήλης — τις μορφές RC[-1] ή R[2]C — η συντεταγμένη που παραλείπεται επιλύεται στο κελί υποδοχής και εκδίδεται ως απόλυτη συντεταγμένη, αγκυρωμένη με $, στο μετατρεπόμενο κείμενο A1. Κατά την αξιολόγηση αυτό είναι αόρατο, αφού η τιμή είναι ίδια και στις δύο περιπτώσεις. Όμως η αγκύρωση σχετική-έναντι-απόλυτης καθορίζει πώς μετατοπίζονται οι αναφορές όταν εισάγονται ή διαγράφονται γραμμές ή στήλες αργότερα, όπως καλύπτεται στο συνοδευτικό άρθρο για την προσαρμογή αναφορών φόρμουλας. Αν χρειάζεστε μια συντεταγμένη να παραμείνει σχετική κατά τη διάρκεια δομικών επεξεργασιών, γράψτε τη μετατόπιση ρητά — R[0]C[-1] αντί για RC[-1] — ώστε ο μετατροπέας να εκδώσει μια μη αγκυρωμένη αναφορά A1

Η μεταγλώττιση R1C1 αποτελεί μέρος της μηχανής φορμουλών στο HotXLS Delphi Excel Component, μαζί με τον μεταγλωττιστή A1, το γράφημα επανυπολογισμού και το API αξιολόγησης που παρουσιάστηκε παραπάνω. Αν ο κώδικάς σας δημιουργεί υπολογιστικά φύλλα επαναλαμβάνοντας φόρμουλες κάτω από στήλες, η μετακίνηση αυτών των βρόχων από συνενωμένη A1 σε μία μόνο σταθερά R1C1 είναι μία από τις φθηνότερες αναβαθμίσεις αξιοπιστίας που διατίθενται