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/


Article by Manoranjan Sahoo
If it helps you then Rate this. Best Regards, Manoranjan Sahoo http://www.dotnetsquare.com Blog: http://blog.msahoo.net

Follow Manoranjan Sahoo or read 63 articles authored by Manoranjan Sahoo

Comments

Author: Ultimaterengan05 Feb 2014 Member Level: Gold   Points : 6

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);
}
}
}
}



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: