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

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.

Passing a value from external class to a asp.net page and binding it to a control

Off late, I was working in a WCF application which involves all the crazy WCF stuffs like duplex contract, callback contract, asynchronous delegates etc. At one point I was in a position where I need to pass an item from an external class to an asp.net page and bind it to the control.

To call a function in a asp.net page from another class we have to either declare the function to be static (so that we can invoke it directly with the class name) or create an instance for the page class and call it through the object.

Both of them wont work because of the following reasons –

  1. If the function is static, it can’t operate on the controls present in the page (since the controls are not static)
  2. If you create an instance for that page class all the controls will be lost as a new instance of the page is created

Finally i achieve it through events and by declaring the function to be static.

//Create a delegate for the event
private delegate void DelegateHandler (type object);

//Create the event
private static event DelegateHandler RaiseEvent event;

//Create an object for the delegate
private static DelegateHandler oHandler = null;

//Add the function to be called from the static function to the delegate
oHandler = new DelegateHandler(OriginalFunctionToBeCalled);

//Add the delegate to the event
event += oHandler;

public static void StaticFunctionToBeCalled(Type obj)
{
    event(obj); //Raise the event
}
private void OriginalFunctionToBeCalled(Type obj)
{
    //bind the value to the control
}

Thats it. Hope it would be useful for someone.