将DataGrid中的数据导出为Excel的方法

1. Utils.cs 文件内容:
2. using System;
3. using System.Data;
4. using System.Configuration;
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;
12. using OnlineCard.Comm.AdminComm.Lib;
14. namespace OCAdminUI
15. {
17.     public delegate string ReplaceColumnValueHandler(object value);
19.     public class DataTableUtil
20.     {
22.         /// <summary>
23.         /// 剔除无用的列, 并根据传入的列名顺序重新排列
24.         /// </summary>
25.         /// <param name="visibleCols">需要显示的列(列名之间用,号割开，如："Id,Name,Email")</param>
26.         /// <param name="dt">要进行修饰的 DataTable</param>
27.         /// <returns></returns>
28.         public static DataTable RejectColumn(string[] visibleCols, DataTable dt)
29.         {
30.             DataView dv = new DataView(dt);
31.             DataTable outTable = dv.ToTable(dt.TableName, false, visibleCols);
32.             return outTable;
33.         }
35.         /// <summary>
36.         /// 替换列名(列名不区分大小些)
37.         /// </summary>
38.         /// <param name="dt"></param>
39.         /// <param name="sourceColumnName"></param>
40.         /// <param name="replaceColumnName"></param>
41.         public static void ReplaceColumnName(DataTable dt, string sourceColumnName, string replaceColumnName)
42.         {
43.             for (int i = 0; i < dt.Columns.Count; i++)
44.             {
45.                 if (dt.Columns[i].ColumnName.ToLower() == sourceColumnName.ToLower())
46.                 {
47.                     dt.Columns[i].ColumnName = replaceColumnName;
48.                     return;
49.                 }
50.             }
51.         }
53.         /// <summary>
54.         /// 替换列的值
55.         /// </summary>
56.         /// <param name="dt"></param>
57.         /// <param name="columnName"></param>
58.         /// <param name="handler"></param>
59.         public static void ReplaceEachColumnValue(DataTable dt, string columnName, ReplaceColumnValueHandler handler)
60.         {
61.             int index = FindColumnIndex(dt, columnName);
62.             if (index > -1)
63.             {
64.                 dt.Columns[index].DataType = typeof(string);
65.                 foreach (DataRow row in dt.Rows)
66.                 {
67.                     row[index] = handler(row[index].ToString());
68.                 }
69.             }
70.         }
72.         private static int FindColumnIndex(DataTable dt, string columnName)
73.         {
74.             for (int i = 0; i < dt.Columns.Count; i++)
75.             {
76.                 if (dt.Columns[i].ColumnName.ToLower() == columnName.ToLower())
77.                 {
78.                     return i;
79.                 }
80.             }
81.             return -1;
82.         }
83.     }
85.     public class ExcelUtil
86.     {
87.         public static void ExportToExcel(DataGrid dtData, string footString, System.Web.UI.Page page)
88.         {
89.             if (dtData != null)
90.             {
91.                 if (dtData.Items.Count == 0)
92.                 {
93.                     Tools.Alert("当前无数据导出", page);
94.                     return;
95.                 }
96.                 dtData.AllowPaging = false;
97.                 dtData.AutoGenerateColumns = false;
99.                 System.Web.HttpResponse httpResponse = page.Response;
100.                 httpResponse.Clear();
101.                 httpResponse.Buffer = true;
102.                 httpResponse.Charset = "gb2312";
103.                 string fileName = DateTime.Now.ToString("yyyyMMddHHmmssms") + ".xls";
104.                 httpResponse.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName,
105.                     System.Text.Encoding.UTF8));
106.                 httpResponse.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
107.                 httpResponse.ContentType = "application/ms-excel";
108.                 System.IO.StringWriter tw = new System.IO.StringWriter();
109.                 System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
110.                 dtData.RenderControl(hw);
111.                 hw.Write(footString);
112.                 string directoryPath = page.Server.MapPath("~/") + "TempExcel";
113.                 string filePath = page.Server.MapPath("~/") + "TempExcel//" + fileName;
114.                 if (!System.IO.Directory.Exists(directoryPath))
115.                 {
116.                     System.IO.Directory.CreateDirectory(directoryPath);
117.                 }
118.                 System.IO.StreamWriter sw = System.IO.File.CreateText(filePath);
119.                 sw.Write(tw.ToString());
120.                 sw.Close();
121.                 DownFile(httpResponse, fileName, filePath);
122.                 httpResponse.End();
123.             }
124.         }
126.         private static bool DownFile(System.Web.HttpResponse Response, string fileName, string fullPath)
127.         {
128.             System.IO.FileStream fs = System.IO.File.OpenRead(fullPath);
129.             try
130.             {
131.                 Response.ContentType = "application/octet-stream";
133.                 Response.AppendHeader("Content-Disposition", "attachment;filename=" +
134.                 HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8) + ";charset=GB2312");
135.                 long fLen = fs.Length;
136.                 int size = 102400;//每100K同时下载数据 
137.                 byte[] readData = new byte[size];//指定缓冲区的大小 
138.                 if (size > fLen) size = Convert.ToInt32(fLen);
139.                 long fPos = 0;
140.                 bool isEnd = false;
141.                 while (!isEnd)
142.                 {
143.                     if ((fPos + size) > fLen)
144.                     {
145.                         size = Convert.ToInt32(fLen - fPos);
146.                         readData = new byte[size];
147.                         isEnd = true;
148.                     }
149.                     fs.Read(readData, 0, size);//读入一个压缩块
150.                     if (readData.Length > 0)
151.                         Response.BinaryWrite(readData);
152.                     fPos += size;
153.                 }
154.                 return true;
155.             }
156.             catch
157.             {
158.                 return false;
159.             }
160.             finally
161.             {
162.                 fs.Close();
163.                 System.IO.File.Delete(fullPath);
164.             }
165.         }
166.     }
167. }
170. .CS文件后台调用的方法(解决方案中需在Code文件夹中引用Utils.cs文件)
172. //导出表格按钮
173.         protected void btnExportDatas_Click(object sender, EventArgs e)
174.         {
175.             if (dgOrderList.Items.Count == 0)
176.             {
177.                 Tools.Alert("当前无可导出数据!", this.Page);
178.             }
179.             else
180.             {
181.                 int totalOrder = 0;
183.                 //总面值
184.                 decimal sumFaceMoney = 0M;
186.                 //总金额
187.                 decimal sumRealMoney = 0M;
189.                 try
190.                 {
191.                     DataSet ds = ChargeOrderService.GetChargeOrdersSumData(false, queryCondition(), this.pager.RecordCount, 1,
192.                         out totalOrder, true, out sumFaceMoney, out sumRealMoney);
194.                     string usedColumnString = "OrderId,CreatedDate,CardNumber,FaceMoney,RequestMoney,RealCZMoney,Status,CsLoginId";
195.                    
196.                     DataTable dt = DataTableUtil.RejectColumn(usedColumnString.Split(','), ds.Tables[0]);
197.                     DataTableUtil.ReplaceColumnName(dt, "OrderId", "充值订单号");                    
198.                     DataTableUtil.ReplaceColumnName(dt, "CreatedDate","时间");
199.                     DataTableUtil.ReplaceColumnName(dt, "CardNumber", "卡号");                      
200.                     DataTableUtil.ReplaceColumnName(dt, "FaceMoney", "面额");
201.                     DataTableUtil.ReplaceColumnName(dt, "RequestMoney", "申请金额");
202.                     DataTableUtil.ReplaceColumnName(dt, "RealCZMoney", "实际金额");
203.                     DataTableUtil.ReplaceColumnName(dt, "Status", "状态");
204.                     DataTableUtil.ReplaceColumnName(dt, "CsLoginId", "操作客服");
206.                     string footString = string.Format("总面值:{0}  总金额:{1}", sumFaceMoney, sumRealMoney);
208.                     DataGrid dtData = new DataGrid();
209.                     dtData.ItemDataBound += new DataGridItemEventHandler(dtData_ItemDataBound);
210.                     dtData.DataSource = dt;
211.                     dtData.DataBind();
212.                     ExcelUtil.ExportToExcel(dtData, footString, this);
213.                 }
214.                 catch (Exception ex)
215.                 {
216.                     Tools.Alert(ex.Message, this.Page);
217.                     return;
218.                 }
219.             }
220.         }
222.         void dtData_ItemDataBound(object sender, DataGridItemEventArgs e)
223.         {
224.             if (e.Item.ItemType == ListItemType.Item ||
225.                 e.Item.ItemType == ListItemType.AlternatingItem)
226.             {
227.                 //在此将显示的值中加入单引号,目的是excel只显示15位的数值,如果显示18位的话excel只显示15位正确的后面是用0代替的,所以需要转换
228.                 e.Item.Cells[2].Attributes.Add("style", "vnd.ms-excel.numberformat:@");
229.                 e.Item.Cells[6].Text = GetCZOrderStatus(e.Item.Cells[6].Text);
230.             }
231.         }
233.         private string GetCZOrderStatus(string value)
234.         {
235.             CZOrderSatus status = (CZOrderSatus)(int.Parse(value));
236.             switch (status)
237.             {
238.                 case CZOrderSatus.Initial:
239.                     return "初始";
240.                 case CZOrderSatus.CheckOut:
241.                     return "签出";
242.                 case CZOrderSatus.Success:
243.                     return "成功";                
244.                 case CZOrderSatus.Failure:
245.                     return "失败";
246.                 case CZOrderSatus.CheckIn:
247.                     return "签入";
248.                 case CZOrderSatus.Finished:
249.                     return "结束";
250.                 default:
251.                     return "_未知_";
252.             }             
253.         }
254.     }