导出到EXCEL，一般只是导出当前页的数据，实现导出所有的数据！！！

 

  1  ///   <summary>
  2           ///  模板列处理
  3           ///   </summary>
  4           ///   <param name="gv"></param>
  5           public  void PrepareGridViewForExport(Control gv) // 模式化特殊元素 flashcong
  6          {
  7             LinkButton lb =  new LinkButton();
  8             Literal l =  new Literal();
  9              string name = String.Empty;
 10              for ( int i =  0; i < gv.Controls.Count; i++)
 11             {
 12                  if (gv.Controls[i].GetType() ==  typeof(LinkButton))
 13                 {
 14                     l.Text = (gv.Controls[i]  as LinkButton).Text;
 15                     gv.Controls.Remove(gv.Controls[i]);
 16                     gv.Controls.AddAt(i, l);
 17                 }
 18                  else  if (gv.Controls[i].GetType() ==  typeof(DropDownList))
 19                 {
 20                     l.Text = (gv.Controls[i]  as DropDownList).SelectedItem.Text;
 21                     gv.Controls.Remove(gv.Controls[i]);
 22                     gv.Controls.AddAt(i, l);
 23                 }
 24                 else  if (gv.Controls[i].GetType() ==  typeof(CheckBox))
 25                 {
 26                     l.Text = (gv.Controls[i]  as CheckBox).Checked ?  " True " :  " False ";
 27                     gv.Controls.Remove(gv.Controls[i]);
 28                     gv.Controls.AddAt(i, l);
 29                 }
 30                  else  if (gv.Controls[i].GetType() ==  typeof(ImageButton))
 31                 {
 32 
 33                     l.Text =  " 图片 ";
 34                     gv.Controls.Remove(gv.Controls[i]);
 35                     gv.Controls.AddAt(i, l);
 36 
 37                 }
 38                  if (gv.Controls[i].HasControls())
 39                 {
 40 
 41                     PrepareGridViewForExport(gv.Controls[i]);
 42 
 43                 }
 44 
 45             }
 46 
 47         }
 48  ///   <summary>
 49           ///  导出到EXCEL
 50           ///   </summary>
 51           ///   <param name="sender"></param>
 52           ///   <param name="e"></param>
 53           protected  void btnPopupOKDesc_OnClick( object sender, EventArgs e)
 54         {
 55              string FileName = lbyear.Text +  " 材料备案明细表.xls ";
 56            
 57              int i =  0;
 58              if ( this.GVSearchResult.Rows.Count ==  0)
 59             {
 60                 Response.Write( " <script>alert('没有查找到数据,无法导出！')</script> ");
 61             }
 62              else
 63             {
 64                  /// 隐藏部分列，不予导出
 65                   for ( int j =  0; j <chllistitem.Items.Count; j++)
 66                 {
 67                      if (chllistitem.Items[j].Selected)
 68                     {
 69                         GVSearchResult.Columns[j+ 1].Visible =  true ;
 70                     }
 71                      else
 72                     {
 73                         GVSearchResult.Columns[j+ 1].Visible =  false ;
 74                     }
 75                 }
 76                  /// 处理表头自动排序的超链接
 77                   while (i < GVSearchResult.Columns.Count)
 78                 {
 79                     GVSearchResult.Columns[i].SortExpression =  "";
 80                     i++;
 81                 }
 82 
 83                  this.GVSearchResult.AllowPaging =  false;  //  将有分页的GridView中的数据全部导出到Excel
 84                  Common.ExcelHelper exhelp =  new Common.ExcelHelper();
 85                 ExcelBind();
 86                 exhelp.Export( this,  " application/ms-excel ", FileName, GVSearchResult);
 87             }   
 88 
 89         }
 90          ///   <summary>
 91           ///  导出EXCEL数据源
 92           ///   </summary>
 93           private  void ExcelBind()
 94         {
 95              string sql =  " select ID,CompanyName,CompanyPerson,CompanyAddress,CompanyType, ProductName,ProductType,ApprovedDate,validDate,BAK1,ProductStandard=substring(ProductStandard,0,LEN(ProductStandard)),DesignSpecifications=substring(DesignSpecifications,0,LEN(DesignSpecifications)),BAK2=substring(BAK2,0,LEN(BAK2)),CompanyPerson,LinkMan,LinkMobile,YearCapacity,ScopeCompanyPerson,ScopeLinkMan,ScopeLinkMobile from T_MaterialBackUp where YEAR(ApprovedDate) =  " + ddlYear.SelectedValue +  "  and MONTH(ApprovedDate) between  " + ddlMonth.SelectedValue +  "  and  " + ddlMonth2.SelectedValue +  "  and datediff(day, getdate(),validDate)>0 or (ApprovedOption1='不同意' and YEAR(ApprovedDate) =  " + ddlYear.SelectedValue +  "  and MONTH(ApprovedDate) between  " + ddlMonth.SelectedValue +  "  and  " + ddlMonth2.SelectedValue +  " ) ";
 96             DataTable dt = Maticsoft.DBUtility.DbHelperSQL.Query(sql).Tables[ 0];
 97             CountRows = dt.Rows.Count;
 98             GVSearchResult.AllowPaging =  false;
 99             GVSearchResult.DataSource = dt;
100             GVSearchResult.DataBind();
101         }
102  ///   <summary>
103           ///  导出成EXECEL 业务层
104           ///   </summary>
105           ///   <param name="FileType"></param>
106           ///   <param name="FileName"></param>
107           ///   <param name="gridview"></param>
108           public   void Export(Page pagename, string FileType,  string FileName, GridView gridview)
109         {
110             pagename.Response.Clear();
111             pagename.Response.Buffer =  false;
112 
113             pagename.Response.Charset =  " GB2312 ";
114 
115             pagename.Response.AppendHeader( " Content-Disposition ",  " attachment;filename= " + HttpUtility.UrlEncode(FileName, Encoding.UTF8).ToString());
116             pagename.Response.ContentEncoding = System.Text.Encoding.Default;
117             pagename.Response.ContentType =  " application/ms-excel ";
118             pagename.Response.Write( " <meta http-equiv=Content-Type content=\"text/html; charset=GB2312\"> ");
119 
120             pagename.EnableViewState =  false;
121             StringWriter tw =  new StringWriter();
122             HtmlTextWriter hw =  new HtmlTextWriter(tw);
123             gridview.RenderControl(hw);
124             pagename.Response.Write(tw.ToString());
125             pagename.Response.End();
126         }

 

转载于:https://www.cnblogs.com/mikehhs/archive/2011/12/05/2276867.html