Εγκυρότητα schema πεδίων pivot XLSX σε Delphi με HotXLS

Το HotXLS γράφει ορισμούς pivot tables XLSX των οποίων τα στοιχεία pivotField και cacheField επικυρώνονται απέναντι στο schema ECMA-376 Part 1 §18.10: τα attributes άξονα χρησιμοποιούν τα tokens ST_Axis axisRow, axisCol και axisPage, τα fields της περιοχής τιμών κουβαλούν dataField="1", οι λίστες items δεν είναι ποτέ κενές, και τα cache fields αποθηκεύουν αριθμητικό numFmtId. Από το v2.384.33 ο reader σέβεται και τα schema defaults που παλιά παρανοούσε

Τα bugs πίσω από αυτόν τον καθαρισμό μοιράζονται ένα μη κολακευτικό χαρακτηριστικό: κανένα δεν απέτυχε ποτέ σε τεστ. Το HotXLS έγραφε ένα pivot, το HotXLS το ξαναδιάβαζε, κάθε field κατέληγε στον σωστό άξονα, και η σουίτα round trips έμενε πράσινη για χρόνια. Το πρόβλημα ήταν ότι writer και reader είχαν συμφωνήσει ήσυχα σε μια ιδιωτική διάλεκτο. Ένα pivot χτισμένο από Delphi έμοιαζε μια χαρά στο component που το έφτιαξε, ενώ ένας έλεγχος απέναντι στα CT_PivotField και CT_CacheField ξεσκέπαζε άκυρα tokens απαρίθμησης, ένα κενό στοιχείο που το schema απαγορεύει και σημαίες που το Excel περιμένει και δεν έπαιρνε ποτέ. Αν παράγετε pivots σε server και τα στέλνετε σε ανθρώπους που τα ανοίγουν σε Excel ή τα ταΐζουν στους δικούς τους parsers, το μόνο συμβόλαιο που μετράει είναι το schema, όχι ό,τι τυχαίνει να συγχωρεί ο δικός σας reader

Γιατί τα round trips του HotXLS δεν έπιασαν ποτέ τα λάθος tokens άξονα;

Τα round trips του HotXLS δεν έπιασαν ποτέ τα λάθος tokens άξονα επειδή ο reader δεχόταν και τις δύο ορθογραφίες. Ο παλιός XlsxPivotAxisAttr εξέπεμπε axis="rowAxis", colAxis και pageAxis, που διαβάζονται φυσικά στα αγγλικά αλλά δεν υπάρχουν στο schema· το ST_Axis ορίζει ακριβώς τέσσερις τιμές, axisRow, axisCol, axisPage και axisValues. Στο μεταξύ το PivotAxisFromToken στο lxPivotXml.pas ταιρίαζε τόσο το token του schema όσο και το εφευρεμένο, οπότε κάθε self-test πέρναγε. Ο writer πλέον εκπέμπει μόνο τα tokens του schema, και ο reader εξακολουθεί να δέχεται τις παλιές ορθογραφίες ώστε αρχεία αποθηκευμένα από παλαιότερες εκδόσεις HotXLS να φορτώνουν ακόμα με ανέπαφη διάταξη

<!-- πριν το v2.384.33: άκυρη τιμή ST_Axis, κενό CT_Items -->
<pivotField axis="rowAxis" defaultSubtotal="1"><items count="0"></items></pivotField>

<!-- από το v2.384.33 -->
<pivotField axis="axisRow" defaultSubtotal="1">
  <items count="4"><item x="0"/><item x="1"/><item x="2" h="1"/><item t="default"/></items>
</pivotField>

Τι απαιτεί το CT_PivotField που ο παλιός writer προσπερνούσε;

Το CT_PivotField απαιτεί τρία πράγματα που ο παλιός BuildPivotTableXml άφηνε έξω ή έκανε λάθος. Πρώτον, ένα field που συγκεντρώνεται στην περιοχή τιμών πρέπει να το λέει στον δικό του ορισμό με dataField="1"· ο writer πλέον θέτει εκείνη τη σημαία σε κάθε field που αναφέρεται από μια εγγραφή στα DataFields, όχι μόνο στη λίστα <dataFields>. Δεύτερον, το CT_Items χρειάζεται τουλάχιστον ένα item, οπότε ένα field χωρίς items δεν παίρνει πλέον κενό <items count="0"> και ολόκληρο το στοιχείο απλώς παραλείπεται. Τρίτον, κάθε item κρατά την κατάστασή του: h="1" για κρυφό item (TXLSPivotItem.IsHidden) και sd="0" για συμπτυγμένες λεπτομέρειες (IsDetailHidden), και τα δύο τα οποία ο παλιός writer πετούσε σε κάθε save

