Sending excel file generated using EPPlus as a response in Web API

I created a web service using ASP.NET Web API to allow users to export data from a SharePoint list into an excel. I chose EPPlus .NET library to generate the excel file as it is one of the most comprehensive libraries out there in reading/writing excel files.

I initially created and tested my code in a console application by saving the excel in the file system. The excel opened fine with all the data. I ported the same code to a web API service and returned the file as HttpResponseMessage as shown below –

string contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
using (MemoryStream excelStream = export.ExportViewAsExcel(siteUrl, listName))
	//set stream position to zero
	excelStream.Position = 0;

	// processing the stream.
	var result = new HttpResponseMessage(HttpStatusCode.OK)
		Content = new ByteArrayContent(excelStream.GetBuffer())
	result.Content.Headers.ContentDisposition =
		new System.Net.Http.Headers.ContentDispositionHeaderValue("attachment")
			FileName = "export.xlsx";
	result.Content.Headers.ContentType =
		new MediaTypeHeaderValue(contentType);

	return result;

Once I opened the downloaded excel I got the below warning –

We found a problem with some content in ‘export.xlsx’. Do you want us to try to recover as much as we can? If you trust the source of this workbook, click Yes.


After spending ridiculously large amount of time in figuring this out I finally found that I just had to set the “content-length” header to avoid the warning as below –

result.Content.Headers.Add("content-length", excelStream.Length.ToString());

Hope this saves someone’s time!

Approve workflow tasks using REST API in SharePoint 2013

Recently I had a requirement to approve a workflow task programmatically. I found some useful posts here and here but they were related to SharePoint 2010 and the code did not work with SharePoint 2013 as it is.

After doing some modifications the below code worked fine and the workflow moved ahead after the task was approved.

