Leggere ed estrarre formule Excel in JavaScript (React)

Qualcuno ha creato questa cartella di lavoro anni fa. Ricalcola quando i dati cambiano, i totali si spostano in modi che nessuno prevede più e non esiste documentazione — perché le formule sono la documentazione. Leggere i numeri non ti dirà come sono stati prodotti. Leggere le regole sì.

Spire.XLS per JavaScript compila un motore per fogli di calcolo in WebAssembly, così un'app React può aprire un .xlsx esistente nel browser, scorrerne le celle ed estrarre la regola dietro ognuna di esse. La cartella di lavoro passa attraverso un file system virtuale (VFS), quindi nulla viene caricato e nessun backend è coinvolto.

A ogni cella vengono poste due domande: contiene una formula e, se sì, cosa dice quella formula? La prima è una verifica di proprietà. La seconda è una lettura. Quasi tutto in questo articolo deriva dal tenere separate queste due cose.

Per la configurazione del progetto, vedi Integrare Spire.XLS per JavaScript in un progetto React. Gli esempi seguenti presuppongono che il pacchetto sia installato e che il modulo WebAssembly sia stato inizializzato.

Quando servono le formule, non i numeri

Il motivo per leggere le regole anziché i valori è quasi sempre uno di questi:

• Subentrare in un modello che nessuno ha documentato. Le regole sono l'unica descrizione sopravvissuta di ciò che fa la cartella di lavoro.
• Spostare i calcoli fuori dal foglio di calcolo. Reimplementare un calcolo nel codice dell'applicazione richiede di conoscere l'espressione esatta, non solo il suo ultimo risultato.
• Verificare la coerenza. Una riga che usa silenziosamente una regola diversa da quelle intorno a sé è invisibile nei valori e ovvia nelle formule.
• Produrre una richiesta di modifica. Un elenco di celle e delle regole che contengono è qualcosa che un utente business può esaminare e correggere.
• Verificare una cartella di lavoro generata dal tuo stesso codice. Confermare che ciò che è stato scritto è ciò che è stato memorizzato — vedi Come inserire formule e funzioni di Excel in JavaScript (React) per il lato di scrittura di questa coppia.

Prerequisiti

Ti serve un progetto React con Spire.XLS per JavaScript installato e il modulo WebAssembly inizializzato, raggiungibile all'indirizzo window.wasmModule.spirexls. La cartella di lavoro che vuoi ispezionare dovrebbe già trovarsi nel VFS — caricata dalla cartella public della tua applicazione con FetchFileToVFS, oppure scritta lì come byte se è arrivata da altrove.

Se il risultato dovrà essere formattato — larghezze di colonna e simili — carica nel VFS anche un font, come fa l'esempio.

Le due domande da porre a ogni cella

Inizia chiedendo al foglio di lavoro la regione che effettivamente utilizza:

// The region the sheet actually uses — not the whole grid
const usedRange = sheet.AllocatedRange;

for (const cell of usedRange.Cells) {
  if (cell.HasFormula) {
    // this cell holds a rule
  }
}

AllocatedRange è la metà di quel frammento che ti protegge. Ciclare su A1:Z1000 in un foglio con dodici righe utilizzate passa la maggior parte del tempo su celle vuote e ti lascia a filtrarle in seguito. Chiedere al foglio la sua regione allocata mantiene il ciclo proporzionato al contenuto, cosa che conta non appena la cartella di lavoro è reale.

Poi HasFormula decide cosa vale la pena leggere. È un semplice booleano e risponde esattamente a una domanda — se la cella contiene una formula — che si rivela una domanda più ristretta di quanto sembri.

Un esempio completo

Il componente seguente carica una cartella di lavoro esistente, scorre il suo intervallo utilizzato e scrive ogni formula trovata in un nuovo foglio come riga leggibile — l'indirizzo della cella e la regola in essa memorizzata:

