Δημιουργία αναφορών Excel βάσει προτύπων στο Delphi με το HotXLS

Ο αξιόπιστος τρόπος να παράγετε μια μορφοποιημένη αναφορά Excel από τη Delphi είναι να ξεκινάτε από ένα βιβλίο εργασίας που έχει ήδη φτιάξει ένας σχεδιαστής. Κάποιος στα οικονομικά διαμορφώνει το τιμολόγιο στο Excel: το λογότυπο, τις κεφαλίδες στηλών, τα περιγράμματα στη ζώνη λεπτομερειών, τη γραμμή συνόλων με έντονα γράμματα, τις μορφές νομίσματος. Ο κώδικάς σας ανοίγει αυτό το αρχείο, ρίχνει ζωντανά δεδομένα στα κελιά που ο σχεδιαστής δέσμευσε γι' αυτά, και αποθηκεύει το αποτέλεσμα. Η εμφάνιση είναι δική τους· οι αριθμοί είναι δικοί σας. Το HotXLS, μια εγγενής βιβλιοθήκη για Delphi και C++Builder που διαβάζει και γράφει βιβλία εργασίας XLS και XLSX χωρίς να οδηγεί το Excel, σας δίνει τις τρεις λειτουργίες που χρειάζεται αυτή η προσέγγιση: αναζήτηση κελιού βάσει του κειμένου του, αντιγραφή ενός εύρους με τα στυλ και τους τύπους του άθικτα, και εισαγωγή γραμμών ώστε όλα από κάτω να μετατοπίζονται προς τα κάτω μαζί με τα δεδομένα

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

Αγκυρώστε κάθε συντεταγμένη σε ένα token δεσμευτή θέσης

Η λύση είναι να κάνετε το πρότυπο να φέρει τις δικές του συντεταγμένες. Ο σχεδιαστής γράφει tokens όπως {{CUSTOMER}}, {{DATE}} και {{DETAIL_START}} μέσα στα κελιά που πρέπει να αγγίξει η γεννήτρια, και η γεννήτρια υπολογίζει κάθε θέση κατά την εκτέλεση από το πού βρίσκει αυτά τα tokens. Οι επεξεργασίες διάταξης δεν έχουν πλέον σημασία, επειδή το token μετακινείται μαζί με το κελί στο οποίο βρίσκεται. Το δεύτερο μισό του συμβολαίου είναι ο κανόνας αποτυχίας: αν λείπει ένα απαιτούμενο token, η εργασία σταματά πριν φτάσουν οποιαδήποτε δεδομένα πελάτη στο αρχείο. Ένα πρότυπο που έχει παρεκκλίνει θα πρέπει να παράγει ένα αποτυχημένο δελτίο εργασίας, όχι ένα παραδοτέο έγγραφο

Εύρεση των tokens: FindText και ReplaceText

Και οι δύο οικογένειες κλάσεων του HotXLS εκθέτουν αναζήτηση σε επίπεδο φύλλου εργασίας. Η FindText επιστρέφει τη γραμμή και τη στήλη του πρώτου κελιού του οποίου το κείμενο ταιριάζει, με μια υπερφόρτωση που προσθέτει διάκριση πεζών-κεφαλαίων. Η ReplaceText αντικαθιστά κάθε εμφάνιση και επιστρέφει πόσες άλλαξε. Οι δύο καλύπτουν τα δύο είδη token που συνήθως έχετε. Μια μοναδική άγκυρα όπως το όνομα πελάτη την εντοπίζετε μία φορά και γράφετε δίπλα της· ένα token που θα έπρεπε να εμφανίζεται ακριβώς μία φορά, όπως η ημερομηνία αναφοράς, το αντικαθιστάτε και ελέγχετε τον αριθμό. Στην πλευρά XLSX, μια συμπλήρωση που αγκυρώνεται με αυτόν τον τρόπο μοιάζει ως εξής:

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  R, C: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    if Book.Open('invoice-template.xlsx') <> 1 then
      raise Exception.Create('Cannot open invoice template');
    Sheet := Book.Sheets[0];               // το TXLSXSheets.Items είναι βάσει 0

    if not Sheet.FindText('{{CUSTOMER}}', R, C) then
      raise Exception.Create('Template drift: {{CUSTOMER}} anchor missing');
    Sheet.Cells[R, C].Value := 'ACME Corp';

    if Sheet.ReplaceText('{{DATE}}',
         FormatDateTime('yyyy-mm-dd', Date)) = 0 then
      raise Exception.Create('Template drift: {{DATE}} token missing');
    // η επέκταση λεπτομερειών και η αποθήκευση ακολουθούν παρακάτω
  finally
    Book.Free;
  end;
