Η Μηχανή Υπολογισμού Τύπων (Formula Engine) του HotXLS και Προσαρμοσμένες Συναρτήσεις στο Delphi

Μια βιβλιοθήκη υπολογιστικών φύλλων που αποθηκεύει μόνο συμβολοσειρές τύπων και μια βιβλιοθήκη με λειτουργική μηχανή υπολογισμού τύπων (formula engine) είναι δύο διαφορετικά προϊόντα που φαίνονται πανομοιότυπα μέχρι τη στιγμή που θα ζητήσετε από ένα από αυτά έναν αριθμό. Ο περισσότερος κώδικας υπολογιστικών φύλλων Delphi δεν παρατηρεί ποτέ αυτό το κενό, επειδή το Excel το καλύπτει: γράψτε το SUM(B2:B501) σε ένα κελί, αποθηκεύστε το, και το Excel επανυπολογίζει το σύνολο τη στιγμή που ένας άνθρωπος ανοίγει το αρχείο. Αν βγάλετε τον άνθρωπο από αυτήν τη διαδικασία και εκτελέσετε το ίδιο βιβλίο εργασίας μέσω μιας ροής εργασιών διακομιστή που εξάγει απευθείας σε CSV, η διαφορά παύει να είναι ακαδημαϊκή. Το CSV μεταφέρει το κυριολεκτικό κείμενο =SUM(B2:B501) εκεί όπου έπρεπε να βρίσκεται ένας αριθμός, επειδή σε κανένα σημείο δεν αξιολογήθηκε πραγματικά ο τύπος

Αυτή είναι η διαχωριστική γραμμή στην οποία το HotXLS βρίσκεται στη σωστή πλευρά. Αντιμετωπίζει έναν τύπο με τον τρόπο που το κάνουν οι μορφές αρχείων, ως αποθηκευμένο κείμενο συν ένα προαιρετικό προσωρινά αποθηκευμένο (cached) αποτέλεσμα, οπότε μια απλή εξαγωγή CSV αναπαράγει τη συνταγή και όχι το πιάτο. Αλλά φέρει επίσης μια μηχανή υπολογισμού που μπορείτε να καλέσετε απευθείας, την ίδια μηχανή και στις δύο προσόψεις XLS και XLSX, συν ένα hook για την επίλυση ονομάτων συναρτήσεων που η μηχανή δεν έχει ακούσει ποτέ. Το HotXLS είναι μια εγγενής βιβλιοθήκη Object Pascal που διαβάζει και γράφει XLS και XLSX από το Delphi και το C++Builder χωρίς αυτοματοποίηση του Excel (Excel automation), και το κομμάτι του υπολογισμού είναι αυτό που μετατρέπει τους αποθηκευμένους τύπους πίσω σε τιμές κατά παραγγελία

Οι τύποι αποθηκεύονται, δεν αξιολογούνται αμέσως (eagerly)

Η εγγραφή ενός τύπου σε ένα κελί δεν υπολογίζει τίποτα. Κατά την αποθήκευση, το βιβλίο εργασίας καταγράφει το κείμενο του τύπου. Στην πλευρά του XLS, καταγράφει επίσης σημαίες που διέπονται από το RecalcOnSave, το οποίο έχει ως προεπιλογή την τιμή True και λέει στο Excel να κάνει επανυπολογισμό κατά το άνοιγμα. Αυτό το μοντέλο είναι σωστό για αρχεία που προορίζονται για το Excel και λάθος για ροές που καταναλώνουν απευθείας τις τιμές των κελιών, είτε πρόκειται για εξαγωγή CSV, εξαγωγή HTML είτε για τον δικό σας κώδικα που διαβάζει τα κελιά. Για αυτές τις περιπτώσεις, αξιολογήστε ρητά με το Calculate. Υπάρχει σε τέσσερα σημεία εισόδου: τα TXLSWorkbook, IXLSWorksheet, TXLSXWorkbook και TXLSXWorksheet εκθέτουν όλα τη function Calculate(const Formula: WideString): Variant

// evaluate in-process, then ship the value rather than the recipe
Total := Book.Calculate('SUM(Sales!B2:B501)');
Sheet.Cells[502, 2].Value := Total;
Book.SaveAsCSV('sales.csv', 0, ',');   // the CSV now carries the number

