Código fuente                          

En los post anteriores hemos preparado el material suficiente como para obtener información de una lista de Sharepoint, en éste vamos a insertar esa información en una hoja de cálculo.

La idea es preparar 2 hojas:

• Una inicial (en este caso es una hoja vacía pero bien puede no serlo como para preparar formatos, generar gráficos, etc.)
• Una hoja formateada con 2 registros falsos

El "código diferencia" que nos dará la herramienta vía reflexión de las 2 hojas significa: el código C# .Net necesario como para abrir la hoja 1 y transformarla en la hoja 2.

La idea es ver el código generado para producir esos 2 registros y transformarlo como para introducir tantos registros como los que me devuelve la consulta a Sharepoint. Es un truco sí.

Abrimos la Open XML SDK 2.0 Productivity Tool y elegimos la opción Compare Files:

Elegimos ver el "Package Code" y veremos que el SDK utiliza la función ChangePackage para transformar el primer archivo Excel en el segundo.

Obviamente generamos un proyecto C# (una librería) y pegamos allí el código generado.

Todo empieza para nosotros aquí (ChangePackage abre la planilla 1 y luego llama a ChangeParts):

//Todo acá empieza todo

private void ChangeParts()

{

//Stores the referrences to all the parts in a dictionary.

BuildUriPartDictionary();

//Adds new parts or new relationships.

AddParts(); //Aquí se crea la tabla de Shared Strings

//Changes the contents of the specified parts.

ChangeCoreFilePropertiesPart1(((CoreFilePropertiesPart) UriPartDictionary["/docProps/core.xml"]));

//aparente mente nada que hacer

ChangeWorkbookPart1(document.WorkbookPart);

ChangeWorksheetPart3(((WorksheetPart) UriPartDictionary["/xl/worksheets/sheet1.xml"]));

ChangeWorkbookStylesPart1(((WorkbookStylesPart) UriPartDictionary["/xl/styles.xml"]));

}

 

Quienes accedemos a listas de Sharepoint o simplemente bases de datos estamos acostumbrados a un manejo de tipos muy distinto a lo que encontramos en Excel.

No olvidemos que por medio del SDK accedemos directamente al paquete Excel sin la mediación de la aplicación.

En Excel el dato es un formato de un tipo numérico, no existe el tipo de datos fecha ni nada que se le parezca (pueden releer esta frase si quieren: las fechas están almacenadas numéricamente mediante un serial del cual se obtienen años, meses, días, horas, minutos…).

Ergo almacenar un dato implica una conversión a Ole Automation Date. Suena más terrible de lo que es: simplemente hay que ejecutar la función ToOADate y listo.

Algo como esto:

cellToInsert.CellValue = new CellValue((retDt.ToOADate()).ToString());

cellToInsert.DataType = new EnumValue<CellValues>(CellValues.Number);

 

¿Ya pasamos lo de las fechas bien?

OK: mayormente los textos también son números. Son índices de una tabla de textos almacenada en el paquete.

Es una cuestión de performance: si una frase aparece 1000 veces en una hoja se la graba 1 vez y se almacena 1000 veces el índice.

En el ejemplo agregamos los encabezados de la lista al conjunto de Shared Strings (Tal el nombre de la tabla de textos).

Nuestra primera tarea ha sido generar una clase que utiliza la clase generada automáticamente desde la herramienta.

public class Excel : IOfficeApps

{

#region Implementation of IOfficeApps

 

public void TransformDocument(string filePathSource, string filePathTarget, XmlNode records)

{

File.Delete(filePathTarget);

File.Copy(filePathSource, filePathTarget);

var xlsx = new GeneratedClass(records);

xlsx.ChangePackage(filePathTarget);

}

 

#endregion

}

La idea de hacer una implementación de una interfaz (IOfficeApps) es que cambiando de librería pueda generar una tabla dentro de un documento Word como posibilidad alternativa.

En el código de ejemplo hemos agregado una región denominada Funciones de edición de clase generada que incluye las funciones que he agregado al código, mayormente han sido obtenidas de la ayuda del SDK con algunas modificaciones.

Los encabezados de columna han sido agregados uno por uno de este modo:

IdHeaders.Add(InsertSharedStringItem("FirstName", sharedStringTablePart1));

TextHeaders.Add("FirstName");

TextHeaders quedará conteniendo la lista de nombres de columna y eso será reutilizado varias veces, por ejemplo aquí (generación de la tabla de formato):

