Ροή Τεράστιων Αρχείων XLSX στη Delphi Χωρίς Φόρτωσή τους

Ένα υπολογιστικό φύλλο με ένα εκατομμύριο γραμμές και δώδεκα στήλες είναι μια απολύτως συνηθισμένη εξαγωγή από μια εργασία αναφοράς βάσης δεδομένων. Ανοίξτε το με τον συνηθισμένο τρόπο, φορτώνοντας ολόκληρο το βιβλίο εργασίας σε ένα TXLSWorkbook, και η διαδικασία πρέπει να υλοποιήσει (materialise) καθένα από αυτά τα δώδεκα εκατομμύρια κελιά ως ένα ζωντανό αντικείμενο πριν εκτελεστεί η πρώτη σας γραμμή επιχειρησιακής λογικής. Το αρχείο στο δίσκο μπορεί να είναι εξήντα megabytes συμπιεσμένης XML. Το δέντρο αντικειμένων στο οποίο επεκτείνεται είναι αρκετές φορές μεγαλύτερο, και πρέπει να βρίσκεται ολόκληρο στη μνήμη (resident) επειδή το μοντέλο είναι εκ σχεδιασμού τυχαίας προσπέλασης (random-access). Για μια αναφορά που σκοπεύετε να διαβάσετε από πάνω προς τα κάτω και να την πετάξετε, αυτή είναι μεγάλη σπατάλη μνήμης για μια δομή που δεν χρειαστήκατε ποτέ

Υπάρχει μια δεύτερη διαδρομή στο ίδιο αρχείο. Αντί να δημιουργήσετε ένα μοντέλο, σαρώνετε την XML του φύλλου εργασίας μόνο προς τα εμπρός (forward only), ένα κελί τη φορά, και αφήνετε κάθε κελί να προσπεράσει αφού το κοιτάξετε. Τίποτα δεν συσσωρεύεται. Η μνήμη παραμένει σχεδόν σταθερή είτε το φύλλο έχει χίλιες γραμμές είτε δέκα εκατομμύρια, επειδή ο αναγνώστης (reader) δεν κρατά ποτέ περισσότερα από το τμήμα που αναλύει (parsing) εκείνη τη στιγμή συν δυο μικρούς πίνακες αναζήτησης (lookup tables). Αυτό ακριβώς κάνει ο άμεσος αναγνώστης (direct reader) του HotXLS, και το υπόλοιπο αυτού του άρθρου αφορά το γιατί παραμένει μικρός και τι σας δίνει σε αντάλλαγμα

Γιατί το μοντέλο εντός μνήμης δεν κλιμακώνεται

Ένα αρχείο XLSX είναι ένα πακέτο ZIP από τμήματα XML που περιγράφονται από το ECMA-376. Κάθε φύλλο εργασίας είναι το δικό του τμήμα, xl/worksheets/sheetN.xml, και μέσα σε αυτό κάθε γραμμή είναι ένα στοιχείο <row> που περιέχει στοιχεία κελιών <c>. Η κανονική διαδρομή φόρτωσης διαβάζει αυτό το τμήμα και κατασκευάζει ένα διευθυνσιοδοτήσιμο (addressable) αντικείμενο για κάθε κελί, έτσι ώστε αργότερα να μπορείτε να ζητήσετε το Cells[12345, 7] και να λάβετε απάντηση σε σταθερό χρόνο. Η τυχαία προσπέλαση είναι όλο το νόημα ενός μοντέλου βιβλίου εργασίας, και είναι ακριβώς αυτό που κάνει την επεξεργασία, την αξιολόγηση τύπων και τη μορφοποίηση βολικές

