Partilhar 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 transferir e instalar a versão certa para o seu sistema operativo.

  • 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 ainda não tiver o Office, poderá qualificar-se para uma subscrição de programador Microsoft 365 E5 através do Programa para Programadores do Microsoft 365. Para obter detalhes, consulte as FAQ. Em alternativa, pode inscrever-se numa avaliação gratuita de um 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 recriar o projeto Yo Office, poderá obter um erro porque a 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. Será adicionada uma pasta que contém o projeto 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
    • Qual é o nome do seu suplemento?My custom functions add-in

    A interface de linha de comandos do Gerador de suplementos yeoman do Office pede projetos de funções personalizadas.

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

  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 utilizar HTTPS e não HTTP, mesmo quando estiver a desenvolver. Se lhe for pedido para instalar um certificado depois de executar um dos seguintes comandos, aceite o pedido 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.

  • Se esta for a primeira vez que desenvolve um Suplemento do Office no seu computador, poderá ser-lhe pedido na linha de comandos para conceder ao Microsoft Edge WebView uma isenção de loopback ("Permitir loopback localhost para o Microsoft Edge WebView?"). Quando lhe for pedido, introduza Y para permitir a isenção. Tenha em atenção que precisará de privilégios de administrador para permitir a isenção. Uma vez permitido, não lhe deverá ser pedida uma isenção quando carregar os Suplementos do Office no futuro (a menos que remova a isenção do seu computador). Para saber mais, consulte "Não é possível abrir este suplemento a partir do localhost" ao carregar um Suplemento do Office ou ao utilizar o Fiddler.

    A linha de comandos na linha de comandos para permitir ao Microsoft Edge WebView uma isenção de loopback.

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 Base e, em seguida, selecione Mais Suplementos.

  2. No separador OS MEUS SUPLEMENTOS , selecione Gerir Os Meus Suplementos e escolha Carregar o 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

Veja a secção Resolução de problemas deste artigo se encontrar erros ao fazer sideload do 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. No friso do Excel , selecione>Suplementos Base.

  3. Na secção Suplementos para Programadores , selecione o suplemento As minhas funções personalizadas para registá-lo .

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

  4. Agora, vamos experimentar a nova função. Na célula B1, escreva o texto =CONTOSO. GETSTARCOUNT("OfficeDev", "Office-Add-in-Samples") e prima Enter. Deverá ver que o resultado na célula B1 é o número atual de estrelas atribuídas ao repositório Office-Add-in-Samples.

Observação

Veja a secção Resolução de problemas deste artigo se encontrar erros ao fazer sideload do 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

Poderá deparar-se com 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.

Pode evitar este conflito ao limpar a cache do Office antes de executar npm run start. Se o seu processo npm já estiver em execução, introduza npm stop, limpe a cache do Office e, em seguida, 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 Partilhar dados de funções personalizadas com o painel de tarefas.