foreach (string s in TextHeaders)

{

ix++;

tableColumns1.Append(new TableColumn {Id = (uint) ix, Name = s});

}

Aquí volvemos a utilizar el objeto para cargar valores en las celdas, hacemos un Trim de "ows_" porque así es como vienen los datos en cada nodo row del XML que obtenemos de Sharepoint.

Cada fila tiene esta estructura:

<z:row ows_ID="2216" ows_FirstName="Takiko" ows_MiddleName="J." ows_LastName="Collins" ows_EmailAddress="takiko0@adventure-works.com" ows_Phone="987-555-0126" ows_ModifiedDate="2001-07-01 00:00:00" ows_EncodedAbsUrl="http://moss:20001/Lists/Contacts/2216_.000" ows_MetaInfo="2216;#" ows__ModerationStatus="0" ows__Level="1" ows_owshiddenversion="1" ows_UniqueId="2216;#{D5A76A0C-D216-419C-B2FA-B1F5CB3DBEFB}" ows_FSObjType="2216;#0" ows_Created="2010-03-16 20:01:12" ows_FileRef="2216;#Lists/Contacts/2216_.000" xmlns:z="#RowsetSchema" />

 

Las procesamos a todas de este modo:

 

foreach (string s in TextHeaders)

{

var attTmp = row.Attribute(string.Concat("ows_", s));

var ix = TextHeaders.IndexOf(s);

var colLetter = Letras.ElementAt(ix);

double retD = 0;

DateTime retDt = DateTime.Today;

 

strValue = "";

var cellToInsert = InsertCellInWorksheet(colLetter.ToString(), rowIndex,

(WorksheetPart)

UriPartDictionary["/xl/worksheets/sheet1.xml"]);

 

if (attTmp != null)

{

if (double.TryParse(attTmp.Value, out retD))

{

cellToInsert.CellValue = new CellValue(attTmp.Value);

cellToInsert.DataType = new EnumValue<CellValues>(CellValues.Number);

}

else

{

if (DateTime.TryParse(attTmp.Value, out retDt))

{

cellToInsert.CellValue = new CellValue((retDt.ToOADate()).ToString());

cellToInsert.DataType = new EnumValue<CellValues>(CellValues.Number);

}

else

{

cellToInsert.CellValue = new CellValue(attTmp.Value);

cellToInsert.DataType = new EnumValue<CellValues>(CellValues.String);

}

}

}

else

{

cellToInsert.CellValue = new CellValue("");

cellToInsert.DataType = new EnumValue<CellValues>(CellValues.String);

}

}

 

¡Y obtenemos este resultado!

 

Vamos a sumar elementos para poder ejecutar la tarea completa:

1.    En el post anterior Sharepoint Web Services consumidos desde Powershell programamos una líbrería WSClientVB que obtenía registros de una lista de Sharepoint en formato Xml.

2.    En este post aprendemos como insertar ese XML en una planilla Excel preformateada

3.    El siguiente script de Powershell hace el trabajo de obtener los registros vía Sharepoint Web Services y pasárselos al método TransformDocument de nuestra nueva librería.

#Folders (jugando con el formato)

$ParentFolder="M:\Training\Screencasts\Office y Sharepoint\Code"

$SolutionFolder="{0}\OOXML_WS" -f $ParentFolder

$ClientFolder= "{0}\{1}\bin\Debug\{1}.dll" -f $SolutionFolder, "WSClientVB"

$SDKFolder= "{0}\{1}\bin\Debug\{1}.dll" -f $SolutionFolder, "SDK2"

 

$SPClientLib=[System.Reflection.Assembly]::Loadfile($ClientFolder)

$SPSDK2=[System.Reflection.Assembly]::Loadfile($SDKFolder)

 

[System.Xml.XmlNode]$Contactos = [WSClientVB.Reader]::GetRecords("http://moss:20001", "Contacts")

Write-Host $Contactos.InnerXml

[System.Xml.XmlNodeList]$Datos=$Contactos.ChildNodes

$docBuilder = New-Object SDK2.Excel

 

$EmptyPath="{0}\{1}" -f $ParentFolder,"EmptyBook.xlsx"

$AutoPath="{0}\{1}" -f $ParentFolder,"AutoBook.xlsx"

 

$docBuilder.TransformDocument($EmptyPath, $AutoPath, $Datos.Item(1))

Write-Host "Listo...."

Código fuente