Επέκταση shared formula si σε XLSX με Delphi: παγίδες

Ένας follower κοινόχρηστου τύπου σε XLSX δεν φέρει κείμενο τύπου. Το στοιχείο του <f t="shared" si="N"/> δείχνει σε ένα master κελί αλλού στο φύλλο, και ο αναγνώστης πρέπει να ανακατασκευάσει το κείμενο μετατοπίζοντας τον master τύπο κατά τη διαφορά γραμμής και στήλης. Το HotXLS Component για Delphi και C++Builder κάνει αυτή την επέκταση κατά το άνοιγμα, οπότε κάθε follower αναφέρει έναν πλήρη τύπο

Αν έχεις ποτέ φορτώσει ένα πραγματικό XLSX σε βιβλιοθήκη τρίτου και βρήκες μια στήλη χιλίων τύπων με κείμενο σε ακριβώς ένα κελί και κενά strings στα υπόλοιπα 999, έχεις συναντήσει αυτό το χαρακτηριστικό από τη λάθος πλευρά. Τίποτα δεν είναι κατεστραμμένο. Το αρχείο κάνει αυτό που του επιτρέπει το ECMA-376, και ο αναγνώστης απλά σταμάτησε στο σημείο που σταμάτησε το XML

Γιατί το κελί κοινόχρηστου τύπου είναι κενό;

Γιατί η μορφή αποθηκεύει σκόπιμα τον τύπο μία φορά. Στο ECMA-376 Part 1 και το ISO/IEC 29500-1, το στοιχείο <f> (§18.3.1.40) φέρει ένα χαρακτηριστικό t τύπου ST_CellFormulaType, και η τιμή shared σημαίνει ότι αυτό το κελί συμμετέχει σε μια ομάδα ταυτοποιημένη από το χαρακτηριστικό si. Ακριβώς ένα κελί στην ομάδα, το master, φέρει επίσης ένα χαρακτηριστικό ref που δίνει το εύρος στο οποίο εφαρμόζεται η ομάδα, και μόνο αυτό το κελί φέρει το κείμενο τύπου ως περιεχόμενο στοιχείου. Κάθε άλλο κελί στην ομάδα είναι follower. Επαναλαμβάνει το t="shared" και το ίδιο si, και το περιεχόμενο στοιχείου του είναι κενό. Το Excel γράφει αυτές τις ομάδες επιθετικά, γιατί ένα fill-down πάνω σε στήλη 200.000 γραμμών συρρικνώνεται από 200.000 strings τύπου σε ένα string συν 199.999 μικροσκοπικά στοιχεία-δεσμευτές θέσης. Η εξοικονόμηση είναι πραγματική και το κόστος πέφτει εξ ολοκλήρου στον αναγνώστη: χωρίς επέκταση, ο follower δεν έχει νόημα από μόνος του

Η μετατόπιση είναι μετάφραση, όχι αντιγραφή κειμένου

Το HotXLS επιλύει έναν follower εντοπίζοντας τον master καταχωρημένο κάτω από το ίδιο si, υπολογίζοντας τη διαφορά γραμμής και στήλης από την άγκυρα του master στο τρέχον κελί, και μεταφράζοντας κάθε αναφορά στον master τύπο κατά αυτή τη διαφορά. Οι σχετικές διαστάσεις μετακινούνται, οι απόλυτες όχι, και οι μικτές αναφορές μετακινούν μόνο το μη-απόλυτο μισό τους. Τα κυριολεκτικά strings παραλείπονται εντελώς, οπότε ένας τύπος που τυχαίνει να περιέχει το κείμενο "A1" κρατά αυτό το κείμενο αμετάβλητο σε κάθε follower

const
  // xl/worksheets/sheet1.xml, trimmed to the interesting cells
  SheetXml: WideString=
    '<row r="1"><c r="A1"><v>1</v></c>'+
    '<c r="B1"><f t="shared" si="4" ref="B1:B3">'+
    'A1+$A$1+A$1+$A1+&quot;A1&quot;+SUM(A1:A2)</f><v>7</v></c></row>'+
    '<row r="2"><c r="B2"><f t="shared" si="4"/><v>8</v></c></row>'+
    '<row r="3"><c r="B3"><f t="shared" si="4"></f><v>9</v></c></row>';

