Καθορισμένα ονόματα και τύποι μεταξύ φύλλων στο Delphi με το HotXLS

Ένα defined name είναι μια ετικέτα που αντιπροσωπεύει μια σταθερά, ένα εύρος κελιών, ή μια έκφραση τύπου, αποθηκευμένη μία φορά στο βιβλίο εργασίας και αναφερόμενη συμβολικά όπου κι αν χρειάζεται. Γράψτε TaxRate σε έναν τύπο και η μηχανή το επιλύει σε ό,τι κρατά ο ορισμός του ονόματος, είτε αυτό είναι η κυριολεκτική τιμή 0.08 είτε το εύρος Data!$A$2:$D$100. Μια αναφορά μεταξύ φύλλων είναι η ορθογώνια ιδέα: το Data!D2 φτάνει σε ένα κελί σε άλλο φύλλο προσδιορίζοντας τη διεύθυνση με ένα όνομα φύλλου. Συνδυάστε τα δύο και ένα φύλλο περίληψης μπορεί να αθροίσει ένα φύλλο λεπτομερειών μέσω ενός ονόματος που δεν αναφέρει ποτέ μια κυριολεκτική διεύθυνση, κάτι που είναι ακριβώς αυτό που θέλετε σε ένα βιβλίο εργασίας που συναρμολογεί μια γεννήτρια και ελέγχει αργότερα ένας λογιστής

Το HotXLS, η εγγενής βιβλιοθήκη Delphi της losLab για αρχεία XLS και XLSX, εκθέτει τον πίνακα ονομάτων και των δύο μορφών με πρόσβαση δημιουργίας, εύρεσης, και διαγραφής, συν μια μηχανή τύπων που επιλύει ονόματα και αναφορές μεταξύ φύλλων εντός της διεργασίας. Οι δύο μορφές κρατούν ξεχωριστές ιεραρχίες κλάσεων, και οι διαφορές ανάμεσα στα APIs ονομάτων τους είναι το σημείο που σκοντάφτει κώδικας που μεταφέρεται από τη μία στην άλλη

Δύο αποθήκες ονομάτων που δεν μοιράζονται διεπαφή

Στην πλευρά XLS, η TXLSWorkbook.GetNames επιστρέφει μια συλλογή IXLSNames της οποίας η υπερφόρτωση Add(Name, RefersTo, Visible) γράφει ένα όνομα μέσα στον πίνακα ονομάτων BIFF. Οι μεμονωμένες καταχωρίσεις επιστρέφουν ως αντικείμενα IXLSName που κουβαλούν Name, RefersTo, ένα επιλυμένο RefersToRange, και μια μέθοδο Delete. Στην πλευρά XLSX, η TXLSXWorkbook.DefinedNames είναι μια συλλογή TXLSXDefinedNames με Add, FindByName, και DeleteByName

Οι συμβάσεις αναζήτησης αποκλίνουν με τρόπο που αναδύεται κατά τη μεταφορά και όχι κατά τη μεταγλώττιση. Η προεπιλεγμένη ιδιότητα Item της συλλογής XLS δέχεται ένα Variant, οπότε και τα δύο Names[0] και Names['TaxRate'] επιλύονται πάνω της. Η συλλογή XLSX δεν έχει τέτοια προεπιλεγμένη ιδιότητα· καλείτε την FindByName('TaxRate'), η οποία επιστρέφει nil όταν το όνομα απουσιάζει. Κώδικας γραμμένος για τη μία πρόσοψη μεταγλωττίζεται έναντι της άλλης μόνο κατά τύχη, και η αποτυχία τείνει να εμφανίζεται ως πρόσβαση nil κατά τον χρόνο εκτέλεσης αντί για μια κόκκινη κυματιστή γραμμή στο IDE

Η εμβέλεια είναι η πρώτη απόφαση, όχι μια σημαία που προσθέτετε αργότερα

Ένα defined name είναι είτε σε εμβέλεια βιβλίου εργασίας, ορατό σε τύπους σε κάθε φύλλο, είτε σε εμβέλεια φύλλου, ορατό μόνο σε τύπους στο φύλλο που το κατέχει. Στο API του XLSX η διάκριση είναι μία μόνο προαιρετική παράμετρος. Η DefinedNames.Add(AName, AFormula) δημιουργεί ένα όνομα σε επίπεδο βιβλίου εργασίας, ενώ η Add(AName, AFormula, ASheetIndex) το δεσμεύει σε ένα φύλλο. Διαβάζοντάς το πίσω, η TXLSXDefinedName.SheetIndex επιστρέφει -1 για εμβέλεια βιβλίου εργασίας και τον δείκτη φύλλου που ξεκινά από το μηδέν σε κάθε άλλη περίπτωση

