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

Utils.cs 文件内容: using System; using System.Data; using System.Configuration; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using OnlineCard.Comm.AdminComm.Lib; namespace OCAdminUI { public delegate string ReplaceColumnValueHandler(object value); public class DataTableUtil { ///

/// 剔除无用的列, 并根据传入的列名顺序重新排列 ///

/// 需要显示的列(列名之间用,号割开，如："Id,Name,Email") /// 要进行修饰的 DataTable /// public static DataTable RejectColumn(string[] visibleCols, DataTable dt) { DataView dv = new DataView(dt); DataTable outTable = dv.ToTable(dt.TableName, false, visibleCols); return outTable; } ///

/// 替换列名(列名不区分大小些) ///

/// /// /// public static void ReplaceColumnName(DataTable dt, string sourceColumnName, string replaceColumnName) { for (int i = 0; i < dt.Columns.Count; i++) { if (dt.Columns[i].ColumnName.ToLower() == sourceColumnName.ToLower()) { dt.Columns[i].ColumnName = replaceColumnName; return; } } } ///

/// 替换列的值 ///

/// /// /// public static void ReplaceEachColumnValue(DataTable dt, string columnName, ReplaceColumnValueHandler handler) { int index = FindColumnIndex(dt, columnName); if (index > -1) { dt.Columns[index].DataType = typeof(string); foreach (DataRow row in dt.Rows) { row[index] = handler(row[index].ToString()); } } } private static int FindColumnIndex(DataTable dt, string columnName) { for (int i = 0; i < dt.Columns.Count; i++) { if (dt.Columns[i].ColumnName.ToLower() == columnName.ToLower()) { return i; } } return -1; } } public class ExcelUtil { public static void ExportToExcel(DataGrid dtData, string footString, System.Web.UI.Page page) { if (dtData != null) { if (dtData.Items.Count == 0) { Tools.Alert("当前无数据导出", page); return; } dtData.AllowPaging = false; dtData.AutoGenerateColumns = false; 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 "_未知_"; } } }