Wednesday, August 12, 2009

Using Excel Service hosted using Sharepoint in .Net Application (C#)

An Excel Sheet is created to do a calculation of ADD.
Cell$G$155 is set to function SUM of the values on Cell$G$145 and Cell$G$150 (Instead of the Cell and Row No, the Defined a Name to the Cell and that can be used)
The Above excel is published to an Excel enabled Sharepoint Library.
The above library url has to be set as trusted Url in the Sharepoint Central Admin Excel Services Settings.
Below is how that published excel can be used for calculation from another application (webpart, Console or anyother .Net Application) using C#.
Add the Web Service Reference of the ExcelService first to the application.

ExcelService oExcelServ = new ExcelService();
oExcelServ.Url = SPContext.Current.Web.Url + "/_vti_bin/ExcelService.asmx";
oExcelServ.Credentials = System.Net.CredentialCache.DefaultCredentials;

Status[] Status = null;
String SessionID = null;
string pathWorkBook = @"http://servername/LibraryName/excelFile.xlsx";
SessionID = oExcelServ.OpenWorkbook(pathWorkBook, "en-US", "en-US", out Status);
int val1, val2;

//EXCELSHEETNAME à The Name of the Sheet in the .xlsx file which is published.
//EXCELSHEETNAME!$G$145 à SheetName!$CellAlphabet$RowNo
Status = oExcelServ.SetCellA1(SessionID, "EXCELSHEETNAME", "EXCELSHEETNAME!$G$145", val1);
Status = oExcelServ.SetCellA1(SessionID, "EXCELSHEETNAME", "EXCELSHEETNAME!$G$150", val2);

//Execute the CalculateWorkbook function to trigger Calculation
Status = oExcelServ.CalculateWorkbook(SessionID, CalculateType.CalculateFull);
object result = null;

// Read the result back from the Cell.
result = oExcelServ.GetCellA1(SessionID, "EXCELSHEETNAME", "EXCELSHEETNAME!$G$155", true, out Status);

No comments: