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) {

Console application – Change app.config based on build configuration

In many cases while writing a console application, we maintain all our application properties in app.config file which is the default configuration file added by Visual Studio IDE. Most of the times we keep all our environment related settings in this file which could vary from DEV to staging to PRD.

So when we try to release a build, we end up changing these values manually every time. Instead, follow the below simple steps by which you can target different app configuration file for the different build configurations in your project.

  1. Unload your project from your solution
  2. Right click the project node and edit .csproj file
  3. Search for the last PropertyGroup node and add the below node after that
<PropertyGroup Condition=" '$(Configuration)|$(Platform)' == 'Release|x86' ">

The above element maps App.Release.Config as the default configuration file when publish a build in Release mode for x86 environments. If you want to targetAny CPU you can use the value AnyCPU instead of x86. You can add multiple nodes similar to this to target multiple build configurations and environments.

Show internal names of SharePoint List fields in List Settings page

This works only in chrome and you need tapermonkey extension for this to work.

Create a new script in tapermonkey with the below script –

// ==UserScript==
// @name         Internal Names
// @namespace
// @require
// @version      0.1
// @description  try to take over the world!
// @author       You
// @include        */_layouts/15/listedit.aspx?*
// @grant        none
// ==/UserScript==
/* jshint -W097 */
'use strict';

// Your code here...
    var counter = 0;
    $('table[summary]:last tr').each(function(){ 
        if(counter == 0){
            var th = '<th class="ms-vh2-nofilter">Internal Name</th>';
            var anchor = $(this).find('td').first().children('a');
            var internalName = $(unescape(anchor.attr('href')).split('Field=')).last()[0]
            var clone = $(anchor).clone();
            td = '<td class="ms-vb2">' + $(clone)[0].outerHTML + '</td>';

The above code adds a new column in the list columns table.

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 &lt; 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.