Αξιολόγηση Μορφοποίησης Υπό Όρους Excel σε Delphi με HotXLS

Το HotXLS είναι ένα εγγενές component υπολογιστικού φύλλου για Delphi και C++Builder, και από την έκδοση 2.209.0 μπορεί να απαντήσει στο ερώτημα που το Excel κανονικά κρατά για τον εαυτό του: για αυτό το ακριβές κελί, ποιοι κανόνες μορφοποίησης υπό όρους ενεργοποιούνται, και σε τι γέμισμα, γραμματοσειρά, data bar ή εικονίδιο καταλήγουν. Αυτή η απάντηση είναι αυτό που χρειάζεστε τη στιγμή που η έξοδός σας είναι μια αναφορά HTML, ένα PDF, ή ένα πλέγμα που ζωγραφίζετε μόνοι σας

Αυτό είναι διαφορετικό πρόβλημα από τη δημιουργία κανόνων. Δύο προηγούμενες σημειώσεις καλύπτουν την πλευρά σύνταξης: η μορφοποίηση υπό όρους και στυλ rich text ασχολείται με την προσάρτηση κανόνων και διαφορικών μορφών σε ένα εύρος, και η κατάτμηση αγκυρωμένων μορφοποιήσεων υπό όρους ασχολείται με το τι συμβαίνει σε ένα εύρος κανόνα όταν εισάγονται ή διαγράφονται γραμμές και στήλες. Και οι δύο είναι δομικές. Αυτή εδώ αφορά σημασιολογία: δεδομένου ενός βιβλίου εργασίας που ήδη φέρει κανόνες, υπολογίστε την επισήμανση

Γιατί η μορφή αρχείου δεν σας λέει ποια κελιά ανάβουν

Η σύντομη απάντηση είναι ότι τα ECMA-376 και ISO 29500-1 ορίζουν αποθήκευση, όχι αξιολόγηση. Ένα στοιχείο conditionalFormatting (§18.3.1.18) φέρει ένα sqref και μια λίστα παιδιών cfRule (§18.3.1.10), και κάθε κανόνας φέρει ένα type, έναν προαιρετικό operator, μια priority, μια σημαία stopIfTrue, ένα ή δύο παιδιά formula, και για τις οπτικές οικογένειες ένα σύνολο κατωφλίων cfvo. Καθένα από αυτά περιγράφει πιστά αυτό που διαμόρφωσε ο χρήστης, και κανένα δεν είναι αλγόριθμος. Για τους μισούς τύπους κανόνων αυτό το κενό δεν έχει σημασία: το cellIs με operator="greaterThan" σημαίνει μεγαλύτερο από, και το containsText σημαίνει ότι το substring είναι παρόν. Το κενό ανοίγει στις οικογένειες συνόλευσης. Ένας κανόνας top10 με rank="10" και percent="1" πάνω σε 27 συμπληρωμένα αριθμητικά κελιά επισημαίνει πόσα κελιά; Το δύο κόμμα επτά δεν είναι αριθμός. Στρογγυλοποίηση, floor, ή ceiling — η προδιαγραφή σιωπά, και η λάθος επιλογή σημαίνει ότι το PDF σας διαφωνεί με το βιβλίο εργασίας που έχει ανοιχτό ο πελάτης δίπλα του

Κανόνες μεμονωμένου κελιού και πού σταματά το TCondFormatRule.Evaluate

Το HotXLS πήρε πρώτα το φθηνό μισό. Η TCondFormatRule.Evaluate στο lxCondFormat.pas, που προστέθηκε στο 2.199.0, απαντά αν ένας κανόνας ενεργοποιείται για ένα κελί χωρίς να ξέρει τίποτα για το υπόλοιπο εύρος. Χειρίζεται τους οκτώ τελεστές σύγκρισης BIFF πίσω από το cellIs (between, notBetween, equal, notEqual, greater, less, greaterEqual, lessEqual), κανόνες ελεύθερης μορφής expression που αξιολογούνται στο κελί ώστε οι σχετικές αναφορές να επαναβασίζονται σωστά, τα τέσσερα κατηγορήματα κειμένου, και τα κατηγορήματα κενών και σφαλμάτων. Τα κατώφλια προέρχονται από τα FFormula1 και FFormula2 επιλυμένα μέσω της TXLSCalculator.GetRangeValue στη θέση κελιού, και αντεστραμμένα όρια αντιμετατίθενται αντί να απορρίπτονται

