Ανάγνωση cached τιμών τύπων Excel σε Delphi χωρίς recalc

Το HotXLS, η εγγενής βιβλιοθήκη Excel για Delphi και C++Builder, διαβάζει την τιμή που το Excel έχει ήδη αποθηκεύσει δίπλα σε έναν τύπο μέσω TryGetCachedFormulaValue και IXLSFormulaCacheReader. Κανένα από τα δύο σημεία εισόδου δεν καλεί την αριθμομηχανή, δεν απομεταφράζει tokens τύπων, δεν ενημερώνει dirty κατάσταση, ούτε γράφει τίποτα πίσω στο μοντέλο, οπότε ένα βιβλίο εργασίας που απλώς διαβάζετε μένει ακριβώς όπως το ανοίξατε

Το σενάριο που οδηγεί εδώ είναι βαρετό και εξαιρετικά συνηθισμένο. Ένα nightly job ανοίγει μερικές εκατοντάδες βιβλία εργασίας φτιαγμένα από άλλους, τραβάει μια στήλη συνόλων από το καθένα, και σπρώχνει τους αριθμούς σε ένα warehouse. Τα σύνολα κάθονται ήδη στα αρχεία — το Excel τα υπολόγισε και τα αποθήκευσε. Όμως τη στιγμή που το job ζητά την τιμή από ένα κελί τύπου, μια βιβλιοθήκη που έχει μόνο μία απάντηση για αυτή την ερώτηση χτίζει γράφο εξαρτήσεων και αξιολογεί όλο το φύλλο, και ένα job που έπρεπε να είναι δεσμευμένο σε I/O γίνεται benchmark υπολογισμών

Γιατί η ανάγνωση κελιού τύπου κοστίζει πλήρη επανυπολογισμό;

Επειδή ένας getter τιμής σε κελί τύπου είναι αίτημα να παράγετε μια τιμή, και ο μόνος καθολικά σωστός τρόπος να παράγετε μία είναι να αξιολογήσετε τον τύπο. Αυτή είναι η σωστή προεπιλογή για εφαρμογή που επεξεργάζεται βιβλία εργασίας, και η λάθος προεπιλογή για γραμμή που τα εξάγει. Χειρότερα, η αξιολόγηση δεν είναι χωρίς παρενέργειες: γράφει αποτελέσματα πίσω στα κελιά, αναποδογυρίζει dirty flags, και μπορεί να επιλύθεί διαφορετικά από την εφαρμογή παραγωγής όταν μια συνάρτηση δεν υποστηρίζεται ή μια εξωτερική αναφορά είναι κομμένη. Ένα job που περιγράψατε στην ομάδα λειτουργιών ως read-only παράγει σιωπηλά ένα βιβλίο εργασίας που δεν ταιριάζει πια με αυτό στον δίσκο, και αν κάτι το αποθηκεύσει αργότερα, το αρχείο στον δίσκο αλλάζει κι αυτό

Η ανάγνωση cached τιμών είναι το άλλο μισό του συμβολαίου. Απαντά σε μια στενότερη ερώτηση — τι αποθήκευσε εδώ η εφαρμογή παραγωγής; — και αρνείται να απαντήσει σε οτιδήποτε άλλο. Όταν θέλετε πραγματικά φρέσκους αριθμούς, το HotXLS σάς δίνει ακόμα τον incremental επανυπολογισμό με γράφο εξαρτήσεων; το ζητούμενο είναι ότι η εξαγωγή και η αξιολόγηση πρέπει να είναι δύο διαφορετικές κλήσεις, όχι μία κλήση με δύο διαθέσεις

Τρεις ορθογώνιες αλήθειες για ένα κελί

Πρώτα το συμπέρασμα: μια cached τιμή τύπου κουβαλά τρεις ανεξάρτητες αλήθειες, και το να τις συμπτύξετε σε ένα μοναδικό Variant χάνει πληροφορία που χρειάζεστε. Το TXLSFormulaCacheInfo τις κρατά χωριστές ως State, Kind και Value. Το TXLSFormulaCacheState καταγράφει την προέλευση σε πέντε περιπτώσεις — xlfcsNotFormula, xlfcsMissing, xlfcsLoaded, xlfcsCalculated και xlfcsInvalidated — ενώ το TXLSFormulaCacheValueKind ταξινομεί το payload ως xlfcvBlank, xlfcvNumber, xlfcvDateTime, xlfcvString, xlfcvBoolean ή xlfcvError. Αυτός ο διαχωρισμός είναι που επιτρέπει η παρουσία να αναφέρεται τίμια: ένα cached blank, ένα cached κενό string, ένα cached False, ένα cached μηδέν και ένα cached σφάλμα είναι όλες πραγματικές τιμές, οπότε η παρουσία δεν μπορεί ποτέ να συναχθεί από VarIsEmpty ή VarIsNull. Το TryGetCachedFormulaValue επιστρέφει True μόνο για xlfcsLoaded και xlfcsCalculated, και ακόμα γεμίζει μια διαγνώσιμη κατάσταση όταν επιστρέφει False

var
  Book: TXLSXWorkbook;
  Info: TXLSFormulaCacheInfo;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('quarterly-model.xlsx');
    // SheetIndex, Row και Col είναι όλα 1-based εδώ
    if Book.TryGetCachedFormulaValue(1, 12, 5, Info) then
      Writeln('cached value: ', VarToStr(Info.Value))
    else
      Writeln('no usable cache, state ordinal ', Ord(Info.State));
  finally
    Book.Free;
  end;
end;

Γιατί λείπει η cached τιμή;

Υπάρχουν ακριβώς τέσσερις λόγοι για τους οποίους το TryGetCachedFormulaValue επιστρέφει False, και η κατάσταση σάς λέει ποιος ισχύει. Το xlfcsNotFormula σημαίνει ότι το κελί κρατά literal ή τίποτα απολύτως, και οι συντεταγμένες εκτός εύρους συμπτύσσονται στην ίδια απάντηση. Το xlfcsMissing σημαίνει ότι το κελί όντως είναι τύπος αλλά ο παραγωγός δεν αποθήκευσε payload τιμής γι' αυτό — σύνηθες αποτέλεσμα όταν μια γεννήτρια γράφει τύπους και αφήνει το Excel να γεμίσει αποτελέσματα στο πρώτο άνοιγμα. Το xlfcsInvalidated σημαίνει ότι το κείμενο του τύπου αντικαταστάθηκε μετά τη φόρτωση, οπότε η τιμή που ήταν εκεί περιγράφει μια έκφραση που δεν υπάρχει πια. Το xlfcsCalculated, αντιθέτως, είναι περίπτωση επιτυχίας: σηματοδοτεί τιμή που παρήγαγε ο δικός σας κώδικας ή ο evaluator του HotXLS σε αυτή τη συνεδρία, σε αντίθεση με το xlfcsLoaded, που ήρθε από το αρχείο

Η ειλικρίνεια για μια ελλιπή cache μετράει περισσότερο από το να την κρύψετε. Το HotXLS αρνείται να επινοήσει τιμή, και στην αποθήκευση είναι εξίσου αυστηρό — μόνο xlfcsLoaded και xlfcsCalculated εκπέμπουν cached τιμή, ενώ xlfcsMissing και xlfcsInvalidated γράφουν τον τύπο μόνο του αντί να παγώσουν ένα ξεπερασμένο νούμερο στο αρχείο. Σας αφήνουν τρεις λογικές απαντήσεις σε μια γραμμή: παράλειψη της γραμμής και καταγραφή του κενού, σκόπιμος επανυπολογισμός μόνο εκείνου του βιβλίου εργασίας με αποδοχή του κόστους, ή αξιολόγηση και συμφωνία. Αν ο υπολογισμένος αριθμός διαφωνεί με αυτό που θα είχε γράψει η εφαρμογή παραγωγής, ο tracer αξιολόγησης τύπων είναι το εργαλείο για να βρείτε πού αποκλίνουν οι δύο υπολογισμοί, αντί να μαντεύετε από το αποτέλεσμα

