Export to Excel. Browser setting?

Discussion in 'ASP .Net' started by Bill E., Sep 7, 2007.

  1. Bill E.

    Bill E. Guest

    I've used the following code countless times to successfully create an
    excel export from a datagrid.

    Protected Sub ExportToExcel(ByVal datagrid As Control)
    'Create an MS Excel Response
    Response.Clear()
    Response.Buffer = True
    Response.ContentType = "application/vnd.ms-excel"
    Response.Charset = ""

    Dim oStringWriter As System.IO.StringWriter = New
    System.IO.StringWriter
    Dim oHtmlTextWriter As System.Web.UI.HtmlTextWriter = New
    System.Web.UI.HtmlTextWriter(oStringWriter)

    datagrid.RenderControl(oHtmlTextWriter)
    Response.Write(oStringWriter.ToString)
    Response.End()
    End Sub

    However, for some reason on one particular project I'm only getting
    the first row of results
    when the browser returns with the response.

    If I comment out the line

    Response.ContentType = "application/vnd.ms-excel"

    I receive all rows. Is there a browser directive that I'm missing?

    Bill E.
    Hollywood, FL
     
    Bill E., Sep 7, 2007
    #1
    1. Advertisements

  2. Bill E.

    Guest Guest

    Hello,

    I have seen a sample on the web and the code used it's a bit different,
    try the sample, it should work fine:

    http://www.dotnetjohn.com/articles.aspx?articleid=231

    protected void btnExport_Click ( object sender, EventArgs e )

    {

    Response.Clear ( );

    Response.AddHeader ( "content-disposition",
    "attachment;filename=FileName.xls" );

    Response.Charset = "";



    Response.ContentType = "application/vnd.xls";

    StringWriter StringWriter = new System.IO.StringWriter ( );

    HtmlTextWriter HtmlTextWriter = new HtmlTextWriter ( StringWriter );

    gvToExport.RenderControl ( HtmlTextWriter );

    Response.Write ( StringWriter.ToString ( ) );

    Response.End ( );

    }

    To check for more resources about generating Excel from ASP .net:

    http://www.tipsdotnet.com/ArticleBlog.aspx?KWID=51&Area=Excel&PageIndex=0




    /// ------------------------------
    /// Braulio Diez
    ///
    /// http://www.tipsdotnet.com
    /// ------------------------------
     
    Guest, Sep 7, 2007
    #2
    1. Advertisements

  3. Bill E.

    Bill E. Guest

    Braulio,

    Thanks for the reply. There really is no difference between the code
    you posted and what I posted except the content type, which I don't
    believe is correct in your case.

    I discovered that the Response.End statement was the culprit. For
    whatever reason, the response was getting truncated by this, and only
    when the content type was Excel. I'll have to look at it in more
    depth because this has not happened before to me.

    Bill
     
    Bill E., Sep 7, 2007
    #3
  4. Bill E.

    Bill E. Guest

    I found the cause of the problem. The datagrid was populated by a
    DataSet based on a stored procedure. One of the columns returned by
    the stored procedure was a varchar type that contained a string
    representing an XML document. Therefore, each row contained an XML
    document in that column. I can only guess that the tags in these XML
    documents were confusing the browser. When I removed this column from
    the stored procedure, everything worked normally and I received a
    perfectly good Excel workbook with all data.

    Bill E.
    Hollywood, FL
     
    Bill E., Sep 20, 2007
    #4
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.