ASP.NET: Proper way of exporting GridView to Excel including dynamically added rows

Question

I've been attempting to implement a simple method for exporting GridViews to Excel sheets. However, I'm stuck on this one for quite a while now:

My GridView has AutoGenerateColumns set to true, as the datasource is dynamic and not really predictable (changing number of columns over time). The HeaderRow it automatically creates is not suitable for me, hence I've also set ShowHeader to false and created my own in OnRowCreated event, like this:

if (e.Row.RowType == DataControlRowType.Header) {

        gv.Controls[0].Controls.AddAt(0, GetHeader());
        gv.Controls[0].Controls.AddAt(1, GetSubHeader());


}

where GetHeader() and GetSubHeader() both return a GridViewRow (there are two rows in the header using different colspans, rowspans and stuff). This all works nicely on the website, however, when I try to export the GridView using OpenXML like this:

    ExcelPackage excel = new ExcelPackage();
    var workSheet = excel.Workbook.Worksheets.Add("List 1");
    var totalCols = gv.Rows[0].Cells.Count;
    var totalRows = gv.Rows.Count;
    var headerRow = gv.HeaderRow;

    for (int i = 0; i < totalCols; i++) {

        workSheet.Cells[1,i+1].Value = headerRow.Cells[i].Text;

    }

    for (int i = 0; i < totalCols; i++) {

        for (int j = 0; j < totalRows; j++) {

            workSheet.Cells[j+2,i+1].Value = gv.Rows[j].Cells[i].Text;

        }

    }

    return excel;

what I get is a table with the original HeaderRow that is hidden - I've figured I should skip the first cycle obviously - and then the rows with actual data. No trace of the two rows I added in OnRowCreated.

Could anybody please explain why this is happening and how I can work this out? The method should ideally always export the entire GridView as it is displayed on the website.

Note: The other way using HtmlTextWriter and GridView.RenderControl() is probably not an option since I wasn't even able to open the file in Excel after downloading (OpenOffice did fine though) and then I read somewhere that the sheets aren't actually usable for data processing, which in this case is crucial.

EDIT: After I added the two HeaderRows in OnRowDataBound instead of OnRowCreated, the loop accounts for them, however all that gets into the worksheet are blank fields and it also seems that they are not taken into account by gv.Rows.Count, as the loop never gets to the last two rows in my table.

I tried debugging and and indeed all that is returned by gv.Rows[j].Cells[i].Text; is a "", even though the cells have their Text property set up AND the table displays OK on the website.

I find this behavior very confusing, I guess I'm missing something big. Could anybody tell my why this is happening?

Answer 1

Doesn't matter if it is autogenerated or otherwise. The trick is to treat everything as an object and a child of an object. Then using the object's properties (and also Excel's), you can then do the manipulation of the data in the gridview.

See sample code below

protected void ExportToExcel(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";
    using (StringWriter sw = new StringWriter())
    {
        HtmlTextWriter hw = new HtmlTextWriter(sw);
        //To Export all pages
        GridView1.AllowPaging = false;
        this.BindGrid();
        GridView1.HeaderRow.BackColor = Color.White;
        foreach (TableCell cell in GridView1.HeaderRow.Cells)
        {
            cell.BackColor = GridView1.HeaderStyle.BackColor;
        }
        foreach (GridViewRow row in GridView1.Rows)
        {
            row.BackColor = Color.White;
            foreach (TableCell cell in row.Cells)
            {
                if (row.RowIndex % 2 == 0)
                {
                    cell.BackColor = GridView1.AlternatingRowStyle.BackColor;
                }
                else
                {
                    cell.BackColor = GridView1.RowStyle.BackColor;
                }
                cell.CssClass = "textmode";
            }
        }
        GridView1.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();
    }
}

This is just one of the ways to go.

There are many other samples. Just google Gridview to Excel.

Hope this helps.