Επικύρωση δεδομένων, AutoFilter και πίνακες φύλλου εργασίας στο Delphi με το HotXLS

Τρία χαρακτηριστικά στο HotXLS μοιράζονται ένα φύλλο εργασίας αλλά λειτουργούν πάνω σε εντελώς διαφορετικά αντικείμενα, και το πρόβλημα ξεκινά όταν υποθέτετε ότι κάνουν παρόμοια πράγματα. Η επικύρωση δεδομένων προσαρτά έναν κανόνα σε ένα εύρος που περιορίζει τι μπορεί να πληκτρολογήσει ένας χρήστης μέσα σε αυτό. Ένα AutoFilter προσαρτά έναν αποθηκευμένο ορισμό κριτηρίων σε μια περιοχή και αλλάζει ποιες γραμμές δείχνει ένας θεατής. Ένας πίνακας τυλίγει ένα εύρος σε μια επώνυμη, τυποποιημένη δομή με εναλλασσόμενη μορφοποίηση. Το ένα περιορίζει την εισαγωγή, το άλλο καταγράφει μια προβολή, το τρίτο επιβάλλει ένα σχήμα. Κανένα τους δεν μετακινεί μόνο του ούτε μία τιμή κελιού, και το AutoFilter συγκεκριμένα ξεγελά τον κόσμο, επειδή η λέξη υποδηλώνει μια ενέργεια ενώ αποθηκεύει μόνο έναν ορισμό. Το να γνωρίζετε ποιο αντικείμενο αγγίζει κάθε κλήση, και πότε η επίδραση όντως υλοποιείται, είναι αυτό που ξεχωρίζει ένα βιβλίο εργασίας που συμπεριφέρεται στο Excel το ίδιο όπως στις δοκιμές σας από ένα που σιωπηλά αποκλίνει

Το AutoFilter αποθηκεύει έναν ορισμό, δεν κόβει γραμμές

Ένα AutoFilter σε ένα αποθηκευμένο αρχείο είναι μια εγγραφή κριτηρίων. Η απόκρυψη γραμμών συμβαίνει αργότερα, όταν το Excel ανοίγει το βιβλίο εργασίας και αποτιμά τα κριτήρια έναντι των δεδομένων. Το HotXLS γράφει αυτή την εγγραφή πιστά και δεν κόβει τίποτα: κάθε γραμμή που φιλτράρατε εξακολουθεί να είναι φυσικά παρούσα στο αρχείο. Μια διοχέτευση που εφαρμόζει ένα φίλτρο για να αποβάλει απορριφθείσες παραγγελίες και έπειτα διαβάζει το βιβλίο εργασίας πίσω θα τις δει όλες, μαζί με τις απορριφθείσες, και ο κώδικας είναι σωστός σύμφωνα με το API ενώ είναι λάθος σύμφωνα με το νοητικό μοντέλο του συγγραφέα. Στο φύλλο εργασίας XLSX, η SetAutoFilter δηλώνει τη φιλτραρισμένη περιοχή και η AddAutoFilterColumn προσαρτά κριτήρια σε μία στήλη της. Όταν ο κώδικας από την πλευρά του διακομιστή χρειάζεται το πραγματικό αποτέλεσμα, για έναν αριθμό γραμμών σε μια περίληψη ή για να προωθήσει μόνο τις γραμμές που ταιριάζουν, η βιβλιοθήκη αποτιμά τα κριτήρια για εσάς αντί να προσποιείται ότι το αρχείο άλλαξε:

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  R, Visible: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('orders.xlsx');
    Sheet := Book.Sheets[0];

    Sheet.SetAutoFilter('A1:E500');
    // Column id 3 = τέταρτη στήλη ΜΕΣΑ στο εύρος φίλτρου (offset που ξεκινά από το μηδέν)
    Sheet.AddAutoFilterColumn(3, xlsxAfOpGreaterOrEqual, '1000');

    Visible := 0;
    for R := 2 to 500 do
      if Sheet.AutoFilterRowVisible(R) then
        Inc(Visible);
    // Το Visible τώρα ταιριάζει με αυτό που θα δείξει το Excel μετά το άνοιγμα του αρχείου

    Book.SaveAs('orders-filtered.xlsx');
  finally
    Book.Free;
  end;
end;

