Saturday, September 3, 2011

ASP.NET DataTable To Excel


   EXPORT DATA FROM DATATABLE TO EXCEL IN ASP.NET
VB - Code

Public Sub ExportExcel(ByVal Datadt As DataTable)
        Dim dt as new datatable
        dt = Datadt       
        Dim attachment As String = "attachment; filename=" & System.DateTime.Now.[Date].ToShortDateString() & "_SalesReport.xls"
        Response.ClearContent()
        Response.AddHeader("content-disposition", attachment)
        Response.ContentType = "application/vnd.ms-excel"
        Dim tab As String = ""
        For Each dc As DataColumn In dt.Columns
            Response.Write(tab + dc.ColumnName)
            tab = vbTab
        Next
        Response.Write(vbLf)
        Dim i As Integer
        For Each dr As DataRow In dt.Rows
            tab = ""
            For i = 0 To dt.Columns.Count - 1
                Response.Write(tab & dr(i).ToString())
                tab = vbTab
            Next
            Response.Write(vbLf)
        Next
        Response.[End]()
    End Sub

C# - Code

    public void ExportExcel(DataTable Datadt)
    {
        DataTable dt = new DataTable();
        dt = Datadt;
        string attachment = "attachment; filename="+System.DateTime.Now.Date.ToShortDateString()+"_SalesReport.xls";
        Response.ClearContent();
        Response.AddHeader("content-disposition", attachment);
        Response.ContentType = "application/vnd.ms-excel";
        string tab = "";
        foreach (DataColumn dc in dt.Columns)
        {
            Response.Write(tab + dc.ColumnName);
            tab = "\t";
        }
        Response.Write("\n");
        int i;
        foreach (DataRow dr in dt.Rows)
        {
            tab = "";
            for (i = 0; i < dt.Columns.Count; i++)
            {
                Response.Write(tab + dr[i].ToString());
                tab = "\t";
            }
            Response.Write("\n");
        }
        Response.End();
    }