Κατασκευή και Ενημέρωση Συγκεντρωτικών Πινάκων (Pivot Tables) Excel στο Delphi με το HotXLS

Το HotXLS κατασκευάζει και ενημερώνει εγγενείς συγκεντρωτικούς πίνακες (pivot tables) XLSX από το Delphi και το C++Builder χωρίς να απαιτείται εγκατάσταση του Excel ή αυτοματοποίηση COM (COM automation) στο μηχάνημα. Καλείτε το AddPivotTable με ένα εύρος προέλευσης, τοποθετείτε πεδία στους άξονες γραμμών, στηλών, σελίδων και δεδομένων, προσθέτετε υπολογιζόμενα στοιχεία ή εμφάνιση ποσοστού επί του συνόλου, και το στοιχείο γράφει τα τμήματα pivotCacheDefinition και pivotTableDefinition που το Excel ανοίγει ως έναν ζωντανό, ενημερώσιμο συγκεντρωτικό πίνακα

Το σενάριο που κάνει αυτή τη δυνατότητα να αξίζει τον κόπο είναι ένας διακομιστής αναφορών (reporting server). Δημιουργείτε εκατοντάδες βιβλία εργασίας κάθε νύχτα, καθένα από τα οποία περιέχει έναν συγκεντρωτικό πίνακα που συνοψίζει έναν λογαριασμό, και τον επόμενο μήνα οι αριθμοί αλλάζουν και κάθε αρχείο πρέπει να αντικατοπτρίζει τις νέες γραμμές προέλευσης. Η καθοδήγηση του Excel από μια υπηρεσία των Windows (Windows service) είναι ασταθής και δεν διαθέτει άδεια χρήσης για διακομιστές, ενώ η χειροκίνητη συγγραφή του XML του συγκεντρωτικού πίνακα είναι ένα έργο αρχαιολογίας προδιαγραφών που δεν τελειώνει ποτέ. Το HotXLS βρίσκεται ανάμεσα σε αυτά τα δύο αδιέξοδα: ένα πληκτρολογημένο μοντέλο αντικειμένων (object model) πάνω από τα τμήματα του συγκεντρωτικού πίνακα OOXML, έτσι ώστε η ίδια Pascal που συμπληρώνει τα κελιά να δηλώνει επίσης τον συγκεντρωτικό πίνακα και να ξαναχτίζει την προσωρινή μνήμη (cache) του στην ίδια διεργασία

Πώς δημιουργείτε έναν συγκεντρωτικό πίνακα στο Delphi χωρίς το Excel;

Δημιουργείτε έναν με μία μόνο κλήση και στη συνέχεια τοποθετείτε πεδία στους άξονες. Το AddPivotTable λαμβάνει το εύρος προέλευσης σε μορφή A1, το κελί προορισμού όπου αγκυροβολεί ο πίνακας και ένα όνομα. Αναλύει το εύρος, σαρώνει κάθε στήλη για να συμπεράνει τον τύπο δεδομένων της, δημιουργεί μια προσωρινή μνήμη συγκεντρωτικού πίνακα (pivot cache) ή επαναχρησιμοποιεί μία που είναι ήδη συνδεδεμένη στο ίδιο εύρος, και επιστρέφει ένα TXLSPivotTable του οποίου τα πεδία ξεκινούν όλα εκτός άξονα. Από εκεί, οι βολικές μέθοδοι AddRowField, AddColumnField, AddPageField και AddDataFieldByName συνδέουν τη διάταξη, και κάθε πεδίο δεδομένων λαμβάνει μία από τις έντεκα συγκεντρώσεις από τον απαριθμητή TXLSPivotAggregation (xlpaSum, xlpaCount, xlpaAverage, xlpaMax, xlpaMin, xlpaProduct, xlpaCountNums, xlpaStdDev, xlpaStdDevP, xlpaVar, xlpaVarP)

uses
  lxHandleX, lxPivot;

var
  Book : TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Pivot: TXLSPivotTable;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('sales.xlsx');
    Sheet := Book.Sheets[1];                 // φύλλο αναφοράς (με βάση το 1 στη μηχανή XLSX)

    // Προέλευση A1:E500 στο φύλλο 'Data', αγκυροβόληση του συγκεντρωτικού πίνακα στη γραμμή 3, στήλη 1
    Pivot := Sheet.AddPivotTable('Data!$A$1:$E$500', 3, 1, 'SalesByRegion');
    if Pivot <> nil then
    begin
      Pivot.AddRowField('Region');
      Pivot.AddColumnField('Quarter');
      Pivot.AddPageField('Year');
      Pivot.AddDataFieldByName('Revenue', xlpaSum);
      Pivot.AddDataFieldByName('Units', xlpaAverage);
      Book.SaveAs('sales-pivot.xlsx');
    end;
  finally
    Book.Free;
  end;
end;

Γιατί η προσωρινή μνήμη (cache) και ο πίνακας είναι δύο ξεχωριστά τμήματα

Ένας συγκεντρωτικός πίνακας αποτελείται στην πραγματικότητα από δύο στοιχεία που αναφέρονται το ένα στο άλλο, και η κατανόηση αυτού του διαχωρισμού είναι απαραίτητη. Το pivotCacheDefinition είναι το στιγμιότυπο δεδομένων (data snapshot): ένα worksheetSource που δείχνει στο εύρος προέλευσης, και ένα cacheField ανά στήλη που κρατά τις διακριτές τιμές αυτής της στήλης (τα sharedItems της) συν τα παραγόμενα όρια. Το pivotTableDefinition είναι η προβολή: ποιο πεδίο προσωρινής μνήμης βρίσκεται σε ποιον άξονα, τα πεδία δεδομένων και οι συγκεντρώσεις τους, οι εναλλαγές διάταξης. Η προβολή συνδέεται με την προσωρινή μνήμη μέσω του cacheId μέσω της σχέσης pivotCaches του βιβλίου εργασίας, ακριβώς όπως ορίζουν το ECMA-376 Part 1 §18.10 and [MS-XLSX]

Αυτή η έμμεση αναφορά δεν είναι γραφειοκρατία, αλλά εξασφαλίζει δύο πράγματα. Μια προσωρινή μνήμη μπορεί να υποστηρίξει αρκετούς πίνακες, οπότε η ενημέρωση της προσωρινής μνήμης μία φορά ενημερώνει κάθε προβολή που αντλείται από αυτήν. Επίσης, η προσωρινή μνήμη αποθηκεύει τις τιμές κάθε στήλης χωρίς διπλότυπα με έναν πίνακα ευρετηρίου ανά εγγραφή αντί για το ακατέργαστο πλέγμα (grid), γι' αυτό και η κατασκευή ενός συγκεντρωτικού πίνακα σημαίνει σάρωση της προέλευσης, και όχι αντιγραφή κελιών. Το HotXLS μοντελοποιεί αυτό με τον ίδιο τρόπο και για τις δύο μηχανές, οπότε ο παραπάνω κώδικας είναι σχεδόν πανομοιότυπος με την κλασική διαδρομή που τεκμηριώνεται μαζί με τη διάταξη δυαδικών εγγραφών SX πίσω από τους κλασικούς συγκεντρωτικούς πίνακες .xls. Εάν η προέλευσή σας ζει σε άλλο φύλλο ή αναφέρεστε σε αυτήν μέσω ενός ονόματος, το πρόθεμα εύρους και οι ορισμένοι τύποι ονομάτων και διαφυλλικές αναφορές ακολουθούν τους συνήθεις κανόνες A1, με εισαγωγικά στα ονόματα φύλλων που περιέχουν κενά

Τα υπολογιζόμενα στοιχεία (calculated items) δεν είναι υπολογιζόμενα πεδία (calculated fields)

Αυτοί οι τρεις όροι ονομάζουν τρία διαφορετικά πράγματα, και η σύγχυσή τους είναι το κλασικό σφάλμα στους συγκεντρωτικούς πίνακες. Ένα υπολογιζόμενο στοιχείο ζει μέσα σε ένα πεδίο και συνδυάζει τα στοιχεία αυτού του πεδίου με το όνομά τους, οπότε μέσα σε ένα πεδίο Region μπορείτε να ορίσετε μια συνθετική γραμμή CoreMarkets ίση με North συν South. Το HotXLS το εκθέτει ως TXLSPivotField.AddCalculatedItem και γράφει ένα <calculatedItem> κάτω από το <calculatedItems> αυτού του πεδίου. Ένα υπολογιζόμενο πεδίο είναι διαφορετικό: είναι μια νέα τιμή που προέρχεται από άλλες στήλες, όπως το Margin από το Revenue και το Cost, και μεταφέρεται ως τύπος σε ένα πεδίο προσωρινής μνήμης μέσω του TXLSPivotCacheField.Formula. Ένα υπολογιζόμενο μέλος (calculated member), που προστίθεται με το TXLSPivotTable.AddCalculatedMember, είναι ένα προσαρμοσμένο μέλος σε επίπεδο πίνακα που μπορεί να λειτουργήσει ως μέτρο (τύπος μέλους data) ή ως μέλος διάστασης, κυρίως ενδιαφέρον για συγκεντρωτικούς πίνακες μορφής OLAP

