Τέλος στον σιωπηλό επανυπολογισμό τύπων σε saves XLS

Το HotXLS, η native βιβλιοθήκη Excel για Delphi και C++Builder, σώζει ένα κλασικό workbook BIFF8 .xls cache first: το TXLSWorksheet.WriteFormula ρωτάει το TXLSWorkbook.TryGetCachedFormulaValue για την τιμή που το Excel αποθήκευσε δίπλα σε κάθε τύπο και καλεί τον evaluator μόνο όταν εκείνο το cache λείπει ή έχει ακυρωθεί. Ένα workbook που άνοιξες και δεν άγγιξες ποτέ σώζει τους ίδιους αριθμούς πίσω, και φρέσκα αποτελέσματα παίρνουν μια explicit κλήση Recalculate αντί να είναι κρυφή παρενέργεια του SaveAs

Το bug που ανάγκασε αυτό το συμβόλαιο στη δημοσιότητα ήταν εξευτελιστικά μικρό. Ένα αρχείο corpus με όνομα nested-subtotals.xls κρατά ένα grand total στο R2C4 του οποίου η cached τιμή είναι 37. Άνοιξέ το με HotXLS, ρώτα το TryGetCachedFormulaValue για το κελί, πάρε 37. Σώσε το χωρίς να αλλάξεις ούτε ένα κελί, άνοιξε το saved αντίγραφο, ρώτα το ίδιο ερώτημα, πάρε 67. Τίποτα στο API δεν είχε ζητηθεί να υπολογίσει οτιδήποτε, και όμως ένας αριθμός μέσα στο αρχείο είχε μετακινηθεί κατά ακριβώς 30 — και το 30 τυχαίνει να είναι το άθροισμα των δύο group subtotals, 10 και 20, που κάθονται μέσα στο εύρος που καλύπτει το grand total

Γιατί το σώσιμο ενός αρχείου XLS αλλάζει τιμή τύπου;

Δύο ανεξάρτητα ελαττώματα έπρεπε να ευθυγραμμιστούν για να γίνει εκείνο το 37, 67, και η διόρθωση του ενός μοναχού του θα είχε κρύψει το άλλο. Το πρώτο ήταν δομικό: ο κλασικός writer ξαναϋπολογίζει κάθε τύπο σε κάθε save. Το δεύτερο ήταν ένας type check που δεν μπορούσε ποτέ να είναι αληθής για τύπο φορτωμένο από δίσκο, που έκανε τον evaluator να μετράει φωλιασμένα κελιά SUBTOTAL δύο φορές. Το αρχείο corpus ήταν απλώς η πρώτη είσοδος όπου ένα recalculation τη στιγμή του save παρήγαγε διαφορετική απάντηση από το Excel και κάποιος σύγκρινε τις δύο. Το δομικό ελάττωμα δηλώνεται εύκολα: πριν το v2.382.3, το TXLSWorksheet.WriteFormula και το shared-formula αδερφάκι του WriteFormulaWithTExp αποκτούσαν το πεδίο FormulaValue οκτώ bytes κάθε Formula record καλώντας το TXLSWorkbook.GetFormulaValue, που είναι ο evaluator. Το cache που το ParseFormula είχε προσεκτικά αποκωδικοποιήσει από το source αρχείο τη στιγμή του load δεν συμβουλευόταν ποτέ στον δρόμο της εξόδου. Στην πράξη, κάθε save ήταν πλήρης επανυπολογισμός με το recalc API επιπέδου workbook παρακαμφθέν, οπότε τίποτα που θα μπορούσες να θέσεις πάνω στο workbook δεν θα το σταματούσε. Κάθε σημείο όπου ο evaluator του HotXLS διαφωνεί με το Excel, είτε νόμιμα ανυποστήρικτη συνάρτηση είτε σκέτο bug, γινόταν σιωπηλή αλλαγή δεδομένων στο save

