ASP. NET export data to Excel implementation program

1. Define document type and character encoding

The code is as follows:

Copy code

Response. Clear (); Response. Buffer = true; Response. Charset = "UTF-8 "; // 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. Response. AppendHeader ("Content-Disposition", "attachment?filename=fileflow.xls "); Response. ContentEncoding = System. Text. Encoding. GetEncoding ("UTF-8 "); // Response. ContentType the specified file type can be application/ms-excel application/ms-word application/ms-txt application/ms-html or other browsers can directly support documents Response. ContentType = "application/ms-excel "; This. EnableViewState = false;

 
2. Define an input stream

The code is as follows:	Copy code
System. IO. StringWriter oStringWriter = new system. IO. StringWriter (); System. Web. UI. HtmlTextWriter oHtmlTextWriter = new system. Web. UI. HtmlTextWriter (oStringWriter );

3. Bind the target data to the input stream output

The code is as follows:	Copy code
This. 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. Response. Write (oStringWriter. ToString ()); Response. End ();

4. If an error message "RegisterForEventValidation can be called only during Render () execution" occurs.

There are two solutions:
 
1. Modify web. config (not recommended) <pages enableEventValidation = "false"> </pages>
2. Modify it directly on the export Execl page

Now let's talk a lot about it. Next we can see that the "ctl00_center_GridView1" control of the. net type "GridView" must be placed in the form tag with runat = server. Note: an unhandled exception occurs during the execution of the current Web request. Check the stack trace information for details about the error and the source of the error in the code. Exception details: System. Web. HttpException: the control "ctl00_center_GridView1" of the type "GridView" must be placed in the form tag with runat = server.

This error description is an error reported when I comment out this program,

The code is as follows:	Copy code
// Publicoverrisponidverifyrenderinginserverform (Controlcontrol) //{ /// Base. VerifyRenderingInServerForm (control ); //}

Although the content in this method is also commented out, that is to say, this is an empty method, but if there is no method, the program will report the above error. When I first saw this error, I thought of a similar error that was reported by the ajax program. The same reason is that the VerifyRenderingInServerForm method is not overwritten. Note that the code exported to Excel is pasted below.

 

The code is as follows:

Copy code

UsingSystem; UsingSystem. Data; UsingSystem. Configuration; UsingSystem. Collections; UsingSystem. Web; UsingSystem. Web. Security; UsingSystem. Web. UI; UsingSystem. Web. UI. WebControls; UsingSystem. Web. UI. WebControls. WebParts; UsingSystem. Web. UI. HtmlControls; UsingSystem. IO; /// <Summary> /// Brief description of ToExcleHelper /// </Summary> PublicclassExportHelper { PublicstaticvoidExportToExcel (IListdataList, string [] fields, string [] headTexts, stringtitle) { GridViewgvw = newGridView (); IntColCount, I; // If the number of filtered fields is the same as the number of corresponding column header names, only the specified fields are exported. If (fields. Length! = 0 & fields. Length = headTexts. Length) { ColCount = fields. Length; Gvw. AutoGenerateColumns = false; For (I = 0; I <ColCount; I ++) { BoundFieldbf = newBoundField (); Bf. DataField = fields [I]; Bf. HeaderText = headTexts [I]; Gvw. Columns. Add (bf ); } } Else { Gvw. AutoGenerateColumns = true; } SetStype (gvw ); Gvw. DataSource = dataList; Gvw. DataBind (); ExportToExcel (gvw, title ); } /// <Summary> /// Export data to Excel /// </Summary> /// <Paramname = "DataList"> IListData </param> /// <Paramname = "Fields"> Field to be exported </param> /// <Paramname = "HeadName"> name displayed for the field </param> PublicstaticvoidExportToExcel (IListdataList, string [] fields, string [] headTexts) { ExportToExcel (dataList, fields, headTexts, string. Empty ); } /// <Summary> /// Set the style /// </Summary> /// <Paramname = "gvw"> </param> PrivatestaticvoidSetStype (GridViewgvw) { Gvw. Font. Name = "Verdana "; Gvw. BorderStyle = System. Web. UI. WebControls. BorderStyle. Solid; Gvw. HeaderStyle. BackColor = System. Drawing. Color. LightCyan; Gvw. HeaderStyle. ForeColor = System. Drawing. Color. Black; Gvw. HeaderStyle. HorizontalAlign = System. Web. UI. WebControls. HorizontalAlign. Center; Gvw. HeaderStyle. Wrap = false; Gvw. HeaderStyle. Font. Bold = true; Gvw. HeaderStyle. Font. Size = 10; Gvw. RowStyle. Font. Size = 10; } /// <Summary> /// Export the data in the GridView to Excel /// </Summary> /// <Paramname = "gvw"> </param> /// <Paramname = "DataList"> </param> PublicstaticvoidExportToExcel (GridViewgvw, stringtitle) { StringfileName; HttpContext. Current. Response. Buffer = true; HttpContext. Current. Response. ClearContent (); HttpContext. Current. Response. ClearHeaders (); FileName = string. Format ("xhmd {0: yyMMddHHmm}.xls", DateTime. Now ); HttpContext. Current. Response. AppendHeader ("Content-Disposition", "attachment; filename =" + fileName ); HttpContext. Current. Response. ContentType = "application/vnd. ms-excel "; StringWritertw = newSystem. IO. StringWriter (); HtmlTextWriterhw = newSystem. Web. UI. HtmlTextWriter (tw ); Gvw. RenderControl (hw ); If (! String. IsNullOrEmpty (title )) { HttpContext. current. response. write ("<B> <center> <fontsize = 3 face = Verdanacolor = # 0000FF>" + title + "</font> </center> </B> "); } HttpContext. Current. Response. Write (tw. ToString ()); HttpContext. Current. Response. Flush (); HttpContext. Current. Response. Close (); HttpContext. Current. Response. End (); Gvw. Dispose (); Tw. Dispose (); Hw. Dispose (); Gvw = null; Tw = null; Hw = null; } PublicstaticvoidDataTable2Excel (System. Data. DataTabledtData) { System. Web. UI. WebControls. DataGriddgExport = null; // Current dialog System. Web. HttpContextcurContext = System. Web. HttpContext. Current; // IO is used to export and return an excel file System. IO. StringWriterstrWriter = null; System. Web. UI. HtmlTextWriterhtmlWriter = null; If (dtData! = Null) { // Set the encoding and attachment format CurContext. Response. ContentType = "application/vnd. ms-excel "; CurContext. Response. ContentEncoding = System. Text. Encoding. UTF8; CurContext. Response. Charset = ""; // Export an excel file StrWriter = newSystem. IO. StringWriter (); HtmlWriter = newSystem. Web. UI. HtmlTextWriter (strWriter ); // To solve the possible paging problem in dgData, you need to redefine a non-paging DataGrid. DgExport = newSystem. Web. UI. WebControls. DataGrid (); DgExport. DataSource = dtData. DefaultView; DgExport. AllowPaging = false; DgExport. DataBind (); // Return to the client DgExport. RenderControl (htmlWriter ); CurContext. Response. Write (strWriter. ToString ()); CurContext. Response. End (); } } }

In conclusion, this operation can be exported by page, which is much more complicated than in the beginning of this article.