I wrote a code to export my data in gridview to excel

Hi Satyapriya, Thank you very much for your responses and help. I really appreciate it. I tried your code but it was giving me exactly how my page looks like on the browser, on the excel file.
So I brainstormed a little bit and came up with something that helped me get what I wanted. I got some help from the code u posted. So I'll give u the credit for assisting me in developing my own solution. Very many thanks Pal. Here's the attached file of my solution.

Thanks. But maybe I should explain my requirement a little more further. I have this page that is used to search for students records using some search criteria like surname, firstname, registration number etc. The search results are based on entry of this criteria mentioned. If they are not entered, all the records in the database will be displayed in gridview. So I would want to export the exact data that is displayed on the gridview to excel, which is of course based on the search criteria entered by the user.

The solution you provided works, but it's for all records, which is not what I'm looking for. I hope u understand my requirement? Many thanks pal.

Hi kacey,


In Asp.net


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

Hi kacey,


Try this...

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.IO;

namespace Export_to_Excel_in_windows
{
    public partial class Form1 : Form
    {
       
        string ConnectionString = System.Configuration.ConfigurationSettings.AppSettings["dsn"];
        OleDbCommand com;
        DataSet ds;
        OleDbDataAdapter oledbda;
        DataTable dt;
        string str;

        public Form1()
        {
            InitializeComponent();
        }

        private void btndisplay_Click(object sender, EventArgs e)
        {
           
            try
            {
                OleDbConnection con = new OleDbConnection(ConnectionString);
                con.Open();
                str = "select * from emp";
                com = new OleDbCommand(str, con);
                ds = new DataSet();
                oledbda = new OleDbDataAdapter(com);
                oledbda.Fill(ds, "emp");
                con.Close();
                DataGridView1.DataSource = ds;
                DataGridView1.DataMember = "emp";
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void btn_export_Click(object sender, EventArgs e)
        {
            //We have to add a reference to the Microsoft Excel object library.
            //Right click on your project and select Add Reference menu. After that go to COM tab and select and add Microsoft Excel 12.0 object library.

            Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
            Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
            app.Visible = true;

            try
            {

                worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets["Sheet1"];
                worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.ActiveSheet;
                worksheet.Name = "Exported from DataGridView";

                for (int i = 1; i < DataGridView1.Columns.Count + 1; i++)
                {
                    worksheet.Cells[1, i] = DataGridView1.Columns[i - 1].HeaderText;
                }

                for (int i = 0; i < DataGridView1.Rows.Count - 1; i++)
                {
                    for (int j = 0; j < DataGridView1.Columns.Count; j++)
                    {
                        worksheet.Cells[i + 2, j + 1] = DataGridView1.Rows[i].Cells[j].Value.ToString();
                    }
                }


                string fileName = String.Empty;

                saveFileExcel.Filter = "Excel files |*.xls|All files (*.*)|*.*";
                saveFileExcel.FilterIndex = 2;
                saveFileExcel.RestoreDirectory = true;

                if (saveFileExcel.ShowDialog() == DialogResult.OK)
                {
                    fileName = saveFileExcel.FileName;

                    workbook.SaveAs(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                }
                else
                    return;

            }
            catch (System.Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                app.Quit();
                workbook = null;
                app = null;
            }
        }
    }
}



Thanks