Το κόστος είναι ότι η τυχαία προσπέλαση απαιτεί τα πάντα να είναι παρόντα ταυτόχρονα. Δεν μπορείτε να κάνετε ευρετηρίαση (indexing) σε μια δομή που έχετε κατασκευάσει μόνο εν μέρει. Επομένως, η μέγιστη χρήση μνήμης (peak memory) μιας πλήρους φόρτωσης είναι συνάρτηση του αριθμού των κελιών, και σε ένα φύλλο με εκατομμύρια συμπληρωμένα κελιά αυτή η συνάρτηση καταλήγει σε ένα σημείο που η υπηρεσία σας δεν θέλει να βρίσκεται, ειδικά αν πολλές τέτοιες εργασίες εκτελούνται ταυτόχρονα σε ένα κοινόχρηστο μηχάνημα. Όταν το μοτίβο πρόσβασης που πραγματικά χρειάζεστε είναι η διαδοχική (sequential) πρόσβαση, το να πληρώνετε για τυχαία προσπέλαση σημαίνει να πληρώνετε για μια δυνατότητα που δεν θα χρησιμοποιήσετε

Μια σάρωση SAX μόνο προς τα εμπρός που δεν δημιουργεί δέντρο

Ο άμεσος αναγνώστης (direct reader) ανοίγει το πακέτο ZIP και διατρέχει κάθε τμήμα φύλλου εργασίας με έναν pull parser τύπου SAX. SAX εδώ σημαίνει ότι ο αναλυτής (parser) αναφέρει γεγονότα ανάλυσης καθώς τα συναντά, ένα στοιχείο έναρξης (start element), μια αλληλουχία κειμένου (text run), ένα στοιχείο λήξης (end element), και μετά προχωρά. Δεν κρατά κανένα δέντρο κόμβων πίσω του. Ο αναγνώστης παρακολουθεί την τρέχουσα γραμμή και στήλη από τα χαρακτηριστικά r, συλλέγει τον τύπο του κελιού, τον δείκτη στυλ (style index), την τιμή, και το κείμενο του τύπου καθώς καταφθάνουν τα γεγονότα, και όταν δει την ετικέτα κλεισίματος </c> εκπέμπει (emits) ένα κελί και το ξεχνά. Το επόμενο κελί επαναχρησιμοποιεί την ίδια χούφτα τοπικών μεταβλητών

Επειδή τίποτα δεν διατηρείται μεταξύ των κελιών, το αποτύπωμα μνήμης (memory footprint) δεν αυξάνεται με τον αριθμό των κελιών. Αυτή είναι η ιδιότητα που αξίζει να κρατήσετε. Ένα φύλλο διακοσίων γραμμών και ένα φύλλο είκοσι εκατομμυρίων γραμμών κοστίζουν στον αναγνώστη την ίδια μνήμη στο σύστημα, και η διαφορά μεταξύ τους είναι μόνο το πόσο διαρκεί η σάρωση. Εγκαταλείπετε την τυχαία προσπέλαση, το κύριο χαρακτηριστικό του μοντέλου, και σε αντάλλαγμα λαμβάνετε ένα ανώτατο όριο (ceiling) στη μνήμη που ο αριθμός των κελιών δεν μπορεί να διαπεράσει

Τι παραμένει στη μνήμη και γιατί αυτά τα δύο τμήματα

Η σάρωση δεν είναι εντελώς χωρίς κατάσταση (stateless), και οι εξαιρέσεις είναι διδακτικές. Δύο μικροί πίνακες πρέπει να κρατηθούν στη μνήμη για όλη τη διάρκεια, επειδή ένα κελί από μόνο του δεν φέρει αρκετές πληροφορίες για να ερμηνευτεί χωρίς αυτούς

Ο πρώτος είναι ο πίνακας κοινόχρηστων συμβολοσειρών (shared string table). Στο SpreadsheetML, ένα κελί κειμένου δεν αποθηκεύει το δικό του κείμενο. Φέρει το t="s" και ένα αριθμητικό ωφέλιμο φορτίο (payload) που είναι ένας δείκτης στο xl/sharedStrings.xml, μια ενιαία αποδιπλοποιημένη (deduplicated) λίστα κάθε διακριτής συμβολοσειράς στο βιβλίο εργασίας. Αυτή είναι μια καλή ανταλλαγή χώρου για αρχεία όπου οι ίδιες ετικέτες επαναλαμβάνονται σε χιλιάδες γραμμές, αλλά σημαίνει ότι ο αναγνώστης πρέπει να φορτώσει αυτόν τον πίνακα συμβολοσειρών εκ των προτέρων (up front) και να τον κρατήσει στη μνήμη, επειδή οποιοδήποτε κελί οπουδήποτε σε οποιοδήποτε φύλλο μπορεί να αναφέρεται σε οποιαδήποτε καταχώριση (entry) σε αυτόν. Το μέγεθος του πίνακα καθορίζεται από τον αριθμό των διακριτών συμβολοσειρών, όχι από τον αριθμό των κελιών, επομένως παραμένει μετριοπαθές (modest) ακόμη και σε τεράστια φύλλα

