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.

excelerror

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!

Advertisements

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.

Integrating activity feed of a Facebook page in your SharePoint site

For the past couple of days one of my colleagues was trying to integrate the activity feed of our client’s Facebook brand page in the client’s public portal. When I was looking into it I initially tried with the Activity Feed plugin provided in the Facebook plugins page.

Our requirement was to load all the activities that are happening in our Facebook page such as comments, posts, videos, likes etc. But when I was using the activity feed plugin I realized that the activity feed plugin only brings the Facebook page activity done from the site in which it is currently hosted. For example, if you have integrated the activity feed plugin in your site http://demo.yoursite.com/, the plugin only shows the activity that is created from http://demo.yoursite.com. But our intentions was to bring the complete set of activities happening in the Facebook page.

Thankfully I found out the Facebook Like Box which met our requirements. It fetches the completely activity feed of a Facebook page and integrates into your site. You can also comment and like posts from the plugin.

ASP.NET DataGrid – Export to CSV encoding issue

If you are exporting an asp.net grid to the CSV format you might see some special characters appearing the exported file. This is because of not setting the right encoding format. I initially tried with UTF8, UTF32 and Unicode and none of them exported the data correctly. Finally I set the encoding to default like this –

Response.ContentEncoding = Encoding.Default;

Finally the single quotes and hypens were appearing correctly in the CSV. Please not that you have to HtmlDecode your data if you have already HtmlEncoded it.

Microsoft Community Contributor Award 2011

Okay, I’m surprised. I got a mail from Microsoft few hours ago stating that I’ve been awarded “Microsoft Community Contributor Award” for the year 2011. This is the mail (but not the complete one) I got.

Microsot Contributor Award

Dear Raghavendra,

Congratulations! We’re pleased to inform you that your contributions to Microsoft online technical communities have been recognized with the Microsoft Community Contributor Award.

The Microsoft Community Contributor Award is reserved for participants who have made notable contributions in Microsoft online community forums such as TechNet, MSDN and Answers. The value of these resources is greatly enhanced by participants like you, who voluntarily contribute your time and energy to improve the online community experience for others.

Thank you for your commitment to Microsoft online technical communities and congratulations again!

Thank you Microsoft for your recognition. Okay what else do I get apart from the certificate and the cool badges? They have given me 12 month free subscription for “Microsoft Press E-Reference Library” which have hundreds of books in different formats and the patterns and practices whitepapers etc. Cool!