Export GridView to Excel using C#.Net
Export to Excel from GridView Using C#.Net
Here I provide the simple code for export gridview to excel in c#.net. Here i considered that, there is one gridview and two button.One button to fetch the data and show on gridview and other is to export that data into excel. To export follow the below steps.Step 1 :
Create one .aspx file. Add one gridview and two button to this.Step2 :
Now Name one of those button as btnExportToExcel. then write the following code to the click event of that button. Make changes the sql query as per your need.
protected void btnExportToExcel_Click(object sender, EventArgs e)
{
try
{
string lblSQLQry="select * from tablename where <condition>";
string fileName="FileName";//you can also pass a unique file name here
ExportToExcelwithDatareader1(lblSQLQry, fileName);
}
catch (SqlException ex){}
catch (SystemException ex){}
}Step3 :
Below the above click event method write the following method. Make changes the connection details as per your need.
public void ExportToExcelwithDatareader1(string strqry, string strfileName)
{
try
{
SqlDataReader dr = null;
StringBuilder sb = new StringBuilder();
SqlCommand cmdexreader = new SqlCommand();
SqlConnection sqlconn="Your SQL Server Connection String";
sqlconn.Open();
cmdexreader.CommandText = strqry;
cmdexreader.CommandTimeout = 0;
cmdexreader.Connection =sqlconn;
dr = cmdexreader.ExecuteReader(CommandBehavior.CloseConnection);
string headername="Report";
sb.Append("<TABLE border='1'><Center><thead style='font-size:16;font-family:Verdana;color:green'><font color='green' size='5'><b>" + headername + "</font></thead></Center>");
for (int count = 1; count < dr.FieldCount - 3; count++)
{
string thname = "";
thname = dr.GetName(count).ToString();
sb.Append("<TH width='71' style='background-color:orange;position:fixed;color:White;font-family:Verdana;white-space: pre-wrap ;font-size:14'><b>" + thname + "</b></TH>");
}
while (dr.Read())
{
sb.Append("<TR>");
for (int col = 1; col < dr.FieldCount - 3; col++)
{
sb.Append("<TD align='Center'><font color='black'>" + dr.GetValue(col).ToString() + "</font></TD>");
}
sb.Append("</TR>");
}
dr.Dispose();
sb.Append("</TABLE>");
Response.Clear();
Response.AddHeader("content-disposition", "attachment;filename=" + strfileName + ".xls");
Response.Charset = "";
Response.ContentType = "application/vnd.xls";
System.IO.StringWriter stringWrite = new System.IO.StringWriter();
stringWrite.WriteLine(sb.ToString());
System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
Response.Write(stringWrite.ToString());
Response.Flush();
Response.End();
}
catch (Exception ex){}
}Step 4 :
That's it. Now you can run the file to see the output.
Thank You.
Reference: http://msahoo.wordpress.com/2010/05/17/export-gridview-to-excel-using-c-net/
Follow
try like this also
How to export datagridview to excel format using c#?In this article I'm going to explain how to convert datagridview to excel format using c#. This is a requirement often in many projects as users may
want to save some data generated in applications for future reference.
Just drag and drop one datagridview and button control to your window application.
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;
namespace WindowsApplication1
{
public partial class Form1 : Form
{
System.Data.DataTable dtblSkill = new System.Data.DataTable();
public Form1()
{
InitializeComponent();
dtblSkill.Columns.Add("SkillCode", typeof(string));
dtblSkill.Columns.Add("SkillName", typeof(string));
AddGridViewSkillColumns();
}
private void AddGridViewSkillColumns()
{
try
{
DataGridViewTextBoxColumn dgvtxtSkillCode = new DataGridViewTextBoxColumn();
dgvtxtSkillCode.Name = "SkillCode";
dgvtxtSkillCode.DataPropertyName = "SkillCode";
dgvtxtSkillCode.HeaderText = "Skill Code";
dgvtxtSkillCode.Width = 120;
DataGridViewTextBoxColumn dgvtxtSkillName = new DataGridViewTextBoxColumn();
dgvtxtSkillName.Name = "SkillName";
dgvtxtSkillName.HeaderText = "Skill Name";
dgvtxtSkillName.DataPropertyName = "SkillName";
dgvtxtSkillName.Visible = true;
dgvtxtSkillName.Width = 160;
dataGridView1.Columns.Add(dgvtxtSkillCode);
dataGridView1.Columns.Add(dgvtxtSkillName);
dataGridView1.DataSource = dtblSkill;
}
catch (Exception ex)
{
MessageBox.Show("Error Details" + ex.Message);
}
}
private void button1_Click(object sender, EventArgs e)
{
Microsoft.Office.Interop.Excel.Application wapp;
Microsoft.Office.Interop.Excel.Worksheet wsheet;
Microsoft.Office.Interop.Excel.Workbook wbook;
wapp = new Microsoft.Office.Interop.Excel.Application();
wapp.Visible = false;
wbook = wapp.Workbooks.Add(true);
wsheet = (Worksheet)wbook.ActiveSheet;
try
{
for (int i = 0; i < this.dataGridView1.Columns.Count; i++)
{
wsheet.Cells[1, i + 1] = this.dataGridView1.Columns[i].HeaderText;
}
for (int i = 0; i < this.dataGridView1.Rows.Count; i++)
{
DataGridViewRow dgvrow = this.dataGridView1.Rows[i];
for (int j = 0; j < dgvrow.Cells.Count; j++)
{
DataGridViewCell cellvalue = dgvrow.Cells[j];
try
{
wsheet.Cells[i + 2, j + 1] = (cellvalue.Value == null) ? "" : cellvalue.Value.ToString();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
wapp.Visible = true;
}
catch (Exception ex)
{
MessageBox.Show("Error Details" + ex.Message);
}
}
}
}