Export data from GridView/DataTable to Excel File using C#.NET for webpage and VB.NET window based application

How to Export GridViewData to Excel File using C#.NET ?

Step 1 : Create a function for Change Controls To Value :

private void ChangeControlsToValue(Control gridView)
    {
        Literal literal = new Literal();
       
        for (int i = 0; i < gridView.Controls.Count; i++)
        {
            if (gridView.Controls[i].GetType() == typeof(LinkButton))
            {

                literal.Text = (gridView.Controls[i] as LinkButton).Text;
                gridView.Controls.Remove(gridView.Controls[i]);
                gridView.Controls.AddAt(i,literal);
            }
            else if (gridView.Controls[i].GetType() == typeof(DropDownList))
            {
                literal.Text = (gridView.Controls[i] as DropDownList).SelectedItem.Text;

                gridView.Controls.Remove(gridView.Controls[i]);

                gridView.Controls.AddAt(i,literal);

            }
            else if (gridView.Controls[i].GetType() == typeof(CheckBox))
            {
                literal.Text = (gridView.Controls[i] as CheckBox).Checked ? "True" : "False";
                gridView.Controls.Remove(gridView.Controls[i]);
                gridView.Controls.AddAt(i,literal);
            }
            if (gridView.Controls[i].HasControls())
            {

                ChangeControlsToValue(gridView.Controls[i]);

            }

        }

    }

Step 2 : On Button Click to export data, Add the following Code :

protected void btnExportToExcel_Click(object sender, EventArgs e)
    {
        if (RadioButtonList1.SelectedIndex == 0)
        {
            GridView1.AllowPaging = false;
            GridView1.GridLines = GridLines.None;
            GridView1.DataBind();
        }
        else
        {
            GridView1.PagerSettings.Visible = false;
            GridView1.GridLines = GridLines.None;
            GridView1.DataBind();
        }

        ChangeControlsToValue(GridView1);
        Response.ClearContent();

        Response.AddHeader("content-disposition", "attachment; filename=GridViewToExcel.xls");

        Response.ContentType = "application/excel";

        StringWriter sWriter = new StringWriter();

        HtmlTextWriter hTextWriter = new HtmlTextWriter(sWriter);

        HtmlForm hForm = new HtmlForm();

        GridView1.Parent.Controls.Add(hForm);

        hForm.Attributes["runat"] = "server";

        hForm.Controls.Add(GridView1);

        hForm.RenderControl(hTextWriter);
       
        // Write below code to add cell border to empty cells in Excel file
        // If we don't add this line then empty cells will be shown as blank white space

        StringBuilder sBuilder = new StringBuilder();
        sBuilder.Append(" <!--[if gte mso 9]>ExportToExcel<![endif]");
        sBuilder.Append(sWriter + "-->");

        Response.Write(sBuilder.ToString());

        Response.End();
    }

 Export data from data table to excel file in window based application using VB.NET

Private Sub exporttoexcel(ByVal dtable As DataTable)

        Dim strTempFile As String = My.Computer.FileSystem.GetTempFileName()

        Dim strLine As New Text.StringBuilder("")

        For c As Integer = 0 To dtable.Columns.Count - 1
            strLine.Append(dtable.Columns(c).ColumnName.ToString & ",")
        Next

        My.Computer.FileSystem.WriteAllText(strTempFile, strLine.ToString.TrimEnd(",") & vbCrLf, True)

        For r As Integer = 0 To dtable.Rows.Count - 1
            strLine = New Text.StringBuilder("")
            For c As Integer = 0 To dtable.Columns.Count - 1
                strLine.Append(dtable.Rows(r).Item(c).ToString & ",")
            Next

            My.Computer.FileSystem.WriteAllText(strTempFile, strLine.ToString.TrimEnd(",") & vbCrLf, True)
        Next

        Process.Start("excel", strTempFile)
    End Sub
-----------------------------------------------------------------------------------------------------------------------
Call this function on button click event to export the rows of the data table to excel file