Μορφοποίηση υπό όρους, Rich Text και στυλ κελιών στο Delphi με το HotXLS

Ένας κανόνας μορφοποίησης υπό όρους στο OOXML είναι δύο ξεχωριστά πράγματα που φορούν ένα όνομα. Η συνθήκη (μια σύγκριση, ένας τύπος, μια αντιστοίχιση κειμένου) αποφασίζει ποια κελιά πληρούν τις προϋποθέσεις. Η εμφάνιση (μια εγγραφή διαφορικής μορφοποίησης, dxf στους όρους του ECMA-376) αποφασίζει πώς φαίνονται αυτά τα κελιά. Το παράθυρο διαλόγου του Excel κρύβει τη ραφή αναγκάζοντάς σας να συμπληρώσετε και τα δύο ταυτόχρονα. Το HotXLS δεν το κάνει αυτό. Δημιουργήστε έναν κανόνα cellIs από τη Delphi και παραλείψτε το στιλ, και ο κανόνας είναι έγκυρος, το εύρος είναι σωστό, ο τύπος αποτιμάται σε true ακριβώς στα σωστά κελιά, και τίποτα δεν αλλάζει χρώμα, επειδή η εντολή του κανόνα ήταν «true, μη ζωγραφίζεις τίποτα». Αυτό το χάσμα ανάμεσα στη συνθήκη και τη συνέπεια είναι το πρώτο πράγμα που πρέπει να μπει σωστά, και ευθύνεται για τους περισσότερους κανόνες που φαίνονται σωστοί στο Manage Rules αλλά δεν επισημαίνουν τίποτα

Το HotXLS γράφει μορφοποίηση υπό όρους εγγενώς και στα δύο αρχεία, BIFF8 .xls και OOXML .xlsx, και κάνει το ίδιο για τα rich text runs και ένα μοντέλο στιλ κελιού βασισμένο σε pool. Τα τρία χαρακτηριστικά μοιράζονται περισσότερη εσωτερική καλωδίωση απ' όσο υποδηλώνει η επίπεδη επιφάνεια του API, και τα σημεία όπου η έξοδος παρεκκλίνει από την πρόθεση είναι συνήθως οι αρθρώσεις ανάμεσά τους

Μια συνθήκη χρειάζεται μια συνέπεια: το στιλ dxf

Στο φύλλο εργασίας XLSX, οι κανόνες σύγκρισης προέρχονται από την AddConditionalFormat, η οποία δέχεται ένα εύρος, έναν τελεστή από την TXLSXCfOperator, και έναν τύπο ή μια κυριολεκτική τιμή, και έπειτα επιστρέφει τον δείκτη του νέου κανόνα μέσα στη συλλογή ConditionalFormats του φύλλου. Το αντικείμενο κανόνα σε αυτόν τον δείκτη εκθέτει μια ιδιότητα Style, και εκεί ζει η επισήμανση. Ορίστε ένα γέμισμα σε αυτήν και τα κελιά που πληρούν τις προϋποθέσεις παίρνουν το γέμισμα. Αφήστε την ανέγγιχτη και έχετε χτίσει τον αόρατο κανόνα που περιγράφηκε παραπάνω

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Idx: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('kpi.xlsx');
    Sheet := Book.Sheets[0];

    // Αρνητική απόκλιση: ανοιχτό κόκκινο γέμισμα
    Idx := Sheet.AddConditionalFormat('D2:D200', xlsxCfOpLessThan, '0');
    Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($FFFFC7CE);

    // Τα διπλότυπα IDs παραγγελιών σημαίνονται με τον ίδιο τρόπο
    Idx := Sheet.AddCondFormatDuplicateValues('A2:A200');
    Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($FFFFEB9C);

    // Κανόνας προσαρμοσμένου τύπου: επισήμανε γραμμές όπου το πραγματικό υπολείπεται του 90% του στόχου
    Idx := Sheet.AddCondFormatExpression('B2:B200', '$C2<$B2*0.9');
    Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($FFFFC7CE);

    Book.SaveAs('kpi-flagged.xlsx');
  finally
    Book.Free;
  end;
