Gridview数据导出到excel中的asp.net

本文关键字：中的 asp net excel 数据 Gridview | 更新日期: 2023-09-27 18:07:28

我试图将网格视图数据传输到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();
}

Gridview数据导出到excel中的asp.net

您的工作表是空白的，因为您的字符串写入器为null。以下内容可能有所帮助

System.Web.UI.HtmlTextWriter htmlWrite =
    new HtmlTextWriter(stringWrite);
    GridView1.RenderControl(htmlWrite);

这是完整的代码

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();
}

可能是导出excel中数据绑定有问题。检查数据是否正确地放入gridview

使用此代码在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();
    }

我想这会对你有帮助。

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();

与其这样做…你能不能用一种更简单的方法，如下所示? 

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();

您可以在这里获得完整的演练

其他要检查的是确保viewstate是on(我昨天刚刚解决了这个问题)。如果你没有打开viewstate, gridview将是空白的，直到你再次加载它。

最好的方法是使用closexml。以下是参考链接

https://closedxml.codeplex.com/wikipage?title=Adding%20DataTable%20as%20Worksheet& referringTitle =文档

，你可以简单地使用

    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();

我没有这里有任何DataSource对应gridview
虽然你有DataBind在你的代码

gvdetails.DataBind();
相关文章：