GridView导出Excel 类库
代码
1 using System;
2 using System.Data;
3 using System.Configuration;
4 using System.IO;
5 using System.Web;
6 using System.Web.Security;
7 using System.Web.UI;
8 using System.Web.UI.WebControls;
9 using System.Web.UI.WebControls.WebParts;
10 using System.Web.UI.HtmlControls;
11
12
13 namespace Unit
14 {
15
16 public class GridViewExportUtil
17 {
18 /// <summary>
19 /// 把 GridView 呈现的内容导出到 Excel
20 /// </summary>
21 /// <param name="fileName"></param>
22 /// <param name="gv"></param>
23 public static void Export(string fileName, GridView gv,HttpResponse pageResponse,string filepath)
24 {
25 pageResponse.Clear();
26 pageResponse.Charset = "UTF-8";
27 pageResponse.Buffer = false;
28 pageResponse.ClearHeaders();
29 pageResponse.ContentEncoding = System.Text.Encoding.UTF8;
30 pageResponse.HeaderEncoding = System.Text.Encoding.UTF8;
31 pageResponse.AddHeader("content-disposition", string.Format("attachment; filename={0}", fileName));
32 //pageResponse.ContentType = "application/ms-excel";
33 pageResponse.ContentType = "application/octet-stream";
34
35 using (StringWriter sw = new StringWriter())
36 {
37
38 using (HtmlTextWriter htw = new HtmlTextWriter(sw))
39 {
40 // Create a form to contain the grid
41 Table table = new Table();
42 table.Style.Add("border-top", "solid 1px #cdcdcd");
43 table.Style.Add("border-left", "solid 1px #cdcdcd");
44
45 // add the header row to the table
46 if (gv.HeaderRow != null)
47 {
48 GridViewExportUtil.PrepareControlForExport(gv.HeaderRow);
49 table.Rows.Add(gv.HeaderRow);
50 }
51
52 // add each of the data rows to the table
53 foreach (GridViewRow row in gv.Rows)
54 {
55 GridViewExportUtil.PrepareControlForExport(row);
56 table.Rows.Add(row);
57 }
58
59 // add the footer row to the table
60 if (gv.FooterRow != null)
61 {
62 GridViewExportUtil.PrepareControlForExport(gv.FooterRow);
63 table.Rows.Add(gv.FooterRow);
64 }
65
66 // render the table into the htmlwriter
67 table.RenderControl(htw);
68 string date = System.DateTime.Now.ToString("MMddyyyy");
69 string outFileName = "ExportExcel_" + date + fileName;
70 string comPath = "Export/" + outFileName;
71 string path = filepath + "\\Export";
72
73 if (Directory.Exists(path) == false)
74 {
75 System.IO.Directory.CreateDirectory(path);
76 }
77 path = path + "\\" + outFileName;
78 System.Text.Encoding encode = System.Text.Encoding.Unicode;
79 StreamWriter stream = new StreamWriter(path, false, encode);
80 stream.Write(sw.ToString());
81 stream.Close();
82
83 //stream.Write(sw);
84 //// render the htmlwriter into the response
85 pageResponse.Write(sw.ToString());
86 pageResponse.End();
87 pageResponse.Flush();
88 pageResponse.Close();
89 }
90 }
91 }
92
93 /// <summary>
94 /// 把 GridView 呈现的内容导出到 Excel
95 /// </summary>
96 /// <param name="fileName"></param>
97 /// <param name="gv"></param>
98 public static void Export(string fileName, GridView gv)
99 {
100 HttpContext.Current.Response.Clear();
101 HttpContext.Current.Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", fileName));
102 HttpContext.Current.Response.ContentType = "application/ms-excel";
103
104
105 using (StringWriter sw = new StringWriter())
106 {
107
108 using (HtmlTextWriter htw = new HtmlTextWriter(sw))
109 {
110 // Create a form to contain the grid
111 Table table = new Table();
112 table.Style.Add("border-top", "solid 1px #cdcdcd");
113 table.Style.Add("border-left", "solid 1px #cdcdcd");
114
115 // add the header row to the table
116 if (gv.HeaderRow != null)
117 {
118 GridViewExportUtil.PrepareControlForExport(gv.HeaderRow);
119 table.Rows.Add(gv.HeaderRow);
120 }
121
122 // add each of the data rows to the table
123 foreach (GridViewRow row in gv.Rows)
124 {
125 GridViewExportUtil.PrepareControlForExport(row);
126 table.Rows.Add(row);
127 }
128
129 // add the footer row to the table
130 if (gv.FooterRow != null)
131 {
132 GridViewExportUtil.PrepareControlForExport(gv.FooterRow);
133 table.Rows.Add(gv.FooterRow);
134 }
135
136 // render the table into the htmlwriter
137 table.RenderControl(htw);
138
139
140 // render the htmlwriter into the response
141 HttpContext.Current.Response.Write(sw.ToString());
142 HttpContext.Current.Response.End();
143 }
144 }
145 }
146
147 /// <summary>
148 /// 把 GridView 呈现的内容导出到 Excel
149 /// </summary>
150 /// <param name="fileName"></param>
151 /// <param name="gv"></param>
152 public static string GetExportFileName(string fileName, GridView gv, string filepath)
153 {
154 try
155 {
156
157 using (StringWriter sw = new StringWriter())
158 {
159
160 using (HtmlTextWriter htw = new HtmlTextWriter(sw))
161 {
162 // Create a form to contain the grid
163 Table table = new Table();
164 table.Style.Add("border-top", "solid 1px #cdcdcd");
165 table.Style.Add("border-left", "solid 1px #cdcdcd");
166
167 // add the header row to the table
168 if (gv.HeaderRow != null)
169 {
170 GridViewExportUtil.PrepareControlForExport(gv.HeaderRow);
171 table.Rows.Add(gv.HeaderRow);
172 }
173
174 // add each of the data rows to the table
175 foreach (GridViewRow row in gv.Rows)
176 {
177 GridViewExportUtil.PrepareControlForExport(row);
178 table.Rows.Add(row);
179 }
180
181 // add the footer row to the table
182 if (gv.FooterRow != null)
183 {
184 GridViewExportUtil.PrepareControlForExport(gv.FooterRow);
185 table.Rows.Add(gv.FooterRow);
186 }
187
188 // render the table into the htmlwriter
189 table.RenderControl(htw);
190 string date = System.DateTime.Now.ToString("MMddyyyy");
191 string outFileName = "ExportExcel_" + date + "_" +DateTime.Now.Ticks.ToString() + "_" + fileName;
192 string comPath = "Export/" + outFileName;
193 string path = filepath + "\\Export";
194
195 if (Directory.Exists(path) == false)
196 {
197 System.IO.Directory.CreateDirectory(path);
198 }
199 path = path + "\\" + outFileName;
200 System.Text.Encoding encode = System.Text.Encoding.Unicode;
201 StreamWriter stream = new StreamWriter(path, false, encode);
202 stream.Write(sw.ToString());
203 stream.Close();
204
205 return comPath;
206
207 }
208 }
209 }
210 catch (Exception e)
211 {
212 throw;
213 }
214 }
215 /// <summary>
216 /// Replace any of the contained controls with literals
217 /// </summary>
218 /// <param name="control"></param>
219 private static void PrepareControlForExport(Control control)
220 {
221 for (int i = 0; i < control.Controls.Count; i++)
222 {
223 Control current = control.Controls[i];
224 if (current is LinkButton)
225 {
226 control.Controls.Remove(current);
227 control.Controls.AddAt(i, new LiteralControl((current as LinkButton).Text));
228 }
229 else if (current is ImageButton)
230 {
231 control.Controls.Remove(current);
232 control.Controls.AddAt(i, new LiteralControl((current as ImageButton).AlternateText));
233 }
234 else if (current is HyperLink)
235 {
236 control.Controls.Remove(current);
237 control.Controls.AddAt(i, new LiteralControl((current as HyperLink).Text));
238 }
239 else if (current is DropDownList)
240 {
241 control.Controls.Remove(current);
242 control.Controls.AddAt(i, new LiteralControl((current as DropDownList).SelectedItem.Text));
243 }
244 else if (current is CheckBox)
245 {
246 control.Controls.Remove(current);
247 control.Controls.AddAt(i, new LiteralControl((current as CheckBox).Checked ? "True" : "False"));
248 }
249 else if (current is System.Web.UI.WebControls.DataControlFieldHeaderCell)
250 {
251 ((DataControlFieldHeaderCell)current).Style.Add("border-right", "solid 1px #cdcdcd");
252 ((DataControlFieldHeaderCell)current).Style.Add("border-bottom", "solid 1px #cdcdcd");
253 ((DataControlFieldHeaderCell)current).Style.Add("font-family", "arial");
254 ((DataControlFieldHeaderCell)current).Style.Add("color", "#3D3D3D");
255 ((DataControlFieldHeaderCell)current).Style.Add("font-size", "10pt");
256 ((DataControlFieldHeaderCell)current).Style.Add("background-color", "#F1FBFF");
257 }
258 else if (current is System.Web.UI.WebControls.DataControlFieldCell)
259 {
260 ((DataControlFieldCell)current).Style.Add("border-right", "solid 1px #cdcdcd");
261 ((DataControlFieldCell)current).Style.Add("border-bottom", "solid 1px #cdcdcd");
262 ((DataControlFieldCell)current).Style.Add("font-family", "arial");
263 ((DataControlFieldCell)current).Style.Add("color", "#3D3D3D");
264 ((DataControlFieldCell)current).Style.Add("font-size", "10pt");
265
266 }
267
268 if (current.HasControls())
269 {
270 GridViewExportUtil.PrepareControlForExport(current);
271 }
272 }
273 }
274 }
275 }
2 using System.Data;
3 using System.Configuration;
4 using System.IO;
5 using System.Web;
6 using System.Web.Security;
7 using System.Web.UI;
8 using System.Web.UI.WebControls;
9 using System.Web.UI.WebControls.WebParts;
10 using System.Web.UI.HtmlControls;
11
12
13 namespace Unit
14 {
15
16 public class GridViewExportUtil
17 {
18 /// <summary>
19 /// 把 GridView 呈现的内容导出到 Excel
20 /// </summary>
21 /// <param name="fileName"></param>
22 /// <param name="gv"></param>
23 public static void Export(string fileName, GridView gv,HttpResponse pageResponse,string filepath)
24 {
25 pageResponse.Clear();
26 pageResponse.Charset = "UTF-8";
27 pageResponse.Buffer = false;
28 pageResponse.ClearHeaders();
29 pageResponse.ContentEncoding = System.Text.Encoding.UTF8;
30 pageResponse.HeaderEncoding = System.Text.Encoding.UTF8;
31 pageResponse.AddHeader("content-disposition", string.Format("attachment; filename={0}", fileName));
32 //pageResponse.ContentType = "application/ms-excel";
33 pageResponse.ContentType = "application/octet-stream";
34
35 using (StringWriter sw = new StringWriter())
36 {
37
38 using (HtmlTextWriter htw = new HtmlTextWriter(sw))
39 {
40 // Create a form to contain the grid
41 Table table = new Table();
42 table.Style.Add("border-top", "solid 1px #cdcdcd");
43 table.Style.Add("border-left", "solid 1px #cdcdcd");
44
45 // add the header row to the table
46 if (gv.HeaderRow != null)
47 {
48 GridViewExportUtil.PrepareControlForExport(gv.HeaderRow);
49 table.Rows.Add(gv.HeaderRow);
50 }
51
52 // add each of the data rows to the table
53 foreach (GridViewRow row in gv.Rows)
54 {
55 GridViewExportUtil.PrepareControlForExport(row);
56 table.Rows.Add(row);
57 }
58
59 // add the footer row to the table
60 if (gv.FooterRow != null)
61 {
62 GridViewExportUtil.PrepareControlForExport(gv.FooterRow);
63 table.Rows.Add(gv.FooterRow);
64 }
65
66 // render the table into the htmlwriter
67 table.RenderControl(htw);
68 string date = System.DateTime.Now.ToString("MMddyyyy");
69 string outFileName = "ExportExcel_" + date + fileName;
70 string comPath = "Export/" + outFileName;
71 string path = filepath + "\\Export";
72
73 if (Directory.Exists(path) == false)
74 {
75 System.IO.Directory.CreateDirectory(path);
76 }
77 path = path + "\\" + outFileName;
78 System.Text.Encoding encode = System.Text.Encoding.Unicode;
79 StreamWriter stream = new StreamWriter(path, false, encode);
80 stream.Write(sw.ToString());
81 stream.Close();
82
83 //stream.Write(sw);
84 //// render the htmlwriter into the response
85 pageResponse.Write(sw.ToString());
86 pageResponse.End();
87 pageResponse.Flush();
88 pageResponse.Close();
89 }
90 }
91 }
92
93 /// <summary>
94 /// 把 GridView 呈现的内容导出到 Excel
95 /// </summary>
96 /// <param name="fileName"></param>
97 /// <param name="gv"></param>
98 public static void Export(string fileName, GridView gv)
99 {
100 HttpContext.Current.Response.Clear();
101 HttpContext.Current.Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", fileName));
102 HttpContext.Current.Response.ContentType = "application/ms-excel";
103
104
105 using (StringWriter sw = new StringWriter())
106 {
107
108 using (HtmlTextWriter htw = new HtmlTextWriter(sw))
109 {
110 // Create a form to contain the grid
111 Table table = new Table();
112 table.Style.Add("border-top", "solid 1px #cdcdcd");
113 table.Style.Add("border-left", "solid 1px #cdcdcd");
114
115 // add the header row to the table
116 if (gv.HeaderRow != null)
117 {
118 GridViewExportUtil.PrepareControlForExport(gv.HeaderRow);
119 table.Rows.Add(gv.HeaderRow);
120 }
121
122 // add each of the data rows to the table
123 foreach (GridViewRow row in gv.Rows)
124 {
125 GridViewExportUtil.PrepareControlForExport(row);
126 table.Rows.Add(row);
127 }
128
129 // add the footer row to the table
130 if (gv.FooterRow != null)
131 {
132 GridViewExportUtil.PrepareControlForExport(gv.FooterRow);
133 table.Rows.Add(gv.FooterRow);
134 }
135
136 // render the table into the htmlwriter
137 table.RenderControl(htw);
138
139
140 // render the htmlwriter into the response
141 HttpContext.Current.Response.Write(sw.ToString());
142 HttpContext.Current.Response.End();
143 }
144 }
145 }
146
147 /// <summary>
148 /// 把 GridView 呈现的内容导出到 Excel
149 /// </summary>
150 /// <param name="fileName"></param>
151 /// <param name="gv"></param>
152 public static string GetExportFileName(string fileName, GridView gv, string filepath)
153 {
154 try
155 {
156
157 using (StringWriter sw = new StringWriter())
158 {
159
160 using (HtmlTextWriter htw = new HtmlTextWriter(sw))
161 {
162 // Create a form to contain the grid
163 Table table = new Table();
164 table.Style.Add("border-top", "solid 1px #cdcdcd");
165 table.Style.Add("border-left", "solid 1px #cdcdcd");
166
167 // add the header row to the table
168 if (gv.HeaderRow != null)
169 {
170 GridViewExportUtil.PrepareControlForExport(gv.HeaderRow);
171 table.Rows.Add(gv.HeaderRow);
172 }
173
174 // add each of the data rows to the table
175 foreach (GridViewRow row in gv.Rows)
176 {
177 GridViewExportUtil.PrepareControlForExport(row);
178 table.Rows.Add(row);
179 }
180
181 // add the footer row to the table
182 if (gv.FooterRow != null)
183 {
184 GridViewExportUtil.PrepareControlForExport(gv.FooterRow);
185 table.Rows.Add(gv.FooterRow);
186 }
187
188 // render the table into the htmlwriter
189 table.RenderControl(htw);
190 string date = System.DateTime.Now.ToString("MMddyyyy");
191 string outFileName = "ExportExcel_" + date + "_" +DateTime.Now.Ticks.ToString() + "_" + fileName;
192 string comPath = "Export/" + outFileName;
193 string path = filepath + "\\Export";
194
195 if (Directory.Exists(path) == false)
196 {
197 System.IO.Directory.CreateDirectory(path);
198 }
199 path = path + "\\" + outFileName;
200 System.Text.Encoding encode = System.Text.Encoding.Unicode;
201 StreamWriter stream = new StreamWriter(path, false, encode);
202 stream.Write(sw.ToString());
203 stream.Close();
204
205 return comPath;
206
207 }
208 }
209 }
210 catch (Exception e)
211 {
212 throw;
213 }
214 }
215 /// <summary>
216 /// Replace any of the contained controls with literals
217 /// </summary>
218 /// <param name="control"></param>
219 private static void PrepareControlForExport(Control control)
220 {
221 for (int i = 0; i < control.Controls.Count; i++)
222 {
223 Control current = control.Controls[i];
224 if (current is LinkButton)
225 {
226 control.Controls.Remove(current);
227 control.Controls.AddAt(i, new LiteralControl((current as LinkButton).Text));
228 }
229 else if (current is ImageButton)
230 {
231 control.Controls.Remove(current);
232 control.Controls.AddAt(i, new LiteralControl((current as ImageButton).AlternateText));
233 }
234 else if (current is HyperLink)
235 {
236 control.Controls.Remove(current);
237 control.Controls.AddAt(i, new LiteralControl((current as HyperLink).Text));
238 }
239 else if (current is DropDownList)
240 {
241 control.Controls.Remove(current);
242 control.Controls.AddAt(i, new LiteralControl((current as DropDownList).SelectedItem.Text));
243 }
244 else if (current is CheckBox)
245 {
246 control.Controls.Remove(current);
247 control.Controls.AddAt(i, new LiteralControl((current as CheckBox).Checked ? "True" : "False"));
248 }
249 else if (current is System.Web.UI.WebControls.DataControlFieldHeaderCell)
250 {
251 ((DataControlFieldHeaderCell)current).Style.Add("border-right", "solid 1px #cdcdcd");
252 ((DataControlFieldHeaderCell)current).Style.Add("border-bottom", "solid 1px #cdcdcd");
253 ((DataControlFieldHeaderCell)current).Style.Add("font-family", "arial");
254 ((DataControlFieldHeaderCell)current).Style.Add("color", "#3D3D3D");
255 ((DataControlFieldHeaderCell)current).Style.Add("font-size", "10pt");
256 ((DataControlFieldHeaderCell)current).Style.Add("background-color", "#F1FBFF");
257 }
258 else if (current is System.Web.UI.WebControls.DataControlFieldCell)
259 {
260 ((DataControlFieldCell)current).Style.Add("border-right", "solid 1px #cdcdcd");
261 ((DataControlFieldCell)current).Style.Add("border-bottom", "solid 1px #cdcdcd");
262 ((DataControlFieldCell)current).Style.Add("font-family", "arial");
263 ((DataControlFieldCell)current).Style.Add("color", "#3D3D3D");
264 ((DataControlFieldCell)current).Style.Add("font-size", "10pt");
265
266 }
267
268 if (current.HasControls())
269 {
270 GridViewExportUtil.PrepareControlForExport(current);
271 }
272 }
273 }
274 }
275 }
调用方法：
protected void ButtonExportGrid_Click(object sender, EventArgs e)
{
DataTable dtTrListExport = bllTr_list.GetDataList(DatePickerBegintime.StringValue.ToString().Trim(), DatePickerEndtime.StringValue.ToString().Trim());
dtTravellingListExport.Columns.Remove(dtTrListExport .Columns["tr_id"]);
dtTravellingListExport.Columns.Remove(dtTrListExport .Columns["Itinerary"]);
dtTravellingListExport.Columns.Remove(dtTrListExport .Columns["Durat"]);
this.GridViewReport.AllowPaging = false;
this.GridViewReport.DataSource = dtTrListExport ;
this.GridViewReport.DataBind();
string fileName = GridViewExportUtil.GetExportFileName("Customers.xls", this.GridViewReport, Server.MapPath(""));
HttpResponse httpResponse = System.Web.HttpContext.Current.Response;
FileInfo fileinfo = new FileInfo(Server.MapPath("") + @"\" + fileName);
httpResponse.Clear();
httpResponse.ClearHeaders();
httpResponse.Buffer = false;
httpResponse.ContentType = "application/octet-stream";
httpResponse.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileinfo.Name, System.Text.Encoding.UTF8));
//httpResponse.AppendHeader("Content-Length", file.Length.ToString());
httpResponse.WriteFile(fileinfo.FullName);
httpResponse.End();
httpResponse.Flush();
httpResponse.Close();
this.GridViewReport.AllowPaging = true;
}