function App() {
  const readFormulasAndFunctions = async () => {
    // Get the Spire.XLS WASM module
    const xlsModule = window.wasmModule?.spirexls;

    // Check whether the module is ready
    if (!xlsModule) {
      alert('Spire.Xls is not ready yet');
      return;
    }

    // Load the font and Excel file into the VFS
    await window.spire.FetchFileToVFS('ARIAL.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/font/`);
    const inputFileName = 'FormulasAndFunctions.xlsx';
    await window.spire.FetchFileToVFS(inputFileName, '', `${process.env.PUBLIC_URL}data/`);

    // Create a Workbook object
    const workbook = new xlsModule.Workbook();

    // Load the Excel workbook
    workbook.LoadFromFile({ fileName: inputFileName });

    // Get the first worksheet
    const sheet = workbook.Worksheets.get(0);

    // Get the used cell range of the worksheet
    const usedRange = sheet.AllocatedRange;

    // Create an output workbook
    const output = new xlsModule.Workbook();
    const outSheet = output.Worksheets.get(0);
    let outRow = 1;

    // Loop through the used cells
    for (const cell of usedRange.Cells) {
      // Check whether the cell contains a formula or function
      if (cell.HasFormula) {
        // Get the cell name
        const cellname = cell.RangeAddressLocal;

        // Get the formula or function in the cell
        const formula = cell.Formula;

        // Write the cell name and formula that were read
        outSheet.Range.get({ row: outRow, column: 1 }).Value = "Cell " + cellname + " contains: " + formula;
        outRow += 1;
      }
    }

    // Set the output column width so the text displays completely
    outSheet.SetColumnWidth(1, 45);

    // Save the output workbook
    const outputFileName = 'ReadFormulasAndFunctions_output.xlsx';
    output.SaveToFile({ fileName: outputFileName, version: xlsModule.ExcelVersion.Version2010 });

    // Release resources
    output.Dispose();

    // Read the converted file from the VFS and trigger a download
    const fileArray = window.dotnetRuntime.Module.FS.readFile(outputFileName);
    const blob = new Blob([fileArray], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
    const url = URL.createObjectURL(blob);
    const a = document.createElement('a');
    a.href = url;
    a.download = outputFileName;
    a.click();
    URL.revokeObjectURL(url);
  };

  return (
    <div style={{ textAlign: 'center', height: '300px' }}>
      <h1>Read Formulas and Functions</h1>
      <button onClick={readFormulasAndFunctions}>
        Start
      </button>
    </div>
  );
}

export default App;

Leggi formule e risultati delle funzioni dai fogli di lavoro Excel

Nota cosa fa il codice con la cartella di lavoro di origine: la legge e nulla più. Per l'output viene creato un secondo Workbook, quindi il file ispezionato non viene mai modificato. Questo conta quando stai esaminando il documento di qualcun altro — l'ispezione dovrebbe essere non distruttiva per costruzione, non perché ti ricordi di non salvare.

Formula o valore

Qui è dove la lettura ristretta di HasFormula ripaga, perché le proprietà che puoi leggere da una cella non restituiscono tutte la stessa cosa:

La coppia che causa più confusione è Formula rispetto a FormulaNumberValue: la stessa cella, due risposte completamente diverse. Una è la regola; l'altra è ciò che la regola ha prodotto. Chiedi quella sbagliata e otterrai un valore tecnicamente valido che non è ciò che stavi cercando — un audit delle formule che restituisce numeri, o un'estrazione di valori che restituisce formule.

Comporre un inventario delle formule

L'esempio scrive ogni occorrenza in una seconda cartella di lavoro e la scarica. Questa è la forma giusta quando l'inventario è esso stesso un documento — qualcosa da consegnare a un revisore o da allegare a un ticket.

Quando invece l'inventario è destinato allo schermo, raccogli prima gli stessi dati e decidi dopo come presentarli:

// Collect first, then decide how to present it
const inventory = [];
for (const cell of usedRange.Cells) {
  if (cell.HasFormula) {
    inventory.push({ cell: cell.RangeAddressLocal, formula: cell.Formula });
  }
}

RangeAddressLocal è ciò che rende utilizzabile il risultato. Restituisce l'indirizzo nella notazione propria del foglio — il nome che una persona userebbe parlando della cella — anziché una coppia riga-colonna, che è tecnicamente equivalente e praticamente illeggibile. Una voce che dice B7 può essere gestita; una voce che dice riga 7, colonna 2 deve prima essere tradotta.

Più di un foglio di lavoro

Il ciclo sopra copre un solo foglio. Un inventario a livello di cartella di lavoro significa ripeterlo per ogni foglio di lavoro, recuperando ciascuno nello stesso modo in cui viene recuperato il primo, con Workbook.Worksheets.get(i) che prende l'indice.

Due dettagli valgono la pena di essere gestiti correttamente prima di scalare il tutto. Registra da quale foglio di lavoro proviene ogni voce, perché B7 su due fogli sono due celle diverse e un elenco che non le distingue è ambiguo esattamente nel momento in cui conta. E mantieni la colonna di output abbastanza larga — gli indirizzi e le stringhe delle regole sono lunghi, e un inventario troncato è peggio di uno stretto.

Perché una cella con formula può non essere rilevata

Una cella che visualizza =SUM(B1:F1) non necessariamente contiene una formula. Se è stata scritta tramite Text o Value invece che tramite Formula, oppure digitata in una cella già formattata come testo, allora i caratteri sono memorizzati come stringa. Il foglio mostra una formula; la cella contiene un'etichetta.

HasFormula lo segnala correttamente come false, e una scansione che si aspetta di trovare quella cella risulta vuota. Questa è la trappola in questo flusso di lavoro perché non sembra un errore: la cartella di lavoro contiene visibilmente delle formule, il codice viene eseguito senza errori e l'inventario è più corto di tutte le celle che sono state digitate come testo.

Quando una formula sembra mancare da un inventario, controlla come è stata scritta prima di controllare il codice di lettura. Se la cartella di lavoro è generata dalla tua stessa applicazione, questa è la stessa distinzione di proprietà che l'inserimento di formule copre dal lato della scrittura.

Problemi comuni

La scansione non trova nulla, ma il foglio è pieno di formule. Sono memorizzate come testo. Vedi la sezione precedente — HasFormula segnala solo le formule reali.

Il risultato è un numero quando volevo la formula, o viceversa. Hai letto la proprietà sbagliata. Formula fornisce la regola, FormulaNumberValue fornisce il numero calcolato.

Il ciclo è lento o produce centinaia di voci vuote. Sta scorrendo un intervallo rettangolare fisso invece della regione allocata del foglio. Usa AllocatedRange come origine dell'iterazione.

Mancano le celle di un secondo foglio. Il ciclo viene eseguito su un solo foglio di lavoro. Ripetilo per ogni foglio e mantieni il nome del foglio accanto a ogni voce.

La cartella di lavoro di origine è cambiata dopo l'esecuzione. Non dovrebbe esserlo — l'esempio legge una cartella di lavoro e ne scrive un'altra. Verifica che l'output venga salvato in un oggetto Workbook diverso, come nel codice sopra.

Domande frequenti

Devo avere Excel installato per leggere le formule da una cartella di lavoro?

No. Il motore è incluso nel pacchetto e funziona come WebAssembly all'interno del browser. L'applicazione originale per fogli di calcolo non è coinvolta in nessun momento.

Posso leggere il valore calcolato invece della formula?

Sì. Leggi FormulaNumberValue anziché Formula dalla stessa cella. Usa prima HasFormula in modo da porre quella domanda solo alle celle in cui ha senso.

Leggere una cartella di lavoro la modifica?

La lettura no. L'esempio apre l'input, crea una cartella di lavoro di output separata per i risultati e salva solo quella — così il file ispezionato resta com'era.

Quali formati Excel posso leggere?

Sia il formato legacy .xls sia i file moderni .xlsx sono supportati dalla stessa API, quindi una cartella di lavoro non deve essere convertita prima di poter essere ispezionata.

Funziona per cartelle di lavoro archiviate su un server?

Sì, se riesci a portare i byte nel browser. Scrivili nel VFS e carica da lì — la lettura stessa è interamente lato client, e la cartella di lavoro viene caricata solo se la tua applicazione sceglie di caricarla.

Vedi anche

• Come inserire formule e funzioni di Excel in JavaScript (React)
• Integrare Spire.XLS per JavaScript in un progetto React
• Creare intervalli denominati in React con JavaScript
• Pagina del prodotto Spire.XLS per JavaScript