Export GridView to Excel

 

 

窗体顶端

原文地址， 需要翻 墙

 

 

 

Introduction:

Exporting GridView to Excel is a very common task which is performed in most of the web applications. There are various techniques of exporting the GridView to excel and it highly depends on the application scenario. In this article I will demonstrate some techniques that you will find useful.

GridView Export the Excel (Basic Code): 

Let's start with the basic export scenario. First, we need to populate the GridView with some data. I have created a custom table which has number of fields. You can check out the screen shot below.

Now, the next task is to populate the GridView with the data from the database. Check out the following code which uses the DataSet to populate the GridView.

private void BindData()

{

SqlConnection myConnection = new SqlConnection("Server=localhost;Database=School;Trusted_Connection=true");

SqlDataAdapter ad = new SqlDataAdapter("SELECT * FROM Users", myConnection);

DataSet ds = new DataSet();

ad.Fill(ds);

gvUsers.DataSource = ds;

gvUsers.DataBind();

}

So, now the GridView is populated with the data. The next task is to export the GridView to excel. You can use the following code for the button click event.

Response.ClearContent();

Response.AddHeader("content-disposition", "attachment; filename=MyExcelFile.xls");

Response.ContentType = "application/excel";

StringWriter sw = new StringWriter();

HtmlTextWriter htw = new HtmlTextWriter(sw);

gvUsers.RenderControl(htw);

Response.Write(sw.ToString());

Response.End();

You will also need to override the VerifyRenderingInServerForm method. Take a look at the code below:

public override void VerifyRenderingInServerForm(Control control)

{

}

When you click the Export to Excel (Default) button a dialog box will popup which will allow you to open or save the exported file. Select open the file and you will see the exported data in the excel spreadsheet. Take a look at the screen shot below which shows the exported GridView in excel spreadsheet.

Exporting GridView to Excel With Style:

Did you see the problem in the above exportation code? Yes, the leading zero's were truncated. It means that if your ID was 000345 it will show up as 345. You can fix this problem by adding a CSS script to the output stream. In order for the ID column to appear correctly you need to store it as text. The text format in excel is represented by "mso-number-format:"\@". Once, you know the format you can append the style in the output stream. Check out the code below.

protected void Btn_ExportClick(object sender, EventArgs e)

{

string style = @" ";

Response.ClearContent();

Response.AddHeader("content-disposition", "attachment; filename=MyExcelFile.xls");

Response.ContentType = "application/excel";

StringWriter sw = new StringWriter();

HtmlTextWriter htw = new HtmlTextWriter(sw);

gvUsers.RenderControl(htw);

// Style is added dynamically

Response.Write(style);

Response.Write(sw.ToString());

Response.End();

}

public override void VerifyRenderingInServerForm(Control control)

{

}

As, you can see in the above code that I have used the string variable "style" to hold the style of the GridView column. And I used the Response.Write method to write the style to the output stream. The last thing that you need to do is to add the style to the ID column. This can be done in the RowDataBound event of the GridView control.

protected void gvUsers_RowDataBound(object sender, GridViewRowEventArgs e)

{

if (e.Row.RowType == DataControlRowType.DataRow)

{

e.Row.Cells[1].Attributes.Add("class", "text");

}

}

 Now, when you export the GridView to excel your exported file will look something like this.

As, you can see that the above table cell uses the class x127. Now, go to the styles section and find the x127.

.xl27
      {mso-style-parent:style0;
      font-weight:700;
      mso-number-format:"\@";
      text-align:center;
      vertical-align:middle;
      border:.5pt solid black;
      white-space:normal;}

When you look up the .x127 you will find the format of the cell defined as mso-number-format:"\@";.  

Finding Style For the Column You can easily find the correct style for your GridView column by opening the exported excel file. Now, click on the column header and select "Format Cells". This will present you with several options to format the cells. Select text from the list and save the file as .htm (webpage) file. Now, open the webpage in your browser and view the source. In the style section you will find different styles for the table cell (td element). Now, locate the ID column by doing a search on the source of the webpage. You will find a line like this:

ID

Exporting GridView With LinkButtons and Paging: 

When you try to export the GridView which contains LinkButtons and Paging enabled then you might see the following error.

You can easily handle this error by going to the page source and turning the EnableEventValidation = "false".

<%@ Page Language="C#" EnableEventValidation="false" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

But let's take a look at the exported file.

As, you can see that the LinkButtons and DropDownLists are also exported with the GridView control. Although the DropDownList does display the correct user selections but it does not look good in the exported file. So, let's see how we can remove the DropDownList and only display the selected text.

I have created a simple DisableControls method which iterates through the GridView control and replace all LinkButtons and DropDownLists with the Literal control.

private void DisableControls(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);

}

 

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

{

DisableControls(gv.Controls[i]);

}

}

}

The idea is very simple just replace all the LinkButton and DropDownList controls with a Literal control and assign their selection to the Literal control Text property. You need to call this function before the exportation takes place.

protected void Btn_ExportExcelPaging(object sender, EventArgs e)

{

DisableControls(gvUsers);

Response.ClearContent();

Response.AddHeader("content-disposition", "attachment; filename=MyExcelFile.xls");

Response.ContentType = "application/excel";

StringWriter sw = new StringWriter();

HtmlTextWriter htw = new HtmlTextWriter(sw);

gvUsers.RenderControl(htw);

Response.Write(sw.ToString());

Response.End();

}

Now, when you export the GridView you will only see the selected text. Check out the screen shot below which shows the effect.

Conclusion:

In this article you learned different ways to export the GridView to excel. I have attached the complete source code to download. I hope you liked this article, happy coding!

Similar Articles

 

窗体底端