end;

Τα χρώματα εδώ είναι τιμές ARGB 32-bit, οπότε το $FFFFC7CE είναι το «ανοιχτό κόκκινο» του Excel που γνωρίζετε από το παράθυρο διαλόγου, με ένα πλήρως αδιαφανές byte άλφα να προηγείται του RGB. Κάθε είδος κανόνα που ενεργοποιείται με μια συνθήκη ανά κελί ακολουθεί το ίδιο σχήμα δημιουργία-μετά-στιλ. Οι αντιστοιχιστές κειμένου (AddCondFormatContainsText, AddCondFormatBeginsWith, AddCondFormatEndsWith) επιστρέφουν έναν δείκτη που στιλάρετε στη συνέχεια, όπως και οι AddCondFormatTop10, AddCondFormatAboveAverage, και οι ανιχνευτές κενών και σφαλμάτων. Μάθετε το μοτίβο μία φορά και όλη η οικογένεια κειμένου-και-σύγκρισης συμπεριφέρεται το ίδιο

Οι data bars, οι κλίμακες χρωμάτων, και τα icon sets ζωγραφίζονται μόνα τους

Τα οπτικά είδη κανόνων λειτουργούν με τον αντίθετο τρόπο. Κουβαλούν την εμφάνισή τους μέσα στον ίδιο τον ορισμό του κανόνα και αγνοούν εντελώς την ιδιότητα Style. Αναθέστε ένα γέμισμα σε έναν κανόνα data bar και δεν συμβαίνει τίποτα, κάτι που μοιάζει με σφάλμα μέχρι να «κουμπώσει» η ταξινόμηση: η AddCondFormatDataBar δέχεται το χρώμα της μπάρας ως άμεσο όρισμα, οι κλίμακες χρωμάτων δύο και τριών σημείων δέχονται τα χρώματα των άκρων τους με τον ίδιο τρόπο, και η AddCondFormatIconSet επιλέγει έναν από τους 26 τύπους icon set, όπως το icsTrafficLights3. Δεν υπάρχει εδώ ξεχωριστή εγγραφή στιλ που να ξεχάσετε, γιατί δεν υπάρχει καθόλου ξεχωριστή εγγραφή στιλ

Οι παράμετροι που αξίζει να σκεφτείτε σε αυτές τις κλήσεις είναι οι άγκυρες τιμών, τυποποιημένες ως TXLSCfValueKind. Το άκρο μιας μπάρας ή κλίμακας μπορεί να βρίσκεται στο ελάχιστο ή το μέγιστο του εύρους, σε έναν κυριολεκτικό αριθμό, σε ένα ποσοστό ή ένα εκατοστημόριο, ή στο αποτέλεσμα ενός τύπου. Οι προεπιλογές, ελάχιστο-του-εύρους και μέγιστο-του-εύρους, συμπεριφέρονται καλά σε τακτοποιημένα δοκιμαστικά δεδομένα και έπειτα σας προδίδουν σε πραγματικά δεδομένα με ακραίες τιμές: μία ασυγκράτητη τιμή τεντώνει την κλίμακα και ισοπεδώνει κάθε άλλη μπάρα σε ένα κοτσάνι. Όταν ένα dashboard προορίζεται να διαβάζεται σε πολλές περιόδους, αγκυρώστε τα άκρα σε σταθερούς αριθμούς ή εκατοστημόρια αντ' αυτού, ώστε μισή μπάρα τον Μάρτιο να σημαίνει την ίδια ποσότητα με μισή μπάρα τον Απρίλιο. Μια αυτόματα κλιμακούμενη μπάρα είναι συγκρίσιμη μόνο με τον εαυτό της

Ο συγγραφέας XLS καλύπτει τέσσερα είδη κανόνων, όχι παραπάνω

