1

I host my site in asphostportal and I am trying to build a Java clinet that connects to Sharepoint (mysite.sharepoint.com) and grabs an excel file. I used the Excel services WSDL file, however the ExcelServices does not provide a Credential property to authenticate unlike its .Net counter part.

I am even trying to figure out the REST API for accessing the excel file. All i need is some why for my java clinet to use the API (either soap or rest) and grab specific cell data from the excel to my java web application.

I have been looking for a clean solution but for the life of me I couldn't find it ...

Any help is greatly appreciate it

1

Since Excel Services in SharePoint 2013/Online supports supports OData, the following examples demonstrate how to retrieve table content:

  • https://contoso.sharepoint.com/_vti_bin/ExcelRest.aspx/Documents/ciscoexpo.xlsx/OData/CiscoSalesTable get all table data

  • https://contoso.sharepoint.com/_vti_bin/ExcelRest.aspx/Documents/ciscoexpo.xlsx/OData/CiscoSalesTable?$filter=Sales ge 100 get specific rows

Prerequisite: excel file have to be prepared as explained in Prepare excel data section

Follow Requesting Excel workbook data from SharePoint Server using OData for a more details.

Prepare excel data

Assume the following excel file

enter image description here

that contains the following items in the workbook:

Before uploading file into SharePoint library, go to File -> Browse View Options -> choose Items in the Workbook as demonstrated below. Then save file and upload it into SharePoint Documents library.

enter image description here

How to read excel data via Excel Services 2010 REST API

I don't have any Java examples that demonstrates how to consume Excel Services, but you could consider the following example in C# as a starting point:

using System.Net;

namespace SharePoint.Client.Office
{
    public class ExcelClient : WebClient
    {

        public ExcelClient(string webUrl, ICredentials credentials)
        {
            BaseAddress = webUrl;
            Credentials =  credentials;
            Headers.Add("X-FORMS_BASED_AUTH_ACCEPTED", "f");
        }

        public string ReadTable(string libraryName, string fileName, string tableName,string formatType)
        {
            var endpointUrl = BaseAddress + string.Format("/_vti_bin/ExcelRest.aspx/{0}/{1}/OData/{2}", libraryName, fileName, tableName);
            return DownloadString(endpointUrl);
        }

    }
}

Usage

var credentials = GetCredentials(webUrl,username,password);
var client = new ExcelClient(webUrl, credentials);
var tableData = client.ReadTable("Documents","ciscoexpo.xlsx", "CiscoSalesTable");

where

private static SharePointOnlineCredentials GetCredentials(Uri webUri, string userName, string password)
{
    var securePassword = new SecureString();
    foreach (var ch in password) securePassword.AppendChar(ch);
    return new SharePointOnlineCredentials(userName, securePassword);
}

References

Excel Services REST API

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.