how to export sql table to excel without loss your formatting?

Question

I want export sql table to excel file. my Code:

public void ExportToExcel(string strQuery)
{
    //Get the data from database into datatable
    OleDbCommand cmd = new OleDbCommand(strQuery);
    DataTable dt = GetData(cmd);

    //Create a dummy GridView
    GridView GridView1 = new GridView();
    GridView1.AllowPaging = false;
    GridView1.DataSource = dt;
    GridView1.DataBind();

    HttpContext.Current.Response.Clear();
    HttpContext.Current.Response.ClearContent();
    HttpContext.Current.Response.Buffer = true;
    HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.xls");
    HttpContext.Current.Response.Charset = "";
    HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
    StringWriter sw = new StringWriter();
    HtmlTextWriter hw = new HtmlTextWriter(sw);

    for (int i = 0; i < GridView1.Rows.Count; i++)
    {
        //Apply text style to each Row
        GridView1.Rows[i].Attributes.Add("class", "textmode");
    }
    GridView1.RenderControl(hw);

    //style to format numbers to string
    string style = @"<style> .textmode { mso-number-format:\@; } </style>";
    HttpContext.Current.Response.Write(style);
    HttpContext.Current.Response.Output.Write(sw.ToString());
    HttpContext.Current.Response.Flush();
    HttpContext.Current.Response.End();
}

and In Button of aspx page:

protected void ExportToExcel(object sender, EventArgs e)
{
    ExportClass Exc = new ExportClass();
    string wherestr = null;
    if ((StartTBX.Text.Length > 0) && (EndTBX.Text.Length > 0)) { wherestr = String.Format("((STATUS = 'User' And PostStatus='Default') or STATUS = 'Admin') And (CreateDate >='{0}' And CreateDate <='{1}')", StartTBX.Text, EndTBX.Text); }
    else if (StartTBX.Text.Length > 0) { wherestr = String.Format("((STATUS = 'User' And PostStatus='Default') or STATUS = 'Admin') And CreateDate >='{0}'", StartTBX.Text); }
    else if (EndTBX.Text.Length > 0) { wherestr = String.Format("((STATUS = 'User' And PostStatus='Default') or STATUS = 'Admin') And CreateDate <='{0}'", EndTBX.Text); }
    else { wherestr = "((STATUS = 'User' And PostStatus='Default') or STATUS = 'Admin')"; }
    Exc.ExportToExcel("SELECT Users.UserID, UserName, Status, LockUser, FName + ' ' + Lname as [نام کامل] FROM Users inner join UsersInfo on Users.UserID=UsersInfo.UserID WHERE " + wherestr + " ORDER BY CreateDate DESC");
}

When I do not use date filtering in code(last else in code), every thing is fine but when i use date filtering, the content of [نام کامل] format is lost. content of [نام کامل] is Non-English words.

without date filter:

with date filter:

Please help me. Thank a lot.

Answer 1

well, my problem resolve with this code:

HttpContext.Current.Response.ContentEncoding = Encoding.Unicode;
HttpContext.Current.Response.BinaryWrite(Encoding.Unicode.GetPreamble());

best regards.