FormView to Excel - How to pull data from controls in template to place in cells.

Question

Here is the layout, I have a FormView control with dynamic data coming in from a SQL server. I am using the ReadOnly mode of the FormView. How can I tweak the PrepareFormViewForExport method to remove the textboxs and leave the values in the FormView behind? Right now the output is showing labels but leaving out the values from the textboxs.

This is the C# code when the export button is clicked.

protected void Bexport_Click(object sender, EventArgs e)
{
    //Clear the Response object
    Response.Clear();
    //set Response header to the Excel filename required (.xls for excel, .doc for word)
    Response.AddHeader("content-disposition", "attachment;filename=ReportOuput.xls");
    Response.Charset = "";
    // If you want the option to open the Excel 
    // file without the save option then un-comment out the line below
    //Response.Cache.SetCacheability(HttpCacheability.NoCache);
    //FOR EXCEL 
    Response.ContentType = "application/vnd.xls";
    //FOR WORD
    //Response.ContentType = "application/vnd.doc";
    //Declare new stringwriter and html writer
    StringWriter stringWrite = new System.IO.StringWriter();
    HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
    //Strip out controls from FormView ?takes data too?
    PrepareFormViewForExport(FormView2);
    //render controls data as HTML
    FormView2.RenderControl(htmlWrite);      
    //Clean out the Javascript postbacks etc.
    string html = stringWrite.ToString();
    html = Regex.Replace(html, "</?(a|A).*?>", "");
    StringReader reader = new StringReader(html);
    //Write xls document
    Response.Write(html);
    //end current Response
    HttpContext.Current.Response.End();

}

Here is the method for removing controls from the FormView before rendering it for export.

private void PrepareFormViewForExport(Control fv)
{
    LinkButton lb = new LinkButton();
    Literal l = new Literal();
    string name = String.Empty;
    TextBox TB = new TextBox();

    for (int i = 0; i < fv.Controls.Count; i++)
    {
        if (fv.Controls[i].GetType() == typeof(LinkButton))
        {
            l.Text = (fv.Controls[i] as LinkButton).Text;
            fv.Controls.Remove(fv.Controls[i]);
            fv.Controls.AddAt(i, l);
        }
        else if (fv.Controls[i].GetType() == typeof(TextBox))
        {
            l.Text = (fv.Controls[i] as TextBox).Text;
            fv.Controls.Remove(fv.Controls[i]);
            fv.Controls.AddAt(i, l);
        }
        else if (fv.Controls[i].GetType() == typeof(DropDownList))
        {
            l.Text = (fv.Controls[i] as DropDownList).SelectedItem.Text;
            fv.Controls.Remove(fv.Controls[i]);
            fv.Controls.AddAt(i, l);
        }
        else if (fv.Controls[i].GetType() == typeof(CheckBox))
        {
            l.Text = (fv.Controls[i] as CheckBox).Checked ? "True" : "False";
            fv.Controls.Remove(fv.Controls[i]);
            fv.Controls.AddAt(i, l);
        }
        if (fv.Controls[i].HasControls())
        {
            PrepareFormViewForExport(fv.Controls[i]);
        }
    }
}

Answer 1

try spread Sheet Gear third party component http://www.spreadsheetgear.com/

Answer 2

Well I found a workaround, not a true sollution. Since my formview is in ReadOnly I changed the controls from textboxs to labels. This makes the html writer dump the data into Excel instead of trying to copy textboxs over. I would still be interested to know if anyone knows how answer the original question however.