Το HotXLS προσαρμόζει αυτόματα τις αναφορές τύπων όταν εισάγετε ή διαγράφετε γραμμές ή στήλες σε ένα φύλλο εργασίας XLSX. Οι μέθοδοι της μηχανής InsertRows, DeleteRows, InsertCols και DeleteCols επαναγράφουν κάθε εναπομείναντα τύπο έτσι ώστε οι αναφορές του A1 — σχετικές, απόλυτες και εύρους — να συνεχίζουν να δείχνουν στα ίδια δεδομένα μετά τη δομική επεξεργασία, και οι αναφορές σε ένα διαγραμμένο μπλοκ να γίνονται #REF!, ταιριάζοντας με τη συμπεριφορά του Excel
Το σφάλμα που αποτρέπει αυτό είναι ένα από τα πιο αθόρυβα κατά τη δημιουργία αναφορών. Μια γεννήτρια γράφει καθημερινά νούμερα στα C2:C9 με το =SUM(C2:C9) από κάτω, και στη συνέχεια ένα μεταγενέστερο βήμα εισάγει μια γραμμή κεφαλίδας στην κορυφή. Εάν η μηχανή μετακινεί μόνο τις τιμές των κελιών και αφήνει το κείμενο του τύπου ως έχει, αυτό το SUM εξακολουθεί να διαβάζει C2:C9 ενώ τα δεδομένα ζουν τώρα στα C3:C10 — οπότε το σύνολο παραλείπει σιωπηλά την τελευταία ημέρα και διπλομετρά μια κεφαλίδα. Τίποτα δεν προκαλεί εξαίρεση, το αρχείο ανοίγει μια χαρά και ο αριθμός είναι απλώς λάθος. Πριν από την έκδοση 2.160, η μηχανή XLSX του HotXLS άφηνε τους τύπους ανέπαφους κατά τις δομικές επεξεργασίες· από την 2.160 η επανεγγραφή είναι αυτόματη και δεν υπάρχει σημαία για ρύθμιση
Τι συμβαίνει στους τύπους όταν εισάγετε μια γραμμή στο Excel;
Ο κανόνας του Excel είναι ότι οι αναφορές ακολουθούν τα δεδομένα, όχι τις διευθύνσεις. Όταν εισάγεται μια γραμμή, κάθε αναφορά της οποίας ο δείκτης γραμμής βρίσκεται στο σημείο εισαγωγής ή κάτω από αυτό μετακινείται προς τα κάτω κατά τον αριθμό των εισαγόμενων γραμμών· οι αναφορές που βρίσκονται εξ ολοκλήρου πάνω από το σημείο εισαγωγής παραμένουν ανέπαφες. Η διαγραφή γραμμών εκτελεί τον ίδιο κανόνα αντίστροφα: οι αναφορές κάτω από το διαγραμμένο μπλοκ γλιστρούν προς τα πάνω, και οι αναφορές μέσα στο ίδιο το διαγραμμένο μπλοκ γίνονται #REF! επειδή τα κελιά που ονόμαζαν δεν υπάρχουν πλέον. Οι στήλες συμπεριφέρονται πανομοιότυπα στον άλλο άξονα. Μια βιβλιοθήκη υπολογιστικών φύλλων που θέλει το αποτέλεσμά της να επιβιώσει από την επαφή με τους χρήστες του Excel πρέπει να αναπαράγει ακριβώς αυτούς τους μηχανισμούς, επειδή οι χρήστες σκέφτονται τους τύπους τους με αυτούς τους όρους χωρίς καν να το σκέφτονται
Το κομμάτι που εκπλήσσει τους προγραμματιστές είναι ότι μετακινούνται και οι απόλυτες αναφορές. Οι άγκυρες $ στο $B$2 ελέγχουν τι συμβαίνει όταν ένας τύπος αντιγράφεται ή συμπληρώνεται σε ένα άλλο κελί — δεν κάνουν τίποτα κατά τις δομικές επεξεργασίες. Εισάγετε μια γραμμή πάνω από τη γραμμή 2 και το Excel ξαναγράφει το $B$2 σε $B$3, με τα σύμβολα του δολαρίου ανέπαφα, επειδή η τιμή από την οποία εξαρτάται ο τύπος μετακινήθηκε φυσικά στη γραμμή 3. Μια μηχανή που θα μετατόπιζε μόνο τις σχετικές αναφορές θα κατέστρεφε ακριβώς τους τύπους που οι άνθρωποι αγκυροβολούν πιο συνειδητά. Το HotXLS μετατοπίζει και τις δύο μορφές και διατηρεί τους δείκτες $ στο επανεγγραμμένο κείμενο
Πώς το HotXLS μετατοπίζει αυτόματα τις αναφορές τύπων;
Και οι τέσσερις μέθοδοι δομικής επεξεργασίας στο TXLSXWorksheet αναθέτουν την εργασία σε μια ενιαία μηχανή γεωμετρίας: τη ShiftSheetGeometry(RowFrom, RowDelta, ColFrom, ColDelta). Η InsertRows(BeforeRow, Count) την καλεί με θετικό δέλτα γραμμής, η DeleteRows(StartRow, Count) με αρνητικό, και οι μέθοδοι στηλών κάνουν το ίδιο στον άξονα των στηλών. Η διαδικασία μετατοπίζει πρώτα τα ίδια τα κελιά — απορρίπτοντας οποιοδήποτε κελί πέφτει μέσα σε ένα διαγραμμένο μπλοκ — και στη συνέχεια διατρέχει κάθε εναπομείναν κελί τύπου και περνά το κείμενό του από το XlsxAdjustFormulaRowColRefs, έναν σαρωτή που βρίσκει αναφορές τύπου A1 και ξαναγράφει τα στοιχεία γραμμής και στήλης τους έναντι της μετατόπισης. Το ίδιο πέρασμα μετατοπίζει συγχωνευμένα εύρη, υπερσυνδέσμους, σχόλια, εικόνες, γραφήματα, μορφοποιήσεις υπό όρους, επικυρώσεις δεδομένων και εύρη πινάκων, έτσι ώστε ολόκληρο το φύλλο να μετακινείται ως μία μονάδα
// Layout before the edit:
// C2..C9 daily figures
// C10 =SUM(C2:C9)
Sheet.InsertRows(2, 1); // one blank row before row 2
// Layout after the call:
// C3..C10 daily figures
// C11 =SUM(C3:C10) -- the range moved with the dataΑξίζει να γνωρίζετε μερικές λεπτομέρειες του σαρωτή. Το XlsxAdjustFormulaRowColRefs αναγνωρίζει αναφορές μεμονωμένων κελιών και στις τέσσερις μορφές αγκύρωσης (A1, $A1, A$1, $A$1) και εύρη δύο γωνιών όπως το A1:B3, προσαρμόζοντας κάθε τελικό σημείο ανεξάρτητα. Οι τύποι των οποίων το κείμενο ξεκινά ήδη με # — ένας δείκτης σφάλματος από προηγούμενη επεξεργασία — παρακάμπτονται αντί να σαρωθούν ξανά. Και η InsertCols προσθέτει μια δική της λεπτομέρεια ισοτιμίας με το Excel: οι νεοεισαχθείσες στήλες κληρονομούν το πλάτος του αριστερού τους γείτονα, που είναι αυτό που κάνει η εντολή Εισαγωγή Στηλών Φύλλου του Excel
Πότε μια διαγραμμένη αναφορά γίνεται #REF!;
Ο κανόνας μετατόπισης για έναν μεμονωμένο δείκτη γραμμής ή στήλης έχει τρία αποτελέσματα. Ένας δείκτης πριν από το σημείο επεξεργασίας παραμένει αμετάβλητος. Ένας δείκτης στο σημείο επεξεργασίας ή μετά από αυτό μετακινείται κατά το δέλτα. Και υπό διαγραφή, ένας δείκτης που πέφτει μέσα στο διαγραμμένο μπλοκ δεν έχει ουσιαστική νέα τιμή — το κελί έχει χαθεί — οπότε ο σαρωτής ξαναγράφει ολόκληρη την αναφορά ως #REF!. Για μια αναφορά εύρους, και τα δύο τελικά σημεία περνούν από τον ίδιο κανόνα, και εάν οποιοδήποτε τελικό σημείο προσγειωθεί μέσα στο διαγραμμένο μπλοκ, η αναφορά ξαναγράφεται ως #REF! αντί να αφεθεί μισο-έγκυρη
// A12 holds =A4+A6+A10
Sheet.DeleteRows(5, 3); // delete rows 5..7
// The formula, now in A9, reads =A4+#REF!+A7
// A4 : above the deleted block, unchanged
// A6 : inside rows 5..7, gone -> #REF!
// A10 : below the block, slides up -> A7Η παραγωγή ενός ηχηρού #REF! αντί της σιωπηλής επαναστοχοθέτησης είναι η σωστή ανταλλαγή, και είναι αυτή που κάνει το Excel. Ένας τύπος που δείχνει σε ένα γειτονικό κελί μετά τη διαγραφή της πραγματικής του εισόδου θα επέστρεφε έναν εύλογο αριθμό· το #REF! διαδίδεται μέσω εξαρτώμενων τύπων και εμφανίζεται στην πρώτη δοκιμή ελέγχου (smoke test). Η ίδια μετατροπή ισχύει και στον άξονα των στηλών
// E1 holds =B1*$C$1
Sheet.DeleteCols(3, 1); // remove column C
// The formula, now in D1, reads =B1*#REF!
// The absolute anchor did not protect $C$1 -- the cell itself is goneΠοιες μορφές αναφοράς δεν επανεγγράφονται;
Ο σαρωτής στοχεύει σε αναφορές A1 στο ίδιο φύλλο με ρητό σχήμα γράμματος στήλης συν αριθμού γραμμής, και αξίζει να είμαστε ακριβείς σχετικά με το τι μένει εκτός αυτού. Οι αναφορές ολόκληρης στήλης όπως A:A και οι αναφορές ολόκληρης γραμμής όπως 1:1 στερούνται ενός από τα δύο στοιχεία, οπότε ο σαρωτής τις αφήνει όπως γράφτηκαν. Οι δομημένες αναφορές πινάκων (Table1[Amount]) περνούν επίσης ανέπαφες. Η επανεγγραφή λειτουργεί επίσης αυστηρά σε σημειογραφία A1 — εάν ο κώδικάς σας δημιουργεί τύπους σε στυλ R1C1, μετατρέψτε τους σε A1 πριν από μια δομική επεξεργασία, όπως περιγράφεται στο συνοδευτικό άρθρο για τη σημειογραφία τύπων R1C1 στο Delphi
Οι κατασκευές μεταξύ φύλλων και επιπέδου βιβλίου εργασίας αντιμετωπίζονται με ξεχωριστά περάσματα και όχι από τον σαρωτή κειμένου κελιών. Μετά την προσαρμογή του επεξεργασμένου φύλλου, η ShiftSheetGeometry διαδίδει την ίδια αλλαγή γεωμετρίας σε τύπους σε άλλα φύλλα που αναφέρονται στο επεξεργασμένο φύλλο, σε εύρη σειρών γραφημάτων, σε εσωτερικούς στόχους υπερσυνδέσμων και σε καθορισμένα ονόματα. Τα καθορισμένα ονόματα λαμβάνουν περαιτέρω προστασία σε επίπεδο κύκλου ζωής φύλλου: από την έκδοση 2.150, η διαγραφή ενός φύλλου εργασίας ξαναγράφει κάθε προσδιοριστή SheetN! μέσα στον τύπο ενός καθορισμένου ονόματος σε #REF!, και η μετονομασία ενός φύλλου ξαναγράφει τον προσδιοριστή στο νέο όνομα, έτσι ώστε τα ονόματα να μην δείχνουν ποτέ σε φύλλο που δεν υπάρχει πλέον. Το πώς ταιριάζουν τα ονόματα και οι τύποι μεταξύ φύλλων καλύπτεται στο άρθρο για τα καθορισμένα ονόματα και τους τύπους μεταξύ φύλλων
Επανυπολογισμός μετά τη μετατόπιση
Η προσαρμογή αναφοράς ξαναγράφει το κείμενο του τύπου· δεν επανυπολογίζει τα αποτελέσματα. Μετά από μια δομική επεξεργασία, οι προσωρινά αποθηκευμένες τιμές που είναι αποθηκευμένες δίπλα στους τύπους περιγράφουν την παλιά γεωμετρία, επομένως η αξιόπιστη σειρά είναι: εκτελέστε πρώτα όλες τις διαγραφές και εισαγωγές, στη συνέχεια ενεργοποιήστε τον επανυπολογισμό μία φορά και μετά αποθηκεύστε. Η εκτέλεση της μετατόπισης πριν από τον επανυπολογισμό διατηρεί επίσης ειλικρινείς τις πληροφορίες εξάρτησης — κάθε επανεγγραμμένη αναφορά ονομάζει την πραγματική της προηγούμενη τιμή, που είναι ακριβώς αυτό που χρειάζονται η μηχανή σταδιακού επανυπολογισμού και το γράφημα εξάρτησής της για να επανυπολογίσουν το ελάχιστο σύνολο των επηρεαζόμενων κελιών. Εάν ένας επανεγγραμμένος τύπος περιέχει τώρα #REF!, ο επανυπολογισμός εμφανίζει την τιμή σφάλματος αμέσως αντί να αφήσει έναν παλιό αριθμό στο αρχείο
Η προσαρμογή αναφοράς τύπου παρέχεται ως τυπική συμπεριφορά της μηχανής XLSX στο HotXLS Delphi Excel Component για Delphi και C++Builder· η σελίδα προϊόντος μεταφέρει την πλήρη αναφορά API επεξεργασίας φύλλου εργασίας, συμπεριλαμβανομένων των μεθόδων εισαγωγής και διαγραφής που εμφανίζονται εδώ