Record Selection BIFF8 e scroll dei riquadri con HotXLS

HotXLS memorizza selezioni dei fogli di lavoro e posizioni di scroll per riquadro tramite una sola API pensata per i riquadri sia su TXLSWorksheet sia su TXLSXWorksheet: SelectAreas, GetSelectedAreas, ScrollWindow e TryGetWindowScroll. Per i file .xls classici, HotXLS scrive record Selection BIFF8 (0x001D) da al massimo 1369 aree ciascuno, converte i nomi logici dei riquadri nei byte di riquadro definiti dal formato, e tiene ogni asse di scroll sul record Window2 o Pane dove Excel se lo aspetta

Il problema di solito emerge in uno strumento di riconciliazione o di audit. Lo strumento apre un export di registro contabile, trova ogni cella in disaccordo col sistema sorgente, e salva il workbook con quelle celle già selezionate sotto una riga di intestazione bloccata, così il revisore atterra direttamente sulle differenze invece di cercarle scorrendo. Con quaranta differenze funziona bene. Il file di fine mese ne ha 3.000, e un singolo record Selection con 3.000 aree non può esistere: il suo corpo richiederebbe 18.009 byte, più del doppio di quello che un record BIFF8 può trasportare. La posizione di scroll ha una trappola simile. Su un foglio con riquadri bloccati, "dove stava guardando l'utente" sono quattro riquadri che condividono due posizioni di riga e due di colonna, non una coordinata unica

Perché una selezione grande richiede più di un record Selection?

