Leitura e Extração de Fórmulas do Excel em JavaScript (React)

Alguém construiu esta pasta de trabalho anos atrás. Ela recalcula quando os dados mudam, os totais se movem de maneiras que ninguém mais prevê, e não há documentação — porque as fórmulas são a documentação. Ler os números não lhe dirá como eles foram produzidos. Ler as regras, sim.

Spire.XLS for JavaScript compila um mecanismo de planilha para WebAssembly, de modo que um aplicativo React pode abrir um .xlsx existente no navegador, percorrer suas células e extrair a regra por trás de cada uma. A pasta de trabalho trafega por um sistema de arquivos virtual (VFS), então nada é enviado e nenhum backend está envolvido.

Duas perguntas são feitas para cada célula: ela contém uma fórmula e, em caso afirmativo, o que essa fórmula diz? A primeira é uma verificação de propriedade. A segunda é uma leitura. Quase tudo neste artigo decorre de manter essas duas coisas separadas.

Para a configuração do projeto, consulte Integrando o Spire.XLS for JavaScript em um Projeto React. Os exemplos abaixo pressupõem que o pacote esteja instalado e que o módulo WebAssembly tenha sido inicializado.

Quando você precisa das fórmulas, não dos números

A razão para ler regras em vez de valores é quase sempre uma destas:

• Assumir um modelo que ninguém documentou. As regras são a única descrição sobrevivente do que a pasta de trabalho faz.
• Mover cálculos para fora da planilha. Reimplementar um cálculo no código do aplicativo exige conhecer a expressão exata, não apenas seu último resultado.
• Verificar consistência. Uma linha que silenciosamente usa uma regra diferente das linhas ao redor é invisível nos valores e óbvia nas fórmulas.
• Produzir uma solicitação de mudança. Uma lista de células e as regras que elas contêm é algo que um usuário de negócios pode revisar e corrigir.
• Verificar uma pasta de trabalho gerada pelo seu próprio código. Confirmar que o que foi escrito é o que foi armazenado — consulte Como Inserir Fórmulas e Funções do Excel em JavaScript (React) para o lado da escrita desse par.

Pré-requisitos

Você precisa de um projeto React com o Spire.XLS for JavaScript instalado e o módulo WebAssembly inicializado, acessível em window.wasmModule.spirexls. A pasta de trabalho que você deseja inspecionar já deve estar no VFS — carregada da pasta pública do seu aplicativo com FetchFileToVFS, ou gravada ali como bytes, caso tenha vindo de outro lugar.

Se o resultado for formatado — larguras de coluna e afins —, carregue também uma fonte no VFS, como o exemplo faz.

As duas perguntas a fazer para cada célula

Comece pedindo à planilha a região que ela realmente usa:

// 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 é a metade desse trecho que protege você. Percorrer A1:Z1000 em uma planilha com doze linhas usadas gasta a maior parte do tempo em células vazias e deixa você filtrando-as depois. Pedir à planilha sua região alocada mantém o loop proporcional ao conteúdo, o que importa assim que a pasta de trabalho é real.

Então HasFormula decide o que vale a pena ler. É um booleano simples e responde exatamente a uma pergunta — se a célula contém uma fórmula —, que acaba sendo uma pergunta mais restrita do que parece.

Um exemplo completo

O componente abaixo carrega uma pasta de trabalho existente, percorre seu intervalo usado e escreve cada fórmula encontrada em uma nova planilha como uma linha legível — o endereço da célula e a regra nela armazenada:

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;

Leia fórmulas e resultados de funções de planilhas do Excel

Observe o que o código faz com a pasta de trabalho de origem: ele a lê e nada mais. Uma segunda Workbook é criada para a saída, de modo que o arquivo inspecionado nunca é modificado. Isso importa quando você está examinando o documento de outra pessoa — a inspeção deve ser não destrutiva por construção, e não por lembrar de não salvar.

Fórmula ou valor

É aqui que a leitura restrita de HasFormula compensa, porque as propriedades que você pode ler de uma célula não retornam todas a mesma coisa:

O par que causa mais confusão é Formula versus FormulaNumberValue: a mesma célula, duas respostas completamente diferentes. Uma é a regra; a outra é o que a regra produziu. Peça a errada e você obterá um valor tecnicamente válido que não é aquilo que você procurava — uma auditoria de fórmulas que retorna números ou uma extração de valores que retorna fórmulas.

Montando um inventário de fórmulas

O exemplo grava cada ocorrência em uma segunda pasta de trabalho e a baixa. Essa é a forma correta quando o inventário é ele próprio um documento — algo para entregar a um revisor ou anexar a um ticket.

