Απόδοση μεγάλων βιβλίων εργασίας Excel στο Delphi με HotXLS

Όταν μια εξαγωγή 300.000 γραμμών ξεπερνά το όριο μνήμης της, ο αριθμός γραμμών συνήθως παίρνει την ευθύνη. Ο αριθμός γραμμών είναι συνήθως αθώος. Τα ακριβά κομμάτια ενός μεγάλου βιβλίου εργασίας είναι αυτά που δημιουργούνται ως παράπλευρη συνέπεια: ένα pool στιλ που μεγαλώνει κατά μία καταχώριση ανά κελί επειδή η μορφοποίηση προστέθηκε μέσα στον βρόχο, XML φύλλου εργασίας συναρμολογημένο ως μία γιγαντιαία συμβολοσειρά κατά τη στιγμή της αποθήκευσης, ένα εκατομμύριο πανομοιότυπα σώματα τύπων αποθηκευμένα ένα προς ένα. Το HotXLS, η εγγενής βιβλιοθήκη Delphi της losLab για αρχεία XLS και XLSX, σας δίνει έναν συγκεκριμένο μοχλό για καθένα από αυτά τα κόστη. Κανένας τους δεν είναι ενεργοποιημένος από προεπιλογή, επειδή καθένας αλλάζει έναν συμβιβασμό, οπότε το να ξέρετε ποιος μοχλός ταιριάζει με ποιο σύμπτωμα είναι η πραγματική δεξιότητα απόδοσης

Πού ξοδεύει μνήμη ένα μεγάλο βιβλίο εργασίας

Υπάρχουν δύο ξεχωριστά καθεστώτα μνήμης για να σκεφτείτε. Κατά τη δημιουργία, το εν μνήμη μοντέλο κελιών μεγαλώνει με κάθε κελί που αγγίζετε: τιμές, μορφές, και τύποι γίνονται όλα αντικείμενα ή καταχωρίσεις pool. Κατά την αποθήκευση, η προεπιλεγμένη διαδρομή XLSX επιπλέον αποδίδει το XML κάθε φύλλου εργασίας σε μια ευρεία συμβολοσειρά πριν το συμπιέσει μέσα στο δοχείο zip, οπότε η αιχμή χρήσης είναι το μοντέλο συν η σειριοποιημένη μορφή του μεγαλύτερου φύλλου. Μια εργασία που επιβιώνει τον βρόχο κατασκευής και έπειτα πεθαίνει μέσα στην SaveAs χτυπά το δεύτερο καθεστώς, όχι το πρώτο, και η λύση για το ένα δεν κάνει τίποτα για το άλλο

Δύο καθεστώτα μνήμης σε μια εργασία μεγάλου βιβλίου εργασίας HotXLS Delphi: το μοντέλο κελιών στη μνήμη που χτίζει ο βρόχος παραγωγής, συν τη σειριοποιημένη συμβολοσειρά XML του μεγαλύτερου φύλλου κατά την προεπιλεγμένη αποθήκευση, την οποία αφαιρεί το StreamingWrite — Ο βρόχος κατασκευής και η κλήση αποθήκευσης αποτυγχάνουν σε δύο διαφορετικά καθεστώτα μνήμης, οπότε η StreamingWrite επιπεδώνει μόνο την αιχμή ώρας αποθήκευσης ενώ η μνήμη διαδρομής κατασκευής χρειάζεται τα μοχλιά δεξαμενής στυλ και callback

Το μέγεθος αρχείου ακολουθεί έναν σχετικό κανόνα: τα κελιά είναι μόνο ένας συντελεστής, δίπλα σε στιλ, κοινόχρηστες συμβολοσειρές, τύπους, εικόνες και σχόλια. Ένα πέρασμα ελέγχου με την ForEachCell και τις μετρήσεις της συλλογής ανά φύλλο σάς λέει ποιος πόρος πράγματι κυριαρχεί σε ένα προβληματικό αρχείο πριν βελτιστοποιήσετε το λάθος. Μια λεπτομέρεια μέτρησης: η Sheet.Cells.Count στην πλευρά XLSX αναφέρει τον αριθμό των στιγμιοτυπωμένων κελιών στην αραιή αποθήκη, όχι την περιοχή του χρησιμοποιημένου εύρους. Ένα φύλλο του οποίου τα δεδομένα καταλαμβάνουν ένα ορθογώνιο 1000 επί 50 με τα μισά κελιά κενά μετρά περίπου 25.000, όχι 50.000. Αυτή η διάκριση έχει σημασία όταν συγκρίνετε το «τεράστιο» αρχείο ενός πελάτη με τα δικά σας fixtures, επειδή η περιοχή χρησιμοποιημένου εύρους και ο πραγματικός πληθυσμός κελιών μπορούν να διαφέρουν κατά μία τάξη μεγέθους σε αραιές οικονομικές διατάξεις

