Δημιουργία πάγκου εργασίας ελέγχου και μετατροπής βιβλίων εργασίας στο Delphi με το HotXLS

Μια μαζική εργασία κανονικοποίησης υπολογιστικών φύλλων είναι τρία προβλήματα ντυμένα με ένα παλτό. Έχετε ένα αρχείο μεικτών μορφών: εποχής BIFF .xls, σύγχρονα .xlsx, μερικά σκόρπια .ods από κάποιο πείραμα LibreOffice, και μια χούφτα αρχεία που κανείς δεν μπορεί να ανοίξει επειδή ο κωδικός πρόσβασης έφυγε μαζί με έναν πρώην υπάλληλο. Ο στόχος είναι να μετατραπούν όλα σε XLSX και CSV. Η έκδοση αυτής της εργασίας που γράφουν οι περισσότεροι είναι ένας βρόχος που ανοίγει κάθε αρχείο και το αποθηκεύει με νέα επέκταση, και δουλεύει μια χαρά μέχρι κάποιος να ρωτήσει ποια αρχεία έχασαν τα γραφήματά τους, έχασαν τις μακροεντολές τους, ή δεν άνοιξαν καθόλου. Ο βρόχος δεν έχει απάντηση, επειδή η μετατροπή από μόνη της δεν κρατά κανένα αρχείο καταγραφής. Ένας πάγκος εργασίας το κάνει: πρώτα απογράφει, μετά μετατρέπει, και τρίτο επαληθεύει, και τα τρία στάδια πρέπει να μοιράζονται πληροφορίες για να είναι οτιδήποτε από αυτά αξιόπιστο

Η συναρμολόγηση αυτού του πάγκου εργασίας σε Delphi ή C++Builder σημαίνει τη σύνδεση τεσσάρων δυνατοτήτων του HotXLS, καμία από τις οποίες δεν χρειάζεται εγκατεστημένο Excel πουθενά στο pipeline. Υπάρχουν δύο εγγενείς μηχανές, μια πρόσοψη BIFF8 για .xls και μια πρόσοψη OOXML για .xlsx και .ods. Υπάρχουν φθηνές κλήσεις εξέτασης που διαβάζουν μεταδεδομένα χωρίς να αναλύουν ολόκληρο το αρχείο. Υπάρχουν μετρητές ελέγχου ανά φύλλο που σας λένε τι πραγματικά περιέχει ένα βιβλίο εργασίας. Και υπάρχει ένας πίνακας μετατροπής με τεκμηριωμένο προφίλ πιστότητας για κάθε διαδρομή. Η δουλειά είναι να γνωρίζετε πού έχει καθένα από αυτά μια αιχμηρή λεπτομέρεια, επειδή το έχει το καθένα, και αυτές οι λεπτομέρειες είναι ακριβώς αυτό που μετατρέπει μια καθαρή νυχτερινή παρτίδα σε ένα περιστατικό Δευτέρα πρωί

Εξετάστε πριν φορτώσετε: ονόματα φύλλων και ανίχνευση κρυπτογράφησης

Το να ανοίγετε ένα βιβλίο εργασίας 200 MB μόνο και μόνο για να ανακαλύψετε ότι είναι κρυπτογραφημένο σπαταλά λεπτά ανά αρχείο, και πολλαπλασιασμένο σε ένα μεγάλο αρχείο σπαταλά ημέρες. Και οι δύο προσόψεις εκθέτουν την GetSheetNames, η οποία διαβάζει μεταδεδομένα φύλλων χωρίς να γεμίζει το βιβλίο εργασίας. Η υλοποίηση BIFF σαρώνει μόνο τις εγγραφές BoundSheet στην αρχή της ροής· η υλοποίηση OOXML διαβάζει μόνο το workbook.xml μέσα στο zip. Δίπλα της, η CanReadEncrypted ανιχνεύει έναν περιέκτη κρυπτογράφησης χωρίς να επιχειρεί αποκρυπτογράφηση:

var
  Probe: TXLSXWorkbook;
  Names: TStringList;
begin
  Names := TStringList.Create;
  Probe := TXLSXWorkbook.Create;
  try
    if Probe.CanReadEncrypted(FileName) then
    begin
      Writeln(FileName + ': encrypted container - route to manual handling');
      Exit;
    end;
    if Probe.GetSheetNames(FileName, Names) <= 0 then
      Writeln(FileName + ': unreadable - quarantine')
    else
      Writeln(Format('%s: %d sheet(s), first "%s"',
        [FileName, Names.Count, Names[0]]));
  finally
    Probe.Free;
    Names.Free;
  end;
