Excel download without MS Office inteop or any other 3rd party library, is inserting additional html markup in the generated xls file

Question

Hi I am developing a rich client UI on the web with jQuery and Microsoft ASP.net 2.0, C# as server side, I basically have 2 issues, listed here under

I am using HTMLTextWriter and StringWriter class libraries for that. What I have searched on the internet and learned from various tutorials and or tips thats the most affordable way to generate the excel sheets on the fly we just need to output the fabricated html in the response and set the required headers to tell the browser what kind of data is being transferred, and at that part everything is functioning fine like it should but While converting the html to the excel spread sheet it appends download page's html markup to the excel markup as well.
When I call Response.Close() after outputting the data, I get connection abort error.

Here's the code snippet

public StringWriter getHTMLStream(){

            output = new StringWriter();
            htmlWriter = new HtmlTextWriter(output);
            string html= @" 
      table {mso-displayed-decimal-separator:'\.'; mso-displayed-thousand-separator:'\,';}.xlGeneral {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:windowtext; font-size:10.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Arial; mso-generic-font-family:auto; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; mso-background-source:auto; mso-pattern:auto; white-space:nowrap;}     ";
            html+=""+this.headerName+"-"+this.vehicleName+"";
            totalCols = (this.reportOrientation == "Horiz")?data.Columns.Count:2;
            if (this.reportOrientation == "Horiz")
            {
                html+="";
                // Add Header Columns
                foreach (DataColumn colName in this.data.Columns)
                {
                    html+=""+colName.ColumnName.Trim()+"";
                }
                html+="";

                // Now Add Data
                foreach (DataRow dr in this.data.Rows)
                {
                    html+="";
                    foreach (string colData in dr.ItemArray)
                    {
                        html+=""+colData+"";
                    }
                    html+="";
                }
            }
            else
            {
                // Get the Columns
                colsArr = new string[this.data.Columns.Count];
                int colCounter = 0;
                foreach (DataColumn colName in this.data.Columns)
                {
                    colsArr[colCounter] = colName.ColumnName;
                    colCounter++;
                }
                //tableHeads = arrayToString(colsArr);
                // Get the row data in string array
                rowArr = new string[this.data.Columns.Count];

                foreach (DataRow dr in this.data.Rows)
                {
                    colCounter = 0;
                    foreach (string col in dr.ItemArray)
                    {
                        rowArr[colCounter] = col;
                        colCounter++;
                    }
                }
                //tableHeads = arrayToString(colsArr);
                // Add PDF Table Cells
                for (int i = 0; i "+colsArr[i].Trim()+""+rowArr[i].Trim()+"";
                }

            }
            html+= "";
            htmlWriter.Write(html);
            return output;
        }

        private string arrayToString(string[] array){
            string result = string.Join(",", array);
            result = result.Substring(0, result.Length - 1);
            return result;
        }
    }

And that's how I am accessing it

Response.Clear();
            createHTML speedXLS = new createHTML("Vehicle Speed Report", "Horiz", vehicleName, speedDT);
            Response.ContentType = "application/vnd.xls";
            Response.AddHeader("content-disposition", "attachment;filename=VSpeedReport-" + vehicleName + ".xls");
            Response.Write(speedXLS.getHTMLStream().ToString());
            //Response.Close();

And here is whats being appended to my excel markup the download page's markup beside the wanted xls markup. How can I tell the server not to append the page's markup in the response body? **

    <html xmlns="http://www.w3.org/1999/xhtml">
    <head><title>

    </title></head>
    <body>
        <form name="form1" method="post" action="downloadData.aspx?reportType=speed&amp;value=1&amp;valUnit=Miles&amp;reqType=XLS&amp;selVehicle=GTA02&amp;Email=gps%40gtalimo.com&amp;startDate=2012-4-27+23%3a21%3a00&amp;endDate=2012-4-28+01%3a21%3a00&amp;vehicleName=GTA+07" id="form1">
    <div>
    <input type="hidden" name="__VIEWSTATE" id="__VIEWSTATE" value="/wEPDwULLTE2MTY2ODcyMjlkZKkkaxju0wAdoc1rGUJiLkzjS9eU" />
    </div>

        <div>

        </div>
        </form>
    </body>
    </html>

** Comments or suggestion please! Thanks!

Gregory Nozik Gregory Nozik · Accepted Answer · 2012-04-28T07:14:42

1. Instead of

       **strong text** Response.Clear();
createHTML speedXLS = new createHTML("Vehicle Speed Report", "Horiz", vehicleName, speedDT);
Response.ContentType = "application/vnd.xls";
Response.AddHeader("content-disposition", "attachment;filename=VSpeedReport-" + vehicleName + ".xls");
Response.Write(speedXLS.getHTMLStream().ToString());

Use

 **Response.Buffer = true;**

   createHTML speedXLS = new createHTML("Vehicle Speed Report", "Horiz", vehicleName, speedDT);
        Response.ContentType = "application/vnd.xls";
        Response.AddHeader("content-disposition", "attachment;filename=VSpeedReport-" + vehicleName + ".xls");
        Response.Write(speedXLS.getHTMLStream().ToString());

When you are using streaming class , use "using" statement.

Excel download without MS Office inteop or any other 3rd party library, is inserting additional html markup in the generated xls file

2 Answers