羅 朝淇的部落格

It is an abbreviation for the Document Object Model for HTML

Introduction: The article "ASP.Net 2.0: Export GridView to Excel" received a very good response from our user community. Some of the excellent tips collected from the user feedback have been included in the first version of the article.  One of the most common questions from our readers is regarding the handling of a Hyperlink column in the GridView Export to Excel. This article will expand on the original article and in this version, we will include the handling the export of the Hyperlink columns in the GridView export to Excel functionality and also re-factor our original logic to use more general features of reflection, allowing for easy extension to include additional control types. This code generalization does have a performance overhead and if the controls embedded in your GridView are limited to a particular set, the original implementation may be more suitable for your requirements. Architectural Changes:

Define a Hash Table which holds the values for the controls to be replaced before the GridView control is exported to Excel.
This HashTable will map the control types that can be potentially embedded in the GridView to the corresponding control property that will be used to represent the particular control when exported to Excel.

The generalized "GetControlPropertyValue" method: In this version, we define a generalized method which will fetch the value of "key" property of the embedded control by using Reflection. We define the key properties for different types of embedded control using our HashTable member variable.
Our HashTable has been setup to perform the following property mappings.

Control Type

Corresponding Value to Represent in Excel

LinkButton or derived class

Text Property value

HyperLink or derived class

Text Property value

DropDownList or derived class

SelectedValue Property Value

CheckBox or derived class

Checked Property Value

If you need to handle additional control types separately for the export process, these control types can be added to the Hashtable.

New version of the PrepareGridViewForExport method: In this updated version, we get the "key" property for the control types that we have defined for replacement in our gridview, by calling GetControlPropertyValue if the control type or it's base type is included in the Hashtable for special handling. The control embedded in the GridView is then replaced by the value of the key property. After all the controls embedded in the GridView are processed recursively, the GridView is rendered into an HtmlTextWriter and output to the Excel formatted response.

Complete Code Listing: using System; using System.Data; using System.Configuration; using System.Collections; 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; using System.Text; using System.IO; using System.Reflection; public partial class DeleteConfirm : System.Web.UI.Page { Hashtable htControls = new Hashtable(); protected void Page_Load(object sender, EventArgs e) { htControls.Add("LinkButton", "Text"); htControls.Add("HyperLink", "Text"); htControls.Add("DropDownList", "SelectedItem"); htControls.Add("CheckBox", "Checked"); } protected void Button1_Click(object sender, EventArgs e) { PrepareGridViewForExport(GridView1); ExportGridView(); } private void ExportGridView() { string attachment = "attachment; filename=Contacts.xls"; Response.ClearContent(); Response.AddHeader("content-disposition", attachment); Response.ContentType = "application/ms-excel"; StringWriter sw = new StringWriter(); HtmlTextWriter htw = new HtmlTextWriter(sw); GridView1.RenderControl(htw); Response.Write(sw.ToString()); Response.End(); } public override void VerifyRenderingInServerForm(Control control) { } private void PrepareGridViewForExport(Control gv) { Literal l = new Literal(); for (int i = 0; i < gv.Controls.Count; i++) { if ((null != htControls[gv.Controls[i].GetType().Name]) || (null != htControls[gv.Controls[i].GetType                ().BaseType.Name])) { l.Text = GetControlPropertyValue(gv.Controls[i]); gv.Controls.Remove(gv.Controls[i]); gv.Controls.AddAt(i, l); } if (gv.Controls[i].HasControls()) { PrepareGridViewForExport(gv.Controls[i]); } } } private string GetControlPropertyValue(Control control) { Type controlType = control.GetType(); string strControlType = controlType.Name; string strReturn = "Error"; bool bReturn; PropertyInfo[] ctrlProps = controlType.GetProperties(); string ExcelPropertyName = (string)htControls[strControlType]; if (ExcelPropertyName == null) { ExcelPropertyName = (string)htControls[control.GetType().BaseType.Name]; if (ExcelPropertyName == null) return strReturn; } foreach (PropertyInfo ctrlProp in ctrlProps) { if (ctrlProp.Name == ExcelPropertyName && ctrlProp.PropertyType == typeof(String)) { try { strReturn = (string)ctrlProp.GetValue(control, null); break; } catch { strReturn = ""; } } if (ctrlProp.Name == ExcelPropertyName && ctrlProp.PropertyType == typeof(bool)) { try { bReturn = (bool)ctrlProp.GetValue(control, null); strReturn = bReturn ? "True" : "False"; break; } catch { strReturn = "Error"; } } if (ctrlProp.Name == ExcelPropertyName && ctrlProp.PropertyType == typeof(ListItem)) { try { strReturn = ((ListItem)(ctrlProp.GetValue(control, null))).Text; break; } catch { strReturn = ""; } } } return strReturn; } } Conclusion: In this article, we saw the technique for including HyperLink controls embedded in the GridView to be exported to Excel, along with other controls such as DropDownList and CheckBox. We also saw how to use Reflection to setup an extensible function to handle various control types. Happy Coding!

