Εξαγωγή αποτελεσμάτων βάσης δεδομένων Delphi σε αναφορές Excel με το HotXLS

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

Το HotXLS είναι μια εγγενής βιβλιοθήκη υπολογιστικών φύλλων σε Object Pascal που γράφει αρχεία XLS και XLSX απευθείας από τη Delphi και τη C++Builder, χωρίς να εμπλέκεται Excel automation. Προσφέρει δύο διαδρομές από ένα TDataset προς ένα βιβλίο εργασίας: το έτοιμο εξάρτημα TDataToXLS, και έναν χειρόγραφο βρόχο πάνω στο API βιβλίου εργασίας. Δεν είναι εναλλάξιμα. Το εξάρτημα είναι πολίτης VCL χτισμένος πάνω στην πρόσοψη XLS, οπότε η σωστή επιλογή εξαρτάται από το πού τρέχει ο κώδικας και ποια μορφή αρχείου περιμένει ο καταναλωτής. Αυτό που ακολουθεί είναι και οι δύο διαδρομές, το σημείο όπου το εξάρτημα σταματά να είναι το σωστό εργαλείο, και πώς να κρατήσετε τους τύπους πεδίων άθικτους όποιο κι αν επιλέξετε

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

Πριν από οποιαδήποτε κλήση API, αποφασίστε πώς προσγειώνεται κάθε τύπος πεδίου της Delphi σε ένα κελί. Ένα κελί που δέχεται μια συμβολοσειρά Delphi παραμένει συμβολοσειρά. Το HotXLS δεν μαντεύει ότι το '1,234.50' εννοούσε να είναι αριθμός, και δεν θα έπρεπε, επειδή η επανανάλυση που εξαρτάται από locale είναι ακριβώς ο τρόπος που ένα γερμανικό δεκαδικό κόμμα γίνεται διαχωριστικό χιλιάδων σε έναν αγγλικό διακομιστή. Το αξιόπιστο μοτίβο είναι να αναθέτετε μέσω των τυποποιημένων accessors: AsFloat ή AsCurrency για αριθμητικά πεδία, AsDateTime για ημερομηνίες ώστε το κελί να κρατά έναν γνήσιο σειριακό αριθμό ημερομηνίας του Excel αντί για μια μορφοποιημένη συμβολοσειρά, και AsString μόνο για πεδία που είναι πράγματι κείμενο

Ο χειρισμός των NULL αξίζει μια ρητή απόφαση αντί για μια προεπιλογή. Η μετατροπή μιας τιμής πεδίου με την VarToStr μετατρέπει το SQL NULL σε κενή συμβολοσειρά, που είναι κελί κειμένου, ενώ η παράλειψη της ανάθεσης αφήνει το κελί πράγματι κενό, που είναι αυτό που περιμένουν οι καταναλωτές AVERAGE, COUNT και pivot-table. Για στήλες χρημάτων, αποφασίστε πριν γραφτεί ο βρόχος αν το NULL σημαίνει μηδέν ή άγνωστο. Τα δύο αποδίδονται πανομοιότυπα μόλις κάποιος μορφοποιήσει τη στήλη, και η διαφορά αλλάζει κάθε συγκεντρωτικό υπολογισμό παρακάτω στη ροή

Η διαδρομή του εξαρτήματος: TDataToXLS σε εφαρμογές VCL

Για μια κλασική εφαρμογή VCL με ένα ερώτημα ήδη συνδεδεμένο σε μια μονάδα δεδομένων, η TDataToXLS είναι η διαδρομή μίας κλήσης. Διατρέχει οποιονδήποτε απόγονο TDataset, είτε FireDAC, ADO, IBX, ή οτιδήποτε άλλο υλοποιεί την αφηρημένη διεπαφή dataset, και παράγει ένα στιλαρισμένο φύλλο εργασίας με λεζάντες κεφαλίδων, γραμματοσειρές, περιγράμματα, προαιρετικά μερικά σύνολα ομάδας, και αυτόματο διαχωρισμό φύλλων για μεγάλα σύνολα αποτελεσμάτων