end;

Δύο λειτουργικές λεπτομέρειες κάνουν αυτόν τον βρόχο φθηνό. Η GetSheetNames δεν επαναφέρει ούτε γεμίζει το instance του βιβλίου εργασίας, οπότε ένα μοναδικό αντικείμενο εξέτασης μπορεί να ταξινομήσει χιλιάδες αρχεία χωρίς να ξαναδημιουργηθεί. Και η έκδοση της ίδιας κλήσης στην πρόσοψη XLS κατανοεί επίσης πακέτα .xlsx, κάτι που την κάνει μια βολική ενιαία εξέταση όταν οι επεκτάσεις αρχείων δεν μπορούν να εμπιστευτούν, όπως σπάνια μπορούν σε ένα τόσο παλιό αρχείο. Η διαλογή πριν από τη φόρτωση αξίζει τη δική της ανάλυση· οι μηχανισμοί της ελαφριάς επιθεώρησης βρίσκονται στο άρθρο μας για τον κατάλογο φύλλων και την ελαφριά επιθεώρηση βιβλίων εργασίας

Καταμέτρηση του τι πραγματικά περιέχει ένα βιβλίο εργασίας

Μόλις ένα αρχείο περάσει τη διαλογή, το πέρασμα ελέγχου αποφασίζει τη διαδρομή μετατροπής του. Η πρόσοψη XLSX εκθέτει έναν μετρητή για κάθε οικογένεια χαρακτηριστικών που επηρεάζει μια απόφαση πιστότητας: συγχωνευμένα κελιά, γραφήματα, εικόνες, μορφοποιήσεις υπό όρους, επικυρώσεις δεδομένων, πίνακες, hyperlinks και σχόλια, καθώς και σημαίες σε επίπεδο βιβλίου εργασίας για μακροεντολές, προστασία και μορφή πηγής. Η διαδρομή μετατροπής για ένα αρχείο εξαρτάται σχεδόν εξ ολοκλήρου από το ποια από αυτά επιστρέφουν μη μηδενικά

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  I: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    if Book.Open(FileName) <> 1 then Exit;
    for I := 0 to Book.Sheets.Count - 1 do
    begin
      Sheet := Book.Sheets[I];
      Writeln(Format('%s: cells=%d merges=%d charts=%d cf=%d dv=%d protected=%s',
        [Sheet.Name, Sheet.Cells.Count, Sheet.MergedCells.Count,
         Sheet.Charts.Count, Sheet.ConditionalFormats.Count,
         Sheet.DataValidations.Count, BoolToStr(Sheet.IsProtected, True)]));
    end;
    if Book.HasVbaProject then
      Writeln('  contains VBA project - macro policy applies');
    if Book.ExternalLinks.Count > 0 then
      Writeln(Format('  %d external link(s)', [Book.ExternalLinks.Count]));
  finally
    Book.Free;
  end;
end;

Διαβάστε την Cells.Count έχοντας κατά νου μία προειδοποίηση. Η αποθήκη κελιών είναι αραιή, οπότε ο αριθμός μετρά στιγμιότυπα κελιών, όχι την ορθογώνια περιοχή του χρησιμοποιούμενου εύρους. Ένα φύλλο με μία τιμή στο A1 και άλλη μία στο ZZ9999 αναφέρει δύο κελιά, όχι το περίπου ένα εκατομμύριο που βρίσκεται ανάμεσά τους. Η αντίστοιχη σάρωση στην πλευρά BIFF χρησιμοποιεί τα όρια UsedRange μαζί με την ForEachCell, και φέρει το off-by-one που παγιδεύει σχεδόν όλους την πρώτη φορά: το UsedRange.FirstRow και τα αδέρφια του είναι βάσει 0, ενώ το Cells.Item[Row, Col] είναι βάσει 1. Μια διάτρεξη που ξεχνά να προσθέσει ένα σε κάθε όριο ελέγχει το λάθος ορθογώνιο και ποτέ δεν το λέει