Το StreamingWrite διορθώνει τη διαδρομή αποθήκευσης, όχι τη διαδρομή κατασκευής

Ο ορισμός της TXLSXWorkbook.StreamingWrite := True αλλάζει την SaveAs σε έναν streaming σειριοποιητή που γράφει το XML φύλλου εργασίας απευθείας μέσα στο stream zip, εξαλείφοντας το ενδιάμεσο συμβολοσειράς ανά φύλλο. Η προεπιλογή της είναι False για συμβατότητα συμπεριφοράς, και η ενεργοποίησή της είναι μια αλλαγή μίας γραμμής:

Book := TXLSXWorkbook.Create;
try
  Sheet := Book.Sheets.Add('Bulk');
  for R := 1 to 100000 do
  begin
    Sheet.Cells[R, 1].Value := R;
    Sheet.Cells[R, 2].Value := 'Row ' + IntToStr(R);
    Sheet.Cells[R, 3].Value := R * 1.5;
  end;
  Book.StreamingWrite := True;   // το XML φύλλου ρέει μέσα στο δοχείο zip
  Book.SaveAs('bulk.xlsx');
finally
  Book.Free;
end;

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

Pools στιλ: πρόσθεσε μία φορά, επαναχρησιμοποίησε τον δείκτη

Η μορφοποίηση XLSX στο HotXLS βασίζεται σε pools: οι Book.Fonts.Add(...), Fills.AddSolid(...) και Borders.Add(...) επιστρέφουν έναν δείκτη pool που ξεκινά από το μηδέν, στον οποίο αναφέρονται τα κελιά. Η κλήση της Fonts.Add με πανομοιότυπες παραμέτρους μέσα σε έναν βρόχο αφαιρεί τα διπλότυπα, οπότε σπαταλά χρόνο και όχι χώρο. Η Alignments.Add συμπεριφέρεται διαφορετικά: επιστρέφει ένα φρέσκο αντικείμενο ανά κλήση, οπότε η δημιουργία στοίχισης ανά κελί μεγαλώνει το pool γραμμικά με τον αριθμό γραμμών. Μία συνήθεια καλύπτει και τις δύο περιπτώσεις. Επιλύστε κάθε δείκτη pool μία φορά, έξω από τον βρόχο, και αναθέστε δείκτες μέσα σε αυτόν

Σύγκριση χρήσης δεξαμενής στυλ HotXLS Delphi: ένα φρέσκο αντικείμενο Alignments.Add που δημιουργείται μία φορά ανά γραμμή μεγαλώνει τη δεξαμενή γραμμικά, ενώ ένας ανυψωμένος δείκτης Fonts.Add που επιλύεται μία φορά πάνω από τον βρόχο επαναχρησιμοποιείται από κάθε κελί με τον μηδενικό δείκτη μετατοπισμένο κατά ένα — Επιλύστε κάθε δείκτη γραμματοσειράς, γεμίσματος, περιγράμματος και στοίχισης μία φορά έξω από τον βρόχο, και μετά αναθέστε εκείνον τον δείκτη δεξαμενής βάσης 0 μετατοπισμένο κατά ένα μέσα σε αυτόν

// βγάλτε τις αναζητήσεις pool έξω από τον καυτό βρόχο
HeaderFont := Book.Fonts.Add('Calibri', 11, True, False);   // δείκτης pool που ξεκινά από το μηδέν
for C := 1 to 24 do
  Sheet.Cells[1, C].FontIndex := HeaderFont + 1;            // τα κελιά αποθηκεύουν δείκτη που ξεκινά από το ένα· 0 = προεπιλογή

Το + 1 δεν είναι τυπογραφικό λάθος και η παράλειψή του είναι το κλασικό σφάλμα που παράγει συμπτώματα εδώ: τα pools μοιράζουν δείκτες που ξεκινούν από το μηδέν, ενώ οι ιδιότητες στην πλευρά του κελιού αντιμετωπίζουν το 0 ως «προεπιλογή», οπότε κάθε δείκτης pool πρέπει να μετατοπίζεται κατά ένα κατά την ανάθεση. Κάντε το λάθος λόγω παράλειψης και οι κεφαλίδες σας αποδίδονται σιωπηλά στην προεπιλεγμένη γραμματοσειρά του βιβλίου εργασίας, ένα ελάττωμα που κανείς δεν προσέχει μέχρι την αναθεώρηση branding

Αντικαταστήστε την κίνηση Variant ανά κελί με callbacks γραμμής