Η AutoFilterRowVisible απαντά ανά γραμμή, και η PreviewAutoFilterRows διατρέχει ολόκληρη την περιοχή μέσω ενός callback όταν χρειάζεστε το σύνολο που ταιριάζει σε ένα πέρασμα. Υπάρχει μια περίπτωση όπου καμία από τις δύο δεν είναι η σωστή απάντηση: αν η απαίτηση είναι ότι οι εξαιρούμενες γραμμές δεν πρέπει καθόλου να υπάρχουν στο αρχείο, μια περικοπή απορρήτου και όχι μια προβολή, διαγράψτε τις γραμμές εντελώς. Ένα φίλτρο είναι το λάθος εργαλείο εκεί, επειδή οποιοσδήποτε παραλήπτης το καθαρίζει με ένα κλικ και τα δεδομένα που σκοπεύατε να αποκρύψετε επιστρέφουν στην οθόνη

Το column id είναι ένα offset, όχι έναν αριθμό στήλης

Το σχόλιο στο παραπάνω απόσπασμα επισημαίνει την παγίδα που κοστίζει τον περισσότερο χρόνο debugging σε αυτό το API. Η AddAutoFilterColumn προσδιορίζει τον στόχο της με τη θέση που ξεκινά από το μηδέν μέσα στο εύρος φίλτρου, όχι με τη στήλη του φύλλου εργασίας. Για ένα φίλτρο στο A1:E500 τα δύο συστήματα αρίθμησης τυχαίνει να διαφέρουν κατά ένα, κάτι που είναι ακριβώς το είδος του παραλίγο λάθους που επιβιώνει σε ένα γρήγορο τεστ και σπάει τη στιγμή που ένας συνάδελφος φιλτράρει διαφορετική στήλη. Για ένα φίλτρο που ξεκινά στη στήλη C, το id 0 σημαίνει στήλη C, και η αναντιστοιχία γίνεται γρήγορα εμφανής. Όταν το εύρος φίλτρου υπολογίζεται κατά τον χρόνο εκτέλεσης, αντλήστε το column id από την ίδια μεταβλητή που έχτισε τη συμβολοσειρά εύρους, ποτέ από μια σταθερά στήλης φύλλου εργασίας. Κάθε στήλη δέχεται μια δεύτερη συνθήκη μέσω της υπερφόρτωσης που δέχεται δύο τελεστές, δύο κριτήρια, και έναν σύνδεσμο and/or, που αντικατοπτρίζει το προσαρμοσμένο παράθυρο διαλόγου φίλτρου του Excel. Η πρόσοψη XLS καλύπτει το ίδιο έδαφος με την SetAutoFilter μαζί με την ApplyAutoFilter, της οποίας οι παράμετροι κριτηρίου και τελεστή ακολουθούν τις παλαιότερες συμβάσεις τύπου COM και αριθμούν το πεδίο από το 1. Η αλλαγή πρόσοψης σημαίνει αλλαγή βάσης δεικτών, οπότε το σημείο κλήσης αξίζει ένα σχόλιο που να λέει ποια βρίσκεται σε χρήση

Οι κανόνες επικύρωσης είναι το συμβόλαιο κάτω από το οποίο επεξεργάζονται οι χρήστες σας

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

var
  Idx: Integer;
begin
  Idx := Sheet.AddListValidation('C2:C500', 'New,Approved,Blocked');
  Sheet.DataValidations[Idx].SetPrompt('Status',
    'Pick one of the listed states');
  Sheet.DataValidations[Idx].SetError('Invalid status',
    'Type or paste only listed values', xlsxDvErrStop);
  Sheet.DataValidations[Idx].AllowBlank := False;

  // Ποσότητες: ακέραιοι αριθμοί, μηδέν ή περισσότερο
  Sheet.AddWholeNumberValidation('D2:D500', xlsxDvOpGreaterOrEqual, '0');
end;

Πέρα από λίστες και ακέραιους αριθμούς, η ίδια οικογένεια καλύπτει δεκαδικούς, ημερομηνίες, ώρες, μήκος κειμένου, και τύπους ελεύθερης μορφής μέσω της AddCustomValidation, και η γενική AddDataValidation εκθέτει τον πλήρη πίνακα τύπου-και-τελεστή για κατασκευαστές κανόνων που οδηγούνται από ρύθμιση. Το στιλ σφάλματος έχει μεγαλύτερη σημασία απ' όσο υποδηλώνει το όνομά του. Η xlsxDvErrStop απορρίπτει κακή εισαγωγή εντελώς· τα στιλ προειδοποίησης και πληροφορίας αφήνουν την τιμή να περάσει μετά από ένα μόνο κλικ. Επιλέξτε ανά στήλη με βάση το αν ο κώδικας που διαβάζει το βιβλίο εργασίας πίσω μπορεί να ανεχτεί μια τιμή εκτός κανόνα. Δύο όρια ανήκουν στο κείμενο prompt ή στο README που παραδίδετε μαζί με το αρχείο. Η επικύρωση στο Excel φυλάει την πληκτρολόγηση, αλλά η επικόλληση ενός μπλοκ πάνω σε ένα επικυρωμένο εύρος γλιστρά πέρα από τον κανόνα, οπότε οποιοσδήποτε κώδικας διαβάζει τα δεδομένα πίσω πρέπει να επικυρώνει ξανά αντί να εμπιστεύεται τα κελιά. Και ένας κανόνας καλύπτει το κυριολεκτικό εύρος που του δώσατε, πράγμα που σημαίνει ότι η προσάρτηση επικύρωσης πριν γνωρίζετε τον τελικό αριθμό γραμμών αφήνει την προστιθέμενη ουρά χωρίς προστασία. Γράψτε πρώτα τα δεδομένα, έπειτα προσαρμόστε το μέγεθος των κανόνων στην πραγματική έκταση

Η παλαιά πρόσοψη προσφέρει τις ίδιες οικογένειες κανόνων με μία εργονομική διαφορά. Οι δημιουργοί στην πλευρά XLS, δηλαδή οι AddWholeNumberValidation, AddDecimalValidation, AddDateValidation, AddTimeValidation, AddTextLengthValidation, και AddCustomValidation, επιστρέφουν απευθείας το αντικείμενο TDataValidation αντί για έναν δείκτη, οπότε η ρύθμιση prompt και σφάλματος αλυσιδώνεται από την επιστρεφόμενη αναφορά αντί από αναζήτηση. Η απαρίθμηση τελεστών (xlsDvBetween, xlsDvGreaterThan, και οι υπόλοιποι) αντικατοπτρίζει το σύνολο XLSX, οπότε ο κώδικας κατασκευής κανόνων μεταφέρεται ανάμεσα στις προσόψεις εκτός από αυτή τη διαφορά στιλ επιστροφής. Το ίδιο το κείμενο prompt αξίζει τόση σκέψη όση και ο κανόνας. Ένα dropdown που απορρίπτει την εισαγωγή με ένα κενό πλαίσιο σφάλματος διδάσκει τους χρήστες να στέλνουν email στο IT· ένα που ονομάζει τις επιτρεπτές καταστάσεις τούς διδάσκει να διορθώσουν το κελί και να συνεχίσουν

Μια αντιστροφή πολικότητας που η βιβλιοθήκη απορροφά για εσάς

Όποιος έχει διαβάσει με το χέρι XML επικύρωσης OOXML έχει συναντήσει το αντεστραμμένο χαρακτηριστικό showDropDown: στο ISO/IEC 29500 η τιμή true σημαίνει «κατάργησε το βέλος του dropdown», το αντίθετο από αυτό που υποδηλώνει το όνομα. Το HotXLS αντιστρέφει αυτό εσωτερικά, οπότε η ιδιότητα ShowDropDown σε έναν κανόνα επικύρωσης σημαίνει αυτό που λέει, με το true να εμφανίζει το dropdown. Ο μόνος τρόπος να καείτε είναι να αναμείξετε επίπεδα αλήθειας, ορίζοντας την ιδιότητα από τον κώδικα ενώ ένας συνάδελφος ελέγχει το αποθηκευμένο XML και «διορθώνει» το χαρακτηριστικό που του φαίνεται ανάποδο. Αποφασίστε αν η ιδιότητα ή το ακατέργαστο XML είναι αυθεντικό για τα εργαλεία αναθεώρησης, και γράψτε την αντιστροφή εκεί όπου ζει αυτή η απόφαση

Οι πίνακες δίνουν σε ένα εύρος σχήμα και όνομα

Ένας πίνακας φύλλου εργασίας, το ListObject στους όρους του Excel, τυλίγει ένα εύρος σε ένα όνομα, τυποποιημένες στήλες, εναλλασσόμενη μορφοποίηση, και υποστήριξη δομημένων αναφορών. Είναι το χαρακτηριστικό που κάνει ένα δημιουργημένο βιβλίο εργασίας να μοιάζει ολοκληρωμένο μόλις οι χρήστες αρχίσουν να το ταξινομούν και να το επεκτείνουν. Η δημιουργία είναι συμμετρική ανάμεσα στις προσόψεις, με την AddTable να δέχεται ένα όνομα, ένα εύρος, και μια λίστα στηλών:

var
  Cols: TStringList;
