How to export excel when the gridview is divided into multiple pages

How to export excel when the gridview is divided into multiple pages

During development, you will encounter the problem of importing data from the gridview to the Excel
In this case, when girdview has multiple pages of data, the data exported in general mode may only be the data on the current page, and the data on the last few pages is still in the database, the traditional method is
Write markup characters and text to ASP. NET
The output stream of the control on the server. A page that is not displayed cannot be output to an Excel file. The following provides a solution to import data to excel.

Step 1: record the query conditions. When a user clicks download, the query conditions are the same and displayed to the datatable.

This step is not fixedCodeYou can use viewstate to record query conditions when querying, for example

Ordermng. orderlist_getsuporderlist_binddata (Param, fdynamicparam, pageparambycus, dystr); (the data on a single page is queried and bound to girdview)
Viewstate ["gparam"]
= Param; viewstate ["dparam"] = fdynamicparam; viewstate ["pageparam"] =
Pageparambycus; viewstate ["strparam"] = dystr; (record query condition)

When the user clicks download, all data is queried to the datatable with the same conditions.

Hashtable gparam = (hashtable) viewstate ["gparam"];
Hashtable dparam = (hashtable) viewstate ["dparam"];
Hashtable pageparam = (hashtable) viewstate ["pageparam"];
String strparam = viewstate ["strparam"]. tostring ();

Is to convert the conditions for starting the query back
Datatable dt = ordermng. orderlist_getsuporderlist (gparam, dparam, pageparam, strparam );

After the same condition is queried, go to the datatable

Step 2: Process datatable, modify column names, sort, and modify the displayed content

Code

 1      Protected     Void Datatabletranfer (datatable DT)
  2   {
  3   Datacolumn DC  =     Null  ;
  4   DT. Columns. Add (  "  Type  "  , Type. GetType (  " System. String  "  ));
  5   DT. Columns. Add (  "  Product Name  "  , Type. GetType (  "  System. String  "  ));
  6   DT. Columns. Add (  "  Settlement status "  , Type. GetType (  "  System. String  "  ));
  7     Foreach  (Datarow row  In  DT. Rows)
  8   {
  9     If (Row [  "  Issecret  "  ]. Tostring ()  =     "  0  "  )
  10   {
  11   Row [  "  Type "  ]  =     "  Network order  "  ;
  12   }
  13     Else  
  14   {
  15  Row [  "  Type  "  ]  =     "  Manual order  "  ;
  16   }
  17   }
  18   Row [ "  Product Name  "  ]  =     "  Id [:  "     +  Row [  "  Productid  "  ]  +    "  ]  "     +  Row [  "  Productname  "  ];
  19     //  Ischeckoutk is the method for determining the page  
  20     Row [  "  Settlement status  "  ]  =  Ischeckout (row [  "  Checkoutid  "  ], Row [  "  Isrepeal  "  ]);
  21   
 22   
  23     //  Modify column name  
  24      DT. Columns [  "  Orderid  "  ]. Columnname  =     " Order No.  "  ;
  25   DT. Columns [  "  Buytime  "  ]. Columnname  =     "  Transaction date  "  ;
  26   DT. Columns [ "  Buycount  "  ]. Columnname  =     "  My sales volume  "  ;
  27     //  Sort column  
  28    DT. Columns [  "  Order No.  "  ]. Setordinal (  0  );
  29   DT. Columns [  "  Type  "  ]. Setordinal (  1  );
  30   DT. Columns [ "  Transaction date  "  ]. Setordinal (  2  );
  31     //  Remove Column  
  32     DT. Columns. Remove (Dt. Columns [  "  Totalsize  " ]);
  33   DT. Columns. Remove (Dt. Columns [  "  Rownum  "  ]);
  34   
  35   } 

Step 3: Convert datatable into Excel

Code

   Public    Static     Void  Tabletoexcel (datatable TB,  String  Filename)
{
  String  Filename  =  Filename;
System. Web. httpcontext Context  =  System. Web. httpcontext. Current;
  If  (Tb  ! =    Null  ))
{
Context. response. Clear ();
Context. response. charset  =     "  Gb2312  "  ;
Context. response. contentencoding  =  System. Text. encoding. getencoding (  "  Gb2312  " );
Context. response. contenttype  =     "  Application/MS-Excel  "  ;
Context. response. appendheader (  "  Content-Disposition  "  ,  "  Attachment; filename = \  "" + System. Web. httputility. urlencode (filename, system. Text. encoding. getencoding (  "  Gb2312  "  ) + Datetime. Now. tostring (  "  Yyyymmdd  "  ) +  "  . Xls \  ""  );

Cultureinfo cult  =     New Cultureinfo (  "  Zh-CN  "  ,  True  );
Stringwriter SW  =     New  Stringwriter (cult );
Htmltextwriter HTW  =     New  Htmltextwriter (SW );
DataGrid dgrid =     New  DataGrid ();
Dgrid. datasource  =  TB. defaultview;
Dgrid. allowpaging  =     False  ;
Dgrid. databind ();
HTW. writeline (  "  <Meta http-equiv = \  "  Content  - Type \  "  Content = \  "  Text  /  HTML; charset  =  Gb2312 \  "  >  "  );
Dgrid. rendercontrol (HTW );
Context. response. Write (SW. tostring ());
Context. response. End ();
}
}
 

Operation completed since