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/