Δύο μοχλοί μειώνουν το κόστος ενός περάσματος μόνο ελέγχου πάνω σε μεγάλα παλιά αρχεία. Ο ορισμός της _DisableGraphics σε true πριν από το άνοιγμα ενός .xls παρακάμπτει εντελώς την ανάλυση του επιπέδου σχεδίασης OfficeArt, κάτι που εξοικονομεί πραγματικό χρόνο σε βιβλία εργασίας πυκνά σε σχήματα. Είναι όμως αυστηρά μια βελτιστοποίηση μόνο για ανάγνωση: η αποθήκευση από ένα instance που ανοίχτηκε έτσι θα έριχνε τα σχέδια που ποτέ δεν αναλύθηκαν, οπότε η σημαία ανήκει μόνο σε διαδρομές που ποτέ δεν θα ξαναγράψουν το αρχείο. Όταν ο έλεγχος χρειάζεται περιεχόμενο ανά κελί αντί για μετρήσεις, το callback ForEachCell διατρέχει απευθείας τα γεμάτα κελιά και παρακάμπτει την επιβάρυνση Variant ανά πρόσβαση που πληρώνουν οι ευρετηριασμένες ιδιότητες κελιών σε κάθε ανάγνωση, κάτι που αθροίζεται γρήγορα σε εκατομμύρια κελιά

Κανονικοποιήστε νωρίς τους ασυνεπείς κωδικούς επιστροφής

Οι κλήσεις IO του HotXLS αναφέρουν σφάλματα μέσω ακέραιων αποτελεσμάτων αντί για εξαιρέσεις, και οι συμβάσεις δεν είναι ενιαίες σε όλο το API. Οι περισσότερες κλήσεις ανοίγματος και αποθήκευσης επιστρέφουν 1 σε επιτυχία και -1 σε αποτυχία. Η GetSheetNames επιστρέφει τον αριθμό φύλλων, ή -1 με τη λίστα άδεια. Η XLSX SaveAsHTML σπάει το μοτίβο ξανά και επιστρέφει 0 για επιτυχία, -1 για δείκτη φύλλου εκτός εύρους. Ένας πάγκος εργασίας που ελέγχει παντού = 1 θα ταξινομήσει λάθος αθόρυβα τις κλήσεις που σηματοδοτούν επιτυχία με άλλο τρόπο, και ένας που ελέγχει <> -1 θα καταπιεί αυτές που αποτυγχάνουν με διαφορετικό κωδικό

Ο κανόνας που επιβιώνει από την επαφή με ολόκληρο το API είναι πιο στενός απ' όσο φαίνεται: αντιμετωπίστε το <= 0 ως αποτυχία για κλήσεις που επιστρέφουν αριθμό, ελέγξτε την τεκμηριωμένη τιμή επιτυχίας για κάθε ρουτίνα αποθήκευσης που πραγματικά χρησιμοποιείτε, και βάλτε και τα δύο πίσω από μία μικρή συνάρτηση ελέγχου αποτελέσματος ώστε η σύμβαση να ζει σε ακριβώς ένα μέρος. Τα pipelines παρτίδας αποτυγχάνουν πολύ πιο συχνά από μια αργή συσσώρευση μη ελεγμένων κωδικών επιστροφής παρά από κάποιο εξωτικό σφάλμα parser, και το κόστος του να το κάνετε λάθος είναι σαράντα χιλιάδες αρχεία αργότερα, όταν κανείς δεν θυμάται ποιες μετατροπές πραγματικά πέτυχαν

Ο πίνακας μετατροπής και πού χάνει δεδομένα κάθε διαδρομή

Οι δύο προσόψεις μοιράζονται τη δουλειά μετατροπής μεταξύ τους. Η TXLSXWorkbook ανοίγει XLSX, ODS και CSV, και αποθηκεύει XLSX, ODS, CSV, HTML, RTF και κρυπτογραφημένο με AES XLSX. Η TXLSWorkbook ανοίγει και αποθηκεύει BIFF, και εξάγει HTML, RTF και CSV. Το χρήσιμο είναι ότι κάθε διαδρομή έρχεται με ένα τεκμηριωμένο προφίλ πιστότητας, όχι μια ασαφή υπόσχεση ορθότητας, ώστε να μπορείτε να αποφασίσετε εκ των προτέρων ποιες διαδρομές είναι ασφαλείς για ποια αρχεία