Το διακριτικό μέρος είναι τα τελικά items υποσυνόλων. Όταν ένα field έχει items, το Excel απαριθμεί ένα επιπλέον item ανά συνάρτηση υποσυνόλου μετά τα items δεδομένων, τυποποιημένα με ST_ItemType: <item t="default"/> για το αυτόματο υπόσύνολο, μετά sum, countA, avg, max, min, product, count, stdDev, stdDevP, var και varP για τα ρητά. Το HotXLS παράγει εκείνες τις εγγραφές από το TXLSPivotField.Subtotals τη στιγμή του save και τις μετρά μέσα στο items count. Τα fields που δημιουργούνται από το AddPivotTable ξεκινούν με κενό σύνολο Subtotals, που γράφει defaultSubtotal="0" και κανένα τελικό item, οπότε ζητήστε υποσύνολα ρητά όταν η αναφορά τα χρειάζεται. Προσέξτε την παγίδα ονομασίας: το xlpsCount αντιστοιχεί στο countA (όλες οι εγγραφές) και το xlpsCountNums στο count (μόνο αριθμοί)

uses
  lxHandleX, lxPivot;

var
  Book  : TXLSXWorkbook;
  Sheet : TXLSXWorksheet;
  Pivot : TXLSPivotTable;
  Region: TXLSPivotField;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('orders.xlsx');
    Sheet := Book.Sheets[1];                  // 1-based, όπως στη μηχανή XLS
    Pivot := Sheet.AddPivotTable('Data!$A$1:$D$800', 3, 6, 'RegionTotals');
    if Pivot = nil then
      raise Exception.Create('Bad source range or anchor');

    Region := Pivot.AddRowField('Region');    // nil αν δεν υπάρχει τέτοιο field
    if Region <> nil then
      Region.Subtotals := [xlpsDefault, xlpsAverage];  // -> t="default", t="avg"
    Pivot.AddColumnField('Quarter');
    Pivot.AddDataFieldByName('Revenue', xlpaSum);      // θέτει στο Revenue dataField="1"

    Book.SaveAs('orders-pivot.xlsx');
  finally
    Book.Free;
  end;
end;

Πώς διαβάζει τώρα το HotXLS τα items υποσυνόλων και τα schema defaults;

Ο reader του HotXLS προσπερνά πλέον κάθε item του οποίου το attribute t είναι παρόν και διάφορο του data, επειδή οι εγγραφές υποσυνόλων, συνόλων συνόλων και κενών δεν κουβαλούν δείκτη cache. Πριν το v2.384.34 εκείνες οι εγγραφές φορτώνονταν ως συνηθισμένα items με CacheItemIndex στο -1, οπότε ένα pivot φτιαγμένο από Excel επέστρεφε με φανταστικά μέλη που δεν έδειχναν πουθενά, και κάθε κώδικας που περπατούσε τα Items έπρεπε να τα φιλτράρει στο χέρι. Αφού ο writer ξαναχτίζει τις τελικές εγγραφές από το Subtotals, η δουλειά του reader είναι να τις μεταφράσει σε εκείνο το σύνολο, όχι να τις κρατήσει ως δεδομένα

Το δεύτερο fix reader αφορά attributes που λείπουν. Στο schema, το defaultSubtotal στο CT_PivotField και το containsString στο CT_SharedItems έχουν και τα δύο default το true, και το Excel τα παραλείπει όταν κρατούν εκείνο το default. Το HotXLS διάβαζε ένα λείπαν attribute ως false, που σήμαινε ότι κάθε pivot αποθηκευμένο από Excel έχανε σιωπηλά το default υπόσυνολό του στο load, και ένα σκέτο text cache field ταξινομούταν ως mixed αντί για string. Είναι ο κατοπτρισμός του bug του άξονα: ένας writer που γράφει πάντα κάθε attribute με τη λέξη δεν εξασκεί ποτέ το μονοπάτι του default, οπότε μόνο αρχεία από άλλον παραγωγό το εκθέτουν

Γιατί το numFmtId="General" ήταν άκυρο σε cache fields;

Η τιμή numFmtId="General" ήταν άκυρη επειδή το ST_NumFmtId είναι unsigned integer, όχι όνομα μορφής. Ο παλιός cache writer έβαζε καρφωμένο εκείνο το string σε κάθε cacheField, δανειζόμενος το όνομα που βλέπουν οι χρήστες στο dialog Format Cells. Το HotXLS γράφει πλέον το NumberFormat του cache field ως αριθμό, που είναι το 0 (η ενσωματωμένη μορφή General) εκτός αν κάτι το έχει ορίσει. Ένας αυστηρός parser που τυποποιεί τα attributes από το schema απορρίπτει την παλιά τιμή ολότελα, και αυτή είναι ακριβώς η κατηγορία αποτυχίας που καταλήγει σε dialog επιδιόρθωσης· το άρθρο για τους κανόνες OPC και markup πίσω από το prompt επιδιόρθωσης του Excel καλύπτει πώς ενεργοποιούνται εκείνα τα dialogs