Το δεύτερο ελάττωμα ζούσε στο callback φωλιασμένων subtotals που χρησιμοποιεί ο evaluator. Το Excel ορίζει κάθε μορφή SUBTOTAL να αγνοεί κελιά του οποίου ο ίδιος ο τύπος είναι άλλο SUBTOTAL, οπότε ο calculator στο lxCalc.pas οπλίζει το FIgnoreSubtotalCells κατά τη διάρκεια της συσσώρευσης και ρωτάει το workbook, μέσω TXLSWorkbook.GetClassicIsSubtotalCell, αν κάθε κελί του range είναι τέτοιο. Εκείνο το callback έφερνε το text του τύπου ως Variant και το τεστάριζε με VarType(f) = varOleStr. Το text γυρίζει από το GetUnCompiledFormula ως Delphi String, και ένα String ανατεθειμένο σε Variant είναι varUString, ποτέ varOleStr. Το κατηγόρημα ήταν False για κάθε κελί σε κάθε φορτωμένο αρχείο, τα group subtotals κυλούσαν μέσα στο grand total δεύτερη φορά, και σε ένα save που ξαναϋπολόγιζε τα πάντα, το 10 + 20 + 7 γινόταν 67

// HotXLS 2.381 και νωρίτερα: Variant τύπου χτισμένο από String
// είναι varUString, οπότε αυτή η σύγκριση δεν πετύχαινε ποτέ
Result := (VarType(f) = varOleStr) and
  (SameText(Copy(f, 1, 9), 'SUBTOTAL(') or
   SameText(Copy(f, 1, 10), '=SUBTOTAL('));

// HotXLS 2.382.0: το VarIsStr δέχεται varString, varOleStr και varUString,
// και το AGGREGATE εξαιρείται από εσωκλείοντα subtotals όπως κάνει το Excel
if VarIsStr(f) then
  Result := SameText(Copy(f, 1, 9), 'SUBTOTAL(') or
    SameText(Copy(f, 1, 10), '=SUBTOTAL(') or
    SameText(Copy(f, 1, 10), 'AGGREGATE(') or
    SameText(Copy(f, 1, 11), '=AGGREGATE(');

Το v2.382.0 κυκλοφόρησε το fix VarIsStr και, ενώ βρισκόταν στην ίδια συνάρτηση, δίδαξε στο callback ότι και κελιά AGGREGATE εξαιρούνται από εσωκλείοντα subtotals. Αυτό από μόνο του έκανε τον έλεγχο corpus να περάσει, γιατί το ξαναϋπολογισμένο 37 τώρα ταίριαζε στο φορτωμένο 37. Δεν έκανε τη βιβλιοθήκη τίμια: το save εξακολουθούσε να ξαναϋπολογίζει, και το test ήταν πράσινο μόνο επειδή ο evaluator τύγχανε να συμφωνεί με το Excel πάνω σε εκείνο το συγκεκριμένο αρχείο. Οι κανόνες για το ποια κελιά παραλείπουν το SUBTOTAL και το AGGREGATE, συμπεριλαμβανομένων των hidden γραμμών, καλύπτονται στο άρθρο για τα SUBTOTAL και AGGREGATE με hidden γραμμές· αυτό που μετράει εδώ είναι ότι κανένας evaluator δεν πρέπει να έχει ψήφο σε αρχείο που δεν του ζήτησες να υπολογίσει

Τι εγγυάται το Excel για τις cached τιμές στο save;

Το Excel μεταχειρίζεται το save ως στιγμιότυπο, όχι ως γεγονός υπολογισμού. Η τιμή που γράφεται στο πεδίο FormulaValue ενός Formula record ([MS-XLS] §2.4.127, layout στο §2.5.133) είναι ό,τι εμφανίζει αυτή τη στιγμή το κελί, που σε manual calculation mode μπορεί να είναι years stale, και το Excel την γράφει πιστά ούτως ή άλλως. Ο επανυπολογισμός είναι ξεχωριστή λειτουργία με δικό της trigger. Το HotXLS ακολουθεί πλέον τον ίδιο κανόνα για κλασικά saves: το WriteFormula και το WriteFormulaWithTExp καλούν πρώτα το TryGetCachedFormulaValue, παίρνουν το CacheInfo.Value όταν η κατάσταση είναι xlfcsLoaded ή xlfcsCalculated, και πέφτουν στο GetFormulaValue μόνο για xlfcsMissing και xlfcsInvalidated. Το μισό του συμβολαίου στην πλευρά ανάγνωσης, συμπεριλαμβανομένου του τι σημαίνει κάθε κατάσταση και γιατί ένα cached κενό ή False εξακολουθεί να μετράει ως τιμή, περιγράφεται στο Read Excel Cached Formula Values in Delphi Without Recalc

