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!

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.

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.

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

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.

Using CommonShowModalDialog in SharePoint

I’ve not found any post explaining the usage of CommonShowModalDialog in SharePoint, the default popup used by SharePoint across the board. So I thought why can’t I make one.

The method exists in core.js and it is used OOTB by SharePoint for opening popups in places like selecting a web application from a list of web applications, selecting a site collection from a list of site collections etc. (you could see these in Central administration). So here is the method –

function openPopup(){
    commonShowModalDialog("/_layouts/selectitem.aspx", "resizable: no; status:no; scroll: no; help: no; center: yes; dialogwidth:410px; dialogHeight:300px;", RetrieveItemValue);
}

CommonShowModalDialog has similar parameters as a simple window.open().

1. Target URL
2. Window features
3. Callback function

To pass a value from the popup to the parent page use window.returnValue in te popup to save the value before closing the window.

//called from the popup page
function closePoup(){
    window.returnValue = myValue;
    window.close();
}

Retrieve the value passed from the popup in the callback function mentioned.

//Retreives the value sent from the popup
function RetrieveItemValue(retValue) {
    alert(retValue);
}

As simple as that.

IIS going blank in a SharePoint 2007 server

Hello

Are you puzzled to see your IIS opening up blank not showing any of your sites? Is SharePoint server 2007 installed in the server? If yes, the issue is because of overloaded thread requests on your IIS. I suspect this has something to do with the WSS timer service. Not sure though. To get back your IIS just do an IISRESET in the box. And there you’ll get all your sites back in IIS.