Asp.Net Export to Excel - Japanese Characters

Question

I am currently using Visual Studio 2008 for my ASP .NET application. I am trying to Export some reports with Japanese Characters to Excel via the Response object. When I try to Export, all the Japanese characters looks garbled. It works fine with Chinese Characters. Here is what I tried:

I tried Installed Japanese Language Pack / Encoding to UTF-8 / UTF-7 / Shift-JIS / Globalization (Web.Config) .. but no luck. Any Ideas how this can be fixed ? Thanks !!

        string attachment = "attachment; filename=PerksPlusReport.xls";
        //Response.Clear();
        Response.ClearContent();
        Response.ClearHeaders();
 Response.AddHeader("content-disposition", attachment);
        //Response.Charset = "UTF-8";
 //Response.Charset = "UTF-7";
 //Response.Charset = "Shift_JIS";
        Response.ContentType = "application/vnd.ms-excel";
        StringWriter sw = new StringWriter();
        HtmlTextWriter htw = new HtmlTextWriter(sw);

        // Create a form to contain the grid
        HtmlForm frm = new HtmlForm();
        ReportGridView.Parent.Controls.Add(frm);
        frm.Attributes["runat"] = "server";
        GridView GridView2 = new GridView();

        ReportView reportDetails = GetReportDetails();
        GridView2.DataSource = GetReportResults(this.ReportId.Value, reportDetails.Sql);

        GridView2.DataBind();
        PrepareGridViewForExport(GridView2);
        frm.Controls.Add(GridView2);
        frm.RenderControl(htw);

        string fileContents = sw.ToString();
        int startSpot = fileContents.IndexOf("<table");
        fileContents = fileContents.Substring(startSpot);
        int endSpot = fileContents.IndexOf("</table>");
        fileContents = fileContents.Substring(0, endSpot + 8);

        try
        {
            // Replace all &lt; and &gt; with < and >
            fileContents = fileContents.Replace("&lt;", "<");
            fileContents = fileContents.Replace("&gt;", ">");
            fileContents = fileContents.Replace("€", "&euro;");

            string RegularExpression = @"<a[^>]*>([^<]*)</a>";
            Regex regex = new Regex(RegularExpression);

            //If match found .. uses the delegate function to replace the whole content with the filtered values 
            if (regex.IsMatch(fileContents))
            {
                regex.Replace(fileContents, delegate (Match m){return fileContents.Replace(m.Captures[0].Value, m.Groups[1].Value);});
            }
        }
        catch (Exception ex2)
        {
            Response.Write(ex2.ToString());
        }

 Response.Write(fileContents);
        Response.End();

Answer 1

/*
Here is fully functional class. Hope this will help someone
For queries, contact amjad@qatar.net.qa
*/



using System;
using System.Data;
using System.Configuration;
using System.IO;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;


public class GridViewExportUtil
{

    public static void Export(string fileName, GridView gv)
    {
        HttpContext.Current.Response.Clear();
        //HttpContext.Current.Response.ContentType = "application/ms-excel";
        HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
        //HttpContext.Current.Response.ContentType = "text/html; charset=utf-8";

        HttpContext.Current.Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", fileName + ".xls"));
        HttpContext.Current.Response.Charset = "";
        HttpContext.Current.Response.Buffer = true;
        HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.Unicode;
        HttpContext.Current.Response.BinaryWrite(System.Text.Encoding.Unicode.GetPreamble());

        //HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
        //HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("iso-8859-2");


        //This will allow you to set the Report Header and Report Title
        //My file name format "ReportTitle_PatientID_PrescriptionNo_ReportDateTime"
        string [] arrFileName = fileName.Replace(".xls","").Split('_');
        string rptTitle = arrFileName[0] + " " + arrFileName[1];
        string rptPatientID = arrFileName[2];
        string rptSpecimen = arrFileName[3];
        string rptDate = arrFileName[4] + " " + arrFileName[5];

        /*Optional Header*/
        //HttpContext.Current.Response.Write("DEPARTMENT OF PHARMACY - Pharmacy Prescription Details<br>");
        //HttpContext.Current.Response.Write("Patient ID: " + rptPatientID + "<br>");
        //HttpContext.Current.Response.Write("Prescription No: " + rptSpecimen + "<br>");
        //HttpContext.Current.Response.Write("Report Date: " + rptDate);
        //HttpContext.Current.Response.Write("<br><br>");


        using (StringWriter sw = new StringWriter())
        {
            using (HtmlTextWriter htw = new HtmlTextWriter(sw))
            {
                //  Create a form to contain the grid
                Table table = new Table();

                //  add the header row to the table
                if (gv.HeaderRow != null)
                {
                    GridViewExportUtil.PrepareControlForExport(gv.HeaderRow);
                    table.Rows.Add(gv.HeaderRow);
                }

                //  add each of the data rows to the table
                foreach (GridViewRow row in gv.Rows)
                {
                    GridViewExportUtil.PrepareControlForExport(row);
                    table.Rows.Add(row);
                }

                //  add the footer row to the table
                if (gv.FooterRow != null)
                {
                    GridViewExportUtil.PrepareControlForExport(gv.FooterRow);
                    table.Rows.Add(gv.FooterRow);
                }

                //  render the table into the htmlwriter
                table.RenderControl(htw);

            }

            //  render the htmlwriter into the response
            HttpContext.Current.Response.Write(sw.ToString());
            HttpContext.Current.Response.End();
        }


    }

    /// <summary>
    /// Replace any of the contained controls with literals
    /// </summary>
    /// <param name="control"></param>
    private static void PrepareControlForExport(Control control)
    {
        for (int i = 0; i < control.Controls.Count; i++)
        {
            Control current = control.Controls[i];
            if (current is LinkButton)
            {
                control.Controls.Remove(current);
                control.Controls.AddAt(i, new LiteralControl((current as LinkButton).Text));
            }
            else if (current is ImageButton)
            {
                control.Controls.Remove(current);
                control.Controls.AddAt(i, new LiteralControl((current as ImageButton).AlternateText));
            }
            else if (current is HyperLink)
            {
                control.Controls.Remove(current);
                control.Controls.AddAt(i, new LiteralControl((current as HyperLink).Text));
            }
            else if (current is DropDownList)
            {
                control.Controls.Remove(current);
                control.Controls.AddAt(i, new LiteralControl((current as DropDownList).SelectedItem.Text));
            }
            else if (current is CheckBox)
            {
                control.Controls.Remove(current);
                control.Controls.AddAt(i, new LiteralControl((current as CheckBox).Checked ? "True" : "False"));
            }

            if (current.HasControls())
            {
                GridViewExportUtil.PrepareControlForExport(current);
            }
        }
    }
}

Answer 2

Just add the following 2 lines. Most of the time we forget the second line which leads to all the frustration.

Response.ContentEncoding = System.Text.Encoding.Unicode;
Response.BinaryWrite(System.Text.Encoding.Unicode.GetPreamble());

Answer 3

Response.ContentEncoding = System.Text.Encoding.Default;

solved my problem. I hope yours too.

Answer 4

This is how I got it working ..

Response.Clear();
Response.ClearContent();
Response.ClearHeaders();
Response.AddHeader("content-disposition", "attachment; filename=FileName.xls");
Response.ContentEncoding = System.Text.Encoding.Unicode;
Response.ContentType = "application/ms-excel";
Response.BinaryWrite(System.Text.Encoding.Unicode.GetPreamble());

Answer 5

Have you tried setting the ContentEncoding property of the Response or explicitly setting the content-encoding HTTP header ?