Σχεδόν κάθε μέρος της παλιάς δυαδικής μορφής του Excel είναι μια μεμονωμένη εγγραφή (record) με καθαρό τύπο δύο byte και μήκος δύο byte. Ένα κελί είναι ένα LABELSST ή ένα NUMBER. Μια συγχωνευμένη περιοχή είναι ένα MERGEDCELLS. Μπορείτε να διαβάσετε το μεγαλύτερο μέρος ενός φύλλου εργασίας διασχίζοντας τις εγγραφές μία προς μία και εκτελώντας ενέργειες με βάση τη λέξη τύπου. Οι συγκεντρωτικοί πίνακες (PivotTables) σπάνε αυτόν τον ρυθμό. Ένας μεμονωμένος συγκεντρωτικός πίνακας δεν είναι απλώς μια εγγραφή, είναι ένα μικρό πρόγραμμα αποτελούμενο από δεκάδες συνεργαζόμενες εγγραφές διασκορπισμένες σε δύο διαφορετικά σημεία στην ίδια ροή σύνθετου εγγράφου OLE (OLE compound document stream), και οι σχέσεις μεταξύ τους είναι θέσης, bit-packed και ασυγχώρητες. Αυτή είναι η δομή που οι περισσότεροι αναγνώστες BIFF8 είτε παρακάμπτουν εντελώς είτε διατηρούν ως αδιαφανή byte, επειδή η συγγραφή μιας τέτοιας δομής από το μηδέν σημαίνει αναπαραγωγή κάθε διασταυρούμενης αναφοράς που διατηρεί το ίδιο το Excel.
Ο λόγος που ένας συγκεντρωτικός πίνακας είναι δύσκολος είναι ότι πρόκειται στην πραγματικότητα για δύο αντικείμενα κολλημένα μαζί. Υπάρχει το pivot cache, ένα αυτοτελές στιγμιότυπο των δεδομένων προέλευσης με τη δική του υποροή (substream), και υπάρχει η προβολή πίνακα (table view), η διάταξη που καθορίζει ποια πεδία κάθονται σε ποιον άξονα. Το cache και η προβολή αναφέρονται μεταξύ τους με βάση τον δείκτη. Αν κάνετε λάθος σε έναν δείκτη, το αρχείο ανοίγει με σφάλμα ανανέωσης ή με ένα σιωπηλά άδειο πλέγμα.
Το pivot cache είναι μια δική του υποροή
Το cache ζει στη ροή workbook globals ως πλήρης υποροή BIFF, η οποία οριοθετείται από μια εγγραφή BOF της οποίας ο τύπος εγγράφου είναι 0x0006 (η τιμή που επισημαίνει ένα pivot cache, σε αντίθεση με το 0x0005 για το workbook ή το 0x0010 για ένα φύλλο εργασίας) και κλείνει από την αντίστοιχη EOF. Μέσα σε αυτό το πλαίσιο η δομή είναι σταθερή. Μια εγγραφή SXDB είναι η κεφαλίδα του cache. Μεταφέρει το πλήθος των εγγραφών, τον αριθμό των πεδίων cache και το αναγνωριστικό ροής που θα αναφέρει η προβολή πίνακα για να συνδεθεί με αυτό το cache. Κάθε στήλη προέλευσης συνεισφέρει μια εγγραφή ορισμού πεδίου SXFDB ακολουθούμενη από μια SXFDBType που την ταξινομεί, και στη συνέχεια τις μοναδικές τιμές που έλαβε αυτή η στήλη, οι οποίες εκπέμπονται ως μία εγγραφή τυποποιημένου στοιχείου ανά διακριτή τιμή.
Οι εγγραφές στοιχείων είναι το σημείο όπου το cache δικαιολογεί την ύπαρξή του. Μια τιμή κειμένου γίνεται SXSTRING, μια αριθμητική τιμή SXNUM, μια λογική τιμή SXBOOLEAN, και ένα σφάλμα τύπου SXERR. Το cache δεν αποθηκεύει το πλέγμα προέλευσης, αποθηκεύει τις διακριτές τιμές ανά πεδίο συν έναν πίνακα δεικτών που λέει, για την εγγραφή n, ποιο διακριτό στοιχείο έλαβε κάθε πεδίο. Γι' αυτό η δημιουργία ενός συγκεντρωτικού πίνακα μέσω προγραμματισμού δεν είναι θέμα αντιγραφής κελιών. Πρέπει να σαρώσετε το εύρος προέλευσης, να συμπεράνετε τον τύπο κάθε πεδίου από τις τιμές που περιέχει, να αφαιρέσετε τα διπλότυπα σε μια λίστα τυποποιημένων στοιχείων και να καταγράψετε κάθε σειρά ως μια πλειάδα (tuple) δεικτών στοιχείων. Το HotXLS κάνει ακριβώς αυτό: μια πλήρως αριθμητική στήλη εκπέμπεται με στοιχεία SXNUM, μια στήλη μικτού κειμένου γίνεται στοιχεία SXSTRING, και οι ημερομηνίες μεταφέρονται ως σειριακές τιμές μέσω της ίδιας αριθμητικής διαδρομής.
SXDBB και το bit-packing που το κάνει ενδιαφέρον
Ο πίνακας δεικτών ανά εγγραφή είναι το μόνο τεχνικά πιο περίεργο μέρος ολόκληρης της δομής, και ζει στην εγγραφή SXDBB. Η απλή κωδικοποίηση θα αποθήκευε τον δείκτη στοιχείου κάθε πεδίου ως μια λέξη 16 bit. Το Excel δεν το κάνει αυτό. Συσκευάζει τον δείκτη κάθε πεδίου σε ακριβώς τον αριθμό των bit που απαιτούνται για τη διευθυνσιοδότηση των στοιχείων αυτού του πεδίου, και όχι περισσότερο. Το πλάτος είναι ceil(log2(itemCount + 1)) bit. Το + 1 έχει σημασία: η επιπλέον τιμή είναι ένας φύλακας (sentinel) που σημαίνει "κενό, καμία τιμή για αυτό το πεδίο σε αυτήν την εγγραφή", οπότε ένα πεδίο με τρία διακριτά στοιχεία πρέπει να αντιπροσωπεύει τέσσερις καταστάσεις και επομένως παίρνει δύο bit, όχι το ένα bit που θα υποδείκνυαν τρία στοιχεία μόνο. Ένα πεδίο χωρίς κανένα στοιχείο συνεισφέρει μηδέν bit και παραλείπεται εντελώς κατά τη συσκευασία.
Τα bit για μία εγγραφή συνενώνονται σε όλα τα πεδία, και στη συνέχεια η επόμενη εγγραφή ξεκινά σε ένα νέο όριο byte. Οι εγγραφές είναι ευθυγραμμισμένες σε byte, όχι bit-packed από άκρη σε άκρη, γεγονός που καθιστά την τυχαία πρόσβαση στον πίνακα εφικτή με κόστος λίγα bit συμπλήρωσης (padding) ανά σειρά. Η συσκευασία μέσα σε ένα byte γίνεται με το λιγότερο σημαντικό bit πρώτο (least-significant-bit first). Μόλις αποδεχτείτε αυτούς τους δύο κανόνες, ο κωδικοποιητής είναι μια απλή αντλία bit, και ο αποκωδικοποιητής είναι ο καθρέφτης του.
// Width of one field's index in the SXDBB stream.
// citmTotal distinct items need ceil(log2(citmTotal + 1)) bits,
// the +1 reserving a "blank" sentinel value.
function BitsForFieldItems(itemCount: Integer): Integer;
var
capacity: Integer;
begin
Result := 0;
if itemCount <= 0 then
Exit; // empty field contributes zero bits
Result := 1;
capacity := 2;
while capacity < itemCount + 1 do
begin
Inc(Result);
capacity := capacity * 2;
end;
end;Ο λόγος που αυτή η λεπτομέρεια δεν μπορεί να αγνοηθεί είναι η οροφή των 8224 byte σε μια μεμονωμένη εγγραφή BIFF. Κάθε εγγραφή στη μορφή αυτή, συμπεριλαμβανομένων των εγγραφών pivot, πρέπει να χωράει το ωφέλιμο φορτίο της σε το πολύ 8224 byte, και ένα ενεργό pivot cache με χιλιάδες σειρές προέλευσης θα ξεπεράσει αυτό το όριο πολύ πριν εκπέμψει κάθε σειρά. Έτσι, ο πίνακας δεικτών χωρίζεται. Το HotXLS περιορίζει το σώμα μιας μεμονωμένης εγγραφής SXDBB στα 8220 byte, που είναι το όριο εγγραφής των 8224 byte μείον την κεφαλίδα εγγραφής τεσσάρων byte για τον τύπο και το μήκος, το διαιρεί με το πλάτος σε byte μιας συσκευασμένης εγγραφής για να μάθει πόσες ολόκληρες σειρές χωρούν, και στη συνέχεια εκπέμπει όσες συνεχόμενες εγγραφές SXDBB απαιτεί ο αριθμός των σειρών. Κάθε συνέχεια ξεκινά καθαρά σε ένα όριο εγγραφής, οπότε καμία σειρά δεν κόβεται ποτέ σε δύο εγγραφές. Ένας αναγνώστης που γνωρίζει το πλάτος bit ανά εγγραφή μπορεί να διασχίσει κάθε SXDBB στη σειρά σαν να ήταν ένας ενιαίος πίνακας bit.
Η προβολή διάταξης: SXLI για το σώμα, SXPI για τη σελίδα
Με το cache κατασκευασμένο, η προβολή πίνακα είναι το δεύτερο μισό. Ο πυρήνας της είναι τα στοιχεία γραμμής άξονα (axis line items), οι σειρές του σώματος του pivot που απαριθμούν κάθε συνδυασμό τιμών πεδίου γραμμής και πεδίου στήλης που σχεδιάζει ο πίνακας. Αυτά μεταφέρονται σε εγγραφές SXLI (τύπος εγγραφής 0x00B5, περιγράφεται στο [MS-XLS] §2.4.275). Μία εγγραφή SXLI κρατά πολλές γραμμές, πάλι μέχρι το όριο των 8224 byte να επιβάλει μια νέα εγγραφή, και χρησιμοποιεί ένα μικρό κόλπο συμπίεσης: κάθε γραμμή αποθηκεύει μόνο πώς διαφέρει από τη γραμμή από πάνω της, εκφρασμένη ως πλήθος κοινού προθέματος, οπότε ένας βαθιά φωλιασμένος άξονας δεν επαναλαμβάνει τις τιμές του εξωτερικού πεδίου σε κάθε σειρά. Η γραμμή γενικού συνόλου και η πρώτη γραμμή οποιασδήποτε εγγραφής επαναφέρουν πάντα αυτό το πλήθος προθέματος στο μηδέν, έτσι ώστε ένας αναγνώστης να μην χρειάζεται ποτέ να κοιτάξει πίσω από ένα όριο εγγραφής για να ανακατασκευάσει μια γραμμή.
Ο άξονας σελίδας, τα πτυσσόμενα φίλτρα που βρίσκονται πάνω από έναν συγκεντρωτικό πίνακα, είναι μια ξεχωριστή εγγραφή. Η SXPI (τύπος εγγραφής 0x00B6, [MS-XLS] §2.4.276) μεταφέρει μία καταχώριση δέκα byte ανά πεδίο σελίδας: τον δείκτη πεδίου pivot isxvd, το επιλεγμένο στοιχείο cache iCache, μια λέξη θέσης ipos και ένα παλαιό αναγνωριστικό αντικειμένου objId. Η τιμή iCache είναι αυτή που πρέπει να προσέξετε. Ένα πεδίο σελίδας που δείχνει "(Όλα)", χωρίς να φιλτράρει τίποτα, αποθηκεύει τον φύλακα 0x7FFD αντί για έναν πραγματικό δείκτη στοιχείου. Ένας συγκεντρωτικός πίνακας που έχει κατασκευαστεί μέσω προγραμματισμού ανοίγει με κάθε πεδίο σελίδας ρυθμισμένο στο "(Όλα)" μέχρι ο καλών να προεπιλέξει ένα στοιχείο, οπότε ο δείκτης cache αυτού του στοιχείου αντικαθιστά τον φύλακα και το Excel ανοίγει με το φίλτρο ήδη εφαρμοσμένο. Δίπλα σε αυτά βρίσκονται οι υποστηρικτικές εγγραφές που περιγράφουν μεμονωμένα πεδία και τη μορφοποίησή τους, SXVD και SXVDEx για ορισμούς προβολής πεδίων, SXIVD για τις λίστες δεικτών πεδίων που ταξινομούν κάθε άξονα, και SXFormat για τη μορφοποίηση αριθμών, με το καθένα να δείχνει πίσω στο ίδιο cache στο οποίο αναφέρονται οι γραμμές του σώματος.
Δύο συγγραφείς σε έναν: ακατέργαστα blobs και το τυποποιημένο μοντέλο
Υπάρχει ένας δομικός λόγος για τον οποίο το HotXLS διατηρεί δύο εντελώς ξεχωριστές διαδρομές για τη συγγραφή ενός συγκεντρωτικού πίνακα, και αυτός προέρχεται απευθείας από τις απαιτήσεις της πιστότητας των δεδομένων. Όταν ένα βιβλίο εργασίας διαβάζεται από τον δίσκο, οι εγγραφές pivot γράφτηκαν από το Excel ή από κάποιον άλλο παραγωγό, και μπορεί να χρησιμοποιούν παραλλαγές εγγραφών, ιδιοτροπίες ταξινόμησης ή εγγραφές επέκτασης που κανένας τρίτος συγγραφέας δεν μοντελοποιεί πλήρως. Το μόνο ασφαλές πράγμα που μπορεί να γίνει με αυτά τα byte είναι να επιστραφούν ανέπαφα. Έτσι, ένας συγκεντρωτικός πίνακας που προήλθε από αρχείο επισημαίνεται με FromRawBlobs = True, και κατά την αποθήκευση ο συγγραφέας αναπαράγει τα διατηρημένα blobs εγγραφών verbatim. Τίποτα δεν αναγεννάται, τίποτα δεν επανερμηνεύεται, και η διαδικασία round-trip μέσω ανοίγματος και αποθήκευσης είναι σταθερή σε επίπεδο byte.
Ένας συγκεντρωτικός πίνακας που κατασκεύασε το ίδιο το πρόγραμμα είναι η αντίθετη περίπτωση. Δεν υπάρχουν αρχικά byte προς διατήρηση, παρά μόνο το τυποποιημένο μοντέλο αντικειμένων: ένα TXLSPivotCache με τα πεδία και τις λίστες στοιχείων του, και ένα TXLSPivotTable με τις αναθέσεις αξόνων του. Αυτός ο πίνακας επισημαίνεται με FromRawBlobs = False, και ο συγγραφέας τον σειριοποιεί με τον δύσκολο τρόπο, εκπέμποντας μια νέα υποροή cache BOF = 0x0006, συσκευάζοντας τον πίνακα δεικτών SXDBB από τους δείκτες στοιχείων που κρατά το τυποποιημένο μοντέλο, και τοποθετώντας τις εγγραφές SXLI και SXPI από τη διαμόρφωση του άξονα. Η σημαία είναι αυτή που επιτρέπει και στους δύο τύπους να συνυπάρχουν σε ένα βιβλίο εργασίας. Χωρίς αυτήν, ένας μεμονωμένος συγγραφέας θα έπρεπε είτε να απορρίψει την πιστότητα των αναγνωσμένων πινάκων είτε να αρνηθεί να δημιουργήσει νέους. Τυχόν εγγραφές επέκτασης ειδικές για τον παραγωγό που μετέφερε ένας αναγνωσμένος πίνακας διατηρούνται ως συμπληρωματικές εγγραφές, προσβάσιμες μέσω της λίστας SupplementalRecords του πίνακα, έτσι ώστε ένας πίνακας που επιθεωρείται μέσω του τυποποιημένου μοντέλου να μην χάνει τα μέρη που το μοντέλο δεν περιγράφει.
Δημιουργία συγκεντρωτικού πίνακα σε κώδικα
Όλος ο παραπάνω μηχανισμός βρίσκεται πίσω από μία κλήση. Η AddPivotTable λαμβάνει το εύρος προέλευσης σε σημειογραφία A1, το κελί προορισμού όπου αγκυρώνεται η επάνω αριστερή γωνία του πίνακα, και ένα όνομα. Αναλύει το εύρος, το σαρώνει για να συμπεράνει τους τύπους πεδίων και να χτίσει το cache (επαναχρησιμοποιώντας ένα υπάρχον cache εάν ένας άλλος πίνακας συνδέεται ήδη στο ίδιο εύρος), και επιστρέφει ένα τυποποιημένο TXLSPivotTable με ένα πεδίο ανά στήλη προέλευσης, με κάθε πεδίο αρχικά εκτός άξονα. Στη συνέχεια, τοποθετείτε τα πεδία στους άξονες και επιλέγετε μια συγκέντρωση (aggregation). Η υπογραφή είναι ακριβώς αυτή, και το cache, η συσκευασία SXDBB και οι εγγραφές προβολής παράγονται όλες για εσάς κατά την αποθήκευση.
uses
lxHandle, lxPivot;
var
Book : TXLSWorkbook;
Sheet: IXLSWorkSheet;
Pivot: TXLSPivotTable;
begin
Book := TXLSWorkbook.Create;
try
Book.Open('Sales.xls');
Sheet := Book.Sheets[1];
// Source A1:E500 on 'Data'; anchor the pivot at row 3, col 1.
Pivot := Sheet.AddPivotTable('Data!$A$1:$E$500', 3, 1, 'SalesByRegion');
if Pivot <> nil then
begin
Pivot.AddRowField('Region');
Pivot.AddColumnField('Quarter');
Pivot.AddDataFieldByName('Revenue', xlpaSum);
end;
Book.SaveAs('Sales-Pivot.xls');
finally
Book.Free;
end;
end;Η πρώτη σειρά του εύρους προέλευσης διαβάζεται ως η κεφαλίδα που ονομάζει τα πεδία cache, οπότε η AddRowField('Region') ταιριάζει με μια στήλη βάσει του κειμένου κεφαλίδας της και όχι βάσει θέσης. Επειδή ο επιστρεφόμενος πίνακας είναι ένα τυποποιημένο μοντέλο με FromRawBlobs = False, ο συγγραφέας ακολουθεί τη διαδρομή από το μηδέν: χτίζει ένα αυτοτελές cache που δεν εξαρτάται από το αν το εύρος προέλευσης είναι ακόμα παρόν κατά τον χρόνο ανανέωσης, που είναι ακριβώς η ιδιότητα που θέλετε όταν ο συγκεντρωτικός πίνακας θα αποσταλεί σε έναν παραλήπτη που μπορεί να μετακινήσει ή να διαγράψει τα υποκείμενα δεδομένα.
Η ανάγνωση και η εναρμόνιση των εγγραφών pivot και cache ενός αρχείου που δεν παρήγαγες εσύ, συμπεριλαμβανομένης της διαδρομής διατήρησης raw-blob, καλύπτεται στον οδηγό ελέγχου βιβλίου εργασίας και πάγκου εργασίας μετατροπής. Όταν το εύρος προέλευσης εκτείνεται σε δεκάδες χιλιάδες σειρές και η ροή SXDBB καλύπτει πολλές συνεχόμενες εγγραφές, οι τεχνικές στις σημειώσεις απόδοσης μεγάλων βιβλίων εργασίας εμποδίζουν τη δημιουργία του cache από το να κυριαρχήσει στον χρόνο εκτέλεσης. Και τα δύο συνδυάζονται με τον pivot writer που παρέχεται στο HotXLS spreadsheet component για Delphi και C++Builder, μαζί με τα API κελιών, τύπων, γραφημάτων και μορφοποίησης που καλύπτονται σε άλλα σημεία αυτού του ιστολογίου.