Introduction: In this article, we will see how to Export an ASP.Net 2.0 GridView to Excel.  The focus of the article is the Export to Excel functionality - the Gridview and it's data binding are only for demonstrating the Export functionality. The code fragments for the Export to Excel functionality below are not linked to the backend structure and can be re-used across projects for the common functionality provided. Step 1: Setup your web page with the Gridview In this article, we will assume you are starting with a web page which holds a GridView named GridView1. The GridView in our demo code is bound to a table named "ContactPhone" in a SQL Express database. The following code which exports the databound GridView to Excel is not dependent on the specific databindings and can be used without changes for your scenario. ContactPhone Table Structure: Column Name Type ContactID Int (Identity) FName Varchar(50) LName Varchar(50) ContactPhone Varchar(20) Step: The Actual Export The code to do the Excel Export is very straightforward. You can also export to different application type by changing the content-disposition and ContentType.  string attachment = "attachment; filename=Contacts.xls"; Response.ClearContent(); Response.AddHeader("content-disposition", attachment); Response.ContentType = "application/ms-excel"; StringWriter sw = new StringWriter(); HtmlTextWriter htw = new HtmlTextWriter(sw); GridView1.RenderControl(htw); Response.Write(sw.ToString()); Response.End(); If you run the code as above, it will result in an HttpException as follows: Control 'GridView1' of type 'GridView' must be placed inside a form tag with runat=server." To avoid this error, add the following code:   public override void VerifyRenderingInServerForm(Control control) { } Step : Convert the contents If the GridView contains any controls, such as Checkboxes, Dropdownlists, we need to replace the contents with their relevant values. The following recursive function uses Reflection to determine the type of control. The control is deleted in preparation for the Excel export and the relevant value of the control is added. 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]); } } Code Listing: Image: Page Design Image : Sample in action Image: Export to Excel button is clicked Image: GridView contents exported to Excel ExcelExport.aspx <%@ Page Language="C#" AutoEventWireup="true" CodeFile="ExportExcel.aspx.cs" Inherits="DeleteConfirm" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server"> <title>Contacts Listing</title> </head> <body> <form id="form1" runat="server"> <div> <strong><span style="font-size: small; font-family: Arial; text-decoration: underline"> Contacts Listing <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Export To Excel" /></span></strong><br /> <br /> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="ContactID" DataSourceID="SqlDataSource1" EmptyDataText="There are no data records to display." style="font-size: small; font-family: Arial" BackColor="White" BorderColor="#DEDFDE" BorderStyle="None" BorderWidth="1px" CellPadding="4" ForeColor="Black" GridLines="Vertical"> <Columns> <asp:BoundField DataField="ContactID" HeaderText="ContactID" ReadOnly="True" SortExpression="ContactID" Visible="False" /> <asp:BoundField DataField="FName" HeaderText="First Name" SortExpression="FName" /> <asp:BoundField DataField="LName" HeaderText="Last Name" SortExpression="LName" /> <asp:BoundField DataField="ContactPhone" HeaderText="Phone" SortExpression="ContactPhone" /> <asp:TemplateField HeaderText="Favorites"> <ItemTemplate> &nbsp; <asp:CheckBox ID="CheckBox1" runat="server" /> </ItemTemplate></asp:TemplateField> </Columns> <FooterStyle BackColor="#CCCC99" /> <RowStyle BackColor="#F7F7DE" /> <SelectedRowStyle BackColor="#CE5D5A" Font-Bold="True" ForeColor="White" /> <PagerStyle BackColor="#F7F7DE" ForeColor="Black" HorizontalAlign="Right" /> <HeaderStyle BackColor="#6B696B" Font-Bold="True" ForeColor="White" /> <AlternatingRowStyle BackColor="White" /> </asp:GridView> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ContactsConnectionString1 %>" DeleteCommand="DELETE FROM [ContactPhone] WHERE [ContactID] = @ContactID" InsertCommand="INSERT INTO [ContactPhone] ([FName], [LName], [ContactPhone]) VALUES (@FName, @LName, @ContactPhone)" ProviderName="<%$ ConnectionStrings:ContactsConnectionString1.ProviderName %>" SelectCommand="SELECT [ContactID], [FName], [LName], [ContactPhone] FROM [ContactPhone]" UpdateCommand="UPDATE [ContactPhone] SET [FName] = @FName, [LName] = @LName, [ContactPhone] = @ContactPhone WHERE [ContactID] = @ContactID"> <InsertParameters> <asp:Parameter Name="FName" Type="String" /> <asp:Parameter Name="LName" Type="String" /> <asp:Parameter Name="ContactPhone" Type="String" /> </InsertParameters> <UpdateParameters> <asp:Parameter Name="FName" Type="String" /> <asp:Parameter Name="LName" Type="String" /> <asp:Parameter Name="ContactPhone" Type="String" /> <asp:Parameter Name="ContactID" Type="Int32" /> </UpdateParameters> <DeleteParameters> <asp:Parameter Name="ContactID" Type="Int32" /> </DeleteParameters> </asp:SqlDataSource> &nbsp; <br /> </div> </form> </body> </html> ExcelExport.aspx.cs  using System; using System.Data; using System.Configuration; using System.Collections; 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; using System.Text; using System.IO; public partial class DeleteConfirm : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { } protected void Button1_Click(object sender, EventArgs e) { //Export the GridView to Excel PrepareGridViewForExport(GridView1); ExportGridView(); } private void ExportGridView() { string attachment = "attachment; filename=Contacts.xls"; Response.ClearContent(); Response.AddHeader("content-disposition", attachment); Response.ContentType = "application/ms-excel"; StringWriter sw = new StringWriter(); HtmlTextWriter htw = new HtmlTextWriter(sw); GridView1.RenderControl(htw); Response.Write(sw.ToString()); Response.End(); } public override void VerifyRenderingInServerForm(Control control) { } 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]); } } } } Implementation Options: In quite a few cases, developers face an error in the Export functionality - typically the error message is "RegisterForEventValidation can only be called during Render();". Our website readers have contributed some good suggestions in the article comments below. I would particularly like to highlight the suggestion by Marianna, who provides an alternative implementation to the VerifyRenderingInServerForm override. This approach is described below: 

