Share via

Tutorial: Criar funções personalizadas no Excel

  • Artigo

Funções personalizadas permitem que você adicione novas funções do Excel definindo essas funções em JavaScript como parte de um suplemento. Os usuários do Excel podem acessar funções personalizadas como fariam com qualquer função nativa no Excel, como SUM(). Você pode criar funções personalizadas que realizam tarefas simples como cálculos ou tarefas mais complexas, como streaming de dados da web em tempo real em uma planilha.

Neste tutorial, você vai:

  • Crie um suplemento de função personalizada usando o gerador Yeoman de suplementos do Office.
  • Usar uma função personalizada predefinida para realizar um cálculo simples.
  • Criar uma função personalizada que solicita dados da web.
  • Criar uma função personalizada que transmite os dados da web em tempo real.

Pré-requisitos

  • Node.js (a versão mais recente de LTS). Visite o siteNode.js para baixar e instalar a versão certa para seu sistema operacional.

  • A versão mais recente do Yeoman e do Yeoman gerador de Suplementos do Office. Para instalar essas ferramentas globalmente, execute o seguinte comando por meio do prompt de comando.

    npm install -g yo generator-office

    Observação

    Mesmo se você já instalou o gerador Yeoman, recomendamos atualizar seu pacote para a versão mais recente do npm.

  • Office conectado a uma assinatura Microsoft 365 (incluindo o Office na web).

    Observação

    Se você ainda não tiver o Office, poderá se qualificar para uma assinatura de desenvolvedor Microsoft 365 E5 por meio do Programa de Desenvolvedor do Microsoft 365; para obter detalhes, confira as perguntas frequentes. Como alternativa, você pode se inscrever para uma avaliação gratuita de 1 mês ou comprar um plano do Microsoft 365.

Criar um projeto com funções personalizadas

Para começar, você criará o projeto de código para criar o suplemento de função personalizada. O gerador Yeoman para suplementos do Office configura seu projeto com algumas funções personalizadas predefinidas que você pode experimentar. Se você executou a inicialização rápida de funções personalizadas e gerou um projeto, continue usando o projeto e pule para esta etapa.

Observação

Se você recriar o projeto do Yo Office, poderá receber um erro porque o cache do Office já tem uma instância de uma função com o mesmo nome. Você pode impedir isso limpando o cache Office antes de executar npm run start.

  1. Execute o comando a seguir para criar um projeto de suplemento usando o gerador Yeoman. Uma pasta que contém o projeto será adicionada ao diretório atual.

    yo office

    Observação

    Ao executar o comando yo office, você receberá informações sobre as políticas de coleta de dados de Yeoman e as ferramentas da CLI do suplemento do Office. Use as informações fornecidas para responder às solicitações como achar melhor.

    Quando solicitado, forneça as informações a seguir para criar seu projeto de suplemento.

    • Escolha um tipo de projeto:Excel Custom Functions using a Shared Runtime
    • Escolha um tipo de script:JavaScript
    • O que você deseja nomear seu suplemento?My custom functions add-in

    A interface da linha de comando do gerador de suplementos do Yeoman Office solicita projetos de funções personalizadas.

    O gerador Yeoman criará os arquivos do projeto e instalará os componentes Node de suporte.

    Observação

    Se você estiver usando Node.js versão 20.0.0 ou posterior, poderá ver um aviso quando o gerador executar a instalação de que você tem um mecanismo sem suporte. Estamos trabalhando em uma correção para isso. Enquanto isso, o aviso não afeta o gerador ou o projeto que você gera, portanto, ele pode ser ignorado.

    Dica

    Você pode ignorar as orientações da próximas etapas fornecidas pelo gerador Yeoman após a criação do projeto de suplemento. As instruções passo a passo deste artigo fornecem todas as orientações necessárias para concluir este tutorial.

  2. Navegue até a pasta raiz do projeto.

    cd "My custom functions add-in"

  3. Compile o projeto.

    npm run build

    Observação

    Os Suplementos do Office devem usar HTTPS, e não HTTP, mesmo durante o desenvolvimento. Se você for solicitado a instalar um certificado após executar npm run build, aceite a solicitação para instalar o certificado que o gerador do Yeoman fornecer.

  4. Inicie o servidor local da web, que é executado no Node.js. Você pode experimentar o suplemento de função personalizada no Excel.

Para testar o seu suplemento no Excel para Windows ou Mac, execute o seguinte comando. Quando você executa este comando, o servidor Web local iniciará e o Excel abrirá com o seu suplemento carregado.

npm run start:desktop

Observação

Os suplementos do Office devem usar HTTPS, não HTTP, mesmo enquanto você estiver desenvolvendo. Se você for solicitado a instalar um certificado depois de executar um dos comandos a seguir, aceite o prompt para instalar o certificado que o gerador Yeoman fornece. Você também pode executar o prompt de comando ou terminal como administrador para que as alterações sejam feitas.

Experimente uma função personalizada predefinida

O projeto de funções personalizadas criado contém algumas funções personalizadas predefinidas configuradas no arquivo src/functions/functions.js. O arquivo ./manifest.xml especifica que todas as funções personalizadas pertencem a CONTOSO namespace. Você usará o namespace CONTOSO para acessar as funções personalizadas no Excel.

