Export DataTable to MS Excel in ASP.NET

.NET framework has a interoperability service with MS office package. It works perfectly fine when i tried to export data to MS Excel sheet from a ASP.NET application but the problem was it's a time consuming. When you have thousands of data the process will take huge time to process.

To over come we can directly write the DataTable content into the Excel cells using Response.Write() by looping through the Excel columns and rows. This method will save huge amount of time for exporting data.


public static void DataTableToExcel(System.Data.DataTable dtExcel)
    {
     
        HttpContext context = HttpContext.Current;
        string attachment = "attachment; filename=pinDoc.xls";
        context.Response.ClearContent();
        context.Response.AddHeader("content-disposition", attachment);
        context.Response.ContentType = "application/vnd.ms-excel";
        string tab = "";
        foreach (DataColumn dc in dtExcel.Columns)
        {
            context.Response.Write(tab + dc.ColumnName);
            tab = "\t";
        }
        context.Response.Write("\n");
        int i;
        foreach (DataRow dr in dtExcel.Rows)
        {
            tab = "";
            for (i = 0; i < dtExcel.Columns.Count; i++)
            {
                context.Response.Write(tab + dr[i].ToString().Replace("\r\n", "").Replace("\n", ""));
                tab = "\t";
            }
            context.Response.Write("\n");
        }

        context.Response.Flush();
        context.Response.End();
    }