var
  Exporter: TDataToXLS;
begin
  Exporter := TDataToXLS.Create(nil);
  try
    Exporter.Dataset := OrdersQuery;          // οποιοσδήποτε απόγονος TDataset
    Exporter.WorksheetName := 'Orders';
    Exporter.HeaderSource := hsDisplayLabel;  // λεζάντες, όχι ακατέργαστα ονόματα στηλών
    Exporter.GroupFields.Add('CustomerID');   // μπλοκ μερικού συνόλου ανά πελάτη
    Exporter.RowsPerSheet := 50000;           // μείνετε κάτω από το ανώτατο όριο γραμμών του BIFF8
    Exporter.VisibleFieldsOnly := True;             // σεβαστείτε το Field.Visible
    Exporter.SaveDatasetAs('orders.xls');
  finally
    Exporter.Free;
  end;
end;

Δύο ιδιότητες κουβαλούν το μεγαλύτερο μέρος του παραγωγικού βάρους εδώ. Η HeaderSource := hsDisplayLabel γράφει το DisplayLabel κάθε πεδίου αντί για το ακατέργαστο όνομα στήλης SQL, οπότε το βιβλίο εργασίας λέει «Customer Name» αντί για CUST_NM. Η RowsPerSheet υπάρχει επειδή το εξάρτημα γράφει BIFF8, του οποίου το πλέγμα σταματά στις 65.536 γραμμές επί 256 στήλες· ο ορισμός της στο 50.000 διαχωρίζει ένα μεγάλο σύνολο αποτελεσμάτων σε φύλλα πριν το ανώτατο όριο μορφής το περικόψει. Η εμφάνιση διαχειρίζεται από τις ιδιότητες HeaderFont, DetailFont, GroupColor και στιλ περιγράμματος, και το σύνολο DisableFormat απενεργοποιεί ολόκληρες κατηγορίες μορφοποίησης όταν ο καταναλωτής θέλει απλά κελιά. Για οτιδήποτε προσαρμοσμένο, τα events AfterCell και AfterRow σας παραδίδουν το μόλις γραμμένο εύρος για μετεπεξεργασία

Πού σταματά το εξάρτημα

Τρεις περιορισμοί είναι ενσωματωμένοι σχεδιαστικά στο TDataToXLS, και το να τους γνωρίζετε εκ των προτέρων αποφεύγει έναν άβολο επανασχεδιασμό δύο sprints αργότερα

• Είναι εξάρτημα VCL με την πλήρη έννοια. Η μονάδα του τραβά μέσα τις Forms, Controls και Dialogs, οπότε η σύνδεσή του σε μια εργασία κονσόλας ή μια υπηρεσία Windows σέρνει το VCL μέσα στο δυαδικό αρχείο. Οι βασικές μονάδες βιβλίου εργασίας δεν έχουν τέτοια εξάρτηση. Χρειάζονται μόνο τις Windows, Classes, SysUtils και Variants, γι' αυτό ο κώδικας από την πλευρά του διακομιστή πρέπει να χρησιμοποιεί αντ' αυτού τον βρόχο που φαίνεται παρακάτω
• Είναι χτισμένο πάνω στην πρόσοψη XLS. Το εξάρτημα γεμίζει ένα IXLSWorkbook και γράφει .xls (BIFF8). Δεν υπάρχει ιδιότητα που να το μεταβάλλει σε έξοδο OOXML
• Τα events του μιλούν τη διάλεκτο XLS. Η παράμετρος Cell: IXLSRange στην AfterCell ανήκει στο μοντέλο αντικειμένων XLS, οπότε η ανά κελί προσαρμογή που γράφεται εκεί είναι κώδικας στιλ XLS ακόμη κι αν το αρχείο μετατραπεί σε .xlsx αργότερα

