C# 在asp.net中将gridview数据导出到excel
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15832339/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
gridview data export to excel in asp.net
提问by Vikash Chauhan
i tried to transfer grid view data to excel .... But the output is a blank excel sheet.How to solve this problem?Is there any code to transfer grid view value to excel sheet with data base?
我试图将网格视图数据传输到excel ....但输出是一个空白的excel表。如何解决这个问题？有没有代码可以将网格视图值传输到带有数据库的excel表？
protected void btnexcel_Click1(object sender, EventArgs e)
{
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.xls");
Response.Charset = "";
Response.ContentType = "application/vnd.ms-excel";
StringWriter sw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(sw);
gvdetails.AllowPaging = false;
gvdetails.DataBind();
gvdetails.HeaderRow.Style.Add("background-color", "#FFFFFF");
gvdetails.HeaderRow.Cells[0].Style.Add("background-color", "green");
gvdetails.HeaderRow.Cells[1].Style.Add("background-color", "green");
gvdetails.HeaderRow.Cells[2].Style.Add("background-color", "green");
for (int i = 0; i < gvdetails.Rows.Count;i++ )
{
GridViewRow row = gvdetails.Rows[i];
row.BackColor = System.Drawing.Color.White;
row.Attributes.Add("class", "textmode");
if (i % 2 != 0)
{
row.Cells[0].Style.Add("background-color", "#C2D69B");
row.Cells[1].Style.Add("background-color", "#C2D69B");
row.Cells[2].Style.Add("background-color", "#C2D69B");
}
}
string style = @"<style> .textmode { mso-number-format:\@; } </style>";
Response.Write(style);
Response.Output.Write(sw.ToString());
Response.End();
}
采纳答案by Ratna
Your sheet is blank because your string writer in null. Here is what may help
您的工作表是空白的，因为您的字符串编写器为空。这是可能有帮助的
System.Web.UI.HtmlTextWriter htmlWrite =
new HtmlTextWriter(stringWrite);
GridView1.RenderControl(htmlWrite);
Here is the full code
这是完整的代码
protected void Button1_Click(object sender, EventArgs e)
{
Response.Clear();
Response.AddHeader("content-disposition", "attachment;
filename=FileName.xls");
Response.ContentType = "application/vnd.xls";
System.IO.StringWriter stringWrite = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlWrite =
new HtmlTextWriter(stringWrite);
GridView1.RenderControl(htmlWrite);
Response.Write(stringWrite.ToString());
Response.End();
}
回答by ????
I don't there there is any DataSourcefor the gridview
Though you have DataBindin your code as
我不存在有任何DataSource的gridview
你虽然 DataBind在你的代码
gvdetails.DataBind();
回答by Pradip
Instead of doing all these.. cant you use a simpler approach as shown below.
而不是做所有这些......你不能使用一个更简单的方法，如下所示。
Response.ClearContent();
Response.AddHeader("content-disposition", "attachment; filename=" + strFileName);
Response.ContentType = "application/excel";
System.IO.StringWriter sw = new System.IO.StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
gv.RenderControl(htw);
Response.Write(sw.ToString());
Response.End();
You can get the entire walkthrough here
您可以在此处获得整个演练
回答by Jeff B
Something else to check is make sure viewstate is on (I just solved this yesterday). If you don't have viewstate on, the gridview will be blank until you load it again.
要检查的其他事情是确保视图状态已打开（我昨天刚刚解决了这个问题）。如果您没有打开 viewstate，gridview 将是空白的，直到您再次加载它。
回答by Black Cloud
may be problem in data binding in export excel . check that data properly bin to a gridview or not.
可能是导出 excel 中数据绑定的问题。检查该数据是否正确装箱到 gridview。
Use this code for export grid view in excel sheet and note that you must add iTextSharp dll in you project.
使用此代码在 Excel 表中导出网格视图，并注意您必须在项目中添加 iTextSharp dll。
protected void btnExportExcel_Click(object sender, EventArgs e)
{
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.xls");
Response.Charset = "";
Response.ContentType = "application/vnd.ms-excel";
StringWriter sw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(sw);
GridView1.AllowPaging = false;
// Re-Bind data to GridView
using (CompMSEntities1 CompObj = new CompMSEntities1())
{
Start = Convert.ToDateTime(txtStart.Text);
End = Convert.ToDateTime(txtEnd.Text);
GridViewSummaryReportCategory.DataSource = CompObj.SP_Category_Summary(Start, End);
SP_Category_Summary_Result obj1 = new SP_Category_Summary_Result();
GridView1.DataBind();
GridView1.Visible = true;
ExportTable.Visible = true;
}
//Change the Header Row back to white color
GridView1.HeaderRow.Style.Add("background-color", "#FFFFFF");
GridView1.Style.Add(" font-size", "10px");
//Apply style to Individual Cells
GridView1.HeaderRow.Cells[0].Style.Add("background-color", "green");
GGridView1.HeaderRow.Cells[1].Style.Add("background-color", "green");
GridView1.HeaderRow.Cells[2].Style.Add("background-color", "green");
GridView1.HeaderRow.Cells[3].Style.Add("background-color", "green");
GridView1.HeaderRow.Cells[4].Style.Add("background-color", "green");
for (int i = 1; i < GridView1.Rows.Count; i++)
{
GridViewRow row = GridView1.Rows[i];
//Change Color back to white
row.BackColor = System.Drawing.Color.White;
//Apply text style to each Row
// row.Attributes.Add("class", "textmode");
//Apply style to Individual Cells of Alternating Row
if (i % 2 != 0)
{
row.Cells[0].Style.Add("background-color", "#C2D69B");
row.Cells[1].Style.Add("background-color", "#C2D69B");
row.Cells[2].Style.Add("background-color", "#C2D69B");
row.Cells[3].Style.Add("background-color", "#C2D69B");
row.Cells[4].Style.Add("background-color", "#C2D69B");
}
}
GridView1.RenderControl(hw);
//style to format numbers to string
string style = @"<style> .textmode { mso-number-format:\@; } </style>";
Response.Write(style);
Response.Output.Write(sw.ToString());
Response.Flush();
Response.End();
}
回答by Such
I think it will help you
我认为它会帮助你
string filename = String.Format("Results_{0}_{1}.xls", DateTime.Today.Month.ToString(), DateTime.Today.Year.ToString());
if (!string.IsNullOrEmpty(GRIDVIEWNAME.Page.Title))
filename = GRIDVIEWNAME.Page.Title + ".xls";
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + filename);
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
HttpContext.Current.Response.Charset = "";
System.IO.StringWriter stringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlWriter = new HtmlTextWriter(stringWriter);
System.Web.UI.HtmlControls.HtmlForm form = new System.Web.UI.HtmlControls.HtmlForm();
GRIDVIEWNAME.Parent.Controls.Add(form);
form.Controls.Add(GRIDVIEWNAME);
form.RenderControl(htmlWriter);
HttpContext.Current.Response.Write("<meta http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\" />");
HttpContext.Current.Response.Write(stringWriter.ToString());
HttpContext.Current.Response.End();
回答by Taran
The Best way is to use closedxml. Below is the link for reference
最好的方法是使用closedxml。下面是链接供参考
https://closedxml.codeplex.com/wikipage?title=Adding%20DataTable%20as%20Worksheet&referringTitle=文档
and you can simple use
你可以简单地使用
var wb = new ClosedXML.Excel.XLWorkbook();
DataTable dt = GeDataTable();//refer documentaion
wb.Worksheets.Add(dt);
Response.Clear();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment;filename=\"FileName.xlsx\"");
using (var ms = new System.IO.MemoryStream()) {
wb.SaveAs(ms);
ms.WriteTo(Response.OutputStream);
ms.Close();
}
Response.End();