var
  Region: TXLSPivotField;
  Member: TXLSPivotCalculatedMember;
begin
  // Ένα υπολογιζόμενο ΣΤΟΙΧΕΙΟ συνδυάζει στοιχεία ενός μόνο πεδίου με βάση το όνομα.
  Region := Pivot.AddRowField('Region');
  Region.AddCalculatedItem('CoreMarkets', '=North+South');

  // Ένα υπολογιζόμενο ΜΕΛΟΣ δηλώνεται σε επίπεδο πίνακα. Ο τύπος μέλους
  // 'data' το χαρακτηρίζει ως μέτρο, ενώ ένας κενός τύπος είναι μέλος διάστασης.
  Member := Pivot.AddCalculatedMember('AvgTicket', '=Revenue/Units');
  Member.MemberType := 'data';
end;

Ένα ειλικρινές όριο ισχύει και για τα τρία. Το HotXLS εκπέμπει το κείμενο του τύπου στο XML ορισμού. Δεν τον αξιολογεί. Το Excel υπολογίζει το υπολογιζόμενο στοιχείο, πεδίο ή μέλος όταν ανοίγει το αρχείο, με τον ίδιο τρόπο που υπολογίζει κάθε συγκεντρωτικό στοιχείο στο πλέγμα (grid). Το HotXLS γράφει τις οδηγίες, όχι τα αποτελέσματα, οπότε οι τύποι που παρέχετε πρέπει να είναι έγκυροι τύποι συγκεντρωτικού πίνακα στη διάλεκτο του ίδιου του Excel, αναφερόμενοι σε ονόματα πεδίων και στοιχείων όπως θα έκανε το Excel

Πώς εμφανίζετε τις τιμές ως ποσοστό επί του συνόλου;

Ορίζετε τη λειτουργία εμφάνισης τιμών στο πεδίο δεδομένων αντί να μετασχηματίζετε τους αριθμούς μόνοι σας. Το TXLSPivotDataField.ShowDataAs λαμβάνει τον απαριθμητή TXLSPivotShowDataAs, ο οποίος αντικατοπτρίζει τις τιμές OOXML ST_ShowDataAs: xlpsdaNormal, xlpsdaDifference, xlpsdaPercent, xlpsdaPercentDiff, xlpsdaRunTotal, xlpsdaPercentOfRow, xlpsdaPercentOfCol, xlpsdaPercentOfTotal και xlpsdaIndex. Ένα συνηθισμένο τέχνασμα είναι να τοποθετήσετε την ίδια στήλη προέλευσης στον άξονα δεδομένων δύο φορές, μία ως ακατέργαστο άθροισμα και μία ως μερίδιο του γενικού συνόλου, ώστε η αναφορά να δείχνει τόσο τον αριθμό όσο και το βάρος του

var
  Rev, Share: TXLSPivotDataField;
begin
  Rev := Pivot.AddDataFieldByName('Revenue', xlpaSum);

  Share := Pivot.AddDataFieldByName('Revenue', xlpaSum);
  Share.DisplayName := 'Share of total';
  Share.ShowDataAs  := xlpsdaPercentOfTotal;

  // Οι λειτουργίες που σχετίζονται με στοιχεία χρειάζονται μια βάση για σύγκριση. Ένα τρέχον σύνολο
  // προς τα κάτω στο 'Quarter' (ευρετήριο πεδίου προσωρινής μνήμης 3) θα γραφόταν ως:
  //   Share.ShowDataAs := xlpsdaRunTotal;
  //   Share.BaseField  := 3;         // ευρετήριο πεδίου προσωρινής μνήμης πάνω στο οποίο εκτελείται ο μετασχηματισμός
  //   Share.BaseItem   := $7FFD;     // $7FFD = "(All)"
end;

Το xlpsdaPercentOfTotal δεν χρειάζεται βάση επειδή είναι σχετικό με το γενικό σύνολο, αλλά οι λειτουργίες που σχετίζονται με στοιχεία χρειάζονται. Τα xlpsdaDifference, xlpsdaPercentDiff και xlpsdaRunTotal απαιτούν το BaseField (το ευρετήριο πεδίου προσωρινής μνήμης πάνω στο οποίο εκτελείται η σύγκριση) και, για τις μορφές που είναι αγκυροβολημένες σε στοιχεία, ένα ευρετήριο BaseItem, όπου το $7FFD αντιπροσωπεύει τη φρουρό τιμή (All). Το HotXLS γράφει αυτά ως χαρακτηριστικά <dataField showDataAs="percentOfTotal" baseField="N" baseItem="M"/> και, όπως και με τους υπολογιζόμενους τύπους, αφήνει την αριθμητική στο Excel