end;

Δύο λεπτομέρειες έχουν σημασία. Πρώτον, οι FindText και ReplaceText ταιριάζουν με την τιμή κειμένου ενός κελιού· ένα token ενσωματωμένο μέσα σε μια συμβολοσειρά τύπου είναι αόρατο σε αυτές, οπότε τα tokens δεσμευτή θέσης ανήκουν σε απλά κελιά, ποτέ μέσα σε τύπους. Δεύτερον, ο αριθμός αντικαταστάσεων είναι ο ανιχνευτής παρέκκλισής σας. Ένα πρότυπο που θα έπρεπε να περιέχει ακριβώς ένα token {{DATE}} αλλά αναφέρει μηδέν αντικαταστάσεις έχει υποστεί επεξεργασία, και η έγερση μιας εξαίρεσης εκείνη τη στιγμή είναι ακριβώς αυτό που μετατρέπει μια σιωπηλή παρέκκλιση διάταξης σε ορατή αποτυχία

Κλωνοποίηση της γραμμής λεπτομέρειας χωρίς απώλεια στυλ ή τύπων

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

const
  DetailRow = 10;            // η μορφοποιημένη γραμμή δείγματος στο πρότυπο
var
  I: Integer;
begin
  // Ανοίξτε πρώτα χώρο πριν από το μπλοκ συνόλων, ώστε το εύρος SUM
  // κάτω από τη ζώνη λεπτομερειών να επεκτείνεται μαζί με τα δεδομένα.
  if Length(Items) > 1 then
    Sheet.InsertRows(DetailRow + 1, Length(Items) - 1);

  for I := 0 to High(Items) do
  begin
    if I > 0 then              // κλωνοποιεί στυλ + τύπους από τη γραμμή δείγματος
      Sheet.CopyRange(DetailRow, 1, DetailRow, 5, DetailRow + I, 1);
    Sheet.Cells[DetailRow + I, 1].Value := Items[I].Name;
    Sheet.Cells[DetailRow + I, 2].Value := Items[I].Qty;
    Sheet.Cells[DetailRow + I, 3].Value := Items[I].UnitPrice;
    Sheet.Cells[DetailRow + I, 4].Formula :=
      Format('B%d*C%d', [DetailRow + I, DetailRow + I]);  // χωρίς πρόθεμα '='
  end;
end;

Παρακολουθήστε προσεκτικά την ανάθεση τύπου. Η ιδιότητα Formula του XLSX δέχεται την έκφραση χωρίς αρχικό σύμβολο ίσον, ενώ η πρόσοψη XLS περιμένει '=B10*C10' ανατεθειμένο μέσω της Value. Το ανακάτεμα των δύο συμβάσεων είναι το πιο συνηθισμένο λάθος μεταφοράς ανάμεσα στις οικογένειες κλάσεων, και αποτυγχάνει χωρίς παράπονο: το κελί απλώς κρατά μια κυριολεκτική συμβολοσειρά που το Excel εμφανίζει ως κείμενο. Αν το πρότυπο διακοσμεί τη ζώνη λεπτομερειών με συγχωνευμένες γραμμές τίτλου, θυμηθείτε ότι μόνο το πάνω-αριστερά κελί μιας συγχωνευμένης περιοχής φέρει τιμή. Οι κανόνες διάταξης στο συνοδευτικό άρθρο για τα συγχωνευμένα κελιά σε πρότυπα αναφορών βασισμένα σε διάταξη εξηγούν γιατί οι περιοχές συγχώνευσης ανήκουν εντελώς έξω από τη ζώνη δεδομένων

Τι μετακινεί η InsertRows, και τι αφήνει πίσω