Ο δεύτερος είναι η αντιστοίχιση μορφοποίησης αριθμών από το τμήμα των στυλ (styles). Ένα αριθμητικό κελί και ένα κελί ημερομηνίας είναι πανομοιότυπα byte προς byte στο αρχείο: και τα δύο είναι ένας απλός αριθμός, επειδή μια ημερομηνία στο SpreadsheetML είναι απλώς μια σειριακή καταμέτρηση ημερών (serial day count). Το μόνο πράγμα που τα διακρίνει είναι το στυλ του κελιού, το οποίο δείχνει μέσω του cellXfs στο xl/styles.xml σε ένα αναγνωριστικό μορφοποίησης αριθμού. Για να αναφέρει μια ημερομηνία ως ημερομηνία και όχι ως τον ακατέργαστο (raw) σειριακό αριθμό, ο αναγνώστης φορτώνει αυτόν τον πίνακα από στυλ σε μορφοποίηση και τον διατηρεί στη μνήμη. Οτιδήποτε άλλο στο αρχείο, τα πραγματικά δεδομένα των κελιών που αποτελούν τον κύριο όγκο των bytes, προσπερνούν (streams past) χωρίς να αποθηκευτούν

Κάθε κελί αναφέρει ένα είδος (kind) και μια τιμή

Κάθε εκπεμπόμενο κελί φτάνει ως μια εγγραφή (record) TXLSDirectCell. Φέρει τον δείκτη και το όνομα του φύλλου, τη γραμμή και τη στήλη (με έναρξη από το 1), ένα σημασιολογικό Kind, την τιμή Value ως Variant, το κείμενο του τύπου Formula χωρίς το αρχικό σύμβολο του ίσον, και τον ακατέργαστο δείκτη στυλ StyleIndex. Το είδος (kind) είναι ένα από τα xdkNumber, xdkString, xdkBoolean, xdkDate, ή xdkError, ώστε να μπορείτε να κάνετε διακλάδωση ανάλογα με το τι σημαίνει το κελί, αντί να το εξάγετε ξανά από τα χαρακτηριστικά. Ένα κελί τύπου αναφέρει το είδος του αποθηκευμένου αποτελέσματός του (cached result), μαζί με το κείμενο του τύπου (formula text), επομένως ένα υπολογισμένο σύνολο έρχεται ως αριθμός που σας λέει επίσης πώς παρήχθη

type
  TReportScan = class
    procedure OnCell(Sender: TObject; const Cell: TXLSDirectCell;
      var Abort: Boolean);
  end;

procedure TReportScan.OnCell(Sender: TObject; const Cell: TXLSDirectCell;
  var Abort: Boolean);
begin
  case Cell.Kind of
    xdkString:  AccumulateLabel(Cell.Row, Cell.Col, VarToStr(Cell.Value));
    xdkNumber:  AddToTotals(Cell.Col, Double(Cell.Value));
    xdkDate:    NoteWhen(Cell.Row, VarToDateTime(Cell.Value));
    xdkBoolean: FlagRow(Cell.Row, Boolean(Cell.Value));
    xdkError:   LogBadCell(Cell.Row, Cell.Col, VarToStr(Cell.Value));
  end;
end;

Διακρίνοντας μια ημερομηνία από έναν αριθμό