Κάθε Sheet.Cells[R, C].Value := X εμπλέκει μια αναζήτηση-ή-δημιουργία κελιού συν μια ανάθεση Variant. Σε μερικές εκατοντάδες χιλιάδες κελιά, αυτό το overhead ανά πρόσβαση γίνεται μετρήσιμο στα profiles. Το HotXLS προσφέρει μαζικά APIs callback και στις δύο προσόψεις (ForEachCell και ForEachRow για ανάγνωση, WriteCells και WriteRows για εγγραφή) που μετακινούν την επανάληψη μέσα στη μηχανή και παραδίδουν στον κώδικά σας ολόκληρες γραμμές τη φορά:

procedure TLedgerExport.FillRow(Sender: TObject;
  SheetIndex, Row, FirstCol, LastCol: Integer;
  var Values: Variant; var Skip: Boolean; var Cancel: Boolean);
begin
  if Row > FCount then
  begin
    Cancel := True;     // σταμάτησε ολόκληρη την εγγραφή
    Exit;
  end;
  Values := VarArrayOf([FRows[Row - 1].Account,
                        FRows[Row - 1].PostedOn,
                        FRows[Row - 1].Amount]);
end;

// μία κλήση μηχανής αντί για εκατοντάδες χιλιάδες προσπελάσεις ιδιοτήτων
Sheet.WriteRows(1, 1, FCount, 3, FillRow);

Η σημαία Skip του callback αφήνει μια γραμμή ανέγγιχτη χωρίς να ματαιώνει, και η Cancel τερματίζει την επεξεργασία νωρίς, κάτι που είναι χρήσιμο όταν η πηγή είναι ένας reader του οποίου το μήκος ανακαλύπτετε καθώς προχωράτε. Συνδυάστε την WriteRows για την κατασκευή με την StreamingWrite για την αποθήκευση και η διαδρομή δημιουργίας δεν έχει πλέον κανένα καυτό σημείο ανά κελί

Μοχλοί από την πλευρά ανάγνωσης στην πρόσοψη XLS

Τα μεγάλα παλαιά αρχεία .xls έχουν το δικό τους σύνολο εργαλείων. Η _DisableGraphics := True πριν την Open παραλείπει εντελώς την ανάλυση του επιπέδου σχεδίασης, κάτι που επιταχύνει τη φόρτωση βιβλίων εργασίας που κουβαλούν χρόνια συσσωρευμένων σχημάτων και ενσωματωμένων εικόνων. Ο περιορισμός είναι αυστηρός: το επίπεδο σχεδίασης απουσιάζει τότε από το μοντέλο, οπότε η αποθήκευση ενός τέτοιου βιβλίου εργασίας γράφει ένα αρχείο χωρίς τα σχέδιά του. Κρατήστε αυτή τη σημαία για εργασίες ανάλυσης μόνο για ανάγνωση. Η SetTempDir ανακατευθύνει τα προσωρινά αρχεία του συγγραφέα BIFF, κάτι που έχει σημασία σε διακομιστές όπου η προεπιλεγμένη τοποθεσία temp έχει ποσόστωση ή βρίσκεται σε αργή αποθήκευση. Η UseSharedFormulas ομαδοποιεί επαναλαμβανόμενα σώματα τύπων σε εγγραφές κοινόχρηστου τύπου, συρρικνώνοντας αρχεία όπου μια στήλη τύπου επαναλαμβάνεται σε εξήντα χιλιάδες γραμμές

Οι βρόχοι ανάγνωσης πάνω σε δεδομένα XLS έχουν μια παγίδα δεικτοδότησης που αξίζει να επισημανθεί επειδή διπλασιάζει τη δουλειά όταν αντιμετωπίζεται αμυντικά και διαφθείρει τα αποτελέσματα όταν παραλείπεται: η UsedRange αναφέρει τα όρια FirstRow, LastRow, FirstCol και LastCol ξεκινώντας από το μηδέν, ενώ η Cells.Item[Row, Col] ξεκινά από το ένα. Μια σάρωση που διατρέχει το χρησιμοποιημένο εύρος πρέπει να προσθέτει ένα σε κάθε συντεταγμένη στην πρόσβαση κελιού, όπως στο Cells.Item[Row + 1, Col + 1], αλλιώς διαβάζει ένα πλέγμα μετατοπισμένο διαγώνια κατά ένα κελί, αποβάλλοντας σιωπηλά την τελευταία γραμμή και στήλη και συμπεριλαμβάνοντας μια φαντασματική πρώτη. Το callback ForEachCell παρακάμπτει εντελώς την αναντιστοιχία, κάτι που είναι ένας ακόμη λόγος να το προτιμάτε για σαρώσεις ολόκληρου φύλλου

Εξετάστε τα αρχεία πριν τα φορτώσετε