Η εμβέλεια λειτουργεί ταυτόχρονα ως η πολιτική σύγκρουσής σας, και αυτός είναι ο λόγος να τη διευθετήσετε πριν γράψετε το πρώτο όνομα. Το Excel επιτρέπει ένα τοπικό στο φύλλο Total σε κάθε φύλλο συν ένα Total σε επίπεδο βιβλίου εργασίας, και ένας τύπος σε ένα δεδομένο φύλλο επιλύει πρώτα το τοπικό. Τα δημιουργημένα βιβλία εργασίας πρέπει να στηρίζονται σε αυτό σκόπιμα. Επιχειρηματικές παραδοχές που καταναλώνουν πολλά φύλλα, όπως φορολογικοί συντελεστές, συναλλαγματικές ισοτιμίες, και η περίοδος αναφοράς, ανήκουν σε εμβέλεια βιβλίου εργασίας. Βοηθητικά εύρη που αναφέρονται μόνο από τους τύπους ενός φύλλου είναι ασφαλέστερα σε εμβέλεια φύλλου, όπου τίποτα δεν μπορεί να τα επισκιάσει και δεν μπορούν να επισκιάσουν τίποτα

var
  Book: TXLSXWorkbook;
  Data, Summary: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Data := Book.Sheets.Add('Data');
    Summary := Book.Sheets.Add('Summary');
    // ... γέμισε το Data!A2:D100 με γραμμές λεπτομερειών ...

    Book.DefinedNames.Add('TaxRate', '0.08');                // εμβέλεια βιβλίου εργασίας, μια σταθερά
    Book.DefinedNames.Add('DataBlock', 'Data!$A$2:$D$100');  // εμβέλεια βιβλίου εργασίας, ένα εύρος
    Book.DefinedNames.Add('LocalNote', 'Summary!$B$1', 1);   // εμβέλεια μόνο στον δείκτη φύλλου 1

    // οι τύποι XLSX δεν παίρνουν αρχικό '='
    Summary.Cells[2, 2].Formula := 'SUM(Data!D2:D100)*TaxRate';
    Book.SaveAs('model.xlsx');
  finally
    Book.Free;
  end;
end;

Ένα defined name δεν χρειάζεται να δείχνει σε ένα εύρος. Το TaxRate παραπάνω αναφέρεται στη γυμνή σταθερά 0.08, και αυτός είναι ο καθαρότερος τρόπος να δημοσιεύσετε μια επιχειρηματική παραδοχή. Εμφανίζεται μία φορά στο Name Manager του Excel, κάθε τύπος αναφέρεται σε αυτό συμβολικά, και η αλλαγή συντελεστή του επόμενου τριμήνου είναι μια επεξεργασία μίας γραμμής στη γεννήτρια αντί για μια αναζήτηση σε δεκατέσσερις συναρμολογημένες συμβολοσειρές τύπου

Το σύμβολο ίσον που ανήκει μόνο στη μία πλευρά

Το κανάλι εισαγωγής τύπων είναι εκεί όπου σπάει πιο συχνά ο μεταφερμένος κώδικας, επειδή οι δύο προσόψεις διαφωνούν για το σύμβολο ίσον. Τα κελιά XLS δέχονται τύπους μέσω της Value με αρχικό =. Τα κελιά XLSX έχουν μια αποκλειστική ιδιότητα Formula που δέχεται την έκφραση χωρίς το πρόθεμα. Γράψτε '=SUM(A1:A10)' μέσα στην TXLSXCell.Formula και το σύμβολο ίσον γίνεται μέρος του αποθηκευμένου κειμένου έκφρασης αντί για δείκτη, και το αρχείο δεν θα συμπεριφερθεί με τον τρόπο που συμπεριφέρθηκε η ίδια συμβολοσειρά στην πλευρά XLS

var
  Book: IXLSWorkbook;   // μετρημένο ως διεπαφή: μην κάνετε Free
  Names: IXLSNames;
begin
  Book := TXLSWorkbook.Create;
  // υποθέστε ότι ένα φύλλο με όνομα 'Data' ήδη κρατά τις γραμμές λεπτομερειών
  Names := Book.GetNames;
  Names.Add('TaxRate', '0.08');
  Names.Add('Helper', 'Data!$A$2:$A$100', False);  // False = κρυμμένο από το Name Manager

  // οι τύποι XLS περνούν μέσα από την Value, με το πρόθεμα '='
  Book.Sheets[1].Cells.Item[2, 2].Value := '=SUM(Data!A2:A100)*TaxRate';
  Book.SaveAs('model.xls');
