将ASP.Net 2.0下的GridView导出为Excel.(转)

将ASP.Net 2.0下的GridView导出为Excel.(转)

简介:

在本文中我们将具体介绍如何将ASP.Net 2.0下的GridView导出为Excel.

本文的焦点是Gridview导为Excel功能和它的数据绑定只出口示范功能.

本文代码可以用来导出为Excel的功能但不局限于这个部分,还可以在很多项目中使用。

 

Step 1: Setup your web page with the Gridview

这里我假定你满足:在一个页面中有个名为GridView1的GridView。在GridView中我们绑定了一个名为ContactPhone的SQL数据库。接下来的代码就是如何将GridView导出为Excel并且不依赖于具体的数据绑定还具有场景自改变能力。

ContactPhone Table Structure:

Column Name

Type

ContactID

Int (Identity)

FName

Varchar(50)

LName

Varchar(50)

ContactPhone

Varchar(20)

Step: The Actual Export

这段代码是直接输出为Excel的,你也可以改变content-disposition和ContentType以输出不同的类型。

 

 1
None.gif string  attachment  =  ” attachment; filename=Contacts.xls ” ;

 2
None.gif

 3
None.gifResponse.ClearContent();

 4
None.gif

 5
None.gifResponse.AddHeader( ” content-disposition ” , attachment);

 6
None.gif

 7
None.gifResponse.ContentType  =  ” application/ms-excel ” ;

 8
None.gif

 9
None.gifStringWriter sw  =  new  StringWriter();

10
None.gif

11
None.gifHtmlTextWriter htw  =  new  HtmlTextWriter(sw);

12
None.gif

13
None.gifGridView1.RenderControl(htw);

14
None.gif

15
None.gifResponse.Write(sw.ToString());

16
None.gif

17
None.gifResponse.End(); 

18
None.gif

19
None.gif

如果你运行以上代码,将返回一个HttpException:

‘GridView1’是一个类型为’GridView’的控件,必须为其添加一个runat=server标记.

为避免这个错误,我们添加以下代码:

 

1
None.gif public  override  void  VerifyRenderingInServerForm(Control control)

2
None.gif

3
ExpandedBlockStart.gif
ContractedBlock.gif
dot.gif
{

4InBlock.gif
5ExpandedBlockEnd.gif}


6
None.gif

 

Step : Convert the contents

如果GridView中有其它控件,比如Checkboxes,Dropdownlists,我们需要将它转换为其相关的值,以下递归就用于导出Excel前的准备工作,将各类控件转换为其相关值.

 

 1
None.gif private  void  PrepareGridViewForExport(Control gv)

 2
None.gif

 3
ExpandedBlockStart.gif
ContractedBlock.gif
dot.gif
{

 4InBlock.gif
 5InBlock.gif    LinkButton lb = new LinkButton();
 6InBlock.gif
 7InBlock.gif    Literal l = new Literal();
 8InBlock.gif
 9InBlock.gif    string name = String.Empty;
10InBlock.gif
11InBlock.gif    for (int i = 0; i < gv.Controls.Count; i++)
12InBlock.gif
13ExpandedSubBlockStart.gifContractedSubBlock.gif    dot.gif{

14InBlock.gif
15InBlock.gif        if (gv.Controls[i].GetType() == typeof(LinkButton))
16InBlock.gif
17ExpandedSubBlockStart.gifContractedSubBlock.gif        dot.gif{

18InBlock.gif
19InBlock.gif            l.Text = (gv.Controls[i] as LinkButton).Text;
20InBlock.gif
21InBlock.gif gv.Controls.Remove(gv.Controls[i]);
22InBlock.gif
23InBlock.gif gv.Controls.AddAt(i, l);
24InBlock.gif
25ExpandedSubBlockEnd.gif        }

26InBlock.gif
27InBlock.gif        else if (gv.Controls[i].GetType() == typeof(DropDownList))
28InBlock.gif
29ExpandedSubBlockStart.gifContractedSubBlock.gif        dot.gif{

30InBlock.gif
31InBlock.gif            l.Text = (gv.Controls[i] as DropDownList).SelectedItem.Text;
32InBlock.gif
33InBlock.gif            gv.Controls.Remove(gv.Controls[i]);
34InBlock.gif
35InBlock.gif            gv.Controls.AddAt(i, l);
36InBlock.gif
37ExpandedSubBlockEnd.gif        }

38InBlock.gif
39InBlock.gif        else if (gv.Controls[i].GetType() == typeof(CheckBox))
40InBlock.gif
41ExpandedSubBlockStart.gifContractedSubBlock.gif        dot.gif{

42InBlock.gif
43InBlock.gif            l.Text = (gv.Controls[i] as CheckBox).Checked? “True” : “False”;
44InBlock.gif
45InBlock.gif            gv.Controls.Remove(gv.Controls[i]);
46InBlock.gif
47InBlock.gif            gv.Controls.AddAt(i, l);
48InBlock.gif
49ExpandedSubBlockEnd.gif        }

50InBlock.gif
51InBlock.gif        if (gv.Controls[i].HasControls())
52InBlock.gif
53ExpandedSubBlockStart.gifContractedSubBlock.gif        dot.gif{

54InBlock.gif
55InBlock.gif            PrepareGridViewForExport(gv.Controls[i]);
56InBlock.gif
57ExpandedSubBlockEnd.gif        }

58InBlock.gif
59ExpandedSubBlockEnd.gif}

60InBlock.gif
61InBlock.gif

Code Listing:

Image: Page Design
A1.jpg

Image : Sample in action

a_2.jpg

Image: Export to Excel button is clicked
a_3.jpg

Image: GridView contents exported to Excel

a_4.jpg

 

ExcelExport.aspx

ContractedBlock.gif
ExpandedBlockStart.gif

  1ExpandedBlockStart.gifContractedBlock.gif<%dot.gif@ Page Language=”C#” AutoEventWireup=”true” CodeFile=”ExportExcel.aspx.cs” Inherits=”DeleteConfirm” %>
  2None.gif
  3None.gif 
  4None.gif
  5None.gif<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Transitional//EN” “http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd”>
  6None.gif
  7None.gif 
  8None.gif
  9None.gif<html xmlns=”http://www.w3.org/1999/xhtml” >
 10None.gif
 11None.gif<head runat=”server”>
 12None.gif
 13None.gif<title>Contacts Listing</title>
 14None.gif
 15None.gif</head>
 16None.gif
 17None.gif<body>
 18None.gif
 19None.gif<form id=”form1″ runat=”server”>
 20None.gif
 21None.gif<div>
 22None.gif
 23None.gif<strong><span style=”font-size: small; font-family: Arial; text-decoration: underline”>
 24None.gif
 25None.gifContacts Listing 
 26None.gif
 27None.gif    <asp:Button ID=”Button1″ runat=”server” OnClick=”Button1_Click” Text=”Export To Excel” /></span></strong><br />
 28None.gif
 29None.gif<br />
 30None.gif
 31None.gif<asp:GridView ID=”GridView1″ runat=”server” AutoGenerateColumns=”False” DataKeyNames=”ContactID”
 32None.gif
 33None.gifDataSourceID=”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”>
 34None.gif
 35None.gif<Columns>
 36None.gif
 37None.gif<asp:BoundField DataField=”ContactID” HeaderText=”ContactID” ReadOnly=”True” SortExpression=”ContactID” Visible=”False” />
 38None.gif
 39None.gif<asp:BoundField DataField=”FName” HeaderText=”First Name” SortExpression=”FName” />
 40None.gif
 41None.gif<asp:BoundField DataField=”LName” HeaderText=”Last Name” SortExpression=”LName” />
 42None.gif
 43None.gif<asp:BoundField DataField=”ContactPhone” HeaderText=”Phone” SortExpression=”ContactPhone” />
 44None.gif
 45None.gif<asp:TemplateField HeaderText=”Favorites”>
 46None.gif
 47None.gif<ItemTemplate>
 48None.gif
 49None.gif    &nbsp;
 50None.gif
 51None.gif    <asp:CheckBox ID=”CheckBox1″ runat=”server” />
 52None.gif
 53None.gif</ItemTemplate></asp:TemplateField>
 54None.gif
 55None.gif</Columns>
 56None.gif
 57None.gif<FooterStyle BackColor=”#CCCC99″ />
 58None.gif
 59None.gif<RowStyle BackColor=”#F7F7DE” />
 60None.gif
 61None.gif<SelectedRowStyle BackColor=”#CE5D5A” Font-Bold=”True” ForeColor=”White” />
 62None.gif
 63None.gif<PagerStyle BackColor=”#F7F7DE” ForeColor=”Black” HorizontalAlign=”Right” />
 64None.gif
 65None.gif<HeaderStyle BackColor=”#6B696B” Font-Bold=”True” ForeColor=”White” />
 66None.gif
 67None.gif<AlternatingRowStyle BackColor=”White” />
 68None.gif
 69None.gif</asp:GridView>
 70None.gif
 71None.gif 
 72None.gif
 73None.gif<asp:SqlDataSource ID=”SqlDataSource1″ runat=”server” ConnectionString=”<%$ ConnectionStrings:ContactsConnectionString1 %>”
 74None.gif
 75None.gifDeleteCommand=”DELETE FROM [ContactPhone] WHERE [ContactID] = @ContactID” InsertCommand=”INSERT INTO [ContactPhone] ([FName], [LName], [ContactPhone]) VALUES (@FName, @LName, @ContactPhone)”
 76None.gif
 77None.gifProviderName=”<%$ ConnectionStrings:ContactsConnectionString1.ProviderName %>”
 78None.gif
 79None.gifSelectCommand=”SELECT [ContactID], [FName], [LName], [ContactPhone] FROM [ContactPhone]”
 80None.gif
 81None.gifUpdateCommand=”UPDATE [ContactPhone] SET [FName] = @FName, [LName] = @LName, [ContactPhone] = @ContactPhone WHERE [ContactID] = @ContactID”>
 82None.gif
 83None.gif<InsertParameters>
 84None.gif
 85None.gif<asp:Parameter Name=”FName” Type=”String” />
 86None.gif
 87None.gif<asp:Parameter Name=”LName” Type=”String” />
 88None.gif
 89None.gif<asp:Parameter Name=”ContactPhone” Type=”String” />
 90None.gif
 91None.gif</InsertParameters>
 92None.gif
 93None.gif<UpdateParameters>
 94None.gif
 95None.gif<asp:Parameter Name=”FName” Type=”String” />
 96None.gif
 97None.gif<asp:Parameter Name=”LName” Type=”String” />
 98None.gif
 99None.gif<asp:Parameter Name=”ContactPhone” Type=”String” />
100None.gif
101None.gif<asp:Parameter Name=”ContactID” Type=”Int32″ />
102None.gif
103None.gif</UpdateParameters>
104None.gif
105None.gif<DeleteParameters>
106None.gif
107None.gif<asp:Parameter Name=”ContactID” Type=”Int32″ />
108None.gif
109None.gif</DeleteParameters>
110None.gif
111None.gif</asp:SqlDataSource>
112None.gif
113None.gif&nbsp;
114None.gif
115None.gif<br />
116None.gif
117None.gif</div>
118None.gif
119None.gif</form>
120None.gif
121None.gif</body>
122None.gif
123None.gif</html>
124None.gif
125None.gif

ExcelExport.aspx.cs 

ContractedBlock.gif
ExpandedBlockStart.gif

  1None.gifusing System;
  2None.gif
  3None.gifusing System.Data;
  4None.gif
  5None.gifusing System.Configuration;
  6None.gif
  7None.gifusing System.Collections;
  8None.gif
  9None.gifusing System.Web;
 10None.gif
 11None.gifusing System.Web.Security;
 12None.gif
 13None.gifusing System.Web.UI;
 14None.gif
 15None.gifusing System.Web.UI.WebControls;
 16None.gif
 17None.gifusing System.Web.UI.WebControls.WebParts;
 18None.gif
 19None.gifusing System.Web.UI.HtmlControls;
 20None.gif
 21None.gifusing System.Text;
 22None.gif
 23None.gifusing System.IO;
 24None.gif
 25None.gif 
 26None.gif
 27None.gifpublic partial class DeleteConfirm : System.Web.UI.Page
 28None.gif
 29ExpandedBlockStart.gifContractedBlock.gifdot.gif{

 30InBlock.gif
 31InBlock.gif 
 32InBlock.gif
 33InBlock.gif    protected void Page_Load(object sender, EventArgs e)
 34InBlock.gif
 35ExpandedSubBlockStart.gifContractedSubBlock.gif    dot.gif{

 36InBlock.gif
 37ExpandedSubBlockEnd.gif    }

 38InBlock.gif
 39InBlock.gif 
 40InBlock.gif
 41InBlock.gif    protected void Button1_Click(object sender, EventArgs e)
 42InBlock.gif
 43ExpandedSubBlockStart.gifContractedSubBlock.gif    dot.gif{

 44InBlock.gif
 45InBlock.gif        //Export the GridView to Excel
 46InBlock.gif
 47InBlock.gif        PrepareGridViewForExport(GridView1);
 48InBlock.gif
 49InBlock.gif        ExportGridView();
 50InBlock.gif
 51ExpandedSubBlockEnd.gif    }

 52InBlock.gif
 53InBlock.gif 
 54InBlock.gif
 55InBlock.gif    private void ExportGridView()
 56InBlock.gif
 57ExpandedSubBlockStart.gifContractedSubBlock.gif    dot.gif{

 58InBlock.gif
 59InBlock.gif        string attachment = “attachment; filename=Contacts.xls”;
 60InBlock.gif
 61InBlock.gif        Response.ClearContent();
 62InBlock.gif
 63InBlock.gif        Response.AddHeader(“content-disposition”, attachment);
 64InBlock.gif
 65InBlock.gif        Response.ContentType = “application/ms-excel”;
 66InBlock.gif
 67InBlock.gif        StringWriter sw = new StringWriter();
 68InBlock.gif
 69InBlock.gif        HtmlTextWriter htw = new HtmlTextWriter(sw);
 70InBlock.gif
 71InBlock.gif        GridView1.RenderControl(htw);
 72InBlock.gif
 73InBlock.gif        Response.Write(sw.ToString());
 74InBlock.gif
 75InBlock.gif        Response.End();
 76InBlock.gif
 77ExpandedSubBlockEnd.gif    }

 78InBlock.gif
 79InBlock.gif 
 80InBlock.gif
 81InBlock.gif    public override void VerifyRenderingInServerForm(Control control)
 82InBlock.gif
 83ExpandedSubBlockStart.gifContractedSubBlock.gif    dot.gif{

 84InBlock.gif
 85ExpandedSubBlockEnd.gif    }

 86InBlock.gif
 87InBlock.gif 
 88InBlock.gif
 89InBlock.gif    private void PrepareGridViewForExport(Control gv)
 90InBlock.gif
 91ExpandedSubBlockStart.gifContractedSubBlock.gif    dot.gif{

 92InBlock.gif
 93InBlock.gif        LinkButton lb = new LinkButton();
 94InBlock.gif
 95InBlock.gif        Literal l = new Literal();
 96InBlock.gif
 97InBlock.gif        string name = String.Empty;
 98InBlock.gif
 99InBlock.gif        for (int i = 0; i < gv.Controls.Count; i++)
100InBlock.gif
101ExpandedSubBlockStart.gifContractedSubBlock.gif        dot.gif{

102InBlock.gif
103InBlock.gif            if (gv.Controls[i].GetType() == typeof(LinkButton))
104InBlock.gif
105ExpandedSubBlockStart.gifContractedSubBlock.gif            dot.gif{

106InBlock.gif
107InBlock.gif                l.Text = (gv.Controls[i] as LinkButton).Text;
108InBlock.gif
109InBlock.gif                gv.Controls.Remove(gv.Controls[i]);
110InBlock.gif
111InBlock.gif                gv.Controls.AddAt(i, l);
112InBlock.gif
113ExpandedSubBlockEnd.gif            }

114InBlock.gif
115InBlock.gif            else if (gv.Controls[i].GetType() == typeof(DropDownList))
116InBlock.gif
117ExpandedSubBlockStart.gifContractedSubBlock.gif            dot.gif{

118InBlock.gif
119InBlock.gif                l.Text = (gv.Controls[i] as DropDownList).SelectedItem.Text;
120InBlock.gif
121InBlock.gif                gv.Controls.Remove(gv.Controls[i]);
122InBlock.gif
123InBlock.gif                gv.Controls.AddAt(i, l);
124InBlock.gif
125ExpandedSubBlockEnd.gif            }

126InBlock.gif
127InBlock.gif            else if (gv.Controls[i].GetType() == typeof(CheckBox))
128InBlock.gif
129ExpandedSubBlockStart.gifContractedSubBlock.gif            dot.gif{

130InBlock.gif
131InBlock.gif                l.Text = (gv.Controls[i] as CheckBox).Checked ? “True” : “False”;
132InBlock.gif
133InBlock.gif                gv.Controls.Remove(gv.Controls[i]);
134InBlock.gif
135InBlock.gif                gv.Controls.AddAt(i, l);
136InBlock.gif
137ExpandedSubBlockEnd.gif            }

138InBlock.gif
139InBlock.gif            if (gv.Controls[i].HasControls())
140InBlock.gif
141ExpandedSubBlockStart.gifContractedSubBlock.gif            dot.gif{

142InBlock.gif
143InBlock.gif                PrepareGridViewForExport(gv.Controls[i]);
144InBlock.gif
145ExpandedSubBlockEnd.gif            }

146InBlock.gif
147ExpandedSubBlockEnd.gif        }

148InBlock.gif
149ExpandedSubBlockEnd.gif    }

150InBlock.gif
151ExpandedBlockEnd.gif}

152None.gif
153None.gif

 

 

Implementation Options:

 

通常情况,在输出函数中开发人员都会面临一个错误,典型的就是”RegisterForEventValidation can only be called during Render();”

 

访问者通常会在评论中提出一些好的建议.我特别要强调的是开发者需要重写VerifyRenderingInServerForm方法,该方法描述如下:

  • Step 1:实现导出功能的上述功能.
  • Step 2:重写一个VerifyRenderingInServerForm的空方法.
  • Step 3:修改ExportGridView函数,在绿色高亮代码部创建HtmlForm【原句为:The code highlighted in green creates and HtmlForm on the fly,在翻译HtmlForm on the fly时遇到了一些困难,故on the fly未翻译,请各位高手指教】,在导出girdview之前,添加gridview 到新的form并且render它(取代原来的render实现)

 

 1
None.gif private  void  ExportGridView()

 2
None.gif

 3
ExpandedBlockStart.gif
ContractedBlock.gif
dot.gif
{

 4InBlock.gif
 5InBlock.gif      string attachment = “attachment; filename=Contacts.xls”;
 6InBlock.gif
 7InBlock.gif      Response.ClearContent();
 8InBlock.gif
 9InBlock.gif      Response.AddHeader(“content-disposition”, attachment);
10InBlock.gif
11InBlock.gif      Response.ContentType = “application/ms-excel”;
12InBlock.gif
13InBlock.gif      StringWriter sw = new StringWriter();
14InBlock.gif
15InBlock.gif      HtmlTextWriter htw = new HtmlTextWriter(sw);
16InBlock.gif
17InBlock.gif 
18InBlock.gif
19InBlock.gif      // Create a form to contain the grid
20InBlock.gif
21InBlock.gif      HtmlForm frm = new HtmlForm();
22InBlock.gif
23InBlock.gif      GridView1.Parent.Controls.Add(frm);
24InBlock.gif
25InBlock.gif      frm.Attributes[“runat”] = “server”;
26InBlock.gif
27InBlock.gif      frm.Controls.Add(GridView1);
28InBlock.gif
29InBlock.gif 
30InBlock.gif
31InBlock.gif      frm.RenderControl(htw);
32InBlock.gif
33InBlock.gif      //GridView1.RenderControl(htw);
34InBlock.gif
35InBlock.gif      Response.Write(sw.ToString());
36InBlock.gif
37InBlock.gif      Response.End();
38InBlock.gif
39ExpandedBlockEnd.gif}


40
None.gif

41
None.gif

 

这样实施有个优势,就是可将其设置为复用代码类库,不用每次去复写基类的方法.

 

Note to readers:

Thank you for your comments and feedback! Happy coding!!!

 

ASP.Net 2.0: Export GridView to Excel – Part II

该文中将会在导出Excel 时GridView引入Hyperlink列,以至于需要使用更多的反射来重新设计原来的逻辑.

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。

发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/111092.html原文链接:https://javaforall.cn

【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛

【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...

(0)
blank

相关推荐

发表回复

您的电子邮箱地址不会被公开。

关注全栈程序员社区公众号