Excel Services es o no es un WEB Service. May 31, 2010
Posted by juanpablo1manrique in BI, Business Inteligent, Excel 2007, Excel Services, SharePoint, WebPart.Tags: EXCELSERVICES
add a comment
Pues si y no, empecemos con el NO
Excel Services tiene gran cantidad de funcionalidades bastante interesante las cuales permiten que por medio de Presentación de archivos de Excel utilizando ExcelWEBAccess, tener en un ambiente web funcionalidades interesantes como búsquedas, presentación de zonas definidas utilizando DefineNames, permite cálculos de formato y cálculos entre celdas, etc.
Ahora el Si, (WebService).
Pero igualmente podemos utilizar el WEBService el cual expone varios metodos WEB para realizar modificaciones sobre archivos de Excel que se encuentren en una librería de documentos de SharePoint.
La dirección de acceso de este Web Service es http://server/_vti_bin/excelservice.asmx?WSDL
El cual expone los siguientes métodos.
Methods
Calculate ( sessionId As string , sheetName As string , rangeCoordinates As RangeCoordinates ) As ArrayOfStatus
CalculateA1 ( sessionId As string , sheetName As string , rangeName As string ) As ArrayOfStatus
CalculateWorkbook ( sessionId As string , calculateType As CalculateType ) As ArrayOfStatus
CancelRequest ( sessionId As string ) As ArrayOfStatus
CloseWorkbook ( sessionId As string ) As ArrayOfStatus
GetApiVersion ( ) As string
GetCell ( sessionId As string , sheetName As string , row As int , column As int , formatted As boolean ) As ArrayOfStatus
GetCellA1 ( sessionId As string , sheetName As string , rangeName As string , formatted As boolean ) As ArrayOfStatus
GetRange ( sessionId As string , sheetName As string , rangeCoordinates As RangeCoordinates , formatted As boolean ) As ArrayOfAnyType
GetRangeA1 ( sessionId As string , sheetName As string , rangeName As string , formatted As boolean ) As ArrayOfAnyType
GetSessionInformation ( sessionId As string ) As string
GetWorkbook ( sessionId As string , workbookType As WorkbookType ) As base64Binary
OpenWorkbook ( workbookPath As string , uiCultureName As string , dataCultureName As string ) As string
Refresh ( sessionId As string , connectionName As string ) As ArrayOfStatus
SetCell ( sessionId As string , sheetName As string , row As int , column As int , cellValue As ) As ArrayOfStatus
SetCellA1 ( sessionId As string , sheetName As string , rangeName As string , cellValue As ) As ArrayOfStatus
SetRange ( sessionId As string , sheetName As string , rangeCoordinates As RangeCoordinates , rangeValues As ArrayOfAnyType ) As ArrayOfStatus
SetRangeA1 ( sessionId As string , sheetName As string , rangeName As string , rangeValues As ArrayOfAnyType ) As ArrayOfStatus
Para consultar una celda pueden utilizar el siguiente código
Private Sub _GetCell()
Dim sessionId As String = Nothing
Dim cellValue As Object = Nothing
Dim objExcelService As New shphost.ExcelService()
Dim status As shphost.Status() = Nothing
Try
objExcelService.UseDefaultCredentials = True
objExcelService.SoapVersion = Services.Protocols.SoapProtocolVersion.Soap12
sessionId = objExcelService.OpenWorkbook(_workbookPath, “en-US”, “en-US”, status)
‘ Retrieve the data from the referenced cell.
cellValue = objExcelService.GetCell(sessionId, _sheetName, _row, _column, True, status)
_cell = cellValue
Catch ex As SoapException
Dim objLog As New EventViewerLog
objLog.CreateLogSharePointComponents(“An error has occurred.SOAPException” & ex.Message)
_cell = “An error has occurred.” & ex.Message
Catch ex As System.Exception
Dim objLog As New EventViewerLog
objLog.CreateLogSharePointComponents(“An error has occurred.Normal Exception” & ex.Message)
_cell = “An error has occurred.” & ex.Message
Finally
Try
If Not String.IsNullOrEmpty(sessionId) Then
objExcelService.CloseWorkbook(sessionId)
End If
Catch ex As Exception
Dim objLog As New EventViewerLog
objLog.CreateLogSharePointComponents(“An error has occurred.Finally Exception” & ex.Message)
End Try
End Try
End Sub
Failed to download excel services chart March 19, 2010
Posted by juanpablo1manrique in Excel Services, Graficas, Graphics.Tags: EXCELSERVICES
add a comment
Excel services es una maravilla para mostrar reportes y gráficas. Hace algunos días habia publicado algunas gráficas y hoy al inicio del día al intentar ingresar a la gráfica salio un error.
Failed to download excel services chart
Para arreglar este error es necesario ingresar a la configuración de excel services en el ShareService Provider y en las opciones de
Memory Cache Threshold
Maximum Unused Object Age
Mover los settings al maximo posible. Se debe tener en cuenta que el servidor donde se este trabajando tenga buena RAM y buen procesador o seguramente se sufrirán problemas de Performace.
Saludos