Παραγωγή .xlsx από την έξοδο του εξαρτήματος

Όταν ο καταναλωτής επιμένει σε .xlsx αλλά η λογική εξαγωγής ήδη ζει στο TDataToXLS, η συνάρτηση γέφυρας στη μονάδα lxXlsxExport μετατρέπει το γεμάτο βιβλίο εργασίας με μία κλήση:

uses lxXlsxExport;

Exporter.SaveDatasetAs('orders.xls');
// το εξάρτημα εκθέτει το IXLSWorkbook που γέμισε
SaveXLSWorkbookAsXLSX(Exporter.Workbook, 'orders.xlsx');

Αντιμετωπίστε τη γέφυρα ως φορέα πινακοποιημένων δεδομένων, όχι ως μετατροπέα πλήρους πιστότητας. Αντιγράφει τιμές, τύπους, μορφές αριθμών, χρώματα γεμίσματος, χαρακτηριστικά γραμματοσειράς, πλάτη στηλών και ρυθμίσεις προβολής. Σκόπιμα δεν αντιγράφει περιγράμματα, συγχωνευμένα εύρη, σχόλια, γραφήματα ή μορφοποίηση υπό όρους. Για ένα επίπεδο πλέγμα κεφαλίδας συν γραμμών αυτό είναι ακριβώς αρκετό. Για μια στιλαρισμένη αναφορά δεν είναι, και η ειλικρινής λύση είναι να δημιουργήσετε το XLSX απευθείας αντί να διορθώσετε το μετατρεμμένο αρχείο

Ο χειρόγραφος βρόχος για υπηρεσίες και εργασίες batch

Ο κώδικας από την πλευρά του διακομιστή πρέπει να στοχεύει απευθείας στο TXLSXWorkbook. Προσέξτε τη διαφορά διάρκειας ζωής ανάμεσα στις δύο προσόψεις πριν αντιγράψετε οποιοδήποτε δείγμα. Το TXLSWorkbook στην πλευρά XLS κρατιέται μέσω μιας διεπαφής με μέτρηση αναφορών και δεν πρέπει να ελευθερώνεται χειροκίνητα, ενώ το TXLSXWorkbook είναι μια απλή κλάση που απαιτεί try..finally Free. Η ανάμειξη των δύο συμβάσεων είναι ένας αξιόπιστος τρόπος να δημιουργήσετε είτε μια διαρροή είτε ένα double-free

procedure ExportOrders(Q: TDataSet; const FileName: string);
var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Row: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Orders');
    Sheet.Cells[1, 1].Value := 'Order No';
    Sheet.Cells[1, 2].Value := 'Customer';
    Sheet.Cells[1, 3].Value := 'Ordered';
    Sheet.Cells[1, 4].Value := 'Amount';

    Row := 2;
    Q.First;
    while not Q.Eof do
    begin
      Sheet.Cells[Row, 1].Value := Q.FieldByName('OrderNo').AsInteger;
      Sheet.Cells[Row, 2].Value := Q.FieldByName('Customer').AsString;
      if not Q.FieldByName('Ordered').IsNull then
        Sheet.Cells[Row, 3].Value := Q.FieldByName('Ordered').AsDateTime;
      Sheet.Cells[Row, 4].Value := Q.FieldByName('Amount').AsFloat;
      Inc(Row);
      Q.Next;
    end;

    Book.StreamingWrite := True;  // ρέει το XML φύλλου απευθείας μέσα στο zip
    Book.SaveAs(FileName);
  finally
    Book.Free;
  end;
end;

