Como Inserir Fórmulas e Funções do Excel em JavaScript (React)

Uma planilha gerada cheia de números pré-calculados é um instantâneo. Ela parece correta no momento em que é produzida e começa a envelhecer imediatamente: os dados por trás dela avançam, os números dentro dela não, e depois que o arquivo saiu da sua aplicação ninguém consegue dizer quais células podem ser alteradas. Uma pasta de trabalho que carrega suas fórmulas, por outro lado, permanece um documento vivo — edite uma entrada e os totais acompanham.

O Spire.XLS for JavaScript é um mecanismo de planilha compilado para WebAssembly, de modo que um aplicativo React pode criar pastas de trabalho no navegador sem um servidor. Os arquivos são lidos e gravados por meio de um sistema de arquivos virtual (VFS), e as fórmulas são escritas da mesma forma que os valores: através do objeto Range de uma célula. Só o nome da propriedade muda.

Esse último ponto é todo o truque. A pergunta interessante não é como escrever uma fórmula, mas com qual das quatro propriedades disponíveis escrevê-la, porque três delas armazenarão silenciosamente sua fórmula como texto simples.

Para a configuração do projeto, consulte Integrating Spire.XLS for JavaScript in a React Project. Os exemplos abaixo pressupõem que o pacote está instalado e que o módulo WebAssembly foi inicializado.

Por que pastas de trabalho geradas devem conter fórmulas

Gerar um arquivo com as respostas já preenchidas é mais fácil de escrever e pior de receber. Os casos em que isso realmente quebra:

• Modelos com espaços reservados. Espera-se que o destinatário substitua as entradas. Se os totais estiverem codificados diretamente, substituir uma entrada deixa os totais errados e nada o avisa.
• Modelos entregues a um analista. Ele vai querer testar uma premissa diferente. Uma planilha que não pode ser recalculada é uma planilha que ele terá de reconstruir.
• Relatórios que precisam ser rastreáveis. Um número sem uma regra visível por trás dele não pode ser verificado. Uma fórmula pode.
• Planilhas que alimentam outras planilhas. Outras células fazem referência a estas; se o valor nunca for recalculado, tudo o que está a jusante herda a desatualização.

Em todos os quatro casos, a fórmula é o ponto central do arquivo. Os valores são um subproduto.

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. O exemplo abaixo também carrega uma fonte no VFS antes de formatar qualquer texto e salva com o sinalizador da versão Excel 2010 para que a saída abra sem problemas tanto no Excel atual quanto em versões mais antigas.

Escolhendo a propriedade que escreve uma fórmula

Cada célula em que você escreve é um objeto Range, e ele expõe quatro propriedades que aceitam algo. Elas não são intercambiáveis:

Text é a propriedade com que se deve ter cuidado, e vale a pena entender por quê antes do código abaixo. Atribua =SUM(B1:F1) a Text e a célula armazenará esses caracteres — ela exibirá a fórmula para sempre, porque nada jamais a avaliará.

Esse comportamento não é um defeito. É exatamente o que o exemplo usa deliberadamente, para que cada linha possa mostrar a fórmula à esquerda e seu resultado à direita: a célula da esquerda usa Text porque sua finalidade é exibir a regra, e a célula da direita usa Formula porque sua finalidade é aplicá-la.

Escrevendo fórmulas nas células

O fluxo é curto:

1. Crie um objeto Workbook.
2. Obtenha uma planilha com o método Workbook.Worksheets.get().
3. Escreva os dados de entrada nas células e defina a formatação das células.
4. Atribua fórmulas às células que devem calcular, por meio da propriedade Range.Formula.
5. Salve a pasta de trabalho com Workbook.SaveToFile().

O exemplo cria uma pequena planilha com uma linha de números de entrada e, em seguida, escreve cinco fórmulas abaixo dela — uma expressão aritmética, uma função de data, uma função trigonométrica, uma média e uma soma:

function App() {
  const insertFormulasAndFunctions = 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 into the VFS
    await window.spire.FetchFileToVFS('ARIAL.TTF', '/Library/Fonts/', `${process.env.PUBLIC_URL}/font/`);

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

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

    // Declare two variables: currentRow and currentFormula
    let currentRow = 1;
    let currentFormula = "";

    // Set the column width
    sheet.SetColumnWidth(1, 32);
    sheet.SetColumnWidth(2, 16);

    // Write data into cells
    sheet.Range.get({ row: currentRow, column: 1 }).Value = "Test Data";
    sheet.Range.get({ row: currentRow, column: 2 }).NumberValue = 1;
    sheet.Range.get({ row: currentRow, column: 3 }).NumberValue = 2;
    sheet.Range.get({ row: currentRow, column: 4 }).NumberValue = 3;
    sheet.Range.get({ row: currentRow, column: 5 }).NumberValue = 4;
    sheet.Range.get({ row: currentRow, column: 6 }).NumberValue = 5;
    currentRow += 2;
    sheet.Range.get({ row: currentRow, column: 1 }).Value = "Formula or Function";
    sheet.Range.get({ row: currentRow, column: 2 }).Value = "Result";

    // Set the cell formatting
    let range = sheet.Range.get({ row: currentRow, column: 1, lastRow: currentRow, lastColumn: 2 });
    range.Style.Font.FontName = "Arial";
    range.Style.KnownColor = xlsModule.ExcelColors.LightGreen;
    range.Style.FillPattern = xlsModule.ExcelPatternType.Solid;
    range.Style.Borders.get(xlsModule.BordersLineType.EdgeBottom).LineStyle = xlsModule.LineStyleType.Medium;
    range.Style.Font.IsBold = true;

    // Mathematical operation
    currentFormula = "=1/2+3*4";
    currentRow += 1;
    sheet.Range.get({ row: currentRow, column: 1 }).NumberFormat = "@";
    sheet.Range.get({ row: currentRow, column: 1 }).Text = currentFormula;
    sheet.Range.get({ row: currentRow, column: 2 }).Formula = currentFormula;

    // Date function
    currentFormula = "=TODAY()";
    currentRow += 1;
    sheet.Range.get({ row: currentRow, column: 1 }).NumberFormat = "@";
    sheet.Range.get({ row: currentRow, column: 1 }).Text = currentFormula;
    sheet.Range.get({ row: currentRow, column: 2 }).Formula = currentFormula;
    sheet.Range.get({ row: currentRow, column: 2 }).Style.NumberFormat = "YYYY/MM/DD";

    // Trigonometric function
    currentFormula = "=SIN(PI()/6)";
    currentRow += 1;
    sheet.Range.get({ row: currentRow, column: 1 }).NumberFormat = "@";
    sheet.Range.get({ row: currentRow, column: 1 }).Text = currentFormula;
    sheet.Range.get({ row: currentRow, column: 2 }).Formula = currentFormula;

    // Average function
    currentFormula = "=AVERAGE(B1:F1)";
    currentRow += 1;
    sheet.Range.get({ row: currentRow, column: 1 }).NumberFormat = "@";
    sheet.Range.get({ row: currentRow, column: 1 }).Text = currentFormula;
    sheet.Range.get({ row: currentRow, column: 2 }).Formula = currentFormula;

    // Sum function
    currentFormula = "=SUM(B1:F1)";
    currentRow += 1;
    sheet.Range.get({ row: currentRow, column: 1 }).NumberFormat = "@";
    sheet.Range.get({ row: currentRow, column: 1 }).Text = currentFormula;
    sheet.Range.get({ row: currentRow, column: 2 }).Formula = currentFormula;

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

    // Release resources
    workbook.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>Insert Formulas and Functions</h1>
      <button onClick={insertFormulasAndFunctions}>
        Start
      </button>
    </div>
  );
}

export default App;

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

Observe a chamada de formatação antes das fórmulas. Range.get() aceita lastRow e lastColumn, então um bloco de cabeçalho pode ser estilizado em uma única chamada em vez de célula por célula — o mesmo objeto que você usa para escrever uma fórmula também carrega o estilo.

Funções por categoria

As cinco fórmulas do exemplo não são cinco técnicas diferentes. São uma única técnica aplicada a cinco tipos de expressão:

Não existe uma API separada para "funções". Uma função é uma fórmula — Range.Formula recebe a string, e o mecanismo decide o que fazer com ela. É por isso que o catálogo de coisas que você pode escrever é tão grande quanto a lista de funções do mecanismo de planilha, sem nenhum wrapper a manter por função.

Exibindo o texto da fórmula ao lado do seu resultado

Um dos hábitos mais úteis em uma planilha gerada é manter a regra visível ao lado da sua saída. O exemplo faz isso colocando a string da fórmula na coluna A como texto literal e o valor avaliado na coluna B:

// Column A displays the rule; column B applies it
sheet.Range.get({ row: currentRow, column: 1 }).NumberFormat = "@";
sheet.Range.get({ row: currentRow, column: 1 }).Text = currentFormula;
sheet.Range.get({ row: currentRow, column: 2 }).Formula = currentFormula;

