Руководство: создание пользовательских функций в Excel

  • Статья

Пользовательские функции позволяют добавлять новые функции в Excel путем определения этих функций в JavaScript как части надстройки. Пользователи в Excel могут получить доступ к пользовательским функциям так же, как и к любой встроенной функции в Excel, например SUM(). Вы можете создавать пользовательские функции, которые будут выполнять простые задачи, такие как вычисления, или более сложные задачи, такие как потоковая передача данных в режиме реального времени из Интернета на лист.

В этом руководстве описан порядок выполнения перечисленных ниже задач.

  • Создание надстройки пользовательской функции с помощью генератора Yeoman для надстроек Office.
  • Использование готовой пользовательской функции для выполнения простых вычислений
  • Создание пользовательской функции, которая получает данные из сети Интернет.
  • Создание пользовательской функции, которая осуществляет потоковую передачу данных в реальном времени из сети Интернет

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

Создание проекта пользовательских функций

Чтобы начать, создайте проект кода для разработки надстройки пользовательской функции. Генератор Yeoman для надстроек Office настроит в вашем проекте некоторые готовые пользовательские функции, которые можно попробовать. Если вы уже с помощью краткого руководства по пользовательским функциям создали проект, то продолжайте работать с ним и пропустите этот шаг.

Примечание.

При повторном создании проекта Yo Office может возникнуть ошибка, так как в кэше Office уже есть экземпляр функции с тем же именем. Это можно предотвратить путем очистки кэша Office перед запуском npm run start.

  1. Выполните следующую команду, чтобы создать проект надстройки с помощью генератора Yeoman. Папка, содержащая проект, будет добавлена в текущий каталог.

    yo office

    Примечание.

    При выполнении команды yo office может появиться запрос о политиках сбора данных генератора Yeoman и средств CLI надстройки Office. Используйте предоставленные сведения, чтобы ответить на запросы подходящим образом.

    При появлении запроса предоставьте следующую информацию для создания проекта надстройки.

    • Выберите тип проекта:Excel Custom Functions using a Shared Runtime
    • Выберите тип скрипта:JavaScript
    • Как вы хотите назвать надстройку?My custom functions add-in

    Интерфейс командной строки генератора надстроек Yeoman Office запрашивает проекты пользовательских функций.

    Генератор Yeoman создаст файлы проекта и установит вспомогательные компоненты Node.

    Примечание.

    Если вы используете Node.js версии 20.0.0 или более поздней, при запуске установки генератора может появилось предупреждение о том, что у вас неподдерживаемый модуль. Мы работаем над исправлением этого. В то же время предупреждение не влияет на генератор или проект, который вы создаете, поэтому его можно игнорировать.

    Совет

    Вы можете игнорировать инструкции по дальнейшим действиям, предоставляемые генератором Yeoman после создания проекта надстройки. Пошаговые инструкции этой статьи содержат все сведения, необходимые для завершения этого учебного курса.

  2. Перейдите к корневой папке проекта.

    cd "My custom functions add-in"

  3. Выполните построение проекта.

    npm run build

    Примечание.

    Надстройки Office должны использовать HTTPS, а не HTTP, даже в случае разработки. Если вам будет предложено установить сертификат после того, как вы запустите npm run build, примите предложение установить сертификат от генератора Yeoman.

  4. Запустите локальный веб-сервер, работающий на Node.js. Вы можете попробовать использовать надстройку пользовательской функции в Excel.

Чтобы проверить надстройку в Excel для Windows или Mac, выполните следующую команду. Когда вы выполните эту команду, запустится локальный веб-сервер и откроется приложение Excel, в котором будет загружена ваша надстройка.

npm run start:desktop

Примечание.

Надстройки Office должны использовать HTTPS, а не HTTP, даже во время разработки. Если после выполнения одной из следующих команд вам будет предложено установить сертификат, примите запрос на установку сертификата, который предоставляет генератор Yeoman. Кроме того, вам может потребоваться запустить командную строку или терминал с правами администратора, чтобы внести изменения.

Проверка работы готовой пользовательской функции

Созданный проект пользовательских функций содержит некоторые готовые пользовательские функции, определенные в файле src/functions/functions.js. Файл ./manifest.xml указывает, что все пользовательские функции принадлежат пространству имен CONTOSO. Вы будете использовать пространство имен CONTOSO для доступа к пользовательским функциям в Excel.

Попробуйте, как работает пользовательская функция ADD, выполнив описанные далее шаги.

  1. В Excel перейдите в любую ячейку и введите =CONTOSO. Обратите внимание на то, что в меню автозаполнения содержится список всех функций в пространстве имен CONTOSO.

  2. Выполните запуск функции CONTOSO.ADD с числами 10 и 200 в качестве входных параметров, введя значение =CONTOSO.ADD(10,200) в ячейке и нажав клавишу ВВОД.

Пользовательская функция ADD вычисляет сумму двух чисел, которые вы указываете и возвращает результат 210.

