Unable to retrieve external data for the following connections: The data sources may be unreachable, may not be responding, or may have denied you access

Olá!

Cenário:

Usando o Excel Services, no SharePoint 2007, para visualizar planilhas Excel no browser e conectadas com fontes de dados externas. A planilha carrega no browser tudo certo, mas ao solicitar a atualização das informações “Refresh” na fonte de dados, o SharePoint exibe um erro.

ERRO:

“Unable to retrieve external data for the following connections: The data sources may be unreachable, may not be responding, or may have denied you access. Verify the data refresh is enabled for the trusted file location and that the workbook data authentication is correctly set.”

Todas as configurações no Excel Services foram verificadas e estão corretas, como Trust File Locations e Trust Data Providers.

SOLUÇÃO:

Para solucionar o problema temos um fator muito importante que vai determinar a resolução:

· Servidor SharePoint e DataSources estão no mesmo servidor; ou

· Servidor SharePoint e DataSources são servidores separados.

Neste post vou detalhar a solução do primeiro caso, quando os servidores são o mesmo.

Para solucionar o problema devemos alterar uma configuração do Shared Services Provider para que o Excel Services use o modo Delegation de acesso. Para isso, usamos o seguinte comando usando a ferramenta de gerenciamento do SharePoint, STSADM.

Stsadm –o set-ecssecutiry –accessmodel delegation –ssp NomeSSP

Stsadm –o execadmsvcjobs

Iisreset

NOTA: Esta opção só deve ser executada se o servidor SharePoint e o servidor onde estão os dados (DataSource) forem o mesmo servidor!

Com isso o Excel Services agora consegue encaminhar as credenciais do usuários para a fonte de dados sem problemas, conseguindo assim, atualizar as informações.

Para a outra configuração, onde os servidores SharePoint e Datasources são diferentes, devemos configurar o método de autenticação do SharePoint para Kerberos, e aí as coisas ficam um pouco mais complicadas: http://blogs.msdn.com/b/martinkearn/archive/2007/04/23/configuring-kerberos-for-sharepoint-2007-part-1-base-configuration-for-sharepoint.aspx.

Abraço!

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:

Planilha no Excel Services acessando Analysis Services:

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.

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;

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

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.

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;

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.

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:

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

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

Acessando e processando dados externos de planilhas via Excel Services Web Services usando uma WebApplication e SharePoint 2010 Client Object Model

Olá!

Overview:

Este post mostra como atualizar dados externos de planilhas no Excel Services em runtime, usando uma WebApplication e SharePoint 2010 Client Object Model.

No final deste post, encontre informações de como fazer o mesmo procedimento usando uma WebPart. Além disso, toda a explicação do motivo do código desenvolvido abaixo.

SOLUÇÃO:

Como o post relacionado abaixo explica o cenário de utilização do meu código, vamos direto ao ponto. Bom proveito! J

Code Snippet