Η εισαγωγή γραμμών πριν από το μπλοκ συνόλων είναι αυτό που κρατά ένα εύρος SUM να επεκτείνεται καθώς μεγαλώνει η ενότητα λεπτομερειών. Στην πλευρά XLSX, η InsertRows μεταφέρει μια μακριά λίστα εξαρτημένων δομών μαζί με τα κελιά: συγχωνευμένα εύρη, ύψη γραμμών, hyperlinks, σχόλια, παγωμένα παράθυρα, εύρη αυτόματου φίλτρου, μορφοποιήσεις υπό όρους, επικυρώσεις δεδομένων, πίνακες, defined names και άγκυρες εικόνων και γραφημάτων. Υπάρχει ένα όριο σε αυτή τη λίστα που αξίζει να απομνημονεύσετε. Η αναδιατύπωση τύπων φτάνει μόνο σε αναφορές μέσα στο ίδιο φύλλο. Ένας τύπος σε ένα φύλλο σύνοψης που δείχνει προς την περιοχή που μετακινήθηκε κρατά τις παλιές συντεταγμένες του και αθόρυβα διαβάζει τα λάθος κελιά, γι' αυτό και τα σύνολα που αντλούνται από άλλα φύλλα εκφράζονται πιο ασφαλώς μέσω ονομάτων σε επίπεδο βιβλίου εργασίας. Το συνοδευτικό άρθρο για τα defined names και τους τύπους μεταξύ φύλλων αναλύει αυτό το μοτίβο

Η παλαιού τύπου μορφή XLS χαράζει τη γραμμή σε πιο δύσκολο σημείο. Το HotXLS κρατά τους πίνακες pivot, τους πίνακες ερωτημάτων και τις εξωτερικές συνδέσεις δεδομένων σε αρχεία BIFF ως ακατέργαστα blocks byte. Επιβιώνουν στο άνοιγμα και την αποθήκευση χωρίς αλλαγή, αλλά δεν μοντελοποιούνται, οπότε η εισαγωγή γραμμών ποτέ δεν τους αγγίζει. Ένα πρότυπο που τοποθετεί έναν πίνακα pivot κάτω από ένα μπλοκ λεπτομερειών που επεκτείνεται αποθηκεύεται χωρίς καμία προειδοποίηση, ενώ το ορθογώνιο πηγής του pivot παρεκκλίνει από τα δεδομένα. Η διέξοδος είναι διαρθρωτική, όχι αμυντική: κρατήστε το περιεχόμενο pivot και ερωτημάτων σε φύλλα στα οποία η γεννήτρια ποτέ δεν εισάγει, και η απαρχαίωση δεν μπορεί να συμβεί

Επανυπολογίστε πριν από την παράδοση, ή γνωρίστε γιατί το παραλείψατε

Το HotXLS δεν αξιολογεί τύπους κατά τη διάρκεια της SaveAs. Όταν ένα άτομο ανοίγει το αρχείο, το Excel επανυπολογίζει τα πάντα (η πρόσοψη XLS εκθέτει τις CalculationMode και RecalcOnSave αν χρειάζεστε να το κατευθύνετε), οπότε μια αναφορά προορισμένη για ένα ανθρώπινο inbox δεν χρειάζεται τίποτα περισσότερο από εσάς. Η εικόνα αλλάζει τη στιγμή που το βιβλίο εργασίας τροφοδοτεί ένα άλλο πρόγραμμα. Η εξαγωγή CSV γράφει τους τύπους ως το κυριολεκτικό κείμενό τους και ποτέ δεν τους υπολογίζει, και οποιοσδήποτε parser παρακάτω που εμπιστεύεται τις αποθηκευμένες τιμές θα διαβάσει μπαγιάτικους αριθμούς ή κενά. Για αυτές τις διαδρομές, υπολογίστε στον server με την Calculate, η οποία αξιολογεί μια αυθαίρετη έκφραση έναντι του φορτωμένου βιβλίου εργασίας και επιστρέφει το αποτέλεσμα:

var
  Total: Variant;
  LastDetail: Integer;
begin
  LastDetail := DetailRow + Length(Items) - 1;
  Total := Book.Calculate(Format('SUM(Invoice!D%d:D%d)',
    [DetailRow, LastDetail]));
  if (not VarIsNumeric(Total)) or
     (Abs(Total - ExpectedTotal) > 0.005) then
    raise Exception.Create('Invoice total does not match the order record');

  if Book.SaveAs('invoice-2026-0611.xlsx') <> 1 then
    raise Exception.Create('Save failed: check output path and permissions');
end;

