一个ASP.NE导出Excel类
支持DataSet、DataReader、DataTable、DataGrid等数据源
看起来有点耍赖，用HTML构造的，但是能用就行
1
using System;
2using System.Text;
3using System.Web;
4using System.Web.UI;
5using System.Web.UI.WebControls;
6using System.Data;
7using System.Data.OleDb;
8namespace JasonChou.BLL
9
{
10
/**//// <summary>
11
/// BLL_Excel 的摘要说明。
12
/// </summary>
13public class BLL_Excel
14{
15 public BLL_Excel(){}
16
17 导出Exce#region 导出Exce
18 /**//// <summary>
19 /// 导出Excel
20 /// </summary>
21 /// <param name="dr">OleDbDataReader 数据源</param>
22 /// <param name="FileName">文件名</param>
23 /// <param name="biaotou">表头</param>
24 public void EduceExcel(OleDbDataReader dr,string FileName,string[] biaotou)
25 {
26 HttpContext.Current.Response.Clear();
27 HttpContext.Current.Response.Buffer= true;//设置缓冲输出
28 HttpContext.Current.Response.Charset="GB2312";//设置输出流的HTTP字符集
29 HttpContext.Current.Response.AppendHeader("Content-Disposition","attachment;filename=\""+System.Web .HttpUtility.UrlEncode (FileName,System.Text .Encoding .UTF8 )+".xls\"");
30 HttpContext.Current.Response.C.Text.Encoding.GetEncoding("GB2312");
31 HttpContext.Current.Response.ContentType = "application/ms-";
32 // _page.EnableViewState = false;//是否保持视图状态
33
34 HttpContext.Current.Response.Write( HTML(dr,biaotou) );
35
36 HttpContext.Current.Response.End ();
37 }
38 /**//// <summary>
39 /// 导出Excel
40 /// </summary>
41 /// <param name="_page">this</param>
42 /// <param name="DB">DataGrid控件名称</param>
43 /// <param name="FileName">要导出的文件名</param>
44 public void EduceExcel(Page _page, DataGrid DB,string FileName)
45 {
46 HttpContext.Current.Response.Clear();
47 HttpContext.Current.Response.Buffer= true;//设置缓冲输出
48 HttpContext.Current.Response.Charset="GB2312";//设置输出流的HTTP字符集
49 HttpContext.Current.Response.AppendHeader("Content-Disposition","attachment;filename=\""+System.Web .HttpUtility.UrlEncode (FileName,System.Text .Encoding .UTF8 )+".xls\"");
50 HttpContext.Current.Response.C.Text.Encoding.GetEncoding("GB2312");
51 HttpContext.Current.Response.ContentType = "application/ms-";
52 _page.EnableViewState = false;//是否保持视图状态
53 System.IO.StringWriter oStringWriter = new System.IO.StringWriter(); //将信息写入字符串
54 System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter); //在WEB窗体上写出一系列的HTML特定字符和文本
55
56 DB.RenderControl (oHtmlTextWriter);
57 HttpContext.Current.Response.Write(oStringWriter.ToString ());
58
59 HttpContext.Current.Response.End ();
60 }
61 /**//// <summary>
62 /// 导出Excel
63 /// </summary>
64 /// <param name="ds">DataSet 数据源</param>
65 /// <param name="FileName">文件名</param>
66 /// <param name="biaotou">表头</param>
67 public void EduceExcel(DataSet ds,string FileName,string[] biaotou)
68 {
69 HttpContext.Current.Response.Clear();
70 HttpContext.Current.Response.Buffer= true;//设置缓冲输出
71 HttpContext.Current.Response.Charset="GB2312";//设置输出流的HTTP字符集
72 HttpContext.Current.Response.AppendHeader("Content-Disposition","attachment;filename=\""+System.Web .HttpUtility.UrlEncode (FileName,System.Text .Encoding .UTF8 )+".xls\"");
73 HttpContext.Current.Response.C.Text.Encoding.GetEncoding("GB2312");
74 HttpContext.Current.Response.ContentType = "application/ms-";
75 // _page.EnableViewState = false;//是否保持视图状态
76
77 HttpContext.Current.Response.Write( HTML(ds,biaotou) );
78
79 HttpContext.Current.Response.End ();
80 }
81
82 /**//// <summary>
83 /// 导出Excel
84 /// </summary>
85 /// <param name="ds">DataSet 数据源</param>
86 /// <param name="FileName">文件名</param>
87 /// <param name="biaotou">表头</param>
88 public void EduceExcel(DataTable dt,string FileName,string[] biaotou)
89 {
90 HttpContext.Current.Response.Clear();
91 HttpContext.Current.Response.Buffer= true;//设置缓冲输出
92 HttpContext.Current.Response.Charset="GB2312";//设置输出流的HTTP字符集
93 HttpContext.Current.Response.AppendHeader("Content-Disposition","attachment;filename=\""+System.Web .HttpUtility.UrlEncode (FileName,System.Text .Encoding .UTF8 )+".xls\"");
94 HttpContext.Current.Response.C.Text.Encoding.GetEncoding("GB2312");
95 HttpContext.Current.Response.ContentType = "application/ms-";
96 // _page.EnableViewState = false;//是否保持视图状态
97
98 HttpContext.Current.Response.Write( HTML(dt,biaotou) );
99
100 HttpContext.Current.Response.End ();
101 }
102 #endregion
103 构造HTML#region 构造HTML
104 /**//// <summary>
105 /// 使用DataSet数据源
106 /// </summary>
107 /// <param name="ds"></param>
108 /// <param name="biaotou"></param>
109 /// <returns></returns>
110 private string HTML (DataSet ds,string[] biaotou)
111 {
112 StringBuilder ss = new StringBuilder();
113 ss.Append("<table>");
114 ss.Append("<tr>");
115 ss.Append(" <td>序号</td>");
116 foreach(string str in biaotou)
117 {
118 ss.Append(" <td> "+ str +"</td>");
119 }
120 ss.Append("</tr>");
121 int ii=1;
122 foreach (DataRow dr in ds.Tables[0].Rows)
123 {
124 ss.Append("<tr>");
125 ss.Append(" <td> "+ (ii++).ToString() +"</td>");
126 int I = dr.Table.Columns.Count;
127 for (int i=0;i<I;i++)
128 {
129 ss.Append(" <td> "+ dr[i].ToString() +"</td>");
130 }
131 ss.Append("</tr>");
132 }
133 ss.Append ("</table>");
134 return ss.ToString();
135 }
136 /**//// <summary>
137 /// 使用OleDbDataReader 数据源
138 /// </summary>
139 /// <param name="dr"></param>
140 /// <param name="biaotou"></param>
141 /// <returns></returns>
142 private string HTML (OleDbDataReader dr,string[] biaotou)
143 {
144 StringBuilder ss = new StringBuilder();
145 ss.Append("<table>");
146
147 ss.Append("<tr>");
148 ss.Append(" <td>序号</td>");
149 foreach(string str in biaotou)
150 {
151 ss.Append(" <td> "+ str +"</td>");
152 }
153 ss.Append("</tr>");
154 int ii=1;
155 while( dr.Read() )
156 {
157 ss.Append("<tr>");
158 ss.Append(" <td> "+ (ii++).ToString() +"</td>");
159 int I = dr.FieldCount;
160 for (int i=0;i<I;i++)
161 {
162 ss.Append(" <td> "+ dr[i].ToString() +"</td>");
163 }
164 ss.Append("</tr>");
165 }
166 ss.Append ("</table>");
167 dr.Close();
168 return ss.ToString();
169 }
170 /**//// <summary>
171 /// 使用DataTable数据源
172 /// </summary>
173 /// <param name="dt"></param>
174 /// <param name="biaotou"></param>
175 /// <returns></returns>
176 private string HTML (DataTable dt,string[] biaotou)
177 {
178 StringBuilder ss = new StringBuilder();
179 ss.Append("<table>");
180
181 ss.Append("<tr>");
182 ss.Append(" <td>序号</td>");
183 foreach(string str in biaotou)
184 {
185 ss.Append(" <td> "+ str +"</td>");
186 }
187 ss.Append("</tr>");
188 int ii=dt.Rows.Count;
189 foreach (DataRow dr in dt.Rows)
190 {
191 ss.Append("<tr>");
192 ss.Append(" <td> "+ (ii++).ToString() +"</td>");
193 int I = dr.Table.Columns.Count;
194 for (int i=0;i<I;i++)
195 {
196 ss.Append(" <td> "+ dr[i].ToString() +"</td>");
197 }
198 ss.Append("</tr>");
199 }
200 ss.Append ("</table>");
201
202 return ss.ToString();
203 }
204 #endregion
205}
206
}
using System;2
using System.Text;3
using System.Web;4
using System.Web.UI;5
using System.Web.UI.WebControls;6
using System.Data;7
using System.Data.OleDb;8
namespace JasonChou.BLL9
{10
/**//// <summary>11
/// BLL_Excel 的摘要说明。12
/// </summary>13
public class BLL_Excel14
{15
public BLL_Excel(){}16
17
导出Exce#region 导出Exce18
/**//// <summary>19
/// 导出Excel20
/// </summary>21
/// <param name="dr">OleDbDataReader 数据源</param>22
/// <param name="FileName">文件名</param>23
/// <param name="biaotou">表头</param>24
public void EduceExcel(OleDbDataReader dr,string FileName,string[] biaotou) 25
{ 26
HttpContext.Current.Response.Clear(); 27
HttpContext.Current.Response.Buffer= true;//设置缓冲输出 28
HttpContext.Current.Response.Charset="GB2312";//设置输出流的HTTP字符集29
HttpContext.Current.Response.AppendHeader("Content-Disposition","attachment;filename=\""+System.Web .HttpUtility.UrlEncode (FileName,System.Text .Encoding .UTF8 )+".xls\""); 30
HttpContext.Current.Response.C.Text.Encoding.GetEncoding("GB2312");31
HttpContext.Current.Response.ContentType = "application/ms-"; 32
// _page.EnableViewState = false;//是否保持视图状态33
34
HttpContext.Current.Response.Write( HTML(dr,biaotou) );35
36
HttpContext.Current.Response.End ();37
}38
/**//// <summary>39
/// 导出Excel40
/// </summary>41
/// <param name="_page">this</param>42
/// <param name="DB">DataGrid控件名称</param>43
/// <param name="FileName">要导出的文件名</param>44
public void EduceExcel(Page _page, DataGrid DB,string FileName) 45
{ 46
HttpContext.Current.Response.Clear(); 47
HttpContext.Current.Response.Buffer= true;//设置缓冲输出 48
HttpContext.Current.Response.Charset="GB2312";//设置输出流的HTTP字符集49
HttpContext.Current.Response.AppendHeader("Content-Disposition","attachment;filename=\""+System.Web .HttpUtility.UrlEncode (FileName,System.Text .Encoding .UTF8 )+".xls\""); 50
HttpContext.Current.Response.C.Text.Encoding.GetEncoding("GB2312");51
HttpContext.Current.Response.ContentType = "application/ms-"; 52
_page.EnableViewState = false;//是否保持视图状态53
System.IO.StringWriter oStringWriter = new System.IO.StringWriter(); //将信息写入字符串54
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter); //在WEB窗体上写出一系列的HTML特定字符和文本55
56
DB.RenderControl (oHtmlTextWriter);57
HttpContext.Current.Response.Write(oStringWriter.ToString ());58
59
HttpContext.Current.Response.End ();60
}61
/**//// <summary>62
/// 导出Excel63
/// </summary>64
/// <param name="ds">DataSet 数据源</param>65
/// <param name="FileName">文件名</param>66
/// <param name="biaotou">表头</param>67
public void EduceExcel(DataSet ds,string FileName,string[] biaotou) 68
{ 69
HttpContext.Current.Response.Clear(); 70
HttpContext.Current.Response.Buffer= true;//设置缓冲输出 71
HttpContext.Current.Response.Charset="GB2312";//设置输出流的HTTP字符集72
HttpContext.Current.Response.AppendHeader("Content-Disposition","attachment;filename=\""+System.Web .HttpUtility.UrlEncode (FileName,System.Text .Encoding .UTF8 )+".xls\""); 73
HttpContext.Current.Response.C.Text.Encoding.GetEncoding("GB2312");74
HttpContext.Current.Response.ContentType = "application/ms-"; 75
// _page.EnableViewState = false;//是否保持视图状态76
77
HttpContext.Current.Response.Write( HTML(ds,biaotou) );78
79
HttpContext.Current.Response.End ();80
}81
82
/**//// <summary>83
/// 导出Excel84
/// </summary>85
/// <param name="ds">DataSet 数据源</param>86
/// <param name="FileName">文件名</param>87
/// <param name="biaotou">表头</param>88
public void EduceExcel(DataTable dt,string FileName,string[] biaotou) 89
{ 90
HttpContext.Current.Response.Clear(); 91
HttpContext.Current.Response.Buffer= true;//设置缓冲输出 92
HttpContext.Current.Response.Charset="GB2312";//设置输出流的HTTP字符集93
HttpContext.Current.Response.AppendHeader("Content-Disposition","attachment;filename=\""+System.Web .HttpUtility.UrlEncode (FileName,System.Text .Encoding .UTF8 )+".xls\""); 94
HttpContext.Current.Response.C.Text.Encoding.GetEncoding("GB2312");95
HttpContext.Current.Response.ContentType = "application/ms-"; 96
// _page.EnableViewState = false;//是否保持视图状态97
98
HttpContext.Current.Response.Write( HTML(dt,biaotou) );99
100
HttpContext.Current.Response.End ();101
}102
#endregion103
构造HTML#region 构造HTML104
/**//// <summary>105
/// 使用DataSet数据源106
/// </summary>107
/// <param name="ds"></param>108
/// <param name="biaotou"></param>109
/// <returns></returns>110
private string HTML (DataSet ds,string[] biaotou)111
{112
StringBuilder ss = new StringBuilder();113
ss.Append("<table>");114
ss.Append("<tr>");115
ss.Append(" <td>序号</td>");116
foreach(string str in biaotou)117
{118
ss.Append(" <td> "+ str +"</td>");119
}120
ss.Append("</tr>");121
int ii=1;122
foreach (DataRow dr in ds.Tables[0].Rows)123
{124
ss.Append("<tr>");125
ss.Append(" <td> "+ (ii++).ToString() +"</td>");126
int I = dr.Table.Columns.Count;127
for (int i=0;i<I;i++)128
{129
ss.Append(" <td> "+ dr[i].ToString() +"</td>");130
}131
ss.Append("</tr>");132
}133
ss.Append ("</table>");134
return ss.ToString();135
}136
/**//// <summary>137
/// 使用OleDbDataReader 数据源138
/// </summary>139
/// <param name="dr"></param>140
/// <param name="biaotou"></param>141
/// <returns></returns>142
private string HTML (OleDbDataReader dr,string[] biaotou)143
{144
StringBuilder ss = new StringBuilder();145
ss.Append("<table>");146
147
ss.Append("<tr>");148
ss.Append(" <td>序号</td>");149
foreach(string str in biaotou)150
{151
ss.Append(" <td> "+ str +"</td>");152
}153
ss.Append("</tr>");154
int ii=1;155
while( dr.Read() )156
{157
ss.Append("<tr>");158
ss.Append(" <td> "+ (ii++).ToString() +"</td>");159
int I = dr.FieldCount;160
for (int i=0;i<I;i++)161
{162
ss.Append(" <td> "+ dr[i].ToString() +"</td>");163
}164
ss.Append("</tr>");165
}166
ss.Append ("</table>");167
dr.Close();168
return ss.ToString();169
}170
/**//// <summary>171
/// 使用DataTable数据源172
/// </summary>173
/// <param name="dt"></param>174
/// <param name="biaotou"></param>175
/// <returns></returns>176
private string HTML (DataTable dt,string[] biaotou)177
{178
StringBuilder ss = new StringBuilder();179
ss.Append("<table>");180
181
ss.Append("<tr>");182
ss.Append(" <td>序号</td>");183
foreach(string str in biaotou)184
{185
ss.Append(" <td> "+ str +"</td>");186
}187
ss.Append("</tr>");188
int ii=dt.Rows.Count;189
foreach (DataRow dr in dt.Rows)190
{191
ss.Append("<tr>");192
ss.Append(" <td> "+ (ii++).ToString() +"</td>");193
int I = dr.Table.Columns.Count;194
for (int i=0;i<I;i++)195
{196
ss.Append(" <td> "+ dr[i].ToString() +"</td>");197
}198
ss.Append("</tr>");199
}200
ss.Append ("</table>");201
202
return ss.ToString();203
}204
#endregion205
}206
} 
浙公网安备 33010602011771号