CODEWITHSUNDEEP
c#asp.netexcelgridview
Antonio Mailtraq
Antonio Mailtraq

Reputation: 1407

Export multiple gridviews to multiple excel sheets

I have 3 gridviews in my website that I need to export to excel, but i need each gridview to appear in different work sheet.

This link Export GridView to multiple Excel sheet uses something quite similar Export multiple gridviews to multiple excel tabs (sheets)

For export multiple gridviews to multiples worksheets, I am using .NET and I have downloaded ClosedXML and also DocumentFormat.OpenXml.dll.

In the web application I don't have error, but the XLS output is empty.

Anybody know how can I resolve this?

Can you suggest any other method?

Thank you in advance.

Please check the code below.

protected void btnExportBoth_Click(object sender, EventArgs e)
{
    XLWorkbook wb = new XLWorkbook();
    GridView[] gvExcel = new GridView[] { gv1, gv2, gv3 };

    string[] name = new string[] { "gv1", "gv2", "gv3" };

    for (int i = 0; i < gvExcel.Length; i++)
    {
        if (gvExcel[i].Visible)
        {
            gvExcel[i].AllowPaging = false;
            gvExcel[i].DataBind();

            DataTable dt = new DataTable(name[i].ToString());
            for (int z = 0; z < gvExcel[i].Columns.Count; z++)
            {
                dt.Columns.Add(gvExcel[i].Columns[z].HeaderText);
            }

            foreach (GridViewRow row in gvExcel[i].Rows)
            {
                dt.Rows.Add();
                for (int c = 0; c < row.Cells.Count; c++)
                {
                    dt.Rows[dt.Rows.Count - 1][c] = row.Cells[c].Text;
                }
            }

            wb.Worksheets.Add(dt);
            gvExcel[i].AllowPaging = true;
        }
    }

    Response.Clear();
    Response.Buffer = true;
    Response.Charset = "";
    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    Response.AddHeader("content-disposition", "attachment;filename=Workbook_Name.xlsx");

    using (MemoryStream MyMemoryStream = new MemoryStream())
    {
        wb.SaveAs(MyMemoryStream);
        MyMemoryStream.WriteTo(Response.OutputStream);
        Response.Flush();
        Response.End();
    }
}

Upvotes: 0

Views: 1187

Answers (1)

Hamamelis
Hamamelis

Reputation: 2105

You are sure have populated the gridview on btnExportBoth_Click method ?

        gvExcel[i].AllowPaging = false;
        gvExcel[i].DataBind();

In btnExportBoth_Click method you must call the method to populate the gridview as on the web.

Upvotes: 1

Related Questions