Η παλαιά πλευρά BIFF8 δεν είναι ένας μικρότερος καθρέφτης της πλευράς XLSX· είναι ένα σκόπιμο υποσύνολο. Η πρόσοψη XLS μπορεί να δημιουργήσει ακριβώς τέσσερα σχήματα κανόνων υπό όρους, data bars, κλίμακες δύο χρωμάτων, κλίμακες τριών χρωμάτων, και icon sets, εκπεμπόμενα ως εγγραφές CF12 μέσα στο stream. Δεν έχει API δημιουργίας για κανόνες cellIs, έκφρασης, ή κειμένου. Κανόνες αυτών των ειδών που ήδη υπάρχουν σε ένα αρχείο που ανοίγετε διαβάζονται, διατηρούνται, και ξαναγράφονται αμετάβλητοι, οπότε το άνοιγμα και η επανααποθήκευση του .xls ενός πελάτη δεν βλάπτει ποτέ τη μορφοποίηση που κουβαλούσε. Αυτό που δεν μπορείτε να κάνετε είναι να δημιουργήσετε επισήμανση ορίων από το μηδέν μέσα σε ένα .xls. Οι επιλογές εκεί είναι είτε να το προσομοιώσετε με συνηθισμένα γεμίσματα κελιών υπολογισμένα στον κώδικα, είτε να κάνετε το παραδοτέο .xlsx, όπου όλη η οικογένεια κανόνων είναι διαθέσιμη

Αυτός είναι ένας περιορισμός που πρέπει να διευθετηθεί πριν υπάρξει το επίπεδο δεδομένων, όχι μετά, επειδή αλλάζει την απόφαση μορφής αρχείου για οτιδήποτε έχει σχήμα dashboard. Μια ομάδα που επέλεξε .xls για συμβατότητα και έπειτα σχεδιάζει μια αναφορά KPI με όρια cellIs έχει επιλέξει δύο πράγματα που δεν ταιριάζουν μεταξύ τους, και η φθηνότερη στιγμή για να το προσέξει είναι στην απόφαση μορφής και όχι τρεις εβδομάδες μέσα στην κατασκευή

Στοίβαξη κανόνων, προτεραιότητα, και επικαλυπτόμενα εύρη

Τα πραγματικά dashboard σπάνια τρέχουν έναν κανόνα ανά εύρος. Μια στήλη απόκλισης μπορεί να κουβαλά μια data bar για το μέγεθος, έναν κανόνα cellIs για το αυστηρό όριο, και έναν κανόνα έκφρασης σε επίπεδο γραμμής πάνω από τους δύο για κλιμακώσεις. Κάθε TXLSXConditionalFormat εκθέτει μια τιμή Priority, και το Excel επιλύει ανταγωνιστικούς κανόνες με σειρά προτεραιότητας. Όταν δύο κανόνες θέλουν να ζωγραφίσουν το ίδιο κελί, ο νικητής αποφασίζεται από έναν αριθμό που ορίζετε εσείς, όχι από όποια σειρά τυχαίνει να κυλήσει ένας αναθεωρητής στο παράθυρο διαλόγου Manage Rules

Μεταχειριστείτε την προτεραιότητα όπως ένα πρόγραμμα σχεδίασης μεταχειρίζεται το z-order. Αναθέστε τη σκόπιμα όπου δύο κανόνες μπορούν να φτάσουν στα ίδια κελιά, και αφήστε κενά ανάμεσα στις τιμές ώστε ένας μεταγενέστερος κανόνας να χωράει χωρίς να χρειάζεται επαναρίθμηση των υπολοίπων. Όπου οι κανόνες δεν μπορούν να συγκρουστούν, ας πούμε μια data bar περιορισμένη στη στήλη E και έναν κανόνα κειμένου περιορισμένο στη στήλη G, η σειρά δημιουργίας είναι εντάξει και η προτεραιότητα δεν αξίζει την προσοχή σας. Ξοδέψτε αυτή την προσοχή στα όρια εύρους αντ' αυτού, γιατί τα ακριβά σφάλματα εδώ σχεδόν ποτέ δεν είναι αναστροφές προτεραιότητας. Είναι εύρη όπως το B2:B200 σε μια αναφορά που μεγάλωσε στις 350 γραμμές, όπου η ακάλυπτη ουρά αποδίδεται ως απλά κελιά που μοιάζουν ακριβώς με υγιή δεδομένα. Αντλήστε κάθε εύρος κανόνα από την ίδια τελική τιμή αριθμού γραμμών που οδηγεί τις σειρές γραφημάτων και τα εύρη επικύρωσης αλλού στο βιβλίο εργασίας, και η ουρά σταματά να πέφτει έξω

