First you need to create New Project and give the Name of the project is ExportDatafromdatagridviewtoExcel. Add the reference in your project, right click on the project and click on add reference.
Now Add Two reference
Microsoft Office 12.0 object Library
Microsoft Excel 12.0 object Library
Add the command button and dataGridView1 control and copy this code and paste. So please change the datasource name,database name and Table name.
usingSystem;
usingSystem.Collections.Generic;
usingSystem.ComponentModel;
usingSystem.Data;
usingSystem.Drawing;
usingSystem.Linq;
usingSystem.Text;
usingSystem.Windows.Forms;
usingSystem.Data.SqlClient;
usingSystem.IO;
usingExcel=Microsoft.Office.Interop.Excel;
namespaceExportDatafromdatagridviewtoExcel
{
public partial class ExportToExcel : Form
{
stringcon = “Data Source=PCName;Initial Catalog=Northwind;Integrated Security=True”;
publicExportToExcel()
{
InitializeComponent();
}
privatevoid ExportToExcel_Load(object sender, EventArgs e)
{
stringsql = “Select Employeeid,FirstName,LastName from Employees”;
SqlConnectioncnn = new SqlConnection(con);
SqlDataAdaptersd = new SqlDataAdapter(sql, cnn);
DataSetds = new DataSet();
cnn.Open();
sd.Fill(ds, “Employees”);
cnn.Close();
dataGridView1.DataSource = ds;
dataGridView1.DataMember = “Employees”;
}
privatevoid cmdExport_Click(objectsender, EventArgs e)
{
Excel._ApplicationExApp = new Microsoft.Office.Interop.Excel.Application();
Excel._WorkbookExwrk = ExApp.Workbooks.Add(Type.Missing);
Excel._WorksheetExwsh = null;
Exwsh=Exwrk.Sheets[“Sheet1”];
Exwsh = Exwrk.ActiveSheet;
Exwsh.Name = “Data Exported From DataGridview”;
//Header
for(int k =1; k < dataGridView1.Columns.Count + 1; k++ )
{
Exwsh.Cells[1,k]=dataGridView1.Columns[k-1].HeaderText;
}
for(int i = 0; i < dataGridView1.Rows.Count – 1; i++)
{
for(int j = 0; j < dataGridView1.Columns.Count; j++)
{
Exwsh.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
}
}
Exwsh.SaveAs(“E:\ExportFromDataGrid”);
ExApp.Quit();
MessageBox.Show(“Data Exported From DataGridview Completed..”);
}
}
}