Una selezione grande richiede più record perché il corpo di un record BIFF8 è limitato a 8224 byte e ogni area selezionata costa sei byte fissi. [MS-XLS] §2.4.248 descrive il record Selection come una parte fissa di 9 byte (il byte del riquadro, rwAct e colAct per la cella attiva, irefAct per l'area attiva e cref per il conteggio delle aree) seguita da cref strutture RefU, ciascuna con due righe a 16 bit e due colonne a 8 bit. Il conteggio massimo che ci sta è (8224 − 9) / 6 arrotondato per difetto, cioè 1369, e produce un corpo di 8223 byte, un byte sotto il limite. TXLSWorksheet.StoreSelectionGroup usa quella costante come MaxAreasPerRecord e scrive un gruppo più grande come record Selection consecutivi per lo stesso riquadro, 1369 aree alla volta

Il dettaglio che morde è irefAct. Ogni blocco ripete la stessa riga attiva, colonna attiva e indice di area attiva, e irefAct indicizza la sequenza aggregata di tutti i blocchi, non le aree dentro il record che lo trasporta. Una selezione di un'area oltre il limite rende la cosa concreta: 1370 aree con l'ultima attiva diventano due record, il primo con cref 1369 e il secondo con cref 1, e entrambi portano irefAct 1369. Quel valore è più grande del conteggio di aree del secondo record stesso. Un reader che verifica irefAct contro cref in ogni record rifiuta un file valido, e un reader che sostituisce il proprio stato a ogni record butta via le prime 1369 aree. Il reader di HotXLS aggiunge i record consecutivi dello stesso riquadro in un unico gruppo, pretende che ogni blocco concordi su cella attiva e indice, e esegue il controllo di range solo al record EOF del foglio di lavoro, quando la sequenza completa è nota. L'overload di SelectAreas che mette il riquadro al primo posto quindi non ha alcun tetto di 1369 aree. Valida ogni riferimento A1 e l'indice attivo prima di prendere il lock di scrittura del foglio, e restituisce False con la selezione precedente invariata se qualcosa è malformato

var
  Book: TXLSWorkbook;
  Sheet: TXLSWorksheet;
  Diffs: TXLSSelectedAreas;
  I: Integer;
begin
  Book := TXLSWorkbook.Create;
  try
    Sheet := Book.Sheets.Add;
    Sheet.FreezePanes(1, 1);           // la riga di intestazione e la colonna A restano ferme

    SetLength(Diffs, 3000);
    for I := 0 to High(Diffs) do
      Diffs[I] := Format('C%d', [I + 2]);

    // Il congelamento azzera la selezione memorizzata, quindi selezionate dopo il congelamento.
    // Le 3000 aree vengono salvate come tre record Selection: 1369 + 1369 + 262
    if not Sheet.SelectAreas(xlspBottomRight, Diffs, 0) then
      raise Exception.Create('Selection rejected');

    Book.SaveAs('reconciliation.xls');
  finally
    Book.Free;
  end;
end;

Quale byte di riquadro usa un record Selection?

Un record Selection identifica il suo riquadro col codice numerico definito dal formato: 0 per basso-destra, 1 per alto-destra, 2 per basso-sinistra e 3 per alto-sinistra. L'enumerazione pubblica TXLSPanePosition è dichiarata in ordine di lettura, xlspTopLeft, xlspTopRight, xlspBottomLeft, xlspBottomRight, così Ord(xlspTopLeft) è 0, che nel file è il riquadro basso-destra. Fare il cast dell'enumerazione direttamente nel byte del riquadro scriverebbe ogni selezione alto-sinistra sul riquadro basso-destra senza alcun errore. Ogni punto d'ingresso di HotXLS consapevole dei riquadri converte l'enumerazione tramite un case esplicito, così chi chiama non ha mai a che fare coi codici numerici. Viene verificata anche l'esistenza del riquadro: quello alto-destra esiste solo con uno split verticale, quello basso-sinistra solo con uno orizzontale, e quello basso-destra solo con entrambi. Per un riquadro che la geometria corrente di split o congelamento non ha, SelectAreas restituisce False, e GetSelectedAreas restituisce un array vuoto con ActiveAreaIndex a -1, senza creare né un riquadro, né un oggetto selezione, né una cella nel workbook

Dove vive la posizione di scroll di ciascun riquadro?

La posizione di scroll di ciascun riquadro è spalmata su due record, perché quattro riquadri condividono solo due posizioni di riga e due di colonna. In un workbook classico, la prima riga visibile dei riquadri superiori e la prima colonna visibile dei riquadri sinistri sono Window2.rwTop e Window2.colLeft, mentre la riga dei riquadri inferiori e la colonna dei riquadri destri sono Pane.rwTop e Pane.colLeft. ScrollWindow(xlspTopRight, R, C) quindi scrive Window2.rwTop e Pane.colLeft, e impostare la colonna del riquadro alto-destra sposta anche quello basso-destra, esattamente come in Excel i due condividono una sola barra di scorrimento orizzontale. I metodi pubblici usano numeri di riga e colonna a base 1. Un riquadro assente restituisce False e azzera entrambe le uscite della query, e una coordinata fuori range viene rifiutata prima che uno dei due assi cambi. Nulla di tutto ciò dipende da come un viewer disegna la griglia. Un controllo di rendering tiene i propri TopRow e LeftCol, come descrive l'articolo sul rendering dei workbook in una griglia VCL personalizzata, e quelli sono stato a runtime, non ciò che viene salvato

XLSX sparge gli stessi dati su due elementi: sheetView/@topLeftCell (ECMA-376 Part 1, §18.3.1.87) per la finestra nel suo complesso e il figlio pane/@topLeftCell (§18.3.1.66) per il lato basso-destra di uno split. Entrambi gli attributi possono essere presenti insieme. HotXLS legge prima l'attributo esterno nei campi a livello di finestra, lascia che il figlio pane sovrascriva solo i campi a livello di riquadro, e riscrive entrambi separatamente. Collassare i due livelli in uno è esattamente il modo in cui una posizione di scroll superiore o sinistra sparisce in silenzio al caricamento. Le copie dei fogli di lavoro portano entrambi i livelli in entrambi gli engine. I punti d'ingresso più vecchi conservano il loro comportamento originale: le proprietà classiche ScrollRow e ScrollColumn, e la SetPaneScroll e GetPaneScroll XLSX a base zero. La geometria stessa di congelamento e split si configura con le impostazioni a livello di foglio trattate in protezione del foglio, impostazione pagina e stampa

var
  Row, Col: Integer;
begin
  Sheet.FreezePanes(1, 1);

  // Basso-destra: asse riga inferiore (Pane.rwTop) e asse colonna destra (Pane.colLeft)
  Sheet.ScrollWindow(xlspBottomRight, 500, 3);

  // Alto-destra condivide l'asse colonna destra, quindi questo sposta anche basso-destra alla colonna 6
  Sheet.ScrollWindow(xlspTopRight, 1, 6);

  if Sheet.TryGetWindowScroll(xlspBottomRight, Row, Col) then
    Memo1.Lines.Add(Format('Bottom-right starts at row %d, column %d', [Row, Col]));
    // Basso-destra parte dalla riga 500, colonna 6
end;

Che cosa succede quando un record Selection è corrotto?

Quando un record Selection è corrotto, HotXLS lo conserva come byte opachi, riporta il codice diagnostico 1304 (xlsDiagnosticSelectionRecordInvalid), e al salvataggio riscrive il corpo originale byte per byte. Prima che un record entri nel gruppo del suo riquadro, il reader lo verifica in ordine. Il byte del riquadro deve essere 3 o meno. I record di un riquadro devono essere contigui nello stream. I 9 byte fissi devono essere presenti. cref deve stare tra 1 e 1369, e il corpo deve essere lungo esattamente 9 + cref × 6 byte. Ogni blocco di un gruppo deve concordare su cella attiva e irefAct, irefAct non deve avere il bit di segno impostato, la colonna attiva deve stare sulla griglia, e nessuna area può avere limiti invertiti. I problemi di un singolo record fisico vengono riportati una volta per record. Le contraddizioni che emergono solo dopo l'aggregazione, come irefAct che punta oltre il conteggio totale di aree o una cella attiva fuori dall'area indicizzata, vengono riportate una volta per gruppo all'EOF. Un gruppo invalido resta invisibile all'API tipizzata: GetSelectedAreas restituisce un array vuoto con indice -1 per quel riquadro, mentre ogni altro riquadro continua a funzionare

var
  I: Integer;
  D: TXLSDiagnostic;
begin
  if Book.Open('supplier-upload.xls') <> 1 then
    Exit;
  for I := 0 to Book.Diagnostics.Count - 1 do
  begin
    D := Book.Diagnostics[I];
    if D.Code = xlsDiagnosticSelectionRecordInvalid then
      Log.Add(Format('%s: record $%.4x kept opaque (%s)',
        [D.SheetName, D.RecordId, D.Message]));
  end;
end;

Come sopravvivono le selezioni agli inserimenti di righe e colonne?

Le selezioni sopravvivono alle modifiche strutturali perché inserire o eliminare righe o colonne intere rimappa ogni gruppo di riquadri rappresentato in entrambi gli engine, classico e XLSX, tramite un unico remapper condiviso. Le aree superstiti conservano il loro ordine e l'area attiva conserva la sua identità. Se l'area attiva viene eliminata, diventa attiva la prima successiva superstite, poi l'ultima precedente superstite se nulla la segue. Se ogni area viene eliminata, il gruppo collassa in una cella al confine dell'eliminazione, e una cella attiva che non cade più dentro l'area scelta si sposta nell'angolo alto-sinistra di quell'area, così indice e coordinata non si contraddicono mai. I limiti sono deliberati. I gruppi classici invalidi vengono saltati dal remapper invece di essere riscritti in una selezione inventata, così i loro byte originali fanno comunque il round trip. Modificare un riquadro sostituisce solo i record di quel riquadro e lascia gli altri byte-identici. ODS non riceve alcuno stato di selezione per riquadro, perché ODF non ha una struttura equivalente di vista del foglio che lo trasporti

Se la vostra applicazione scrive file .xls che gli utenti aprono e devono navigare, che sia per rivedere le celle segnalate, riprendere da dove si erano fermati o condividere una dashboard bloccata, l'API di selezione e scroll pensata per i riquadri fa parte del componente spreadsheet HotXLS per Delphi, e funziona allo stesso modo per XLS e XLSX