Experimentar a função personalizada ADD preenchendo as seguintes etapas no Excel.

  1. No Excel, vá para qualquer célula e digite =CONTOSO. Observe que o menu de preenchimento automático mostra a lista de todas as funções na CONTOSO namespace.

  2. Executar a CONTOSO.ADD função, com números 10 e 200 como parâmetros de entrada, especificando o valor =CONTOSO.ADD(10,200) na célula e pressionando enter.

As ADD função personalizada calcula a soma dos dois números que você forneceu e retorna o resultado da 210.

Se o namespace CONTOSO não estiver disponível no menu de preenchimento automático, siga as etapas a seguir para registrar o suplemento no Excel.

  1. No Excel , selecione>Suplementos internos e selecione Mais Suplementos.

  2. Na guia MEUS SUPLEMENTOS , selecione Gerenciar Meus Suplementos e escolha Carregar Meu Suplemento.

  3. Escolha Procurar... e navegue até o diretório raiz do projeto criado pelo gerador Yeoman.

  4. Selecione o arquivo manifest. XML e escolha abrir, escolha Carregar.

  5. Agora, vamos experimentar a nova função. Na célula B1, digite o texto =CONTOSO. GETSTARCOUNT("OfficeDev", "Excel-Custom-Functions") e pressione Enter. Você deve ver que o resultado na célula B1 é o número atual de estrelas fornecido para o repositório do GitHub de funções personalizadas do Excel.

Observação

Consulte a seção Solução de problemas deste artigo se você encontrar erros ao carregar o suplemento.

Criar uma função personalizada que solicita dados da web

Integração de dados da Web é uma ótima maneira de ampliar o Excel por meio de funções personalizadas. Em seguida, você criará uma função personalizada chamada getStarCount que mostra quantas estrelas um determinado repositório do GitHub tem.

  1. No projeto O meu suplemento de funções personalizadas localize o arquivo ./src/functions/functions.js e abra-o no editor de código.

  2. Em function.js, adicione o código a seguir.

    /**
  * 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. Execute o seguinte comando para recriar o projeto.

    npm run build

  4. Execute as etapas a seguir (para o Excel na Web, Windows ou Mac) para registrá-lo novamente no Excel. Você deve concluir essas etapas antes que a nova função esteja disponível.

  1. Feche o Excel e abra-o novamente.

  2. Na faixa de opções do Excel , selecione>Suplementos internos.

  3. Na seção Suplementos do Desenvolvedor , selecione Meu suplemento de funções personalizadas para registrá-lo .

    A caixa de diálogo Meus Suplementos que mostra suplementos ativos, com o botão suplemento Minha função personalizada realçado.

  4. Agora, vamos experimentar a nova função. Na célula B1, digite o texto =CONTOSO. GETSTARCOUNT("OfficeDev", "Office-Add-in-Samples") e pressione Enter. Você deve ver que o resultado na célula B1 é o número atual de estrelas fornecidas ao repositório Office-Add-in-Samples.

Observação

Consulte a seção Solução de problemas deste artigo se você encontrar erros ao carregar o suplemento.

Criar uma função personalizada assíncrona de streaming

A função getStarCount retorna o número de estrelas que um repositório tem em um determinado momento. As funções personalizadas também retornam dados que estão sendo alterados continuamente. Essas funções são chamadas de funções de streaming. Elas devem incluir um parâmetro invocation que se refere à célula que chamou a função. O parâmetro invocation é usado para atualizar o conteúdo da célula a qualquer momento.

No exemplo de código a seguir, você perceberá que há duas funções, currentTime e clock. A função currentTime é uma função estática que não usa streaming. Ele retorna a data como uma cadeia de caracteres. A função clock usa a função currentTime para fornecer o novo horário a cada segundo a uma célula no Excel. Ela usa invocation.setResult para fornecer o horário para a célula do Excel e invocation.onCanceled para controlar o que acontece quando a função é cancelada.

O projeto Meus suplementos de funções personalizadas já contém as duas funções a seguir no arquivo ./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);
  };
}

Para experimentar as funções, digite o texto =CONTOSO. CLOCK() na célula C1 e pressione Enter. Você deverá ver a data atual, que transmite uma atualização a cada segundo. Embora esse relógio seja um cronômetro em um loop, você pode usar a mesma ideia para definir um cronômetro em funções mais complexas que fazem solicitações da Web para dados em tempo real.

Solução de problemas

Você pode encontrar problemas se executar o tutorial várias vezes. Se o Office cache já tiver uma instância de uma função com o mesmo nome, o seu complemento obtém um erro quando ele é sideload.

Você pode evitar esse conflito desmarcando o cache do Office antes de executar npm run start. Se o processo npm já estiver em execução, insira npm stop, desmarque o cache do Office e reinicie o npm.

Uma mensagem de erro Excel intitulada

Próximas etapas

Parabéns! Neste tutorial, você criou um novo projeto de funções personalizadas, experimentou uma função predefinida, criou uma função personalizada que solicita dados da Web e criou uma função personalizada que transmite dados. Em seguida, saiba como compartilhar dados de função personalizados com o painel de tarefas.