Как вставлять формулы и функции Excel в JavaScript (React)

Сгенерированная электронная таблица, заполненная заранее вычисленными числами, — это снимок состояния. Она выглядит правильно в момент создания и сразу начинает устаревать: данные, лежащие в её основе, меняются, а числа внутри неё — нет, и после того как файл покинул ваше приложение, никто не может сказать, какие ячейки разрешено изменять. Книга, которая несёт в себе формулы, напротив, остаётся живым документом — измените входные данные, и итоги последуют за ними.

Spire.XLS for JavaScript — это движок электронных таблиц, скомпилированный в WebAssembly, поэтому приложение на React может создавать книги прямо в браузере, без сервера. Файлы читаются и записываются через виртуальную файловую систему (VFS), а формулы записываются так же, как и значения: через объект Range ячейки. Меняется только имя свойства.

В этом последнем пункте и заключается весь фокус. Интересный вопрос не в том, как записать формулу, а в том, с помощью какого из четырёх доступных свойств это сделать, поскольку три из них молча сохранят вашу формулу как обычный текст.

О настройке проекта см. Интеграция Spire.XLS for JavaScript в проект React. Примеры ниже предполагают, что пакет установлен, а модуль WebAssembly инициализирован.

Почему сгенерированные книги должны нести формулы

Сгенерировать файл с уже вписанными ответами проще, а получать его — хуже. Вот случаи, когда это действительно ломается:

• Шаблоны с заполнителями. Предполагается, что получатель заменит входные данные. Если итоги жёстко закодированы, замена входного значения оставит итоги неверными, и ничто об этом не предупредит.
• Модели, передаваемые аналитику. Ему захочется проверить другое допущение. Лист, который нельзя пересчитать заново, — это лист, который придётся перестраивать.
• Отчёты, которые должны быть прослеживаемыми. Число, за которым не видно правила, проверить нельзя. Формулу — можно.
• Листы, питающие другие листы. Другие ячейки ссылаются на них; если значение никогда не пересчитывается, устаревание наследует всё, что находится ниже по цепочке.

Во всех четырёх случаях формула — это суть файла. Значения — лишь побочный продукт.

Предварительные требования

Вам понадобится проект на React с установленным Spire.XLS for JavaScript и инициализированным модулем WebAssembly, доступным по адресу window.wasmModule.spirexls. Пример ниже также загружает шрифт в VFS перед форматированием любого текста и сохраняет файл с флагом версии Excel 2010, чтобы результат корректно открывался и в текущей версии Excel, и в более старых.

Выбор свойства, которое записывает формулу

Каждая ячейка, в которую вы пишете, — это объект Range, и он предоставляет четыре свойства, принимающих значение. Они не взаимозаменяемы:

Text — то, с чем нужно быть осторожным, и стоит понять почему, прежде чем перейти к коду ниже. Присвойте =SUM(B1:F1) свойству Text, и ячейка сохранит эти символы — она будет отображать формулу вечно, потому что ничто её никогда не вычислит.

Такое поведение — не дефект. Именно его пример использует намеренно, чтобы каждая строка показывала формулу слева, а её результат справа: левая ячейка использует Text, потому что она должна отображать правило, а правая использует Formula, потому что она должна его применять.

Запись формул в ячейки

Процесс короткий:

1. Создайте объект Workbook.
2. Получите лист с помощью метода Workbook.Worksheets.get().
3. Запишите входные данные в ячейки и задайте форматирование ячеек.
4. Присвойте формулы ячейкам, которые должны вычисляться, через свойство Range.Formula.
5. Сохраните книгу с помощью Workbook.SaveToFile().

В примере создаётся небольшой лист со строкой входных чисел, а под ней записываются пять формул — арифметическое выражение, функция даты, тригонометрическая функция, среднее значение и сумма:

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;

Вставка формул и результатов функций в листы Excel

Обратите внимание на вызов форматирования перед формулами. Range.get() принимает lastRow и lastColumn, поэтому блок заголовка можно стилизовать одним вызовом, а не по ячейкам — тот же объект, который используется для записи формулы, несёт и стиль.

Функции по категориям

Пять формул в примере — это не пять разных приёмов. Это один приём, применённый к пяти видам выражений:

Отдельного API для «функций» нет. Функция и есть формула — Range.Formula получает строку, а движок решает, что с ней делать. Именно поэтому список того, что можно записать, так же велик, как перечень функций движка электронных таблиц, и для каждой функции не нужно поддерживать обёртку.