Το ζήτημα των ημερομηνιών αξίζει μια πιο προσεκτική ματιά, επειδή είναι εκεί που οι περισσότεροι απλοϊκοί σαρωτές (scanners) κάνουν λάθος. Δεν υπάρχει τύπος ημερομηνίας σε ένα αριθμητικό κελί. Ένα κελί που περιέχει τη σειριακή τιμή 46000 θα μπορούσε να είναι μια ποσότητα, μια τιμή ή η 17η Φεβρουαρίου 2025, και το αρχείο σας λέει ποιο από όλα είναι μόνο μέσω του αναγνωριστικού μορφοποίησης αριθμού, στο οποίο φτάνετε μέσω του στυλ του κελιού. Το ECMA-376 δεσμεύει ένα μπλοκ ενσωματωμένων αναγνωριστικών μορφοποίησης (built-in format ids) των οποίων η σημασία είναι σταθερή σε κάθε συμμορφούμενο (conforming) παραγωγό, και τα αναγνωριστικά που φέρουν ημερομηνία βρίσκονται σε δύο εύρη (ranges): από 14 έως 22 για τις τυπικές μορφές ημερομηνίας και ώρας, και από 45 έως 47 για τις μορφές παρελθόντος χρόνου (elapsed-time) όπως το [h]:mm:ss. Όταν η ρύθμιση DetectDates είναι ενεργοποιημένη (on), κάτι που ισχύει από προεπιλογή, ο αναγνώστης επιλύει το στυλ κάθε αριθμητικού κελιού στο αναγνωριστικό μορφοποίησής του, και ένα κελί του οποίου το αναγνωριστικό εμπίπτει σε αυτά τα δεσμευμένα εύρη αναφέρεται ως xdkDate με την τιμή του (Value) ήδη μετατρεπόμενη σε TDateTime της Delphi. Οι προσαρμοσμένες μορφές ελέγχονται επίσης, επιθεωρώντας τον κώδικα μορφοποίησης για διακριτικά (tokens) ημερομηνίας και ώρας, αλλά τα δεσμευμένα εύρη αποτελούν την αξιόπιστη ραχοκοκαλιά. Απενεργοποιήστε (off) το DetectDates και ο πίνακας στυλ (styles table) δεν θα φορτωθεί καν, κάθε αριθμητικό κελί θα περάσει ως xdkNumber, και η σάρωση θα είναι ελαφρώς πιο λιτή (fractionally leaner)

Παράλειψη φύλλων και πρόωρη ματαίωση (abort early)

Η διαδοχική σάρωση έχει ένα αθόρυβο πλεονέκτημα (quiet advantage) που η τυχαία προσπέλαση δεν μπορεί να προσφέρει: μπορείτε να σταματήσετε. Το γεγονός (event) OnSheet πυροδοτείται πριν ανοίξει κάθε φύλλο εργασίας, και σας δίνει δύο διακόπτες (switches). Ρυθμίστε το SkipSheet και ολόκληρο αυτό το τμήμα δεν αναλύεται (parsed) ποτέ, που είναι ο τρόπος με τον οποίο σαρώνετε μόνο τα φύλλα που σας ενδιαφέρουν σε ένα βιβλίο εργασίας με πολλά φύλλα χωρίς να πληρώνετε το κόστος για να διαβάσετε τα υπόλοιπα. Ρυθμίστε το Abort (ματαιώνω) και ολόκληρη η σάρωση τερματίζεται αμέσως. Το γεγονός OnCell φέρει το δικό του Abort, ώστε να μπορείτε να σταματήσετε τη στιγμή που βρήκατε αυτό που ψάχνατε, μια συγκεκριμένη γραμμή, μια τιμή-φρουρό (sentinel value), το τέλος ενός μπλοκ κεφαλίδας, χωρίς να διαβάσετε τα υπόλοιπα εκατομμύρια κελιά. Σε μια σάρωση μόνο προς τα εμπρός (forward-only scan), η ματαίωση είναι πραγματικά δωρεάν, επειδή η εργασία που παραλείπετε είναι εργασία που δεν είχε συμβεί ακόμα

procedure TReportScan.OnSheet(Sender: TObject; SheetIndex: Integer;
  const SheetName: WideString; var SkipSheet: Boolean; var Abort: Boolean);
begin
  // Scan only the "Data" sheet; leave the rest unread
  SkipSheet := SheetName <> 'Data';
end;

Καταμέτρηση κελιών χωρίς χειριστή (handler)

