有两种方式导出数据,都可以实现Gridview导出excel,如果Gridview是分页的,解决了只能导出当前页的问题
第一种
例: 在A.aspx页面上 放入Gridview 在导出excel的button的单击事件里不做处理只调转到另外一个页面,这么做是为了导出所有数据不止是当前页的
例如
protected void Button1_Click(object sender, EventArgs e){Response.Redirect("B.aspx?startTime=" + startTime + "&endTime=" + endTime + "&userID=" + userID);}
在B页面的aspx页面里 拖入一个Gridview,把不需要的属性都可以不设置,这里的Gridview就是为了承载数据用的 注意这里是AllowPaging="false"
例如:
<asp:GridView ID="gvUserInfo" runat="server" AutoGenerateColumns="False"EmptyDataText="还没有任何信息" Width="100%" AllowPaging="false"Font-Size="12px" HeaderStyle-BackColor="#e9f3d6"PagerStyle-BackColor="#e9f3d6" HeaderStyle-ForeColor="#5c9948"><Columns><asp:BoundField HeaderText="用户名" DataField="userName" /><asp:BoundField HeaderText="编号" DataField="userCode" /><asp:BoundField HeaderText="年龄" DataField="userAge" /></Columns></asp:GridView>
再在B的页面的.cs编写方法方法 如下
private void Export(){//request接收传过来的参数string startTime = Request["StartTime"]; ;string endTime = Request["EndTime"];string userID = Request["UserID"];//业务逻辑处理//IList<UserInfo> list = new List<UserInfo>();//list = blldao.GetUserInfolist(userID);//返回结果集//gvUserInfo.DataSource = list;//gvUserInfo.DataBind();//下边就是导出的部分Response.Clear();Response.Buffer = true;Response.Charset = "GB2312";//下面这行很重要, attachment 参数表示作为附件下载,您可以改成 online在线打开 //filename=FileFlow.xls 指定输出文件的名称,注意其扩展名和指定文件类型相符,可以为:.doc .xls .txt .htm Response.AppendHeader("Content-Disposition", "attachment;filename=1.xls");Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");//Response.ContentType指定文件类型 可以为application/ms-excel、application/ms-word、application/ms-txt、application/ms-html 或其他浏览器可直接支持文档 Response.ContentType = "application/ms-excel";}
此方法在Page_Load里运行
protected void Page_Load(object sender, EventArgs e){Export();}
还有另外一种方法是不需要中间处理页面的
需要添加AppLibrary.dll 这个包在我的资源里边,方法如下 此方法在导出按钮里调用即可
public void OutDataToExcel(System.Web.UI.WebControls.GridView gridview, IList<UserInfo> list){HttpContext hc = HttpContext.Current;ArrayList al = new ArrayList();int count = gridview.Columns.Count;for (int i = 0; i < count; i++){string t = gridview.Columns[i].HeaderText;al.Add(t);}AppLibrary.WriteExcel.XlsDocument doc = new AppLibrary.WriteExcel.XlsDocument();doc.FileName = DateTime.Now.ToString("yyyyMMddHHMMss") + ".xls";string SheetName = string.Empty;SheetName = "数据查询";AppLibrary.WriteExcel.Worksheet sheet = doc.Workbook.Worksheets.Add(SheetName);AppLibrary.WriteExcel.Cells cells = sheet.Cells;#region 第一个样式AppLibrary.WriteExcel.XF XFstyle = doc.NewXF();//添加样式的XFstyle.HorizontalAlignment = AppLibrary.WriteExcel.HorizontalAlignments.Centered;XFstyle.Font.FontName = "宋体";//字体XFstyle.UseMisc = true;XFstyle.TextDirection = AppLibrary.WriteExcel.TextDirections.LeftToRight;//文本位置XFstyle.Font.Bold = false;//加粗#region 边框线的样式XFstyle.BottomLineStyle = 1;XFstyle.LeftLineStyle = 1;XFstyle.TopLineStyle = 1;XFstyle.RightLineStyle = 1;#endregionXFstyle.UseBorder = true;XFstyle.PatternBackgroundColor = AppLibrary.WriteExcel.Colors.Blue;XFstyle.PatternColor = AppLibrary.WriteExcel.Colors.White;XFstyle.Pattern = 1;#region 宽度AppLibrary.WriteExcel.ColumnInfo colInfo = new AppLibrary.WriteExcel.ColumnInfo(doc, sheet);colInfo.ColumnIndexStart = 0;colInfo.ColumnIndexEnd = 4;colInfo.Width = 10 * 256;sheet.AddColumnInfo(colInfo);AppLibrary.WriteExcel.ColumnInfo colInfo1 = new AppLibrary.WriteExcel.ColumnInfo(doc, sheet);colInfo1.ColumnIndexStart = 5;colInfo1.ColumnIndexEnd = 7;colInfo1.Width = 15 * 256;sheet.AddColumnInfo(colInfo1);AppLibrary.WriteExcel.ColumnInfo colInfo2 = new AppLibrary.WriteExcel.ColumnInfo(doc, sheet);colInfo2.ColumnIndexStart = 8;colInfo2.ColumnIndexEnd = 9;colInfo2.Width = 15 * 256;sheet.AddColumnInfo(colInfo2);AppLibrary.WriteExcel.ColumnInfo colInfo3 = new AppLibrary.WriteExcel.ColumnInfo(doc, sheet);colInfo3.ColumnIndexStart = 12;colInfo3.ColumnIndexEnd = 12;colInfo3.Width = 15 * 256;sheet.AddColumnInfo(colInfo3);AppLibrary.WriteExcel.ColumnInfo colInfo4 = new AppLibrary.WriteExcel.ColumnInfo(doc, sheet);colInfo4.ColumnIndexStart = 13;colInfo4.ColumnIndexEnd = 13;colInfo4.Width = 50 * 256;sheet.AddColumnInfo(colInfo4);AppLibrary.WriteExcel.ColumnInfo colInfo5 = new AppLibrary.WriteExcel.ColumnInfo(doc, sheet);colInfo5.ColumnIndexStart = 14;colInfo5.ColumnIndexEnd = 14;colInfo5.Width = 15 * 256;sheet.AddColumnInfo(colInfo5);AppLibrary.WriteExcel.ColumnInfo colInfo6 = new AppLibrary.WriteExcel.ColumnInfo(doc, sheet);colInfo6.ColumnIndexStart = 15;colInfo6.ColumnIndexEnd = 15;colInfo6.Width = 50 * 256;sheet.AddColumnInfo(colInfo6);#endregion#endregion#region 添加表头int idx = 1;for (int i = 0; i < al.Count; i++){cells.Add(1, idx, al[i], XFstyle);idx++;}#endregionint f = 2;//从第二行开始填充数据foreach (UserInfo m in list){cells.Add(f, 1, m.userName, XFstyle);cells.Add(f, 2, m.userCode, XFstyle);cells.Add(f, 3, m.userAge, XFstyle);cells.Add(f, 5, float.Parse(m.sHTMoney).ToString("f0"), XFstyle);cells.Add(f, 6, m.PayResult == "000" ? "成功" : "失败", XFstyle);cells.Add(f, 7, m.CreateTime.ToString(), XFstyle);f++;}doc.Send();hc.Response.Flush();hc.Response.End();}