Excel Format for Export in ASP.NET

 Recently I found this solution hope can help to others. I have a report that is created with GridViews and the client ask for functionality to export it to Excel. In some cases the report was giving different sum results in Excel as in the page. That is because when you export to Excel from ASP.NET you don't have control of the format and some values were not recognized as numbers. I didn't know that you can specify that with CSS. This was the solution

protected void Button1_ServerClick(object sender, EventArgs e)

            Response.Clear();

            Response.AddHeader("content-disposition", "attachment;filename=" + Request.QueryString[2].ToString() + "_"          +       DateTime.Now.ToString("dd-MMM-yy").ToString() + ".xls");

            Response.Charset = "";

            Response.ContentEncoding = Encoding.UTF7;

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

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

            System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);

 

            htmlWrite.WriteLine(@"<style>.number {mso-number-format:0\.00; } </style>");

            Panel p1 = (Panel)frmResults.FindControl("pnlForm");

            p1.RenderControl(htmlWrite);

           

            Response.Write(stringWrite.ToString());

            Response.End();

 

        }

In the code above is to do the export. Look at this code
htmlWrite.WriteLine(@"<style>.number {mso-number-format:0\.00; } </style>");

here I'm adding to the writer for render a css class that specifies mso-number-format that is format for numbers in Microsoft Office Applications

Then in the Databound event handler I linked the class to the cells I need to format

protected void grdValues_DataBound(object sender, EventArgs e)

        {

            foreach(GridViewRow gvr in grdAward.Rows)

            {

                if (gvr.RowType == DataControlRowType.DataRow)

                {

                    gvr.Cells[10].Attributes.Add("class", "number");

                    gvr.Cells[11].Attributes.Add("class", "number");

                    gvr.Cells[12].Attributes.Add("class", "number");

                    gvr.Cells[13].Attributes.Add("class", "number");

                }

            }

        }