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

  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; 
                httpResponse.Clear(); 
                httpResponse.Buffer = true; 
                httpResponse.Charset = "gb2312"; 
                string fileName = DateTime.Now.ToString("yyyyMMddHHmmssms") + ".xls"; 
                httpResponse.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName, 
                    System.Text.Encoding.UTF8)); 
                httpResponse.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312"); 
                httpResponse.ContentType = "application/ms-excel"; 
                System.IO.StringWriter tw = new System.IO.StringWriter(); 
                System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw); 
                dtData.RenderControl(hw); 
                hw.Write(footString); 
                string directoryPath = page.Server.MapPath("~/") + "TempExcel"; 
                string filePath = page.Server.MapPath("~/") + "TempExcel\\" + fileName; 
                if (!System.IO.Directory.Exists(directoryPath)) 
                { 
                    System.IO.Directory.CreateDirectory(directoryPath); 
                } 
                System.IO.StreamWriter sw = System.IO.File.CreateText(filePath); 
                sw.Write(tw.ToString()); 
                sw.Close(); 
                DownFile(httpResponse, fileName, filePath); 
                httpResponse.End(); 
            } 
        } 

        private static bool DownFile(System.Web.HttpResponse Response, string fileName, string fullPath) 
        { 
            System.IO.FileStream fs = System.IO.File.OpenRead(fullPath); 
            try 
            { 
                Response.ContentType = "application/octet-stream"; 

                Response.AppendHeader("Content-Disposition", "attachment;filename=" + 
                HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8) + ";charset=GB2312"); 
                long fLen = fs.Length; 
                int size = 102400;//每100K同时下载数据  
                byte[] readData = new byte[size];//指定缓冲区的大小  
                if (size > fLen) size = Convert.ToInt32(fLen); 
                long fPos = 0; 
                bool isEnd = false; 
                while (!isEnd) 
                { 
                    if ((fPos + size) > fLen) 
                    { 
                        size = Convert.ToInt32(fLen - fPos); 
                        readData = new byte[size]; 
                        isEnd = true; 
                    } 
                    fs.Read(readData, 0, size);//读入一个压缩块 
                    if (readData.Length > 0) 
                        Response.BinaryWrite(readData); 
                    fPos += size; 
                } 
                return true; 
            } 
            catch 
            { 
                return false; 
            } 
            finally 
            { 
                fs.Close(); 
                System.IO.File.Delete(fullPath); 
            } 
        } 
    } 
} 


.CS文件后台调用的方法(解决方案中需在Code文件夹中引用Utils.cs文件) 

//导出表格按钮 
        protected void btnExportDatas_Click(object sender, EventArgs e) 
        { 
            if (dgOrderList.Items.Count == 0) 
            { 
                Tools.Alert("当前无可导出数据!", this.Page); 
            } 
            else 
            { 
                int totalOrder = 0; 

                //总面值 
                decimal sumFaceMoney = 0M; 

                //总金额 
                decimal sumRealMoney = 0M; 

                try 
                { 
                    DataSet ds = ChargeOrderService.GetChargeOrdersSumData(false, queryCondition(), this.pager.RecordCount, 1, 
                        out totalOrder, true, out sumFaceMoney, out sumRealMoney); 

                    string usedColumnString = "OrderId,CreatedDate,CardNumber,FaceMoney,RequestMoney,RealCZMoney,Status,CsLoginId"; 
                    
                    DataTable dt = DataTableUtil.RejectColumn(usedColumnString.Split(','), ds.Tables[0]); 
                    DataTableUtil.ReplaceColumnName(dt, "OrderId", "充值订单号");                     
                    DataTableUtil.ReplaceColumnName(dt, "CreatedDate","时间"); 
                    DataTableUtil.ReplaceColumnName(dt, "CardNumber", "卡号");                       
                    DataTableUtil.ReplaceColumnName(dt, "FaceMoney", "面额"); 
                    DataTableUtil.ReplaceColumnName(dt, "RequestMoney", "申请金额"); 
                    DataTableUtil.ReplaceColumnName(dt, "RealCZMoney", "实际金额"); 
                    DataTableUtil.ReplaceColumnName(dt, "Status", "状态"); 
                    DataTableUtil.ReplaceColumnName(dt, "CsLoginId", "操作客服"); 

                    string footString = string.Format("总面值:{0}  总金额:{1}", sumFaceMoney, sumRealMoney); 

                    DataGrid dtData = new DataGrid(); 
                    dtData.ItemDataBound += new DataGridItemEventHandler(dtData_ItemDataBound); 
                    dtData.DataSource = dt; 
                    dtData.DataBind(); 
                    ExcelUtil.ExportToExcel(dtData, footString, this); 
                } 
                catch (Exception ex) 
                { 
                    Tools.Alert(ex.Message, this.Page); 
                    return; 
                } 
            } 
        } 

        void dtData_ItemDataBound(object sender, DataGridItemEventArgs e) 
        { 
            if (e.Item.ItemType == ListItemType.Item || 
                e.Item.ItemType == ListItemType.AlternatingItem) 
            { 
                //在此将显示的值中加入单引号,目的是excel只显示15位的数值,如果显示18位的话excel只显示15位正确的后面是用0代替的,所以需要转换 
                e.Item.Cells[2].Attributes.Add("style", "vnd.ms-excel.numberformat:@"); 
                e.Item.Cells[6].Text = GetCZOrderStatus(e.Item.Cells[6].Text); 
            } 
        } 

        private string GetCZOrderStatus(string value) 
        { 
            CZOrderSatus status = (CZOrderSatus)(int.Parse(value)); 
            switch (status) 
            { 
                case CZOrderSatus.Initial: 
                    return "初始"; 
                case CZOrderSatus.CheckOut: 
                    return "签出"; 
                case CZOrderSatus.Success: 
                    return "成功";                 
                case CZOrderSatus.Failure: 
                    return "失败"; 
                case CZOrderSatus.CheckIn: 
                    return "签入"; 
                case CZOrderSatus.Finished: 
                    return "结束"; 
                default: 
                    return "_未知_"; 
            }              
        } 
    }