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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s