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
$.ajax({
	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) {
		debugger;
	},
	error: function (data) {
		debugger;
	}
});

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' ">
   <AppConfig>App.Release.config</AppConfig>
</PropertyGroup>

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    https://spuser.wordpress.com/
// @require      http://code.jquery.com/jquery-latest.js
// @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...
$(document).ready(function(){
    var counter = 0;
    $('table[summary]:last tr').each(function(){ 
        if(counter == 0){
            var th = '<th class="ms-vh2-nofilter">Internal Name</th>';
            $(this).append($(th));
            counter++;
        }
        else{
            var anchor = $(this).find('td').first().children('a');
            var internalName = $(unescape(anchor.attr('href')).split('Field=')).last()[0]
            var clone = $(anchor).clone();
            debugger;
            $(clone).text(internalName);
            td = '<td class="ms-vb2">' + $(clone)[0].outerHTML + '</td>';
            $(this).append($(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) {
 try{
    var context = new SP.ClientContext.get_current();
    var currentWeb = context.get_web();
 
    var currentUser = context.get_web().get_currentUser();
    context.load(currentUser);
 
    var allGroups = currentWeb.get_siteGroups();
    context.load(allGroups);
 
    context.executeQueryAsync(
       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
                context.load(groupUsers);
                context.executeQueryAsync(
                   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;
                            break;
                         }
                      }
                      OnComplete(userInGroup);
                   },
                   function onFailureGrp(sender2, args2){
                      OnComplete(false);
                   }
               );
            }
         }
       },
       function onFailure(sender, args){
          OnComplete(false);
       }
    );
 }
 catch(e){
    OnComplete(false);
 }
}

And this is how you the function should be invoked –

function IsCurrentUserAManager() 
{
		try{
			IsCurrentUserMemberOfGroup("Managers", function (isCurrentUserInGroup) {
				if(!isCurrentUserInGroup)
				{
				    //user in group		    
				}
			});
		}
		catch(e){			
		}		
}