Export Grid to Excel

Export to Excel

In this article we are going to read and understand how in a web application we can export a grid data in the excel file. As many times in real time programming we generate reports in the grid format to display to the user.

For example

1 .The list of commodities purchased this month

2. Reports of SMS Sent.

3. Reports of invites. etc.and user might want to save this list for the future use. In Excel format then we need to export this grid to the excel.

Prerequisites:

1. To view an Excel workbook file's contents, you must have installed Microsoft Excel (alone or with MS-Office) on your system.

2. Microsoft Visual Studio (VS) must be installed on the (I haven't tested it on the Prior versions)

Let's start with creating an application in VS2008 (You can even go for VS2005 or VS2010)

Following the steps to we are going to follow.

1. Create a new project in VS2008 as name it as "ExporttoExcel" in the C# category.
2. Place a gridview on the default.aspx page and rename it to grdtoexport.
3. And place a button which will export the grid to excel.
4. Now lets create a datatable which will bind the grid.

The Code will look like:

protected void Page_Load(object sender, EventArgs e)

{

//creating a table for the grid use namespace System.Data;

DataTable dt = new DataTable ();

//adding columns to the datatale

try

{

dt.Columns.Add("Srno");

dt.Columns.Add("Name");

}

catch { }

//adding values to the datatable

for (int i = 1; i <= 10; i++)

{

DataRow dr = dt.NewRow();

dr[0] = i;

dr[1] = "Meetu Choudhary " + i.ToString();

dt.Rows.Add(dr);

}

//binding databale to the grid

grdtoexport.DataSource = dt;

grdtoexport.DataBind();

}

Writing a ExportToExcel class

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Data;

using System.Configuration;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

using System.Text;

using System.IO;

namespace ExportToExcel

{

/// <summary>

/// Summary description for ExportToExcel

/// </summary>

public class ExportToExcel

{

public ExportToExcel()

{

//

// TODO: Add constructor logic here

//

}

public void ExportGridView(GridView GridView1, String strFileName)

{

PrepareGridViewForExport(GridView1);

//string attachment = "attachment; filename=Contacts.xls";

HttpContext.Current.Response.ClearContent();

HttpContext.Current.Response.Buffer = true;

HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=" + strFileName);

HttpContext.Current.Response.ContentType = "application/ms-excel";

HttpContext.Current.Response.Charset = "";

//System.Web.UI.Page.EnableViewState = false;

StringWriter sw = new StringWriter();

HtmlTextWriter htw = new HtmlTextWriter(sw);

GridView1.RenderControl(htw);

HttpContext.Current.Response.Write(sw.ToString());

HttpContext.Current.Response.End();

}

private void PrepareGridViewForExport(Control gv)

{

LinkButton lb = new LinkButton();

Literal l = new Literal();

string name = String.Empty;

for (int i = 0; i < gv.Controls.Count; i++)

{

if (gv.Controls[i].GetType() == typeof(LinkButton))

{

l.Text = (gv.Controls[i] as LinkButton).Text;

gv.Controls.Remove(gv.Controls[i]);

gv.Controls.AddAt(i, l);

}

else if (gv.Controls[i].GetType() == typeof(DropDownList))

{

l.Text = (gv.Controls[i] as DropDownList).SelectedItem.Text;

gv.Controls.Remove(gv.Controls[i]);

gv.Controls.AddAt(i, l);

}

else if (gv.Controls[i].GetType() == typeof(CheckBox))

{

l.Text = (gv.Controls[i] as CheckBox).Checked ? "True" : "False";

gv.Controls.Remove(gv.Controls[i]);

gv.Controls.AddAt(i, l);

}

if (gv.Controls[i].HasControls())

{

PrepareGridViewForExport(gv.Controls[i]);

}

}

}

/*Use this commented function in all the pages where the above export function is used

//public override void VerifyRenderingInServerForm(Control control)

//{

//}*/

}

}

Calling the Function to export on button click

protected void btnexport_Click(object sender, EventArgs e)

{

ExportToExcel ex = new ExportToExcel();

ex.ExportGridView(grdtoexport, "Client.xls");

}

You can download the code from here

Regards, Meetu Choudhary
Microsoft MVP-ASP/ASP.NET

MsDnM My Forums My Blog

Progress Bar demo

Hi All,

I am Developing this Application just to help a newbie in the technology and even many of my students have asked me for the application which will demonstrate them how to use a progress bar in C#.net for a windows application. So here is a small application which will solve the purpose.

So Here Is the application and description how to develop the application.

1. Create a New Project in VS 2008 or above as per your convince I have installed VS 2008 so I am using that if you want to use VS 2005 you can even go with that also. For now VS2008

2. Create New Project : Select Windows Forms Application under the Visual C# tab and give the name to the project as prjProgressBar.

3. Now Place The ProgressBar Control and the Button Tool on the form from the tools box as shown in the figure below.

4. Set the following Properties of the progress bar:

• Minimum =0 (By Default). This value will specify the lower bound for the progress bar.
• Maximum =500 (Default value is 100). This is the upper bound value of the progress bar when progress is complete.
• Step= 5 (Default is 10) This value is the value by which the progressbar will increase the current value with the next step when performstep function will be called. (in this case 500/5=100 i.e. in ten steps we will complete the progressbar.)
• Value =0 (Default is 0) This will set or get the current value of the progressbar.
• Style is the cosmetic property of the control default is blocks you may change it to marquee or continues. I am not changing it.
• MarqueeanimationSpeed=100 (default is also 100): this is the speed in milisseconds set for the animation of the progressbar.

Now On the button click event we will start the progress bar and show a message at final step that in how many steps we have completed the progress bar. The code will be:

private void button1_Click(object sender, EventArgs e)
{
int i = 0;
while (progressBar1.Value < progressBar1.Maximum )
{
MessageBox.Show("Step : " + i);
}
}

>Now The above code I have written on the button click so you can write it at any place you needed. Suppose You want that as the data is begin added in the database then you need to perform a step in the database you can do that. But in that case you should set the maximum value of the progressbar to the total no. of records to be inserted and this will be set by the code at the event where you have calculated the total no. of records. with the help of this line.

progressBar1.Maximum=NoRecs;

and the step value should be 1 and when the insertion is completed in the database then use
progressBar1.PerformStep();
line of code there.

The Sample Code is attached.

---

Thanks and Regards
Meetu Choudhary
MVP || My Blog || MySite || My Forums