Asp.net 2.0 GridView数据导出Excel文件(示例代码下载)
(一) . 运行示例图
1. 待导出数据的GridView图:
2. 生成的Excel文件
(二). 代码
1. 前台页面 GridViewToExcelFile.aspx 代码:
1
<%@ Page Language="C#" AutoEventWireup="true" EnableEventValidation="false" CodeFile="GridViewToExcelFile.aspx.cs" Inherits="_Default" %>
2
3<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
4
5<html xmlns="http://www.w3.org/1999/xhtml" >
6<head runat="server">
7 <title>Untitled Page</title>
8</head>
9<body>
10 <form id="form1" runat="server">
11 <div>
12 <asp:GridView ID="gvFoods" runat="server" BackColor="LightGoldenrodYellow" BorderColor="Tan" BorderWidth="1px" CellPadding="2" ForeColor="Black" GridLines="None" EnableViewState="true">
13 <FooterStyle BackColor="Tan" />
14 <SelectedRowStyle BackColor="DarkSlateBlue" ForeColor="GhostWhite" />
15 <PagerStyle BackColor="PaleGoldenrod" ForeColor="DarkSlateBlue" HorizontalAlign="Center" />
16 <HeaderStyle BackColor="Tan" Font-Bold="True" />
17 <AlternatingRowStyle BackColor="PaleGoldenrod" />
18 </asp:GridView>
19 <br />
20 <asp:Button ID="Button1" runat="server" BackColor="#C0C0FF" OnClick="Button1_Click"
21 Text="Create Excel" Width="137px" />
22 </div>
23 </form>
24</body>
25</html>
<%@ Page Language="C#" AutoEventWireup="true" EnableEventValidation="false" CodeFile="GridViewToExcelFile.aspx.cs" Inherits="_Default" %>2
3
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">4
5
<html xmlns="http://www.w3.org/1999/xhtml" >6
<head runat="server">7
<title>Untitled Page</title>8
</head>9
<body>10
<form id="form1" runat="server">11
<div>12
<asp:GridView ID="gvFoods" runat="server" BackColor="LightGoldenrodYellow" BorderColor="Tan" BorderWidth="1px" CellPadding="2" ForeColor="Black" GridLines="None" EnableViewState="true">13
<FooterStyle BackColor="Tan" />14
<SelectedRowStyle BackColor="DarkSlateBlue" ForeColor="GhostWhite" />15
<PagerStyle BackColor="PaleGoldenrod" ForeColor="DarkSlateBlue" HorizontalAlign="Center" />16
<HeaderStyle BackColor="Tan" Font-Bold="True" />17
<AlternatingRowStyle BackColor="PaleGoldenrod" />18
</asp:GridView>19
<br />20
<asp:Button ID="Button1" runat="server" BackColor="#C0C0FF" OnClick="Button1_Click"21
Text="Create Excel" Width="137px" />22
</div>23
</form>24
</body>25
</html>2. 页面后台文件 GridViewToExcelFile.aspx.cs 代码:
1 using System;
2 using System.Data;
3 using System.Configuration;
4 using System.Web;
5 using System.Web.Security;
6 using System.Web.UI;
7 using System.Web.UI.WebControls;
8 using System.Web.UI.WebControls.WebParts;
9 using System.Web.UI.HtmlControls;
10 using System.IO;
11 using System.Text;
12 using System.Collections;
13
14 public partial class _Default : System.Web.UI.Page
15 {
16 protected void Page_Load(object sender, EventArgs e)
17 {
18 if(!Page.IsPostBack)
19 {
20 CreateStructure();
21 this.gvFoods.DataSource = this.CreateData();
22 this.gvFoods.DataBind();
23 }
24 }
25 private DataTable CreateStructure()
26 {
27 DataTable dt = new DataTable();
28 dt.Columns.Add(new DataColumn("CategoryID", typeof(int)));
29 dt.Columns.Add(new DataColumn("CategoryName", typeof(string)));
30 dt.Columns.Add(new DataColumn("Price", typeof(int)));
31 return dt;
32 }
33 public DataSet CreateData()
34 {
35 DataSet ds = new DataSet();
36 DataTable dt = this.CreateStructure();
37
38 DataRow drNew = dt.NewRow();
39 drNew = dt.NewRow();
40 drNew["CategoryID"] = 1;
41 drNew["CategoryName"] = "Apple";
42 drNew["Price"] = 2;
43 dt.Rows.Add(drNew);
44
45 drNew = dt.NewRow();
46 drNew["CategoryID"] = 2;
47 drNew["CategoryName"] = "Banana";
48 drNew["Price"] = 3;
49 dt.Rows.Add(drNew);
50
51 drNew = dt.NewRow();
52 drNew["CategoryID"] = 3;
53 drNew["CategoryName"] = "Orange";
54 drNew["Price"] = 1;
55 dt.Rows.Add(drNew);
56
57 drNew = dt.NewRow();
58 drNew["CategoryID"] = 4;
59 drNew["CategoryName"] = "Radish";
60 drNew["Price"] = 2;
61 dt.Rows.Add(drNew);
62
63 drNew = dt.NewRow();
64 drNew["CategoryID"] = 5;
65 drNew["CategoryName"] = "Pen";
66 drNew["Price"] = 3;
67 dt.Rows.Add(drNew);
68
69 drNew = dt.NewRow();
70 drNew["CategoryID"] = 6;
71 drNew["CategoryName"] = "Pencil";
72 drNew["Price"] = 7;
73 dt.Rows.Add(drNew);
74
75 drNew = dt.NewRow();
76 drNew["CategoryID"] = 7;
77 drNew["CategoryName"] = "Ruler";
78 drNew["Price"] = 3;
79 dt.Rows.Add(drNew);
80
81 drNew = dt.NewRow();
82 drNew["CategoryID"] = 8;
83 drNew["CategoryName"] = "Eraser";
84 drNew["Price"] = 5;
85 dt.Rows.Add(drNew);
86
87 ds.Tables.Add( dt );
88 return ds;
89 }
90 protected void Button1_Click(object sender, EventArgs e)
91 {
92 StringWriter sw = new StringWriter();
93 HtmlTextWriter htw = new HtmlTextWriter(sw);
94 this.gvFoods.RenderControl(htw);
95 string strHtml = sw.ToString().Trim();
96
97 string ExcelFileName = "FoodList.xls";
98 string FilePhysicialPathName = Request.PhysicalApplicationPath;
99
100 //生成的Excel文件名
101 string objectExcelFileName = Path.Combine(FilePhysicialPathName, ExcelFileName);
102
103 if( File.Exists( objectExcelFileName ))
104 {
105 File.Delete(objectExcelFileName);
106 }
107 FileStream fs = new FileStream(objectExcelFileName, FileMode.Create);
108 BinaryWriter bw = new BinaryWriter(fs, Encoding.GetEncoding("GB18030"));
109 bw.Write(strHtml);
110 bw.Close();
111 fs.Close();
112
113 }
114 public override void VerifyRenderingInServerForm(Control control)
115 {
116 //base.VerifyRenderingInServerForm(control);
117 }
118 }
119
2 using System.Data;
3 using System.Configuration;
4 using System.Web;
5 using System.Web.Security;
6 using System.Web.UI;
7 using System.Web.UI.WebControls;
8 using System.Web.UI.WebControls.WebParts;
9 using System.Web.UI.HtmlControls;
10 using System.IO;
11 using System.Text;
12 using System.Collections;
13
14 public partial class _Default : System.Web.UI.Page
15 {
16 protected void Page_Load(object sender, EventArgs e)
17 {
18 if(!Page.IsPostBack)
19 {
20 CreateStructure();
21 this.gvFoods.DataSource = this.CreateData();
22 this.gvFoods.DataBind();
23 }
24 }
25 private DataTable CreateStructure()
26 {
27 DataTable dt = new DataTable();
28 dt.Columns.Add(new DataColumn("CategoryID", typeof(int)));
29 dt.Columns.Add(new DataColumn("CategoryName", typeof(string)));
30 dt.Columns.Add(new DataColumn("Price", typeof(int)));
31 return dt;
32 }
33 public DataSet CreateData()
34 {
35 DataSet ds = new DataSet();
36 DataTable dt = this.CreateStructure();
37
38 DataRow drNew = dt.NewRow();
39 drNew = dt.NewRow();
40 drNew["CategoryID"] = 1;
41 drNew["CategoryName"] = "Apple";
42 drNew["Price"] = 2;
43 dt.Rows.Add(drNew);
44
45 drNew = dt.NewRow();
46 drNew["CategoryID"] = 2;
47 drNew["CategoryName"] = "Banana";
48 drNew["Price"] = 3;
49 dt.Rows.Add(drNew);
50
51 drNew = dt.NewRow();
52 drNew["CategoryID"] = 3;
53 drNew["CategoryName"] = "Orange";
54 drNew["Price"] = 1;
55 dt.Rows.Add(drNew);
56
57 drNew = dt.NewRow();
58 drNew["CategoryID"] = 4;
59 drNew["CategoryName"] = "Radish";
60 drNew["Price"] = 2;
61 dt.Rows.Add(drNew);
62
63 drNew = dt.NewRow();
64 drNew["CategoryID"] = 5;
65 drNew["CategoryName"] = "Pen";
66 drNew["Price"] = 3;
67 dt.Rows.Add(drNew);
68
69 drNew = dt.NewRow();
70 drNew["CategoryID"] = 6;
71 drNew["CategoryName"] = "Pencil";
72 drNew["Price"] = 7;
73 dt.Rows.Add(drNew);
74
75 drNew = dt.NewRow();
76 drNew["CategoryID"] = 7;
77 drNew["CategoryName"] = "Ruler";
78 drNew["Price"] = 3;
79 dt.Rows.Add(drNew);
80
81 drNew = dt.NewRow();
82 drNew["CategoryID"] = 8;
83 drNew["CategoryName"] = "Eraser";
84 drNew["Price"] = 5;
85 dt.Rows.Add(drNew);
86
87 ds.Tables.Add( dt );
88 return ds;
89 }
90 protected void Button1_Click(object sender, EventArgs e)
91 {
92 StringWriter sw = new StringWriter();
93 HtmlTextWriter htw = new HtmlTextWriter(sw);
94 this.gvFoods.RenderControl(htw);
95 string strHtml = sw.ToString().Trim();
96
97 string ExcelFileName = "FoodList.xls";
98 string FilePhysicialPathName = Request.PhysicalApplicationPath;
99
100 //生成的Excel文件名
101 string objectExcelFileName = Path.Combine(FilePhysicialPathName, ExcelFileName);
102
103 if( File.Exists( objectExcelFileName ))
104 {
105 File.Delete(objectExcelFileName);
106 }
107 FileStream fs = new FileStream(objectExcelFileName, FileMode.Create);
108 BinaryWriter bw = new BinaryWriter(fs, Encoding.GetEncoding("GB18030"));
109 bw.Write(strHtml);
110 bw.Close();
111 fs.Close();
112
113 }
114 public override void VerifyRenderingInServerForm(Control control)
115 {
116 //base.VerifyRenderingInServerForm(control);
117 }
118 }
119
(三). 示例代码下载
