Excel export using gridview

 

In fact, there are many examples of Excel on the Internet, but they are not very good, their code is not very full, and reading is still very obscure. After several days of exploration, I finally want to export the results of the report excel. Below is my.

I. Foreground page

Content on the first page of the gridview

Content on page 2 of the gridview:

This situation may occur when you export an Excel file. When you open the Excel file, you find that the content on the second page of the gridview is not exported to the Excel file. To solve this problem, you only need to set the pagination of the gridview to flase before export.

The following shows how to export all the content in the gridview. Open the Excel table as follows:

 

This allows you to export all the content in the gridview, including the content on the first and second pages of the gridview.

 

II. Implementation Code

1. Front-end code:

<Div style = "margin: 20px;">
<Asp: gridview id = "gvrecord" runat = "server" allowpaging = "true" cellpadding = "3"

Autogeneratecolumns = "false" bordercolor = "white"
Borderstyle = "ridge" borderwidth = "2px" backcolor = "white" cellspacing = "1"

Gridlines = "NONE" onprerender = "gvrecord_prerender"
Onpageindexchanged = "gvrecord_pageindexchanged"
Onpageindexchanging = "gvrecord_pageindexchanging">
<Pagersettings firstpagetext = "home page" lastpagetext = "last page"
Nextpagetext = "Next" previouspagetext = "previous"/>
<Rowstyle forecolor = "black" backcolor = "# e5f1ff" horizontalalign = "center"/>
<Columns>
<Asp: templatefield headertext = "extension">

<Itemtemplate>
<Asp: Label id = "label1" runat = "server" text = '<% # BIND ("extn") %>'> </ASP: Label>
</Itemtemplate>
<Itemstyle width = "200px"/>
</ASP: templatefield>
<Asp: templatefield headertext = "wake up time">

<Itemtemplate>
<Asp: Label id = "label2" runat = "server" text = '<% # BIND ("calltime") %>'> </ASP: Label>
</Itemtemplate>
<Itemstyle width = "300px"/>
</ASP: templatefield>
<Asp: templatefield headertext = "status">

<Itemtemplate>
<Asp: Label id = "label3" runat = "server" text = '<% # getresult (eval ("status "). tostring () %> '> </ASP: Label>
</Itemtemplate>
<Itemstyle width = "250px"/>
</ASP: templatefield>
<Asp: templatefield headertext = "call number">

<Itemtemplate>
<Asp: Label id = "label4" runat = "server" text = '<% # BIND ("callcount") %>'> </ASP: Label>
</Itemtemplate>
<Itemstyle width = "150px"/>
</ASP: templatefield>
</Columns>
<Footerstyle backcolor = "# c6c3c6" forecolor = "black"/>
<Pagerstyle backcolor = "# c6c3c6" forecolor = "black" horizontalalign = "right"/>
<Selectedrowstyle backcolor = "# 9471de" font-bold = "true" forecolor = "white"/>
<Headerstyle backcolor = "# c9e2ff" font-bold = "true" forecolor = "#000000"/>
</ASP: gridview>

<P>
<Asp: button id = "btnexcel" cssclass = "button" runat = "server" text = "statements
"
Onclick = "btnexcel_click"/> </P>
</Div>

 

2. The background code is as follows:

Using system;
Using system. collections;
Using system. configuration;
Using system. Data;
Using system. LINQ;
Using system. Web;
Using system. Web. Security;
Using system. Web. UI;
Using system. Web. UI. htmlcontrols;
Using system. Web. UI. webcontrols;
Using system. Web. UI. webcontrols. webparts;
Using system. xml. LINQ;
Using model;
Using Dal;
Using system. Collections. Generic;
Using system. IO;

Public partial class _ 3c_callmanager_callrecord: system. Web. UI. Page
{
Int selectflag = 0;
Protected void page_load (Object sender, eventargs E)
{
If (! Ispostback)
{
BIND ();
}
}

Public String getresult (string Str)
{
If (STR = "0 ")
Return "haven'tcalled ";
If (STR = "1 ")
Return "check-out ";
If (STR = "2 ")
Return "success ";
If (STR = "3 ")
Return "fail ";
If (STR = "4 ")
Return "dealing ";
If (STR = "5 ")
Return "artificial wake ";
Else
Return "unkown ";

}
Public void BIND (){
Selectflag = 0;
Gvrecord. datasource = vgcallservice. getinfo ();
Gvrecord. databind ();
}
Protected void btnexcel_click (Object sender, eventargs E)
{
Datetime dt = system. datetime. now;
String STR = DT. tostring ("yyyymmddhhmmss ");
STR = STR + ". xls ";

Gvrecord. allowpaging = false;
If (selectflag = 0)
BIND ();
If (selectflag = 1)
Selectbind ();

Gridviewtoexcel (gvrecord, "application/MS-excel", STR );

// Export (gvrecord, "application/MS-excel", STR );

}

/// <Summary>
/// Export grid data to excel
/// </Summary>
/// <Param name = "Ctrl"> grid name (such as gridview1) </param>
/// <Param name = "filetype"> file type to be exported (Excel: Application/MS-Excel) </param>
/// <Param name = "FILENAME"> name of the file to be saved </param>
Public static void gridviewtoexcel (control Ctrl, string filetype, string filename)
{
Httpcontext. Current. response. charset = "gb2312 ";
Httpcontext. Current. response. contentencoding = system. Text. encoding. utf8; // pay attention to Encoding
Httpcontext. Current. response. appendheader ("content-disposition ",
"Attachment; filename =" + httputility. urlencode (filename, system. Text. encoding. utf8). tostring ());
Httpcontext. Current. response. contenttype = filetype; // image/JPEG; text/html; image/GIF; VND. MS-Excel/MSWord

CTRL. Page. enableviewstate = false;
Stringwriter Tw = new stringwriter ();
Htmltextwriter hW = new htmltextwriter (TW );
CTRL. rendercontrol (HW );
Httpcontext. Current. response. Write (TW. tostring ());
Httpcontext. Current. response. End ();
}
/// <Summary>
/// Reload this verifyrenderinginserverform is neccessary
/// </Summary>
/// <Param name = "control"> </param>

Public override void verifyrenderinginserverform (Control)
{

}

Protected void gvrecord_prerender (Object sender, eventargs E)
{
If (selectflag = 0)
BIND ();
}
Protected void gvrecord_pageindexchanged (Object sender, eventargs E)
{

}
Protected void gvrecord_pageindexchanging (Object sender, gridviewpageeventargs E)
{
This. gvrecord. pageindex = E. newpageindex;

BIND ();
}
}

 

 

Note:

1) Foreground page <% @ page Language = "C #" enableeventvalidation = "false" autoeventwireup = "true" codefile = "callrecord. aspx. CS "inherits =" _ 3c_callmanager_callrecord "%> Add enableeventvalidation =" false ". Otherwise, an error is returned.

2) Code of the database bound to the gridview

Gvrecord. datasource = vgcallservice. getinfo (); // vgcallservice. getinfo () is a collection of databases. I encapsulated it and made different adjustments based on the collection you obtained.
Gvrecord. databind ();
3) Click the key code for the next page of The gridview, In the pageindexchanging event.

This. gvrecord. pageindex = E. newpageindex; // pay attention to this.

BIND ();

4) in the Excel export button event, remember to first gvrecord. allowpaging = false; cancel the pagination of the gridview, and then call the gridviewtoexcel method.

5) The following are indispensable.

Public override void verifyrenderinginserverform (Control)
{

}