Home > Artigo, SharePoint Technologies > Acessando e processando dados externos de planilhas via Excel Services Web Services

Acessando e processando dados externos de planilhas via Excel Services Web Services


Autor:

Thiago Silva

Publicação:

16/Out/10

Overview

Uma das funcionalidades do SharePoint 2010, quando o assunto é BI, é o Excel Services. Juntamente com o PerformancePoint Services e as WebParts de gráficos, eles formam uma grande ferramenta para acompanhamento de resultados e tomadas de decisão.

Essas ferramentas tem um potencial ainda maior quando os conectamos com um Analysis Services para atualizar as informações automaticamente baseando no processamento de cubos OLAP.

Neste artigo, utilizei uma planilha Excel publicada no Excel Services do SharePoint 2010. Esta planilha possui uma conexão de dados com um cubo no Analysis Services para atualizar valores de uma pivot table, e por sua vez, atualizar informações em um gráfico, usando a WebPart de gráfico que está conecta a planilha no Excel Services.

O Excel Services pode atualizar a conexão de dados quando o usuário abre a planilha, ou de tempos em tempos, mas só enquanto o usuário interage com a planilha. Ou seja, é necessário que o usuário matenha uma sessão aberta para que a atualização dos dados seja realizada.

No meu caso, eu preciso atualizar a planilha em runtime, sem interação do usuário, por que o usuário vai ver apenas a WebPart de gráficos (fiz esta conexão da WebPart de gráficos com o Excel Services por que a WebPart não acessa dados do Analysis Services diretamente).

Solução

Para essa solução vou trabalhar com os seguintes produtos e tecnologias:

· Windows 7 Enterprise;

· Microsoft SharePoint Server 2010 Enterprise;

· SQL Server 2008 Enterprise;

Neste exemplo, já tenho as aplicações funcionando. O Excel Services já está rodando a planilha que possui conexão com o cubo no Analysis Services do SQL Server e a WebPart de gráfico, por sua vez, lê as informações da planilha hospedada no Excel Services. Vejam as imagens do ambiente montado:

WebPart de Gráfico acessando planilha no Excel Services:

image

Planilha no Excel Services acessando Analysis Services:

image

Se clicarmos em “Data” e depois em “Refresh All Connections”, a conexão com os dados irá atualizar e trazer os dados atuais da fonte de dados.

image

Mas, no meu caso, preciso que esta atualização seja feita com o arquivo fechado, sem intereção do usuário. Por este, criei um TimeJob (serviço de agendamento de tarefas do SharePoint) para realizar esta operação de tempos em tempos.

Neste artigo vou usar uma WebPart, para simplificar o deploy e “start” da operação usando um botão.

Crie um projeto de Visual Web Part no Visual Studio 2010 para começarmos. Caso preciso de informações de como fazer, acesse: http://www.canalsharepoint.com.br/tutoriais/Tutoriais/SharePoint%202010%20-%20Visual%20WebParts.aspx.

1. Adicionando Excel Services Web Services

1.1. Na Solution Explorer, clique com o botão direito no projeto e selecione Add Service Reference;

image

1.2. Clique em Advanced para abrir a janela de opções avançadas, e depois clique em Add Web Reference;

image

image

1.3. Insira a URL do Excel Services Web Services do seu site, no meu caso: http://notebook01/sites/bi/_vti_bin/excelservice.asmx. Dê o nome de “ExcelServices” e depois, clique em Add Reference.

image

1.4. Adicione o seguinte código na sua WebPart:

