Announcement

Wednesday, 12 February 2014

Export DataTable to Excel without dll

You can simply export DataTable to Excel file without using dll provided that Microsoft Office is installed.

Here is the code:-

Function ExportToExcel() expects a parameter of type DataTable which contains required information
that is to be converted into excel.


private void ExportToExcel(DataTable table)
        {
            HttpContext.Response.Clear();
            HttpContext.Response.ClearContent();
            HttpContext.Response.ClearHeaders();
            HttpContext.Response.Buffer = true;
            HttpContext.Response.ContentType = "application/ms-excel";
            HttpContext.Response.Write(@"<!DOCTYPE HTML PUBLIC ""-//W3C//DTD HTML 4.0 Transitional//EN"">");
            //Set the name of report as per query fired by the user.
            HttpContext.Response.AddHeader("Content-Disposition", "attachment;filename=Reports.xls");

            HttpContext.Response.Charset = "utf-8";
            HttpContext.Response.ContentEncoding = System.Text.Encoding.GetEncoding("windows-1250");
            //sets font
            HttpContext.Response.Write("<font style='font-size:10.0pt; font-family:Calibri;'>");
            HttpContext.Response.Write("<BR><BR><BR>");
            //sets the table border, cell spacing, border color, font of the text, background, foreground, font height
            HttpContext.Response.Write("<Table border='1' bgColor='#ffffff' " +
              "borderColor='#000000' cellSpacing='0' cellPadding='0' " +
              "style='font-size:10.0pt; font-family:Calibri; background:white;'> <TR>");
            //am getting my grid's column headers
            //int columnscount = GridView_Result.Columns.Count;
            int columnscount = table.Columns.Count;
            for (int j = 0; j < columnscount; j++)
            {   //write in new column
                HttpContext.Response.Write("<Td>");
                //Get column headers  and make it as bold in excel columns
                HttpContext.Response.Write("<B>");
                //HttpContext.Response.Write(GridView_Result.Columns[j].HeaderText.ToString());
                HttpContext.Response.Write(table.Columns[j].ToString());
                HttpContext.Response.Write("</B>");
                HttpContext.Response.Write("</Td>");
            }
            HttpContext.Response.Write("</TR>");
            foreach (DataRow row in table.Rows)
            {   //write in new row
                HttpContext.Response.Write("<TR>");
                for (int i = 0; i < table.Columns.Count; i++)
                {
                    HttpContext.Response.Write("<Td>");
                    HttpContext.Response.Write(row[i].ToString());
                    HttpContext.Response.Write("</Td>");
                }
                HttpContext.Response.Write("</TR>");
            }
            HttpContext.Response.Write("</Table>");
            HttpContext.Response.Write("</font>");
            HttpContext.Response.Flush();
            HttpContext.Response.End();
        }

No comments: