一个ASP.NE导出Excel类
支持DataSet、DataReader、DataTable、DataGrid等数据源
看起来有点耍赖,用HTML构造的,但是能用就行
1using 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}
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}