Το μονοπάτι fallback κρατιέται σκόπιμα, δεν αφαιρείται. Ένας τύπος που ανάθεσες σε αυτή τη συνεδρία μέσω Cells[Row, Col].Formula φτάνει χωρίς cache, και ένας τύπος που αντικατέστησες πάνω σε φορτωμένο κελί μαρκάρεται xlfcsInvalidated από το _SetCompiledFormula· και οι δύο αξιολογούνται τη στιγμή του save ακριβώς όπως πριν, οπότε ένα παραγόμενο workbook εξακολουθεί να ανοίγει στο Excel με αριθμούς μέσα του. Όταν καν ο evaluator δεν μπορεί να παράγει τιμή, ο writer εκπέμπει payload μηδέν και θέτει fAlwaysCalc (grbit bit 0 του §2.4.127) ώστε το Excel να ξαναϋπολογίζει το κελί στο open αντί να εμπιστευτεί το placeholder

procedure RoundTripWithoutRecalc(const Source, Target: string);
var
  Book: TXLSWorkbook;
  Before, After: TXLSFormulaCacheInfo;
begin
  Book := TXLSWorkbook.Create;
  try
    Book.Open(Source);
    // sheet, γραμμή και στήλη με βάση το 1: R2C4 στο πρώτο sheet
    if not Book.TryGetCachedFormulaValue(1, 2, 4, Before) then
      raise Exception.Create('R2C4 carries no usable cache');
    Book.SaveAs(Target);        // χωρίς τον evaluator για cached κελιά
  finally
    Book.Free;
  end;

  Book := TXLSWorkbook.Create;
  try
    Book.Open(Target);
    Book.TryGetCachedFormulaValue(1, 2, 4, After);
    // Before.Value = After.Value = 37 για το nested-subtotals.xls
    // Μια save που ξαναϋπολόγιζε θα είχε γράψει 67 εδώ
  finally
    Book.Free;
  end;
end;

Πού κρατά η root ενός BIFF shared formula την cached τιμή της;

Στο δικό της Formula record, όπως κάθε άλλο κελί τύπου, και ακριβώς αυτό έκανε το root κελί μιας shared ομάδας το ένα μέρος όπου το cache-first saving έχανε ακόμα. Ένα shared formula στο BIFF8 αποθηκεύεται ως ShrFmla record ([MS-XLS] §2.4.260) που έπεται του Formula record του πάνω-αριστερού κελιού, και κάθε member κελί, root συμπεριλαμβανομένου, κουβαλάει rgce αποτελούμενο από ένα μεμονωμένο token PtgExp (§2.5.198): το πρώτο byte της parsed έκφρασης είναι $01, ακολουθούμενο από τη γραμμή και στήλη του root κελιού. Τα follower κελιά είναι αυτοτελή — το HotXLS διαβάζει το FormulaValue του καθενός και λύνει την έκφραση ψάχνοντας τη compiled formula της ρίζας. Το root κελί είναι διαφορετικό, γιατί όταν το Formula record του γίνεται parse η έκφραση δεν υπάρχει ακόμα· φτάνει ένα record αργότερα

Εκείνο το ένα-record κενό είναι πού πήγαινε το cache. Το TXLSReader.ParseFormula αποκωδικοποιεί την cached τιμή και, βλέποντας PtgExp του οποίου οι συντεταγμένες ισούνται με τις του ίδιου του κελιού, θυμάται το κελί στα FSharedFormulaRow και FSharedFormulaCol και δημοσιεύει το cache πάνω στο κελί. Όταν φτάνει το ShrFmla record ($04BC), το ParseSharedFormula μεταγλωττίζει την έκφραση και την εγκαθιστά με _SetCompiledFormula, και το _SetCompiledFormula κάνει ό,τι οφείλει για οποιαδήποτε αλλαγή τύπου: σβήνει το FCachedFormulaValue και επαναφέρει την κατάσταση σε xlfcsMissing. Το φορτωμένο 37 της ρίζας πετιόταν λοιπόν πριν κανείς προλάβει να το διαβάσει, το TryGetCachedFormulaValue ανέφερε τη ρίζα ως uncached, και ο writer cache-first έπεφτε πιστά στον evaluator για ακριβώς το κελί που όλοι κοιτούσαν. Το Array record (§2.4.4) μοιράζεται την ίδια σειρά και είχε την ίδια τρύπα