var
  Wb: TXLSXWorkbook;
  Sh: TXLSXWorksheet;
begin
  Wb:= TXLSXWorkbook.Create;
  try
    Wb.Open(FileName);
    Sh:= Wb.Sheets[1];
    // Master, verbatim
    // B1 -> A1+$A$1+A$1+$A1+"A1"+SUM(A1:A2)
    // Follower one row down: relative row moves, absolute row frozen,
    // the mixed A$1 keeps its row, and the literal stays a literal
    // B2 -> A2+$A$1+A$1+$A2+"A1"+SUM(A2:A3)
    ShowMessage(Sh.Cells[2, 2].Formula);
  finally
    Wb.Free;
  end;
end;

Το χαρακτηριστικό ref είναι πύλη, όχι διακόσμηση. Ένας follower του οποίου οι συντεταγμένες πέφτουν έξω από το εφαρμόσιμο εύρος του master δεν επεκτείνεται, γιατί το αρχείο τότε κάνει μια δήλωση που η ομάδα δεν υποστηρίζει. Ομοίως, όταν μια μετατόπιση θα έσπρωχνε μια αναφορά πάνω από τη γραμμή ένα ή αριστερά από τη στήλη A, το HotXLS εκπέμπει #REF! για εκείνο το token αντί να το περιορίζει σιωπηρά, που είναι αυτό που θα παρήγαγε το ίδιο το Excel για την ίδια επεξεργασία. Αυτή η μετάφραση είναι στενή ξαδέρφη, αλλά όχι το ίδιο πράγμα, με την επανεγγραφή αναφορών που συμβαίνει όταν εισάγεις ή διαγράφεις γραμμές. Εκείνο το μονοπάτι έχει τους δικούς του κανόνες για το τι κάνει ένα εύρος όταν μια επεξεργασία το κόβει, και περιγράφεται ξεχωριστά στο άρθρο για την προσαρμογή αναφοράς τύπου κατά την εισαγωγή και διαγραφή. Η κοινόχρηστη επέκταση είναι απλούστερη: είναι μια καθαρή μετατόπιση από γνωστή άγκυρα, εφαρμοσμένη μία φορά, κατά τον χρόνο ανάλυσης

Ποια σχήματα αναφοράς πρέπει να καλύπτει ο μετατοπιστής;

Όλα, αλλιώς η επέκταση είναι ένα bug απώλειας δεδομένων μεταμφιεσμένο. Ένας αφελής μετατοπιστής που καταλαβαίνει μόνο τα A1 και A1:B2 θα διαφθείρει ή θα ρίξει τις πιο εξωτικές μορφές, και τα πραγματικά βιβλία εργασίας είναι γεμάτα από αυτές. Ο μεταφραστής κοινόχρηστου τύπου του HotXLS αναγνωρίζει ολόκληρη την οικογένεια A1 πριν αποφασίσει τι θα μετακινήσει. Εξωτερικές αναφορές βιβλίου εργασίας όπως [Book.xlsx]Sheet1!A1 και αναφορές 3D όπως Sheet1:Sheet3!A1 κρατούν το πρόθεμά τους ανέπαφο ενώ η ακόλουθη αναφορά κελιού μετατοπίζεται. Ονόματα φύλλων σε εισαγωγικά επιβιώνουν, συμπεριλαμβανομένης της δύσκολης περίπτωσης όπου το φύλλο ονομάζεται κυριολεκτικά A1, οπότε το 'A1'!A1 μετατοπίζει μόνο το μέρος μετά το θαυμαστικό. Το ολόκληρη-στήλη A:A μετακινεί τη διάσταση στήλης του και τίποτα άλλο· το ολόκληρη-γραμμή 1:1 μετακινεί τη διάσταση γραμμής του και τίποτα άλλο· το $A:$A δεν μετακινείται καθόλου. Δομημένες αναφορές πίνακα όπως Table[A1] μένουν ανέγγιχτες, γιατί το μέρος σε αγκύλες είναι όνομα στήλης, όχι συντεταγμένη

