How to export GridView data to Excel

Source: Internet
Author: User
Tags httpcontext

I tried to export the Gridview to Excel. I thought it was very simple, but it was not very easy to apply it. I have summarized the following points:
1. because the content of the gridview may be displayed by page, the allowpaging attribute of the gridview is set to false each time an excel file is exported, and then databind () is clicked, ensure that all data is collected;
2. You do not need to set the export path separately. A dialog box will pop up asking you to confirm the storage location;
3. Write an empty VerifyRenderingInServerForm method (which must be written) to confirm that the HtmlForm control is displayed for the specified ASP. NET server control at runtime;
4. Do not forget to reset its allowpaging attribute after export;
After setting all these settings, click [export]. The following error occurs: you can only call RegisterForEventValidation (RegisterForEventValidation can only be called during Render ();) when executing Render ();) I checked the code and found no problems. After a while, I couldn't figure it out. Then I searched and found a solution:

1. Process code in excel format

/// <Summary>
/// Export to Excel
/// </Summary>
/// <Param name = "control"> control </param>
/// <Param name = "encoding"> Export format </param>
/// <Param name = "filename"> File name </param>
Public static void ConvertToExcel (System. Web. UI. Control control, string encoding, string filename)
// Set the file name format to prevent Chinese file names from being garbled
String FileName = System. Web. HttpUtility. UrlEncode (Encoding. UTF8.GetBytes (filename ));
System. Web. HttpContext. Current. Response. Clear ();
System. Web. HttpContext. Current. Response. Buffer = true;
System. Web. HttpContext. Current. Response. Charset = "" + encoding + "";
// The following line is very important. The attachment parameter indicates downloading as an attachment. You can change it to online.
// Filename=FileFlow.xls specifies the name of the output file. Note that the extension is consistent with the specified file type. It can be. doc. xls. txt. htm.
System. Web. HttpContext. Current. Response. AppendHeader ("Content-Disposition", "attachment; filename =" + FileName );
System. Web. HttpContext. Current. Response. ContentEncoding = System. Text. Encoding. GetEncoding ("" + encoding + "");
// Response. ContentType specifies the file type which can be application/ms-excel, application/ms-word, application/ms-txt, application/ms-html, or other browsers can directly support documents.
System. Web. HttpContext. Current. Response. ContentType = "application/ms-excel ";
Control. EnableViewState = false;
// Define an input stream
System. IO. StringWriter oStringWriter = new System. IO. StringWriter ();
System. Web. UI. HtmlTextWriter oHtmlTextWriter = new System. Web. UI. HtmlTextWriter (oStringWriter );

Control. RenderControl (oHtmlTextWriter );
// This indicates that the current page is output. You can also bind the datagrid or other controls that support the obj. RenderControl () attribute.
System. Web. HttpContext. Current. Response. Write (oStringWriter. ToString ());
System. Web. HttpContext. Current. Response. End ();
2. Call code
Protected void btnExcel_Click (object sender, EventArgs e)
String filename = String. Format ("xxxxtable -00000}..xls", DateTime. Now. Month. ToString ());
ConvertToExcel (mygridview "UTF-8", filename );

Public override void VerifyRenderingInServerForm (Control control Control)
// Base. VerifyRenderingInServerForm (control );
3. Prevent data loss due to format changes after being imported to an excel file (for example, if the document number is imported to an Excel file, the column will be represented by the excel file in scientific notation, and part of the data will be lost ), the following code must be added to the RowDataBound event of the gridview.
// Set the format of the exported cell to prevent data loss in excel
Protected void gvSettlement_RowDataBound (object sender, GridViewRowEventArgs e)
If (e. Row. RowType = DataControlRowType. DataRow)
E. Row. Cells [0]. Attributes. Add ("style", "vnd. Ms-excel.numberformat :@");


Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.