end;

Αυτό το απόσπασμα δείχνει δύο ακόμη ιδιοτροπίες της πλευράς XLS. Η συλλογή φύλλων ξεκινά από το ένα, οπότε το Sheets[1] είναι το πρώτο φύλλο, σε αντίθεση με το Sheets[0] του XLSX που ξεκινά από το μηδέν. Και η τρίτη παράμετρος της Add δημιουργεί ένα κρυμμένο όνομα: παρόν στο αρχείο και χρησιμοποιήσιμο από τύπους, αλλά αόρατο στο Name Manager του Excel. Τα κρυμμένα ονόματα είναι το σωστό όχημα για εσωτερική υδραυλική της γεννήτριας που οι τελικοί χρήστες δεν πρέπει ποτέ να επεξεργαστούν ή να διαγράψουν κατά λάθος

Αναφορές μεταξύ φύλλων, και τι συμβαίνει όταν μετακινούνται γραμμές

Και οι δύο μηχανές τύπων δέχονται την τυπική σύνταξη μεταξύ φύλλων. Απλά ονόματα φύλλων προσδιορίζουν απευθείας όπως το Data!A1· ένα όνομα με κενά ή σημεία στίξης χρειάζεται μονά εισαγωγικά, όπως στο 'Sheet With Space'!A1. Μέσα στο κείμενο RefersTo ενός ονόματος, χρησιμοποιήστε απόλυτες αναφορές όπως το Data!$A$2:$D$100 σχεδόν πάντα. Μια σχετική αναφορά μέσα σε ένα defined name επιλύεται σχετικά με το κελί που το χρησιμοποιεί, κάτι που είναι ένα σκόπιμο χαρακτηριστικό του Excel και μια αξιόπιστη πηγή σύγχυσης όταν ενεργοποιείται κατά λάθος

Οι δομικές επεξεργασίες είναι εκεί όπου η λογιστική μεταξύ φύλλων δικαιολογεί το κόστος της, και η πλευρά XLSX κρατά τα ονόματα συνεπή σε αυτές. Οι InsertRows και DeleteRows μετατοπίζουν τα εύρη defined names μαζί με κελιά, συγχωνεύσεις, υπερσυνδέσμους, και άγκυρες γραφημάτων, οπότε ένα όνομα που δείχνει στο Data!$A$2:$D$100 εξακολουθεί να καλύπτει το μπλοκ δεδομένων αφού η γεννήτρια ανοίξει ένα κενό από πάνω του. Οι τύποι έρχονται με μία τεκμηριωμένη επιφύλαξη: η εισαγωγή γραμμών προσαρμόζει μόνο τις αναφορές που στοχεύουν το φύλλο που επεξεργάζεται. Ένας τύπος στο Summary που αναφέρεται στο Data!D2:D100 ξαναγράφεται όταν μπαίνουν γραμμές στο Data, που είναι η περίπτωση που συνήθως θέλετε. Επαληθεύστε το αντί να το υποθέσετε, επειδή η μηχανή θα σας το πει φθηνά:

// η μηχανή υπολογισμού επιλύει ονόματα και αναφορές μεταξύ φύλλων εντός της διεργασίας
V := Book.Calculate('SUM(Data!D2:D100)*TaxRate');
if VarIsNumeric(V) then
  Log('net total checks out: ' + FloatToStr(V));

Η Calculate αποτιμά μια αυθαίρετη έκφραση έναντι της τρέχουσας κατάστασης του βιβλίου εργασίας χωρίς να αποθηκεύει τίποτα, κάτι που την κάνει το φυσικό primitive επιβεβαίωσης για τεστ γεννήτριας. Υπολογίστε το αναμενόμενο συγκεντρωτικό από τα δεδομένα πηγής σε Pascal, αποτιμήστε τον δικό του τύπο του βιβλίου εργασίας, και συγκρίνετε τα δύο. Το άρθρο για τη μηχανή τύπων καλύπτει τι αποτιμά η μηχανή, πότε, και πώς να την επεκτείνετε με προσαρμοσμένες συναρτήσεις

Τα ονόματα _xlnm που κατέχει το επίπεδο ιδιοτήτων

