I have used the following code to retrieve values from a excel spreadsheet (SomeBook.xlsx), which has been published in the sharepoint site from excel services. The documnet library :/sites/ExcelServices/SpreadSheets/">http://<servername>:<port number>/sites/ExcelServices/SpreadSheets/ has been added in the trusted file location and I am able to view the excel from browser also. fyi somebook.xlsx is populated from a custom list residing in the same site as the excel document library using .net code (not using .iqy).
Code:
ExcelService
excelService = new ExcelService();excelService.SoapVersion = SoapProtocolVersion.Soap12;
excelService.PreAuthenticate =
true; System.Net.NetworkCredential credentials = new System.Net.NetworkCredential("username", "password", "domain");excelService.Credentials = credentials; Status[] status = null;
string SessionId = null;string pathWorkbook = "http://<server name>:<port number>/sites/ExcelServices/SpreadSheets/SomeBook.xlsx";
SessionId = excelService.OpenWorkbook(pathWorkbook,
String.Empty, String.Empty, out status);
Now every time the SessionId is returned as null and i get the following error:
You do not have permissions to open this file on Excel Services
at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet) at System.Security.CodeAccessPermission.Demand() at System.Net.HttpWebRequest..ctor(Uri uri, ServicePoint servicePoint) at System.Net.HttpRequestCreator.Create(Uri Uri) at System.Net.WebRequest.Create(Uri requestUri, Boolean useUriBase) at System.Net.WebRequest.Create(Uri requestUri) at System.Web.Services.Protocols.WebClientProtocol.GetWebRequest(Uri uri) at System.Web.Services.Protocols.HttpWebClientProtocol.GetWebRequest(Uri uri) at System.Web.Services.Protocols.SoapHttpClientProtocol.GetWebRequest(Uri uri) at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters) at ExcelServices.ExcelWebService.ExcelService.OpenWorkbook(String workbookPath, String uiCultureName, String dataCultureName, Status[]& status) at ExcelServices.UCExcelService.GetData_Click(Object sender, EventArgs e)
Do you have any suggestions? I have also tried with excelService.Credentials = System.Net.CredentialCache.DefaultCredentials but no success.
Thanks!!
Poonam