begin
  Cols := TStringList.Create;
  try
    Cols.CommaText := 'OrderId,Customer,Status,Amount,Owner';
    Sheet.AddTable('Orders', 'A1:E500', Cols);
  finally
    Cols.Free;
  end;
end;

Στην πλευρά XLSX το αντικείμενο πίνακα που προκύπτει εκθέτει την StyleName (την ενσωματωμένη οικογένεια TableStyleMedium2 και τα αδέρφια της), εναλλαγές ριγέ εμφάνισης, και μια σημαία γραμμής συνόλων, οπότε η εφαρμογή στιλ εταιρικής ταυτότητας είναι μια ανάθεση ιδιότητας αντί για ένα χειροκίνητο πέρασμα μορφοποίησης. Σε παλαιά αρχεία .xls η ίδια κλήση γράφει τις εγγραφές πίνακα BIFF8, και η πρόσοψη προσφέρει επίσης την AddPivotTable για προβολές περίληψης χτισμένες από πεδία γραμμών, στηλών, και δεδομένων, μια υπενθύμιση ότι οι «πίνακες» στην παλαιότερη μορφή φτάνουν πιο μακριά απ' όσο το ListObject του OOXML. Ονομάστε τους πίνακες όπως ονομάζετε τις όψεις βάσης δεδομένων. Ο κώδικας παρακάτω στη ροή που διαβάζει Orders[Amount] με δομημένη αναφορά επιβιώνει από την αναδιάταξη στηλών που σπάει τον κώδικα βασισμένο σε θέση

Δύο συμβάσεις γλιτώνουν καθαρισμό αργότερα. Το Excel απαιτεί τα ονόματα πινάκων να είναι μοναδικά σε ολόκληρο το βιβλίο εργασίας, οπότε μια γεννήτρια που εκδίδει ένα φύλλο ανά περιοχή χρειάζεται ένα σχήμα όπως Orders_EMEA αντί να επαναχρησιμοποιεί το Orders. Ένα διπλότυπο δεν αποτυγχάνει κατά τη στιγμή της εγγραφής· αναδύεται ως παράθυρο διαλόγου επιδιόρθωσης όταν ο χρήστης ανοίγει το αρχείο, που είναι το χειρότερο σημείο για να το ανακαλύψει. Η άλλη σύμβαση αφορά τη γραμμή συνόλων: όταν είναι ενεργοποιημένη, κάθεται ακριβώς κάτω από το εύρος δεδομένων, οπότε οποιοσδήποτε κώδικας προσθέτει αργότερα με «τελευταία χρησιμοποιημένη γραμμή συν ένα» γράφει μέσα στη ζώνη συνόλων αντί για μετά από αυτήν. Παρακολουθήστε την έκταση δεδομένων ξεχωριστά από την έκταση πίνακα και οι προσθήκες προσγειώνονται εκεί που περιμένετε

Τα τρία χαρακτηριστικά συνδυάζονται φυσικά σε παραδοτέα εισαγωγής δεδομένων. Ένας πίνακας ορίζει την επεξεργάσιμη περιοχή, η επικύρωση περιορίζει τις στήλες στις οποίες πληκτρολογούν οι χρήστες, και ένα προρυθμισμένο φίλτρο γλιτώνει τον παραλήπτη από τα πρώτα κλικ. Υπάρχει ένα δίκαιο επιχείρημα για την αποστολή ενός φίλτρου ήδη εφαρμοσμένου ώστε το βιβλίο εργασίας να ανοίγει εστιασμένο στις γραμμές που έχουν σημασία, αρκεί να θυμάστε ότι οι εξαιρούμενες γραμμές εξακολουθούν να είναι στο αρχείο και ένας περίεργος παραλήπτης μπορεί να τις αποκαλύψει. Το να φέρετε αποτελεσματικά αποτελέσματα ερωτημάτων στο φύλλο, το ανάντη μισό αυτής της διοχέτευσης, καλύπτεται στο εξαγωγή αποτελεσμάτων βάσης δεδομένων σε Excel από τη Delphi, και βιβλία εργασίας όπου τύποι συνοψίζουν τα επικυρωμένα δεδομένα ωφελούνται από defined names για σταθερές αναφορές μεταξύ φύλλων

Η επικύρωση, τα φίλτρα, και οι πίνακες είναι η διαφορά ανάμεσα στην αποστολή ενός πλέγματος τιμών και στην αποστολή μιας μικρής εφαρμογής. Η πλήρης αναφορά κανόνων, φίλτρων, και πινάκων βρίσκεται στη σελίδα προϊόντος HotXLS Delphi Component