Η έκφραση που παραδίδεται στο Calculate είναι συνηθισμένο κείμενο τύπου Excel. Οι αναφορές μεταξύ φύλλων, τα καθορισμένα ονόματα και οι φωλιασμένες συναρτήσεις επιλύονται όλες έναντι του τρέχοντος βιβλίου εργασίας στη μνήμη, γεγονός που καθιστά την κλήση χρήσιμη πολύ πέρα από τη διόρθωση των εξαγωγών CSV. Αντιμετωπίστε το ως μηχανισμό επαλήθευσης (assertion mechanism). Ένας δημιουργός που μόλις έγραψε πεντακόσιες γραμμές λεπτομερειών μπορεί να ζητήσει από το βιβλίο εργασίας το δικό του γενικό σύνολο και να το συγκρίνει με το νούμερο που υπολόγισε ανεξάρτητα στην Pascal, εντοπίζοντας ένα σφάλμα εύρους "off-by-one" προτού το κάνει ο ελεγκτής του πελάτη

Πλαισιώνει επίσης τη σωστή στρατηγική δοκιμών για έξοδο με πολλούς τύπους. Το Excel παραμένει η υλοποίηση αναφοράς της γλώσσας τύπων, οπότε για τους λίγους τύπους που έχουν επιχειρηματικές συνέπειες, διατηρήστε ένα εγκεκριμένο αρχείο δοκιμών (fixture file) του οποίου οι αναμενόμενες τιμές παρήχθησαν από το ίδιο το Excel, και κάντε τη ροή κατασκευής (build pipeline) να αξιολογεί τους τύπους του παραγόμενου βιβλίου εργασίας με το Calculate έναντι αυτών των δοκιμών. Οι διαφορές στη συνέχεια εμφανίζονται ως αποτυχημένες δοκιμές στο Delphi και όχι ως ασυμφωνίες που ανακαλύπτει ένας πελάτης συγκρίνοντας δύο αναφορές

Προσθήκη επιχειρηματικών συναρτήσεων με το OnUserFunction

Όταν η μηχανή συναντά ένα όνομα συνάρτησης που δεν αναγνωρίζει, προκαλεί ένα συμβάν (event) αντί να αποτύχει αμέσως. Εκχωρήστε το OnUserFunction σε οποιαδήποτε κλάση βιβλίου εργασίας και μπορείτε να επιλύσετε την κλήση μόνοι σας:

procedure TReportBuilder.HandleUserFunction(Sender: TObject;
  const FunctionName: WideString; const Args: Variant;
  var Value: Variant; var Handled: Boolean);
begin
  if SameText(FunctionName, 'DISCOUNT') then
  begin
    Value := Args[0] * 0.9;   // Args arrives as a Variant array
    Handled := True;
  end;
end;

// wiring and use
Book.OnUserFunction := HandleUserFunction;
Sheet.Cells[1, 1].Value := 200;
Sheet.Cells[1, 2].Formula := 'DISCOUNT(A1)';
Net := Book.Calculate('DISCOUNT(A1) + SUM(A1:A1)');