Οι γραμμές που έχουν σημασία είναι οι τυποποιημένες αναθέσεις και ο φύλακας IsNull. Οι ημερομηνίες φτάνουν ως σειριακοί αριθμοί ημερομηνίας, τα ποσά φτάνουν ως doubles, και οι NULL ημερομηνίες παραγγελίας παραμένουν πράγματι κενές αντί να γίνονται κενές συμβολοσειρές. Η StreamingWrite := True αλλάζει μόνο τη διαδρομή αποθήκευσης: το XML φύλλου εργασίας ρέει απευθείας μέσα στο δοχείο zip αντί να συναρμολογείται πρώτα ως μία μεγάλη συμβολοσειρά, κάτι που ισοπεδώνει την αιχμή μνήμης κατά τη στιγμή της SaveAs για αριθμούς γραμμών έξι ψηφίων. Κάθε μέθοδος αποθήκευσης έχει επίσης μια υπερφόρτωση TStream, οπότε το βιβλίο εργασίας μπορεί να πάει απευθείας σε μια απόκριση HTTP χωρίς να αγγίξει τον δίσκο. Το άρθρο για streaming write και εργασίες batch περνά μέσα από αυτό το μοτίβο ανάπτυξης, και το άρθρο απόδοσης μεγάλων βιβλίων εργασίας καλύπτει τι να κάνετε όταν οι αριθμοί γραμμών ανεβαίνουν περισσότερο

Αυτός ο βρόχος είναι επίσης η διαδρομή που κλιμακώνεται σε πολλά threads. Και οι δύο μηχανές είναι εγγενείς συγγραφείς Object Pascal, ροές εγγραφών BIFF8 στη μία πλευρά και OOXML zip συν XML στην άλλη, οπότε κανένα κομμάτι μιας εξαγωγής δεν αγγίζει COM automation ούτε χρειάζεται άδεια Excel στον διακομιστή. Αυτό που σας αγοράζει είναι παραλληλισμός χωρίς σημείο συμφόρησης μίας παρουσίας, αρκεί κάθε thread να χτίζει το δικό του βιβλίο εργασίας. Τα αντικείμενα βιβλίου εργασίας δεν είναι thread-safe για κοινή χρήση, οπότε ο κανόνας είναι μία παρουσία ανά εξαγωγή, ποτέ μια κοινή που προστατεύεται από ένα lock

Ένα όριο αξίζει να το γνωρίζετε πριν σχεδιάσετε γύρω του. Το πλέγμα XLSX σταματά στις 1.048.576 γραμμές επί 16.384 στήλες, οπότε ο διαχωρισμός φύλλων που χειρίζεται η RowsPerSheet στην πλευρά XLS σπάνια χρειάζεται εδώ. Ένα βιβλίο εργασίας ενός εκατομμυρίου γραμμών σπάνια είναι αυτό που θέλει κι ένας ανθρώπινος καταναλωτής. Όταν το σύνολο αποτελεσμάτων είναι πράγματι τόσο μεγάλο, ένα οριοθετημένο αρχείο είναι συνήθως το καλύτερο συμβόλαιο, και το άρθρο για εξαγωγή CSV και TSV καλύπτει διαχωριστικά, συμπεριφορά BOM, και την επιφύλαξη αποτίμησης τύπων που ισχύει εκεί

Επιλέγοντας ένα σημείο εκκίνησης

Αν η εξαγωγή ζει σε ένα εργαλείο desktop VCL και η έξοδος .xls είναι αποδεκτή, ξεκινήστε με το TDataToXLS και την υποστήριξη ομαδοποίησής του. Είναι ο λιγότερος κώδικας, και η γέφυρα μέσω της SaveXLSWorkbookAsXLSX είναι εκεί όταν κάποιος ζητήσει αργότερα .xlsx, αρκεί να αποδεχτείτε τους περιορισμούς πιστότητας που περιγράφηκαν ήδη. Αν ο κώδικας τρέχει χωρίς επίβλεψη, ή ο καταναλωτής απαιτεί .xlsx εξαρχής, γράψτε τον βρόχο. Και οι δύο διαδρομές έρχονται με λειτουργικά demo projects και είναι μέρος του πακέτου HotXLS Delphi Component