mvark/DownloadAsExcelWithHeaderFooter.cs

## DownloadAsExcelWithHeaderFooter.cs
<%@ Page Language="C#" %>
<script runat="server">
    void Page_Load(Object Src, EventArgs E)
    {
        try
        {
            System.Data.DataTable workTable = new System.Data.DataTable();
            workTable.TableName = "Customers";
            workTable.Columns.Add("Id");
            workTable.Columns.Add("Name");
            System.Data.DataRow workRow;

            for (int i = 0; i <= 9; i++)
            {
                workRow = workTable.NewRow();
                workRow[0] = i;
                workRow[1] = "CustName" + i.ToString();
                workTable.Rows.Add(workRow);
            }
            string strBody = DataTable2ExcelString(workTable);
            Response.AppendHeader("Content-Type", "application/vnd.ms-excel");
            Response.AppendHeader("Content-disposition", "attachment; filename=my.xls");
            Response.Write(strBody);
        }
        catch(Exception ex)
        {
            Response.Write(ex.ToString());
        }
    }

    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]-->");
        //@page definition is used to store document layout settings for the entire document.
        //The line below will add a header & footer to the downloaded Excel sheet.
        sbTop.Append(@"<style>
                        @page
                        {
                        mso-header-data:'&R Date: &D Time: &T';
                        mso-footer-data:'&L Proprietary & Confidential &R Page &P of &N';
                        }
                        </style>"
                      );
        sbTop.Append("</head><body><table>");
        string bottom = "</table></body></html>";
        StringBuilder sb = new StringBuilder();
        //Build the body
        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>" + dt.Rows[x][i] + "</td>");
            }
            sb.Append("</tr>");
        }

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

        return SSxml;
    }
</script>
	<%@ Page Language="C#" %>
	<script runat="server">
	void Page_Load(Object Src, EventArgs E)
	{
	try
	{
	System.Data.DataTable workTable = new System.Data.DataTable();
	workTable.TableName = "Customers";
	workTable.Columns.Add("Id");
	workTable.Columns.Add("Name");
	System.Data.DataRow workRow;

	for (int i = 0; i <= 9; i++)
	{
	workRow = workTable.NewRow();
	workRow[0] = i;
	workRow[1] = "CustName" + i.ToString();
	workTable.Rows.Add(workRow);
	}
	string strBody = DataTable2ExcelString(workTable);
	Response.AppendHeader("Content-Type", "application/vnd.ms-excel");
	Response.AppendHeader("Content-disposition", "attachment; filename=my.xls");
	Response.Write(strBody);
	}
	catch(Exception ex)
	{
	Response.Write(ex.ToString());
	}
	}

	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]-->");
	//@page definition is used to store document layout settings for the entire document.
	//The line below will add a header & footer to the downloaded Excel sheet.
	sbTop.Append(@"<style>
	@page
	{
	mso-header-data:'&R Date: &D Time: &T';
	mso-footer-data:'&L Proprietary & Confidential &R Page &P of &N';
	}
	</style>"
	);
	sbTop.Append("</head><body><table>");
	string bottom = "</table></body></html>";
	StringBuilder sb = new StringBuilder();
	//Build the body
	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>" + dt.Rows[x][i] + "</td>");
	}
	sb.Append("</tr>");
	}

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

	return SSxml;
	}
	</script>