Step 1: Implement the Export functionality as described above.

Step 2: Remove the code to override the VerifyRenderingInServerForm method.

Step 3: Modify the code for the ExportGridView function as below. The code highlighted in green creates and HtmlForm on the fly, before exporting the gridview, adds the gridview to this new form and renders the form (instead of rendering the gridview in our original implementation)

private void ExportGridView() { string attachment = "attachment; filename=Contacts.xls"; Response.ClearContent(); Response.AddHeader("content-disposition", attachment); Response.ContentType = "application/ms-excel"; StringWriter sw = new StringWriter(); HtmlTextWriter htw = new HtmlTextWriter(sw); // Create a form to contain the grid HtmlForm frm = new HtmlForm(); GridView1.Parent.Controls.Add(frm); frm.Attributes["runat"] = "server"; frm.Controls.Add(GridView1); frm.RenderControl(htw); //GridView1.RenderControl(htw); Response.Write(sw.ToString()); Response.End(); } This implementation has the advantage that it can be setup as re-usable code in a separate library, without having to override the base class method each time. Note to readers: Thank you for your comments and feedback! Happy coding!!! ASP.Net 2.0: Export GridView to Excel - Part II This version of the article includes handling of the Hyperlink columns in the GridView export to Excel and also re-factors our original logic to use more general features of Reflection  Conclusion: In this article, we saw how to Export an ASP.Net 2.0 GridView to Excel. The code fragments are not linked to the backend structure and can be re-used across projects for the common functionality provided. The focus of the article is the Export to Excel functionality - the Gridview and it's data binding are only for demonstrating the following functionality.  For reference on Exporting ASP.Net 1.1 DataGrid to Excel: Export ASP.NET DataGrid to Excel Disclaimer: This article is for purely educational purposes and is a compilation of notes, material and my understanding on this subject. Any resemblance to other material is an un-intentional coincidence and should not be misconstrued as malicious, slanderous, or any anything else hereof.

<head runat="server">
    <title>未命名頁面</title>
    <script type="text/javascript">
    function showalert(str)
    {
        alert(str);
    }
    </script>
</head> protected void Page_Load(object sender, EventArgs e)
{
    Button1.Attributes.Add("onClick","showalert('fuck top');");
}

form1.DefaultButton = "btn_submit";
form1.DefaultFocus = "txt_what"; 放在Page_load就可以了。預設按鈕一定要是Button不然就錯了

    protected void Page_Load(object sender, EventArgs e)
    {
        //下面四種方式效果完全一樣
        Page.SetFocus(btnClear);
        //this.SetFocus(btnClear);
        //Page.SetFocus(btnClear.ClientID);
        //btnClear.Focus();
    }

//建立連線
SqlConnection conn;
conn = new SqlConnection("data source = EasyFlow;initial catalog = EFNET;user id =sa;password = XXX");
conn.Open(); //下指令
SqlCommand cmd;
cmd = new SqlCommand("select dept as 部門,empname as 員工名稱,empno as 員工代號,extension as 分機,phs as PHS from extension where deptno like @empno", conn);
cmd.Parameters.Add("@empno", SqlDbType.NVarChar).Value = txt_what.Text.Trim() + "%"; //讀取到中介
SqlDataReader dr;
dr = cmd.ExecuteReader(); //仲介資料給GridView去顯示
GridView.DataSource = dr;
GridView.DataBind(); //若要讀取仲介的每一筆資料並丟給DropDownList
while (dr.Read())
{
    DropDownList.Items.Add(dr["dept"].ToString()); //dr["dept"].ToString() 是dr的dept欄位資料
}

DropDownList.Items.Add("ABC");

string[ ] which = new string[3] { "員工名稱", "員工代號","部門代號"};
for (int i = 0; i <= which.GetUpperBound(0) ; i++)
{ DropDownList1.Items.Add(which[i].ToString()); }

DropDownList 控制項會在使用者選取項目時，引發 SelectedIndexChanged 事件。根據預設，這個事件不會造成網頁張貼至伺服器，但您可以將 AutoPostBack 屬性設定為 true(這是重點)，讓控制項強制執行立即張貼。