Export a Dataset to Excel & preserve number formatting for numeric values

Export a Dataset to Excel & preserve number formatting for numeric values
This code sample is based on Daniel Olson's DataTable2ExcelString (DataSet) method. View Demo

<%@ Page Language="C#" %>
<script runat="server">
    void Page_Load(Object Src, EventArgs E)
    {
        //cook up a dummy DataTable to use data for exporting to Excel
            System.Data.DataTable workTable = new System.Data.DataTable();
            workTable.TableName = "Analysis";
            workTable.Columns.Add("Original");
            workTable.Columns.Add("Current");
            System.Data.DataRow workRow;
            
            //never mind the counter, we are just trying to get some numbers to format
            for (int i = 8; i <= 12; i++)
            {
                workRow = workTable.NewRow();
                workRow[0] = i;
                workRow[1] = i * 0.2;
                workTable.Rows.Add(workRow);
            }
            string strBody = DataTable2ExcelString(workTable);
            Response.AppendHeader("Content-Type", "application/vnd.ms-excel");
            Response.AppendHeader("Content-disposition", "attachment; filename=numberformatting.xls");
            Response.Write(strBody);
    }

    
    public string DataTable2ExcelString(System.Data.DataTable dt)
    {
        StringBuilder sbTop = new StringBuilder();
        sbTop.Append("<html xmlns:o=\"urn:schemas-microsoft-com:office:office\" 
	xmlns:x=\"urn:schemas-microsoft-com:office:excel\" ");
        sbTop.Append("xmlns=\"http://www.w3.org/TR/REC-html40\"><head>
	<meta http-equiv=Content-Type content=\"text/html; charset=windows-1252\">");
        sbTop.Append("<meta name=ProgId content=Excel.Sheet>
	<meta name=Generator content=\"Microsoft Excel 9\"><!--[if gte mso 9]>");
        sbTop.Append("<xml><x:ExcelWorkbook><x:ExcelWorksheets>
	<x:ExcelWorksheet><x:Name>" + dt.TableName + "</x:Name><x:WorksheetOptions>");
        sbTop.Append("<x:Selected/><x:ProtectContents>False</x:ProtectContents>
	<x:ProtectObjects>False</x:ProtectObjects>");
        sbTop.Append("<x:ProtectScenarios>False</x:ProtectScenarios></x:WorksheetOptions>
	</x:ExcelWorksheet></x:ExcelWorksheets>");
        sbTop.Append("<x:ProtectStructure>False</x:ProtectStructure>
	<x:ProtectWindows>False</x:ProtectWindows></x:ExcelWorkbook></xml>");
        sbTop.Append("<![endif]--></head><body><table>");
        string bottom = "</table></body></html>";
        StringBuilder sb = new StringBuilder();
        //Header
        sb.Append("<tr>");
        for (int i = 0; i < dt.Columns.Count; i++)
        {
            sb.Append("<td>" + dt.Columns[i].ColumnName + "</td>");
        }
        sb.Append("</tr>");

        //Items
        for (int x = 0; x < dt.Rows.Count; x++)
        {
            sb.Append("<tr>");
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                sb.Append(@"<td style='mso-number-format:000\.00'>" + dt.Rows[x][i] + "</td>");
            }
            sb.Append("</tr>");
        }

        string SSxml = sbTop.ToString() + sb.ToString() + bottom;

        return SSxml;
    }
</script>

For a more detailed explanation of the code above, check this article: "Download as Word/Excel"

Code formatted with JTidy.de

'Anil' Radhakrishna's Code Gallery