Ανοίξτε τον πίνακα ονομάτων ενός δημιουργημένου αρχείου σε έναν επιθεωρητή χαμηλού επιπέδου και θα βρείτε καταχωρίσεις που δεν γράψατε ποτέ: _xlnm.Print_Area, _xlnm.Print_Titles, και τα συγγενικά τους. Έτσι αποθηκεύει το OOXML (ECMA-376 / ISO 29500) περιοχές εκτύπωσης και επαναλαμβανόμενες γραμμές τίτλου, ως defined names με δεσμευμένα αναγνωριστικά. Το HotXLS τα διαχειρίζεται μέσω αποκλειστικών ιδιοτήτων φύλλου εργασίας, οπότε ο ορισμός των PrintArea ή PrintTitleRows γράφει την αντίστοιχη καταχώριση _xlnm.* για εσάς

Η παγίδα είναι το να φτάνετε μέσα σε εκείνον τον δεσμευμένο χώρο ονομάτων με το χέρι. Προσθέστε μια καταχώριση _xlnm.Print_Area μέσω της DefinedNames.Add ενώ ταυτόχρονα ορίζετε την ιδιότητα PrintArea και το βιβλίο εργασίας κουβαλά δύο αντικρουόμενους ορισμούς για ένα δεσμευμένο όνομα, μια κατάσταση που το Excel επιλύει με τρόπους στους οποίους κανένα προϊόν δεν πρέπει να στηρίζεται. Αντιμετωπίστε κάθε αναγνωριστικό που ξεκινά με _xlnm. ως ανήκον στο επίπεδο ιδιοτήτων. Για να επιθεωρήσετε τη ρύθμιση εκτύπωσης, διαβάστε τις ιδιότητες, όχι τον πίνακα ονομάτων. Το άρθρο για προστασία και ρύθμιση σελίδας καλύπτει τις ιδιότητες περιοχής εκτύπωσης εντός πλαισίου

Δύο όρια που αξίζει να γνωρίζετε πριν δεσμευτείτε σε έναν σχεδιασμό

Τα defined names δεν ταξιδεύουν μαζί μέσα από τη γέφυρα ευκολίας XLS-προς-XLSX. Η SaveXLSWorkbookAsXLSX αντιγράφει περιεχόμενο κελιού και βασική μορφοποίηση, και ο πίνακας ονομάτων δεν βρίσκεται στην τεκμηριωμένη λίστα αντιγραφής της, οπότε ένα βιβλίο εργασίας που στηριζόταν στα ονόματά του τα χάνει στη διάβαση. Ξαναδημιουργήστε τα ονόματα μέσω της DefinedNames.Add μετά τη μετατροπή. Αυτό το βήμα είναι λιγότερο κουραστικό απ' όσο ακούγεται, επειδή σας δίνει μια στιγμή να κανονικοποιήσετε τις εμβέλειές τους αντί να μεταφέρετε ό,τι τυχαία είχε το αρχείο XLS

Το άλλο όριο είναι η απόκλιση ανάμεσα σε συμβολοσειρές τύπου και ονόματα φύλλων. Το Excel ξαναγράφει τις αναφορές φύλλων μέσα σε τύπους και ονόματα κατά τη διάρκεια μιας διαδραστικής μετονομασίας, οπότε αρχεία που επεξεργάζεται ένας χρήστης στο Excel παραμένουν συνεπή μόνα τους. Η έκθεση βρίσκεται στην πλευρά της γεννήτριας: όταν ο κώδικας Pascal συναρμολογεί συμβολοσειρές τύπου από ένα κυριολεκτικό όνομα φύλλου, η μετονομασία του φύλλου σε ένα σημείο και η λησμονιά στο άλλο παράγει μια αναφορά σε ένα φύλλο που δεν υπάρχει πια. Κρατήστε το όνομα φύλλου σε μία μόνο σταθερά Delphi και τροφοδοτήστε την και στην Sheets.Add και στη συναρμολόγηση των τύπων σας, και τα δύο δεν μπορούν ποτέ να διαφωνήσουν. Αυτό είναι το ίδιο ένστικτο που υποστηρίζει την ονομασία των κελιών εξόδου μιας αναφοράς αντί για σκληρά κωδικοποιημένες διευθύνσεις: ένα template του οποίου το κελί συνόλου είναι επώνυμο συνεχίζει να λειτουργεί αφού ένας σχεδιαστής εισάγει τρεις γραμμές από πάνω του, ενώ μια γεννήτρια που γράφει σε ένα κυριολεκτικό B17 προσγειώνει σιωπηλά τον αριθμό της στο λάθος σημείο. Το άρθρο για δημιουργία αναφορών βάσει template χτίζει ακριβώς πάνω σε αυτό το μοτίβο

Το πλήρες API defined names και για τις δύο μορφές, μαζί με την αναφορά της μηχανής τύπων, έρχεται με το HotXLS Delphi Component