Solved

Export to excel with c# custom Control

Posted on 2010-11-22
3
835 Views
Last Modified: 2012-05-10


I want to create custom Button control in c#.On Click on the Button the data in gridview will be Exported To Excell.
i have tried.
Below are some code
protected override void OnClick(EventArgs e)
        {
            base.OnClick(e);

            foreach (Control c in this.Page.Controls)
            {
                String s = c.GetType().ToString();
                if (c is GridView)
                {
                    GridView gv = (GridView)c;
                    if (gv != null)
                    {

                        ExportToExcell(gv);
                    }
                }
            }          
        }

        public void ExportToExcell(GridView gv)
        {
            string attachment = "attachment; filename=Data.xls";
            Context.Response.ClearContent();
            Context.Response.AddHeader("content-disposition", attachment);
            Context.Response.ContentType = "application/ms-excel";

            StringWriter sw = new StringWriter();
            HtmlTextWriter htw = new HtmlTextWriter(sw);
            HtmlForm frm = new HtmlForm();

            gv.Parent.Controls.Add(frm);
            frm.Attributes["runat"] = "server";
            frm.Controls.Add(gv);
            frm.RenderControl(htw);
            Context.Response.Write(sw.ToString());
            Context.Response.End();
        }
0
Comment
Question by:dynamicweb09
  • 2
3 Comments
 
LVL 3

Expert Comment

by:abdkhlaif
ID: 34187582
one way to do this is first to convert your gridview to a DataTable using the attached code then use the ExportToExcel method at (just copy & paste):
http://forums.asp.net/p/975095/1237999.aspx#1237999 
to convert the DataTable to Excel

protected void Button2_Click(object sender, EventArgs e)
{
	DataTable dt = new DataTable();

	// copy columns from the gridview to dt:
	foreach (TableCell tc in GridView1.HeaderRow.Cells)
		dt.Columns.Add(tc.Text);

	// import rows from gridview to dt:
	foreach (GridViewRow gvr in GridView1.Rows)
	{
		DataRow nr = dt.NewRow();
		for (int c = 0; c < dt.Columns.Count; c++)
			nr[c] = gvr.Cells[c].Text;
		
		dt.Rows.Add(nr);
	}

	// convert dt to Excel file:
	ExportToExcel(dt);
}

Open in new window

0
 
LVL 1

Author Comment

by:dynamicweb09
ID: 34187617
how will i write all the code within custom control.
0
 
LVL 3

Accepted Solution

by:
abdkhlaif earned 500 total points
ID: 34194552
since you are creating a custom button, inherit from System.Web.UI.WebControls.Button which already has OnClick and Page members. Add a new member to point to the GridView that the button will export to Excel. Then, override the constructor to set the GridView member, then override the OnClick to convert the GridView to a DataTable and call ExportToExcel which will use the Page member to write the Excel file as a response.

to use this custom control in your web application, add the following code to Page_Load to create & add the control to your page:

ExportToExcelButton c = new ExportToExcelButton(GridView1);
c.Text = "Export to Excel";
Page.FindControl("form1").Controls.Add(c);

The full code of the custom control is attached:
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

/// <summary>
/// Summary description for ExportToExcelButton
/// </summary>
public class ExportToExcelButton : System.Web.UI.WebControls.Button
{
	private GridView gridview;
	
	public ExportToExcelButton(GridView gridview1)
	{
		gridview = gridview1;
	}

	protected override void OnClick(EventArgs e)
	{
		DataTable dt = new DataTable();

		foreach (TableCell tc in gridview.HeaderRow.Cells)
			dt.Columns.Add(tc.Text);

		foreach (GridViewRow gvr in gridview.Rows)
		{
			DataRow nr = dt.NewRow();
			for (int c = 0; c < dt.Columns.Count; c++)
				nr[c] = gvr.Cells[c].Text;

			dt.Rows.Add(nr);
		}

		ExportToExcel(dt);
	}

	protected void ExportToExcel(DataTable dataTable)
	{
		// We can't do anything if there's no data...
		if ((dataTable == null) || (dataTable.Rows.Count <= 0))
			return;

		// Set up the Response object...
		Page.Response.BufferOutput = false;
		Page.Response.Charset = string.Empty;
		Page.Response.Clear();
		Page.Response.ClearContent();
		Page.Response.ClearHeaders();

		Page.Response.ContentType = "application/vnd.ms-excel";

		// Open the document OVER this window:
		string contentDisposition = "inline; filename=ExcelFile.xls";
		Page.Response.AppendHeader("content-disposition", contentDisposition);

		// Create a table HTML tag...
		string textOutput =
		"<table border='1' bordercolor='black' rules='all' " +
		"style='BORDER: 1px double; BORDER-COLLAPSE: collapse;'>\r\n";
		Page.Response.Write(textOutput);

		///////////////////////////////////////////////////////////////////////////
		// Write out the column headers...
		///////////////////////////////////////////////////////////////////////////
		textOutput = "<tr height=17 style='height:12.75pt'>\r\n";
		for (int i = 0; i < dataTable.Columns.Count; i++)
		{
			textOutput += string.Format(
			System.Globalization.CultureInfo.InvariantCulture,
			"<th align='left'>{0}</th>",
			dataTable.Columns[i].ColumnName);
		}
		textOutput += "</tr>\r\n";
		Page.Response.Write(textOutput);

		///////////////////////////////////////////////////////////////////////////
		// Write out each row of the DataTable...
		///////////////////////////////////////////////////////////////////////////
		textOutput = string.Empty;
		for (int i = 0; i < dataTable.Rows.Count; i++)
		{
			System.Data.DataRow dataRow = dataTable.Rows[i];
			textOutput = "<tr height=17 style='height:12.75pt'>\r\n";

			// Loop through the columns for each row and grab the values...
			for (int j = 0; j < dataRow.Table.Columns.Count; j++)
			{
				string dataRowText = dataRow[j].ToString().Trim();

				// Remove any tabs, carriage returns, or newlines from the value...
				dataRowText = dataRowText.Replace("\t", " ");
				dataRowText = dataRowText.Replace("\r", string.Empty);
				dataRowText = dataRowText.Replace("\n", " ");

				textOutput += string.Format(
				System.Globalization.CultureInfo.InvariantCulture,
				"<td align='left' style='mso-number-format:\\@;'>{0}</td>",
				dataRowText);
			}

			textOutput += "</tr>\r\n";
			Page.Response.Write(textOutput);
		}

		// Close the HTML table tag...   
		textOutput = "</table>\r\n";
		Page.Response.Write(textOutput);

		// Close the Response stream...
		Page.Response.Flush();
		Page.Response.Close();
		Page.Response.End();
	}
}

Open in new window

0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
how to convert MM/dd/YYYY hh:mm:ss AM to YYYY-MM-dd datetime format by linq? 4 76
InputLanguage 1 33
RegEx with optional part 4 42
How to read text with RegEx... 2 25
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question