//Some times outcome field's internal name could be different
//Go to list settings of your Workflow Tasks to verify the internal names of all the fields below
var item = {
	"__metadata": { "type": "SP.Data.Workflow_x0020_TasksListItem" },
	"WorkflowOutcome" : "Approved",
	"Status" : "Approved",
	"PercentComplete" : 1,
	"Checkmark" : "Yes"

//URL here is the direct uri to the item which can be obtained by doing a GET request for the item and reading data.__metadata.uri from the response
	url: "http://yoursite/PG/_api/Web/Lists(guid'3c871781-7c22-4bd2-8b2c-310babe69ccd')/Items(3)",
	type: "POST",
	contentType: "application/json;odata=verbose",
	data: JSON.stringify(item),
	headers: {
		"Accept": "application/json;odata=verbose",
		"X-RequestDigest": $("#__REQUESTDIGEST").val(),
		"X-HTTP-Method": "MERGE",
		"If-Match": "*"
	success: function (data) {
	error: function (data) {

SharePoint 2010 – Check if user in a specific group using Client Object Model

This post is just a modification of the solution provided by Vadim Gremyachev for SharePoint 2013 here. I’ve modified the code to work with SharePoint 2010 (since getByName() is not available in SP 2010)

function IsCurrentUserMemberOfGroup(groupName, OnComplete) {
    var context = new SP.ClientContext.get_current();
    var currentWeb = context.get_web();
    var currentUser = context.get_web().get_currentUser();
    var allGroups = currentWeb.get_siteGroups();
       function(sender, args){
          var count = allGroups.get_count();
          for(i = 0; i < count; i++){
             var grp = allGroups.getItemAtIndex(i);
             //provide your group name
             if(grp.get_loginName() == groupName){
                var groupUsers = grp.get_users();
                //load users of the group
                   function(sender2, args2){
                      var userInGroup = false;
                      var groupUserEnumerator = groupUsers.getEnumerator();
                      while (groupUserEnumerator.moveNext()) {
                         var groupUser = groupUserEnumerator.get_current();
                         //check if current user is in the group members
                         if (groupUser.get_id() == currentUser.get_id()) {
                            userInGroup = true;
                   function onFailureGrp(sender2, args2){
       function onFailure(sender, args){

And this is how you the function should be invoked –

function IsCurrentUserAManager() 
			IsCurrentUserMemberOfGroup("Managers", function (isCurrentUserInGroup) {
				    //user in group		    

People picker not resolving entities in SharePoint 2010

In one of the applications I was working lately, one usual day I was informed that the people picker suddenly stopped working across the site. It sounded very weird at first. After reviewing all the changes that were done recently in the site I found that the custom master page the application was using was updated to support IE9 compatibility which was breaking all the people picker controls in all the pages. So changing back it to IE8 fixed the issue.

Not compatible –

<meta http-equiv=”X-UA-Compatible” content=”IE=9″/>

Change it to –

<meta http-equiv=”X-UA-Compatible” content=”IE=8″/>

“The Subscription Settings service and corresponding application and proxy needs to be running in order to make changes to these settings” – even after configuring subscription Settings Service

I completed configuring my SharePoint 2013 box yesterday and with at most curiosity of creating my first app I opened Visual Studio 2012 and create a SharePoint hosted app. And I was greeted with my first error message on SharePoint 2013.

Apps are disabled on the site‘.

I then learnt that the ‘App Management Service’ and the ‘Microsoft SharePoint Foundation Subscription Settings Service’ should be up and running before I can configure the App URLs in Central Administration and deploying apps.

(Note: You must create a separate app domain for deploying your apps before all these which I have done already following this article)

The former is created automatically while installing SP 2013 but to get the later running we have to do it manually by creating a service application. This can be done through the powershell –

add-pssnapin </code><code>"Microsoft.Sharepoint.Powershell"
$manaccount = Get-SPManagedAccount nlv\Administrator
Remove-SPServiceApplicationPool -Identity SettingsServiceAppPool
$appPoolService = New-SPServiceApplicationPool -Name SettingsServiceAppPool -Account $manaccount
$appService = New-SPSubscriptionSettingsServiceApplication -ApplicationPool $appPoolService -Name SettingsServiceApp -DatabaseName SettingsServiceDB
$proxyService = New-SPSubscriptionSettingsServiceApplicationProxy -ServiceApplication $appService
So now I see that both the services are up and running.

So again I navigated to Apps -> Configure Apps URLs in CA and again daang! Same message –

‘The Subscription Settings service and corresponding application and proxy needs to be running in order to make changes to these settings’

I was googling for an hour and found everywhere that only these two service applications have to be started to configure app URLs. And finally I found the silly step which has to be done after creating subscription settings service application given by chaks here.

You have to do an IISRESET and bingo! Now you will able to configure App URLs in CA.


Opening a SharePoint list item without the navigations in a page using ‘IsDlg’

When you are working with custom master pages with a customized left navigation (done with SP designer) you would have noticed that no matter how many times you set it as “Custom Master Page” and “Default Master Page” in designer the list forms in the site will open with its own left navigation. This is because the list forms will make use of the application master page which will be residing in the Layouts folder and not the default or custom master pages.

In one of my recent projects I had a mail notification feature for a list which will send a mail when a new item is added. The mail will be sent with a link to the item. So I build the URL as http://app/sites/site2/Lists/Invoices/Dispform.aspx?ID=5 which points to the item with the ID as 5 in the list Invoices.

But opening the link as such will show the list item with the old left navigation which we don’t want. So when I checked out the URL opened by SharePoint when “View item” is clicked in the ribbon I found that it opens with a query parameter IsDlg in the URL which mentions whether the item is opened in a dialog. But unfortunately when I tried the same parameter as IsDlg=1 in the link the item opened fine with the left navigation hidden but the scrollbars disabled. Arrgh. Now the SP madness begins. But before I plucked all my hair out I luckily found this brilliant answer by XueQi which mentions that we can either use IsDlg=0 or IsDlg=2 for the scrollbars to work fine.

And the expedition continues.

Filtering list items in SharePoint based on current user and current user groups

Of late, I was working on a list view where I’ve to filter the items based on the current user and the current user groups. Filtering the items by the current user is a straight forward approach where you can select the user field and set the value to [Me]. But there is no provision in the list view filter page where you can filter the items based on the group specified in the field.

But with the help of designer you can manually edit the CAML of the XSLT list viewer webpart in the view page and use the Membership element to filter based on the group. All you have to do is to use CurrentUserGroups as the membership type to show that particular item for the users in that specified group.

  <Membership Type=\"CurrentUserGroups\">
    <FieldRef Name=\"AssignedTo\"/>
    <FieldRef Name=\"AssignedTo\"></FieldRef>
    <Value Type=\"Integer\">

Let’s consider that an item has the filed value as “Kate, John; Approvers”. The item will be shown to the user John kate as well as the members of the group ‘Approvers’. Remember that you have to set the people or group field to allow multiple values as well as selecting objects from SharePoint groups to hold such a value.