var
  I: Integer;
  Rule: TCondFormatRule;
  Value: Variant;
begin
  Value := Sheet.Cells[Row, Col].Value;
  for I := 0 to CondFormat.RuleCount - 1 do
  begin
    Rule := CondFormat.Rule(I);
    // Single-cell verdict only. Aggregate and visual kinds answer False.
    if Rule.Evaluate(Calculator, SheetIndex, Row, Col, Value) then
      ApplyHighlight(Row, Col, Rule.Style);
  end;
end;

Το ειλικρινές μέρος αυτής της μεθόδου είναι αυτό που αρνείται να μαντέψει. Τα top10, aboveAverage, belowAverage, duplicateValues και uniqueValues επιστρέφουν False, όχι επειδή είναι δύσκολα αλλά επειδή είναι μη αποφασίσιμα από ένα κελί — καθένα από αυτά χρειάζεται μια στατιστική πάνω στο σύνολο του πεδίου. Οι τέσσερις οπτικές οικογένειες, dataBar, colorScale2, colorScale3 και iconSet, επιστρέφουν False για διαφορετικό λόγο: ποτέ δεν παράγουν καθόλου boolean, παράγουν ένα payload απόδοσης, και ένας τύπος επιστροφής Boolean είναι λάθος σχήμα για αυτές

Πώς αποφεύγει ένας αξιολογητής επιπέδου φύλλου εργασίας την επανασάρωση του φύλλου;

Υπολογίζοντας κάθε κοινή ποσότητα μία φορά, στην κατασκευή, και ποτέ ξανά. Η TXLSXConditionalFormatEvaluator στο lxHandleX.pas είναι μια αμετάβλητη στιγμιαία εικόνα για ένα φύλλο εργασίας, χτισμένη μέσω της TXLSXWorksheet.CreateConditionalFormatEvaluator, και ο συνολικός της σχεδιασμός είναι μια άμυνα έναντι της αφελούς υλοποίησης όπου κάθε ζωγραφισμένο κελί ενεργοποιεί μια πλήρη σάρωση εύρους

Τέσσερα πράγματα συμβαίνουν στον κατασκευαστή. Κάθε ξεχωριστό πολυπεριοχικό sqref αναλύεται ακριβώς μία φορά σε ένα TXlsxCfRangeSnapshot, οπότε δέκα κανόνες που μοιράζονται ένα εύρος μοιράζονται μία ανάλυση και ένα πέρασμα στατιστικών. Αυτό το πέρασμα ρέει τον μέσο όρο, την πληθυσμιακή απόκλιση, το ελάχιστο και το μέγιστο πάνω από τα συμπληρωμένα κελιά σε μία μόνο διαδρομή, και κρατά έναν διατεταγμένο αριθμητικό πίνακα μόνο όταν ένας κανόνας Top/Bottom ή εκατοστημορίου πραγματικά χρειάζεται στατιστικά διάταξης. Τα κλειδιά διπλότυπων και μοναδικών χτίζονται ασφαλή για Unicode και ταξινομούνται σε παρτίδα μία φορά αντί ανά αναζήτηση. Μετά ο άξονας γραμμών κόβεται σε ζώνες σε κάθε όριο περιοχής, ώστε η EvaluateCell να κάνει δυαδική αναζήτηση μιας ζώνης και να επισκέπτεται μόνο κανόνες των οποίων τα εύρη μπορούν ενδεχομένως να φτάσουν σε αυτή τη γραμμή