Η φθηνότερη λειτουργία μεγάλου βιβλίου εργασίας είναι αυτή που αποφεύγετε. Η GetSheetNames και στις δύο προσόψεις απαριθμεί τα φύλλα εργασίας ενός αρχείου χωρίς να φορτώνει δεδομένα κελιών. Η υλοποίηση XLSX διαβάζει μόνο το manifest του βιβλίου εργασίας μέσα στο zip και αφήνει ρητά την παρουσία βιβλίου εργασίας άδεια, και η πρόσοψη XLS σταματά τη σάρωση στο πρώτο όριο υπο-stream. Αυτό την κάνει τον σωστό προκαταρκτικό έλεγχο για το «ποιο φύλλο πρέπει να στοχεύσει αυτή η εργασία εισαγωγής», και η CanReadEncrypted απαντά στο «είναι αυτό ένα κρυπτογραφημένο δοχείο» πριν από μια καταδικασμένη προσπάθεια Open

Ροή προελέγχου για άγνωστο αρχείο Excel σε Delphi με HotXLS: το GetSheetNames απαριθμεί φύλλα εργασίας χωρίς φόρτωση δεδομένων κελιών, κωδικός επιστροφής μηδέν ή κάτω αδειάζει τη λίστα και σηματοδοτεί αποτυχία, το CanReadEncrypted σηματοδοτεί κρυπτογραφημένα δοχεία πριν από καταδικασμένο Open, και μόνο τότε τρέχει η πλήρης φόρτωση — Η GetSheetNames και η CanReadEncrypted απαντούν ποιο φύλλο να στοχευθεί και αν ο περιέκτης είναι αναγνώσιμος πριν αναλυθεί οποιοδήποτε δεδομένο κελιού

Names := TStringList.Create;
Book := TXLSXWorkbook.Create;
try
  if Book.GetSheetNames('big-unknown.xlsx', Names) <= 0 then
    raise Exception.Create('cannot enumerate sheets');   // η αποτυχία αδειάζει τη λίστα
  // επιλέξτε το φύλλο στόχο, και έπειτα αποφασίστε αν αξίζει ένα πλήρες Open
finally
  Book.Free;
  Names.Free;
end;

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

Προσαρμόζοντας την προσέγγιση στην εργασία

Για μη επιτηρούμενες διοχετεύσεις που δημιουργούν πολλά μεγάλα αρχεία σε σειρά, δύο ακόμη συνήθειες στρογγυλεύουν την εικόνα. Τα αντικείμενα βιβλίου εργασίας δεν είναι thread-safe για κοινή χρήση, αλλά τίποτα δεν εμποδίζει ένα ανεξάρτητο βιβλίο εργασίας ανά worker thread, κάτι που παραλληλοποιεί καθαρά τη μαζική μετατροπή. Και όταν η έξοδος πηγαίνει σε HTTP αντί σε δίσκο, οι υπερφορτώσεις αποθήκευσης TStream συνδυάζονται με την StreamingWrite ώστε μια μεγάλη απόκριση να μην υλοποιείται ποτέ ως προσωρινό αρχείο. Ισχύει μία λειτουργική υποσημείωση: η αποθήκευση stream γράφει από την τρέχουσα θέση χωρίς περιτύλιξη πίσω, οπότε ορίστε Position := 0 πριν παραδώσετε το stream στο πλαίσιο απόκρισης. Το άρθρο για streaming write και εργασίες batch αναπτύσσει αυτό το μοτίβο από την πλευρά του διακομιστή, και το άρθρο εξαγωγής βάσης δεδομένων δείχνει πού εντάσσονται αυτοί οι μοχλοί σε μια αναφορά οδηγούμενη από dataset

Τέλος, κρατήστε ένα fixture χειρότερης περίπτωσης ανά οικογένεια αναφοράς και χρονομετρήστε το στο CI. Οι οπισθοδρομήσεις απόδοσης στη δημιουργία εγγράφων σπάνια ανακοινώνουν τον εαυτό τους. Ένα στιλ που προστέθηκε μέσα σε έναν βρόχο ή μια εξέταση που αντικαταστάθηκε με ένα πλήρες Open δεν αλλάζει τίποτα λειτουργικά, και η νυχτερινή δέσμη εργασιών απλώς παίρνει σαράντα λεπτά παραπάνω. Ένα χρονομετρημένο τεστ σε ένα αντιπροσωπευτικό fixture μισού εκατομμυρίου κελιών μετατρέπει αυτή την απόκλιση σε ένα κόκκινο build αντί για ένα περιστατικό λειτουργίας

Εκδόσεις αξιολόγησης, demo projects με ένα παράδειγμα μαζικής δημιουργίας, και η πλήρης αναφορά API είναι διαθέσιμα στη σελίδα HotXLS Delphi Component

Απόδοση μεγάλων βιβλίων εργασίας Excel στο Delphi με το HotXLS