Τρεις λεπτομέρειες χρήζουν προσοχής. Πρώτον, ορίστε το Handled := True μόνο όταν αναγνωρίσατε πραγματικά το όνομα. Αφήνοντάς το σε False επιτρέπετε στη μηχανή να συνεχίσει τον κανονικό χειρισμό άγνωστης συνάρτησης, οπότε ένας ενιαίος χειριστής μπορεί να εξυπηρετήσει πολλά βιβλία εργασίας χωρίς να διεκδικεί ό,τι περνάει από αυτόν. Δεύτερον, συγκρίνετε τα ονόματα χωρίς διάκριση πεζών-κεφαλαίων με τη SameText, καθώς οι δημιουργοί τύπων πληκτρολογούν discount( και DISCOUNT( εναλλακτικά. Τρίτον, τα ορίσματα φτάνουν προ-αξιολογημένα: η κλήση DISCOUNT(A1) σας παραδίδει την τιμή του A1, όχι την αναφορά, οπότε μια συνάρτηση δεν μπορεί να γνωρίζει από πού προήλθαν οι είσοδοί της. Αυτό το τελευταίο σημείο θέτει τον περιορισμό για τον οποίο μιλάει η επόμενη ενότητα

Αντιμετωπίστε το σώμα του χειριστή με την ίδια αμυντική στάση όπως κάθε εξωτερικό σημείο εισόδου. Ο πίνακας Args αντικατοπτρίζει ό,τι πληκτρολόγησε ο δημιουργός του τύπου, επομένως επικυρώστε τον αριθμό και τους τύπους των ορισμάτων πριν κάνετε αναφορά σε αυτά, και αποφασίστε εκ των προτέρων τι επιστρέφει μια μη έγκυρη κλήση: μια τιμή σφάλματος Variant ή μια εξαίρεση (exception). Η επιλογή έχει σημασία επειδή μια εξαίρεση που προκαλείται μέσα στον χειριστή διαδίδεται προς τα έξω μέσω της κλήσης Calculate που προκάλεσε την αξιολόγηση. Αυτό είναι αποδεκτό σε μια αυστηρά ελεγχόμενη γεννήτρια και απρεπές σε μια υπηρεσία που αξιολογεί βιβλία εργασίας που έχουν γραφτεί από χρήστες, όπου ένας κακός τύπος θα μπορούσε να καταρρίψει ολόκληρο το αίτημα. Σε αυτό το πλαίσιο, κάντε catch μέσα στον χειριστή και επιστρέψτε μια τιμή-φρουρό (sentinel) που μπορεί να αναγνωρίσει και να καταγράψει η γύρω ροή εργασίας

Οι συναρτήσεις με επίγνωση θέσης χρειάζονται την παραλλαγή Ex

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

procedure TReportBuilder.HandleUserFunctionEx(Sender: TObject;
  const FunctionName: WideString; const Args: Variant;
  const Context: TXLSUserFunctionContext;
  var Value: Variant; var Handled: Boolean);
begin
  if SameText(FunctionName, 'REGIONRATE') then
  begin
    // ο ίδιος τύπος παράγει διαφορετικό συντελεστή σε κάθε περιφερειακό φύλλο
    Value := RateForSheet(Context.SheetIndex) * Args[0];
    Handled := True;
  end;
end;

Το TXLSUserFunctionContext μεταφέρει τα SheetIndex, Row και Col του κελιού που αξιολογείται. Εάν το αποτέλεσμα μιας συνάρτησης εξαρτάται έστω και ελάχιστα από τη θέση της, συνδέστε το συμβάν Ex από την αρχή. Η εκ των υστέρων προσθήκη πλαισίου (context) σε έναν χειριστή που καλούν ήδη τριάντα τύποι είναι πολύ πιο περίπλοκη από την επιλογή της σωστής υπογραφής την πρώτη ημέρα, και τα δύο συμβάντα είναι κατά τα άλλα τόσο παρόμοια που δεν υπάρχει λόγος να ξεκινήσετε με το στενότερο

Οι προσαρμοσμένες συναρτήσεις δεν μεταφέρονται στο Excel

Μια προσαρμοσμένη συνάρτηση ζει εξ ολοκλήρου μέσα στη διεργασία σας. Το όνομα DISCOUNT σημαίνει κάτι μόνο όσο εκτελείται ο κώδικας Delphi και ο χειριστής συμβάντων του. Ανοίξτε το αποθηκευμένο αρχείο στο Excel και το DISCOUNT είναι απλώς ένα μη αναγνωρίσιμο όνομα. Το κελί εμφανίζει το σφάλμα #NAME? εκτός εάν υπάρχει μια αντίστοιχη συνάρτηση VBA ή πρόσθετο (add-in) στο μηχάνημα του χρήστη. Αυτό είναι το σχεδιαστικό γεγονός που διαχωρίζει μια επίδειξη από ένα έτοιμο προϊόν προς παράδοση, και επιβάλλει μια επιλογή που πρέπει να κάνετε σκόπιμα αντί να την ανακαλύψετε αργότερα

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

Υπάρχει ένα ήσυχο πλεονέκτημα στο συμβόλαιο μόνο για τιμές: προστατεύει την πνευματική ιδιοκτησία. Ένας κανόνας τιμολόγησης που αξιολογείται στη Delphi διεργασία σας και παραδίδεται ως αριθμός δεν μπορεί να αναλυθεί αντίστροφα (reverse-engineered) από το βιβλίο εργασίας με τον τρόπο που μπορεί να γίνει με έναν ορατό τύπο, και ένας χρήστης δεν μπορεί να τον καταστρέψει επεξεργαζόμενος ένα ενδιάμεσο κελί. Οι γεννήτριες τιμολογίων, οι καταστάσεις προμηθειών και οι κάρτες χρεώσεων ανήκουν σχεδόν πάντα σε αυτήν την κατηγορία. Η περίπτωση που χρειάζεται πραγματικά ζωντανούς τύπους είναι το διαδραστικό μοντέλο what-if, όπου ο πελάτης αναμένεται να αλλάξει εισόδους και να παρακολουθήσει τα σύνολα να κινούνται, και αυτά πρέπει να κατασκευαστούν από το λεξιλόγιο του ίδιου του Excel συν τα καθορισμένα ονόματα

Λειτουργίες υπολογισμού, επανάληψη (iteration) και R1C1: οι ρυθμίσεις της πρόσοψης XLS

Η πρόσοψη XLS εκθέτει τις ρυθμίσεις υπολογισμού επιπέδου BIFF που διαβάζει το Excel από το αρχείο. Το CalculationMode δέχεται xlCalcManual, xlCalcAutomatic (την προεπιλογή) ή xlCalcAutomaticExceptTables, και καθορίζει πώς συμπεριφέρεται το Excel μόλις ανοίξει το αρχείο. Ένα βιβλίο εργασίας μοντέλου με χιλιάδες τύπους είναι συχνά πιο φιλικό να παραδίδεται σε χειροκίνητη λειτουργία (manual mode), ώστε ο παραλήπτης να αποφασίζει πότε θα γίνει ο επανυπολογισμός. Το EnableIteration (προεπιλογή False), μαζί με το MaxIterations (προεπιλογή 100) and MaxIterationChange (προεπιλογή 0.001), ξεκλειδώνει τις σκόπιμες κυκλικές αναφορές του είδους επαναληπτικής σύγκλισης που εμφανίζονται σε ορισμένα χρηματοοικονομικά μοντέλα. Το ReferenceStyle εναλλάσσει μεταξύ εμφάνισης A1 και R1C1, και το UseFullPrecision αντικατοπτρίζει την επιλογή ακρίβειας όπως εμφανίζεται (precision-as-displayed) του Excel

Αυτές οι ιδιότητες ζουν στην πρόσοψη XLS επειδή αντιστοιχίζονται σε εγγραφές BIFF. Κατά τη δημιουργία .xlsx, σχεδιάστε τους τύπους έτσι ώστε να μην εξαρτώνται από επαναληπτικές ρυθμίσεις, ή υπολογίστε τις συγκλίνουσες τιμές στο Delphi και γράψτε τα αποτελέσματα

Τύποι πινάκων (Array formulas): το δημόσιο σημείο εισόδου είναι το XLSX

Οι παραδοσιακοί τύποι πινάκων στυλ CSE δημιουργούνται μέσω του TXLSXRange.SetArrayFormula:

// one array formula spanning A2:A4
Sheet.RCRange[2, 1, 4, 1].SetArrayFormula('A1*{1;2;3}');

Η αντίστοιχη μέθοδος υπάρχει στην ιεραρχία κλάσεων XLS αλλά βρίσκεται σε ιδιωτική ενότητα, οπότε δεν υπάρχει υποστηριζόμενος τρόπος δημιουργίας νέων τύπων πινάκων σε αρχεία .xls. Οι υπάρχοντες σε ανοιχτά αρχεία ολοκληρώνουν την κυκλική διαδρομή ανέπαφοι. Αυτό που δεν μπορείτε να κάνετε είναι να τους δημιουργήσετε. Ο κανόνας που ακολουθεί είναι αρκετά απλός: όταν η σημασιολογία πινάκων αποτελεί μέρος της απαίτησης, στοχεύστε στο .xlsx. Εάν ένα παλαιό παραδοτέο .xls χρειάζεται πραγματικά συμπεριφορά πίνακα, η ρεαλιστική οδός είναι να υπολογίσετε το αποτέλεσμα του πίνακα στο Delphi και να γράψετε τις μεμονωμένες τιμές στα κελιά

Δύο σχετικά αναγνώσματα σε αυτόν τον ιστότοπο: το καθορισμένα ονόματα και τύποι μεταξύ φύλλων καλύπτει την επίλυση ονομάτων που εκτελεί η μηχανή, και το άρθρο εξαγωγή CSV και TSV περιγράφει λεπτομερώς τη συμπεριφορά εξαγωγής που καθιστά απαραίτητο τον ρητό υπολογισμό. Η πλήρης αναφορά της μηχανής, συμπεριλαμβανομένου του υποστηριζόμενου συνόλου συναρτήσεων, παρέχεται με το HotXLS Component