Отображение текста формулы рядом с её результатом

Одна из самых полезных привычек при работе со сгенерированным листом — держать правило на виду рядом с его результатом. В примере это делается так: строка формулы помещается в столбец A как обычный текст, а вычисленное значение — в столбец 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;

Именно предварительное присвоение "@" в качестве числового формата не даёт столбцу с подписями пытаться интерпретировать строку — ячейка объявляется текстовой до того, как в неё что-либо записано. Столбцу результатов такая забота не нужна, но ему может понадобиться собственный формат отображения: строка с датой задаёт .Style.NumberFormat = "YYYY/MM/DD", без чего значение отображается как порядковый номер, а не как дата.

Лист, который несёт свои правила таким образом, переживёт любое преобразование, потому что подписи — это обычный текст, которого не коснётся никакой движок.

Одна формула на весь диапазон

Настоящим листам редко нужна одна формула; им нужно одно и то же правило вниз по столбцу. Поскольку строку строите вы, ссылками вы управляете явно:

// 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}`;
}

Это то же поведение относительных ссылок, которое вы получили бы, протянув формулу вниз в Excel, только записанное вручную. Если правило всегда должно указывать на одно фиксированное входное значение, закрепите его — $A$1 не смещается при перемещении формулы, а A1 смещается.

Синтаксис формул, который часто сбивает с толку

• Ведущий знак равенства. Строка формулы без = — не формула. Она будет сохранена как текст и никогда не вычислена.
• Относительные и абсолютные ссылки. A1 смещается; $A$1 — нет. Выбирайте осознанно, когда генерируете формулы в цикле.
• Ссылки между листами. Указывайте имя листа внутри строки — Sheet2!A1. Если имя листа содержит пробелы, заключите его в кавычки: 'Q1 Sales'!A1.
• Разделители аргументов в разных локалях. Строка сохраняется так, как вы её написали. Используйте форму с запятыми, как показано выше, если файл будут открывать в разных локалях, где в некоторых вместо запятых отображаются точки с запятой.
• Волатильные функции. TODAY() и NOW() меняются при каждом пересчёте книги, поэтому значение, прочитанное позже, не совпадёт с тем, что вы видели. Этот разрыв между правилом и его последним вычисленным значением заслуживает внимания сам по себе — именно им занимается Чтение и извлечение формул Excel в JavaScript (React).

Частые проблемы

Ячейка показывает формулу вместо результата. Она была записана через Text, а не через Formula. Переприсвойте её через Formula — ячейке нужно правило, а не символы.

Дата отображается как пятизначное число. Это порядковое значение без применённого формата даты. Задайте .Style.NumberFormat для ячейки, как это сделано в примере для строки с TODAY().

Форматирование применяется к ячейкам, которых я не хотел затрагивать. Проверьте диапазон, переданный в Range.get(). Указание lastRow и lastColumn применяет изменение к блоку, что удобно для заголовка и легко приводит к ошибкам в границах.

Формула сохранена, но при чтении обратно ячейка выглядит пустой. Результаты появляются после того, как книга была вычислена. Сохраняйте файл после записи формул, чтобы вычисленные значения сохранились вместе с ним.

Часто задаваемые вопросы

Нужен ли установленный Excel или Office, чтобы записывать формулы?

Нет. Движок электронных таблиц поставляется вместе с пакетом и работает как WebAssembly в браузере. Ничего не автоматизируется, и на машине пользователя ничего не требуется.

Может ли формула ссылаться на другой лист той же книги?

Да, и записывается она точно так же, как в Excel — включите имя листа в строку формулы.

Можно ли смешивать формулы и обычные значения на одном листе?

Да, и обычно так и делается. Свойства независимы: одни ячейки получают данные через NumberValue или Value, другие получают правила через Formula.

Что происходит с результатами, когда получатель открывает файл?

Формулы сохранены, и Excel пересчитывает их при открытии книги. В этом и смысл записи правил, а не результатов — файл остаётся корректным, даже если входные данные потом изменят.

Требует ли запись формул серверной части?

Нет. Книга создаётся в браузере и возвращается в виде байтов, которые вы превращаете в Blob для скачивания. Ничего никуда не загружается.

См. также

• Чтение и извлечение формул Excel в JavaScript (React)
• Интеграция Spire.XLS for JavaScript в проект React
• Создание именованных диапазонов в React с помощью JavaScript
• Страница продукта Spire.XLS for JavaScript