Γιατί κόβονταν pivot tables κάτω από τη γραμμή 65535;

Τα pivot tables XLSX τοποθετημένα στη γραμμή 65536 ή χαμηλότερα κόβονταν επειδή το κοινό μοντέλο pivot αποθήκευε τα FirstRow, LastRow, FirstHeaderRow, FirstDataRow και τα αντίστοιχα στηλών ως Word, και ο κώδικας μετατόπισης γραμμών τα στενούσε με Min(.., High(Word)). Είναι υπόλειμμα του record SxView του BIFF8, όπου τα 16 bits φτάνουν, αλλά ένα φύλλο XLSX τρέχει ως 1.048.576 γραμμές. Από το v2.384.37 εκείνες οι ιδιότητες στο TXLSPivotTable είναι Integer, τα στενώματα χάθηκαν, και μόνο ο writer BIFF8 στενεύει τις τιμές. Το TXLSXWorksheet.AddPivotTable και το AddPivotTableCopy επιστρέφουν πλέον nil για άγκυρα εκτός 1..1048576 επί 1..16384, ή για αντίγραφο του οποίου η έκταση θα ξεφύγει από το πλέγμα

var
  Pivot: TXLSPivotTable;
  Check: TXLSXWorkbook;
begin
  // Η γραμμή 70001 τυλιγόταν στο εύρος 16-bit· τώρα επιβιώνει από save και load
  Pivot := Sheet.AddPivotTable('Data!$A$1:$D$800', 70001, 1, 'LateTotals');
  if Pivot = nil then
    Exit;  // άγκυρα εκτός φύλλου ή άλυτο source range
  Pivot.AddRowField('Region');
  Pivot.AddDataFieldByName('Revenue', xlpaSum);
  Book.SaveAs('late.xlsx');

  Check := TXLSXWorkbook.Create;
  try
    Check.Open('late.xlsx');
    Pivot := Check.Sheets[1].PivotTables.FindByName('LateTotals');
    Assert((Pivot <> nil) and (Pivot.FirstRow = 70001));
  finally
    Check.Free;
  end;
end;

Η classic μηχανή XLS πήρε το αντίστοιχο fix στο v2.384.38. Το μοντέλο της αποθήκευε παλιά τις raw μηδενικές τιμές SxView και DConRef και περνούσε τις άγκυρες του AddPivotTable κατευθείαν, ενώ η τεκμηρίωση, τα demos και η μηχανή XLSX χρησιμοποιούσαν όλα κελιά 1-based όπως τα Cells[Row, Col]. Και οι δύο μηχανές κρατούν πλέον θέσεις 1-based στο μοντέλο, ο reader BIFF8 προσθέτει 1 και ο writer αφαιρεί 1 στο όριο του record, οπότε κώδικας που αγκυρώνει στο (0, 0) πρέπει να πάει στο (1, 1), επειδή το classic AddPivotTable επιστρέφει πλέον nil για άγκυρα εκτός 1..65536 επί 1..256· η νέα κλήση γράφει τα ίδια bytes με την παλιά. Η διάταξη του record καθαυτή είναι αμετάβλητη και περιγράφεται στο records SX του BIFF8 πίσω από τα classic pivot tables .xls

Επικυρώστε απέναντι στο schema, όχι στον δικό σας reader

Το μάθημα γενικεύει πέρα από τα pivots: ένας επιεικής reader κρύβει παραβάσεις του writer, οπότε ένα round trip μέσα από τον δικό σας κώδικα αποδεικνύει συνέπεια, όχι ορθότητα. Κάθε bug εδώ επιβίωσε επειδή η ανεκτική πλευρά και η ελαττωματική πλευρά ζούσαν στην ίδια βιβλιοθήκη. Οι έλεγχοι που πραγματικά πιάνουν αυτή την κατηγορία ελαττώματος είναι μια επικύρωση schema των παραγόμενων μερών, αρχεία παραγόμενα από Excel που περνούν από τον reader σας με attributes παραλειμμένα στα defaults τους, και fixtures που καρφώνουν το ακριβές token αντί για το parsed αποτέλεσμα. Τα pivots που χτίζετε μέσω του API, συμπεριλαμβανομένων των calculated fields, calculated items και διατάξεων percent-of-total που δείχνει το χτίσιμο και φρεσκάρισμα XLSX pivot tables με calculated fields, παίρνουν το διορθωμένο XML χωρίς αλλαγή κώδικα, ενώ pivots φορτωμένα από αρχεία Excel εξακολουθούν να ξαναπαίζουν τα πρωτότυπα μέρη τους μέχρι να τα τροποποιήσετε

Όλα αυτά τα fixes έρχονται στο τρέχον HotXLS Delphi spreadsheet component, που διαβάζει και γράφει XLS, XLSX και pivot tables από Delphi και C++Builder χωρίς Excel ή COM automation στη μηχανή