1. using System;
2. using System.Collections.Generic;
3. using System.Linq;
4. using System.Web;
5. using System.Web.UI;
6. using System.Web.UI.WebControls;
7.  
8. using System.Web.Services.Protocols;
9. using SampleExcelServiceApplication.ExcelService;
10. using Microsoft.SharePoint.Client;
11. using System.IO;
12.  
13. namespace SampleExcelServiceApplication
14. {
15.     public partial class _Default : System.Web.UI.Page
16.     {
17.         protected void Page_Load(object sender, EventArgs e)
18.         {
19.             ExcelService.ExcelService es = new ExcelService.ExcelService();
20.  
21.             string workbookpath = "http://notebook01/sites/bi/documents/Chart.xlsx&quot;;
22.             Status[] outStatus;
23.  
24.             es.Credentials = System.Net.CredentialCache.DefaultCredentials;
25.  
26.             try
27.             {
28.                 string sessionid = es.OpenWorkbook(workbookpath, "en-US", "en-US", out outStatus);
29.                                 
30.                 es.Refresh(sessionid, "notebook01_sqlexpress SSAS Teste Eventos");
31.  
32.                 byte[] contents = es.GetWorkbook(sessionid, WorkbookType.FullWorkbook, out outStatus);
33.  
34.                 using (ClientContext clientContext = new ClientContext("http://notebook01/sites/bi&quot;))
35.                 {
36.                     Microsoft.SharePoint.Client.File.SaveBinaryDirect(clientContext,"/sites/bi/Documents/Chart.xlsx",new MemoryStream(contents),true);
37.                 }
38.  
39.                 es.CloseWorkbook(sessionid);
40.             }
41.             catch (Exception ex)
42.             {
43.                 Label lblmensagem = new Label();
44.                 lblmensagem.Text = ex.ToString();
45.                 this.Controls.Add(lblmensagem);
46.             }
47.         }
48.     }
49. }

 

Abraço!

Post relacionado: https://thiagottss.wordpress.com/2010/10/17/acessando-e-processando-dados-externos-de-planilhas-via-excel-services-web-services/

Utilizando o Excel Services para exibir um gráfico básico

Overview

Recentemente estive avaliando algumas soluções para gerar gráficos e exibi-los no portal baseado em SharePoint Server. Existem diversas soluções para isso, mas o SharePoint já possui esta funcionalidade graças ao Excel Services.

Neste artigo irei descrever de forma bem simples como podemos publicar um gráfico de uma planilha Excel em nosso portal utilizando o MOSS 2007.

Excel Services nos permite visualizar arquivos Excel dentro do browser. Você pode até configurar parâmetros que os usuários podem modificar. Por exemplo, você pode querer publicar uma planilha que calcula os preços de seus produtos. Você pode ter um parâmetro onde o usuário pode entrar com um valor de desconto e então o preço será modificado. A parte da planilha que pode ser modificada, são os parâmetros que você determinou. Excel Services não foi desenvolvido para edição de planilhas. Ainda há a necessidade de termos o aplicativo do Office, o Excel.

Algumas considerações:

· Excel Services está disponível apenas no MOSS 2007 Enterprise;

· As planilhas somente podem ser publicadas no formato do Office 2007, .xslx;

· A biblioteca de documentos ou relatórios que você vai publicar a planilha deve estar configurada como “Trusted File Location”. Esta tarefa é realizada dentro do Shared Services Provider (SSP). Iremos abordar este passo no artigo.

Tendo isso em mente, vamos nessa!

Solução

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

· Windows Server 2003 R2 com SP2;

· Microsoft Office SharePoint Server 2007 Enterprise com SP2;

· SQL Server 2005 com SP2;

Primeiramente iremos configurar nossa biblioteca de documentos como “Trusted File Location” no Shared Services Provider. (http://technet.microsoft.com/en-us/library/cc261678.aspx)

Adicionando uma biblioteca como Trusted File Location

1. Em Adminstrative Tools, abra o SharePoint Central Administration Web application;

2. No menu à esquerda, na seção Shared Services Administration, clique em SharedServices1 (por padrão, este é o SSP criado com o SharePoint);

3. Na home do Shared Services, na seção Excel Services Settings, clique em Trusted file locations;

4. Na página Trusted File Locations, clique em Add Trusted File Location;

5. Na seção Adress, digite o endereço da biblioteca onde a planilha será armazenada. Como podemos observar três opções: Windows SharePoint Services, UNC e HTTP. Em nosso exemplo, iremos utilizar uma biblioteca dentro do próprio Sharepoint, então use Windows SharePoint Services.

a. Insira o endereço da biblioteca sem o “Forms/AllItems.aspx”. No meu exemplo, utilizei a biblioteca padrão criada na instalação do SharePoint (“http://litwaredemo:2000/Shared%20Documents/”)

6. Para o nosso exemplo, isso é suficiente. Deixe todos os outros campos com o valor padrão, clique em Ok.

Agora crie um gráfico qualquer numa planilha Excel, similar a este abaixo:

Nota: Como nosso intuito é mostrar as funcionalidades do SharePoint, não irei abordar esta parte do Office. Para ajuda, consulte: http://office.microsoft.com/en-us/excel/CH100648161033.aspx.

Publicando o gráfico no SharePoint, utilizando o Excel Services

1. Dentro do Office Excel, no topo à esquerda, clique no botão do Office e então em Publish, depois em Excel Services.

2. Quando a caixa Save As abrir, clique em Excel Services Options;

3. Na tab Show, você pode escolher o que será publicado no Excel Services, neste exemplo, nós só queremos que o gráfico seja mostrado, não precisamos da tabela de onde ele foi gerado.

4. Dê um nome para sua planilha que será postada na biblioteca que você habilitou como Trusted File Location, atente-se a isso. Se você publicar a planilha em uma biblioteca diferente da que você configurou, não irá funcionar.

a. Certifique-se que a opção Open in Excel Services está marcada para que o gráfico abra no browser ao fim do processo. Caso contrário, será necessário mais um passo. Que é criar uma Web Part Excel Viewer e escolher a planilha que você publicou.

Feito isso, já temos o gráfico publicado no Excel Services. Agora como podemos utilizar efetivamente em nosso portal? Simples, agora nós iremos utilizar a Web Part Excel Web Access para exibir onde queremos no nosso portal.

 

Configure no painel de ferramentas e busque a sua planina publicada, depois clique em Ok.

Pronto! Acabamos de publicar e disponibilizar um gráfico usando o Excel Services!

Conclusão

Nesse artigo nós apresentamos como configurar, publicar e disponibilizar um gráfico usando o Excel Services. Esse artigo figurou uma situação muito simples, bem básico. Mas, podemos ir muito além com o recurso, por exemplo, publicar uma planilha que tem um gráfico gerado de uma tabela dinâmica, que, por sua vez, é alimentada por uma conexão com um banco SQL Server.

Assim, as informações sendo atualizadas de tempos em tempos, tornando o nosso gráfico DINÂMICO!! Isso mesmo, com o passar do tempo o gráfico muda a cada refresh de página.

Quem sabe, temos este desafio para um próximo artigo! Até Breve, espero que tenham gostado.