GridView导出EXCEL代码如何把gridview的数据导出excel
一,导出当前页显示的数据方法。
假如gridview1是分页显示数据,双击 btnCurrentToExcel按钮,编写代码
protected void btnOut_Click(object sender, EventArgs e)
{
Response.Clear();
Response.Buffer = true;
Response.Charset = "GB2312";
Response.AppendHeader("Content-Disposition", "attachment;filename=CellsList.xls");
// 如果设置为 GetEncoding("GB2312");导出的文件将会出现乱码!!!
Response.ContentEncoding = System.Text.Encoding.UTF7;
Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。
System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
this.GridView1.RenderControl(oHtmlTextWriter);
Response.Output.Write(oStringWriter.ToString());
Response.Flush();
Response.End();
}
public override void VerifyRenderingInServerForm(Control control)
{ }
二.假如我想把分页显示的gridview中的全部数据打出来,怎么做?
开始我也不知道,其实我们可以做一个层,设为hidden(不设也可以),层里放另外的gridview2(不分页),双击时,gridview2才绑定,双击btnToExcel,编写代码
protected void btnToExcel_Click(object sender, EventArgs e)
{
DataTable Dt = News.GetAllNewslist();
this.GridView2.DataSource = Dt;
this.GridView2.DataBind();
Response.Clear();
Response.Buffer = true;
Response.Charset = "GB2312";
Response.AppendHeader("Content-Disposition", "attachment;filename=ExcelFileName.xls");
Response.ContentEncoding = System.Text.Encoding.UTF7;
Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。
System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
this.GridView2.RenderControl(oHtmlTextWriter);
Response.Output.Write(oStringWriter.ToString());
Response.Flush();
Response.End();
}
全部打印:
protected void btPortOut_Click(object sender, EventArgs e)
{
// PWCommon.PwCls.PwClsInstance.ExcelPortOut(this.GridView);
Response.Clear();
string rowData = "";
string columnName = "";
System.IO.StringWriter sw = new System.IO.StringWriter();
DataTable vTable = null;
vTable = (DataTable)Session["dldy"];
columnName = "所属区局\t供电所\t馈线名称\t台区GIS号\t台区名称\t安装地址\t变压器容量(kVA)\t最大负载率(%)\t最大负载率发生时间\tA相电流(A)\tB相电流(A)\tC相电流(A)\t";
sw.WriteLine(columnName);
foreach (DataRow dr in vTable.Rows)
{
rowData = "";
System.Text.StringBuilder rowDatasb = new System.Text.StringBuilder();//2009-7-6,优化rowData
foreach (DataColumn dc in vTable.Columns)
{
switch (dc.ColumnName.ToString().ToLower())
{
case "gdjname":
rowDatasb.Append(dr[dc.ColumnName] + "\t");
break;
case "bdzname":
rowDatasb.Append(dr[dc.ColumnName] + "\t");
break;
case "linename":
rowDatasb.Append(dr[dc.ColumnName] + "\t");
break;
case "transformerid":
rowDatasb.Append(dr[dc.ColumnName] + "\t");
break;
case "transformername":
rowDatasb.Append(dr[dc.ColumnName] + "\t");
break;
case "setaddress":
rowDatasb.Append(dr[dc.ColumnName] + "\t");
break;
case "transformersize":
rowDatasb.Append(dr[dc.ColumnName] + "\t");
break;
case "datasum":
rowDatasb.Append(dr[dc.ColumnName] + "\t");
break;
case "maxdate":
rowDatasb.Append(dr[dc.ColumnName] + "\t");
break;
case "dataa":
rowDatasb.Append(" " + dr[dc.ColumnName] + "\t");
break;
case "datab":
rowDatasb.Append(" " + dr[dc.ColumnName] + "\t");
break;
case "datac":
rowDatasb.Append(dr[dc.ColumnName] + "\t");
break;
}
}
rowData = rowDatasb.ToString();
sw.WriteLine(rowData.Substring(0, rowData.Length - 1));
}
sw.Close();
Response.AddHeader("Content-Disposition", "attachment; filename=cb.xls");
Response.ContentType = "application/ms-excel";
Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
Response.Write(sw);
Response.End();
}
三。
public void ExcelPortOut(System.Web.UI.WebControls.GridView rp1)
{
StringBuilder sb = new StringBuilder();
StringWriter sw = new StringWriter(sb);
HtmlTextWriter htw = new HtmlTextWriter(sw);
Page page = new Page();
HtmlForm form = new HtmlForm();
// Deshabilitar la validación de eventos, sólo asp.net 2
page.EnableEventValidation = false;
// Realiza las inicializaciones de la instancia de la clase Page que requieran los diseñadores RAD.
page.DesignerInitialize();
page.Controls.Add(form);
form.Controls.Add(rp1);
page.RenderControl(htw);
Response.Clear();
Response.Buffer = true;
Response.ContentType = "applicationnd.ms-excel";
Response.AddHeader("Content-Disposition", "attachment;filename=data.xls");
Response.Charset = "UTF-8";
Response.ContentEncoding = Encoding.Default;
string html = sb.ToString();
html = Regex.Replace(html, @"\<a[^\>]*\>", " ");
html = Regex.Replace(html, @"\</a\>", " ");
html = Regex.Replace(html, @"\<table[^\>]*\>", "<table cellpadding='0' cellspacing='0' border='1'");
html = Regex.Replace(html, @"\<tr[^\>]*\>", "<tr> ");
html = Regex.Replace(html, @"\<td[^\>]*\>", "<td style=\"vnd.ms-excel.numberformat:@\"> ");
Response.Write(html);
Response.End();
}
protected void lkb_Excel_Click(object sender, EventArgs e)
{
DataGrid1.EnableViewState = false;
foreach (DataGridItem dgi in DataGrid1.Items)
{
Label lbl = dgi.Cells[1].FindControl("Label1") as Label;
if (lbl != null)
{
lbl.Text = string.Format("=TEXT({0},\"000000\")", lbl.Text);
}
else
{
break;
}
}
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.Buffer = true;
HttpContext.Current.Response.Charset = "GB2312";
HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=FileName.xls");
// 经测试如果设置为 GetEncoding("GB2312"),导出的文件将会出现乱码。
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
//设置输出文件类型为excel文件。
HttpContext.Current.Response.ContentType = "application/ms-excel";
System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
this.DataGrid1.RenderControl(oHtmlTextWriter);
HttpContext.Current.Response.Output.Write(oStringWriter.ToString());
HttpContext.Current.Response.Flush();
HttpContext.Current.Response.End();
DataGrid1.EnableViewState = true;
}
public override void VerifyRenderingInServerForm(Control control)
{
//base.VerifyRenderingInServerForm(control);
}
}