Ένας αναγνώστης για τις μηχανές classic, OOXML και ODF

Μια γραμμή δεν θα πρέπει να νοιάζεται αν το αρχείο που μόλις άνοιξε ήταν BIFF, OOXML ή ODF. Το IXLSFormulaCacheReader είναι το μοναδικό read-only σημείο εισόδου και για τα τρία: τόσο το TXLSWorkbook.CreateFormulaCacheReader όσο και το TXLSXWorkbook.CreateFormulaCacheReader επιστρέφουν έναν ελαφρύ προσαρμογέα πάνω στη αραιή αναζήτηση κελιών που ήδη χρησιμοποιεί κάθε μηχανή, με πανομοιότυπες συντεταγμένες φύλλου, γραμμής και στήλης 1-based. Οι κλάσεις βιβλίου εργασίας σκόπιμα δεν υλοποιούν οι ίδιες το interface — μια αναφορά interface στο βιβλίο εργασίας θα άλλαζε τη σημασιολογία ιδιοκτησίας του και θα άφηνε καλούντες να γλιστρήσουν πέρα από το μίσθωμα διάρκειας ζωής. Αντ' αυτού, η καταστροφή του βιβλίου εργασίας καθαρίζει τον ακατέργαστο δείκτη μέσα σε αυτό το μίσθωμα, και οποιοσδήποτε αναγνώστης κρατιέται ακόμα από τον κώδικά σας σηκώνει EXLSFormulaCacheReaderInvalidated στην επόμενη ερώτησή του αντί να απο-αναφερθεί σε ελεύθερη μνήμη. Είναι fail-fast έλεγχος διάρκειας ζωής, όχι εγγύηση συγχρονισμού

var
  Reader: IXLSFormulaCacheReader;
  Info: TXLSFormulaCacheInfo;
  Row, Missing, Errors: Integer;
  Total: Double;
begin
  Reader := Book.CreateFormulaCacheReader;
  Total := 0;
  Missing := 0;
  Errors := 0;
  for Row := 2 to LastRow do
    if Reader.TryGetCachedFormulaValue(1, Row, 7, Info) then
    begin
      case Info.Kind of
        xlfcvNumber: Total := Total + Double(Info.Value);
        xlfcvError:  Inc(Errors);
      end;
    end
    else if Info.State = xlfcsMissing then
      Inc(Missing);
  // Καμία αριθμομηχανή δεν έτρεξε, κανένα dirty flag δεν κουνήθηκε, το Book είναι αμετάβλητο
end;

Πού κατοικούν πραγματικά τα cached bytes

Για classic .xls αρχεία η cache είναι το πεδίο FormulaValue της εγγραφής Formula, οκτώ bytes που περιγράφονται από το [MS-XLS] §2.5.133. Όταν η υψηλή λέξη ισούται με $FFFF το payload δεν είναι IEEE 754 double αλλά tagged variant, και η διάταξη είναι εύκολο να χαλάσει διακριτικά: ο τύπος του variant κάθεται στο val[0] και το payload boolean ή BErr στο val[2], με το val[1] απροσδιόριστο. Το HotXLS προηγουμένως διάβαζε το payload από το val[1], που είναι το είδος off-by-one που αναδύεται μόνο στα συγκεκριμένα αρχεία που κάνουν cache boolean ή σφάλμα αντί για αριθμό. Ο αναγνώστης και ο writer των shared formulas συμφωνούν πλέον στις ίδιες μετατοπίσεις, οπότε ένα cached TRUE επιβιώνει ανέπαφο από κύκλο φόρτωσης και αποθήκευσης αντί να αποσυντεθεί σε θόρυβο

