ASP. NET gridview export data to excel

ASP. NET gridview export data to excel

First, put the gridview control in the panel.

<Asp: Panel id = "Panel1" runat = "server" width = "100%">
<Asp: gridview id = "gridview1" runat = "server" width = "100%" cellpadding = "4" forecolor = "#333333"
Autogeneratecolumns = "false" allowpaging = "true" allowsorting = "true" onRowdeleting = "gridview1_rowdeleting"
OnRowediting = "gridview1_rowediting" datakeynames = "Sno" onDatabound = "gridviewdomaindatabound"
Font-size = "14px">
<Columns>
<Asp: hyperlinkfield datatextfield = "Sno" headertext = "student ID" datanavigateurlfields = "Sno"
Datanavigateurlformatstring = "studentinfo. aspx? Id = {0} "/>
<Asp: boundfield datafield = "sname" headertext = "name"/>
<Asp: boundfield datafield = "ssex" headertext = "gender"/>
<Asp: boundfield datafield = "snational" headertext = "national"/>
<% -- <Asp: boundfield datafield = "sdegree" headertext = ""/> -- %>
<Asp: boundfield datafield = "spoliticalface" headertext = "face rectification"/>
<Asp: boundfield datafield = "sphone" headertext = "phone"/>
<Asp: boundfield datafield = "semail" headertext = "E-mail"/>
<Asp: boundfield datafield = "saddress" headertext = "Address"/>
<Asp: boundfield datafield = "sidcard" headertext = "ID card number"/>
<Asp: commandfield headertext = "edit" showeditbutton = "true" buttontype = "image" editimageurl = "~ /Admin/images/edit.gif "/>
<Asp: commandfield headertext = "delete" showdeletebutton = "true" buttontype = "image" deleteimageurl = "~ /Admin/images/del.gif "/>
</Columns>
<Pagertemplate> // The following are custom pages.
<Div id = "Main">
<Table>
<Tr>
<TD style = "text-align: Left;">
<Div id = "info">
& Nbsp; Page times: <asp: Label id = "lblpagecurrent" runat = "server" text = "1" cssclass = "txtinfo"> </ASP: Label>
/<Asp: Label id = "lblpagecount" runat = "server" text = "1"> </ASP: Label> & nbsp; Total & nbsp; <asp: label
Id = "lblpagerow" runat = "server" text = "1" cssclass = "txtinfo"> </ASP: Label> & nbsp; records
</Div>
</TD>
<TD style = "text-align: right;">
<Div id = "page">
<Asp: linkbutton id = "btnfirst" runat = "server" cssclass = "Link" commandname = "pager"
Commandargument = "first" onCommand = "navigatetopage"> [homepage] </ASP: linkbutton> & nbsp;
<Asp: linkbutton id = "btnprev" runat = "server" cssclass = "Link" commandname = "pager" commandargument = "Prev"
OnCommand = "navigatetopage"> [Previous Page] </ASP: linkbutton> & nbsp;
<Asp: linkbutton id = "btnnext" runat = "server" cssclass = "Link" commandname = "pager" commandargument = "Next"
OnCommand = "navigatetopage"> [Next Page] </ASP: linkbutton> & nbsp;
<Asp: linkbutton id = "btnlast" runat = "server" cssclass = "Link" commandname = "pager" commandargument = "last"
OnCommand = "navigatetopage"> [last page] </ASP: linkbutton> & nbsp;
</Div>
</TD>
</Tr>
</Table>
</Div>
</Pagertemplate>
<Footerstyle backcolor = "#507cd1" font-bold = "true" forecolor = "white"/>
<Pagerstyle backcolor = "# 2461bf" forecolor = "white" horizontalalign = "center"/>
<Selectedrowstyle backcolor = "# d1ddf1" forecolor = "#333333" font-bold = "true"/>
<Headerstyle backcolor = "#507cd1" font-bold = "true" forecolor = "white"/>
<Alternatingrowstyle backcolor = "white"/>
<Rowstyle backcolor = "# eff3fb"/>
<Editrowstyle backcolor = "# 2461bf"/>
</ASP: gridview>
</ASP: Panel>

 

Add another button to trigger the export event:

<Asp: linkbutton id = "lbtnexcel" runat = "server" text = "export to excel" onClick = "lbtnexcel_click"> </ASP: linkbutton>

 

The above is the ASPX page code, and the background code is:

Protected void lbtnexcel_click (Object sender, eventargs E)
{
Gridview1.bottompagerrow. Visible = false; // hide the paging part after being exported to an Excel table.

Gridview1.columns [9]. Visible = false; // hide the "edit" column
Gridview1.columns [10]. Visible = false; // hide the "delete" column

Gridview1.allowpaging = false; // cancel the page to export all data. Otherwise, only a few pieces of data on the current page can be exported.

Gridview1.datasource = Ds; // unpaginated and rebind the dataset. DS is the dataset.
Gridview1.databind ();

Datetime dt = datetime. Now; // name the exported Excel table based on its purpose and system time.
String filename = DT. year. tostring () + dt. month. tostring () + dt. day. tostring () + dt. hour. tostring () + dt. minute. tostring () + dt. second. tostring ();

/* For example, some exported tables have numbers such as column numbers and ID card numbers. If you do not process them, the exported data is a number by default, for example, the original string "0010" is changed to the number 10, and the string "1245787888" is changed to the scientific notation 1.236 + E9, so that the expected results cannot be reached, therefore, you need to add a formatted data type to the corresponding column before export. The following format is string type */

Foreach (gridviewrow DG in this. gridview1.rows)
{
DG. cells [0]. Attributes. Add ("style", "Vnd. ms-excel.numberformat :@;");
DG. cells [5]. Attributes. Add ("style", "Vnd. ms-excel.numberformat :@;");
DG. cells [6]. Attributes. Add ("style", "Vnd. ms-excel.numberformat :@;");
DG. cells [8]. Attributes. Add ("style", "Vnd. ms-excel.numberformat :@;");
}

Response. Clear ();
Response. addheader ("content-disposition", "attachment; filename =" + system. web. httputility. urlencode ("Student table" + filename, system. text. encoding. utf8) + ". xls "); // export file name
Response. contentencoding = system. Text. encoding. utf7; // if it is set to "gb2312", Chinese characters may be garbled
Response. contenttype = "applicationshlnd.xls ";
System. Io. stringwriter ostringwriter = new system. Io. stringwriter ();
System. Web. UI. htmltextwriter ohtmltextwriter = new system. Web. UI. htmltextwriter (ostringwriter );
Panel1.rendercontrol (ohtmltextwriter); // Add the panel into the output stream.
Response. Write (ostringwriter. tostring (); // output the stream.
Response. Flush ();
Response. End ();
}

// Reload the verifyrenderinginserverform method. Otherwise, the following error message is displayed during running: the "gridview1" control of the "gridview" type must be placed in the form tag with runat = server"
Public override void verifyrenderinginserverform (Control)
{
// Override verifyrenderinginserverform.
}