Η εξαγωγή CSV γράφει UTF-8 με BOM, τερματισμούς γραμμής CRLF και εισαγωγικά κατά RFC 4180. Αυτό που δεν κάνει είναι να αξιολογεί τύπους: ένα κελί που κρατά =SUM(...) εξάγεται ως το κυριολεκτικό κείμενο του τύπου, οπότε ένα φύλλο τύπων μετατρέπεται σε φύλλο συμβολοσειρών εκτός αν υπολογίσετε πρώτα τις τιμές. Η εξαγωγή HTML παράγει έναν ενιαίο πίνακα, με colspan και rowspan να αντικαθιστούν τα συγχωνευμένα κελιά και βασικά στυλ ενσωματωμένα ως inline. Η εξαγωγή RTF έχει πιο αυστηρό όριο: δεν μπορεί να εκτείνει συγχωνευμένα κελιά κατά μήκος στηλών, οπότε τα κελιά συνέχειας μιας συγχώνευσης βγαίνουν κενά. Η εισαγωγή ODS είναι σκόπιμα ελαφριά, σύμφωνα με την ίδια την τεκμηρίωση της βιβλιοθήκης. Οι βαθμωτές τιμές και τα αποθηκευμένα αποτελέσματα τύπων περνούν· τα στυλ, οι ζωντανές εκφράσεις τύπων ODF και τα σχέδια όχι. Αυτό έχει σημασία τη στιγμή που το αρχείο περιέχει πραγματικά αρχεία OpenDocument που διέπονται από το OASIS ODF 1.3, όπου οτιδήποτε κοντά σε μια οπτικά πιστή μετατροπή χρειάζεται περισσότερα απ' όσα χτίστηκε να μεταφέρει αυτή η διαδρομή εισαγωγής, και το πέρασμα ελέγχου είναι αυτό που σας λέει ότι υπάρχουν αυτά τα αρχεία προτού η παρτίδα τα ισοπεδώσει σιωπηλά

Η SaveXLSWorkbookAsXLSX είναι γέφυρα δεδομένων, όχι γέφυρα διάταξης

Η πρόσοψη BIFF δεν μπορεί να γράψει OOXML απευθείας, οπότε η διάβαση από .xls σε .xlsx περνά μέσα από τη συνάρτηση SaveXLSWorkbookAsXLSX στη μονάδα lxXlsxExport. Η πιστότητα αυτής της γέφυρας αξίζει να δηλωθεί ξεκάθαρα, επειδή το όνομα υπόσχεται περισσότερα απ' όσα κάνει. Αντιγράφει τιμές, τύπους, μορφές αριθμών, χρώματα γεμίσματος, βασικά χαρακτηριστικά γραμματοσειράς, πλάτη στηλών και ρυθμίσεις προβολής όπως τα πλέγματα. Δεν αντιγράφει περιγράμματα, συγχωνευμένα εύρη, σχόλια, γραφήματα ή μορφοποιήσεις υπό όρους. Για κανονικοποίηση σε επίπεδο δεδομένων, όπου συστήματα παρακάτω θα αναλύσουν το αποτέλεσμα και κανείς δεν κοιτάζει τη μορφοποίηση, αυτό είναι ακριβώς αρκετό και δεν χάνεται τίποτα που χρειάζεται κανείς. Για μια μορφοποιημένη αναφορά διοικητικού συμβουλίου προορισμένη να διαβαστεί από άνθρωπο, δεν είναι αρκετό, και εδώ ακριβώς κερδίζουν τη θέση τους οι μετρητές ελέγχου: ένα αρχείο που ο έλεγχος σημείωσε ότι φέρει γραφήματα και μορφοποιήσεις υπό όρους θα πρέπει να δρομολογηθεί σε μια χειροκίνητη ουρά, όχι μέσα από μια γέφυρα που θα ρίξει και τα δύο χωρίς λέξη

var
  Legacy: IXLSWorkbook;        // αναφορά interface: μην κάνετε Free
  Modern: TXLSXWorkbook;
begin
  if SameText(ExtractFileExt(FileName), '.xls') then
  begin
    Legacy := TXLSWorkbook.Create;
    if Legacy.Open(FileName) <= 0 then Exit;
    if SaveXLSWorkbookAsXLSX(Legacy,
         ChangeFileExt(FileName, '.xlsx')) <= 0 then
      Writeln('bridge failed: ' + FileName);
  end
  else
  begin
    Modern := TXLSXWorkbook.Create;
    try
      Modern.StreamingWrite := True;     // στέλνει το XML του φύλλου ως ροή μέσα στο zip
      if Modern.Open(FileName) = 1 then
        Modern.SaveAsCSV(ChangeFileExt(FileName, '.csv'), 0, ',');
    finally
      Modern.Free;
    end;
  end;
end;

