Import and Export Data from Excel File using Gridview

First How to Import Data from Excel File

•  Create Excel file named Test.xlsx.
•  In that Excel file give two columns named Id, Name.
• Make some entries in that columns.

Now on the Page Load event (using System.Data.OleDb)

 protected void Page_Load(object sender, EventArgs e)

        {

            string FileName = Server.MapPath("Test.xlsx");

            DataSet ds=ImportExcel(FileName);

            GridView1.DataSource = ds;

            GridView1.DataBind();

        }

        public DataSet ImportExcel(string FileName)

        {

            string strSQL = "SELECT * FROM [Demo$]";

            OleDbConnection excelConnection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName + ";Extended Properties='Excel 12.0;HDR=YES;'");

            excelConnection.Open();

            OleDbCommand dbCommand = new OleDbCommand(strSQL, excelConnection);

            OleDbDataAdapter dataAdapter = new OleDbDataAdapter(dbCommand);

            DataSet dsExcel = new DataSet();

            dataAdapter.Fill(dsExcel, "Demo");

            dataAdapter.Dispose();

            dbCommand.Dispose();

            excelConnection.Close();

            excelConnection.Dispose();

            return dsExcel;

        }

Now How to Export Data to Excel File From Gridview

(using System.Web)

 protected void Button1_Click(object sender, EventArgs e)

        {

            string filename = Server.MapPath("Test1.xlsx");

            HttpResponse response = HttpContext.Current.Response;

            // first let's clean up the response.object

            response.Clear();

            response.Charset = "";

            // set the response mime type for excel

            response.ContentType = "application/vnd.ms-excel";

            response.AddHeader("Content-Disposition", "attachment;filename=\"" + filename + "\"");

            // create a string writer

            using (StringWriter sw = new StringWriter())

            {

                using (HtmlTextWriter htw = new HtmlTextWriter(sw))

                {

                    // instantiate a datagrid

                  

                    GridView1.RenderControl(htw);

                    response.Write(sw.ToString());

                    response.End();

                }

            }

        }

If you run the code as above, it will result in an HttpException as follows:

Control 'GridView1' of type 'GridView' must be placed inside a form tag with runat=server." 

To avoid this error, add the following code:  

        

public override void VerifyRenderingInServerForm(Control control)

        {

        }