Ομαδοποίηση ημερομηνιών και αριθμών, και φίλτρα πεδίων σελίδας

Η ομαδοποίηση διαμορφώνεται στο πεδίο προσωρινής μνήμης (cache field), και όχι στο πεδίο του συγκεντρωτικού πίνακα, επειδή αλλάζει τον τρόπο με τον οποίο κατηγοριοποιείται ο τομέας προέλευσης. Ορίστε το HasGroup := True στο πεδίο προσωρινής μνήμης που επιστρέφεται από το Cache.FindFieldByName, και στη συνέχεια επιλέξτε είτε τις παραμέτρους αριθμητικού εύρους (GroupStartNum, GroupEndNum, GroupInterval) είτε τις σημαίες ιεραρχίας ημερομηνιών (GroupByDate με GroupMonths, GroupQuarters, GroupYears και ένα εύρος GroupStartDate / GroupEndDate). Το HotXLS εκπέμπει το αντίστοιχο στοιχείο <fieldGroup><rangePr groupBy="months"/> έτσι ώστε ένα πεδίο ομαδοποιημένο ανά μήνα ή ανά αριθμητική ζώνη πλάτους 1000 να ανοίγει με τον τρόπο που θα το είχε ομαδοποιήσει το Excel

Τα πεδία σελίδας (page fields) είναι τα πτυσσόμενα μενού φίλτρων πάνω από τον πίνακα. Το AddPageField τοποθετεί ένα πεδίο στον άξονα σελίδας, και το PageItemIndex προεπιλέγει ένα μεμονωμένο στοιχείο προσωρινής μνήμης, με προεπιλογή το xlPageItemAll ($7FFD, που σημαίνει (All)). Για να επιτρέψετε στον αναγνώστη να επιλέξει πολλά στοιχεία ταυτόχρονα, ορίστε το MultipleItemSelectionAllowed := True, το οποίο το HotXLS γράφει ως <pivotField multipleItemSelectionAllowed="1"/>. Για κριτήρια πέρα από τη χειροκίνητη επιλογή, κάθε πεδίο φέρει μια πληκτρολογημένη συλλογή Filters που καλύπτει τις οικογένειες ST_FilterType του OOXML — φίλτρα πλήθους, ποσοστού, αθροίσματος, λεζάντας, τιμής και ημερομηνίας — με κάθε καταχώριση να συνδυάζει έναν τύπο φίλτρου με τις τιμές σύγκρισης

Πώς ενημερώνετε μια προσωρινή μνήμη συγκεντρωτικού πίνακα όταν αλλάζει η προέλευση;

Το RefreshPivotCache στο TXLSXWorkbook σαρώνει εκ νέου το εύρος προέλευσης και ξαναχτίζει την προσωρινή μνήμη επί τόπου, κάτι που χρειάζεται μια μαζική ροή εργασίας (batch pipeline) αφού επεξεργαστεί τις υποκείμενες γραμμές. Περάστε το αναγνωριστικό προσωρινής μνήμης (cache id), και η μέθοδος ξαναδιαβάζει κάθε κελί στο εύρος προέλευσης (κεφαλίδα στην πρώτη γραμμή, δεδομένα από τη δεύτερη), ξαναχτίζει τον τομέα κοινόχρηστων στοιχείων (shared-item domain) κάθε πεδίου αφαιρώντας τα διπλότυπα και υπολογίζοντας εκ νέου τα όρια ανά τύπο, και ξαναγράφει τα ευρετήρια στοιχείων ανά εγγραφή. Επιστρέφει 1 σε περίπτωση επιτυχίας και -1 όταν το αναγνωριστικό προσωρινής μνήμης είναι άγνωστο ή το φύλλο προέλευσης λείπει

var
  Rc: Integer;
begin
  // ...τα δεδομένα προέλευσης έχουν αλλάξει από τότε που δημιουργήθηκε ο συγκεντρωτικός πίνακας...
  Sheet := Book.Sheets[1];
  Sheet.Cells[2, 5].Value := 128000;             // ένα διορθωμένο σχήμα εσόδων (Revenue)

  // Ανακατασκευάστε τα κοινόχρηστα στοιχεία και τα ευρετήρια εγγραφών της προσωρινής μνήμης από την προέλευση.
  Rc := Book.RefreshPivotCache(Pivot.CacheId);   // 1 = ενημερώθηκε, -1 = λείπει η προσωρινή μνήμη / προέλευση
  if Rc = 1 then
    Book.SaveAs('sales-pivot-refreshed.xlsx');