// One master, expanded two columns to the right and zero rows down.
// Master D1: A1+A:A+$A:$A
// F1       : C1+C:C+$A:$A
//
// One master, expanded three rows down and zero columns across.
// Master A1: B1+$C$1+"A1"+A:A+1:1+'Data'!A1+LOG10(A1)+Table[A1]+'A1'!A1
// A3       : B3+$C$1+"A1"+A:A+3:3+'Data'!A3+LOG10(A3)+Table[A1]+'A1'!A3
//
// Note what did NOT move in the second line: the absolute $C$1, the
// string literal "A1", the whole column A:A under a pure row delta,
// the function name LOG10, and the structured reference Table[A1]

Τα ονόματα συναρτήσεων είναι η σιωπηλή παγίδα εδώ. Ένας σαρωτής token που αρπάζει γράμματα ακολουθούμενα από ψηφία θα ξαναγράψει ευχαρίστως το LOG10 σε LOG11 μία γραμμή πιο κάτω. Το HotXLS απαιτεί ένα όριο αναφοράς πριν από ένα υποψήφιο token και μετά από αυτό, οπότε ένα αναγνωριστικό που συνεχίζει σε γράμμα, ψηφίο, κάτω παύλα, τελεία, ή ανοιχτή παρένθεση δεν είναι αναφορά κελιού. Αν δουλεύεις στην άλλη οικογένεια σημειογραφίας, το ίδιο πρόβλημα ορίου εμφανίζεται διαφορετικά, και το άρθρο για τη σημειογραφία R1C1 καλύπτει πού αποκλίνουν τα δύο μοντέλα

Γιατί ένα αυτοκλειόμενο στοιχείο f καταπίνει την επόμενη τιμή;

Γιατί ένα αυτοκλειόμενο στοιχείο δεν παράγει συμβάν τέλους στοιχείου. Αυτό είναι το πιο ακριβό bug σε ολόκληρο το χαρακτηριστικό, και δεν είναι ειδικό σε κανέναν συγκεκριμένο parser XML. Στο TXMLReader, το <f t="shared" si="4"/> εγείρει ακριβώς ένα συμβάν Element με το IsEmptyElement ορισμένο σε True, και ποτέ δεν εγείρει το αντίστοιχο EndElement. Ένας parser που κλείνει την κατάσταση καταγραφής τύπου του μόνο στο EndElement επομένως παραμένει μέσα στον τύπο, και το επόμενο κείμενο που βλέπει, που είναι το cached αποτέλεσμα μέσα στο <v>, προσαρτάται στον buffer τύπου. Χειρότερα, η κατάσταση επιβιώνει το όριο κελιού, οπότε το επόμενο κελί που έχει ένα πραγματικό <f> βλέπει το κείμενο τύπου του να απορροφάται από το προηγούμενο κελί. Η διόρθωση είναι να τερματίζεται η κατάσταση τύπου στο ίδιο το συμβάν Element όποτε το IsEmptyElement είναι True, και να τρέχει εκεί ολόκληρη η επίλυση follower αντί να περιμένει. Αυτό σημαίνει ανάγνωση των t, si, ref, aca, και ca από τα χαρακτηριστικά, εφαρμογή της κοινόχρηστης επέκτασης, εγγραφή των χαρακτηριστικών επαναϋπολογισμού πάνω στο κελί, και καθαρισμό της κοινόχρηστης κατάστασης, όλα μέσα στον κλάδο που χειρίζεται το κενό στοιχείο. Πρόσεξε ότι η μορφή επιτρέπει και τις δύο γραφές, <f t="shared" si="4"/> και <f t="shared" si="4"></f>, και η δεύτερη εγείρει πράγματι EndElement. Ένας σωστός αναγνώστης πρέπει να χειρίζεται το ζεύγος πανομοιότυπα, γι' αυτό το HotXLS καλύπτει και τις δύο γραφές στο ίδιο αρχείο regression

Αραιές, μη διατεταγμένες τιμές si και η ουρά εκκρεμοτήτων

