Εξαγωγή βιβλίων εργασίας Excel σε CSV, TSV, HTML και RTF από το Delphi με το HotXLS

Φανταστείτε μια νυχτερινή εργασία που χτίζει ένα βιβλίο εργασίας τιμολογίων μέσα από κώδικα και το γράφει ως CSV για να το εισαγάγει ένα σύστημα παρακάτω στη ροή. Οι αριθμοί φαίνονται σωστοί στο Excel. Το CSV ανοίγει καθαρά σε έναν επεξεργαστή κειμένου. Έπειτα ο εισαγωγέας πνίγεται στη στήλη συνόλων, επειδή το πεδίο ποσού για τη γραμμή 42 διαβάζεται =SUM(D2:D41), ο τύπος ως κυριολεκτικό κείμενο, όχι το νούμερο στο οποίο θα έπρεπε να υπολογιστεί. Τίποτα δεν είναι χαλασμένο. Αυτή είναι τεκμηριωμένη συμπεριφορά, και είναι το πρώτο πράγμα που πρέπει να καταλάβετε σχετικά με την εξαγωγή από το HotXLS: ο συγγραφέας σειριοποιεί το μοντέλο κελιών ακριβώς όπως βρίσκεται, και ένα κελί τύπου του οποίου η τιμή δεν υπολογίστηκε ποτέ έχει μόνο το κείμενο του τύπου του να παραδώσει

Γιατί το CSV σας περιέχει τύπους αντί για αριθμούς

Το HotXLS αποθηκεύει το κείμενο τύπου και την υπολογισμένη τιμή ως δύο ξεχωριστά πράγματα. Η SaveAsCSV δεν τρέχει τη μηχανή υπολογισμού κατά την έξοδο, εκ σχεδιασμού: μια εξαγωγή δεν πρέπει να μεταβάλλει το βιβλίο εργασίας, και δεν πρέπει να ρισκάρει να κολλήσει σε μια παθολογική αλυσίδα τύπων. Αρχεία που αποθήκευσε το ίδιο το Excel κουβαλούν αποθηκευμένα αποτελέσματα δίπλα στους τύπους, οπότε η επανεξαγωγή τους συμπεριφέρεται όπως αναμένετε. Η παγίδα είναι ειδική για βιβλία εργασίας που δημιούργησε ο δικός σας κώδικας, όπου γράφτηκαν τύποι που όμως δεν αποτιμήθηκαν ποτέ. Η λύση είναι να κάνετε τις τιμές να υπάρχουν πριν την εξαγωγή, χρησιμοποιώντας την ίδια μηχανή Calculate που επιλύει αναφορές μεταξύ φύλλων και προσαρμοσμένες συναρτήσεις:

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  R: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('invoice-run.xlsx');
    Sheet := Book.Sheets[0];

    // Υλοποίησε τα αποτελέσματα τύπων ώστε το CSV να κουβαλά αριθμούς, όχι κείμενο '=...'
    for R := 2 to 41 do
      if Sheet.Cells[R, 4].Formula <> '' then
        Sheet.Cells[R, 4].Value := Book.Calculate(Sheet.Cells[R, 4].Formula);

    Book.SaveAsCSV('feed.csv', 0, ',');    // φύλλο 0, κόμμα
    Book.SaveAsCSV('feed.tsv', 0, #9);     // ίδιο φύλλο ως TSV
  finally
    Book.Free;
  end;
end;

Προσέξτε τι κάνει πραγματικά ο βρόχος: αντικαθιστά τα κελιά τύπου με τις υπολογισμένες τιμές τους. Αυτό είναι ακριβώς σωστό για ένα εξαγωγικό πέρασμα μιας χρήσης και λάθος αν σκοπεύετε να αποθηκεύσετε το βιβλίο εργασίας ξανά ως .xlsx αργότερα, επειδή μόλις αντικαταστήσατε ζωντανούς τύπους με παγωμένους αριθμούς. Εξάγετε από ένα αντίγραφο, ή περιορίστε την επανεγγραφή ώστε να αγγίζει μόνο το πέρασμα εξαγωγής. Η μηχανή πίσω από την Calculate προχωρά πέρα από αυτό, συμπεριλαμβανομένης της καταχώρισης δικών σας συναρτήσεων, κάτι που είναι το θέμα του μηχανή τύπων του HotXLS και προσαρμοσμένες συναρτήσεις

Τι εγγυάται ο συγγραφέας οριοθετημένων αρχείων

Η διαδρομή CSV παράγει UTF-8 με byte order mark, τερματισμούς γραμμής CRLF, και εισαγωγικά κατά RFC 4180. Κάθε πεδίο που περιέχει το διαχωριστικό, ένα εισαγωγικό, ή αλλαγή γραμμής τυλίγεται, και τα ενσωματωμένα εισαγωγικά διπλασιάζονται. Οι ημερομηνίες αποδίδονται ως yyyy-mm-dd hh:nn:ss ανεξάρτητα από τη μορφή εμφάνισης του κελιού. Αυτή είναι η σωστή επιλογή για έναν μηχανικό καταναλωτή, αν και εκπλήσσει όποιον περίμενε να μεταφερθεί η μορφοποίηση της οθόνης. Τα κελιά rich text ισοπεδώνονται με συνένωση των runs τους

Αυτές οι προεπιλογές διευθετούν τις περισσότερες διαφωνίες με έναν εισαγωγέα πριν καν ξεκινήσουν, αλλά δύο από αυτές ανήκουν έτσι κι αλλιώς στο δικό σας συμβόλαιο διεπαφής. Η πρώτη είναι το BOM. Είναι αυτό που επιτρέπει στο Excel να ανοίγει το αρχείο με τους τονισμένους χαρακτήρες άθικτους, ωστόσο μια χούφτα αυστηροί parsers αντιμετωπίζουν αυτά τα τρία bytes ως δεδομένα· αν ο δικός σας είναι ένας από αυτούς, αφαιρέστε τα κατά την παράδοση. Η δεύτερη είναι το TSV. Δεν είναι καθόλου ξεχωριστό χαρακτηριστικό, απλώς ο ίδιος συγγραφέας καλεσμένος με #9 ως διαχωριστικό, οπότε όλα τα παραπάνω ισχύουν γι' αυτό αμετάβλητα. Το φύλλο προς εξαγωγή επιλέγεται με δείκτη που ξεκινά από το μηδέν στην υπερφόρτωση πολλαπλών ορισμάτων, ενώ η συντόμευση ενός ορίσματος SaveAsCSV(FileName) παίρνει το ενεργό φύλλο

Η εξαγωγή HTML είναι ένα στιγμιότυπο, όχι μια μορφή ανταλλαγής

Εκεί όπου το CSV πετά τα πάντα εκτός από τις τιμές, η SaveAsHTML προσπαθεί να κρατήσει την εμφάνιση: ένα <table> ανά φύλλο, συγχωνευμένες περιοχές εκφρασμένες ως colspan και rowspan, βασική μορφοποίηση κελιού ενσωματωμένη ως inline CSS. Τα χρώματα σχετικά με θέμα παραλείπονται αντί να επιλύονται, οπότε ένα template που στηρίζεται σε θέσεις θέματος βγαίνει πιο απλό απ' όσο φαίνεται στο Excel. Ορίστε ρητά χρώματα RGB σε οτιδήποτε πρέπει να επιβιώσει το ταξίδι. Το αντικείμενο επιλογών ελέγχει τον φάκελο:

var
  Opts: TXLSXHtmlExportOptions;
begin
  Opts := TXLSXHtmlExportOptions.Create;
  try
    Opts.Title := 'Weekly settlement';
    Opts.TableClass := 'report-grid';     // σημείο πρόσδεσης για το stylesheet της φιλοξενούσας σελίδας
    Opts.WriteDocument := True;           // πλήρης σελίδα, όχι απόσπασμα
    if Book.SaveAsHTML('settlement.html', 0, Opts) <> 0 then
      raise Exception.Create('Sheet index out of range');
  finally
    Opts.Free;
  end;
end;

Δύο λεπτομέρειες σε αυτό το απόσπασμα αξίζουν προσοχή. Γυρίστε την WriteDocument σε False και η έξοδος γίνεται ένα γυμνό απόσπασμα πίνακα αντί για πλήρη σελίδα, κάτι που θέλετε όταν εγχέετε μια προεπισκόπηση μέσα σε μια υπάρχουσα διάταξη: ορίστε την TableClass και αφήστε το stylesheet της φιλοξενούσας σελίδας να κάνει τη θεματοποίηση. Η σύμβαση επιστροφής είναι επίσης το αντίστροφο των περισσότερων κλήσεων του HotXLS. Η SaveAsHTML επιστρέφει 0 σε επιτυχία και -1 για λάθος δείκτη φύλλου, οπότε ένας έλεγχος από συνήθεια για = 1 θα αναφέρει κάθε επιτυχημένη εξαγωγή ως αποτυχία. Όταν χρειάζεστε μια περιοχή αντί για ολόκληρο φύλλο, ίσως για αποστολή με email ή για ενσωμάτωση ενός μεμονωμένου μπλοκ, η TXLSXRange.SaveAsHTML εξάγει οποιοδήποτε ορθογώνιο εύρος με τους ίδιους κανόνες απόδοσης

Έξοδος RTF και πού εξακολουθεί να δικαιολογεί τη θέση της

Ο τέταρτος στόχος γράφει πίνακες RTF 1.6, ένα φύλλο ανά κλήση μέσω της SaveAsRTF. Τα πλάτη στηλών προσεγγίζονται σε περίπου 96 twips ανά χαρακτήρα πλάτους στήλης. Ο δομικός περιορισμός που πρέπει να γνωρίζετε είναι ότι τα συγχωνευμένα κελιά δεν εκτείνονται στην έξοδο: μόνο το κελί άγκυρα κουβαλά το περιεχόμενό του, και τα καλυμμένα κελιά εκπέμπονται κενά. Αυτό αποκλείει το RTF για templates βαριά σε διάταξη. Εξακολουθεί να δικαιολογεί τη θέση του ως ο δρόμος της λιγότερης αντίστασης για να ρίξετε πινακοποιημένα αποτελέσματα σε έναν επεξεργαστή κειμένου ή σε ένα παλαιό σύστημα διαχείρισης εγγράφων που προϋπάρχει της εισαγωγής HTML

Κύκλος ανάγνωσης-εγγραφής: η εισαγωγή CSV είναι καταστροφική εκ σχεδιασμού

Η ανάγνωση CSV πίσω έχει το δικό της συμβόλαιο. Η OpenCSV καθαρίζει ολόκληρο το βιβλίο εργασίας και το ξαναχτίζει ως ένα μοναδικό φύλλο με όνομα Sheet1. Είναι κατασκευαστής στο πνεύμα, όχι συγχώνευση, οπότε ποτέ μην την καλείτε σε ένα βιβλίο εργασίας που ακόμη κρατά μη αποθηκευμένο περιεχόμενο. Η μετάδοση του #0 ως διαχωριστικού ενεργοποιεί αυτόματη ανίχνευση διαχωριστικού. Η σημαία ADetectTypes ελέγχει την προαγωγή τύπου: με αυτήν ενεργή, οι αριθμητικές συμβολοσειρές γίνονται αριθμοί, οι συμβολοσειρές ISO-8601 γίνονται ημερομηνίες, και τα true/false γίνονται booleans. Απενεργοποιήστε την όταν η τροφοδοσία κουβαλά αναγνωριστικά με μπροστά μηδενικά, ταχυδρομικούς κώδικες, ή κωδικούς προϊόντων, όλα τα οποία η προαγωγή τα παραμορφώνει σιωπηλά σε αριθμούς (ένα μπροστά μηδενικό απλώς εξαφανίζεται τη στιγμή που το 00123 γίνεται 123). Και οι δύο προσόψεις εκθέτουν την ίδια εισαγωγή. Συνδυάστε την με τις παραπάνω κλήσεις εξαγωγής και έχετε μια γέφυρα μορφών που δεν χρειάζεται καμία εγκατάσταση Excel πουθενά στη διοχέτευση, το σενάριο που καλύπτεται στο δημιουργία αναφορών από βάση δεδομένων σε Excel με το HotXLS

Εξαγωγή απευθείας σε ένα stream

Κάθε συγγραφέας εδώ έχει μια υπερφόρτωση stream δίπλα στην έκδοση με όνομα αρχείου: CSV, HTML, RTF, και οι ίδιες οι μορφές βιβλίου εργασίας. Σε κώδικα διακομιστή αυτές οι υπερφορτώσεις είναι αυτές που πρέπει να χρησιμοποιείτε. Ένα web endpoint που εξυπηρετεί μια λήψη CSV μπορεί να γράψει σε ένα TMemoryStream και να το παραδώσει απευθείας στο αντικείμενο απόκρισης, χωρίς προσωρινό αρχείο, χωρίς εργασία εκκαθάρισης, και χωρίς σύγκρουση μεταξύ δύο αιτημάτων που έτυχε να διαλέξουν το ίδιο δημιουργημένο όνομα. Το ίδιο ισχύει για την προώθηση εξαγωγών σε αποθήκευση blob ή τη σύνδεσή τους σε εξερχόμενο mail. Το σύστημα αρχείων βγαίνει εντελώς από την εικόνα

Αυτό το μοτίβο ενισχύεται από το πώς αναπτύσσεται η βιβλιοθήκη. Και οι δύο προσόψεις είναι εγγενείς αναγνώστες και συγγραφείς Object Pascal, οπότε δεν υπάρχει εγκατάσταση Excel, δεν υπάρχει COM automation, και δεν υπάρχει σημείο συμφόρησης ανά διεργασία που να σειριοποιεί αιτήματα στον διακομιστή. Κάθε αίτημα μπορεί να κατέχει το δικό του αντικείμενο βιβλίου εργασίας, να τρέχει την επανεγγραφή υπολογισμού από την πρώτη ενότητα, και να ρέει την εξαγωγή του παράλληλα με τους γείτονές του. Η μνήμη είναι ο ένας πόρος που πρέπει να παρακολουθείτε. Το μοντέλο βιβλίου εργασίας ζει στη RAM για όλη τη διάρκεια της εξαγωγής, οπότε μια υπηρεσία που ανοίγει πολύ μεγάλα αρχεία μόνο και μόνο για να τα ξαναβγάλει ως CSV θα πρέπει να θέτει ανώτατο όριο στις ταυτόχρονες εργασίες, ή να βάζει σε ουρά τις υπερμεγέθεις, αντί να αφήνει μια αιχμή κίνησης να αποφασίσει το working set

Ένας μικρότερος διακόπτης: ορίστε την IncludeBOM στις επιλογές HTML όταν το απόσπασμα θα αποθηκευτεί ως αυτόνομο αρχείο που κάποιο εργαλείο παρακάτω στη ροή μυρίζει για κωδικοποίηση. Όταν εξυπηρετείτε HTML απευθείας μέσω HTTP, αφήστε τη δήλωση charset στις κεφαλίδες απόκρισης αντ' αυτού

Όταν τα bytes εξακολουθούν να βγαίνουν λάθος

Η πιο συχνή ερώτηση υποστήριξης σχετικά με την εξαγωγή CSV είναι το πρόβλημα της εισαγωγής με διαφορετική στολή: το Excel δείχνει mojibake αντί για τονισμένους χαρακτήρες. Το ένστικτο είναι να κατηγορήσετε τον συγγραφέα, αλλά αυτός εκπέμπει ένα UTF-8 BOM ακριβώς γι' αυτόν τον λόγο, και το αρχείο είναι σχεδόν πάντα σωστό όταν φεύγει από τον κώδικά σας. Κάτι ανάμεσα εκεί και το Excel έφαγε το BOM. Μια μεταφορά FTP σε λειτουργία κειμένου, μια αντιγραφή stream που παραλείπει τα πρώτα τρία bytes, ένας proxy που επανακωδικοποιεί στη διαδρομή: οποιοδήποτε από αυτά θα αφαιρέσει τον δείκτη και θα αφήσει το Excel να μαντέψει την κωδικοποίηση, κάτι που κάνει άσχημα. Διαγνώστε αυτό στο σύνορο, όχι στην κλήση εξαγωγής. Ανοίξτε το παραδοθέν αρχείο σε έναν hex viewer και επιβεβαιώστε ότι το EF BB BF εξακολουθεί να είναι το πρώτο πράγμα μέσα του

Αυτό είναι το κοινό νήμα και για τις τέσσερις μορφές. Η κλήση εξαγωγής είναι το εύκολο μέρος, και το HotXLS κάνει μια υπερασπίσιμη επιλογή σε κάθε απόφαση που αντιμετωπίζει ο συγγραφέας. Οι αστοχίες ζουν στις ραφές, όπου το κείμενο τύπου συναντά έναν parser που ήθελε αριθμό, όπου ένα BOM συναντά μια μεταφορά που δεν το διατηρεί, όπου ένα συγχωνευμένο κελί συναντά το επίπεδο μοντέλο πίνακα του RTF. Καθένα από αυτά είναι ένα γεγονός που πρέπει να γραφτεί στο συμβόλαιο ανάμεσα στον εξαγωγέα σας και σε ό,τι τον καταναλώνει, επειδή ο καταναλωτής δεν μπορεί να διαβάσει τις προθέσεις σας μέσα από τα bytes. Για την πλήρη λίστα μεθόδων και στις δύο προσόψεις βιβλίου εργασίας, η σελίδα προϊόντος HotXLS Delphi Component κουβαλά την πλήρη αναφορά