Το fix στο v2.382.3 προσθέτει ένα τρίτο πεδίο, το FSharedFormulaCachedValue, δίπλα στις εκκρεμείς συντεταγμένες ρίζας. Το ParseFormula κρύβει εκεί το αποκωδικοποιημένο cache όταν αναγνωρίζει ρίζα, και τόσο το ParseSharedFormula όσο και το ParseArrayFormula το ξαναπαίζουν μέσω _SetCellCachedFormulaValue αμέσως μετά την εγκατάσταση της compiled έκφρασης, και μετά επαναφέρουν το stash σε Unassigned. Η παραλλαγή String του cache δεν επηρεάζεται από όλα αυτά γιατί το payload της φτάνει σε ξεχωριστό String record και δρομολογείται με συντεταγμένες κελιού, όχι με σειρά record. Αν δουλεύεις με την OOXML πλευρά της ίδιας έννοιας, το άρθρο για το XLSX shared formula si expansion εξηγεί γιατί η μορφή package δεν έχει το αντίστοιχο πρόβλημα σειράς αλλά τις δικές της παγίδες expansion

Γιατί τα followers ενός shared formula χρειάζονται σχετική μετατόπιση;

Γιατί η έκφραση αποθηκευμένη στο ShrFmla είναι γραμμένη σχετικά με το root κελί, και ένα follower που την ξαναχρησιμοποιεί verbatim αξιολογεί τις αναφορές της ρίζας αντί για τις δικές του. Ο παλιός reader εγκαθιστούσε Value.GetCopy() πάνω σε κάθε follower, deep copy χωρίς μετατόπιση, οπότε μια ομάδα με ρίζα στο B1 με =A1*3 έδινε σε κάθε follower κι αυτό =A1*3. Το cache-first saving στην πραγματικότητα σκέπαζε αυτό για φορτωμένα αρχεία, αφού τα followers είχαν το δικό τους FormulaValue και δεν χρειαζόταν ποτέ την έκφραση για να σωθούν σωστά· αναδύθηκε τη στιγμή που οτιδήποτε ξαναϋπολόγιζε. Ο reader εγκαθιστά πλέον TXLSCompiledFormula.GetCopy(row - srow, col - scol), που διασχίζει το syntax tree και μετατοπίζει κάθε σχετική αναφορά κατά την απόσταση του follower από τη ρίζα, οπότε ο follower στο B2 έχει γνήσιο =A2*3

Το regression test που καρφώνει και τις δύο συμπεριφορές αξίζει να το διαβάσεις γιατί αρνείται να αφήσει μια συμφωνία να περάσει. Χτίζει ένα workbook με =A1*3 και =A2*3 πάνω στις εισόδους 2 και 4, μετά εγχέει τα σκόπιμα λάθος caches 999 και 888 μέσω _SetCellCachedFormulaValue, μία φορά με UseSharedFormulas ανοιχτό και μία κλειστό. Μετά από save και reload, και τα δύο κελιά πρέπει ακόμα να αναφέρουν 999 και 888 — απόδειξη ότι το save δεν άγγιξε ούτε τη ρίζα ούτε το cache του follower. Μόνο μετά από explicit Recalculate πρέπει να γίνουν 6 και 12, απόδειξη ότι η μετατοπισμένη έκφραση του follower είναι σωστή. Ένα test που έσπερνε τις αληθινές τιμές θα είχε περάσει και κάτω από τον παλιό writer, που είναι όλο το νόημα του να σπέρνεις λάθος

var
  Book: TXLSWorkbook;
  Info: TXLSFormulaCacheInfo;