Если пространство имен CONTOSO недоступно в меню автозаполнения, для регистрации надстройки в Excel выполните следующие действия.

  1. В Excel выберите Главная>надстройки, а затем — Другие надстройки.

  2. На вкладке MY ADD-INS выберите Управление моими надстройками и выберите Отправить мою надстройку.

  3. Выберите Обзор... и откройте корневой каталог проекта, созданный генератором Yeoman.

  4. Выберите файл manifest.xml и нажмите Открыть, затем нажмите кнопку Отправить.

  5. Теперь давайте оценим, как работает новая функция. В ячейке B1 введите текст =CONTOSO.GETSTARCOUNT("OfficeDev", "Excel-Custom-Functions") и нажмите клавишу ВВОД. Результат в ячейке B1 — это текущее количество звезд, отданных репозиторию Excel-Custom-Functions Github.

Примечание.

Если при загрузке надстройки неопубликованных приложений возникают ошибки, см. раздел Устранение неполадок этой статьи.

Создание пользовательской функции, которая запрашивает данные из сети Интернет

Интеграция данных из Интернета — отличный способ расширения функционала Excel через пользовательские функции. Затем вы создадите пользовательскую функцию с именем getStarCount, показывающую, сколько звезд имеет данный репозиторий Github.

  1. В проекте Моя надстройка с настраиваемыми функциями найдите файл ./src/functions/functions.js и откройте его в редакторе кода.

  2. В function.jsдобавьте следующий код.

    /**
  * Gets the star count for a given Github repository.
  * @customfunction 
  * @param {string} userName string name of Github user or organization.
  * @param {string} repoName string name of the Github repository.
  * @return {number} number of stars given to a Github repository.
  */
  async function getStarCount(userName, repoName) {
    try {
      //You can change this URL to any web request you want to work with.
      const url = "https://api.github.com/repos/" + userName + "/" + repoName;
      const response = await fetch(url);
      //Expect that status code is in 200-299 range
      if (!response.ok) {
        throw new Error(response.statusText)
      }
        const jsonResponse = await response.json();
        return jsonResponse.watchers_count;
    }
    catch (error) {
      return error;
    }
  }

  3. Выполните указанную ниже команду, чтобы повторно собрать проект.

    npm run build

  4. Чтобы повторно зарегистрировать надстройку в Excel, выполните указанные ниже действия (для Excel в Интернете, для Windows или для Mac). Выполните описанные ниже действия, чтобы новая функция стала доступной.

  1. Закройте Excel, а затем откройте Excel повторно.

  2. На ленте Excel выберите Главная>надстройки.

  3. В разделе Надстройки разработчика выберите Надстройка Мои пользовательские функции , чтобы зарегистрировать ее.

    Диалоговое окно

  4. Теперь давайте оценим, как работает новая функция. В ячейке B1 введите текст =CONTOSO. GETSTARCOUNT("OfficeDev", "Office-Add-in-Samples") и нажмите клавишу ВВОД. Вы увидите, что результатом в ячейке B1 является текущее количество звезд, предоставленных репозиторию Office-Add-in-Samples.

Примечание.

Если при загрузке надстройки неопубликованных приложений возникают ошибки, см. раздел Устранение неполадок этой статьи.

Создание потоковой асинхронной пользовательской функции

Функция getStarCount возвращает количество звезд, которые есть у репозитория в определенный момент времени. Пользовательские функции также возвращают непрерывно изменяемые данные. Эти функции называются потоковыми передачами функций. Они должны содержать параметр invocation, ссылающийся на ячейку, из которой была вызвана функция. Параметр invocation используется для обновления содержимого ячейки в любое время.

В примере кода ниже вы заметите наличие двух функций, currentTime и clock. Функция currentTime — это статическая функция, которая не использует потоковую передачу функций. Она возвращает дату в виде строки. Функция clock использует функцию currentTime для обеспечения нового времени каждую секунду для ячейки в Excel. В ней используется invocation.setResult для передачи времени в ячейку Excel и invocation.onCanceled для обработки отмены функции.

Проект Моя надстройка с настраиваемыми функциями уже содержит две следующие функции в файле ./src/functions/functions.js.

/**
 * Returns the current time
 * @returns {string} String with the current time formatted for the current locale.
 */
function currentTime() {
  return new Date().toLocaleTimeString();
}
    
/**
 * Displays the current time once a second
 * @customfunction
 * @param {CustomFunctions.StreamingInvocation<string>} invocation Custom function invocation
 */
function clock(invocation) {
  const timer = setInterval(() => {
    const time = currentTime();
    invocation.setResult(time);
  }, 1000);
    
  invocation.onCanceled = () => {
    clearInterval(timer);
  };
}

Чтобы опробовать функции, введите текст =CONTOSO.CLOCK() в ячейку C1 и нажмите ВВОД. Должна отобразиться текущая дата, которая потоком обновляется каждую секунду. Хотя эти часы являются просто таймером в цикле, однако можно использовать аналогичную идею настройки таймера для более сложных функций, которые выполняют веб-запросы в режиме реального времени.

Устранение неполадок

При выполнении руководства несколько раз могут возникнуть проблемы. Если в кэше Office уже есть экземпляр функции с таким же именем, в вашей надстройке возникнет ошибка при ее загрузке без публикации.

Этот конфликт можно предотвратить, очистив кэш Office перед запуском npm run start. Если процесс npm уже запущен, введите npm stop, очистите кэш Office, а затем перезапустите npm.

Сообщение об ошибке в Excel под названием

Дальнейшие действия

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