Μια συνήθεια επαλήθευσης δικαιολογεί το κόστος της. Μετά τη δημιουργία, ανοίξτε το αρχείο στο Excel, επιλέξτε το μορφοποιημένο εύρος, και περάστε μια φορά από το Manage Rules για κάθε αλλαγή template. Η μορφοποίηση υπό όρους είναι μία από τις λίγες περιοχές όπου ο μόνος αυθεντικός αποδότης είναι η ίδια η εφαρμογή που καταναλώνει το αρχείο, οπότε ένα unit test πάνω στο XML αποδεικνύει ότι ο κανόνας γράφτηκε, όχι ότι το Excel τον ζωγραφίζει όπως εννοούσατε. Ένα λεπτό οπτικού ελέγχου κλείνει αυτό το χάσμα

Rich text: πολλές μορφοποιήσεις μέσα σε ένα κελί

Ένα κελί rich-text στο μοντέλο XLSX κρατά μια λίστα από runs, όπου κάθε run είναι ένα τμήμα κειμένου συν τα δικά του χαρακτηριστικά γραμματοσειράς. Χτίζετε τη λίστα στο πλάι ως αντικείμενο TXLSXRichText, προσθέτετε runs σε αυτό, και έπειτα προσαρτάτε ολόκληρο το αντικείμενο σε ένα κελί. Ο κανόνας κυριότητας είναι το σημείο που δαγκώνει. Η ανάθεση στην Cell.RichText παραδίδει την κυριότητα εκείνου του αντικειμένου στο κελί, και το κελί το ελευθερώνει κατά τη δική του καταστροφή. Ελευθερώστε το κι εσείς οι ίδιοι και έχετε ένα double-free, το είδος που μένει σιωπηλό στο run που το προκάλεσε και αναδύεται ως κατάρρευση κάπου άσχετο πολύ αργότερα

var
  Rich: TXLSXRichText;
  Run: TXLSXRichTextRun;
begin
  Rich := TXLSXRichText.Create;
  Rich.AddRunText('Status: ');
  Run := Rich.AddRunText('OVERDUE');
  Run.Bold := True;
  Run.Color := $FFC00000;
  Run.ColorIsAuto := False;
  Run := Rich.AddRunText(' (escalated to regional manager)');
  Run.Italic := True;
  Sheet.Cells[2, 7].RichText := Rich;   // η κυριότητα μεταφέρεται στο κελί: μην κάνετε Free
end;

Η ρητή ColorIsAuto := False δεν είναι προαιρετική διακόσμηση. Ένα run κουβαλά μια σημαία αυτόματου χρώματος, και μια ανάθεση χρώματος τηρείται μόνο αφού καθαριστεί εκείνη η σημαία. Ορίστε την Color και ξεχάστε την ColorIsAuto και το run βγαίνει έντονο αλλά επίμονα μαύρο, χωρίς κανένα σφάλμα να δείχνει την αιτία. Τα runs υποστηρίζουν επίσης διαγράμμιση, τις παραλλαγές υπογράμμισης, και κατακόρυφη στοίχιση για εκθέτη και δείκτη, ενώ η PlainText ισοπεδώνει ολόκληρη τη λίστα πίσω σε μία μόνο συμβολοσειρά όταν χρειάζεται να εξάγετε ή να συγκρίνετε το περιεχόμενο κειμένου

Το rich text σε επίπεδο κελιού είναι αποκλειστικά για XLSX. Η πρόσοψη XLS δεν έχει δημόσιο API για τη συγγραφή του, αν και τα runs είναι διαθέσιμα εκεί σε σχόλια και text boxes μέσω της TextRuns, και οι rich συμβολοσειρές που διαβάζονται από ένα υπάρχον .xls επιβιώνουν άθικτες σε έναν κύκλο ανάγνωσης-εγγραφής. Η έλξη είναι η ίδια όπως με τη μορφοποίηση υπό όρους: οτιδήποτε αναμειγνύει μορφοποιήσεις μέσα σε ένα κελί ανήκει στον συγγραφέα XLSX

Το pool στιλ και το off-by-one που καταλήγει στο παραδοτέο

Η απλή μορφοποίηση κελιών στο μοντέλο XLSX περνά μέσα από συλλογές pool πάνω στο βιβλίο εργασίας. Οι Fonts.Add, Fills.AddSolid, και Borders.Add καταχωρούν η καθεμία έναν ορισμό και επιστρέφουν τον δείκτη του μέσα στο pool. Αυτοί οι δείκτες ξεκινούν από το μηδέν. Οι ιδιότητες στην πλευρά του κελιού που τους καταναλώνουν, όπως η FontIndex, κρατούν το 0 για το «προεπιλεγμένο», οπότε η τιμή που αναθέτετε σε ένα κελί είναι ο δείκτης pool συν ένα:

HeaderFont := Book.Fonts.Add('Calibri', 11, True, False);  // δείκτης pool, ξεκινά από το μηδέν
for Col := 1 to 6 do
  Sheet.Cells[1, Col].FontIndex := HeaderFont + 1;          // δείκτης κελιού, ξεκινά από το ένα

Παραλείψτε το + 1 και κάθε κεφαλίδα επιστρέφει στην προεπιλεγμένη γραμματοσειρά. Δεν υπάρχει εξαίρεση και καμία προειδοποίηση, μόνο ένα βιβλίο εργασίας που μοιάζει σαν να μην το στιλάρισε κανείς. Το σφάλμα δεύτερης τάξης κρύβεται στον βρόχο: η κλήση της Fonts.Add μία φορά ανά γραμμή. Οι πανομοιότυποι ορισμοί γραμματοσειράς αφαιρούν τα διπλότυπα, οπότε το αρχείο δεν διαφθείρεται, αλλά η δουλειά πάει χαμένη, και το pool στοίχισης συγκεκριμένα επιστρέφει ένα φρέσκο αντικείμενο σε κάθε κλήση αντί να συμπτύσσει τα διπλότυπα. Χτίστε τη χούφτα στιλ μία φορά πριν τον βρόχο και επαναχρησιμοποιήστε τους δείκτες τους. Σε αναφορές εκατοντάδων χιλιάδων γραμμών, αυτή η μία αλλαγή είναι ένας από τους μοχλούς που καλύπτονται στο συντονισμό απόδοσης μεγάλων βιβλίων εργασίας για το HotXLS. Όταν χρειάζεστε μόνο μια έτοιμη σημασιολογική εμφάνιση, και οι δύο προσόψεις εκθέτουν την ApplyBuiltinStyle πάνω σε εύρη, η οποία αντιστοιχίζεται στα ενσωματωμένα στιλ Good, Bad, Neutral, και accent του Excel χωρίς να αγγίξετε καθόλου τα pools

Η μορφοποίηση υπό όρους, το rich text, και τα pooled στιλ είναι το τελευταίο μίλι μιας αναφοράς, εφαρμοσμένα αφού έχουν διευθετηθεί το μοντέλο δεδομένων και η διάταξη, και αυτά τα προγενέστερα στάδια είναι το θέμα της δημιουργίας αναφορών βάσει template με το HotXLS. Η πλήρης αναφορά κανόνων, runs, και στιλ βρίσκεται στη σελίδα προϊόντος HotXLS Delphi Component