Ο έλεγχος του υπολογισμένου συνόλου έναντι της εγγραφής παραγγελίας πριν από την αποθήκευση είναι φθηνή ασφάλεια με καλή απόδοση. Μετατρέπει ένα λάθος τιμολόγιο σε μια αποτυχημένη εργασία. Ένας χειριστής μπορεί να ξαναδοκιμάσει μια αποτυχημένη εργασία σε δευτερόλεπτα· ένα λάθος τιμολόγιο που βρίσκεται ήδη στα εισερχόμενα ενός πελάτη κοστίζει σε έναν account manager μια συγγνώμη και μια διόρθωση

Δύο οικογένειες κλάσεων, ένας αλγόριθμος

Η ίδια λογική μεταφέρεται ανάμεσα στις μορφές, αλλά όχι ο ίδιος κώδικας. Η TXLSWorkbook για το παλαιού τύπου .xls είναι βασισμένη σε interface και μετρά αναφορές, με ευρετηρίαση φύλλων βάσει 1, και ποτέ δεν την ελευθερώνετε με το χέρι. Η TXLSXWorkbook για .xlsx είναι ένα απλό αντικείμενο που πρέπει να ελευθερώσετε σε ένα try..finally, με ευρετηρίαση φύλλων βάσει 0 και τη σύμβαση τύπων που φαίνεται παραπάνω. Οι FindText, ReplaceText, CopyRange και InsertRows ζουν και οι τέσσερις και στις δύο πλευρές, οπότε το σχήμα άγκυρα-κλωνοποίηση-επανυπολογισμός μεταφέρεται καθαρά. Η πρακτική συμβουλή είναι να δεσμευτείτε σε μία μορφή ανά pipeline, ή να κρύψετε τους δύο κύκλους ζωής αντικειμένων πίσω από έναν δικό σας λεπτό adapter αντί να διασκορπίζετε τη διαφορά μέσα στη γεννήτρια

Το μέγεθος σπάνια έχει σημασία για το είδος αναφοράς που παράγει αυτό το μοτίβο. Η κλωνοποίηση μιας μορφοποιημένης γραμμής μερικές χιλιάδες φορές δεν είναι τίποτα για το σημερινό υλικό. Η διαδρομή αποθήκευσης γίνεται σημείο συμφόρησης μόνο όταν μια ζώνη λεπτομερειών φτάνει σε εξαψήφιο αριθμό γραμμών, και εκείνη τη στιγμή η ενεργοποίηση της StreamingWrite στέλνει το XML του φύλλου εργασίας απευθείας στο πακέτο εξόδου αντί να το προσωρινά αποθηκεύει· το άρθρο για streaming εγγραφές σε δουλειές παρτίδας server καλύπτει πότε αξίζει αυτός ο συμβιβασμός. Τα γραφήματα συμπεριφέρονται όπως η υπόλοιπη διάταξη: στην πλευρά XLSX τόσο η άγκυρα του γραφήματος όσο και οι αναφορές σειράς του μετακινούνται όταν τρέχει η InsertRows πάνω από αυτά, οπότε ένα γράφημα κάτω από τη γραμμή συνόλων παραμένει δεμένο με τα σωστά δεδομένα, ενώ στην πλευρά XLS τα γραφήματα κάθονται στα δικά τους φύλλα γραφημάτων και, όπως οι πίνακες pivot, ποτέ δεν μετατοπίζονται. Αυτό είναι ένα ακόμα επιχείρημα για να κρατάτε τα φύλλα παρουσίασης καθαρά από το φύλλο που επεκτείνει η γεννήτρια

Αυτή η προσέγγιση άγκυρα-κλωνοποίηση-επανυπολογισμός επιτρέπει σε έναν σχεδιαστή να κατέχει το πώς φαίνεται ένα βιβλίο εργασίας ενώ ο κώδικάς σας κατέχει τι λέει, κάτι που συνήθως είναι αυτό που κάνει την παραγόμενη έξοδο Excel να αξίζει να συντηρείται. Οι κλήσεις αναζήτησης, αντιγραφής και εισαγωγής που παρουσιάζονται εδώ, μαζί με τη μηχανή τύπων που χρησιμοποιείται για τον έλεγχο συνόλου πριν από την παράδοση, συνοδεύουν το HotXLS Delphi Component για Delphi και C++Builder