Save the contents of gridview to the excel file?

You must realize that there are some drawbacks with these approaches. The problem with the first approach which uses RenderControl method is that you are actually saving a HTML content into a file with XLS extension.

The problem with other suggested approaches is that you are only retrieving the GridView's data source as a DataTable so all the styling gets lost.

What you need to do is convert your HTML content into an Excel file in C#.

For example you can achieve that pretty easily with this Excel's library for C# and export that real Excel file to ASP.NET's client like the following:

StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
GridView1.RenderControl(htw);

ExcelFile excel;
var options = LoadOptions.HtmlDefault;
using (var htmlStream = new MemoryStream(options.Encoding.GetBytes(sw.ToString())))
    excel = ExcelFile.Load(htmlStream, options);

excel.Save(this.Response, "GridView.xls");

My code:

        private void button1_Click(object sender, EventArgs e)
        {
            //Create Workbook
            Workbook workbook = new Workbook();
            workbook.CreateEmptySheets(1);
            Worksheet sheet = workbook.Worksheets[0];

            //Insert DataTable to Excel
            sheet.InsertDataTable((DataTable)this.dataGridView1.DataSource, true, 2, 1, -1, -1);

            //Save and Launch File
            workbook.SaveToFile("DataImport.xlsx", ExcelVersion.Version2010);
            System.Diagnostics.Process.Start(workbook.FileName);
        }

        private void Form1_Load_1(object sender, EventArgs e)
        {
            //Load Data from Database to DataGridView
            string connString = @"Provider=Microsoft.ACE.OLEDB.12.0;
                                Data Source=D:\work\VIP.mdb;Persist Security Info=False;";
            DataTable dataTable = new DataTable();
            using (OleDbConnection conn = new OleDbConnection(connString))
            {
                conn.Open();
                string sql = "select Name,Gender,Birthday,Email,Number,Country from VIP";
                OleDbDataAdapter dataAdapter = new OleDbDataAdapter(sql, conn);
                dataAdapter.Fill(dataTable);
            }
            this.dataGridView1.DataSource = dataTable;
        }

I use a .NET Excel component to import data.

You can use a free source free data export component to help u:
private void GetData(string selectCommand)
        {
            try
            {
                // Specify a connection string. Replace the given value with a
                // valid connection string for a Northwind SQL Server sample
                // database accessible to your system.
                String connectionString = txtConnstr.Text.Trim();
                // Create a new data adapter based on the specified query.
                OleDbDataAdapter adpter = new OleDbDataAdapter(selectCommand, connectionString);
                // Create a command builder to generate SQL update, insert, and
                // delete commands based on selectCommand. These are used to
                // update the database.
                OleDbCommandBuilder commandBuilder = new OleDbCommandBuilder(adpter);
                // Populate a new data table and bind it to the BindingSource.
                DataTable table = new DataTable();
                table.Locale = System.Globalization.CultureInfo.InvariantCulture;
                adpter.Fill(table);
                bindingSource1.DataSource = table;
                dataGridView1.DataSource = bindingSource1;
                // Resize the DataGridView columns to fit the newly loaded content.
                dataGridView1.AutoResizeColumns(
                    DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader);
            }
            catch (SqlException)
            {
                MessageBox.Show("To run this example, replace the value of the " +
                    "connectionString variable with a connection string that is " +
                    "valid for your system.");
            }
        }
        private void btnExportForm_Click(object sender, EventArgs e)
        {
            FormExportData form = new FormExportData();
            form.Command = this.txtCmd.Text.Trim();
            form.ConnStr = this.txtConnstr.Text.Trim();
            if (form.ShowDialog(this)!=DialogResult.OK)
            {
                return;
            }
        }
    }
}

since the whole process can not describe in detail, so please see:

Export Data from GridView to Excel

Hi Amol,

Try this...

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="import_the_gridview_data_in_excel._Default" %>





   


   

   

   
   

   
   
            Text="Import to Excel" />
   






using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Text;
namespace import_the_gridview_data_in_excel
{
    public partial class _Default : System.Web.UI.Page
    {
        string connStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
       
        protected void Page_Load(object sender, EventArgs e)
        {
            SqlConnection conn = new SqlConnection(connStr);
            SqlDataAdapter sqlda = new SqlDataAdapter("SELECT * from employee", conn);
            DataSet ds = new DataSet();
            sqlda.Fill(ds, "employee");
            GridView1.DataSource = ds;
            GridView1.DataMember = "employee";
            GridView1.DataBind();
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            ExportToExcel("Report.xls", GridView1);
        }
        private void ExportToExcel(string strFileName, GridView dg)
        {
            Response.Clear();
            Response.Buffer = true;
            Response.ContentType = "application/vnd.ms-excel";
            Response.Charset = "";
            this.EnableViewState = false;
            System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
            System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
            GridView1.RenderControl(oHtmlTextWriter);
            Response.Write(oStringWriter.ToString());
            Response.End();

        }
        public override void VerifyRenderingInServerForm(Control control)
        {
        }
    }
}




Thanks
If this post helps you mark it as answer