Το τέταρτο είναι αυτό που έχει τη μεγαλύτερη σημασία σε κλίμακα. Ένας σχετικός τύπος κανόνα όπως =A1>AVERAGE($A$1:$A$100) σημαίνει κάτι διαφορετικό σε κάθε κελί του πεδίου, και η προφανής υλοποίηση μεταγλωττίζει ένα φρέσκο δέντρο σύνταξης ανά κελί. Το TXlsxCfRulePlan το μεταγλωττίζει μία φορά και επαναξιολογεί το ίδιο δέντρο μέσω αντιστρέψιμων μετατοπίσεων συντεταγμένων, που διατηρεί τη συμπεριφορά αγκύρωσης του Excel χωρίς δέσμευση δέντρου σύνταξης ανά κελί. Οι κανόνες μετά στρωματοποιούνται κατά priority, και μια αντιστοίχιση σε έναν κανόνα του οποίου το StopIfTrue είναι ενεργό διακόπτει τον βρόχο, ακριβώς όπως το Excel κάνει βραχυκύκλωμα

var
  Evaluator: TXLSXConditionalFormatEvaluator;
  Res: TXLSXCfCellResult;
begin
  Evaluator := Sheet.CreateConditionalFormatEvaluator;
  try
    if Evaluator.EvaluateCell(Row, Col, Res) then
    begin
      if Res.HasFillColor then
        Canvas.Brush.Color := TColor(Res.FillColor);
      if Res.HasIcon then
        // IconIndex is zero-based inside Res.IconSetType
        DrawIcon(Res.IconSetType, Res.IconIndex, Res.IconCount);
      if Res.HasDataBar then
        // DataBarAxis and DataBarEnd are normalised to 0..1
        DrawBar(Res.DataBarAxis, Res.DataBarEnd, Res.DataBarColor);
      if not Res.ShowCellValue then
        Exit;  // showValue="0" on the rule hides the number
    end;
  finally
    Evaluator.Free;
  end;
end;

Πώς στρογγυλοποιεί στην πραγματικότητα το Excel έναν κανόνα Top 10 percent;

Κάνει floor, με ελάχιστο το ένα, και περιλαμβάνει ισοπαλίες στο σημείο αποκοπής. Αυτό δεν είναι γραμμένο πουθενά στο ISO 29500-1 — καθηλώθηκε δοκιμάζοντας το Excel 16 με χειροποίητα βιβλία εργασίας και διαβάζοντας πίσω ποια κελιά επισήμανε η εφαρμογή. Το HotXLS υλοποιεί ακριβώς αυτό: ο αριθμός κατάταξης είναι Floor(Count * Min(Rank, 100) / 100), ανεβασμένος στο 1 όταν καταλήγει στο μηδέν, περιορισμένος στον συμπληρωμένο αριθμό, και η τιμή αποκοπής μετά συγκρίνεται με >= ώστε κάθε κελί ίσο με το όριο να επισημαίνεται ακόμη κι όταν αυτό ξεπερνά τον ζητούμενο αριθμό. Είκοσι επτά τιμές και ένας κανόνας 10 τοις εκατό επισημαίνουν δύο κελιά, συν όποια περαιτέρω κελιά είναι ισόπαλα με το δεύτερο

Οι κανόνες above-average έκρυβαν μια δεύτερη ασάφεια: το aboveAverage με stdDev="1" επιλέγει κελιά μία τυπική απόκλιση πάνω από τον μέσο όρο, αλλά η δειγματική και η πληθυσμιακή απόκλιση διαφέρουν κατά τη διόρθωση Bessel και διαφωνούν ορατά σε μικρά εύρη, που είναι ακριβώς όπου χρησιμοποιείται η μορφοποίηση υπό όρους. Το Excel 16 χρησιμοποιεί την πληθυσμιακή απόκλιση, και το HotXLS ταιριάζει, με τη σημαία equalAverage να κάνει τη αυστηρή σύγκριση περιληπτική μόνο όταν καμία ζώνη απόκλισης δεν είναι σε παιχνίδι. Οι κανόνες διπλότυπων και μοναδικών ενεργοποίησαν αντ' αυτού ταυτότητα κλειδιού. Αν ένα κελί κρατά τον αριθμό 100 και άλλο κρατά το κείμενο "100", το Excel τα αντιμετωπίζει ως το ίδιο κλειδί διπλότυπου, οπότε το HotXLS κανονικοποιεί το αριθμητικό κείμενο στον αριθμητικό χώρο κλειδιών αντί να συγκρίνει ακατέργαστα strings. Τα κενά κελιά είναι η κατοπτρική περίπτωση: ένα πραγματικά κενό κελί συμμετέχει στη μέτρηση εύρους αλλά δεν στυλιζάρεται το ίδιο, οπότε τα κενά κελιά σε μια στήλη δεν ανάβουν όλα ως διπλότυπα το ένα του άλλου

Κλίμακες χρώματος και σύνολα εικονιδίων: παρεμβολή και κανόνες ορίων

Οι οπτικές οικογένειες επιλύονται σε αριθμούς έτοιμους για απόδοση αντί για booleans, και η συμπεριφορά ορίων τους καθηλώθηκε με τον ίδιο τρόπο. Για μια κλίμακα χρώματος με ρητά αριθμητικά κατώφλια, το HotXLS περιορίζει το κλάσμα θέσης στο κλειστό διάστημα μηδέν έως ένα, μετά παρεμβάλλει ανά κανάλι με περικοπή αντί για στρογγυλοποίηση — μια τιμή κάτω από το ελάχιστο στοπ παίρνει το ελάχιστο χρώμα αντί για ένα εξωτερικά προεκτεταμένο, μια κλίμακα τριών στοπ επιλέγει το ζεύγος της συγκρίνοντας με το ενδιάμεσο στοπ, και μια εκφυλισμένη κλίμακα της οποίας τα δύο άκρα φέρουν το ίδιο κατώφλι συμπτύσσεται στο ανώτερο χρώμα αντί να διαιρεί με το μηδέν. Τα σύνολα εικονιδίων χρειάστηκαν το αντίθετο είδος προσοχής, επειδή κάθε cfvo μετά το πρώτο φέρει τη δική του αυστηρότητα σύγκρισης: το HotXLS διαβάζει το ThresholdEqualsInclude ανά κατώφλι και εφαρμόζει >= ή > αναλόγως, περπατώντας προς τα πάνω ώστε το υψηλότερο ικανοποιημένο κατώφλι να κερδίζει τον δείκτη εικονιδίου. Ένα αντεστραμμένο σύνολο αναστρέφει τον επιλυμένο δείκτη αντί για τα κατώφλια, οι παρακάμψεις ανά εικονίδιο μπορούν να τραβήξουν ένα glyph από διαφορετική οικογένεια, και οποιοδήποτε άκυρο κατώφλι ματαιώνει τον κανόνα αντί να παράγει ένα ευλογοφανές λάθος εικονίδιο

Τροφοδότηση ενός πλέγματος, μιας εξαγωγής HTML και ενός PDF από ένα αποτέλεσμα

Επειδή η EvaluateCell επιστρέφει ένα πλήρως επιλυμένο TXLSXCfCellResult — διαφορικό γέμισμα και χρώμα γραμματοσειράς με απόχρωση θέματος ήδη εφαρμοσμένη, bold, italic, underline, id μορφής αριθμού, κατευθυντικές θετικές και αρνητικές εκτάσεις μπάρας, θέση άξονα, οικογένεια και δείκτη εικονιδίου — κάθε καταναλωτής διαβάζει την ίδια εγγραφή και κανένας δεν χρειάζεται να καταλαβαίνει τα εσωτερικά των κανόνων. Το HotXLS χρησιμοποιεί αυτή τη μία διαδρομή για εξαγωγή HTML, εξαγωγή PDF και τον διαδραστικό viewer, που είναι ο μόνος πρακτικός τρόπος να αποτραπεί η απόκλιση τριών renderers μεταξύ τους. Η έκδοση 2.210.0 το συνέδεσε στο TXLSWorkbookViewer, που αποθηκεύει στην cache έναν προετοιμασμένο αξιολογητή ανά ενεργό φύλλο εργασίας και τον επαναχρησιμοποιεί κατά μήκος κύλισης, επιλογής και επανασχεδίασης, απελευθερώνοντάς τον όταν αλλάζει το βιβλίο εργασίας ή το φύλλο εργασίας — η ανακατασκευή της στιγμιαίας εικόνας σε κάθε Paint θα ματαίωνε ολόκληρο τον σχεδιασμό χρόνου κατασκευής. Αυτή η cache είναι επίσης γιατί υπάρχει η TXLSWorkbookViewer.RefreshConditionalFormats: η στιγμιαία εικόνα είναι αμετάβλητη, οπότε αν τροποποιήσετε το συνδεδεμένο βιβλίο εργασίας επί τόπου τα συγκεντρωτικά στατιστικά και τα επιλυμένα κατώφλια είναι μπαγιάτικα μέχρι να την καλέσετε

// Editing behind a live viewer: the cached snapshot must be invalidated.
Sheet := Viewer.XlsxWorkbook.Sheets[1];
Sheet.Cells[5, 2].Value := 4200;         // changes mean, min, max, ranking
Viewer.RefreshConditionalFormats;        // drop evaluator, repaint

Τι δεν θα κάνει ο αξιολογητής για εσάς

Τρία όρια αξίζει να δηλωθούν καθαρά. Η κλασική μεμονωμένη TCondFormatRule.Evaluate και η επιπέδου φύλλου εργασίας TXLSXConditionalFormatEvaluator είναι διαφορετικές επιφάνειες με διαφορετικές δυνατότητες, και η μεμονωμένη σκόπιμα αρνείται τις οικογένειες συνόλευσης και οπτικές αντί να τις προσεγγίζει — αν χρειάζεστε Top/Bottom ή μια κλίμακα χρώματος, χτίστε τον αξιολογητή. Οι σχετικές χρονικές περίοδοι εξαρτώνται από το ρολόι του μηχανήματος τη στιγμή της αξιολόγησης, οπότε ένας κανόνας timePeriod αποδίδεται διαφορετικά σε ένα PDF που παράγεται σήμερα και ένα που παράγεται την επόμενη εβδομάδα, που είναι σωστή συμπεριφορά και ακόμη ένα εισιτήριο υποστήριξης που περιμένει να συμβεί αν το αρχείο σας αναμένεται να είναι σταθερό σε επίπεδο byte. Το τρίτο είναι γραμματικό παρά τεχνικό: η γραμματική τύπου μορφοποίησης υπό όρους απαγορεύει δομημένες αναφορές πίνακα, οπότε ένας κανόνας δεν μπορεί να απευθυνθεί σε μια στήλη πίνακα με όνομα όπως μπορεί ένας τύπος φύλλου εργασίας, και αυτός είναι περιορισμός της μορφής και όχι της υλοποίησης

Αν χτίζετε έξοδο αναφοράς, μια pipeline εξαγωγής ή ένα προσαρμοσμένο πλέγμα που πρέπει να συμφωνεί με το Excel κελί προς κελί, το ίδιο επιλυμένο αποτέλεσμα οδηγεί επίσης το προσαρμοσμένο πλέγμα υπολογιστικού φύλλου VCL που περιγράφεται αλλού σε αυτό το blog. Πλήρης τεκμηρίωση API, το μοντέλο κανόνων και δοκιμαστικά downloads για το HotXLS Delphi spreadsheet component είναι διαθέσιμα στη σελίδα προϊόντος