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

Code
  1Utils.cs 文件内容: 
  2using System; 
  3using System.Data; 
  4using System.Configuration; 
  5using System.Web; 
  6using System.Web.Security; 
  7using System.Web.UI; 
  8using System.Web.UI.WebControls; 
  9using System.Web.UI.WebControls.WebParts; 
 10using System.Web.UI.HtmlControls; 
 11
 12using OnlineCard.Comm.AdminComm.Lib; 
 13
 14namespace OCAdminUI 
 15{ 
 16
 17    public delegate string ReplaceColumnValueHandler(object value); 
 18
 19    public class DataTableUtil 
 20    { 
 21
 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        } 
 34
 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        } 
 52
 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        } 
 71
 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    } 
 84
 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; 
 98
 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        } 
125
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"; 
132
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} 
168
169
170.CS文件后台调用的方法(解决方案中需在Code文件夹中引用Utils.cs文件) 
171
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; 
182
183                //总面值 
184                decimal sumFaceMoney = 0M; 
185
186                //总金额 
187                decimal sumRealMoney = 0M; 
188
189                try 
190                { 
191                    DataSet ds = ChargeOrderService.GetChargeOrdersSumData(false, queryCondition(), this.pager.RecordCount, 1, 
192                        out totalOrder, true, out sumFaceMoney, out sumRealMoney); 
193
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", "操作客服"); 
205
206                    string footString = string.Format("总面值:{0}  总金额:{1}", sumFaceMoney, sumRealMoney); 
207
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        } 
221
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        } 
232
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    } 
255
256

转载于:https://www.cnblogs.com/happyriver/archive/2008/11/10/1330322.html