Code Snippet
  1. using System;
  2. using System.Web.UI;
  3. using System.Web.UI.WebControls;
  4. using System.Web.UI.WebControls.WebParts;
  5.  
  6. using SharePointWebParts.Demo.ExcelServices;
  7. using Microsoft.SharePoint;
  8.  
  9. namespace SharePointWebParts.Demo.VisualWebPartTeste
  10. {
  11.     public partial class VisualWebPartTesteUserControl : UserControl
  12.     {
  13.         protected void Page_Load(object sender, EventArgs e)
  14.         {
  15.             LiteralControl lc = new LiteralControl();
  16.             lc.Text = “<h3>Clique aqui para atualizar a planilha no Excel Services!</h3>”;
  17.             this.Controls.Add(lc);
  18.  
  19.             Button btnAtualiza = new Button();
  20.             btnAtualiza.Text = “Atualizar Agora!”;
  21.             btnAtualiza.Click += new EventHandler(btnAtualiza_Click);
  22.             this.Controls.Add(btnAtualiza);
  23.         }
  24.  
  25.         void btnAtualiza_Click(object sender, EventArgs e)
  26.         {
  27.             ExcelService es = new ExcelService();
  28.  
  29.             string workbookpath = http://notebook01/sites/bi/documents/Chart.xlsx&#8221;;
  30.             Status[] outStatus;
  31.  
  32.             es.Credentials = System.Net.CredentialCache.DefaultCredentials;
  33.  
  34.             try
  35.             {
  36.                 string sessionid = es.OpenWorkbook(workbookpath, “en-US”, “en-US”, out outStatus);
  37.  
  38.                 es.Refresh(sessionid, “notebook01_sqlexpress SSAS”);
  39.  
  40.                 byte[] contents = es.GetWorkbook(sessionid, WorkbookType.FullWorkbook, out outStatus);
  41.                 
  42.                 using (SPSite site = new SPSite(SPContext.Current.Web.Url))
  43.                 {
  44.                     using (SPWeb web = site.OpenWeb())
  45.                     {
  46.                         web.AllowUnsafeUpdates = true;
  47.  
  48.                         web.Files.Add(workbookpath, contents, true);
  49.  
  50.                         web.AllowUnsafeUpdates = false;
  51.                     }
  52.                 }
  53.  
  54.                 es.CloseWorkbook(sessionid);
  55.             }
  56.             catch (Exception ex)
  57.             {
  58.                 Label lblmensagem = new Label();
  59.                 lblmensagem.Text = ex.ToString();
  60.                 this.Controls.Add(lblmensagem);
  61.             }
  62.         }
  63.     }
  64. }

 

1.5. Clique em Build e depois em Deploy Solution;

image

1.6. Adicione sua WebPart na página desejada e clique no botão “Atualizar Agora!” para fazer com nosso código acesse o Excel Services via Web Services, abra a planilha em runtime, processe os dados das conexões externas e salve a planilha atualizada.

image

1.7. Se houveram mudanças na fonte de dados, a planilha irá atualizar e, consequentemente, a WebPart de Gráficos também será atualizada.

Veja nas imagens que, no meu caso, as informações mudaram. Assim, atualizando a planilha no Excel Services e, consequentemente a WebPart de Gráficos.

Planilha no Excel Services Atualizada:

image

WebPart de Gráficos após clicar no botão da WebPart para atualizar as informações:

image

Conclusão

Neste artigo mostrei como atualizar conexões de dados externas em planilhas rodando no Excel Services em runtime usando Excel Services Web Services.

Informações adicionais:

https://thiagottss.wordpress.com/2010/10/17/acessando-e-processando-dados-externos-de-planilhas-via-excel-services-web-services-usando-uma-webapplication-e-sharepoint-2010-client-object-model/ – Acessando e processando dados externos de planilhas via Excel Services Web Services usando uma WebApplication e SharePoint 2010 Client Object Model

