Το HotXLS, το εξάρτημα Excel για Delphi και C++Builder, διαχωρίζει αυτόματα έναν κανόνα μορφοποίησης υπό συνθήκη ή επικύρωσης δεδομένων σε δύο ή περισσότερα ξεχωριστά αντικείμενα κανόνων όποτε μια εισαγωγή ή διαγραφή γραμμής ή στήλης κόβει το καλυπτόμενο εύρος του κανόνα σε κομμάτια που χρειάζονται διαφορετικές σχετικές αγκύρωσεις τύπου, έπειτα αναθέτει εκ νέου σε κάθε κανόνα μορφοποίησης υπό συνθήκη έναν φρέσκο, μοναδικό αριθμό προτεραιότητας. Η συμπεριφορά κυκλοφόρησε στην έκδοση 2.196 της μηχανής XLSX και εκτελείται αυτόματα, χωρίς ρύθμιση για εξαίρεση. Η αιτία είναι στενή αλλά συνηθισμένη: ένας κανόνας cellIs ή έκφρασης του οποίου ο τύπος διαβάζει ένα κελί σχετικά με το δικό του εύρος, σε ένα φύλλο εργασίας που αργότερα λαμβάνει μια γραμμή εισαγόμενη ή αφαιρούμενη κάπου στη μέση εκείνου του ακριβούς εύρους
Τα περισσότερα κείμενα για την αυτοματοποίηση του Excel σταματούν στο πρόβλημα κειμένου τύπου: μετατόπιση των αριθμών γραμμής και στήλης μέσα σε κάθε SUM() και κάθε VLOOKUP() ώστε οι αναφορές να εξακολουθούν να δείχνουν στα σωστά κελιά. Εκείνο το μισό της ιστορίας είναι πραγματικό, και καλύπτεται στο συνοδευτικό άρθρο για το πώς το HotXLS ξαναγράφει αναφορές τύπου όταν μετακινούνται γραμμές και στήλες, αλλά μια μορφοποίηση υπό συνθήκη ή ένας κανόνας επικύρωσης δεδομένων δεν είναι απλώς ένας τύπος που κάθεται σε ένα κελί. Ζευγαρώνει έναν τύπο με ένα εύρος, sqref σε όρους ECMA-376, και τα δύο πρέπει να κινούνται μαζί. Όταν μια δομική επεξεργασία τεμαχίζει εκείνο το εύρος σε δύο κομμάτια που θα χρειάζονταν δύο διαφορετικές σχετικές μετατοπίσεις για να παραμείνουν σωστά, το να κρατάτε ένα αντικείμενο κανόνα με μία συμβολοσειρά τύπου παύει να είναι επιλογή, και το να προσποιείστε το αντίθετο είναι πώς ένας κανόνας επισήμανσης αρχίζει σιωπηλά να συγκρίνει τις λάθος γραμμές
Γιατί η εισαγωγή μιας γραμμής διαχωρίζει έναν κανόνα μορφοποίησης υπό συνθήκη αντί απλώς να τον μετακινεί;
Μια μορφοποίηση υπό συνθήκη ή κανόνας επικύρωσης δεδομένων κρατά ακριβώς έναν τύπο για ολόκληρο το εύρος του, αξιολογούμενο σχετικά με ένα μοναδικό κελί άγκυρας, οπότε μόλις μια επεξεργασία αναγκάσει δύο μέρη εκείνου του εύρους να χρειάζονται δύο διαφορετικές σχετικές μετατοπίσεις, ένας τύπος δεν μπορεί πλέον να περιγράψει σωστά και τα δύο μέρη. Το ECMA-376 εκφράζει την κάλυψη ενός κανόνα ως το χαρακτηριστικό sqref στο στοιχείο conditionalFormatting ή dataValidation, και το Excel αξιολογεί τα Formula1 και Formula2 σαν το κείμενο να είχε πληκτρολογηθεί στο επάνω-αριστερό κελί εκείνου του sqref και να γεμίστηκε σε όλο το υπόλοιπό του, με τον ίδιο τρόπο που ένας συνηθισμένος σχετικός τύπος γεμίζει προς τα κάτω μια στήλη. Φανταστείτε μια επισήμανση απόκλισης πάνω από τα B2:B50 που σημαδεύει οποιοδήποτε πραγματικό στοιχείο υπερβαίνει τον προϋπολογισμό του, χτισμένη ως κανόνας cellIs του οποίου το Formula1 είναι το κείμενο C2, που σημαίνει σύγκριση του κελιού B της τρέχουσας γραμμής έναντι του κελιού C της ίδιας γραμμής
Idx := Sheet.AddConditionalFormat('B2:B50', xlsxCfOpGreaterThan, 'C2');
Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($FFFFC7CE);
Sheet.InsertRows(25, 1); // one blank separator row, starting at old row 25Εισάγετε εκείνη τη μία γραμμή διαχωρισμού στην παλιά γραμμή 25 και οι γραμμές πάνω από το σημείο εισαγωγής δεν μετακινούνται, οπότε το μερίδιό τους από τον κανόνα εξακολουθεί να διαβάζει σωστά το Formula1 ως C2. Οι γραμμές που ήταν 25 έως 50 γλιστρούν προς τα κάτω στις 26 έως 51, και για αυτές το C2 είναι πλέον εντελώς λάθος κελί, αφού η γραμμή 26 χρειάζεται να συγκρίνει έναντι του C26, όχι έναντι ενός στοιχείου προϋπολογισμού δύο ντουζίνες γραμμές πάνω από αυτήν
Πώς αποφασίζει το HotXLS αν ένας κανόνας χρειάζεται διαχωρισμό
Το HotXLS δημιουργεί επιπλέον αντικείμενα κανόνων μόνο όταν η γεωμετρία πράγματι το απαιτεί: μια εσωτερική ρουτίνα, το XlsxBuildShiftedRuleParts, διατρέχει κάθε ασύνδετη περιοχή στο sqref του κανόνα, υπολογίζει ποιο ήταν το κελί άγκυρας εκείνης της περιοχής πριν την επεξεργασία και τι γίνεται μετά, και ελέγχει αν κάθε προκύπτον κομμάτι θα χρειαζόταν την ίδια διόρθωση σχετικής μετατόπισης. Αν όλα τα κομμάτια συμφωνούν, ένας κανόνας επιβιώνει, με το sqref του ξαναχτισμένο ως η ένωση των μετατοπισμένων κομματιών και τον τύπο του επαναβασισμένο μία φορά. Ένας γνήσιος διαχωρισμός συμβαίνει μόνο όταν τα κομμάτια διαφωνούν, ακριβώς η περίπτωση B2:B50 παραπάνω, όπου το πάνω μπλοκ κρατά την αρχική του άγκυρα και το κάτω μπλοκ χρειάζεται μια νέα
Η επαναβασιοποίηση του τύπου ενός κομματιού είναι μια κίνηση δύο βημάτων που επαναχρησιμοποιεί μηχανισμό που το HotXLS ήδη φέρει για ομάδες κοινόχρηστων τύπων OOXML: πρώτα ο τύπος μεταφράζεται σαν να είχε αρχικά αγκυρωθεί στο δικό του επάνω-αριστερό κελί εκείνου του κομματιού, χρησιμοποιώντας την ίδια αριθμητική σχετικής μετατόπισης που επεκτείνει έναν κοινόχρηστο τύπο σε όλο το εύρος του, έπειτα το αποτέλεσμα περνά μέσα από τον ίδιο σαρωτή μετατόπισης γραμμών-και-στηλών που ξαναγράφει συνηθισμένους τύπους φύλλου εργασίας. Έτσι το Formula1 πηγαίνει από C2 σε C26 σε δύο κινήσεις αντί για μία χειρόγραφη ειδική περίπτωση: μεταφράστε το C2 προς τα εμπρός κατά 23 γραμμές για να πάρετε C25, σαν ο κανόνας να είχε ξεκινήσει πάντα εκεί, έπειτα αφήστε τη συνηθισμένη μετατόπιση στη γραμμή 25 να το σπρώξει στο C26. Κάθε άλλη ιδιότητα, χρώμα γεμίσματος, stop-if-true, ο ίδιος ο τελεστής, μεταφέρεται αμετάβλητη στο νέο αντικείμενο κανόνα, οπότε και τα δύο μισά συνεχίζουν να ζωγραφίζουν κελιά με το χρώμα που πάντα ζωγράφιζαν
// ConditionalFormats now holds two rules instead of one:
// B2:B25 Formula1 = 'C2' (rows above the insert)
// B26:B51 Formula1 = 'C26' (rows that shifted down)Διαχωρίζονται οι ράβδοι δεδομένων και τα σύνολα εικονιδίων με τον ίδιο τρόπο όπως οι κανόνες cellIs;
Όχι: το HotXLS διαμερίζει μόνο τα είδη κανόνα των οποίων η ορθότητα πράγματι εξαρτάται από έναν σχετικό τύπο ανά περιοχή, τις συγκρίσεις cellIs και τους κανόνες έκφρασης, και αφήνει κάθε άλλο είδος μορφοποίησης υπό συνθήκη ως ένα μοναδικό αντικείμενο κανόνα του οποίου το sqref απλώς μεγαλώνει για να καλύψει τα μετατοπισμένα κομμάτια ως ένωση πολλαπλών περιοχών. Εσωτερικά ο κλάδος είναι ένας απλός έλεγχος Kind, cf.Kind in [cfkCellIs, cfkExpression], τίποτα πιο εξωτικό από αυτό. Οι ράβδοι δεδομένων, οι κλίμακες δύο και τριών χρωμάτων, τα σύνολα εικονιδίων, οι κατατάξεις πάνω και κάτω, και οι ανιχνευτές διπλότυπων, κενών, και σφαλμάτων φέρουν ένα φορτίο, ένα χρώμα ράβδου, ένα σύνολο σημείων κλίμακας, μια οικογένεια εικονιδίων, που περιγράφει όλο το καλυπτόμενο εύρος ταυτόχρονα αντί για μια σχετική σύγκριση ανά κελί, οπότε ο διαχωρισμός τους σε πολλά αντικείμενα κανόνων με προτεραιότητα δεν θα αγόραζε καμία ορθότητα και θα προσέθετε μόνο κανόνες προς διαχείριση. Όταν μια επεξεργασία διαιρεί το εύρος τους, το HotXLS επανασυνδυάζει τα κομμάτια σε έναν κανόνα με ένα sqref πολλαπλών περιοχών και επαναγκυρώνει το φορτίο ως μία μονάδα αντί να κλωνοποιεί ένα νέο αντικείμενο κανόνα ανά κομμάτι. Η διάκριση ευθυγραμμίζεται με την ταξινομία ειδών κανόνα στο άρθρο βασικών στοιχείων μορφοποίησης υπό συνθήκη και εμπλουτισμένου κειμένου: οι ράβδοι δεδομένων, οι κλίμακες χρώματος, και τα σύνολα εικονιδίων ήδη ξεχωρίζουν από τους κανόνες cellIs αγνοώντας εντελώς την ιδιότητα Style, και τώρα αποδεικνύεται ότι ξεχωρίζουν και από την επανα-αγκύρωση ανά περιοχή για τον ίδιο υποκείμενο λόγο
Γιατί αλλάζουν οι προτεραιότητες κανόνων μετά από μια δομική επεξεργασία;
Οι προτεραιότητες αλλάζουν επειδή κάθε κλώνος ξεκινά κρατώντας ακριβώς την ίδια τιμή προτεραιότητας με τον κανόνα από τον οποίο διαχωρίστηκε, και το HotXLS εκτελεί ένα πέρασμα κανονικοποίησης μετά που επιλύει τα προκύπτοντα διπλότυπα σε μια καθαρή, χωρίς κενά διάταξη αντί να αφήνει δύο κανόνες ισόβαθμους στην ίδια θέση. Μια δεύτερη εσωτερική ρουτίνα, το XlsxNormalizeConditionalFormatPriorities, παίρνει την τρέχουσα προτεραιότητα κάθε μορφοποίησης υπό συνθήκη, επιστρέφει στη θέση εκείνου του κανόνα στη συλλογή για οποιονδήποτε κανόνα που ποτέ δεν είχε ρητά ορισμένη μία, ταξινομεί ολόκληρη τη λίστα σταθερά ώστε οι ισοβαθμίες να κρατούν την αρχική σχετική τους σειρά, και επαναριθμεί το ταξινομημένο αποτέλεσμα σε μια πυκνή ακολουθία 1, 2, 3 χωρίς κενά και χωρίς επαναλήψεις. Το HotXLS το εκτελεί μία φορά πριν ξεκινήσει μια μετατόπιση, οπότε η κλωνοποίηση ξεκινά από μια καθαρή βάση, και ξανά μετά από κάθε διαχωρισμό και αφού αφαιρεθεί κάθε αδειασμένος κανόνας, οπότε το αρχείο που αποθηκεύεται ποτέ δεν έχει δύο καταχωρίσεις κανόνα που διεκδικούν την ίδια προτεραιότητα. Αυτό έχει σημασία αν ακολουθήσατε τη συμβουλή στο άρθρο βασικών στοιχείων μορφοποίησης υπό συνθήκη να αφήνετε κενά μεταξύ τιμών προτεραιότητας ώστε ένας μεταγενέστερος κανόνας να μπορεί να χωθεί χωρίς επαναρίθμηση των υπολοίπων: τα κενά επιβιώνουν μέχρι την επόμενη επεξεργασία γραμμής ή στήλης που αγγίζει εκείνο το φύλλο εργασίας, έπειτα καταρρέουν, επειδή η κανονικοποίηση εγγυάται μόνο μοναδικότητα και σταθερή σειρά, όχι ότι το αρχικό σας σχήμα αρίθμησης επιστρέφει αμετάβλητο
Οι κανόνες επικύρωσης δεδομένων διαχωρίζονται επίσης, χωρίς προτεραιότητα προς επαναρίθμηση
Οι κανόνες επικύρωσης δεδομένων περνούν από την ίδια λογική διαμερισμού εύρους όπως οι μορφοποιήσεις υπό συνθήκη cellIs και έκφρασης, και σε αντίθεση με τη μορφοποίηση υπό συνθήκη, κάθε τύπος επικύρωσης ακολουθεί εκείνη τη διαδρομή ομοιόμορφα: το HotXLS δεν έχει ξεχωριστή οικογένεια μη-τύπου για επικύρωση δεδομένων με τον τρόπο που έχουν οι ράβδοι δεδομένων και τα σύνολα εικονιδίων για τη μορφοποίηση υπό συνθήκη, οπότε ένας απλός κανόνας λίστας ή ακέραιου αριθμού διαμερίζεται από την ταυτόσημη ρουτίνα που χειρίζεται έναν σχετικό προσαρμοσμένο τύπο. Αυτό που διαφέρει είναι η προτεραιότητα: το ECMA-376 δεν δίνει καθόλου χαρακτηριστικό priority στο στοιχείο dataValidation, οπότε δεν υπάρχει βήμα επαναρίθμησης για επικυρώσεις όπως υπάρχει για μορφοποιήσεις υπό συνθήκη. Φανταστείτε μια επικύρωση προσαρμοσμένου τύπου που εμποδίζει το πραγματικό ποσό κάθε γραμμής από το να υπερβεί τον δικό της προϋπολογισμό στη στήλη δίπλα του
Sheet.AddCustomValidation('D2:D400', 'D2<=C2');
Sheet.DeleteRows(150, 5); // remove five rows out of the validated range
// DataValidations now holds two rules instead of one:
// D2:D149 Formula1 = 'D2<=C2' (rows above the deletion)
// D150:D395 Formula1 = 'D150<=C150' (rows that shifted up)Αυτό έχει σημασία για τον ίδιο λόγο που το άρθρο βασικών στοιχείων επικύρωσης δεδομένων προειδοποιεί έναντι της προσάρτησης ενός κανόνα πριν οριστικοποιηθεί ο αριθμός γραμμών: μια επικύρωση καλύπτει μόνο τα κυριολεκτικά κελιά που της δώσατε, και μια μεταγενέστερη δομική επεξεργασία μπορεί να αφήσει δύο ή περισσότερους κανόνες να κάνουν τη δουλειά που έκανε κάποτε ένας. Τίποτα δεν σπάει λειτουργικά: κάθε κελί στο αρχικό εύρος εξακολουθεί να επικυρώνεται από κάτι, αλλά κώδικας που υποθέτει μία καταχώριση DataValidations ανά στήλη θα αρχίσει να δεικτοδοτεί λάθος μετά την πρώτη επεξεργασία που το αγγίζει. Υπάρχει ένα σκληρό ανώτατο όριο στο πόσο μακριά μπορεί να φτάσει αυτό: αν ο διαχωρισμός θα έσπρωχνε ένα φύλλο εργασίας πέρα από 65.534 κανόνες επικύρωσης δεδομένων, το HotXLS εγείρει εξαίρεση αντί να γράψει ένα αρχείο που το Excel θα απέρριπτε σιωπηλά, κάτι που είναι η βιβλιοθήκη να αρνείται να κατασκευάσει ένα κατεστραμμένο βιβλίο εργασίας αντί για ένα όριο που η συνηθισμένη χρήση είναι πιθανό να φτάσει
Τι να ελέγξετε μετά από μαζική εισαγωγή ή διαγραφή
Τα δύο πράγματα που αξίζει να επαληθεύσετε αφού ένα σενάριο εκτελέσει μια παρτίδα επεξεργασιών γραμμών ή στηλών πάνω σε ένα φύλλο γεμάτο μορφοποιήσεις υπό συνθήκη και επικυρώσεις είναι ο συνολικός αριθμός κανόνων και η σειρά προτεραιότητας, αφού και τα δύο μπορούν να παρεκκλίνουν με τρόπους που είναι εύκολο να χαθούν σε αναθεώρηση κώδικα και προφανείς τη στιγμή που κάποιος ανοίγει το Manage Rules στο Excel. Μία επεξεργασία σπάνια κάνει πολλή ζημιά: μια μεμονωμένη εισαγωγή στη μέση ενός κανόνα cellIs παράγει το πολύ δύο αντικείμενα κανόνων εκεί όπου υπήρχε ένα. Ο κίνδυνος πολλαπλασιάζεται όταν μια ρουτίνα δημιουργίας αναφορών εισάγει γραμμές μία τη φορά σε βρόχο πάνω σε ένα φύλλο που ήδη φέρει πολλούς κανόνες αγκυρωμένους σε τύπο: κάθε πέρασμα μπορεί να ξαναδιαχωρίσει κανόνες που ένα προηγούμενο πέρασμα ήδη διαχώρισε, και πέντε αρχικοί κανόνες cellIs μπορούν να καταλήξουν αρκετές φορές περισσότερα θραύσματα χαμηλής αξίας που καλύπτουν λωρίδες του αρχικού εύρους. Η ομαδοποίηση δομικών επεξεργασιών, η εισαγωγή ολόκληρου του νέου μπλοκ σε μία κλήση αντί για μία γραμμή τη φορά, κρατά τον αριθμό κανόνων δεμένο με τον αριθμό των γνησίως ξεχωριστών αγκυρών αντί με τον αριθμό των επεξεργασιών που εκτελέστηκαν
Ο διαμερισμός κανόνων και η κανονικοποίηση προτεραιότητας διατίθενται ως τυπική συμπεριφορά της μηχανής XLSX στο εξάρτημα Excel HotXLS για Delphi για Delphi και C++Builder· η σελίδα προϊόντος φέρει την πλήρη αναφορά API επεξεργασίας φύλλου εργασίας, συμπεριλαμβανομένων των μεθόδων μορφοποίησης υπό συνθήκη και επικύρωσης δεδομένων που περιγράφονται εδώ