Η πιστότητα τύπου στις μορφές πακέτων είναι ξεχωριστό πρόβλημα με τη δική του παγίδα. Στο OOXML η cached τιμή κρέμεται από το στοιχείο c ως <v>, με το attribute t να ονομάζει τον τύπο κατά ECMA-376 Part 1 §18.3.1.4. Το HotXLS διαβάζει t="e" απευθείας σε Variant varError και το αντιστοιχίζει πίσω στο τυπικό κείμενο σφάλματος στην αποθήκευση, οπότε σφάλματα δεν προσποιούνται ποτέ ότι είναι συνηθισμένοι ακέραιοι — αλλά το RTL της Delphi δεν θα σας βοηθήσει εδώ, επειδή το VarAsType(Integer, varError) σηκώνει εξαίρεση μετατροπής. Η δουλεύουσα κατασκευή θέτει TVarData.VType και TVarData.VError απευθείας. Οι ημερομηνίες ακολουθούν την ίδια πειθαρχία προς την άλλη κατεύθυνση: το t="d" και ο τύπος τιμής ημερομηνίας του ODF είναι ρητές δηλώσεις τύπου και γίνονται varDate, ενώ μια αριθμητική cache BIFF δεν κουβαλά καθόλου flag ημερομηνίας και παραμένει Double. Το HotXLS δεν μαντεύει ποτέ ημερομηνία από αριθμητική μορφή κελιού, επειδή η μορφή αριθμού είναι παρουσίαση και η cache είναι δεδομένα. Το ODF προσθέτει μια ακόμα περίπτωση που αξίζει να ξέρετε — το office:value-type="void" εκφράζει cache που υπάρχει αλλά δεν κουβαλά τιμή, και αφού το ODF δεν έχει τύπο τιμής σφάλματος, κείμενο που μοιάζει με σφάλμα διατηρείται ως κείμενο αντί να προαχθεί σε σφάλμα

function DescribeCache(const Info: TXLSFormulaCacheInfo): string;
begin
  case Info.State of
    xlfcsNotFormula:  Result := 'not a formula cell';
    xlfcsMissing:     Result := 'formula stored with no cached value';
    xlfcsInvalidated: Result := 'formula replaced since load';
  else
    case Info.Kind of
      xlfcvError:    Result := 'error code ' + IntToStr(TVarData(Info.Value).VError);
      xlfcvDateTime: Result := DateTimeToStr(VarToDateTime(Info.Value));
      xlfcvBoolean:  Result := BoolToStr(Info.Value, True);
      xlfcvNumber:   Result := FloatToStr(Double(Info.Value));
      xlfcvString:   Result := VarToStr(Info.Value);
    else
      Result := 'present but blank';
    end;
  end;
end;

Μοιράζονται οι shared formulas τις cached τιμές τους;

Όχι, και το να το υποθέσετε είναι ο τρόπος με τον οποίο μια σάρωση καταλήγει να αναφέρει το ίδιο νούμερο για μια ολόκληρη στήλη. Ένας shared formula του OOXML μοιράζεται μόνο την έκφραση του τύπου και τη βελτιστοποίηση αποθήκευσης· κάθε κελί μέλος εξακολουθεί να έχει το δικό του <v>. Το HotXLS επομένως δεν διαδίδει ποτέ την cache του ριζικού μέλους σε follower που ήρθε χωρίς τιμή, και ένας follower που φόρτωσε ως xlfcsMissing συνεχίζει να αναφέρει xlfcsMissing μετά από αποθήκευση και επανανοίγματος. Αν δουλεύετε πώς αποθηκεύεται και απλώνεται η ομάδα εξαρχής, η μηχανική του attribute si του shared formula και της εξάπλωσής του καλύπτεται χωριστά· για ανάγνωση cache, ο κανόνας συμπτύσσεται σε μία γραμμή — ρωτήστε κάθε κελί, μην εμπιστευτείτε τίποτα που δεν ζητήσατε

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