begin
  Book := TXLSWorkbook.Create;
  try
    Book.Open('quarterly-model.xls');
    Book.Sheets[1].Cells[1, 1].Value := 5;   // άλλαξε μια είσοδο

    // Οι loaded caches εξαρτημένων τύπων ΔΕΝ ακυρώνονται από
    // literal επεξεργασία, οπότε σκέτο SaveAs θα κρατούσε τους παλιούς αριθμούς.
    // Ζήτησε επανυπολογισμό όταν θέλεις πράγματι φρέσκα αποτελέσματα:
    Book.Recalculate;

    if Book.TryGetCachedFormulaValue(1, 1, 2, Info) then
      Writeln('B1 now ', VarToStr(Info.Value),
        ', state ordinal ', Ord(Info.State));   // xlfcsCalculated
    Book.SaveAs('quarterly-model-updated.xls');
  finally
    Book.Free;
  end;
end;

Τι δεν κάνει για σένα το συμβόλαιο cache-first

Το cache-first saving διατηρεί ό,τι φορτώθηκε· δεν παρακολουθεί αν ό,τι φορτώθηκε εξακολουθεί να ισχύει. Η αλλαγή ενός literal από τον οποίο εξαρτάται ένας τύπος μαρκάρει το dependency graph dirty για τον evaluator, αλλά αφήνει το cache xlfcsLoaded του εξαρτημένου κελιού στη θέση του, και ο κλασικός writer θα έγραφε ευχάριστα εκείνη την παλιοτιμή εκτός αν καλέσεις Recalculate ή διαβάσεις πρώτα το Value του κελιού, που το υπολογίζει και μετακινεί την κατάσταση σε xlfcsCalculated. Αυτός είναι ο ίδιος συμβιβασμός που κάνει το Excel σε manual calculation mode, και είναι ο σωστός για μια pipeline που ανοίγει αρχεία τρίτων, επεξεργάζεται μερικές ετικέτες και σώζει — αλλά σημαίνει ότι ένα workbook που επεξεργάζεται εισόδους πρέπει να κατέχει ρητά το δικό του βήμα επανυπολογισμού. Η πολιτική RecalcBeforeSave του XLSX writer δεν αλλάζει από αυτή τη δουλειά και έχει το δικό της manual mode που διατηρεί caches στο ίδιο πνεύμα. Δύο μικρότερα όρια ακολουθούν από αυτό: το μονοπάτι cache-first βοηθά μόνο κελιά του οποίου η κατάσταση είναι xlfcsLoaded ή xlfcsCalculated· ένας generator που γράφει τύπους και δεν τους αξιολογεί ποτέ εξακολουθεί να πληρώνει μία αξιολόγηση ανά κελί τη στιγμή του save, ακριβώς όπως πριν. Και το fix των φωλιασμένων subtotals διορθώνει ποια κελιά παραλείπει ο evaluator, όχι κάθε συνάρτηση που υλοποιεί ο evaluator — ένα αρχείο του οποίου οι τύποι δεν μπορούν να υπολογιστούν από το HotXLS πανομοιότυπα με το Excel είναι πλέον ασφαλές να κάνει round-trip αναλλοίωτο, αλλά ένα σκόπιμο Recalculate πάνω σε εκείνο το αρχείο θα παράγει ακόμα την απάντηση της βιβλιοθήκης και όχι του Excel, και πρέπει να συγκρίνεις τις δύο πριν εμπιστευτείς ένα ξαναϋπολογισμένο save

Τα cache-first κλασικά saves, τα αποκατεστημένα caches ριζών shared και array formulas, η μετατόπιση σχετικών αναφορών για shared followers και οι διορθωμένοι κανόνες φωλιάσματος SUBTOTAL και AGGREGATE κυκλοφορούν όλα μέσα στο standard HotXLS Delphi Spreadsheet Component για Delphi και C++Builder, χωρίς εξάρτηση από Excel ή οποιονδήποτε OLE automation server· η σελίδα προϊόντος κουβαλάει την πλήρη αναφορά API για τα σημεία εισόδου workbook, cache reader και επανυπολογισμού που χρησιμοποιούνται εδώ