Excel Services: Consulta de datos externos

A finales del mes pasado Birchman y SUGES hicieron un webcast de SharePoint y Project Server en el que se demostraba como integrar ambos productos. (Podéis ver el vídeo en la web de SUGES y aquí).

Uno de los puntos de debate tratados en la presentación, fué la limitación de excel services al recuperar datos de listas de SharePoint. Cuando nuestro Excel tiene un origen de datos apuntando a una lista de SharePoint éste no puede refrescar la conexión, ni de forma automática ni de forma manual. En la parte superior de la pantalla nos sale una barra de detalle que nos dice lo siguiente:

Si nos paramos a observar detenidamente veremos que ésta limitación existe en Excel Services, pero no en Excel (cliente pesado).
Entonces, para solucionar ésta limitación, el workaround utilizado pasa por un código simple que lo único que hace es abrir el Excel en local, con el cliente pesado, refrescar las conexiones, guardarlo y subirlo de nuevo a la librería de SharePoint.
A grandes rasgos, el código sería similar al siguiente:

static void Main(string[] args)
{
SPSecurity.RunWithElevatedPrivileges(delegate()
{
using (SPSite site = new SPSite("http://demo.sugesspps.com/"))
{
//Declaramos los objetos necesarios de Interop.Excel
Application oExcelApp = new Application();
Workbook oWorkBook = null;
Worksheet oSheet = new Worksheet();

//Declaramos los objetos del SP para acceder a la web, coger el SPFile
SPWeb web = site.OpenWeb();
//Accedemos a lista donde esta el excel
SPList list = web.Lists["Excels"];
//Recogemos el fichero
SPListItemCollection excelCollection = list.Items;

foreach (SPListItem excel in excelCollection)
{
SPFile file = excel.File;
//Nos guardamos el nombre del excel
string filename = @"C:\informes\" + file.Name;

//Si existe lo eliminamos
if (System.IO.File.Exists(filename) == true)
{
System.IO.File.Delete(filename);
}

//Leemos el fichero de y lo pasamos al disco duro
byte[] dataSHP = file.OpenBinary();
FileStream fsStoDisk = new FileStream(filename, FileMode.Create);
BinaryWriter writerToDisk = new BinaryWriter(fsStoDisk);
writerToDisk.Write(dataSHP, 0, (int)file.Length);
writerToDisk.Close();
fsStoDisk.Close();

//Accedemos a él y lo abrimos
oWorkBook = oExcelApp.Workbooks.Open(filename, Type.Missing, Type.Missing, Type.Missing, "Icnet2011", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
System.Threading.Thread.Sleep(4000);
//refrescamos las conexiones
oWorkBook.RefreshAll();
System.Threading.Thread.Sleep(10000);
//Guardamos los cambios
oWorkBook.Save();
//Cerramos el fichero
oWorkBook.Close();
oExcelApp = null;
//Ahora pasamos el fichero del disco duro al SHP
byte[] dataDisk = null;
FileStream fsToSHP = new FileStream(filename, FileMode.Open, FileAccess.Read);
BinaryReader brToSHP = new BinaryReader(fsToSHP);
long numBytes = new FileInfo(filename).Length;
dataDisk = brToSHP.ReadBytes((int)numBytes);
//Cargamos el fichero
file.SaveBinary(dataDisk);
}
}
});


}

Puntos a destacar:

• Los Sleep's son para garantizar el tiempo de apertura y refresco de conexiones del Excel. Lo correcto sería utilizar eventos que alertasen de la finalización de apertura de Excel y de la misma forma, con el refresco de conexiones.


• Utiliza la librería Microsoft.Office.Interop.Excel


• Consume una licencia de Office

Opciones de despliegue:

• Aplicación de consola bajo una tarea programada de Windows


• Timer Job de SharePoint


• Event Handler en la lista que hace de fuente de datos del Excel

Una variante muy interesante a ésta es utilizar la librería de Open XML como soporte para realizar las operaciones sobre el Excel. Esta librería es más libiana que la de Interop, como ya sabéis.
Si tenéis alguna variante más, no dudéis en comentar en el blog.