Το χαρακτηριστικό si είναι ένας ανεπίσημος ακέραιος χωρίς πρόσημο που παρέχεται από το αρχείο, όχι μια θέση πίνακα που ελέγχεις εσύ. Τίποτα στο σχήμα δεν απαιτεί οι κοινόχρηστοι δείκτες να είναι πυκνοί, να ξεκινούν από το μηδέν, ή να εμφανίζονται σε αύξουσα σειρά, και τίποτα δεν εμποδίζει ένα εχθρικό ή απλώς παράξενο αρχείο από το να χρησιμοποιήσει si="4294967290" στο πρώτο κελί. Η διαστασιολόγηση ενός πίνακα αναζήτησης από το μεγαλύτερο παρατηρημένο si είναι επομένως μια πρωτόγονη λειτουργία εξάντλησης μνήμης, όχι βελτιστοποίηση. Το HotXLS κρατά το μονοπάτι ανοίγματος βιβλίου εργασίας σε έναν ταξινομημένο αραιό πίνακα αντ' αυτού: οι κοινόχρηστες ομάδες καταχωρούνται κάτω από το ακέραιο κλειδί τους σε μια ταξινομημένη TStringList, κάτι που κάνει την αναζήτηση δυαδική αναζήτηση πάνω σε όσες ομάδες πραγματικά υπάρχουν, χωρίς σχέση με το αριθμητικό μέγεθος των δεικτών. Η σειρά είναι το δεύτερο μισό του προβλήματος. Ένας master κανονικά προηγείται των followers του στη σειρά εγγράφου, αλλά αυτό είναι σύμβαση παρά κανόνας, οπότε κάθε follower που δεν μπορεί να επιλύσει το si του τη στιγμή που αναλύεται πηγαίνει σε μια ουρά εκκρεμοτήτων. Όταν το φύλλο ολοκληρωθεί, η ουρά αναπαράγεται έναντι του πλέον πλήρους πίνακα, και οι καθυστερημένοι masters επιλύουν τα ορφανά τους. Κελιά που ποτέ δεν βρίσκουν master κρατούν κενό τύπο, που είναι η ειλικρινής έκβαση για ένα αρχείο που αναφέρεται σε μια ομάδα που ποτέ δεν όρισε

Επέκταση κοινόχρηστων τύπων χωρίς φόρτωση του βιβλίου εργασίας

Οι streaming αναγνώστες αντιμετωπίζουν την ίδια απαίτηση κάτω από πολύ πιο σφιχτό προϋπολογισμό μνήμης, και το λύνουν με έναν πίνακα τοπικό στο φύλλο εργασίας. Οι TXLSDirectReader και TXLSRowCursor επεκτείνουν και οι δύο τους followers σε πλήρεις τύπους ανά κελί ενώ διατηρούν τη συμπεριφορά περιορισμένης μνήμης και προβολής τους, οπότε ένα πέρασμα μόνο-προς-τα-εμπρός πάνω σε ένα φύλλο 300 MB εξακολουθεί να σου δίνει πραγματικό κείμενο τύπου

var
  Reader: TXLSDirectReader;
  Cursor: TXLSRowCursor;
begin
  // Projection: only rows 2..3, only column A. The master lives in row 1,
  // outside the projection, and is still parsed so the followers resolve
  Reader:= TXLSDirectReader.Create;
  try
    Reader.FirstRow:= 2;
    Reader.LastRow:= 3;
    Reader.IncludeColumn(1);
    Reader.OnCell:= HandleCell;   // Cell.Formula is fully expanded here
    Reader.ReadFile(FileName);
  finally
    Reader.Free;
  end;

  // Forward-only row traversal, same expansion
  Cursor:= TXLSRowCursor.Create;
  try
    Cursor.Open(FileName);
    if Cursor.FindFirst then
      repeat
        if Cursor.CellCount > 0 then
          WriteLn(Cursor.RowIndex, ': ', Cursor.Cells[0].Formula);
      until not Cursor.FindNext;
  finally
    Cursor.Free;
  end;
end;

Δύο περιορισμοί προκύπτουν από αυτόν τον σχεδιασμό. Πρώτον, η προβολή δεν μπορεί ποτέ να παραλείψει τον master. Ένα φίλτρο γραμμής ορισμένο με FirstRow και LastRow, ή ένα φίλτρο στήλης χτισμένο με IncludeColumn, μπορεί να παραλείψει την εκπομπή του κελιού master στο callback σου, αλλά ο parser πρέπει ακόμα να καταγράψει το si, τις συντεταγμένες άγκυρας, το εφαρμόσιμο εύρος, και το κείμενο τύπου του, αλλιώς κάθε follower μέσα στην προβολή επιλύεται σε τίποτα. Μόνο η εργασία στην πλευρά follower, η μετατόπιση και η αποκωδικοποίηση τιμής, είναι ασφαλές να παραλειφθεί. Δεύτερον, ο πίνακας είναι ανά φύλλο εργασίας και ο κύκλος ζωής του πρέπει να διαχειρίζεται ρητά: το TXLSRowCursor κρατά μία περίπτωση για τη διάρκεια ενός περάσματος φύλλου και την καθαρίζει σε επανεκκίνηση, αλλαγή φύλλου, τέλος αρχείου, εξαίρεση, και κλείσιμο, οπότε μια ομάδα ορισμένη σε φύλλο ένα δεν μπορεί ποτέ να διαρρεύσει σε φύλλο δύο. Επειδή το μονοπάτι streaming είναι κρίσιμος βρόχος, χρησιμοποιεί ένα open-addressing ακέραιο hash αντί για τον ταξινομημένο πίνακα strings, κάτι που αποφεύγει μια μετατροπή ακεραίου-σε-string ανά κελί

Τι συμβαίνει κατά την αποθήκευση, και πού είναι τα όρια

Μόλις ένας follower έχει επεκταθεί είναι ένας συνηθισμένος τύπος, και το HotXLS τον ξαναγράφει ως ανεξάρτητο στοιχείο <f> χωρίς t="shared" και χωρίς si. Το round trip είναι σταθερό και τα cached αποτελέσματα <v> επιβιώνουν, αλλά η έξοδος είναι μεγαλύτερη από την είσοδο για ένα έντονα κοινόχρηστο φύλλο, και η ομαδοποίηση που δημιούργησε το Excel δεν ανακατασκευάζεται κατά την αποθήκευση. Αν η πιστότητα σε επίπεδο byte των κοινόχρηστων ομάδων σε νοιάζει περισσότερο από το να έχεις πραγματικό κείμενο τύπου σε κάθε κελί, αυτή είναι η ανταλλαγή που αποδέχεσαι. Η πλευρά XLS είναι διαφορετική, παρεμπιπτόντως: η εγγραφή BIFF8 SHRFMLA έχει τη δική της κωδικοποίηση και τον δικό της writer, με έναν διακόπτη κοινόχρηστης ομάδας στο βιβλίο εργασίας

Δύο σχετικά πράγματα δεν είναι ρητά κοινόχρηστοι τύποι παρόλο που μοιράζονται το στοιχείο <f>. Οι παλαιοί τύποι πίνακα CSE χρησιμοποιούν t="array" με ένα ref που καλύπτει το αγκυρωμένο εύρος, και οι δυναμικοί πίνακες χρησιμοποιούν την ίδια γραφή t="array" αλλά ταυτοποιούνται από ένα χαρακτηριστικό cm που αλυσιδώνει μέσω του cellMetadata σε μια εγγραφή XLDAPR. Η μεταχείριση ενός κελιού spill δυναμικού πίνακα ως κοινόχρηστου ή CSE follower είναι ένα γνήσιο bug ορθότητας, και ο διαχωρισμός καλύπτεται στο άρθρο για τύπους δυναμικού πίνακα και spill. Διάβασε τις τρεις περιπτώσεις ως τρεις parsers που τυχαίνει να μοιράζονται ένα όνομα ετικέτας, και ο κώδικας παραμένει ειλικρινής

Η επέκταση κοινόχρηστου τύπου, οι streaming αναγνώστες, και ο μεταφραστής αναφορών που περιγράφονται εδώ διατίθενται ως μέρος του στοιχείου Excel HotXLS για Delphi και C++Builder· η σελίδα του προϊόντος φέρει την πλήρη αναφορά API τύπων και direct-read, συμπεριλαμβανομένων των ιδιοτήτων προβολής που χρησιμοποιήθηκαν παραπάνω