datagrid直接导出到excel和分页导出的区别
1.直接导出
private void btnCheckListPrint_Click(object sender, System.EventArgs e) { Response.Clear(); Response.Buffer= true; Response.Charset="GB2312"; Response.AppendHeader("Content-Disposition","attachment;filename=FileName.xls"); Response.ContentEncoding=System.Text.Encoding.GetEncoding("GB2312");//设置输出流为简体中文 Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。 this.EnableViewState = false; System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo("ZH-CN",true); System.IO.StringWriter oStringWriter = new System.IO.StringWriter(myCItrad); System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter); this.DataGrid1.RenderControl(oHtmlTextWriter); Response.Write(oStringWriter.ToString());///关键语句,html流写入excel Response.End(); }
2.分页导出
1/** <summary> 2 /// 把DataTable内容导出伟excel并返回客户端 3 /// </summary> 4 /// <param name="dgData">待导出的DataTable</param> 5 /// 创 建 人:陈文凯 6 /// 创建日期:2005年10月08日 7 /// 修 改 人: 8 /// 修改日期: 9 public static void DataTable2Excel(System.Data.DataTable dtData) 10 { 11 System.Web.UI.WebControls.DataGrid dgExport = null; 12 // 当前对话 13 System.Web.HttpContext curContext = System.Web.HttpContext.Current; 14 // IO用于导出并返回excel文件 15 System.IO.StringWriter strWriter = null; 16 System.Web.UI.HtmlTextWriter htmlWriter = null; 17 18 if (dtData != null) 19 { 20 // 设置编码和附件格式 21 curContext.Response.ContentType = "application/vnd.ms-excel"; 22 curContext.Response.ContentEncoding =System.Text.Encoding.UTF8; 23 curContext.Response.Charset = ""; 24 25 // 导出excel文件 26 strWriter = new System.IO.StringWriter(); 27 htmlWriter = new System.Web.UI.HtmlTextWriter(strWriter); 28 29 // 为了解决dgData中可能进行了分页的情况,需要重新定义一个无分页的DataGrid 30 dgExport = new System.Web.UI.WebControls.DataGrid(); 31 dgExport.DataSource = dtData.DefaultView; 32 dgExport.AllowPaging = false; 33 dgExport.DataBind(); 34 35 // 返回客户端 36 dgExport.RenderControl(htmlWriter); 37 curContext.Response.Write(strWriter.ToString()); 38 curContext.Response.End(); 39 } 40 }
也就是先把分页的功能赋值为false,在导出html流到excel。
还有的是直接导出到excel,让客户下载excel的。代码如下:
页面上就一个DataGrid和一个按钮,按钮用来触发将数据到Excel,后台的其他代码也就不弄上来了,下面是那个关键的按钮事件 程序代码:
private void Button1_Click(object sender, System.EventArgs e) { DataTable SourceTb = new DataTable(); DataColumn myDataColumn;
myDataColumn = new DataColumn(); myDataColumn.DataType = System.Type.GetType("System.String"); myDataColumn.ColumnName = "RowIndex"; //序 号 SourceTb.Columns.Add(myDataColumn);
myDataColumn = new DataColumn(); myDataColumn.DataType = System.Type.GetType("System.String"); myDataColumn.ColumnName = "CheckUpManName"; //审批人 SourceTb.Columns.Add(myDataColumn);
myDataColumn = new DataColumn(); myDataColumn.DataType = System.Type.GetType("System.String"); myDataColumn.ColumnName = "CheckUpIdeas"; //审批意见 SourceTb.Columns.Add(myDataColumn);
myDataColumn = new DataColumn(); myDataColumn.DataType = System.Type.GetType("System.String"); myDataColumn.ColumnName = "CheckUpdate"; //审批时间 SourceTb.Columns.Add(myDataColumn);
myDataColumn = new DataColumn(); myDataColumn.DataType = System.Type.GetType("System.String"); myDataColumn.ColumnName = "CheckUpRole"; //审批岗位 SourceTb.Columns.Add(myDataColumn);
myDataColumn = new DataColumn(); myDataColumn.DataType = System.Type.GetType("System.String"); myDataColumn.ColumnName = "HandleType"; //操作类型(1:提交| 9:驳回) SourceTb.Columns.Add(myDataColumn);
DataRow myDataRow; for(int i = 0;i < 30;i ++) { myDataRow = SourceTb.NewRow(); myDataRow["RowIndex"] = i.ToString(); myDataRow["CheckUpManName"] = "张三"; myDataRow["CheckUpIdeas"] = "同意"; myDataRow["CheckUpdate"] = "2006-03-20"; myDataRow["CheckUpRole"] = "物资部主任"; SourceTb.Rows.Add(myDataRow); }
//绑定数据到DataGrid1 this.DataGrid1.DataSource = SourceTb.DefaultView; this.DataGrid1.DataBind(); //将DataGrid1构成的html代码写进StringWriter this.DataGrid1.Page.EnableViewState = false; System.IO.StringWriter tw = new System.IO.StringWriter(); System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw); this.DataGrid1.RenderControl(hw); string HtmlInfo = tw.ToString().Trim();
string DocFileName = "审批信息.xls"; string FilePathName = Request.PhysicalPath; FilePathName = FilePathName.Substring(0,FilePathName.LastIndexOf("//")); //得到Excel文件的物理地址 FilePathName = FilePathName +"//" + DocFileName; System.IO.File.Delete(FilePathName); FileStream Fs = new FileStream(FilePathName, FileMode.Create); BinaryWriter BWriter= new BinaryWriter(Fs,System.Text.Encoding.GetEncoding("GB18030")); //将DataGrid的信息写入Excel文件 BWriter.Write(HtmlInfo); BWriter.Close(); Fs.Close(); }
好了,写入信息到Excel文件成功了,至于说生成的Excel文件在哪里,大家一看就应该知道,要下载的话,也就是很简单的<a href='Excel文件路径'>文件下载</a>咯
|