Atribuir "@" como formato de número primeiro é o que impede a coluna de rótulos de tentar interpretar a string — a célula é declarada como texto antes que qualquer coisa seja escrita nela. A coluna de resultados não precisa desse cuidado, mas pode precisar de um formato de exibição próprio: a linha da data define .Style.NumberFormat = "YYYY/MM/DD", sem o qual o valor é renderizado como um número de série em vez de uma data.

Uma planilha que carrega suas próprias regras assim sobrevive a cada ida e volta, porque os rótulos são texto simples que nenhum mecanismo vai tocar.

Uma fórmula em todo um intervalo

Planilhas reais raramente precisam de uma única fórmula; elas precisam da mesma regra ao longo de uma coluna. Como você é quem monta a string, você controla as referências explicitamente:

// One rule, many rows: the row number in the reference shifts with each cell
for (let row = 2; row <= 11; row += 1) {
  sheet.Range.get({ row: row, column: 3 }).Formula = `=A${row}*B${row}`;
}

Esse é o mesmo comportamento de referência relativa que você obteria arrastando uma fórmula para baixo no Excel, escrito por extenso. Se a regra deve sempre apontar para uma entrada fixa, fixe-a — $A$1 não muda quando a fórmula se move, enquanto A1 muda.

Sintaxe de fórmulas que costuma causar problemas

• O sinal de igual no início. Uma string de fórmula sem = não é uma fórmula. Ela será armazenada como texto e nunca avaliada.
• Referências relativas versus absolutas. A1 muda; $A$1 não. Escolha deliberadamente ao gerar fórmulas em um laço.
• Referências entre planilhas. Nomeie a planilha dentro da string — Sheet2!A1. Se o nome da planilha contiver espaços, coloque-o entre aspas: 'Q1 Sales'!A1.
• Separadores de argumentos entre localidades. A string é armazenada como você a escreve. Mantenha a forma separada por vírgulas usada acima se o arquivo for aberto em uma combinação de localidades, nas quais algumas exibem ponto e vírgula.
• Funções voláteis. TODAY() e NOW() mudam sempre que a pasta de trabalho é recalculada, então um valor lido depois não corresponderá ao que você viu. Essa lacuna entre uma regra e seu último valor calculado merece ser conhecida por si só — é disso que trata Reading and Extracting Excel Formulas in JavaScript (React).

Problemas comuns

A célula exibe a fórmula em vez do resultado. Ela foi escrita por meio de Text em vez de Formula. Reatribua-a com Formula — a célula precisa da regra, não dos caracteres.

Uma data aparece como um número de cinco dígitos. Esse é o valor de série sem nenhum formato de data aplicado. Defina .Style.NumberFormat na célula, como o exemplo faz para a linha de TODAY().

A formatação recai sobre células que eu não pretendia tocar. Verifique o intervalo que você passou para Range.get(). Informar lastRow e lastColumn aplica a alteração a um bloco, o que é conveniente para um cabeçalho e fácil de delimitar incorretamente.

A fórmula é armazenada, mas a célula parece vazia quando lida de volta. Os resultados aparecem depois que a pasta de trabalho é calculada. Salve após escrever as fórmulas para que os valores calculados viajem junto com o arquivo.

Perguntas frequentes

Preciso do Excel ou do Office instalado para escrever fórmulas?

Não. O mecanismo de planilha é fornecido com o pacote e é executado como WebAssembly no navegador. Nada é automatizado e nada é necessário na máquina do usuário.

Uma fórmula pode fazer referência a uma planilha diferente na mesma pasta de trabalho?

Sim, e você a escreve exatamente como faria no Excel — inclua o nome da planilha na string da fórmula.

Posso misturar fórmulas e valores simples em uma mesma planilha?

Sim, e normalmente você fará isso. As propriedades são independentes: algumas células recebem dados por meio de NumberValue ou Value, outras recebem regras por meio de Formula.

O que acontece com os resultados quando o destinatário abre o arquivo?

As fórmulas são armazenadas, e o Excel recalcula quando a pasta de trabalho é aberta. Esse é o objetivo de escrever regras em vez de resultados — o arquivo permanece correto mesmo que as entradas sejam editadas depois.

Escrever fórmulas exige um backend?

Não. A pasta de trabalho é criada no navegador e retornada como bytes que você transforma em um Blob para download. Nada é enviado.

Veja também

• Reading and Extracting Excel Formulas in JavaScript (React)
• Integrating Spire.XLS for JavaScript in a React Project
• Create Named Ranges in React with JavaScript
• Spire.XLS for JavaScript Product Page