Lucas Lucas - 4 months ago 34
ASP.NET (C#) Question

Export multiple gridviews to multiple excel tabs (sheets)

I have 6 gridviews in my website that I need to export to excel, but each one in one different sheet.

This link Export GridView to multiple Excel sheet uses something quite similar, but he is using datasets and I am not. I am new to C#, so I couldnt change it to fit my solution.

My code exports all gridviews to the same sheet. I created a loop to run throught my gridviews, now I need to put a code to generate each one to each excel sheet.

protected void btExcel_Click(object sender, EventArgs e)
{
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment;filename=FARPOP_Mensal_" + txDt.Text + ".xls");
Response.ContentEncoding = System.Text.Encoding.GetEncoding("Windows-1252");
Response.Charset = "ISO-8859-1";
Response.ContentType = "application/vnd.ms-excel";

using (StringWriter sw = new StringWriter())
{
HtmlTextWriter hw = new HtmlTextWriter(sw);
GridView[] gvExcel = new GridView[] { gvAnual, gvPorUF, gvPorFarmacia, gvPorMunicipio, gvPorPV, gvPorCNPJ };
for (int i = 0; i < gvExcel.Length; i++)
{
GridView gv = gvExcel[i];

//
// Need to redirect to each sheet here?
//

gvExcel[i].HeaderRow.BackColor = Color.White;
gvExcel[i].UseAccessibleHeader = false;
gvExcel[i].AllowPaging = false;
for (int x = 0; x < gvExcel[i].Columns.Count; x++)
{
gvExcel[i].Columns[x].Visible = true;
}

gvExcel[i].DataBind();
foreach (TableCell cell in gvExcel[i].HeaderRow.Cells)
{
cell.BackColor = Color.CadetBlue;
cell.Enabled = false;
}

foreach (GridViewRow row in gvExcel[i].Rows)
{
row.BackColor = Color.White;
foreach (TableCell cell in row.Cells)
{

cell.Controls.Clear();
if (row.RowIndex % 2 == 0)
{
cell.BackColor = Color.White;
}
else
{
cell.BackColor = Color.LightBlue;
}
cell.CssClass = "textmode";
}
}

gvExcel[i].RenderControl(hw);
}

//style to format numbers to string
string style = @"<style> .textmode { } </style>";
Response.Write(style);
Response.Output.Write(sw.ToString());
Response.Flush();
Response.End();
}
}

Answer

I followed maniak1982 comments and found a good solution.

So, if you need multiple gridviews to multiples worksheets, using .NET, you will need to download ClosedXML and also DocumentFormat.OpenXml.dll.

After referencing the dll, use the following code:

 protected void btExcel_Click(object sender, EventArgs e)
{
    XLWorkbook wb = new XLWorkbook();
    GridView[] gvExcel = new GridView[] { GridView1, GridView2, GridView3, GridView4, GridView5, GridView6};
    string[] name = new string[] { "Name1", "Name2", "Name3", "Name4", "Name5", "Name6" };
    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();
    }
}

It took me days of searching... But it's working perfectly. Be happy

Comments