Quando o inventário é para a tela, em vez disso, colete os mesmos dados primeiro e decida como apresentá-los depois:

// 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 é o que torna o resultado utilizável. Ele retorna o endereço na notação da própria planilha — o nome que uma pessoa usaria ao discutir a célula — em vez de um par linha-e-coluna, que é tecnicamente equivalente e praticamente ilegível. Uma entrada que diz B7 pode ser usada; uma entrada que diz linha 7, coluna 2 precisa ser traduzida primeiro.

Mais de uma planilha

O loop acima cobre uma planilha. Um inventário no nível da pasta de trabalho significa repeti-lo para cada planilha, por vez, obtendo cada uma da mesma forma que a primeira, com Workbook.Worksheets.get(i) recebendo o índice.

Dois detalhes valem a pena acertar antes de ampliar a escala. Registre de qual planilha veio cada entrada, porque B7 em duas planilhas são duas células diferentes e uma lista que não as distingue é ambígua exatamente no momento em que isso importa. E mantenha a coluna de saída larga o suficiente — os endereços e as strings de regras são longos, e um inventário truncado é pior que um estreito.

Por que uma célula de fórmula pode passar despercebida

Uma célula que exibe =SUM(B1:F1) não contém necessariamente uma fórmula. Se ela foi escrita por meio de Text ou Value em vez de Formula, ou digitada em uma célula que já estava formatada como texto, então os caracteres são armazenados como uma string. A planilha mostra uma fórmula; a célula contém um rótulo.

HasFormula relata isso corretamente como false, e uma varredura que espera encontrar essa célula fica vazia. Esta é a armadilha neste fluxo de trabalho porque não parece uma falha: a pasta de trabalho contém visivelmente fórmulas, o código roda sem erro e o inventário fica faltando exatamente a quantidade de células que foram digitadas como texto.

Quando uma fórmula parece estar faltando em um inventário, verifique como ela foi escrita antes de verificar o código de leitura. Se a pasta de trabalho é gerada pelo seu próprio aplicativo, essa é a mesma distinção de propriedade que inserir fórmulas aborda do lado da escrita.

Problemas comuns

A varredura não encontra nada, mas a planilha está cheia de fórmulas. Elas estão armazenadas como texto. Consulte a seção acima — HasFormula relata apenas fórmulas reais.

O resultado é um número quando eu queria a fórmula, ou o contrário. Você leu a propriedade errada. Formula fornece a regra, FormulaNumberValue fornece o número calculado.

O loop é lento ou produz centenas de entradas vazias. Ele está percorrendo um intervalo retangular fixo em vez da região alocada da planilha. Use AllocatedRange como fonte da iteração.

Faltam células de uma segunda planilha. O loop é executado em uma única planilha. Repita-o para cada planilha e mantenha a planilha junto de cada entrada.

A pasta de trabalho de origem mudou após a execução. Não deveria ter mudado — o exemplo lê uma pasta de trabalho e grava em outra. Verifique se a saída está sendo salva em um objeto Workbook diferente, como no código acima.

Perguntas frequentes

Preciso ter o Excel instalado para ler fórmulas de uma pasta de trabalho?

Não. O mecanismo vem incluído no pacote e é executado como WebAssembly dentro do navegador. O aplicativo de planilha original não está envolvido em momento algum.

Posso ler o valor calculado em vez da fórmula?

Sim. Leia FormulaNumberValue em vez de Formula da mesma célula. Use HasFormula primeiro para só fazer essa pergunta a células onde ela faz sentido.

Ler uma pasta de trabalho a modifica?

A leitura não modifica. O exemplo abre a entrada, cria uma pasta de trabalho de saída separada para os resultados e salva apenas essa — de modo que o arquivo inspecionado é deixado como estava.

Quais formatos do Excel posso ler?

Tanto o formato legado .xls quanto os arquivos modernos .xlsx são suportados pela mesma API, então uma pasta de trabalho não precisa ser convertida antes de poder ser inspecionada.

Isso funciona para pastas de trabalho armazenadas em um servidor?

Sim, se você conseguir levar os bytes para o navegador. Grave-os no VFS e carregue a partir de lá — a leitura em si é totalmente do lado do cliente, e a pasta de trabalho só é enviada se o seu próprio aplicativo optar por enviá-la.

Veja também

• Como Inserir Fórmulas e Funções do Excel em JavaScript (React)
• Integrando o Spire.XLS for JavaScript em um Projeto React
• Criar Intervalos Nomeados no React com JavaScript
• Página do Produto Spire.XLS for JavaScript