Αν το SUBTOTAL(109, ...) και το SUBTOTAL(9, ...) επιστρέφουν τον ίδιο αριθμό σε ένα βιβλίο εργασίας που περιέχει κρυφές γραμμές, το ένα από τα δύο είναι λάθος. Το HotXLS, το native στοιχείο υπολογιστικού φύλλου Excel για Delphi και C++Builder, συμπεριφερόταν ακριβώς έτσι μέχρι την έκδοση 2.197.0, γιατί η μηχανή υπολογισμού του δεν είχε τρόπο να ρωτήσει ένα φύλλο εργασίας αν μια δεδομένη γραμμή ήταν κρυφή
Το σύμπτωμα σπάνια φτάνει ως αναφορά bug για κωδικούς τύπων. Φτάνει ως αναντιστοιχία: μια εργασία batch στον server υπολογίζει ένα σύνολο, ένας χρήστης ανοίγει το ίδιο αρχείο στο Excel με εφαρμοσμένο φίλτρο, και οι δύο αριθμοί διαφέρουν κατά ό,τι έτυχε να αθροίζουν οι φιλτραρισμένες γραμμές. Κανείς δεν υποψιάζεται τη συνάρτηση συνάθροισης, γιατί το string του τύπου στο κελί είναι πανομοιότυπο και στα δύο σημεία. Η διαφορά βρίσκεται εξ ολοκλήρου σε αυτό που επιτρεπόταν να δει ο αξιολογητής
Γιατί το SUBTOTAL 109 περιλαμβάνει κρυφές γραμμές;
Επειδή στους περισσότερους σχεδιασμούς μηχανών, το επίπεδο που αξιολογεί έναν τύπο ποτέ δεν μαθαίνει για την ορατότητα γραμμής. Το HotXLS ήταν μια σχολική περίπτωση: η μηχανή υπολογισμού στο lxCalc.pas έφτανε στις τιμές κελιών μέσω ενός μοναδικού callback TXLSGetValue που απαντά με μια τιμή για μια τριάδα (φύλλο, γραμμή, στήλη) και τίποτα άλλο. Η ορατότητα είναι ένα χαρακτηριστικό παρουσίασης αποθηκευμένο στην εγγραφή γραμμής, και κανένα κομμάτι αυτής της εγγραφής δεν ταξίδευε κάτω στην αλυσίδα κλήσεων. Η μηχανή είχε επομένως ένα μονοπάτι συνάθροισης, και τα δύο μισά του πίνακα αριθμών συνάρτησης του SUBTOTAL επιλύονταν σε αυτό. Αυτό δεν είναι μια κατηγορία ελαττώματος τύπου σφάλματος στρογγυλοποίησης: είναι ολόκληρος ο λόγος που υπάρχει το δεύτερο μισό του πίνακα. Το ECMA-376 Part 1, δημοσιευμένο ως ISO/IEC 29500-1, ορίζει το SUBTOTAL στους ορισμούς συναρτήσεων τύπων (§18.17.7) με ένα πρώτο όρισμα που επιλέγει τόσο την εσωτερική συνάθροιση όσο και την πολιτική κρυφών γραμμών. Οι κωδικοί 1 έως 11 αντιστοιχούν στα AVERAGE, COUNT, COUNTA, MAX, MIN, PRODUCT, STDEV, STDEVP, SUM, VAR, και VARP ενώ περιλαμβάνουν τιμές σε χειροκίνητα κρυμμένες γραμμές. Οι κωδικοί 101 έως 111 επιλέγουν τις ίδιες έντεκα συναθροίσεις και τις αποκλείουν. Ένας χρήστης που πληκτρολογεί 109 αντί για 9 κάνει μια σκόπιμη δήλωση για κρυφά δεδομένα, και μια μηχανή που καταρρίπτει τη διάκριση ανατρέπει σιωπηρά αυτή τη δήλωση
Σε τι αντιστοιχούν οι αριθμοί συνάρτησης μέσα στη μηχανή
Το HotXLS επιλύει το πρώτο όρισμα του SUBTOTAL στην CalcSubtotalFunc, που κανονικοποιεί τους κωδικούς 101 έως 111 στα ίδια αναγνωριστικά εσωτερικής συνάρτησης με τους κωδικούς 1 έως 11 και μετά διανέμει στην ίδια τη συνάθροιση. Το μεγαλύτερο μέρος της οικογένειας ρέει μέσα από τον αυξητικό συσσωρευτή ExcelSum, αυτόν που χειρίζεται τα SUM, COUNT, COUNTA, MIN, MAX, και AVERAGE. Πέντε από αυτά δεν μπορούν: τα STDEV, VAR, STDEVP, VARP, και PRODUCT χρειάζονται ένα πέρασμα κλειστής μορφής πάνω στα δεδομένα, οπότε η CalcSubtotalFunc δρομολογεί τους εσωτερικούς κωδικούς 12, 46, 193, 194, και 183 σε έναν ξεχωριστό reducer, τον SubtotalReduceVariance. Αυτός ο διαχωρισμός είναι το πρώτο πράγμα που αξίζει να χαρτογραφήσεις πριν αγγίξεις οτιδήποτε, γιατί δύο ανεξάρτητα μονοπάτια συνάθροισης σημαίνουν δύο ανεξάρτητους βρόχους διάτρεξης κελιών, και μια διόρθωση που εφαρμόζεται μόνο στο ένα από αυτά παράγει τη χειρότερη δυνατή έκβαση: το SUBTOTAL(109, ...) σέβεται το φίλτρο ενώ το SUBTOTAL(107, ...) στο ίδιο εύρος δεν το σέβεται. Καταμετρώντας τους βρόχους στο HotXLS βρέθηκαν έξι από αυτούς μόλις συμπεριλήφθηκε το AGGREGATE, απλωμένοι σε αξιολόγηση εύρους, απλή συλλογή εύρους, και τρεις ξεχωριστούς reducers
Γιατί ένα προσωρινό πεδίο αντί για έξι νέες υπογραφές;
Γιατί το να περάσεις μια νέα παράμετρο μέσα από έξι συναρτήσεις διάτρεξης κελιών, συν οτιδήποτε τις καλεί, είναι μια ευρεία αλλαγή σε ένα κρίσιμο μονοπάτι κώδικα για χάρη μιας μόνο boolean. Το HotXLS είχε ήδη ένα προηγούμενο για την εναλλακτική: ένα εφήμερο πεδίο στον υπολογιστή, στο ίδιο πνεύμα με το προσωρινό πεδίο που χρησιμοποιεί η GetRangeInfo για να καταγράψει πότε μια αναφορά 3D επιλύθηκε σε εξωτερικό βιβλίο εργασίας. Η έκδοση 2.197.0 πρόσθεσε ένα δεύτερο. Η μηχανή απέκτησε έναν τύπο callback, TXLSIsRowHidden, δηλωμένο ως συνάρτηση των (SheetIndex, row) που επιστρέφει Boolean, αποθηκευμένο στο FIsRowHidden, συν μια εφήμερη σημαία FIgnoreHiddenRows. Η σημαία οπλίζεται στην είσοδο της CalcSubtotalFunc όταν ο κωδικός συνάρτησης πέφτει στο 101 έως 111, και στην είσοδο της CalcAggregateFunc για τους κωδικούς επιλογής AGGREGATE που επιλέγουν αποκλεισμό κρυφών γραμμών. Κάθε βρόχος διάτρεξης κελιών μετά την εξετάζει και παραλείπει μία γραμμή όταν είναι θετική, προσθέτοντας μία μόνο γραμμή ο καθένας
// The shape repeated in all six cell-walk loops
for sh := s1 to s2 do
for rr := r1 to r2 do
begin
if FIgnoreHiddenRows and Assigned(FIsRowHidden) and FIsRowHidden(sh, rr) then
Continue;
for cc := c1 to c2 do
begin
// ... fold Cells[rr, cc] into the accumulator ...
end;
end;Δύο λεπτομέρειες στον κώδικα οπλισμού φέρουν την ορθότητα ολόκληρου του σχήματος. Η σημαία αποθηκεύεται και αποκαθίσταται αντί απλώς να τίθεται και να καθαρίζεται, γιατί ένα όρισμα SUBTOTAL μπορεί να περιέχει μια έκφραση που τρέχει τη δική της αξιολόγηση ενώ η εξωτερική συνάθροιση βρίσκεται ακόμα στη στοίβα, και αυτή η εμφωλευμένη εργασία δεν πρέπει να κληρονομήσει ή να καταστρέψει την εξωτερική πύλη. Και η αποκατάσταση κατοικεί σε ένα μπλοκ finally, γιατί η CalcSubtotalFunc έχει αρκετές πρώιμες εξόδους για κωδικούς σφάλματος· μια σημαία που παραμένει οπλισμένη μετά από μια επιστροφή σφάλματος θα διέφθειρε σιωπηρά τον επόμενο άσχετο τύπο στη σειρά επαναϋπολογισμού
prevIgnoreHidden := FIgnoreHiddenRows;
if (fnCode >= 101) and (fnCode <= 111) and Assigned(FIsRowHidden) then
FIgnoreHiddenRows := True;
try
// aggregate over Item.Child[2] .. Item.Child[ChildCount]
// every Exit path below is covered by the finally
finally
FIgnoreHiddenRows := prevIgnoreHidden;
end;Ο έλεγχος Assigned είναι αυτό που κρατά την αλλαγή συμβατή. Το HotXLS επέκτεινε τον κατασκευαστή του υπολογιστή με μια τρίτη παράμετρο με προεπιλογή nil, οπότε κάθε κώδικας που χτίζει έναν TXLSCalculator με την παλιά κλήση δύο ορισμάτων εξακολουθεί να μεταγλωττίζεται και εξακολουθεί να παίρνει την παλαιά συμπεριφορά συμπερίληψης κρυφών. Τίποτα στο υπάρχον API δεν άλλαξε σχήμα
Από πού προέρχεται πραγματικά το bit κρυφής γραμμής;
Από το φύλλο εργασίας, μέσω δύο διαφορετικών πηγών, γιατί το HotXLS φέρει δύο μηχανές βιβλίου εργασίας. Η παλαιά πλευρά BIFF απαντά από την TXLSRowInfoList.GetHidden, προσπελάσιμη μέσω της TXLSWorkbook.GetRowHidden. Η πλευρά OOXML απαντά από την TXLSXWorksheet.GetRowHidden, προσπελάσιμη μέσω της TXLSXWorkbook.GetCalcRowHidden. Και οι δύο είναι καλωδιωμένες στον υπολογιστή κατά την κατασκευή, δίπλα στο callback τιμής κελιού που καθρεφτίζουν. Οι συμβάσεις γραμμών είναι εκεί που αυτού του είδους η γέφυρα συνήθως πάει στραβά, οπότε αξίζει να δηλωθούν ρητά. Ο υπολογιστής δίνει στο callback μια γραμμή με βάση 0, ταιριάζοντας με τις συντεταγμένες που ήδη χρησιμοποιεί η TXLSGetValue. Το φύλλο εργασίας XLSX ευρετηριάζει τον χάρτη κρυφής γραμμής του με αριθμό γραμμής με βάση το 1, ακριβώς όπως το Excel αριθμεί τις γραμμές, που είναι επίσης αυτό που εκθέτει η δημόσια ιδιότητα RowHidden[ARow]. Η γέφυρα XLSX επομένως προσθέτει ένα πριν από την αναζήτηση, και η γέφυρα BIFF δεν το κάνει, γιατί η TXLSRowInfoList έχει ήδη βάση 0. Και οι δύο γέφυρες αντιμετωπίζουν έναν δείκτη φύλλου ή γραμμή εκτός έγκυρου εύρους ως ορατό, οπότε ένα ερώτημα εκτός ορίων υποβαθμίζεται στην παλιά απάντηση συμπερίληψης κρυφών αντί να ρίχνει δεδομένα
Τι αλλάζει για φιλτραρισμένα βιβλία εργασίας
Αυτή είναι η περίπτωση που παράγει τα δελτία υποστήριξης. Η εφαρμογή ενός AutoFilter στο HotXLS μέσω της ApplyAutoFilter αξιολογεί τα κριτήρια στήλης και κρύβει κάθε γραμμή δεδομένων που δεν ταιριάζει, ακριβώς όπως κάνει το Excel όταν ένας χρήστης κάνει κλικ σε ένα αναπτυσσόμενο φίλτρο. Πριν την v2.197.0 αυτές οι κρυφές γραμμές ήταν αόρατες στον χρήστη και πλήρως ορατές στη μηχανή υπολογισμού, οπότε ένα SUBTOTAL(109, ...) στην πλευρά του server ανέφερε το μη φιλτραρισμένο σύνολο. Τώρα η ίδια κλήση αναφέρει το φιλτραρισμένο
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
VisibleRows: Integer;
begin
Book := TXLSXWorkbook.Create;
try
Book.Open('orders.xlsx');
Sheet := Book.Sheets[0];
Sheet.SetAutoFilter('A1:E500');
Sheet.AddAutoFilterColumn(3, xlsxAfOpGreaterOrEqual, '1000');
VisibleRows := Sheet.ApplyAutoFilter; // hides the non-matching rows
Sheet.Cells[501, 4].Formula := '=SUBTOTAL(109,D2:D500)';
Book.Recalculate;
// The cell value now agrees with what Excel shows for the same filter,
// and VisibleRows tells you how many rows fed into it
Book.SaveAs('orders-filtered.xlsx');
finally
Book.Free;
end;
end;Η χειροκίνητη απόκρυψη λειτουργεί με τον ίδιο τρόπο, αφού το RowHidden[ARow] := True είναι η ίδια κατάσταση που γράφει το φίλτρο. Αυτή η ισοδυναμία είναι σκόπιμη στο Excel και τώρα ισχύει και στο HotXLS. Μία συνέπεια αξίζει μια σημείωση σε όποια τεκμηρίωση συνοδεύει τα βιβλία εργασίας που παράγεις: ένα σύνολο υπολογισμένο με τον κωδικό 109 είναι ένας αριθμός εξαρτώμενος από την προβολή, οπότε ένας παραλήπτης που καθαρίζει το φίλτρο τον αλλάζει. Όταν μια αναφορά πρέπει να δηλώνει ένα σταθερό ποσό ανεξάρτητα από το τι κάνει ο αναγνώστης στην προβολή, ο κωδικός 9 είναι η σωστή επιλογή και ήταν πάντα. Τα φίλτρα, η επικύρωση και οι πίνακες καλύπτονται μαζί στο άρθρο για την επικύρωση δεδομένων, το AutoFilter, και τους πίνακες. Επειδή η απόκρυψη γραμμών δεν αγγίζει κανέναν τύπο, δεν λερώνει από μόνη της τον γράφο εξαρτήσεων, κάτι που αξίζει να γνωρίζεις αν βασίζεσαι στον αυξητικό επαναϋπολογισμό πάνω στον λερωμένο υπογράφο για να κρατάς αποκρίσιμα τα μεγάλα βιβλία εργασίας
Οι κωδικοί επιλογής AGGREGATE και ένα όριο που παραμένει ανοιχτό
Το AGGREGATE είναι SUBTOTAL με ένα δεύτερο όρισμα πολιτικής, και το HotXLS το χειρίζεται στην CalcAggregateFunc. Το όρισμα επιλογής κωδικοποιεί ανεξάρτητους διακόπτες: αν εμφωλευμένες κλήσεις SUBTOTAL και AGGREGATE μέσα στο εύρος παραλείπονται, αν τιμές σε κρυφές γραμμές παραλείπονται, και αν τιμές σφάλματος καταστέλλονται αντί να διαδίδονται. Το HotXLS οπλίζει την κοινή πύλη κρυφής γραμμής για τους κωδικούς επιλογής 2, 3, 6, και 7, και καταστέλλει τιμές σφάλματος για τους κωδικούς επιλογής 4 έως 7. Το όρισμα αριθμού συνάρτησης μετά επιλέγει τη συνάθροιση ακριβώς όπως το SUBTOTAL, συμπεριλαμβανομένης της δρομολόγησης διακύμανσης, τυπικής απόκλισης, και γινομένου μέσω των δικών τους reducers. Ένα τεκμηριωμένο κενό παραμένει, και είναι καλύτερο να δηλωθεί εδώ παρά να ανακαλυφθεί στην παραγωγή: η σημασιολογία αγνόησης εμφωλευμένου SUBTOTAL που συνδέεται με τους χαμηλούς κωδικούς επιλογής δεν είναι υλοποιημένη στο HotXLS. Η ανίχνευση ενός εμφωλευμένου SUBTOTAL μέσα σε ένα εύρος αναφοράς απαιτεί τη σήμανση της κατάστασης αναδρομής του αξιολογητή ώστε μια εσωτερική συνάθροιση να μπορεί να ανακοινώσει τον εαυτό της στην εξωτερική, που είναι μια μεγαλύτερη αλλαγή από την πύλη κρυφής γραμμής. Στην πράξη η έκθεση είναι μικρή, γιατί τα πραγματικά βιβλία εργασίας σχεδόν πάντα τοποθετούν τύπους SUBTOTAL έξω από τα εύρη που συναθροίζουν άλλοι τύποι SUBTOTAL. Αν ο δικός σου generator χτίζει επικαλυπτόμενα εύρη συνάθροισης, μην βασίζεσαι στους χαμηλούς κωδικούς επιλογής για την αποδιπλασιοποίησή τους
Ο φύλακας arity που κυκλοφόρησε μαζί του
Η έκδοση 2.197.0 επίσης έκλεισε ένα κενό επικύρωσης στον ίδιο dispatcher, και ο λόγος σχεδιασμού είναι ο ίδιος που παρακίνησε το προσωρινό πεδίο: βάλε τον έλεγχο εκεί όπου μπορεί να γραφτεί μία φορά. Περίπου 280 σώματα ενσωματωμένων συναρτήσεων επαλήθευαν το καθένα τον δικό του αριθμό ορισμάτων έναντι του Item.ChildCount, κάτι που δεν άφηνε συνεπές όριο για την περίπτωση των πάρα πολλών ορισμάτων. Μια κλήση όπως =SIN(1,2) έφτανε σε ένα σώμα συνάρτησης που εξέταζε το πρώτο του όρισμα, αγνοούσε το πλεόνασμα, και επέστρεφε έναν ευλογοφανή αριθμό εκεί όπου το Excel επιστρέφει #VALUE!. Το HotXLS ήδη αποθήκευε τη δηλωμένη arity κάθε ενσωματωμένης συνάρτησης στο μητρώο συναρτήσεών του, εκτεθειμένη ως THashFunc.ArgsCnt με το -1 να σημαίνει μια μεταβλητή συνάρτηση όπως SUM, IF, ή CONCAT. Η έκδοση 2.197.0 το προώθησε μέσω μιας νέας ιδιότητας TXLSFormula.FuncArgsCntByPtg και πρόσθεσε μία πύλη στην κορυφή της GetValueItemFunc, του κύριου dispatcher
lDeclaredArgs := FFormula.FuncArgsCntByPtg[Item.IntValue];
if lDeclaredArgs >= 0 then
begin
lProvidedArgs := Item.ChildCount - 1; // Child[0] is the function node
if lProvidedArgs > lDeclaredArgs then
begin
Result := lxErrorValue; // =SIN(1,2) now yields #VALUE!
Exit;
end;
end;Ο φύλακας απορρίπτει πάρα πολλά ορίσματα και σκόπιμα δεν λέει τίποτα για πολύ λίγα. Η παράλειψη ενός προαιρετικού τελικού ορίσματος είναι νόμιμη στο Excel για το VLOOKUP, το SUBSTITUTE, και μια μακριά λίστα άλλων, οπότε ένας συμμετρικός έλεγχος θα είχε σπάσει σωστούς τύπους για να πιάσει λανθασμένους. Άγνωστα αναγνωριστικά αναφέρονται ως μεταβλητά και παρακάμπτουν εντελώς την πύλη, κάτι που κρατά τις συναρτήσεις ορισμένες από τον χρήστη μακριά από το δρόμο της· αν καταχωρείς τις δικές σου συναρτήσεις, η συμπεριφορά που περιγράφεται στον οδηγό για τη μηχανή τύπων και τις προσαρμοσμένες συναρτήσεις δεν επηρεάζεται. Η συγκεντρωτική διαχείριση της περίπτωσης πολύ λίγων ορισμάτων είναι ξεχωριστή δουλειά, γιατί καθένα από αυτά τα 280 σώματα έχει τη δική του σημασιολογία κωδικού σφάλματος και πρέπει να εξεταστούν ένα-ένα αντί να υποτεθεί
Η μηχανή υπολογισμού που περιγράφεται εδώ, και οι δύο προσόψεις βιβλίου εργασίας, και τα APIs AutoFilter και ορατότητας γραμμής που την τροφοδοτούν είναι μέρος του στοιχείου υπολογιστικού φύλλου HotXLS Delphi, που διατίθεται με πλήρη πηγαίο κώδικα για Delphi και C++Builder και δεν απαιτεί εγκατάσταση Excel στο μηχάνημα που το τρέχει