Αξίζει να αναφέρουμε μια πρόσφατη βελτίωση επειδή μετατρέπει μια κοινή ερώτηση σε μια μοναδική φθηνή κλήση (single cheap call). Ο αναγνώστης μετράει κάθε συμπληρωμένο (populated) κελί που προσπερνά, και το κάνει αυτό είτε έχει επισυναφθεί ένας χειριστής (handler) OnCell είτε όχι. Παλαιότερα, χωρίς καθορισμένο χειριστή, ο αριθμός των συμπληρωμένων κελιών επέστρεφε ως μηδέν, αφού η καταμέτρηση ήταν μια παρενέργεια (side effect) της εκπομπής (emitting). Τώρα η καταμέτρηση είναι ανεξάρτητη από την εκπομπή. Αυτό σημαίνει ότι μπορείτε να κάνετε μία ερώτηση, πόσα συμπληρωμένα κελιά περιέχει πραγματικά αυτό το βιβλίο εργασίας, και να λάβετε την απάντηση στην τιμή μιας σάρωσης χωρίς καθόλου callbacks. Τα ReadFile και ReadStream επιστρέφουν και τα δύο αυτό το σύνολο ως Int64, και ο ίδιος αριθμός είναι διαθέσιμος αργότερα μέσω της ιδιότητας CellCount. Η επιστροφή του -1 σηματοδοτεί ότι το αρχείο δεν μπόρεσε να ανοιχτεί ή δεν είναι πακέτο OOXML

var
  Reader: TXLSDirectReader;
  Populated: Int64;
begin
  Reader := TXLSDirectReader.Create;
  try
    // No OnCell handler: a pure populated-cell census, still near-constant memory
    Populated := Reader.ReadFile('quarterly_export.xlsx');
    if Populated < 0 then
      raise Exception.Create('Not a readable XLSX package')
    else
      Writeln(Format('%d populated cells (CellCount = %d)',
        [Populated, Reader.CellCount]));
  finally
    Reader.Free;
  end;
end;

Για την πλήρη σάρωση, επισυνάπτετε (attach) τον χειριστή και καλείτε το ReadFile με τον ίδιο ακριβώς τρόπο. Η αντίθεση (contrast) με μια πλήρη φόρτωση είναι όλη η ουσία: ενώ η φόρτωση του quarterly_export.xlsx σε ένα βιβλίο εργασίας θα επέκτεινε κάθε κελί σε ένα αντικείμενο στη μνήμη και θα τα κρατούσε όλα (hold the lot), ο άμεσος αναγνώστης διατηρεί μόνο τις κοινόχρηστες συμβολοσειρές και τον πίνακα στυλ, ενώ τα δώδεκα εκατομμύρια κελιά ρέουν μέσα από το OnCell σας ένα προς ένα. Η αριθμητική που εκτελέστηκε ανά κελί δεν αφήνει τίποτα πίσω της, επομένως η μέγιστη μνήμη καθορίζεται από τον αριθμό των διακριτών συμβολοσειρών του βιβλίου εργασίας, όχι από τον αριθμό των γραμμών του

Ο άμεσος αναγνώστης είναι το σωστό εργαλείο όταν η εργασία (job) είναι η ανάγνωση ενός μεγάλου βιβλίου εργασίας μία φορά και η εξαγωγή ή η σύνοψή του. Αντ' αυτού, όταν χρειάζεστε την τυχαία προσπέλαση του πλήρους μοντέλου αλλά θέλετε να συμπεριφέρεται καλά σε μεγάλα αρχεία (behave on big files), η ρύθμιση (tuning) στις σημειώσεις μας για την απόδοση μεγάλων βιβλίων εργασίας στη Delphi καλύπτει αυτή τη διαδρομή. Και όταν η κατεύθυνση αντιστραφεί, με παραγωγή μεγάλης εξόδου αντί για κατανάλωσή της, ο οδηγός βήμα προς βήμα (walkthrough) εγγραφής ροής (streaming-write) για εργασίες δέσμης διακομιστή (server batch jobs) εφαρμόζει την ίδια πειθαρχία σταθερής μνήμης (constant-memory discipline) στην εγγραφή. Και τα τρία αποστέλλονται (ship) ως μέρος του στοιχείου HotXLS για Delphi και C++Builder, μαζί με τα API ανάγνωσης, εγγραφής, τύπων και μορφοποίησης που καλύπτονται αλλού σε αυτό το ιστολόγιο