end;

Το εννοιολογικό όριο εδώ αξίζει να αναφερθεί ξεκάθαρα. Προτού υπάρξει αυτή η μέθοδος, το HotXLS βασιζόταν στη σημαία refreshOnLoad="1" flag και άφηνε όλη την εργασία ενημέρωσης στο Excel κατά το επόμενο άνοιγμα, κάτι που είναι εντάξει όταν ένας άνθρωπος ανοίγει το αρχείο, αλλά άχρηστο σε μια αυτοματοποιημένη ροή εργασίας (headless pipeline) που πρέπει να παραδώσει σωστά δεδομένα. Το RefreshPivotCache καθιστά την αποθηκευμένη προσωρινή μνήμη τρέχουσα εντός της διεργασίας, και επειδή οι πίνακες συνδέονται με μια προσωρινή μνήμη μέσω αναγνωριστικού, κάθε συγκεντρωτικός πίνακας που αντλείται από αυτήν την προσωρινή μνήμη βλέπει την ενημέρωση. Αυτό που δεν κάνει είναι η διάταξη ή η συγκέντρωση του ορατού πλέγματος (grid) — το Excel εξακολουθεί να υπολογίζει εκ νέου τον αποδοθέντα πίνακα από την ενημερωμένη προσωρινή μνήμη όταν ανοίγει το αρχείο

Τι γράφει το HotXLS και τι υπολογίζει το Excel

Κρατήστε υπόψη τον καταμερισμό εργασίας και τίποτα δεν θα σας εκπλήξει. Το HotXLS γράφει τον ορισμό: παράγει το pivotCacheDefinition, τις εγγραφές προσωρινής μνήμης και το pivotTableDefinition, πλήρη με άξονες, συγκεντρώσεις, υπολογιζόμενα στοιχεία και μέλη, εμφανίσεις τιμών, ομαδοποίηση και φίλτρα. Το Excel είναι η αριθμομηχανή: κατά το άνοιγμα υλοποιεί τις κατηγορίες ομάδων, αξιολογεί τους υπολογιζόμενους τύπους, εφαρμόζει τους μετασχηματισμούς showDataAs και συγκεντρώνει το σώμα των δεδομένων. Οι τιμές που βλέπει ο χρήστης είναι του Excel, παραγόμενες από τις οδηγίες που έγραψε το HotXLS, γι' αυτό και κάθε τύπος και ευρετήριο βάσης πρέπει να είναι σωστά κατά το χρόνο εγγραφής και όχι να ελέγχονται κατά το χρόνο απόδοσης (render)

Δύο περιορισμοί αξίζει να είναι γνωστοί προτού σχεδιάσετε γύρω από αυτήν τη δυνατότητα. Το AddPivotTable αναλύει ένα ορθογώνιο εύρος A1 με προαιρετικό πρόθεμα φύλλου. Οι πηγές ονομασμένων εύρων (named-range) και εξωτερικών βιβλίων εργασίας βρίσκονται εκτός των δυνατοτήτων επίλυσης του builder, αν και οι προσωρινές μνήμες που διαβάζονται από ένα αρχείο που δημιουργήθηκε στο Excel διατηρούν την πηγή ονομασμένου εύρους τους κατά την κυκλική διαδρομή. Επίσης, οι συγκεντρωτικοί πίνακες που έχουν δημιουργηθεί στο Excel και διαβάζονται από το HotXLS από τον δίσκο αναπαράγονται byte προς byte κατά την αποθήκευση, οπότε οι πληκτρολογημένες επεξεργασίες που περιγράφονται εδώ εφαρμόζονται καθαρά σε συγκεντρωτικούς πίνακες που δημιουργείτε σε κώδικα, ενώ οι υπάρχοντες συγκεντρωτικοί πίνακες παραμένουν χωρίς απώλειες. Για την πλευρά εισαγωγής μιας αναφοράς — τα επικυρωμένα κελιά και τους φιλτραρισμένους πίνακες που συνοψίζει ο συγκεντρωτικός πίνακας — δείτε επικύρωση δεδομένων, AutoFilter και δομημένους πίνακες

Το μοντέλο συγκεντρωτικού πίνακα που εμφανίζεται εδώ αποτελεί μέρος του τυπικού HotXLS Delphi Excel Component για Delphi και C++Builder, το οποίο διαβάζει και γράφει τόσο τα πληκτρολογημένα τμήματα συγκεντρωτικών πινάκων XLSX όσο και τις κλασικές εγγραφές BIFF8 από το ίδιο μοντέλο αντικειμένων