Warning : Export GridView to Excel file

Question

I have a page with malty controls and a grid view.

I want to export the gridview data to excel file. I use this class.

public class GridViewExportUtil
{

public static void Export(string fileName, GridView gv)
{
    HttpContext.Current.Response.Clear();
    HttpContext.Current.Response.AddHeader(
        "content-disposition", string.Format("attachment; filename={0}", fileName));
    HttpContext.Current.Response.ContentType = "application/ms-excel";

    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.Flush();
            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);
        }
    }
}

}

My gridview have special format and i use persian language.

when i use this class, it is export gridview to excel, but

myproblem is : when open excel file , i get error "is in a different format than specified by the file extension". when open the file ,Persian words are illegible.

score 0 · Answer 1 · edited May 23 '17 at 12:29

0

This is a common issue exporting to excel, it depends which version the user has installed.

Have a look at the solution to this question: the file you are trying to open is in a different format than specified by the file extension in Asp.Net

edited May 23 '17 at 12:29

Community

1
1

answered Jul 03 '14 at 09:49

Chris L

2,262
1
18
33

Thanks , but What is the namespace for XLWorkbook ? – Niloo Jul 05 '14 at 04:28

score 0 · Accepted Answer · answered Jul 05 '14 at 05:43

By adding some styling at the header part of your output in the export function, and for the Right-to-left alignment option it's inserted at the level of the declaration which called .

Example if you're using C# in your project:

private string AddExcelStyling()
{

StringBuilder sb = new StringBuilder();

sb.Append("<html xmlns:o='urn:schemas-microsoft-com:office:office'\n" +

"xmlns:x='urn:schemas-microsoft-com:office:excel'\n" +

"xmlns='http://www.w3.org/TR/REC-html40'>\n" +

"<head>\n");

sb.Append("<style>\n");

sb.Append("@page");

sb.Append("mso-page-orientation:landscape;}\n");

sb.Append("</style>\n");

sb.Append("<!--[if gte mso 9]><xml>\n");

sb.Append("<x:ExcelWorkbook>\n");

sb.Append("<x:ExcelWorksheets>\n");

sb.Append("<x:ExcelWorksheet>\n");

sb.Append("<x:Name>Sheet Name</x:Name>\n");

sb.Append("<x:WorksheetOptions>\n");

sb.Append("<x:Print>\n");

sb.Append("<x:HorizontalResolution>600</x:HorizontalResolution\n");

sb.Append("<x:VerticalResolution>600</x:VerticalResolution\n");

sb.Append("</x:Print>\n");

sb.Append("<x:Selected/>\n");

sb.Append("<x:DisplayRightToLeft/>\n");

sb.Append("<x:DoNotDisplayGridlines/>\n");

sb.Append("</x:WorksheetOptions>\n");

sb.Append("</x:ExcelWorksheet>\n");

sb.Append("</x:ExcelWorksheets>\n");

sb.Append("</x:ExcelWorkbook>\n");

sb.Append("</xml><![endif]-->\n");

sb.Append("</head>\n");

sb.Append("<body>\n");

return sb.ToString();

}

See this link: http://forums.asp.net/p/1445619/3358464.aspx

Warning : Export GridView to Excel file

2 Answers2