September 20, 2012

Export data to Excel Using DataTable

----------------
C# Code :
----------------

        public void ExportToExcel(DataTable dt)
        {
            Response.Clear();
            Response.ClearContent();
            Response.ClearHeaders();

            Response.Buffer = true;
            Response.ContentType = "application/vnd.ms-excel";
            Response.Write("<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.0 Transitional//EN\">");
            Response.AddHeader("content-disposition", "attachment;filename=PolicyClaimDetail.xls");
            Response.ContentEncoding = Encoding.UTF8;
            Response.Charset = "";
            EnableViewState = false;

            //Set Fonts
            Response.Write("<font style='font-size:10.0pt; font-family:Calibri;'>");
            Response.Write("<BR><BR><BR>");

            //Sets the table border, cell spacing, border color, font of the text, background,
            //foreground, font height
            Response.Write("<Table border='1' bgColor='#ffffff' borderColor='#000000' cellSpacing='0' cellPadding='0' style='font-size:10.0pt; font-family:Calibri; background:white;'> <TR>");

            // Check not to increase number of records more than 65k according to excel,03
            if (dt.Rows.Count.ToString() <= 65536)
            {
                // Get DataTable Column's Header
                foreach (DataColumn column in dt.Columns)
                {
                    //Write in new column
                    Response.Write("<Td>");

                    //Get column headers  and make it as bold in excel columns
                    Response.Write("<B>");
                    Response.Write(column);
                    Response.Write("</B>");
                    Response.Write("</Td>");
                }

                Response.Write("</TR>");

                // Get DataTable Column's Row
                foreach (DataRow row in dt.Rows)
                {
                    //Write in new row
                    Response.Write("<TR>");

                    for (int i = 0; i <= dt.Columns.Count - 1; i++)
                    {
                        Response.Write("<Td>");
                        Response.Write(row(i).ToString());
                        Response.Write("</Td>");
                    }

                    Response.Write("</TR>");
                }
            }

            Response.Write("</Table>");
            Response.Write("</font>");

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

----------------------
VB.Net Code :

    Sub ExportToExcel(ByVal dt As DataTable)

        Response.Clear()
        Response.ClearContent()
        Response.ClearHeaders()

        Response.Buffer = True
        Response.ContentType = "application/vnd.ms-excel"
        Response.Write("<!DOCTYPE HTML PUBLIC ""-//W3C//DTD HTML 4.0 Transitional//EN"">")
        Response.AddHeader("content-disposition", "attachment;filename=PolicyClaimDetail.xls")
        Response.ContentEncoding = Encoding.UTF8
        Response.Charset = ""
        EnableViewState = False

        'Set Fonts
        Response.Write("<font style='font-size:10.0pt; font-family:Calibri;'>")
        Response.Write("<BR><BR><BR>")

        'Sets the table border, cell spacing, border color, font of the text, background,
        'foreground, font height
        Response.Write("<Table border='1' bgColor='#ffffff' borderColor='#000000' cellSpacing='0' cellPadding='0' style='font-size:10.0pt; font-family:Calibri; background:white;'> <TR>")

        ' Check not to increase number of records more than 65k according to excel,03
        If dt.Rows.Count.ToString() <= 65536 Then

            ' Get DataTable Column's Header
            For Each column As DataColumn In dt.Columns
                'Write in new column
                Response.Write("<Td>")

                'Get column headers  and make it as bold in excel columns
                Response.Write("<B>")
                Response.Write(column)
                Response.Write("</B>")
                Response.Write("</Td>")
            Next

            Response.Write("</TR>")

            ' Get DataTable Column's Row
            For Each row As DataRow In dt.Rows
                'Write in new row
                Response.Write("<TR>")

                For i As Integer = 0 To dt.Columns.Count - 1
                    Response.Write("<Td>")
                    Response.Write(row(i).ToString())
                    Response.Write("</Td>")
                Next

                Response.Write("</TR>")
            Next
        End If

        Response.Write("</Table>")
        Response.Write("</font>")

        Response.Flush()
        Response.End()
    End Sub