Advertisements
  1. October 29, 2010 at 18:44

    Olá Thiago muito bom artigo… eu estou tentando fazer o básico, mas não esta indo. Tenho o sharepoint 2010 enterprise, sql 2008 r2 e quero atualizar os dados da planilha que fiz upload para o sharepoint. Esta dando erro:

    Excel Erro ao tentar estabelecer uma conexão com a fonte de dados externa. Falha ao atualizar as seguintes conexões:

    Sessões

    Bom… já verifiquei serviços, logs e ativei o servide de declaração de token do windows … e nada … unica coisa que lembro é de ter escolhido na instalação do sharepoint para usar o modo de logon ntlm (windows authentication) e não kerberos… tem alguma dica ???

    • October 29, 2010 at 19:17

      Olá Fabio,
      tudo bem?

      Este erro está dando quando você clica para atualizar os dados no próprio browser, ou no código? Quando você abre a planilha no Excel (na máquina), e manda atualizar, tudo atualiza normalmente?
      Tente colocar o arquivo de conexão de dados em um biblioteca no SharePoint também, clicando em Propriedades da Conexão no Excel, na tab de definições, você tem a opção de Exportar o arquivo de conexão e colocar em uma biblioteca de Conexões de Dados no SharePoint.

      O SharePoint e seu computador estão no mesmo domínio?

      Abraço!

  2. Fernando
    May 13, 2011 at 20:58

    Olá Thiago, tudo bem?

    Estou atrás da resolução de um problema. Eu quero
    chamar o Web Service do Excel Services de dentro de uma planilha excel cliente (VBA). Até ai tudo bem, o problema é que eu
    não consigo usar o método SetRangeA1 e GetRangeA1. Ele dá a seguinte mensagem de erro: “Excel Services was unable to parse the range request.”.

    A chamada está assim:

    outStatus = objSClient.SetRangeA1(sessionId, “Sheet1”, “Sheet1!Range(“”B3:B5″”)”, Linhas)

    Você saberia me dizer como resolver este problema? Quando eu faço as chamadas em um projeto .NET funciona normalmente.

    Muito obrigado antecipadamente pela sua atenção,
    Fernando.

  3. November 1, 2011 at 10:44

    Bom Dia Tiago,

    Você saberia dizer se essa solução poderia ser desenvolvida como uma solução Sandboxed?

    Obrigado pela atenção,
    Ronaldo Silva.

    • November 3, 2011 at 15:16

      Olá Ronaldo!
      Tudo bem?

      Acredito que não seja possível utilizar chamadas via webservices nas soluções sandboxed. Mas vale a pesquisa.

      Poucos recursos externos a sitecollection são possíveis utilizando sandboxed.

      No momento estou um pouco sem tempo, mas vou pesquisar o assunto e ver temos alguma alternativa.

      Abraço!
      Thiago.

  4. Elton Alves
    July 23, 2012 at 01:55

    Olá Thiago,
    Não tive sucesso… Estou precisando justamente desta solução.. Eu criei uma biblioteca de conexão e tenho um arquivo de conexão nela.. A partir deste arquivo, de configuração, eu gerei o arquivos xlsx com o powerpivot, em conexões coloquei para que seja atualizado sempre que aberto, mas os gráficos não estão sendo atualizados.. preciso configurar alguma outra coisa? Obrigado!

  5. July 23, 2012 at 08:59

    Oi Elton,
    Tudo bem?

    Quando você clica em Dados > Atualizar todas as conexões, o gráfico atualiza?

    • Elton
      July 24, 2012 at 23:18

      Isso Thiago, ele atualiza… na planilha do excel.. ai eu salvo e atualiza o gra’fico da webpart… fiz aquelas configura’coes q vc sugeriu.. tanto no Servico do excel.. ( la’ defini local seguro e etc.. tanto da conexao quanto da planilha ), fiz aquela outra configuracao sugerida de do secure storage, acho que ‘e isso, colocando um usu’ario que usamos para as configuracoes..

      S’o nao consigo encontrar o motivo que nao faz automatico.. se ‘e que ‘e possivel.. na planilha eu tenho com um “group by” q utilizo no powerpivot.. essa informacao que quero montar o grafico..

      quando eu abro a planilha no cliente ele atualiza automatico, mas precisa salvar.. queria q isso fosse automatico.. por acha que ‘e incoveniente o usu’ario ter q entrar toda vez na planilha e salvar…

      tentei customizar uma webpart para atualizar dinamico.. se inseri um item na lista, capturo o evento e pego pelo workbookediting jogo em um byte[], e depois salvo novamente na biblioteca, mas isso eu perco essa atualizacao.. e para de atualizar automatica…

      • July 25, 2012 at 13:34

        Elton,

        Você está usando SharePoint 2010 com PowerPivot? Se sim o data refresh tem um mecanismo pronto.
        http://technet.microsoft.com/en-us/library/gg413467 – Automatically Refresh PowerPivot Data in SharePoint

        Se não está usando… você precisa criar esse mecanismo de update e save, como fiz no post. Primeiro passo é verificar se tudo funciona manualmente, clicando no browser. Se funciona, partimos para a automatização.

  6. Cláudio
    November 4, 2014 at 17:30

    Boa Noite Thiago!

    Tenho um problema que com certeza você já deve ter visto por ai, é o seguinte, tento atualizar uma planilha via browser utilizando uma fonte de dados externa, mas da falha.
    Aparece uma mensagem de erro: Erro ao tentar estabelecer uma conexão com fonte de dados externa.
    O que pode ser isso? Criei o arquivo de conexão, apontando para o servidor de SQL em seguida testei a atualização no modo local e funcionou, só que quando coloco esse arquivo (planilha dinâmica) para ser atualizada via browser da o erro.
    Pode me ajudar, por favor?

    • November 4, 2014 at 20:22

      Oi Cláudio!

      O arquivo de conexão também precisa estar em uma biblioteca do SharePoint. Por padrão, o Excel coloca o arquivo de conexão local, em uma pasta dos “Meus Documentos”.

      Além disso, precisa verificar/configurar se o SharePoint confia em arquivos de conexão de dados armazenados onde você vai salvar o arquivo. Isso você verifica nas Configurações do Excel Services no Central Administration.

      E ainda, dependendo do caso, você precisa configurar Secure Store Services… por exemplo para domínios diferentes, Kerberos e outros.

      É… é um pouco “chato” fazer isso funcionar. Mas, funciona! 😉
      Abraço!
      Thiago.

  7. Cláudio
    November 4, 2014 at 21:21

    Thiago, boa noite!

    Coloquei o arquivo de conexão na biblioteca do sharepoint e configurei Excel service para confiar no local da biblioteca aonde esta o arquivo, só que quando peço para atualizar, aparece uma mensagem de erro dizendo:
    Não sabemos o que aconteceu.
    Tente novamente junto com um ID do erro.
    e ainda não atualiza os dados.

    Eu uso tanto o Excel, SharePoint e o Ms Project Server na versão 2013.

    Agraço!!!

    • November 11, 2014 at 12:41

      Cláudio,

      É provável que você precise configurar contas de acesso no Secure Store Service (e que tenham acesso no local onde está fazendo leitura dos dados). E precisa monitorar os LOGS do SharePoint para identificar onde está o problema.

      Thiago.

  8. Sidney
    June 10, 2015 at 23:33

    Boa noite Thiago.
    Parabens pelo post.
    Gostaria de saber se é possivel desabilitar os botoes “dados e aparencia” e “configuracoes” da webpart.

    Obrigado
    Sidney

  9. Sidney
    June 10, 2015 at 23:39
    • June 22, 2015 at 11:16

      Muito bom! Obrigado por postar, e compartilhar, a resposta.

      Uma dica, se não quiser fazer esse procedimento toda que vez que criar o gráfico, você pode exportar a webpart e importar de novo. Assim, sempre que criar uma nova webpart de gráfico, utilize o modelo que importou. Assim já virá sem os menus.

      Abraço!

  1. October 17, 2010 at 03:20
  2. January 2, 2011 at 22:49

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: