Export to excel from web page and data formating

Many time in commercial web sites client want to export data as excel sheet result correct with date time formatting and number decimal point formatting.

here is that kind of example.

string styles = "style> .amount { mso-number-format:0.00; } .exDate { mso-number-format: dd\\/mmm\\/yyyy; } /style> ";
//please write lessthan sign before "style" word


Table xl = new Table();
xl.BorderWidth = 1;
System.Web.UI.HtmlControls.HtmlForm form = new System.Web.UI.HtmlControls.HtmlForm();

string attachment = "attachment; filename=saActionTest.xls";

Response.ClearContent();
Response.AddHeader("content-disposition", attachment);
Response.ContentType = "application/ms-excel";

System.IO.StringWriter stw = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htextw = new System.Web.UI.HtmlTextWriter(stw);

TableRow trTitle = new TableRow();
TableCell tcTitle = new TableCell();
tcTitle.Text = "saAction Test";
trTitle.Cells.Add(tcTitle);

TableCell tcDate = new TableCell();
tcDate.CssClass = "exDate";
tcDate.Text = DateTime.Now.ToShortDateString();
trTitle.Cells.Add(tcDate);

double m1 = 150.50;
TableRow trTitle2 = new TableRow();
TableCell tcTitle2 = new TableCell();
tcTitle2.CssClass = "amount";
tcTitle2.Text = m1.ToString();
trTitle2.Cells.Add(tcTitle2);

xl.Rows.Add(trTitle);

xl.RenderControl(htextw);
xl.Dispose();

Response.Write(stw.ToString());
Response.End();


And the excel sheet result is :



You can see that there is also date, Now Some time many developer faces date problem MM/dd/yyy or dd/MM/yyyy etc. it depends on system's setting as default though you want to add your own date format you have to apply style "mso-number-format" in your code.

There are many other style you can apply to get correct date and number decimal points amount.

mso-number-format:"0" No Decimals
mso-number-format:"0\.00" 2 Decimals
mso-number-format:"mm\/dd\/yy" Date format
mso-number-format:"m\/d\/yy\ h\:mm\ AM\/PM" D -T AMPM
mso-number-format:"Short Date" 03/07/2009
mso-number-format:"Medium Date" 05-jan-2008
mso-number-format:"Short Time" 8:67
mso-number-format:"Medium Time" 8:67 am
mso-number-format:"Long Time" 8:67:25:00
mso-number-format:"Percent" Percent - two decimals
mso-number-format:"0\.E+00" Scientific Notation
mso-number-format:"\@" Text
mso-number-format:"\#\ ???\/???" Fractions - up to 3 digits (312/943)
mso-number-format:"\0022£\0022\#\,\#\#0\.00" £12.76
mso-number-format:"\#\,\#\#0\.00_ \;\[Red\]\-\#\,\#\#0\.00\ " 2 decimals, negative numbers in red and signed
(1.86 -1.66)
mso-number-format:"\\#\\,\\#\\#0\\.00_\\)\\;\\[Black\\]\\\\(\\#\\,\\#\\#0\\.00\\\\)" Accounting Format –5,(5)