Export data to excel for non-latin characters

A common feature in applications is to export data in excel. With .NET it is pretty simple with the use of the Microsoft Excel Object Library. Sadly, in asp.net this is not available in medium trust. The alternative in a web application is to render the data in a HTML table and send it to the user with a “application/vnd.ms-excel” Content Type. With asp.net you can use a DataGrid control to render the table. The code for this is all over the Internet.

For those that have tried to use this code with non-latin characters (e.g. Greek characters) you might have the non-latin characters not showing properly. And yes, it is a encoding issue!

You might have noticed in Visual Studio  that there are 2 UTF-8 options to save a file. The default encoding for Visual Studio is UTF-8 (with signature). In order to set this in your exported excel, you just have to add the line  CurrentResponse.ContentEncoding = Encoding.UTF8. But this is the UTF-8 version without the signature and this line might not do the trick.

In order to add the signature you just have to add a few bytes in the start of the response stream. The code is very simple, check it out.

Sub DataToExcel(ByVal dt As DataTable, ByVal CurrentResponse As System.Web.HttpResponse, ByVal ExcelFileName As String)
    CurrentResponse.Clear()
    CurrentResponse.Charset = ""
    CurrentResponse.ContentType = "application/vnd.ms-excel"
    CurrentResponse.AddHeader("Content-Disposition", "attachment;filename=" & ExcelFileName & ".xls")  
 
    'Here we set the correct encoding so that all characters show!
    CurrentResponse.ContentEncoding = System.Text.Encoding.UTF8
    CurrentResponse.Charset = "65001"
    Dim b As Byte() = New Byte() {&HEF, &HBB, &HBF}
    CurrentResponse.BinaryWrite(b)
 
    Dim stringWrite As New System.IO.StringWriter
    Dim htmlWrite As New System.Web.UI.HtmlTextWriter(stringWrite)
    Dim dg As New System.Web.UI.WebControls.DataGrid
    dg.DataSource = dt
    dg.DataBind()
    dg.RenderControl(htmlWrite)
    CurrentResponse.Write(stringWrite.ToString)
    CurrentResponse.End()
End Sub

This function will not work if instead of a DataGrid we use the newer GridView control. If we use the GridView control you will get an error that the GridView needs to be included in a <form runat=”server”> tag. I haven’t succeeded in using a GridView, so we keep using the DataGrid control.

Here is also the C# version of the code, thanks to the comment of Ahmed

public void DataToExcel(DataTable dt, System.Web.HttpResponse CurrentResponse, string ExcelFileName)
{
	CurrentResponse.Clear();
	CurrentResponse.Charset = "";
	CurrentResponse.ContentType = "application/vnd.ms-excel";
	CurrentResponse.AddHeader("Content-Disposition", "attachment;filename=" + ExcelFileName + ".xls");
 
	//Here we set the correct encoding so that all characters show!
	CurrentResponse.ContentEncoding = System.Text.Encoding.UTF8;
	CurrentResponse.Charset = "65001";
	byte[] b = new byte[] {
		0xef,
		0xbb,
		0xbf
	};
	CurrentResponse.BinaryWrite(b);
 
	System.IO.StringWriter stringWrite = new System.IO.StringWriter();
	System.Web.UI.HtmlTextWriter htmlWrite = new System.Web.UI.HtmlTextWriter(stringWrite);
	System.Web.UI.WebControls.DataGrid dg = new System.Web.UI.WebControls.DataGrid();
	dg.DataSource = dt;
	dg.DataBind();
	dg.RenderControl(htmlWrite);
	CurrentResponse.Write(stringWrite.ToString);
	CurrentResponse.End();
}