Ο βρόχος παραπάνω δείχνει επίσης τον μοχλό ρυθμού διεκπεραίωσης στην πλευρά OOXML. Ο ορισμός της StreamingWrite σε true στέλνει το XML του φύλλου εργασίας απευθείας μέσα στο πακέτο εξόδου αντί να το σκηνοθετεί ως μία γιγάντια συμβολοσειρά στη μνήμη, κάτι που είναι η διαφορά ανάμεσα σε μια άνετη εκτέλεση και μια κατάρρευση λόγω έλλειψης μνήμης μόλις τα αρχεία φτάσουν σε εκατοντάδες χιλιάδες γραμμές. Η διαστασιολόγηση και η συμπεριφορά μνήμης για αυτή τη λειτουργία έχουν τη δική τους ανάλυση στο άρθρο μας για streaming εγγραφές σε δουλειές παρτίδας server. Μία ακόμα ιδιότητα έχει σημασία για μια παρτίδα που θέλει να χρησιμοποιήσει κάθε πυρήνα: καμία από τις δύο προσόψεις δεν είναι thread-safe, αλλά καμία δεν μοιράζεται ούτε global κατάσταση, οπότε το υποστηριζόμενο μοτίβο για παράλληλη μετατροπή είναι ένα instance βιβλίου εργασίας ανά worker thread, χωρίς κλείδωμα μεταξύ τους

Τα αρχεία με κωδικό πρόσβασης, και τι να τα κάνετε

Τα κλειδωμένα αρχεία του αρχείου χωρίζονται καθαρά ανά μορφή, και ο διαχωρισμός αποφασίζει πού πηγαίνουν. Η παλαιού τύπου κρυπτογράφηση .xls, είτε RC4, είτε RC4 πάνω από CryptoAPI, είτε η παλιά συσκότιση XOR, είναι αναγνώσιμη: περάστε τον κωδικό πρόσβασης στην Open και το αρχείο μετατρέπεται όπως κάθε άλλο. Τα κρυπτογραφημένα πακέτα .xlsx είναι διαφορετική ιστορία. Το HotXLS τα ανιχνεύει με την CanReadEncrypted αλλά δεν μπορεί να τα αποκρυπτογραφήσει, οπότε η μόνη έντιμη κίνηση είναι να τα δρομολογήσετε σε μια ουρά όπου ένας άνθρωπος ανοίγει και ξανααποθηκεύει το καθένα στο Excel πριν επανενταχθεί στο pipeline. Αυτή η ασυμμετρία αξίζει να σχεδιαστεί εκ των προτέρων, επειδή τα κρυπτογραφημένα αρχεία XLSX είναι αυτά που πιθανότατα είναι τα αρχεία που πραγματικά νοιάζεται κάποιος γι' αυτά

Κλείνοντας τον βρόχο με επαλήθευση

Το τρίτο στάδιο είναι αυτό που παραλείπεται, και η παράλειψή του είναι αυτό που μετατρέπει μια μαζική μετατροπή σε υποχρέωση κινδύνου. Καμία διαδρομή αποθήκευσης στο HotXLS δεν αξιολογεί τύπους. Το Excel επανυπολογίζει όταν ανοίγει ένα αρχείο, οπότε μια μετατροπή XLSX-σε-XLSX παραμένει σωστή, αλλά ένας προορισμός CSV λαμβάνει το κείμενο του τύπου αυτούσιο εκτός αν το pipeline τρέξει πρώτα Calculate στα κελιά και ξαναγράψει τα αποτελέσματα. Το να το γνωρίζετε αυτό εκ των προτέρων είναι η διαφορά ανάμεσα σε ένα CSV γεμάτο αριθμούς και ένα CSV γεμάτο συμβολοσειρές =SUM(...) που κανείς δεν προσέχει μέχρι μια εισαγωγή παρακάτω να πνιγεί πάνω τους

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

Ένας πάγκος εργασίας πρώτα-έλεγχος μετατρέπει μια ριψοκίνδυνη μαζική μετατροπή σε μια μετρήσιμη διαδικασία με μια λωρίδα καραντίνας για τα αρχεία που δεν μπορούν να περάσουν καθαρά. Όλες οι κλήσεις εξέτασης, καταμέτρησης και μετατροπής που παρουσιάζονται εδώ είναι μέρος του HotXLS Delphi Component, το οποίο τις τρέχει εγγενώς in-process χωρίς αυτοματισμό Excel